千万级表插入百万级数据_百万级数据，分页如何处理？-CSDN博客

本文链接：https://blog.csdn.net/weixin_36242382/article/details/113367133

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

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

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

goods user g_u

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

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

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

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

查询的时间会大大减少：