超过1000w条数据,MySQL查询越来越慢?四种方案帮你解决!

MySQL百万数据优化总结 一 为什么使用unionall因为union操作会对结果去重且排序,所以从速度来说,unionall会更胜一筹。 阅读详情

一般分页

在系统中需要进行分页操作时,我们通常会使用 LIMIT 加上偏移量的方式实现,语法格式如下。

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

在有对应索引的情况下,这种方式一般效率还不错。但它存在一个让人头疼的问题,在偏移量非常大的时候,也就是翻页到很靠后的页面时,查询速度会变得越来越慢。

我们来演示一下。

先创建一个订单表 t_order。

CREATE TABLE `t_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `order_no` varchar(32) NOT NULL COMMENT '订单号',
  `user_id` varchar(20) NOT NULL COMMENT '用户ID',
  `amount` decimal(18,2) NOT NULL COMMENT '订单金额',
  `order_status` tinyint(4) NOT NULL COMMENT '订单状态:0新建 1处理中 2成功 3失败',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order_no` (`order_no`) USING BTREE COMMENT '订单号唯一索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

往表中插入1100w 条数据。( t1 是一个有100条数据的表,这里我利用笛卡尔乘积的方式插入1100w条数据)

set @N=0;
INSERT INTO t_order(`order_no`,`user_id`,`amount`,`order_status`,`create_time`)
select
 CONCAT("APP", @N:=@N+1),
 CONCAT("USER_ID_", @N+1),
 @N%10000,
 @N%4,
 NOW()
 from t1 a, t1 b, t1 c, t1 d
LIMIT 11000000;

我们看下,如下这些查询花费的时间。

select * from t_order order by id limit 0, 10;  
select * from t_order order by id limit 10000, 10; 
select * from t_order order by id limit 100000, 10; 
select * from t_order order by id limit 1000000, 10;
select * from t_order order by id limit 10000000, 10;

执行时间如下:

-- 0.002
-- 0.045
-- 0.069
-- 0.517
-- 4.134

同样是只查询10条数据,最开始的时候查询花费 0.002s,而到最后,查询花费了 4.134s。

这是什么原因呢?

这是因为查询时 MySQL 并不是跳过 OFFSET 行,而是取 OFFSET+N 行,然后放弃前 OFFSET 行,最后返回 N 行,当 OFFSET 特别大的时候,效率就非常的低下。

拿 limit 10000, 10 这条语句来说明一下, MySQL在执行这条查询的时候,需要查询 10010 (10000 + 10) 条记录,然后只返回最后 10 条,并将前面的 10000 条记录抛弃,这样当翻页越靠后时,代价就变得越来越高。

知道问题所在了,那有什么办法可以优化,解决这个问题呢?

1优化一:记录位置,避免使用 OFFSET

首先获取第一页的结果:

select * from t_order limit 10;

假如上边返回的是 id 为1 ~ 10的记录,我们将 10 这个值记住,下一页查询就可以直接从 10 这个值开始。

select * from t_order where id > 10 limit 10;

这样做,无论翻页到多少页,性能都会很好:

select * from t_order limit 10;      
select * from t_order where id > 10000 limit 10; 
select * from t_order where id > 100000 limit 10; 
select * from t_order where id > 1000000 limit 10; 
select * from t_order where id > 10000000 limit 10;

执行时间如下:

-- 0.003
-- 0.005
-- 0.002
-- 0.002
-- 0.002

而如果我们当前记录的 id 值为 10000,我们想查上一页怎么办呢?返回去查一下即可:

select * from t_order where id <= 10000 order by id desc limit 10,10;

这种优化方式,可以实现上一页、下一页这种的分页。但如果想要实现跳转到指定页码的话,就需要保证 id 连续不中断,再通过计算找到准确的位置。

2优化二:计算边界值,转换为已知位置的查询

