MySQL 5.6.38最佳化執行個體一則

MySQL 5.6.38最佳化執行個體一則

導讀：在日常的MySQL的SQL語句最佳化工作中,總會遇到了各種各樣的問題。今天就是遇到了一個比較詭異的問題，在這裡記錄下來方便自己的記憶。

    MySQL版本資訊: MySQL 5.6.38

1. SQL語句（其中的關鍵字資訊已經做脫敏處理）：

SELECT  id , name, headurl, intro, gender, location, job, birthday, source,created_at FROM user  WHERE name LIKE  '%name%'  ORDER BY created_at DESC LIMIT  0 ,  100 ;

  2. 表user的表結構：

  3. SQL的執行計畫和profile資訊以及執行耗時:

  4.最佳化思路：在執行計畫中可以看得到SQL語句由於是模糊查詢所以並沒有使用索引,並且在執行SQL之後可以明顯的看出在建立排序索引上面耗費了99%以上的時間,我們在看整個的SQL語句，只有在欄位created_at上面有做排序操作,所以按照最佳化思路那麼我們就需要在created_at這個欄位上面建立索引。建立索引之後的表結構：

紅框就是添加的索引資訊。

  5.修改之後的SQL的執行計畫和profile以及耗時資訊：

    在上面的執行計畫進行比對我們可以很明顯的看出來,返回的資料由450w減少到了100行,資料量大大的減少了；但是在執行SQL之後發現耗時居然更長了使用了6s多,並且分析profile的時候發現在sending data耗時花費了6.6s的樣子,在這裡解釋一下 sending data耗時指的的是從引擎層發送資料到server層或者是client層。

    發現這種情況我感到很吃驚，我並不知道發生了什麼事情導致這種結果。在多方查詢無果之後我之後請教我的一個師兄，經過我詳細的描述和實驗，他告訴我:主要是由於在where條件過濾和排序的時候走索引沒有查詢到任何的結果導致mysql擷取查詢所有的索引然後在去回表進行全域掃描;在沒有添加的索引的情況下,SQL直接就回回表不會進行全部的索引掃描。

    為了驗證這個結果,我更改了where條件,在沒有添加created_at這個欄位索引的情況下進行對比情況:

沒有添加索引的耗時:

100  rows  in  set  ( 2.53  sec)

添加索引的耗時：

100  rows  in  set  ( 0.16  sec)

可以很明顯的看到添加索引之後 速度提高了一大堆,並且這個是有查詢結果的。