sql优化之大数据量分页查询（mysql）

当需要从数据库查询的表有上万条记录的时候，一次性查询所有结果会变得很慢，特别是随着数据量的增加特别明显，这时就需要使用分页查询。对于数据库分页查询，也有很多种方法和优化的点。

谈优化前的准备工作

为了对下面列举的一些优化进行测试，需要使用已有的一张表作为实际例子。

表名：order_history。

描述：某个业务的订单历史表。

主要字段：unsigned int id，tinyint(4) int type。

字段情况：该表一共37个字段，不包含text等大型数据，最大为varchar(500)，id字段为索引，且为递增。

数据量：5百万+。

MySQL版本：5.7.16。

线下找一张百万级的测试表可不容易，如果需要自己测试的话，可以写shell脚本什么的插入数据进行测试。

一次查询出所有记录

不使用分页查询的情况下，一次查询出表中的所有记录，也就是全表扫描：

执行三次查询的时间分别是8023ms、8122ms和8329ms。

实际的项目当然是不可能进行全表扫描的一次性查询出所有记录的做法，这样会因为数据的响应、传输和装载过慢而影响页面渲染的性能，严重影响用户体验。

一般的分页查询

一般的分页查询使用简单的limit子句就可以实现。limit子句的声明如下：

limit子句可以被用于指定select语句返回的记录数，以下是使用limit子句的注意事项：

1.第一个参数指定第一个返回记录行的偏移量，注意从0开始。

2.第二个参数指定返回记录行的最大数目。

3.如果只给定一个参数，这个参数表示返回的最大记录行数目。

4.如果第二个参数值为-1，那么就表示检索从某一个偏移量到记录集的结束所有的记录行。

5.初始记录行的偏移量是0，而不是1。

上面这条语句会从orders_history表中查询offset:1000开始之后的10条记录，也就是第1001条记录到第1010条记录的数据（1001= 1000001 limit 100;

当然了，也可以使用in的方式来进行查询，这种方式经常用在多表关联的情况下，使用其他表查询的id集合来进行查询：

但是使用这种in查询方式的时候要注意的是，某些MySQL版本并不支持在in子句中使用limit子句。

使用临时表优化大数据量分页查询

对于使用id限定优化中的问题，需要id是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行in查询。

这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

关于数据表id的扩展说明

一般情况下，在数据库中建立表的时候，会强制为每一张表添加id递增字段，这样方便查询。

而如果像是订单库等数据量非常庞大，一般会进行分库分表。这个时候就不建议使用数据表的id作为唯一标识，而应该使用分布式的高并发唯一id生成器来生成，并在数据表中使用另外的字段来存储这个唯一标识。

查询的方法是先使用范围查询定位id（或者索引），然后再使用索引进行定位数据，就能够提高好几倍查询速度。即先select id，然后再select *。

"从前车马很慢，书信很远，一生只够爱一人。"