几种数据查询深分页的简单优化方案

一、普通分页查询存在的问题

如果数据表中的数据量非常大，那么使用类似如下SQL语句分页查询数据，就会导致性能低下。

select * from test limit 10000000, 1000;

性能低下的原因是上述SQL会查询数据表中的10001000条数据，最终舍弃前面的10000000条数据，返回1000条数据。这种大数据量下查询大页码数据的现象，也叫作深分页问题。

二、深分页常见优化方案

常见的深分页方案包括：范围查询、子查询、延迟关联和覆盖索引。

2.1 范围查询

如果数据表中存在连续的自增ID，则按照ID的范围查询可以优化一定的性能，例如，下面的SQL。

select * from test where id > 10000000 and id <=  10001000 order by id asc;

也可以记录上一次已经查询到的当前最大ID值，查询大于此ID值的N条数据作为返回结果，如下所示。

select * from test where id > 10000000 limit 1000;

这种方案需要ID连续递增，并且不能解决跳页的问题。

PS：跳页问题的场景是：不连续分页，从第1页直接翻到第2页以外的其他页码，例如从第1页直接翻到第10页等等。

2.2 子查询

通过子查询的方式，可以先查询limit 1这条数据对应的主键值，随后再根据这个主键值作为查询条件查询分页数据，例如下面的查询SQL。

select * from test where id >= (select id from test limit 10000000, 1) limit 1000;

子查询的过程会产生一种新的临时表，会影响到查询性能，并且这种方案只能使用在ID正序的场景。

2.3 延迟关联查询

延迟关联查询的方案中，会使用INNER JOIN，并且包含子查询的方式查询数据。

select t1.* from test t1 INNER JOIN (select id from test limit 10000000, 1000) t2 on (t1.id = t2.id);

这里，还可以使用逗号连接子查询。

select t1.* from test t1, (select id from test limit 10000000, 1000) t2 where t1.id = t2.id;

2.4 覆盖索引

覆盖索引说的是要查询的字段尽量都在索引树中，尽量不要再回表查询数据，假设只需要查询test表中的id, username，则在id和username上添加索引，使得查询的所有字段数据都在索引上。

select id, username from test limit 10000000, 1000;

这种方案需要保证要查询的字段都被添加了索引，但是，如果当前分页查询的数据占整张表的大部分数据时，索引可能失效，造成回表查询。