MySQL InnoDB 常用优化参数

内存相关

• innodb_buffer_pool_size 缓冲池，会缓冲索引页、数据页、undo页、插入缓冲、自适应哈希索引、innodb存储的锁信息、数字字典信息等
• innodb_buffer_pool_instances 允许多个缓冲池实例，每页根据哈希平均分配到不同缓冲池实例中，减少数据库内部资源竞争，增加数据库并发处理能力
  
• innodb_old_blocks_pct 确定modpoint位置，默认37，modpoint指新读取到的页放入LRU（最近最少使用算法）列表中的位置，modpoint之后的列表称为old列表，之前的称为new列表
• innodb_old_blocks_time 表示页读入mid位置后需要等待多久才会被加入到LRU列表的热端
• innodb_purge_batch_size 控制每次full purge回收的undo页的数量
• innodb_change_buffer_max_size 控制change buffer最大使用内存数量

IO相关

• MySQL 5.6 开始支持 Multi-Range Read (MRR)，减少磁盘随机访问，将随机访问转化为较为顺序的访问，适用于 range, ref, eq_ref 类型的查询
  • 查看是否开启 show variables like '%optimizer_switch%' mrr=on,mrr_cost_based=off（off为不判断，总是开启）'
    
  • read_rnd_buffer_size控制键值的缓冲区大小，当大于该值时，执行器对已经缓存的数据根据RowID进行排序，并通过RowID来取得行数据，默认256K
• 5.6 开始支持Index Condition Pushdown（ICP），取出索引的同时，判断是否可以进行条件过滤，过滤后再去获取记录，可以大大减少上层SQL对记录的索取，支持range,ref,eq_ref,ref_or_null类型的查询。
• innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略
  • 默认值为1，事务提交时必须调用一次fsync，将日志刷新到磁盘
  • 0，事务提交时不写入重做日志，这个操作仅在master thread中完成，master thread每1秒会进行一次重做日志文件的fsync操作
  • 2，事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作
  • 优缺点，＃1数据有保障但是依赖于磁盘的性能；＃2在操作系统宕机时会造成事务丢失。＃0&＃2的设置都会使事务丧失ACID特性
• innodb_purge_batch_size
• innodb_max_purge_lag & innodb_max_purge_lag_delay
• binlog_max_flush_queue_time
• innodb_read_io_threads & innodb_write_io_threads，多核cpu可以通过这两个参数更有效的利用cpu性能
• innodb_io_capacity 可以充分利用固态硬盘带来的高IOPS特性
• innodb_purge_threads 将purge线程从master线程分离出来，提高cpu使用率提升存储引擎性能，innodb1.2之后可以设置多个purge线程
• innodb_flush_neighbors 刷新邻接页，对于高iops的磁盘，建议关闭此特性

系统相关

• innodb_fast_shutdown 0表示完成所有full purge和merge insert buffer，并将所有脏页刷新回磁盘；1表示不需要完成full purge和merge insert buffer，但要刷新脏页；2表示不执行以上所有操作，但将日志全部写入日志文件，下次启动时需要恢复（recovery）
• innodb_force_recovery

SLOW QUERY 查询相关

• long_query_time
• log_slow_queries
• log_queries_not_useing_indexes 开启后，没有用到索引的查询也会记录到slowlog里
  
• log_throttle_queries_not_using_indexes 5.6.5新增，表示每分钟允许记录到slow log的未使用索引的sql语句次数
• 可以使用mysqldumpslow命令分析slow log
• log_output，有FILE和TABLE两种，动态修改且是全局的

数据安全性相关

• sync_binlog=[N]表示每写缓冲多少次就同步到磁盘，1表示同步写磁盘的方式写二进制日志。默认为0，由操作系统决定同步
• innodb_support_xa=1，可以保证二进制日志和InnoDB存储引擎数据文件的同步
• innodb_log_file_size 指每个重做日志文件的大小，innodb1.2之前必须小于4G，之后扩大到512G
  
• innodb_log_files_in_group 指定了日志文件组中重做日志文件的数量，默认为2
• innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认为1

在线DDL相关

• innodb_online_alter_log_max_size Online DDL 原理是在执行创建或删除操作的同时，将INSERT、UPDATE、DELETE 这类 DML 操作日志写入到一个缓存中，此参数控制缓存大小，默认128M