背景
基本上只要是做後臺開發，都會接觸到分頁這個需求或者功能吧。基本上大家都是會用MySQL的LIMIT來處理，而且我現在負責的專案也是這樣寫的。但是一旦資料量起來了，其實LIMIT的效率會極其的低，這一篇文章就來講一下LIMIT子句優化的。

LIMIT優化
很多業務場景都需要用到分頁這個功能，基本上都是用LIMIT來實現。

建表並且插入200萬條資料：

新建一張t5表

CREATE TABLE t5 (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
text varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY ix_name (name),
KEY ix_test (text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建立儲存過程插入200萬資料

CREATE PROCEDURE t5_insert_200w()
BEGIN
DECLARE i INT;
SET i=1000000;
WHILE i<=3000000 DO
INSERT INTO t5(name,text) VALUES(‘god-jiang666’,concat(‘text’, i));
SET i=i+1;
END WHILE;
END;

呼叫儲存過程插入200萬資料

call t5_insert_200w();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
在翻頁比較少的情況下，LIMIT是不會出現任何效能上的問題的。

但是如果使用者需要查到最後面的頁數呢？

通常情況下，我們要保證所有的頁面可以正常跳轉，因為不會使用order by xxx desc這樣的倒序SQL來查詢後面的頁數，而是採用正序順序來做分頁查詢：

select * from t5 order by text limit 100000, 10;
1
在這裡插入圖片描述

採用這種SQL查詢分頁的話，從200萬資料中取出這10行資料的代價是非常大的，需要先排序查出前1000010條記錄，然後拋棄前面1000000條。我的macbook pro跑出來花了5.578秒。

接下來我們來看一下，上面這條SQL語句的執行計劃：

explain select * from t5 order by text limit 1000000, 10;
1
在這裡插入圖片描述

從執行計劃可以看出，在大分頁的情況下，MySQL沒有走索引掃描，即使text欄位我已經加上了索引。

這是為什麼呢？

回到MySQL索引（二）如何設計索引中有提及到，MySQL資料庫的查詢優化器是採用了基於代價的，而查詢代價的估算是基於CPU代價和IO代價。

如果MySQL在查詢代價估算中，認為全表掃描方式比走索引掃描的方式效率更高的話，就會放棄索引，直接全表掃描。