mysql性能优化总结

系统配置优化

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 。

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，并且以小表驱动大表，减少嵌套循环 次数。