| SQL | 執行時間 |
|---|---|
| select * from user where sex = 1 limit 100, 10; | OK, Time: 0.005000s |
| select * from user where sex = 1 limit 1000, 10; | OK, Time: 0.007000s |
| select * from user where sex = 1 limit 10000, 10; | OK, Time: 0.016000s |
| select * from user where sex = 1 limit 100000, 10; | OK, Time: 0.169000s |
| select * from user where sex = 1 limit 1000000, 10; | OK, Time: 5.892000s |
| select * from user where sex = 1 limit 10000000, 10; | OK, Time: 33.465000s |
可以看到，limit 的偏移量越大，執行時間越長。
原因分析
首先來分析一下這句 SQL 執行的過程，就拿上面表格中的第一行來舉例。
由于 sex 列是索引列，MySQL會走 sex 這棵索引樹，命中 sex=1 的數據。
然后又由于非聚簇索引中存儲的是主鍵 id 的值，且查詢語句要求查詢所有列，所以這里會發生一個回表的情況，在命中 sex 索引樹中值為1的數據后，拿著它葉子節點上的值也就是主鍵 id 的值去主鍵索引樹上查詢這一行其他列（name、sex）的值，最后返回到結果集中，這樣第一行數據就查詢成功了。
最后這句 SQL 要求limit 100, 10，也就是查詢第101到110個數據，但是 MySQL 會查詢前110行，然后將前100行拋棄，最后結果集中就只剩下了第101到110行，執行結束。
小結一下，在上述的執行過程中，造成 limit 大偏移量執行時間變久的原因有：
綜合上述兩個原因，MySQL 花費了大量時間在回表上，而其中a次回表的結果又不會出現在結果集中，這才導致查詢時間變得越來越長。
優化方案
覆蓋索引
既然無效的回表是導致查詢變慢的主要原因，那么優化方案就主要從減少回表次數方面入手，假設在limit a, b中我們首先得到了a+1到a+b條數據的id，然后再進行回表獲取其他列數據，那么就減少了a次回表操作，速度肯定會快上不少。
這里就涉及到覆蓋索引了，所謂的覆蓋索引就是從非主聚簇索引中就能查到的想要數據，而不需要通過回表從主鍵索引中查詢其他列，能夠顯著提升性能。
基于這樣的思路，優化方案就是先查詢得到主鍵id，然后再根據主鍵id查詢其他列數據，優化后的 SQL 以及執行時間如下表。
| 優化后的 SQL | 執行時間 |
|---|---|
| select * from user a join (select id from user where sex = 1 limit 100, 10) b on a.id=b.id; | OK, Time: 0.000000s |
| select * from user a join (select id from user where sex = 1 limit 1000, 10) b on a.id=b.id; | OK, Time: 0.00000s |
| select * from user a join (select id from user where sex = 1 limit 10000, 10) b on a.id=b.id; | OK, Time: 0.002000s |
| select * from user a join (select id from user where sex = 1 limit 100000, 10) b on a.id=b.id; | OK, Time: 0.015000s |
| select * from user a join (select id from user where sex = 1 limit 1000000, 10) b on a.id=b.id; | OK, Time: 0.151000s |
| select * from user a join (select id from user where sex = 1 limit 10000000, 10) b on a.id=b.id; | OK, Time: 1.161000s |
果然，執行效率得到了顯著提升。
條件過濾
當然還有一種有缺陷的方法是基于排序做條件過濾。
比如像上面的示例 user 表，我要使用 limit 分頁得到1000001到1000010條數據，可以這樣寫 SQL：
select * from user where sex = 1 and id > (select id from user where sex = 1 limit 1000000, 1) limit 10;
但是使用這樣的方式優化是有條件的：主鍵id必須是有序的。在有序的條件下，也可以使用比如創建時間等其他字段來代替主鍵id，但是前提是這個字段是建立了索引的。
總之，使用條件過濾的方式來優化 limit 是有諸多限制的，一般還是推薦使用覆蓋索引的方式來優化。
小結
主要分析了 limit 分頁大偏移量慢的原因，同時也提出了響應的優化方案，推薦使用覆蓋索引的方式來優化 limit 分頁大偏移執行時間久的問題。
希望能幫助到大家。
以上就是MySQL limit分頁大偏移量慢的原因及優化方案的詳細內容，更多關于MySQL limit 分頁的資料請關注腳本之家其它相關文章！
標簽：惠州 公主嶺 阿里 沈陽 天津 牡丹江 呼和浩特 合肥
巨人網絡通訊聲明：本文標題《MySQL limit分頁大偏移量慢的原因及優化方案》，本文關鍵詞 MySQL,limit,分頁,大,偏移量,；如發現本文內容存在版權問題，煩請提供相關信息告之我們，我們將及時溝通與處理。本站內容系統采集于網絡，涉及言論、版權與本站無關。
