百万数据分页查询优化方案
分页问题
分页列表查询是项目中的热点需求，这种需求的特点是：字段多、数据量大、访问频繁、使用率高的特点，这个功能是给用户最直观的展示系统的信息，针对于多、大、频、热这几个特点，会引申出一个问题：列表展示的数据可能是来自于不同的数据维度、需要关联N张表查询得到，那么，如何让用户更快、更准的获取到需要的数据，便成了程序员在编码时需要考虑到并且需要解决的问题，因为随时间推移，线上系统不乏几百万数据的表。
准备
CREATE TABLE `test_temp` (
`test_id` int NOT NULL AUTO_INCREMENT,
`field_1` varchar(20) DEFAULT NULL,
`field_2` varchar(20) DEFAULT NULL,
`field_3` bigint DEFAULT NULL,
`create_date` date DEFAULT NULL,
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;写一个存储过程生成200万测试数据:
drop procedure if exists test_insert;
create procedure test_insert(n int)
begin
declare v int default 0;
SET AUTOCOMMIT = 0;
while v < n
do
insert into test_temp(field_1, field_2, field_3, create_date)
values (concat('testing',v),
substring(md5(rand()), 1, 10),
floor(rand() * 1000000),
adddate('1970-01-01', rand(v) * 10000));
set v = v + 1;
end while;
SET AUTOCOMMIT = 1;
end;插入数据:
call test_insert(2000000);测试数据方案来自于:
https://blog.csdn.net/weixin_38924697/article/details/119978916
现象
带分页的语句，我们一般使用Limit实现，那么基于以上数据我们写一个SQL:
SELECT * from test_temp LIMIT 1,10执行时间:0.004秒
此时，我们模拟分页数据特别往后的情况，分页数据越往后越慢。
SELECT SQL_NO_CACHE * from test_temp LIMIT 19999900,10执行时间：1.348秒，速度慢了二十余倍。
实际的业务场景下，可能会关联N张表，而且线上服务器的压力会比单机开发环境更重，因此实际接口响应时间会更长。
问题原因
- 回表:查询频率高的字段会建立索引，但是并不是所有的查询字段都会在索引上，无法命中索引的字段则需要回表，回表是IO操作，因为需要根据索引查找到数据行后，再根据数据行的主键或唯一索引去聚簇索引中查找具体的数据行。因此在执行回表操作时需要从磁盘读取数据，而磁盘IO是相对较慢的操作。
- 查询规则：limit 19999900，10并不是从第19999900行开始扫描，使用explain查看执行计划：
解决方案
当查询的字段都被索引覆盖时，可无需回表，那么我们可以先查询出主键id，再根据主键id拼接id条件或者作为临时表JOIN原表就可以了。因为主键id是最快的索引：聚簇索引，通过id就能快速找到指定行。
查询方案一：
先查询出id，再根据id直接查询数据。
查询出id
SELECT test_id from test_temp LIMIT 1999995,5
执行计划:
再根据这些id为条件查询数据
SELECT * from test_temp WHERE test_id in (2952993,2952995,2952996,2952997)；
优化后的查询时间为:0.002秒。
执行计划
查询方案二:
使用子查询作为临时表Inner join主表查询：
SELECT * from (SELECT test_id from test_temp LIMIT 1999995,5) as temp INNER JOIN test_temp tt on tt.test_id = temp.test_id
整体查询时间:0.245秒
执行计划:
性能对比
在不考虑MySQL执行时校验权限、建立连接的情况下，两种方案整体查询时间在0.25秒左右，相比于整体查询的1.34秒具有较大优势。方案一相较于方案二的SQL语句更加简洁易懂，而方案二只需与MySL建立一次查询即可。
其他优化项
请参考:
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2023-07-26，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
暂无评论
推荐阅读
相关推荐
MySQL不走索引的情况分析
更多 >
腾讯云开发者
