前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >百万数据分页查询优化方案

百万数据分页查询优化方案

作者头像
关忆北.
发布2023-10-11 09:41:13
2530
发布2023-10-11 09:41:13
举报
文章被收录于专栏:关忆北.关忆北.
分页问题

分页列表查询是项目中的热点需求,这种需求的特点是:字段多、数据量大、访问频繁、使用率高的特点,这个功能是给用户最直观的展示系统的信息,针对于多、大、频、热这几个特点,会引申出一个问题:列表展示的数据可能是来自于不同的数据维度、需要关联N张表查询得到,那么,如何让用户更快、更准的获取到需要的数据,便成了程序员在编码时需要考虑到并且需要解决的问题,因为随时间推移,线上系统不乏几百万数据的表。

准备
代码语言:javascript
复制
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万测试数据:

代码语言:javascript
复制
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;

插入数据:

代码语言:javascript
复制
call test_insert(2000000);

测试数据方案来自于:

https://blog.csdn.net/weixin_38924697/article/details/119978916

现象

带分页的语句,我们一般使用Limit实现,那么基于以上数据我们写一个SQL:

代码语言:javascript
复制
SELECT * from test_temp LIMIT 1,10

执行时间:0.004秒

img
img
img
img

此时,我们模拟分页数据特别往后的情况,分页数据越往后越慢。

代码语言:javascript
复制
SELECT SQL_NO_CACHE * from test_temp LIMIT 19999900,10

执行时间:1.348秒,速度慢了二十余倍。

实际的业务场景下,可能会关联N张表,而且线上服务器的压力会比单机开发环境更重,因此实际接口响应时间会更长。

问题原因
  1. 回表:查询频率高的字段会建立索引,但是并不是所有的查询字段都会在索引上,无法命中索引的字段则需要回表,回表是IO操作,因为需要根据索引查找到数据行后,再根据数据行的主键或唯一索引去聚簇索引中查找具体的数据行。因此在执行回表操作时需要从磁盘读取数据,而磁盘IO是相对较慢的操作。
  2. 查询规则:limit 19999900,10并不是从第19999900行开始扫描,使用explain查看执行计划:
img
img
解决方案

当查询的字段都被索引覆盖时,可无需回表,那么我们可以先查询出主键id,再根据主键id拼接id条件或者作为临时表JOIN原表就可以了。因为主键id是最快的索引:聚簇索引,通过id就能快速找到指定行。

查询方案一:

先查询出id,再根据id直接查询数据。

查询出id
代码语言:javascript
复制
SELECT test_id from test_temp LIMIT 1999995,5
img
img

执行计划:

img
img
再根据这些id为条件查询数据
代码语言:javascript
复制
SELECT * from test_temp WHERE test_id in (2952993,2952995,2952996,2952997);
img
img

优化后的查询时间为:0.002秒。

执行计划

img
img
查询方案二:

使用子查询作为临时表Inner join主表查询:

代码语言:javascript
复制
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
img
img

整体查询时间:0.245秒

执行计划:

img
img
性能对比

在不考虑MySQL执行时校验权限、建立连接的情况下,两种方案整体查询时间在0.25秒左右,相比于整体查询的1.34秒具有较大优势。方案一相较于方案二的SQL语句更加简洁易懂,而方案二只需与MySL建立一次查询即可。

其他优化项

请参考:

百万查询注意点

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2023-07-26,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 分页问题
  • 准备
  • 现象
  • 问题原因
  • 解决方案
    • 查询方案一:
    • 查询出id
    • 再根据这些id为条件查询数据
      • 查询方案二:
      • 性能对比
      • 其他优化项
      相关产品与服务
      云数据库 MySQL
      腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
      领券
      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档