MySQL 百萬級數據，怎麼做分頁查詢？

作者：大神養成記
來源：www.cnblogs.com/geningchao/p/6649907.html

方法 1: 直接使用數據庫提供的 SQL 語句

• 適應場景: 適用於數據量較少的情況 (元組百 / 千級)

• 原因 / 缺點: 全表掃描, 速度會很慢 且 有的數據庫結果集返回不穩定 (如某次返回 1,2,3, 另外的一次返回 2,1,3). Limit 限制的是從結果集的 M 位置處取出 N 條輸出, 其餘拋棄.

方法 2: 建立主鍵或唯一索引, 利用索引 (假設每頁 10 條)

• 語句樣式: MySQL 中, 可用如下方法: SELECT * FROM 表名稱 WHERE id_pk > (pageNum*10) LIMIT M

• 適應場景: 適用於數據量多的情況 (元組數上萬)

• 原因: 索引掃描, 速度會很快. 有朋友提出: 因爲數據查詢出來並不是按照 pk_id 排序的，所以會有漏掉數據的情況，只能方法 3

方法 3: 基於索引再排序

• 語句樣式: MySQL 中, 可用如下方法: SELECT * FROM 表名稱 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M

• 適應場景: 適用於數據量多的情況 (元組數上萬). 最好 ORDER BY 後的列對象是主鍵或唯一所以, 使得 ORDERBY 操作能利用索引被消除但結果集是穩定的 (穩定的含義, 參見方法 1)

• 原因: 索引掃描, 速度會很快. 但 MySQL 的排序操作, 只有 ASC 沒有 DESC(DESC 是假的, 未來會做真正的 DESC, 期待...).

方法 4: 基於索引使用 prepare

第一個問號表示 pageNum，第二個？表示每頁元組數

• 語句樣式: MySQL 中, 可用如下方法: PREPARE stmt_name FROM SELECT * FROM 表名稱 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M

• 適應場景: 大數據量

• 原因: 索引掃描, 速度會很快. prepare 語句又比一般的查詢語句快一點。

方法 5: 利用 MySQL 支持 ORDER 操作可以利用索引快速定位部分元組, 避免全表掃描

比如: 讀第 1000 到 1019 行元組 (pk 是主鍵 / 唯一鍵).

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

方法 6: 利用 "子查詢 / 連接 + 索引" 快速定位元組的位置, 然後再讀取元組.

比如 (id 是主鍵 / 唯一鍵, 藍色字體時變量)

利用子查詢示例:

SELECT * FROM your_table WHERE id <= 
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc 
LIMIT $pagesize

利用連接示例:

SELECT * FROM your_table AS t1 
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

mysql 大數據量使用 limit 分頁，隨着頁碼的增大，查詢效率越低下。

測試實驗

1.  直接用 limit start, count 分頁語句， 也是我程序中用的方法：

select * from product limit start, count

當起始頁較小時，查詢沒有性能問題，我們分別看下從 10， 100， 1000， 10000 開始分頁的執行時間（每頁取 20 條）。

如下：

select * from product limit 10, 20   0.016秒
select * from product limit 100, 20   0.016秒
select * from product limit 1000, 20   0.047秒
select * from product limit 10000, 20   0.094秒

我們已經看出隨着起始記錄的增加，時間也隨着增大， 這說明分頁語句 limit 跟起始頁碼是有很大關係的，那麼我們把起始記錄改爲 40w 看下（也就是記錄的一般左右）

select * from product limit 400000, 20   3.229秒

再看我們取最後一頁記錄的時間

select * from product limit 866613, 20   37.44秒

像這種分頁最大的頁碼頁顯然這種時間是無法忍受的。推薦閱讀：MySQL 數據庫開發的 36 條軍規。

從中我們也能總結出兩件事情：

1. limit 語句的查詢時間與起始記錄的位置成正比

2. mysql 的 limit 語句是很方便，但是對記錄很多的表並不適合直接使用。

2.  對 limit 分頁問題的性能優化方法

利用表的覆蓋索引來加速分頁查詢

我們都知道，利用了索引查詢的語句中如果只包含了那個索引列（覆蓋索引），那麼這種情況會查詢很快。

因爲利用索引查找有優化算法，且數據就在查詢索引上面，不用再去找相關的數據地址了，這樣節省了很多時間。另外 Mysql 中也有相關的索引緩存，在併發高的時候利用緩存就效果更好了。

在我們的例子中，我們知道 id 字段是主鍵，自然就包含了默認的主鍵索引。現在讓我們看看利用覆蓋索引的查詢效果如何。

這次我們之間查詢最後一頁的數據（利用覆蓋索引，只包含 id 列），如下：

select id from product limit 866613, 20 0.2秒

相對於查詢了所有列的 37.44 秒，提升了大概 100 多倍的速度

那麼如果我們也要查詢所有列，有兩種方法，一種是 id>= 的形式，另一種就是利用 join，看下實際情況：

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

查詢時間爲 0.2 秒！

另一種寫法

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

查詢時間也很短！

3.  複合索引優化方法

