《性能测试》读书笔记_数据库优化

数据库配置常用参数

• innodb_buffer_pool_size:InnoDB 缓存数据、索引、锁、插入缓冲、数据字典等
• innodb_log buffer size:InnoDB日志缓存区的大小
• innodb log file size:InnoDB 重做日志的大小
• innodb_log_files ingroup:InnoDB重写日志文件组
• innodb_file_per_table:设置为0表示使用共享表空间，设置为1表示启用InnoDB的独立表空间，便于管理
• innodb_temp_data file path:设置临时表最大空间
• innodb_status_file:可启用InnoDB的状态文件，便于管理员查看以及监控
• innodb_flush_log_at_trx_commit: 当设置为0时，该模式速度最快，但不太安全mysqld 进程的崩溃会导致上一秒所有事务数据的丢失。当设置为1时，该模式是最安全的，但是速度最慢。在 mysqld 服务崩溃或者服务器主机宕机的情况下，二进制日志最多只可能丢失一个语句或者一个事务。当设置为2时，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，截至上一秒的所有事务数据才可能丢失
• max_connections:将所有的账号的所有的客户端并行连接到MySOL服务的最大数量，简单说是指 MySQL 服务能够接受的最大并行连接数
• max_user_connections:将某一个账号的所有客户端并行连接到 MySQL服务的最大数量，简单说是指同一个账号能够同时连接到 MySOL服务的最大连接数。设置为0表示不限制
• max_connect_errors:指某一个IP 主机连续连接失败的次数，如果超过这个值，这个IP 主机将会阻止自身发送出去的连接请求
• tmp_table_size:设置临时表内存最大值，每个线都要分配该空间，不宜设置过大max_heap_table_size和tmp_table_size 建议设置一样大
• sort_buffer_size:排序缓冲区大
• join_buffer_size:表连接缓冲区大小
• read_buffer_size:读取缓冲区大小。
• read_rnd_buffer_size:随机读取缓冲区大小
• query_cache_size:查询缓存，建议关闭。设置为0表示关闭，设置为1表示开启
• key_buffer_size:索引块使用的缓冲大小。如果是InoDB引擎，key buffer size可以设置较小。如果是以 MyISAM引擎为主，则可设置较大。
• interactive_timeout:交互式连接的超时时间，例如使用MySQL客户端连接数据库
• wait_timeout:非交互式连接的超时时间，例如使用JDBC 连接数据库。
• innodb_read_io_threadsInnoDB & innodb_write_io_threads:读写O线程数，建议等于CPU核数，值的允许范围是 1~64。如果读操作比写操作多，可适度调大读线数，调小写线程数。
• long_query_time:设置慢查询值，单位为秒。
• slow_query_log:是否开启MySQL慢 SQL的日志，设置为0表示关闭，设置为1表示开启
• log_output:设置日志输出是写表还是写日志文件，为了便于程序去统计，建议写表
• slow_query log file:设置慢查询日志存放的路径。
• log_throttle_queries_not_using_indexes:该参数用来限制相同慢查询语句每分钟被记录到慢查询日志中的次数，防止慢查询日志的过快增大。
• log_queries_not_using indexes: 是否检查未使用索引的 SQL，设置为0表示关设置为1表示开启。
• innodb_buffer_pool_dump_at_shutdown & innodb_buffer_pool load_at_startup: 重启MySQL 服务时，快速预热缓冲池，设置为0表示关闭，设置为1表示开启。
• innodb_print_all_deadlocks:是否打印死锁日志，设置为0表示关闭，设置为1表示开启

SQL 及索引优化

避免全表扫描

• 避免不写where 条件导致全面扫描以及返回结果量过大的情况
• 对查询频次较高且数据量比较大的表，在where查询条件、order by 排序、group by分组等操作的列上建立索引。
• 避免使用模糊匹配(全模糊和左模糊)，会导致索引无法使用，引起全表扫描
• 避免列运算(在建有索引的列上使用函数或表达式)，会导致索引无法使用，引起全表扫描。
• 尽量避免使用!=或<>运算。
• 尽量避免在where 子句中对字段进行 null值判断，尽可能使用not null填充数据库字段，备注、描述、评论之类的可以设置为 null，其他的字段最好不要使用 null。
• 尽量避免在 where 子句中使用or 来连接条件，如果一个字段有索引，另一个字段没有索引，则将进行全表扫描。
• in和not in 也要慎用，否则会导致全表扫描。对于连续的数值，能用between 就不要用in。用exists 代替in是一个好的选择。
• 使用联合索引时，必须使用该索引中的第一个字段作为条件才能保证系统使用该索引，应尽可能让字段顺序与索引顺序相一致。
• 避免使用 select count(*)，应使用 count(1)或 count(具体列名)，否则会引起全表扫描

索引建立原则

• 索引可以提高 select效率，但同时降低了insert 及 update效率，因为insert 或 update有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
• 对查询频次较高且数据量比较大的表，建立索引。
• 在排序字段上，因为排序效率低，添加索引能提高查询效率。
• 在多表连接的字段上需要建立索引，这样可以极大提高表连接的效率
• 对可以扩展已有索引的情况，就不要新建索引
• 需要建索引的字段长度不宜过大，越短检索性能越好。例如只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对数字型而言只需要比较一次就够了。
• 使用前级索引，对于TEXT 和 BLOG 等关的字段，全文检索性能不高，使用前缀匹配可以大大提高检索速度。

其他方面

• 尽量避免多表关联，不建议进行三表及以上的关联查询。
• 尽量避免返回结果集过大。有些场景需要业务需要返回超大结果集，有些场景下则没有必要。尽量不要使用 select *，而是指定列进行查询;通过where 条件做精确限制减小返回结果集大小，从而减轻应用服务端处理大型结果集的消耗。
• 尽量避免大事务操作，提高系统并发能力。
• 分库分表。分库分表的核心目的是将单点资源瓶颈，尤其是磁盘IO 瓶颈，分散给多个节点来共同承担。