如果 id 连续不中断,我们就可以计算出每一页的边界值,让 MySQL 根据边界值进行范围扫描,查出数据。

select * from t_order where id between 0 and 10;
select * from t_order where id between 10000 and 10010;
select * from t_order where id between 100000 and 100010;
select * from t_order where id between 1000000 and 1000010;
select * from t_order where id between 10000000 and 10000010;

执行时间如下:

-- 0.001
-- 0.002
-- 0.002
-- 0.001
-- 0.001

3优化三:使用索引覆盖+子查询优化

先在索引树中找到开始位置的 id 值,再根据找到的 id 值查询行数据。

select * from t_order where id >= (select id from t_order order by id limit 0, 1) order by id limit 10;
select * from t_order where id >= (select id from t_order order by id limit 10000, 1) order by id limit 10;
select * from t_order where id >= (select id from t_order order by id limit 100000, 1) order by id limit 10;
select * from t_order where id >= (select id from t_order order by id limit 1000000, 1) order by id limit 10;
select * from t_order where id >= (select id from t_order order by id limit 10000000, 1) order by id limit 10;

执行时间如下:

-- 0.007
-- 0.009
-- 0.047
-- 0.332
-- 2.822

可以看到,这种优化方式也可以提升查询速度。这其实是利用了索引覆盖的如下好处:

  • 索引文件不包含行数据的所有信息,故其大小远小于数据文件,因此可以减少大量的IO操作。

  • 索引覆盖只需要扫描一次索引树,不需要回表扫描行数据,所以性能比回表查询要高。

4优化四:使用索引覆盖+连接查询优化

这种优化方式跟 优化三 原理一样。也是先在索引上进行分页查询,当找到 id 后,再统一通过 JOIN 关联查询得到最终需要的数据详情。

select * from t_order a Join (select id from t_order order by id limit 0, 10) b ON a.id = b.id;  
select * from t_order a Join (select id from t_order order by id limit 10000, 10) b ON a.id = b.id; 
select * from t_order a Join (select id from t_order order by id limit 100000, 10) b ON a.id = b.id; 
select * from t_order a Join (select id from t_order order by id limit 1000000, 10) b ON a.id = b.id;
select * from t_order a Join (select id from t_order order by id limit 10000000, 10) b ON a.id = b.id;

执行时间如下:

-- 0.001
-- 0.023
-- 0.028
-- 0.348
-- 2.955

来源:MySQL分页查询越来越慢?是时候该优化了!_子查询导致翻页慢-优快云博客

查询效率提升10倍!3种优化方案,帮你解决MySQL深分页问题 开发经常遇到分页查询的需求,但是当翻页过多的时候,就会产生深分页,导致查询效率急剧下降。有没有什么办法,能解决深分页的问题呢?本文总结了三种优化方案,查询效率直接提升10倍,一起学习一下。先创建一张用户表,只在create_time字段上加索引: 然后往用户表中插入100万条测试数据,这里可以使用存储过程: 2. 验证深分页问题 每页10条,当我们查询第一页的时候,速度很快: 在不到0.01秒内直接返回了,所以没显示出执行时间。当我们翻到第10000页的时候,查询效率急剧下降: 执行时间变 阅读详情

相关推荐

MySQL数据库优化10w条数据10s!哪100w数据呢?

多线程插入(单表) 问:为何对同一个表的插入多线程会比单线程快?同一时间对一个表的写操作不应该是独占的吗? 答:在数据里做插入操作的时候,整体时间的分配是这样的: 链接耗时 (30%) 发送query到服务器 (20%) 解析query (20%) 插入操作 (10% * 词条数目) 插入index (10% * Index的数目) 关闭链接 (10%) 从这里可以看出来,真正耗时的不是操作,而是...

weixin_45625815的博客 1138

每日一面系列之如何优化大表

