写在前面
当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时需要使用分页查询。对于数据库分页查询,也有很多种方法和优化的点。下面简单说一下我知道的一些方法。
一般分页查询
一般分页查询采用limit page,pagesize 格式来操作
例如
需要查询user表中的第一页的数据,每一页数据有10个
SELECT * FROM New_info.user limit 1,10;
如果查询user表中第二页的数据,每一页有15个数据
SELECT * FROM New_info.user limit 2,15;
LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点:
- 第一个参数指定第一个返回记录行的偏移量
- 第二个参数指定返回记录行的最大数目
- 如果只给定一个参数:它表示返回最大的记录行数目
- 第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行
- 初始记录行的偏移量是 0(而不是 1)
弊端
随着查询偏移的增大,尤其查询偏移大于10万以后,查询时间急剧增加。
这种分页查询方式会从数据库第一条记录开始扫描,所以越往后,查询速度越慢,而且查询的数据越多,也会拖慢总查询速度。
使用子查询优化
这种方式先定位偏移位置的 id,然后往后查询,这种方式适用于 id 递增的情况。
适应场景: 适用于数据量多的情况(元组数上万)
SELECT * FROM New_info.user where id>10 limit 2,15;
如果id不是递增的,容易漏掉数据
基于索引再排序
适应场景:
适用于数据量多的情况(元祖数上万) 最好PRDER BY后的列对象是主键或者唯一
所以,使得ORDER BY操作能够利用索引被消除但结果集是稳定的
原因:
索引扫描,速度会很快,但是Mysql的排序操作,只有ASC没有DESC
代码示例
SELECT * FROM New_info.user WHERE id>(2*10) ORDER BY id ASC LIMIT 10;
基于索引使用prepare(第一个问号表示pageNum,第二个?表示每页元组数)
适应场景:
大数据量
原因:
索引扫描,速度会很快,prepare语句比一般的查询语句块一点
代码示例:
PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (?* ?) ORDER BY id_pk ASC LIMIT M
利用MySQL支持ORDER操作可以利用索引快速定位部分元组,避免全表扫描
比如: 读第1000到1019行元组(pk是主键/唯一键).
SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20
利用”子查询/连接+索引”快速定位元组的位置,然后再读取元组. 道理同方法5
例如(id是主键/唯一键,蓝色字体时变量):
利用子查询示例:
SELECT * FROM your_table WHERE id <=
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc LIMIT $pagesize
利用连接示例:
SELECT * FROM your_table AS t1
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;
mysql大数据量使用limit分页,随着页码的增大,查询效率越低下。