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

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

文章最開始先給大家兩條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;

結構簡單明瞭,其中agecreate_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.

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

MySQLLIMITORDER 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的背後做了很多的最佳化,但是這部分對於不夠熟悉瞭解的人來說確實是太黑盒,遇到類似的問題排查也很困難。也許這就是程式設計師成長路上的必經之路吧。

相關文章