百万级数据，分页如何处理

最近遇到了这么一个情况，数据库里面的数据由于长期的堆积，导致数据量不断的上升，而后台的系统每次进行分页查询的时候，效率都会降低很多。后来查看了一下之后，发现此时的分页原理主要是采用了传统的物理分页 limit n,m 的方式。

为了方便演示，我特意创建了以下几张表进行实例演练：

表分别是商品表，用户表，用户选购商品记录表：

goods user g_u

三张表的关系比较简单，user的id和goods里面的id合并生成关联数据，存储在了g_u里面。三张数据库表的设计如下所示：

CREATE TABLE `goods` (
  `id` int(11) NOT NULL,
  `name` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `price` decimal(6,1) NOT NULL,
  `des` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `age` tinyint(3) NOT NULL,
  `sex` tinyint(1) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `g_u` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `g_id` int(11) NOT NULL COMMENT '商品id',
  `u_id` int(11) NOT NULL COMMENT '用户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2800001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

这个模拟的应用场景非常简单，用户和商品之间的关系维持在了一对多的关联中。为了方便进行后续的测试，我用jmeter批量创建了1900000条测试数据，模拟一次百万级的数据查询场景。

相应的数据脚本也已经存在百度云中了，需要的同学可以前往下载：
地址：

链接: https://pan.baidu.com/s/1BfddJ8MBtnpeiV84gNmClA  

提取码: 4kmp

假设现在需求里面有这样的一个业务场景，需要我们对购买记录表里面的数据进行分页查询，那么对于常规的分页查询操作，常人会想到的方式可能是通过下述的语句：

SELECT * from g_u as gu ORDER BY id limit 1850000,100

测试一下发现，查询的时间为：

当我们搜索的数据越靠后边的时候，搜索的速度就会越低下，因此这个时候，适当的创建索引就显得比较重要了。

首先我们来做一次explain的sql检测，检测结果为如下所示：

 由于我们查询的时候，使用的是根据主键索引id进行排序，因此查询的时候key一项为PRIMARY。

 SELECT * FROM g_u WHERE id >=(SELECT id FROM g_u LIMIT 1850000,1) ORDER BY id  LIMIT 100

此时查询有了一些许的提升，但是依旧查询缓慢

 通过explain执行计划分析结果可见：

子查询用到了索引，外部查询用到了where的辅助索引

这个时候我们不妨可以试下通过利用主键id来提升我们的查询效率：

SELECT * FROM g_u as gu WHERE gu.id>($firstId+$pageSize*$pageSize)  limit 100

查询的时间一下子大大缩短了许多：

通过explain分析一下该sql：

 

这里面，sql在运行的时候借助了主键索引的帮助，因此效率大大提升了。

但是这个时候，可能你会有这么一个疑惑。如果说数据的索引不是连续的该如何处理分页时候每页数据的完整性和一致性？

这里不妨可以试试另外的一种思路，通过建立一张第三方的表g_u_index表，将原本乱序的id存储在g_u_index中，在g_u_index一表中，我们可以通过该表有序的g_u_index.id来对应原本相应的无序的g_u.id。建表的sql语句如下所示：

CREATE TABLE `g_u_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `index` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_id_index` (`id`,`index`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1900024 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ps: 可以为id和index两者建立一套复合索引，提升查询的效率。

这里我们需要保证一点就是，g_u表中插入的数据顺序需要和g_u_index表中插入的顺序是一致的。然后查询分页指定的index时候可以这么来查：

 SELECT g_u_index.index FROM g_u_index  WHERE id=($firstId+$pageSize*$pageSize)  limit 1

通过执行explain分析后，结果变成如下所示：

 查询时间为：0.001s

 有了第三方表的帮助下，此时分页的sql优化可以调整为以下这种方式：

SELECT * FROM g_u as gu where gu.id>(
SELECT g_u_index.index FROM g_u_index  WHERE id=($firstId+$pageSize*$pageSize) limit 1
) limit 100

通过构建了第三方表之后，数据的查询时间一下子大大缩减了：

 查询的时候为了更加人性化，通常不需要显示这些无意义的id，需要的是商品名称和用户姓名，假设我们还是只采用最原始的无第三方表的方式进行查询的话，效率会比较底下：

SELECT gu.id,goods.`name`,`user`.username FROM g_u as gu ,goods ,`user` 
where goods.id=gu.g_id AND `user`.id=gu.u_id 
ORDER BY id limit 1500000,1000

结果：

 因此如果借助了第三方表查询的话，sql可以调整成下方这种类型：

SELECT goods.`name`,`user`.username FROM g_u as gu ,goods ,`user` 
where goods.id=gu.g_id AND `user`.id=gu.u_id 
and 
gu.id>=(
SELECT g_u_index.index FROM g_u_index  WHERE id=(9+1000*1900) limit 1
) limit 100

查询的时间会大大减少：

 通过explain执行计划分析之后，结果如下：

在实际的业务场景中，一张原来就有上百万数据的表要做出这样的id拆分，并且同步到第三方表的确实不太容易，这里推荐一种思路，可以借助阿里的中间件canal来实现对于数据库日志的订阅，然后自定义进行数据的同步操作。

对于sql的优化需要结合实际的业务需求来开展，总的来说，这部分还是需要有一定的实战演练才能变强。

常用的sql优化技巧小结：

1.数据量大的时候，应尽量避免全表扫描，应考虑在 where及 order by 涉及的列上建立索引，建索引可以大大加快数据的检索速度。

2.适当的使用Explain可以对sql进行相应的深入分析。

3.当只要一行数据时使用LIMIT 1。

4.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

5.不要在 where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

6.适当的时候采用覆盖索引可以提高查询的效率。