MySql 性能到底能有多高？MySql 這個數據庫絕對是適合 dba 級的高手去玩的，一般做一點 1 萬篇新聞的小型系統怎麼寫都可以，用 xx 框架可以實現快速開發。可是數據量到了 10 萬，百萬至千萬，他的性能還能那麼高嗎？一點小小的失誤，可能造成整個系統的改寫，甚至更本系統無法正常運行！好了，不那麼多廢話了。

用事實說話，看例子：

數據表 collect (id, title ,info ,vtype) 就這 4 個字段，其中 title 用定長，info 用 text, id 是逐漸，vtype 是 tinyint，vtype 是索引。這是一個基本的新聞系統的簡單模型。現在往裏面填充數據，填充 10 萬篇新聞。最後 collect 爲 10 萬條記錄，數據庫表佔用硬 1.6G。

OK , 看下面這條 sql 語句：

select id,title from collect limit 1000,10;

很快；基本上 0.01 秒就 OK，再看下面的

select id,title from collect limit 90000,10;

從 9 萬條開始分頁，結果？

8-9 秒完成，my god 哪出問題了？其實要優化這條數據，網上找得到答案。看下面一條語句:

select id from collect order by id limit 90000,10;

很快，0.04 秒就 OK。爲什麼？因爲用了 id 主鍵做索引當然快。網上的改法是：

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

這就是用了 id 做索引的結果。可是問題複雜那麼一點點，就完了。看下面的語句

select id from collect where vtype=1 order by id limit 90000,10; 很慢，用了 8-9 秒！

到了這裏我相信很多人會和我一樣，有崩潰感覺！vtype 做了索引了啊？怎麼會慢呢？vtype 做了索引是不錯，你直接

select id from collect where vtype=1 limit 1000,10;

是很快的，基本上 0.05 秒，可是提高 90 倍，從 9 萬開始，那就是 0.05*90=4.5 秒的速度了。和測試結果 8-9 秒到了一個數量級。

從這裏開始有人提出了分表的思路，這個和 dis #cuz 論壇是一樣的思路。思路如下：

建一個索引表：t (id,title,vtype) 並設置成定長，然後做分頁，分頁出結果再到 collect 裏面去找 info 。是否可行呢？實驗下就知道了。

10 萬條記錄到 t(id,title,vtype) 裏，數據表大小 20M 左右。用

select id from t where vtype=1 order by id limit 90000,10;

很快了。基本上 0.1-0.2 秒可以跑完。爲什麼會這樣呢？我猜想是因爲 collect 數據太多，所以分頁要跑很長的路。limit 完全和數據表的大小有關的。其實這樣做還是全表掃描，只是因爲數據量小，只有 10 萬才快。OK， 來個瘋狂的實驗，加到 100 萬條，測試性能。加了 10 倍的數據，馬上 t 表就到了 200 多 M，而且是定長。還是剛纔的查詢語句，時間是 0.1-0.2 秒完成！分表性能沒問題？

錯！因爲我們的 limit 還是 9 萬，所以快。給個大的，90 萬開始

select id from t where vtype=1 order by id limit 900000,10;

看看結果，時間是 1-2 秒！why ?

分表了時間還是這麼長，非常之鬱悶！有人說定長會提高 limit 的性能，開始我也以爲，因爲一條記錄的長度是固定的，mysql 應該可以算出 90 萬的位置纔對啊？可是我們高估了 mysql 的智能，他不是商務數據庫，事實證明定長和非定長對 limit 影響不大？怪不得有人說 discuz 到了 100 萬條記錄就會很慢，我相信這是真的，這個和數據庫設計有關！

難道 MySQL 無法突破 100 萬的限制嗎？？？到了 100 萬的分頁就真的到了極限？

答案是：NO 爲什麼突破不了 100 萬是因爲不會設計 mysql 造成的。下面介紹非分表法，來個瘋狂的測試！一張表搞定 100 萬記錄，並且 10G 數據庫，如何快速分頁！

好了，我們的測試又回到 collect 表，開始測試結論是：

30 萬數據，用分表法可行，超過 30 萬他的速度會慢道你無法忍受！當然如果用分表 + 我這種方法，那是絕對完美的。但是用了我這種方法後，不用分表也可以完美解決！

答案就是：複合索引！有一次設計 mysql 索引的時候，無意中發現索引名字可以任取，可以選擇幾個字段進來，這有什麼用呢？

開始的

select id from collect order by id limit 90000,10;

這麼快就是因爲走了索引，可是如果加了 where 就不走索引了。抱着試試看的想法加了 search(vtype,id) 這樣的索引。

然後測試

select id from collect where vtype=1 limit 90000,10;

非常快！0.04 秒完成！

再測試:

select id ,title from collect where vtype=1 limit 90000,10;

非常遺憾，8-9 秒，沒走 search 索引！

再測試：search(id,vtype)，還是 select id 這個語句，也非常遺憾，0.5 秒。

綜上：如果對於有 where 條件，又想走索引用 limit 的，必須設計一個索引，將 where 放第一位，limit 用到的主鍵放第 2 位，而且只能 select 主鍵！

完美解決了分頁問題了。可以快速返回 id 就有希望優化 limit ， 按這樣的邏輯，百萬級的 limit 應該在 0.0x 秒就可以分完。看來 mysql 語句的優化和索引時非常重要的！

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/j7IAlEy4szzmoc8n2Pb0DQ