一次偶然機會發現的MySQL“負最佳化”

騎牛上青山發表於2023-01-19
MySql

文章最開始先給大家兩條sql，請猜猜他們執行會有什麼區別？

SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 1
SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 2

這兩條sql看似只是limit的數值不同，但是第一個執行耗時3ms，第二個執行耗時66s，相差2000多倍。

故事的起因

今天要講的這件事和上述的兩個sql有關，是數年前遇到的一個關於MySQL查詢效能的問題。主要是最近刷到了一些關於MySQL查詢效能的文章，大部分文章中講到的都只是一些常見的索引失效場合，於是我回想起了當初被那個離奇的“索引失效”支配的恐懼。

場景復現

由於事情已經過去多年，因此我只能憑藉記憶在本地的資料庫進行模擬。首先建立資料庫school，資料表student：

CREATE TABLE `student` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `student_age_IDX` (`age`) USING BTREE,
  KEY `student_create_time_IDX` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

結構簡單明瞭，其中age和create_time使用BTREE構建了索引。

在使用儲存過程往資料庫填充了500w條左右的資料後，我們使用如下的sql來進行測試：

SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 1

結果如下：

之後嘗試執行如下sql：

SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 2

這就是我們開篇提到的那兩個sql，效能差距是2000多倍。那麼問題來了，為什麼limit的值會影響sql效能，並且會差別如此之大？故事要從MySQL的最佳化說起。

MySQL的“負最佳化”

在分析sql效能的時候，我們當然最常用的是EXPLAIN，將兩個sql分別EXPLAIN，結果如下：


可以看到sql執行計劃並無二致，那麼為什麼執行時間卻相差這麼遠呢？

查詢相關文件就可以在MySQL的官網找到如下的解釋：

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

大致意思就是LIMIT與ORDER BY一起使用MySQL會在找到LIMIT設定的值後立即返回。雖然沒有找到具體的原理性的解釋，但是從上述的這個描述中我們也能夠大致理解這個思路了。

在MySQL中LIMIT與ORDER BY是特殊的組合，尤其是當ORDER BY中的存在BTREE索引的情況下。

普通的查詢是根據條件進行篩選，然後在結果集中排序，然後獲取LIMIT條數的資料，但是在具備上述條件的特殊sql中執行邏輯是這樣的，根據ORDER BY欄位的B+樹索引來查詢滿足條件的資料，直到湊滿LIMIT設定的數值為止，這就存在一個問題，在結果集中的資料大於LIMIT的場景下，這個效能固然是非常棒的，但是如果最後的結果集中的資料小於LIMIT，就會存在永遠湊不滿的情況，所以最終這個MySQL的效能最佳化就會變成全表掃描的“負最佳化”。

根據上述的情況來看的話我們可以大膽猜測，既然是索引導致的最佳化問題，那麼是不是把age欄位的索引去掉反而會更快？

手動執行DROP INDEX student_age_IDX ON school.student刪除索引，然後執行語句，果然執行速度變成了毫秒級：

檢視執行計劃發現在執行時使用了create_time的索引，因此其速度也能保持在毫秒級。

然後我們乾脆把create_time的索引也去除掉：


可以看到沒有索引的情況下耗時也不過是1秒出頭，遠遠不是66秒。可見在這種情況下MySQL的效能最佳化甚至遠遠比不上無索引的查詢。

總結

其實出現這個問題的場景也不算十分特殊，但是排查原因相當困難。當初是花了好幾天查資料翻文件加上不斷實驗才找到了問題所在。只能說MySQL在解析和執行sql的背後做了很多的最佳化，但是這部分對於不夠熟悉瞭解的人來說確實是太黑盒，遇到類似的問題排查也很困難。也許這就是程式設計師成長路上的必經之路吧。

相關文章