大数据量的表的查询优化方案有很多,例如:创建索引、优化查询语句、缓存查询结果、提升硬件配置、数据归档和分离,以及数据分片技术(分库分表)等,而这些技术通常是一起配合使用,来共同解决大数据量表的查询速度慢的问题的,其中分库分表的实现最为复杂,所以需要根据自身业务的需要酌情使用。

mclongyi的专栏 390

MySQL 分库分表:用 ShardingSphere 解决 “单表千万级数据” 查询慢问题(附分布式事务方案)

摘要:针对订单表数据量达3000万行引发的性能问题,采用ShardingSphere实现水平分表方案。将单表拆分为30个小表后,查询耗时从5秒降至0.1秒,DDL操作时间从数小时缩短至秒级。文章深入分析了单表数据过大的三大痛点(索引失效、写入卡顿、DDL灾难),介绍了水平拆分与垂直拆分的适用场景,并通过实战案例详细演示了ShardingSphere-JDBC的分表实现流程,包括分片规则配置、分布式ID生成和事务处理等核心功能。最后总结了分库分表的演进路线和避坑指南,为大数据量表优化提供了完整解决方案。

lamehsl的专栏 995

mysql千万级数据量查询优化参考 —— 筑梦之路

Mysql查询性能优化要从三个方面考虑,库表结构优化、索引优化和查询优化。

筑梦之路 3766

【MySQL 数据量大怎么进行优化】

【MySQL 数据量大怎么进行优化】

学无止境 2672

SAP高级外币评估_FINS_FXV_S4 2022 OP

摘要:S/4HANA 2022 On-Premise中的高级外币评估(FINS_FXV)取代了经典外币估值(FAGL_FCV),提供更优化的财务结算功能。主要创新包括:增量发布逻辑、简化账户配置、行项目级估值可见性等。该功能支持IFRS9等会计准则,通过会计原则(Accounting Principle)进行配置,需完成财务报表版本定义、语义标记分配等必要设置。系统演示展示了从汇率维护(FB60/MIRO)、测试运行到最终过账(FB03)的完整流程,能自动计算并过账汇率差异。高级外币评估简化了期末结算操作,

yanzibing99的博客 1262

超过1000w条数据,MySQL查询越来越慢?四种方案帮你解决!

在系统中需要进行分页操作时,我们通常会使用 LIMIT 加上偏移量的方式实现,语法格式如下。在有对应索引的情况下,这种方式一般效率还不错。但它存在一个让人头疼的问题,在偏移量非常大的时候,也就是翻页到很靠后的页面时,查询速度会变得越来越慢。我们来演示一下。先创建一个订单表 t_order。往表中插入1100w 条数据。( t1 是一个有100条数据的表,这里我利用笛卡尔乘积的方式插入1100w条数据)我们看下,如下这些查询花费的时间。

y19910825的博客 1299

数字锁相环的原理与FPGA实现

数字锁相环是锁相环电路的全数字实现。锁相环电路能够实现对输入信号的相位进行跟踪,进而在噪声中提取纯净的有用信号。以上就是今天要讲的内容,本文简单地介绍了锁相环的原理,然后,在Vivado仿真平台上,通过verilog做仿真验证。最终仿真结果能够实现锁定,但锁定时间较长,表明参数仍可做调整。

weixin_45857378的博客 8514

MySQL为什么单表不要超过2000W条?

MySQL 的表数据是以页的形式存放的,页在磁盘中不一定是连续的。 页的空间是 16K, 并不是所有的空间都是用来存放数据的,会有一些固定的信息,如,页头,页尾,页码,校验码等等。

程序猿老白~的博客 1779

简单的服务器取证

记一次入门服务器取证——药品直销网,涵盖旧版本宝塔、静态IP的配置更改、代码的理解

m0_74559567的博客 3757

Cursor设置界面汉化保姆级教程:手把手教你修改JS文件实现中文界面(附避坑指南)

