稍微深度用过 MySQL limit 分页的同学都知道,Limit 会将offset代表的记录数都查找出来,然后舍弃。不仅如此,越往后查找,速度越慢。

表基本情况


  首先交代一个背景,某张表的数据量八百多万,自增主键:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 的积。
1
SELECT t.* FROM cnnvd_vul_product t  ORDER BY t.cvpt_id asc LIMIT 8000000,10

join


  offset 查询方式会随着 offset 的增大而减慢,使用 inner join 改良:
1
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:
1
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

1
2
3
4
5
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,并查看结果:

1
2
3
4
5
6
7
8
9
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)