文章最開始先給大家兩條sql，請猜猜他們執行會有什麼區別？ 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看似只是limit的數值不同，但是第一個執行耗時3ms，第二個執行耗時66s，相差2000多倍。

故事的起因

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

場景復現

由於事情已經過去多年，因此我只能憑藉記憶在本地的數據庫進行模擬。首先創建數據庫school，數據表student： sql 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來進行測試： 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： 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刪除索引，然後執行語句，果然執行速度變成了毫秒級：