一次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;           

優化前表關鍵字段聲明

表名	字段名字	字段類型	是否是索引
expand	ID	varchar(44)	主鍵
	CITY_CODE	varchar(20)	否
plan	EXPAND_ID
	STATUS
moment		varchar(64)

優化前執行計劃

可以看到這是個糟糕的執行計劃，目前能肉眼看出的

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平台的執行時間

優化前

優化後