分頁查詢的最佳化方式：1、子查詢最佳化，可透過把分頁的SQL語句改寫成子查詢的方法來獲得效能上的提升。 2.id限定優化，可以根據查詢的頁數和查詢的記錄數計算出查詢的id的範圍，然後根據「id between and」語句來查詢。 3.基於索引再排序進行最佳化，透過索引去找相關的資料位址，避免全表掃描。 4.延遲關聯優化，可以使用JOIN，先在索引列上完成分頁操作，然後再回表取得所需的列。
本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。
分頁查詢的效率在資料量大的時候特別重要，影響到前端回應和使用者體驗。
分頁查詢的最佳化方式
#1、使用子查詢最佳化
#這種方式先定位偏移位置的id，然後往後查詢，這種方式適用於id 遞增的情況。
子查詢最佳化原理：https://www.jianshu.com/p/0768ebc4e28d
select * from sbtest1 where k=504878 limit 100000,5;的查詢過程：
首先會查詢到索引葉子節點數據，然後根據葉子節點上的主鍵值去叢集索引上查詢所需的全部欄位值。像下圖左邊這樣，需要查詢100005次索引節點，查詢100005次聚集索引的數據，最後再將結果過濾掉前100000條，取出最後5條。 MySQL耗費了大量隨機I/O在查詢叢集索引的資料上，而有100000次隨機I/O查詢到的資料是不會出現在結果集當中的。
既然一開始是利用索引的，為什麼不先沿著索引葉子節點查詢到最後需要的5個節點，然後再去聚簇索引中查詢實際數據。這樣只需要5次隨機I/O，類似上圖右邊的過程。這就是子查詢最佳化，這種方式先定位偏移位置的id，然後往後查詢，這種方式適用於id遞增的情況。如下圖：
mysql> select * from sbtest1 where k=5020952 limit 50,1; mysql> select id from sbtest1 where k=5020952 limit 50,1; mysql> select * from sbtest1 where k=5020952 and id>=( select id from sbtest1 where k=5020952 limit 50,1) limit 10; mysql> select * from sbtest1 where k=5020952 limit 50,10;
在子查詢優化中，謂詞中k是否有索引，對查詢效率有很大影響，上述語句沒有使用索引走全表掃描需要24.2s，走了索引後只需要0.67s。
mysql> explain select * from sbtest1 where k=5020952 and id>=( select id from sbtest1 where k=5020952 limit 50,1) limit 10; +----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+ | 1 | PRIMARY | sbtest1 | NULL | index_merge | PRIMARY,c1 | c1,PRIMARY | 8,4 | NULL | 19 | 100.00 | Using intersect(c1,PRIMARY); Using where | | 2 | SUBQUERY | sbtest1 | NULL | ref | c1 | c1 | 4 | const | 88 | 100.00 | Using index | +----+-------------+---------+------------+-------------+---------------+------------+---------+-------+------+----------+------------------------------------------+ 2 rows in set, 1 warning (0.11 sec)
但這種最佳化方法也有限制：
這種寫法，要求主鍵ID必須是連續的
Where子句不允許再加入其他條件
2、使用id限定優化
這種方式假設資料表的id是連續遞增的，則我們根據查詢的頁數和查詢的記錄數可以算出查詢的id的範圍，可以使用id between and 來查詢。
假設資料庫中表格的id是連續遞增的，則可以根據查詢的頁數和查詢的記錄數計算出查詢的id的範圍，然後根據id between and語句來查詢。 id的範圍可以透過分頁公式計算得到，比如說目前頁面大小為m，當前頁數為no1，則頁面最大值為max=(no1 1)m-1，最小值為min=no1m，SQL語句可以表示為id between min and max。
select * from sbtest1 where id between 1000000 and 1000100 limit 100;
這種查詢方式能夠大幅最佳化查詢速度，基本上能夠在幾十毫秒之內完成。限制是需要明確知道id的情況，但一般在分頁查詢的業務表中，都會添加基本的id字段，這為分頁查詢帶來很多便利。上述SQL還有另一種寫法：
select * from sbtest1 where id >= 1000001 limit 100;
可以看到執行時間上的差異：
mysql> show profiles; +----------+------------+--------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------------------------------------------------------------------------------+ | 6 | 0.00085500 | select * from sbtest1 where id between 1000000 and 1000100 limit 100 | | 7 | 0.12927975 | select * from sbtest1 where id >= 1000001 limit 100 | +----------+------------+--------------------------------------------------------------------------------------------------------------+
也可以使用in的方式來進行查詢，這種方式常用在多表關聯的時候進行查詢，使用其他表格查詢的id集合，來進行查詢：
select * from sbtest1 where id in (select id from sbtest2 where k=504878) limit 100;
使用in查詢的方式要注意某些mysql版本不支援在in子句中使用limit。
3、基於索引再排序來優化
基於索引再排序是利用索引查詢中有最佳化演算法，透過索引再去找相關的資料位址，避免全表掃描，這樣節省了很多時間。另外Mysql中也有相關的索引緩存，在並發高的時候利用快取效果會更好。在MySQL中可以使用如下語句：
SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
這種方法適用於資料量多的情況(元組數上萬)，最好ORDER BY後面的列物件是主鍵或唯一索引，使得ORDER BY操作能利用索引被消除但結果集是穩定的。例如下面兩個語句：
mysql> show profiles; +----------+------------+--------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------------------------------------------------------------------------------+ | 8 | 3.30585150 | select * from sbtest1 limit 1000000,10 | | 9 | 1.03224725 | select * from sbtest1 order by id limit 1000000,10 | +----------+------------+--------------------------------------------------------------------------------------------------------------+
對索引欄位id使用order by語句後，效能有了明顯的提升。
4、使用延遲關聯來優化
和上述的子查詢做法類似，我們可以使用JOIN，先在索引列上完成分頁操作，然後再回表獲取所需的列。
select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;
从实验中可以得出，在采用JOIN改写后，上面的两个局限性都已经解除了，而且SQL的执行效率也没有损失。
5、记录上次查询结束的位置
和上面使用的方法都不同，记录上次结束位置优化思路是使用某种变量记录上一次数据的位置，下次分页时直接从这个变量的位置开始扫描，从而避免MySQL扫描大量的数据再抛弃的操作。
select * from t5 where id>=1000000 limit 10;
6、使用临时表优化
使用临时存储的表来记录分页的id然后进行in查询
这种方式已经不属于查询优化，这儿附带提一下。
对于使用 id 限定优化中的问题，需要 id 是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。
【相关推荐：mysql视频教程】
以上是mysql分頁查詢怎麼最佳化的詳細內容。更多資訊請關注PHP中文網其他相關文章！
