一次MySQL线上浅调优分享

慢SQL

explain
    SELECT expand.ID 
           expand.CITY_NAME
    FROM expand expand
        LEFT JOIN plan plan ON expand.ID = plan.EXPAND_ID
        LEFT JOIN moment moment ON expand.ID = moment.EXPAND_ID
    WHERE 
      expand.CITY_CODE = '000000'
      AND moment.STATUS = 'PUBLISHED'
      AND plan.STATUS = 'AWAY'
    GROUP BY expand.ID 
    LIMIT 20;

优化前表关键字段声明

优化前执行计划

可以看到这是个糟糕的执行计划，目前能肉眼看出的
1.plan走的全表扫描。
2.group by用的临时表和文件排序

第一次优化

由于没做过SQL优化，只能像修计算机主机一样，拆成小单元排除法，来慢慢优化，于是先分析以下SQL的执行计划

explain
    SELECT expand.ID 
           expand.CITY_NAME
    FROM expand expand
        LEFT JOIN plan plan ON expand.ID = plan.EXPAND_ID
        LEFT JOIN moment moment ON expand.ID = moment.EXPAND_ID

分析：

1.moment表没走索引，因为type是all。
2.plan表也没走索引，PLAN__SEARCH_INDEX是个聚集索引(GARDEN_ID, EXPAND_ID)，上面SQL没用到GARDEN_ID，没遵循最左原则，possible_keys，ref也为空，所以断定plan表也没走索引，至于为什么type是index，还不太理解。

优化：

直接给plan表和moment表的EXPAND_ID加上索引

第二次优化

基于第一次优化后，再查询SQL执行计划，如下：

explain
    SELECT expand.ID 
           expand.CITY_NAME
    FROM expand expand
        LEFT JOIN plan plan ON expand.ID = plan.EXPAND_ID
        LEFT JOIN moment moment ON expand.ID = moment.EXPAND_ID

分析

1.moment和plan都走索引了，type是ref类型，possible_keys，ref也是期望的
2.但是看到Extra，留下了疑问，为什么明明没有用到where关键字，moment表的extra信息显示using where呢？经过查询相关mysql资料，原来是因为moment.EXPAND_ID的字段类型长度和expand.ID的字段类型长度不一致导致的。

优化

修改moment.EXPAND_ID字段长度，保持跟expand.ID字段长度一致

经过二次优化后，执行计划终于是正常的了。Extra信息只显示using index

explain
    SELECT expand.ID 
           expand.CITY_NAME
    FROM expand expand
        LEFT JOIN plan plan ON expand.ID = plan.EXPAND_ID
        LEFT JOIN moment moment ON expand.ID = moment.EXPAND_ID

第三次优化

经过二次优化后，开始where条件的优化

explain
    SELECT expand.ID 
           expand.CITY_NAME
    FROM expand expand
        LEFT JOIN plan plan ON expand.ID = plan.EXPAND_ID
        LEFT JOIN moment moment ON expand.ID = moment.EXPAND_ID
    WHERE moment.STATUS = 'PUBLISHED'
      AND expand.CITY_CODE = '0000'
      AND plan.STATUS = 'AWAY'

分析

1.where条件加入plan.status后，执行计划显示plan表不走索引了.
2.expand表的type是eq_ref证明join里用了expand主键，这个没问题。同时也发现CITY_CODE也没走索引
3.moment表显示正常。

优化

1.给plan.status加索引
2.给expand.CITY_CODE加索引

第三次优化完的执行计划

扩展

1.group by的优化也是个很大的学问，涉及到紧凑索引扫描和松散索引扫描，由于第三次优化的时候误打正着的命中了紧凑索引扫描的逻辑，就顺便把group by用的临时表和文件排序的优化做完了。
2.还搞出了“索引下推”的优化
最后sql执行计划如下：

explain
    SELECT expand.ID 
           expand.CITY_NAME
    FROM expand expand
        LEFT JOIN plan plan ON expand.ID = plan.EXPAND_ID
        LEFT JOIN moment moment ON expand.ID = moment.EXPAND_ID
    WHERE 
      expand.CITY_CODE = '000000'
      AND moment.STATUS = 'PUBLISHED'
      AND plan.STATUS = 'AWAY'
    GROUP BY expand.ID 
    LIMIT 20;

总结：

1.用索引的时候注意2个字段的类型，注意索引失效的场景
2.group by的时候使用紧凑索引，或者松散索引，不要用临时表+文件排序
3.注意使用索引下推的优化

参考连接：

• extra内容说明：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information
• group by优化：https://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html
• 索引下推优化：https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
• 执行计划说明：https://segmentfault.com/a/1190000022696458

生产环境上SQL平台的执行时间

优化前

优化后