如何查询千万级数据

原创已于 2022-02-21 11:10:33 修改 · 2.8k 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#java

于 2022-02-21 11:07:18 首次发布

面试题专栏收录该内容

27 篇文章

订阅专栏

本文探讨了在大规模数据和偏移量大的情况下，如何通过子查询优化偏移量查询，以及利用id限定方式优化数据量大的分页性能。通过实验对比，展示了不同策略对查询速度的影响和适用场景。

文章目录

测试演示
优化操作(重点)

数据了大一般都有做分库分表的处理

测试演示

演示中表有3148000条数据

普通分页

SELECT * FROM table LIMIT 参数1 , 参数2

第一个参数指定第一个返回记录行的偏移量
第二个参数指定返回记录行的最大数目
测试性能效果

SELECT * FROM `user_operation_log` LIMIT 10000, 10

查询3次时间分别为：
59 ms
49 ms
50 ms
这样看起来速度还行，不过是本地数据库，速度自然快点。

相同偏移量，不同数据量

SELECT * FROM `user_operation_log` LIMIT 10000, 10
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 1000
SELECT * FROM `user_operation_log` LIMIT 10000, 10000
SELECT * FROM `user_operation_log` LIMIT 10000, 100000
SELECT * FROM `user_operation_log` LIMIT 10000, 1000000

查询时间如下：
数据量越大，花费时间越长

相同数据量，不同偏移量

SELECT * FROM `user_operation_log` LIMIT 100, 100
SELECT * FROM `user_operation_log` LIMIT 1000, 100
SELECT * FROM `user_operation_log` LIMIT 10000, 100
SELECT * FROM `user_operation_log` LIMIT 100000, 100
SELECT * FROM `user_operation_log` LIMIT 1000000, 100

在这里插入图片描述

从上面结果可以得出结束：偏移量越大，花费时间越长

优化操作(重点)

既然我们经过上面一番的折腾，也得出了结论，针对上面两个问题：偏移大、数据量大，我们分别着手优化

优化偏移量大问题

采用子查询方式

我们可以先定位偏移位置的 id，然后再查询数据

SELECT * FROM `user_operation_log` LIMIT 1000000, 10

SELECT id FROM `user_operation_log` LIMIT 1000000, 1

SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10

查询结果如下：
第一条花费的时间最大，第三条比第一条稍微好点
子查询使用索引速度更快
缺点：只适用于id递增的情况
id非递增的情况可以使用以下写法，但这种缺点是分页查询只能放在子查询里面

SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t)

采用 id 限定方式

这种方法要求更高些，id必须是连续递增，而且还得计算id的范围，然后使用 between，sql如下

SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100

SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100

查询结果如下：

优化数据量大问题

返回结果的数据量也会直接影响速度

SELECT * FROM `user_operation_log` LIMIT 1, 1000000

SELECT id FROM `user_operation_log` LIMIT 1, 1000000

SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 1, 1000000

查询结果如下：
从结果可以看出减少不需要的列，查询效率也可以得到明显提升
第一条和第三条查询速度差不多，这时候你肯定会吐槽，那我还写那么多字段干啥呢，直接 * 不就完事了
注意本人的 MySQL 服务器和客户端是在_同一台机器_上，所以查询数据相差不多，有条件的同学可以测测客户端与MySQL分开
用 "SELECT * " 数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。
增大网络开销，* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。特别是MySQL和应用程序不在同一台机器，这种开销非常明显。