MySQL性能优化

概述

mysql作为常用的数据库之一，不管是面试还是工作，都是不可避免的，掌握mysql性能优化非常重要，他不仅让你能轻松面对mysql优化面试，也能帮助你在工作中写出更加优秀的sql。

系统配置优化

1）保证从内存中读取数据，扩大innodb_buffer_pool_size，默认128M，可到3/4，修改 my.cnf，降低磁盘操作。
2）数据预热，通过预热脚本，将磁盘上的全部数据缓存到内存中。
3）降低磁盘写入次数，增大redolog，减少落盘次数，生产中不开通用查询日志，遇到性能问题开慢查询日志，不要求极高安全性，将写redolog策略 innodb_flush_log_at_trx_commit设置为0或2，不即时写入减少磁盘操作。
4）提高磁盘读写性能，使用SSD或内存磁盘。

表结构设计优化

1）设计中间表，针对于统计或实时性不高需求。
2）设计冗余字段，减少表之间关联操作，比如用户、订单，也可以在订单表加入一个所属用户姓名。
3）拆表，字段太多或者字段使用较少进行拆分。
4）主键优化，每张表一个主键索引，分布式情况下雪花，不然就自增。
5）字段设计，将表字段长度设计得尽量小，因为越小查询越快，而且最好都为not null避免null值比较，能用数值类型就别用文本，数值类型效率高，如性别。

SQL索引及优化

1）使用explain查看索引使用情况，通过慢查询日志找执行时间过程的sql，进行分析，查看索引使用情况，重点关注如下：
type列，连接类型，最好达到range级别，杜绝出现all级别。
rows列，扫描行数，预估值。
extra列，详细说明，建议优化的值如下：Using filesort，Using temporary 。
常见索引：where 字段 、组合索引 （最左前缀） 、 索引下推 （非选择行不加锁） 、覆盖索引（不回表） on 两边、排序 、分组统计。
2）sql语句中in包含的值不应过多，避免消耗过大。
3）select语句务必指明字段名称，减少CPU、IO、内存、网络带宽消耗，而且表结构变化用*可能影响前端。
4）当只需要一条数据的时候，使用limit 1，停止全表扫描。
5）排序字段加索引提高效率。
6）限制条件中其他字段没有索引，尽量少用or，否则会造成查询不走索引。
7）用union all 代替union，因为union将结果合并后还要过滤性操作去重。
8）order by rand()不走索引，别用。
9）区分in和exists、not in和not exists，如果是exists，那么以外层表为驱动表，先被访问，如果是in，那么先执行子查询。所以in适合于外表大而内表小的情况；exists适合于外表小而内表大的情况。
10）使用合理的分页方式以提高分页的效率，降低 limit m,n 中m 的值。
11）分段查询，当查询范围过大，查询缓慢时，通过程序，分段进行查询，循环遍历，将结果合并处理。
12）不建议使用%前缀模糊查询，只有’name%’才有走索引。
13）where子句中对字段少用表达式操作，因为会造成不走索引。
14）避免隐式转换，传入类型和定义类型不一致触发隐式转换。
15）联合索引，要遵守最左前缀法则，否则失效。
16）优化器采取它认为合适的索引来检索sql语句效率不理想可以使用force index来强制查询走某个索引。
17）存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。
18）使用join优化，尽量使用inner join，避免left join，并且以小表驱动大表，减少嵌套循环次数。