优化Innodb存储引擎

优化innodb表的存储布局

一旦数据达到了稳定的状态，或是增加到了很大的情况下，就要考虑使用optimize table 语句来重新组织表，压缩浪费的空间，重新组织的表能占用更少的空间，有更好的全表扫描性能，当索引或调整应用的代码效果都不明显的时候，这是一个很直接的办法。

optimize table拷贝部分表的数据重建索引，主要是减少表空间中的碎片和索引中数据的压缩，具体的好处取决于每个表中的数据，可能有些能获取很大的提升，有的不能，如果表很大那么这个操作可能会很慢，或正在重建的索引不再bufferpool，第一次加载大量数据后，可能会运行很慢。

在innodba中，有个长的主键，会浪费很多的空间，在所有的二级索引记录中主键值都是被复制的指向同一个记录，如果有个很长的主键就考虑创建一个自增的主键。

使用varchar而不是char来存储字符变量，char是固定长度的，可能会浪费空间

对很大的表或是很多的text或numeric的列，考虑使用compressed行格式。

优化innodb的事务管理

默认的mysql使用autocommit=1,这样在繁忙的系统是不是很好，好的做法是将朵儿dml放在一个事务中，oracle的那种处理方式。如果事务修改了数据，那么在每次提交的时候，innodb都会刷新日志到磁盘上，每次修改都提交，那么对于io的吞吐会有限制。

如果事务都是select的语句，那么autocommit会识别只读事务优化他们。

在插入，更新，删除大量的数据后避免rollback，在一个很慢的系统上，做这个操作只会更坏，回滚可能会花费原dml语句几倍的时间去执行，杀掉数据库的进程不会有帮助，服务器在启动的时候会自动回滚。

一旦出现了回滚，为了加快操作，可以加大buffer pool,或是杀掉server，然后使用innodb_force_recovery=3启动服务器。

如果能够承受丢失最近一次提交的数据，可以设置innodb_flush_log_at_trx_commit=0，这样每一秒会刷新下日志，但是刷新是没有保证的，同时设置innodba_support_xa=0，会减少因为同步二进制日志和磁盘数据的刷新次数。

当行被修改或被删除后，行及相关的undo日志不会马上被物理删除，即使事务提交结束后也不会马上删除，类似oracle的多版本管理。

当一个长时间运行的事务修改了表，对这个表进行的查询不会使用转换索引技术，通常查询可以在二级索引中获取数据，儿不会查询表。如果二级索引页上发现了一个page_max_trx_id太新了，或是二级索引上的数据被标记成被删除的状态，innodb也许会使用聚簇索引来查找记录。

优化innodb只读事务

mysq5.6.4中，innodb对于只读的事务不会 事务id，对于写操作，锁读，或ｓｅｌｅｃｔ．．ｆｏｒ　ｕｐｄａｔｅ会有事务ｉｄ，这样减小了内部数据结构的大小。

当事务以start transaction_read_only开始的时候就是只读的事务，在这个事务中是不能对数据做修改的，但是对于会话级别的临时表可以修改，或是加锁，因为这些改变对于别的会话都是不可见的。

优化innodb redo

确保redo日志文件很大，跟buffer pool一样大，redo 日志文件的大小和数量是由innodb_log_file_size和innodb_log_file_in_group来控制的。

考虑增大log_buffer,大的log_buffer能让大的事务在提交前不必写日志到磁盘上，所以系统上有很多的插入删除，更新等操作，需要设置大的log buffer,它的大小是由Innodb_log_buffer_size控制的。

对innodb表的批量数据加载

当导入数据的时候，关闭autocommit。

如果在二级键上有唯一约束，那么在加载数据的时候，临时禁用set unique_checks=0;

如果有外键约束，在加载数据的时候禁用set foreign_key_checks=0

使用多行的insert语句来减少客户端与服务器端的交互

当加载数据到包含auto-increment字段的表时，设置innodb_autoinc_lock_mode=2

优化innodb查询

对每列不要创建分开的二级索引

当列不含空值得时候，要声明成not null

如果对一个不经常更新的表有很多重复的查询，可以启用查询缓存，相当于oracle的结果集缓存

query_cache_type=1

query_cache_size=10M

优化innodb的磁盘io

设置innodb_buffer_pool_size，通常这个值设置成物理内存的80%

如果因为checkpoin的操作导致了性能下降，考虑增加innodb_io_capacity的值。

其他io相关的参数

innodb_adaptive_flushing,innodb_change_buffer_max_size,innodb_change_buffering,innodb_flush_neighbors,innodb_log_buffer_size,innodb_log_file_size,innodb_lru_scan_depth,innodb_max_dirty_pages_pct,innodb_max_purge_lag, innodb_open_files,innodb_page_size,innodb_random_read_ahead,innodb_read_ahead_threshold,innodb_read_io_threads,innodb_rollback_segments,innodb_write_io_threads, andsync_binlog.

优化有很多表的系统

innodb在系统启动后第一次访问表的时候计算索引的行值，而不是将它存储在表中，对于将数据分区到很多表的系统可能会花费很长的时间，为了后面使用表，可以先用下面的语句预热下select 1 from tab_nmae limit 1;

优化innodb配置参数

可以配置的有下面的几点

1让innodb使用高性能内存分配器

http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-use_sys_malloc.html

2控制DML类型操作来决定哪个innodb的缓存来放改变的数据避免频繁的小磁盘写，默认的是缓存所有的dml操作。跟oracle的keep池等概念一样

http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-change_buffering.html

3打开自适应hash索引，使用Innodb_adaptive_hash_index

4设置并发线程

5控制预读的数量，在负载高的系统上，太多的预读会导致系统间隙性性能下降

6增加读写后台进程 数量

7控制后台io线程数量

8控制后台写的算法

9利用多核处理器，和缓存配置来减少上下文切换

10调整日志文件设置

11控制buffer pool的大小和数量

12增加最大并发事务的数量

13减少在并发线程间的转换，设置innodb_thread_concurrency=32，innodb_concurrency_tickets=5000,这样可以让线程在被交换出去之前完成后面的工作，避免上下文的切换。