一、前言
由于前面讲过MySQL设计规约，那在数据量达到一定量级的时候，必定会考虑性能优化。该篇主要分享MySQL性能优化之SQL语句优化。
二、性能优化的思路
- 首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
- 查看执行计划，查看有问题的SQL的执行计划
- 针对查询慢的SQL语句进行优化
- 使用【show profile[s]】 查看有问题的SQL的性能使用情况
- 调整操作系统参数优化
- 升级服务器硬件
三、SQL语句优化
(1）LIMIT优化
- 如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。
- 处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。 LIMIT OFFSET , SIZE；
LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。
解决方案：单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写rows
select * from (select * from tuser2 where id > 1000000 and id < 1000500
ORDER BY id) t limit 0, 20
(2）索引优化
- 为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引，不过要考虑数据的业务场景：查询多还是增删多？
- 尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边。
- 尽量使用覆盖索引，SELECT语句中尽量不要使用*。
- order by、group by语句要尽量使用到索引
- 索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多的索引键值。太长的列，可以选择建立前缀索引
- 索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。
- order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。
(3）查询优化
- 小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数。
- 避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描
- 避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典型的场景就是数据量少的时候）
- JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。
- WHERE条件中尽量不要使用not in语句（建议使用not exists）
- 合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。
网友评论