MySQL查詢優化器帶來的潛在問題

緣起

之前聽了一節SQL優化的公開課，感覺很過癮，公開課鏈接如下：

SQL開發中容易被忽略的BUG_騰訊課堂

要學習，就離不開實踐，我喜歡一句話：

擼起袖子加油干

那麼就讓我復現一下，並做簡單分析。

現象

表DDL如下：

插入若干實驗數據：

此時執行這個SQL：

select *, count(*) from tb1 group by emp_no;

得到的結果如下：

這個SQL的執行計劃如下：

此時強制指定使用dept_no_2這個索引，結果如下：

可以發現結果出現了明顯的不同，這個SQL的執行計劃如下：

執行計劃不同，得到的結果也不同，實際上的表現就是同一條SQL得到的結果不同，無法給出正確的答案。

原理分析

以上面的表和數據為例，畫出emp_no的索引結構和dept_no_2的索引結構。

這是索引emp_no的建議示意圖，上面是索引，底下是表：

在使用這個索引的時候，會經歷以下步驟：

1. 檢索索引，得到主鍵值；
2. 通過主鍵值在表中檢索數據，可以得到按照主鍵值排序的鍵值對列表，類似[{1:(109,10101,..), 2:(109,10101,...)}...]；
3. group by自帶排序效果，此時按照emp_no列排序，對於有一樣emp_no的，按照插入順序排列。

因此會發現結果中的dept_no顯示為109，這是因為109的記錄是先寫入的。

下面是dept_no_2的索引結構：

使用這個索引的時候SQL會經歷以下步驟：

1. 檢索索引，得到主鍵值；
2. 通過主鍵值在表中檢索數據，可以得到按照主鍵值排序的鍵值對列表，類似[{1:(109,10101,..), 2:(109,10101,...)}...]；
3. 因為group by子句自帶排序效果，因此優化器會根據emp_no排序，但是面對有相同emp_no的值，優化器會選擇索引上有序的dept_no進行排序，因此看到的結果中dept_no顯示為101。

解決方案

MySQL的SQL在規範性上一直不如Oracle，在MySQL5.7以後默認已經不允許這種非規範的SQL。

書寫SQL的時候要進行規範檢查，建議將sql_mode設置為ONLY_FULL_GROUP_BY。

例行閑扯

我以前是個Oracle DBA，當我轉行做MySQL的時候我看見這種不完全group by語句，內心中是有無數個羊駝飛過的。

但是當我用了很多年MySQL之後我覺得這種不規範的SQL有時候還挺爽的，我當時並沒有考慮過這種爽的背後隱藏的危險。

還好，MySQL5.7以後sql_mode中默認添加了ONLY_FULL_GROUP_BY。

這次終於圖文無關了，因為我喜歡咖啡，我就找了一張咖啡豆的圖片。