本文提供Cursor编辑器设置界面汉化的详细教程,从定位JS文件到安全修改实现中文界面,包含必备工具清单、文件路径指引和常见问题解决方案。通过手把手教学帮助用户轻松完成汉化,提升使用体验,特别适合不熟悉英文界面的开发者。

weixin_29007809的博客 1415

1000w条数据插入mysql如何设计?

批量插入减少网络和事务开销。事务控制减少频繁的事务提交。是导入海量数据的最快方式。禁用索引与自动提交避免索引更新和事务管理的开销。分批处理避免一次性插入过多数据导致性能瓶颈。调优MySQL参数提升系统的整体插入性能。

qq_74042166的博客 4796

【Python】多分类算法—Random Forest

【Python】多分类算法—Random Forest 本文将主要就Random Forest(随机森林)的多分类应用进行描述,当然也可运用于二分类中 文章目录【Python】多分类算法—Random Forest前言一、pandas是什么?二、使用步骤1.引入库2.读入数据总结 前言 本文主要引用的是sklearn 一、pandas是什么? 示例:pandas 是基于NumPy 的一种工具,该工具是为了解决数据分析任务而创建的。 二、使用步骤 1.引入库 代码如下(示例): import nump

weixin_43166884的博客 1万+

MySQL1000万条数据分页查询优化

这种问题的解决就是通过构建一个新的小表,以小表来join驱动大表;或者构建一个子查询语句,用exist,in做查询;或者注意索引下推和索引覆盖的使用。 概述 今天一个朋友问我一个问题,说MySQL很多数据查询怎么优化。我就直接说了个分页查询就行了啊,查询字段加索引。然而结果并没有我想象得那么简单,他分页查询直接把服务查崩了。原来他们数据量已经有好几百万了。你可能会问不就几百万数据吗,分页只查询10条怎么可能查询会很慢。后面我会模拟1000万条数据分页查询。 准备工作 1.新建一个测试库,我这里就叫

微信公众号:一颗向上的草莓 2602

【Python爬虫(1)】专栏开篇:夯实Python基础

要想熟练掌握 Python 爬虫技术,扎实的 Python 基础是必不可少的。这就好比建造高楼大厦,Python 基础就是那稳固的基石。只有打好了这个基础,我们才能在爬虫的学习道路上稳步前行,理解并运用各种爬虫技术和框架。

邓邓子的博客 2026

C3 模块与普通 Bottleneck 堆叠区别

维度普通 Bottleneck 堆叠C3 模块设计理念残差学习CSP + 特征分治信息路径单一路径双路径(主干 + bypass)融合方式相加(Add)拼接(Concat)+ 卷积计算效率较低高(通道减半)特征保留弱(多次非线性)强(直连保留原始信息)梯度传播单一主路径多样化、更稳定任务适配通用(分类为主)专为目标检测优化可扩展性有限极强(即插即用各种模块)实际性能基准线显著优于(尤其在小目标和效率方面)

浩瀚之水的专栏 108

MySQL分页用limit 为什么会越来越慢

学完之后,若是想验收效果如何,其实最好的方法就是可自己去总结一下。比如我就会在学习完一个东西之后自己去手绘一份xmind文件的知识梳理大纲脑图,这样也可方便后续的复习,且都是自己的理解,相信随便瞟几眼就能迅速过完整个知识,脑补回来。下方即为我手绘的MyBtis知识脑图,由于是xmind文件,不好上传,所以小编将其以图片形式导出来传在此处,细节方面不是特别清晰。但可给感兴趣的朋友提供完整的MyBtis知识脑图原件(包括上方的面试解析xmind文档)

weixin_42179304的博客 1182

SolidWorks 小车底盘 装配体

通过制作小车底盘了解solidworks装配体的部分知识

及时总结 2229
上一篇: EasyExcel 简单导入/导出 Controller Demo
下一篇: 学会 CompletableFuture:让你的代码免受阻塞之苦!
Java_2017_csdn
博客等级 码龄10年 329粉丝 49原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值