关于mysql innodb引擎性能优化的一点心得个人总结了一些mysql优化的一些手段。 mysql的默认参数配置通常是考虑到通用性，小型网站的架构。可优化的空间是非常大的，主要介绍innodb引擎的。 1. innodb_buffer_poor_size. 默认为好像是8M还是16M的样子。该参数相当于myisam引擎的key_buffer-size参数，8M大小对于一个相对成熟点的系统来说是远远不够的。 作用是缓冲数据和索引，对于mysql的性能提升是线性的，当 innodb_buffer_poor_size的值大于所有数据的总大小乘以110%-120%时，再继续增大该值没有多大意义，当 innodb_buffer_poor_size的值远小于所有数据的总大小，性能会直线下降，通常对于独立的mysql服务器， innodb_buffer_poor_size最大可以设为内存大小的70-80%。
2. 开启慢查询日志slow.log，增加参数：log-queries-not-using-indexes，所有没走索引的sql语句都会记录到slow.log中,这样可以很方便的把系统中所有没走索引的sql语句全部抓出来优化
3. 为什么sql语句性能低？ 因为访问了太多的数据（过多的行或列，或没走索引）。 通过explain查看sql语句有没走索引，访问的行数rows possibility key 是理论上应该走的索引，key表示实际走的索引，如果发现possibility key是有走索引的，实际上key为null 时，是由于mysql底层的优化器对sql语句进行了优化，因为评估该sql语句访问的行数太多，所以用了全表扫描，出现这种情况可以在sql语句中强制使用索引（force index）来对比使用force index和原sql语句的执行时间，选择时间短的语句，有时候mysql查询优化器不一定是对的。 rows参数表示扫描的行数， Extra参数如果是Using index 说明是最好的情况，走了覆盖索引，其次是using wherw, 如果出现using filesort和using temporary就要看看是否可优化.
4. 提高query_cache命中率： 查看query_cache命中率
Qcache_hits表示命中次数。 命中率的计算方式 Qcache_hits/(Qcache_hits+ Qcache_inserts) * 100% 下面讲讲提高qcache命中率的方法： 一）：数据动静分离。 比如一篇文章，文章内容，作者放在一张表，另外的扩展信息，比如文章的浏览量，回答数，投票数放在另一张表，因为浏览量等是动态数据，操作比较频繁，如果放在一张表中，浏览量+1时对表进行更新时，该表的query_cache都会失效. 值得注意的是：单条sql语句的query_cache大小是有限制的，当一条sql语句返回的数据大小超过query_cache_limit (默认1M)的值时不会缓存，所以可以适当增加query_cache_limit的值 query_cache_size表示所有query_cache的总大小，当整个数据库的query_cahce大小超过该值，sql语句也无法缓存。
5. 减少查询次数： 一）:字段冗余 二）：应用层cache
6. 复杂查询分解： 假设这样一个场景：一篇文章对应一个作者，在一个list列表里，有20篇文章，同时要显示每个作者对应的家庭住址信息。 一般这种的业务只要通过一个连表查询的sql语句就可以取到信息。 但是，可以分解为2个查询， 一）：先取出20篇文章列表，foreach循环取出每个作者uid，放在数组里，去重复 二）: 通过uid数组去用户表中批量查询（in）取出每个作者家庭住址 这样做有什么好处？ 一）：query_cache命中率的可能性更高。 连表查询只要user表和文章表其中一个表发生写入，该连表查询的query_cache就会失效。 而分成2个查询，就算其中一个表的query_cache失效了，另一个表的query_cache依然还在。 二）：缓解写入堵塞：因为在连表查询的过程中，2个表访问涉及到行的数据都会加上读锁，对这些数据的写入将会被堵塞，导致性能降低， 分2次查询，这种写入堵塞的时间将会大大降低。 三）：方便扩展：当数据库对业务进行垂直拆分的时候，比如把user表放到另一个库的时候，这时候要对连表查询进行重构
7.分页优化： 比如SELECT * FROM question ORDER BY id LIMIT 1000000, 10; 性能比较低下， 有2种优化： 一种是between：SELECT * FROM question WHERE id BETWEEN 1000000 AND 1000010; 如果id不是连续的，可以先找出id，再用in查询 第二张是倒着分页：比如总数为1000010条，最后一页数据可通过以下语句来查询：
SELECT * FROM question ORDER BY id desc LIMIT 0, 10;
8. 应用层连接mysql所有的账号，权限不宜过多过细。 在执行sql前，mysql要先进行用户的检验，以及检验是否有执行该sql语句的权限
9. 关闭skip_name_resolve，减少逆向dns解析的消耗
下面再聊聊对innodb写入的优化： 1. innodb_flush_log_at_trx_commit。默认为1，该参数对于写入性能提升有非常大的帮助。可以设置的值为0，1，2。 推荐设置2。 设置0表示写入数据不刷新到磁盘，每秒定时flush到磁盘，该值是速度最快的，但是不安全，当mysql服务器挂掉，会丢失1秒的写入数据 设置1时表示每次写入都刷新到磁盘，该值速度最慢，但是是最安全的 设置2时表示每次写入时不刷新到磁盘，但刷新到系统缓存，每秒flush到磁盘，性能较设置1时有很大的提升，就算是mysql服务器挂掉也不会丢失1秒的数据，只有当整个操作系统挂掉时，才有可能丢失1秒的数据 测试实例：当设置为1的时候，我在生产环境insert 一条记录的时间是30ms（表的记录数是30万） 当把参数改成2，时间马上变成0ms。
2. 对于多表同时写入用事务，循环插入用事务（安全，速度也很快）或values拼接（速度最快，但不安全）。 通常有那么种业务场景，添加一个商品，往商品表insert一条记录，同时往其他扩展表写入信息（库存，商品扩展信息等..），通常的理解是用事务，为了保证数据的一致性。 此外，事务是否还有其他的优势？答案是有的，可提升多表写入的性能。 为什么？ 一). innodb默认是自动提交的，不用手动开启事务的情况下，没写入一次，都会有自动开启事务，自动commit的消耗.
二). 不手动开启事务的情况下，10个insert语句会写入10次redo日志，每次insert自动commit时，会把该事务的所有日志写入到redo log中，10条insert语句则需要写入10次。 而手动开启事务，当10条insert完成时，再最后commit，只需要写入1次redo log。
同理：批量导入数据该如何提高性能？实质上批量导入数据就是循环insert到同一张表。 一): 不手动开启事务，在foreach里循环insert语句。性能最差
二): 手动开启事务，循环插入，最后再commit一次，性能倒数第二差
三): 把所有insert语句拼接成一条，即values(a,b),(c,d),(e,f)。性能最好 四): 在（三）的基础上外面加手动开启事务。性能其次。
贴一个别人的测试数据
多条数据合并为同一个SQL，并且在事务中进行插入
可以看到，第四种方式1万条记录只要0.309秒，第三种方式是0.218秒，第二种方式1.05秒，第一种方式11.678秒
3. 导入数据优化： 一): 导入数据之前执行set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行set unique_checks=1 二): 导入前执行set autocommit=0禁止自动事务的自动提交，数据导入完成之后，执行set autocommit=1 恢复自动提交操作 三): 导入前执行set foreign_key_checks=0来禁止对外键的检查，数据导入完成之后再执行set foreign_key_checks=1
4. innodb_log_file_size。该值对高并发写入性能有提升的帮助，官方建议设置为innodb_buffer_poor_size值的25%-100%，个人建议50%.
5. 适当减少索引。 索引会影响写入速度. 通常索引用的是B-tree算法，一般为3层结构，最多不超过5层，每写入一条数据，需要额外的把数据放到B-tree中合适的位置，方便索引快速找到数据(理想的情况下可能一步到位插入到对应位置，很多情况下还可能面临左旋右旋拆页的情况)。
6.根据业务需求，考虑是否可关闭xa事务支持，可以有效减少磁盘刷新次数 |
|
|
