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

what? 兩條sql執(zhí)行結(jié)果的id列居然不一致。。。。。。

一、LIMIT 處理過程

為了故事的順利發(fā)展，我們得先創(chuàng)建一張表：

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個(gè)列，id列是主鍵，key1列是二級(jí)索引列。表中包含9999條數(shù)據(jù)。

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)

當(dāng)我們執(zhí)行上面的這條sql，是使用了 idx_key1 二級(jí)索引，這個(gè)好理解，因?yàn)樵诙?jí)索引idx_key1中，key1列是有序的。而查詢是要取按照key1列排序的第1條記錄，那MySQL只需要從idx_key1中獲取到第一條二級(jí)索引記錄，然后直接回表得到完整聚簇索引的記錄返回客戶端即可。

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

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)

當(dāng) limit 1 換成 limit 5000, 1 后，我們發(fā)現(xiàn)沒有使用 idx_key1 二級(jí)索引，反而使用了全表掃描，并且進(jìn)行 Using filesort。

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

Limit具體是怎么搞？

我們知道，MySQL 內(nèi)部其實(shí)是分為 server層 和 存儲(chǔ)引擎層，具體 server層和存儲(chǔ)引擎層具體的交互這里就不說了。

對(duì)于limit的操作，MySQL是在server層準(zhǔn)備向客戶端發(fā)送記錄的時(shí)候才會(huì)去處理limit子句中的內(nèi)容。

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

如果使用 idx_key1 索引執(zhí)行上述查詢，那么MySQL會(huì)這樣處理：

（1）server層向InnoDB要第1條記錄，InnoDB從idx_key1中獲取到第1條二級(jí)索引記錄，然后進(jìn)行回表操作得到完整的聚簇索引記錄，然后返回給server層。server層準(zhǔn)備將其發(fā)送給客戶端，此時(shí)發(fā)現(xiàn)還有個(gè)limit 5000, 1的要求，意味著符合條件的記錄中的第5001條才可以返回給客戶端，則不能將記錄返回給客戶端，同時(shí)會(huì)先記錄下當(dāng)前是第1條。

（2）server層再向InnoDB要下一條記錄，InnoDB再根據(jù)二級(jí)索引記錄的next_record屬性找到下一條二級(jí)索引記錄，再次進(jìn)行回表得到完整的聚簇索引記錄返回給server層。server層再將其發(fā)送給客戶端的時(shí)候發(fā)現(xiàn)當(dāng)前記錄仍然不是5001條，所以就放棄了將記錄發(fā)送給客戶端，同時(shí)將記錄數(shù)+1。

（3）。。。重復(fù)上述操作

（4）直到server層發(fā)現(xiàn)InnoDB返回的聚簇索引記錄是5001條的時(shí)候，server層才會(huì)將InnoDB返回的完整聚簇索引記錄發(fā)送給客戶端。

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

二、開始的圖

說著說著，差點(diǎn)忘記了故事的前奏的圖了??

奇怪了？為什么都是 limit 5000,1，而兩條sql執(zhí)行結(jié)果的id列居然不一致，我們來看一下兩條sql的執(zhí)行計(jì)劃：

mysql> explain select id from t_null_index limit 5000, 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 | 9847 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_null_index 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 | NULL  |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

通過執(zhí)行計(jì)劃，我們可以看出 select id from t_null_index limit 5000, 1;這條sql執(zhí)行過程采用了idx_key1，我們上面說到 limit 5000, 1 這個(gè)條件意味著會(huì)進(jìn)行5001次回表操作，為什么這里又走了 idx_key1 索引呢？

其實(shí)，由于 select id 查詢的查詢列表只有一個(gè) id 列，而 idx_key1 索引的葉子節(jié)點(diǎn)包含了 索引列key1+主鍵id的信息，故MySQL可以通過僅掃描二級(jí)索引idx_key1，然后無需回表操作直接就可以獲取到想要的id列并且返回server層，server層再判斷是否滿足第5001條記錄，如果不滿足，再向InnoDB要下一條記錄，直到滿足為止。這樣就省去了5001條記錄的回表操作，從而大大提升了查詢效率。

那到底為啥兩條sql執(zhí)行結(jié)果的id列值不一樣？

我們來畫一畫 idx_key1索引的示意圖，如圖所示：

通過圖上，我們可以看出 idx_key1 索引B+樹的葉子節(jié)點(diǎn)，根據(jù)key1值由左向右升序排列，當(dāng)key1列相同的節(jié)點(diǎn)，則按照id升序由左向右排序。

mysql> explain select id from t_null_index limit 5000, 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 | 9847 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

對(duì)于上述SQL，由于掃描二級(jí)索引 idx_key1，其實(shí)結(jié)果集是按照 key1 和 id 這兩個(gè)鍵進(jìn)行排序的，可以通過 select * from t_null_index order by key1, id limit 5000, 1; 來驗(yàn)證結(jié)果的id列是否和上面圖中的SQL結(jié)果一致。而對(duì)于select * from t_null_indexlimit 5000, 1; 該SQL由于走全表掃描并且默認(rèn)按照主鍵id升序排序，兩條SQL執(zhí)行的排序規(guī)則不一致，所以就會(huì)導(dǎo)致兩條結(jié)果的id列值不一致。

通過上圖，我們可以看出，掃描 idx_key1 索引列的SQL 和 顯示 order by key1,id 的SQL的執(zhí)行結(jié)果id列值是相同的。

那如果顯示對(duì) select * from t_null_index order by key1 limit 5000, 1; 結(jié)果會(huì)如何？

通過執(zhí)行結(jié)果，我們可以看出掃描 idx_key1 索引列的SQL 和 顯示 order by key1 的SQL的執(zhí)行結(jié)果id列值還是不相同的。

根據(jù)前面我們的分析，我們知道 select id from t_null_index limit 5000, 1;會(huì)通過掃描二級(jí)索引 idx_key1 來獲得結(jié)果集，并且結(jié)果集是按照 key1 和 id 這兩個(gè)鍵進(jìn)行排序的。而對(duì)于 select * from t_null_index order by key1 limit 5000, 1; 這條SQL執(zhí)行會(huì)直接全表掃描后再在引擎層根據(jù)key1進(jìn)行文件堆排序。這種排序的結(jié)果集存在根據(jù)key1升序的情況下，相同的key1，id列可能是亂序，所以就會(huì)出現(xiàn)圖中兩個(gè)值不相等的情況。

附：select * from t_null_index order by key1 limit 5000, 1; 執(zhí)行計(jì)劃

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.00 sec)

附：select * from t_null_index order by key1 limit 4990, 20; 出現(xiàn)亂序的情況

到此這篇關(guān)于MySQL Limit執(zhí)行過程分析探索的文章就介紹到這了,更多相關(guān)MySQL Limit內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論