MySQL Limit執行過程分析探索

故事還得從下面的圖說起：

what? 兩條sql執行結果的id列居然不一致。。。。。。

一、LIMIT 處理過程

為了故事的順利發展，我們得先建立一張表：

CREATE TABLE `t_null_index` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `key1` char(1) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key1` (`key1`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3

表 t_null_index 包含3個列，id列是主鍵，key1列是二級索引列。表中包含9999條資料。

mysql> select * from t_null_index order by key1 limit 1;
+-------+------+----------------------------------+
| id    | key1 | common_field                     |
+-------+------+----------------------------------+
| 10019 | a    | a9ecd8f845cd4e6791e99af406e075c1 |
+-------+------+----------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from t_null_index order by key1 limit 1;
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_null_index | NULL       | index | NULL          | idx_key1 | 4       | NULL |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

當我們執行上面的這條sql，是使用了 idx_key1 二級索引，這個好理解，因為在二級索引idx_key1中，key1列是有序的。而查詢是要取按照key1列排序的第1條記錄，那MySQL只需要從idx_key1中獲取到第一條二級索引記錄，然後直接回表得到完整聚簇索引的記錄返回使用者端即可。

但是如果我們把上邊語句的 limit 1 換成 limit 5000, 1，效果會如何？

mysql> select * from t_null_index order by key1 limit 5000, 1;
+-------+------+----------------------------------+
| id    | key1 | common_field                     |
+-------+------+----------------------------------+
| 10125 | e    | e90499ca17b44727ab44a08c1cf609e8 |
+-------+------+----------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from t_null_index order by key1 limit 5000, 1;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_null_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9847 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

當 limit 1 換成 limit 5000, 1 後，我們發現沒有使用 idx_key1 二級索引，反而使用了全表掃描，並且進行 Using filesort。

開始我很不理解，limit 5000, 1 也可以使用二級索引 idx_key1啊，我們可以先掃描到第5001條二級索引記錄，對5001條二級索引記錄通過主鍵id回表取得完成聚簇索引記錄不就好了嗎？這樣的代價也比全表掃描+filesort牛批啊。

Limit具體是怎麼搞？

我們知道，MySQL 內部其實是分為 server層 和 儲存引擎層，具體 server層和儲存引擎層具體的互動這裡就不說了。

對於limit的操作，MySQL是在server層準備向用戶端傳送記錄的時候才會去處理limit子句中的內容。

select * from t_null_index order by key1 limit 5000, 1;

如果使用 idx_key1 索引執行上述查詢，那麼MySQL會這樣處理：

（1）server層向InnoDB要第1條記錄，InnoDB從idx_key1中獲取到第1條二級索引記錄，然後進行回表操作得到完整的聚簇索引記錄，然後返回給server層。server層準備將其傳送給使用者端，此時發現還有個limit 5000, 1的要求，意味著符合條件的記錄中的第5001條才可以返回給使用者端，則不能將記錄返回給使用者端，同時會先記錄下當前是第1條。

（2）server層再向InnoDB要下一條記錄，InnoDB再根據二級索引記錄的next_record屬性找到下一條二級索引記錄，再次進行回表得到完整的聚簇索引記錄返回給server層。server層再將其傳送給使用者端的時候發現當前記錄仍然不是5001條，所以就放棄了將記錄傳送給使用者端，同時將記錄數+1。

（3）。。。重複上述操作

（4）直到server層發現InnoDB返回的聚簇索引記錄是5001條的時候，server層才會將InnoDB返回的完整聚簇索引記錄傳送給使用者端。

從上述過程中我們可以看出，由於MySQL中是server層實際向用戶端傳送記錄前才會判斷limit子句是否符合要求，所以如果使用二級索引執行上述查詢的話，意味著需要進行5001次回表操作。server層在執行執行計劃分析的時候會覺得執行這麼多次回表的成本太大了，還不如直接 全表掃描+filesort 快呢，所以就選擇了 全表掃描+filesort 執行查詢。

二、開始的圖

說著說著，差點忘記了故事的前奏的圖了