07.26 MySQL 高效分頁方案

稍微深度用過 MySQL limit 分頁的同學都知道，Limit 會將offset代表的記錄數都查找出來，然後捨棄。不僅如此，越往後查找，速度越慢。

表基本情況

首先交代一個背景，某張表的數據量八百多萬，自增主鍵：

mysql> SELECT count(*) from cnnvd_vul_product;

+----------+

| count(*) |

+----------+

| 8996061 |

+----------+

1 row in set

mysql> show index from cnnvd_vul_product;

+-------------------+------------+--------------+--------------+--------------+-------------+------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality | Index_type |

+-------------------+------------+--------------+--------------+--------------+-------------+----------+

| cnnvd_vul_product | 0 | PRIMARY | 1 | cvpt_id | 8996061 | BTREE |

+-------------------+------------+--------------+--------------+--------------+-------------+------------+

4 rows in set

分頁查詢

offset

最常見的就是 limit offset,num。offset 可以是 rows * pageNo 的積。

SELECT t.* FROM cnnvd_vul_product t ORDER BY t.cvpt_id asc LIMIT 8000000,10

join

offset 查詢方式會隨著 offset 的增大而減慢，使用 inner join 改良：

SELECT t1.* from cnnvd_vul_product t1 INNER JOIN(SELECT t.cvpt_id from cnnvd_vul_product t ORDER BY t.cvpt_id limit 8000000,10)a ON t1.cvpt_id = a.cvpt_id

where

join 要查詢兩次，查詢速度同樣會隨著子查詢結果數目的增多和減慢，使用 where：

SELECT t.cvpt_id,t.cvpt_vluname FROM cnnvd_vul_product t WHERE t.cvpt_id > 8000000 ORDER BY t.cvpt_id LIMIT 10

開啟 profiling

mysql> show profiles;

Empty set, 1 warning (0.00 sec)

mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

執行 SQL，並查看結果：

mysql> mysql> show profiles;

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 1 | 3.67786100 | SELECT t.* FROM cnnvd_vul_product t ORDER BY t.cvpt_id asc LIMIT 8000000,10 |

| 2 | 1.31961500 | SELECT t1.* from cnnvd_vul_product t1 INNER JOIN(SELECT t.cvpt_id from cnnvd_vul_product t ORDER BY t.cvpt_id limit 8000000,10)a ON t1.cvpt_id = a.cvpt_id |

| 3 | 0.00038500 | SELECT t.* FROM cnnvd_vul_product t WHERE t.cvpt_id > 8000000 ORDER BY t.cvpt_id asc LIMIT 10 |

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

3 rows in set, 1 warning (0.00 sec)

大家一起交流學習，大家有什麼想諮詢的可以關注，謝謝。