mysql分頁查詢如何優化

這篇文章主要介紹了mysql分頁查詢如何優化的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇mysql分頁查詢如何優化文章都會有所收獲，下面我們一起來看看吧。

分頁查詢的優化方式：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分頁查詢如何優化”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。