优化表的存储
- 当表的数据增长几个G了，可以使用optimize table优化表存储，这个语句会拷贝表的数据，重建索引。如果数据量很大，索引不能全部放入buffer pool，这个过程会较慢。
- 主键应该使用较小的存储空间的字段，因为所有的普通索引都会存放主键，可以考虑使用自增主键或者字符串的子串。
- 如果有很多NULL，使用varchar而不是char存储。
- 对于有很多重复文本的表，开启compressed。
只读事务优化
只读事务不需要建立事务id，避免为只读事务建立事务id，可以提高效率
- 可以开启auto commit，普通的select就会是只读事务。
- 可以显示开启只读事务，start transaction read only。
redo log优化
- 增加单个redo log的大小，因为当redo log满了的时候需要执行check point导致数据落盘，set innodb_log_file_size=xxx。
- 增加redo log buffer大小，因为大事务在使用完redo log buffer之后需要将log写到磁盘，set innodb_log_buffer_size=xxx，可动态调整。
导数优化
- mysqldump导出数据的时候默认开启了-opt参数，可以提高导入数据时的效率。
- 关闭自动提交set autocommit=0，使用commit提交。
- 关闭索引重复约束检查，set unique_checks=0。
- 关闭外键约束检查，set foreign_key_checks=0。
- 一次insert多个值, insert into tb(v,x) values(v1,x1),(v2,x2)...。
- 按照主键的顺序插入数据，innodb使用聚簇索引，数据按照主键大小在磁盘上顺序排列。
- 当创建新的mysql实列的时候，关闭redo log，alter instance disable innodb redo_log。
- 使用mysql shell导入数据。
查询优化
- 只建立必要的索引，索引也会对插入，删除，更新操作带来开销。
- 如果索引列不包含NULL，应该使用NOT NULL声明，可以帮助优化器选择合适的索引。
DDL优化
- 可以在加载数据之后再建立普通索引。
- 使用truncate table清空表。外键约束会导致truncate table像普通的delete，在这种情况下建议drop table，然后再create table。
- 改变主键需要重排数据在磁盘的存储，所以不建议。
磁盘IO优化
如果已经优化了数据库和查询，依然很高的磁盘IO活动，导致数据库慢。在cpu使用率低于70%的时候，瓶颈可能在磁盘。
- 增加buffer pool大小，set innodb_buffer_pool_size=xx，建议在系统内存的50%到70%。
- 调整innodb_flush_method，默认是fsync，可以设置为O_DSYNC测一下。
- 当innodb创建表空间文件或者表文件的时候可能触发一次大量写入数据，可以设置innodb_fsync_threshold定义一个阈值，周期性的小批量写数据到磁盘。
- 如果可以使用fdatasync()，建议不用fsync()，fdatasync()只写文件的数据块，不写元数据块
- linux上使用aio，磁盘调度策略使用noop或者deadline
- 使用raid阵列，可以将data和log放到不同的磁盘
- ssd的随机读写比hdd好，hdd适用于顺序读写；表文件，表空间文件，undo表空间文件，临时表文件都是随机读写，innodb系统表，bin log和redo log是顺序读写
使用ssd(cat /sys/block/sdx/queue/rotational)可以设置如下参数:
set innodb_checksum_algorithm=crc32
set innodb_flush_neighbors=0(刷写脏页的时候不刷写同一个extend的其他脏页，1刷写同一个extent的连续脏页，2刷写同一个extent的所有脏页)
set onnodb_idle_flush_pct=xx(减少空闲时候写ssd，延长使用寿命)
set innodb_io_capacity=xx(根据磁盘速度调整)
set innodb_io_capacity_max=xx
set innodb_log_compressed_pages=0(减少redo log)
set innodb_redo_log_capacity=xx(更多使用缓存，减少写ssd)
set innodb_page_size=xx(设置大小和ssd块大小接近，减少写没有变更的数据)
set binlog_row_image=minimal(减少日志) - 如果吞吐量因为checkpoint周期性的下降，可以增大innodb_io_capacity，触发更高频率的flush。如果不存在因为flush操作带来的性能下降，应该尽量调小这个值。合理的设置应该是show engine innodb status可以看到：
历史list长度不超过几千
insert buff megers接近rows inserted
buffer pool中modified pages低于innodb_max_dirty_pages_pct(在没有做批量插入的时候持续观测) - mysql写数据的时候是以16KB为单位，但是磁盘IO是以512为单位，所以可能发生16KB的数据只有一部分写到磁盘了（发生服务器或存储断电），所以存在double write机制，将16KB的数据先写到磁盘一个固定的区域（循环使用，顺序写，系统表空间），然后再实际写到数据文件。fushion io的ssd支持原子写，可以省去两次写。
- 如果确认在恢复数据库的时候使用的zlib版本一致，可以禁用innodb_log_compressed_pages减少redo log产生
优化innodb配置
innodb会自动进行一些优化调整，performance schema记录了性能数据。
- 调整可以存放到change buffer的数据，innodb_change_buffering可以配置为all,none,inserts,deletes,changes,purges，数据更新操作（inserts,deletes,update）会导致索引需要更新，为了延缓更新索引的时机，可以将变更先记录到change buffer，等空闲的时机再将索引页读入内存，然后和change buffer里面的数据合并，将更新合并后的数据写入磁盘，从而减少磁盘io。
- 设置innodb_adaptive_hash_indexes可以关闭或者开启自适应hash索引，适用于等值查询，由innodb自动优化建立，show engine innodb status可以查看使用hash索引的查询比例。
- 限制线程并发数，当并发线程数量达到innodb_thread_concurrency，新的请求会sleep等待innodb_thread_sleep_delay，然后重试，如果依旧需要等待，就会进入一个先进先出队列等待。innodb会根据系统负载自动调整innodb_thread_sleep_delay，可以手动设置innodb_thread_max_sleep_delay限制最大值。
- 调整buffer pool预读页数量，当io容量没有满的时候预读可以提高效率，但是在高负载的情况下会带来性能下降。线性预读是当innodb连续读取innodb_read_ahead_threshold个页的时候，innodb异步读取下一个extent；随机预读是当innodb发现一个extent里面的13个连续页面被读到内存了（且在LRU young区域），那么这个extent的其他页面也会被读取到内存。show engine innodb status\G;可以查看页面预读效率。
- 调整后台io线程数量，适用于高性能服务器，如果show engine innodb status\G看到超过64*innodb_read_io_threads个排队请求，可以调整innodb_read_io_threads大小测试。
- 对于存在大量io和使用高速磁盘（raid,ssd)的情况，可以调高innodb_io_capacity优化性能。innodb后台会刷写脏页和change buffer的数据到磁盘，为了避免后台写影响到服务器的正常工作，会根据innodb_io_capacity调整后台刷写进程活动。
- 调整buffer pool的刷写。当buffer pool中脏页比例达到innodb_max_dirty_pages_pct_lwm定义的低水位（默认是buffer pool的10%）,innodb就会开始刷写脏页到磁盘;如果脏页达到innodb_max_dirty_pages_pct（默认是buffer pool的90%），innodb就会积极的刷写脏页到磁盘。
- innodb_lru_scan_depth控制当page cleaner线程扫描buffer pool LRU链表寻找脏页的的时候扫描多少页。只有当io容量有空闲的时候考虑增加这个值；如果写脏页占尽了io容量，则可以降低这个值，特别是在buffer pool很大的情况下可能用到。innodb_lru_scan_depth应该从较小的值逐步增大，直到观察不到0 free pags。innodb_lrc_scan_depth*innodb_buffer_pool_instances代表了page cleaner扫描的总页数。
- innodb使用自适应算法基于redo log的产生速度和当前flushing的速度来动态调整flush的频率（每秒写脏页到磁盘的数量）。当innodb需要重用一个redo log文件的部分，那么就需要将所有与这部分redo log相关的脏页刷写到磁盘，或者redo log文件满了，都会发生sharp checkpoint，导致吞吐量下降。当系统性能平缓，大的日志文件（innodb_log_file_size），小的毛刺，则应该使用较大的innodb_flushing_avg_loops值保证flushing操作平滑。如果系统性能存在大的毛刺，日志文件较小，则应该使用innodb_flushing_avg_loops保证innodb更快的应对当前性能变化，避免使用75%的日志空间（会触发异步flush，硬编码）。
- 在多核系统上，如果线程在sleep前先尝试在锁上面自旋，如果获取到锁了，那么就会减少线程切换带来的开销，从而提升效率。但是当多个线程在同一个锁上面旋转，就会反复使对方的cache失效。innodb通过让不同线程在锁上旋转的时间随机，来减少这种情况的发生。innodb_spin_wait_delay*innodb_spin_wait_pause_multiplier限制了最大的可以自旋的时间。现在不同的处理器执行pause指令的时间不同，所以可以通过innodb_spin_wait_pause_multiplier对应调整。
- 为了避免预读机制和全表扫描导致的将热点数据从buffer pool驱逐，innodb使用LRU链表管理页面，并且将LRU链表分为两部分:young（热数据）和old（冷数据）。innodb_old_blocks_pct控制old占有的比例，默认37%。 新放入buffer pool的数据会被插入这个分界点。预读进来的页如果不进行后续访问，就会逐渐被从buffer pool驱逐；对old区域的链表记录第一次访问时间，如果短时间（innodb_old_blocks_time，默认1秒）内再次访问(全表扫描时，多次读取一个页面内的不同表记录的时间间隔较小)，并不会导致页面被移动到yong区域，。innodb_old_blocks_pct较小会导致新读取的页很快从缓冲区驱逐，较大会导致和普通LRU链表一样。较大的innodb_old_blocks_time会导致页面被从old链表驱逐的几率变大，这个参数带来的性能影响不好预估。对于大表扫描，应该将innodb_old_blocks_pct调小，对于可以放入内存的小表，可以将innodb_old_blocks_pct调大。
- 调整redo log file大小，增加这个值会减少因为回收redo log带来的磁盘IO，同时又会导致crash recovery时间变长。在两者间取一个平衡。
- 调整buffer pool的数量（innodb_buffer_pool_instances，最大64）和大小(innodb_buffer_pool_size，所有buffer pool的总大小)，保证每个buffer pool不小于1GB。
- 调整最大并发事务
- innodb在执行delete sql的时候不会立即将记录从物理存储删除，只有当delete sql对应的undo log被丢弃时才会执行物理删除。如果DML集中在少量几个表，应该保持innodb_purge_threads较小，减少purge线程之间访问表产生的竞争；如果DML分散在很多表，那么应该加大innodb_purge_threads（最大32）。
- 减少线程切换和竞争带来的开销。如果线程数量达到innodb_thread_concurrency，新的线程就需要排队等待。
