在实际开发中分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。DBA的同事给出的解决方法就是加“索引/组合索引”,例如在name,create_time,status等字段上加组合索引,这样根据这些条件查询的时候能够有效的利用索引,性能迅速的就提高了。
以上做法对 select * from table limit 0,10 这个没有问题,但是当 limit 1000000,10 的时候数据读取就很慢了。我只想要10条数据但是每次查询都扫描了100多万行,在高频访问下堵的死死的。那么有什么办法解决这个问题呢?
前提:数据库主键ID为自增
正常分页SQL语句为:
select id,name,content,create_time from users order by id asc limit 1000000,10
优化后SQL语句为:
select id,name,content,create_time from users where id > 1000000 order by id asc limit 10
看到这里大家应该明白了吧?进行查询的时候可以将上一页的最大值ID当成参数作为查询条件的,那么当次查询只需要扫描最大ID开始的20行数据即可。
那么有的同学会问如果设计的表主键不是自增又该怎么办?这里还有一种方法根据数据的入库时间,具体方法和上面雷同。
select id,name,content,create_time from users where id > '2020-01-01 12:29:23' order by id asc limit 10
这样查询的时间基本固定,并且也不会随着数据量的增长而发生变化。
如下图所示,小编准备了300w+的数据,这张表共有28个字段。
正常分页所需时长:
从200w开始获取10条数据共花费12s多。
根据入库时间进行分页:
这里的查询时间是上面SQL查询出来结果的create_time,可以看到花费了2s多。性能提升了约6倍!
备注:create_time 字段没有增加索引。
当一个数据库表过于庞大,LIMIT offset, length中的offset值过大,则SQL查询语句会非常缓慢,你需增加order by,并且order by字段需要建立索引。
如果使用子查询去优化LIMIT的话,则子查询必须是连续的,某种意义来讲,子查询不应该有where条件,where会过滤数据,使数据失去连续性。
如果你查询的记录比较大,并且数据传输量比较大,比如包含了text类型的field,则可以通过建立子查询。
SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);
如果limit语句的offset较大,你可以通过传递pk键值来减小offset = 0,这个主键最好是int类型并且auto_increment
SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;
这条语句,大意如下:
SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10; 如果limit的offset值过大,用户也会翻页疲劳,你可以设置一个offset最大的,超过了可以另行处理,一般连续翻页过大,用户体验很差,则应该提供更优的用户体验给用户。