MySQL慢查询优化(线上案例调优)(上)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 《基础》

一.复杂的深分页问题优化

背景

有一个article表,用于存储文章的基本信息的,有文章id,作者id等一些属性,有一个content表,主要用于存储文章的内容,主键是article_id,需求需要将一些满足条件的作者发布的文章导入到另外一个库,所以我同事就在项目中先查询出了符合条件的作者id,然后开启了多个线程,每个线程每次取一个作者id,执行查询和导入工作。

查询出作者id是1111,名下的所有文章信息,文章内容相关的信息的SQL如下:

SELECT
        a.*, c.*
FROM
        article a
LEFT JOIN content c ON a.id = c.article_id
WHERE
        a.author_id = 1111
AND a.create_time < '2020-04-29 00:00:00'
LIMIT 210000,100点击复制代码复制出错复制成功

因为查询的这个数据库是机械硬盘的,在offset查询到20万时,查询时间已经特别长了,运维同事那边直接收到报警,说这个库已经IO阻塞了,已经多次进行主从切换了,我们就去navicat里面试着执行了一下这个语句,也是一直在等待, 然后对数据库执行show proceesslist 命令查看了一下,发现每个查询都是处于Writing to net的状态,没办法只能先把导入的项目暂时下线,然后执行kill命令将当前的查询都杀死进程(因为只是客户端Stop的话,MySQL服务端会继续查询)。

然后我们开始分析这条命令执行慢的原因:

是否是联合索引的问题

当前是索引情况如下:

article表的主键是id,author_id是一个普通索引
content表的主键是article_id点击复制代码复制出错复制成功

所以认为当前是执行流程是先去article表的普通索引author_id里面找到1111的所有文章id,然后根据这些文章id去article表的聚集索引中找到所有的文章,然后拿每个文章id去content表中找文章内容等信息,然后判断create_time是否满足要求,进行过滤,最终找到offset为20000后的100条数据。

所以我们就将article的author_id索引改成了联合索引(author_id,create_time),这样联合索引(author_id,create_time)中的B+树就是先安装author_id排序,再按照create_time排序,这样一开始在联合(author_id,create_time)查询出来的文章id就是满足create_time < '2020-04-29 00

00'条件的,后面就不用进行过滤了,就不会就是符合就不用对create_time过滤。

流程确实是这个流程,但是去查询时,如果limit还是210000, 100时,还是查不出数据,几分钟都没有数据,一直到navica提示超时,使用Explain看的话,确实命中索引了,如果将offset调小,调成6000, 100,勉强可以查出数据,但是需要46s,所以瓶颈不在这里。

真实原因如下:

先看关于深分页的两个查询,id是主键,val是普通索引

直接查询法

select * from test where val=4 limit 300000,5;点击复制代码复制出错复制成功

先查主键再join

select * from test a 
inner join
(select id from test where val=4 limit 300000,5) as b 
on a.id=b.id;点击复制代码复制出错复制成功

这两个查询的结果都是查询出offset是30000后的5条数据,区别在于第一个查询需要先去普通索引val中查询出300005个id,然后去聚集索引下读取300005个数据页,然后抛弃前面的300000个结果,只返回最后5个结果,过程中会产生了大量的随机I/O。第二个查询一开始在普通索引val下就只会读取后5个id,然后去聚集索引下读取5个数据页。

同理我们业务中那条查询其实是更加复杂的情况,因为我们业务的那条SQL不仅会读取article表中的210100条结果,而且会每条结果去content表中查询文章相关内容,而这张表有几个TEXT类型的字段,我们使用show table status命令查看表相关的信息发现

Name Engine Row_format Rows Avg_Row_length
article InnoDB Compact 2682682 266
content InnoDB Compact 2824768 16847

发现两个表的数据量都是200多万的量级,article表的行平均长度是266,content表的平均长度是16847,简单来说是当 InnoDB 使用 Compact 或者 Redundant 格式存储极长的 VARCHAR 或者 BLOB 这类大对象时,我们并不会直接将所有的内容都存放在数据页节点中,而是将行数据中的前 768 个字节存储在数据页中,后面会通过偏移量指向溢出页。

(详细了解可以看看这篇文章深度好文带你读懂MySQL和InnoDB

这样再从content表里面查询连续的100行数据时,读取每行数据时,还需要去读溢出页的数据,这样就需要大量随机IO,因为机械硬盘的硬件特性,随机IO会比顺序IO慢很多。所以我们后来又进行了测试,

只是从article表里面查询limit 200000,100的数据,发现即便存在深分页的问题,查询时间只是0.5s,因为article表的平均列长度是266,所有数据都存在数据页节点中,不存在页溢出,所以都是顺序IO,所以比较快。

//查询时间0.51s
SELECT a.* FROM article a  
WHERE a.author_id = 1111  
AND a.create_time < '2020-04-29 00:00:00' 
LIMIT 200100, 100点击复制代码复制出错复制成功

相反的,我们直接先找出100个article_id去content表里面查询数据,发现比较慢,第一次查询时需要3s左右(也就是这些id的文章内容相关的信息都没有过,没有缓存的情况),第二次查询时因为这些溢出页数据已经加载到buffer pool,所以大概0.04s。

SELECT SQL_NO_CACHE c.* 
FROM article_content c 
WHERE c.article_id in(100个article_id)点击复制代码复制出错复制成功

解决方案

所以针对这个问题的解决方案主要有两种:

先查出主键id再inner join

非连续查询的情况下,也就是我们在查第100页的数据时,不一定查了第99页,也就是允许跳页查询的情况,那么就是使用先查主键再join这种方法对我们的业务SQL进行改写成下面这样,下查询出210000, 100时主键id,作为临时表temp_table,将article表与temp_table表进行inner join,查询出中文章相关的信息,并且去left Join content表查询文章内容相关的信息。 第一次查询大概1.11s,后面每次查询大概0.15s

SELECT
        a.*, c.*
FROM article a
INNER JOIN(
        SELECT  id FROM article a
        WHERE   a.author_id = 1111
        AND a.create_time < '2020-04-29 00:00:00'
        LIMIT 210000 ,
        100
) as temp_table ON a.id = temp_table.id
LEFT JOIN content c ON a.id = c.article_id点击复制代码复制出错复制成功

优化结果

优化前,offset达到20万的量级时,查询时间过长,一直到超时。

优化后,offset达到20万的量级时,查询时间为1.11s。

利用范围查询条件来限制取出的数据

这种方法的大致思路如下,假设要查询test_table中offset为10000的后100条数据,假设我们事先已知第10000条数据的id,值为min_id_value

select * from test_table where id > min_id_value order by id limit 0, 100,就是即利用条件id > min_id_value在扫描索引是跳过10000条记录,然后取100条数据即可,这种处理方式的offset值便成为0了,但此种方式有限制,必须知道offset对应id,然后作为min_id_value,增加id > min_id_value的条件来进行过滤,如果是用于分页查找的话,也就是必须知道上一页的最大的id,所以只能一页一页得查,不能跳页,但是因为我们的业务需求就是每次100条数据,进行分批导数据,所以我们这种场景是可以使用。针对这种方法,我们的业务SQL改写如下:

//先查出最大和最小的id
SELECT min(a.id) as min_id , max(a.id) as max_id 
FROM article a 
WHERE a.author_id = 1111  
AND a.create_time < '2020-04-29 00:00:00' 
//然后每次循环查找
while(min_id<max_id) {
                SELECT a.*, c.* FROM article a LEFT JOIN content c ON a.id = c.article_id  WHERE a.author_id = 1111  AND a.id > min_id LIMIT 100
                //这100条数据导入完毕后,将100条数据数据中最大的id赋值给min_id,以便导入下100条数据
}点击复制代码复制出错复制成功

优化结果

优化前,offset达到20万的量级时,查询时间过长,一直到超时。

优化后,offset达到20万的量级时,由于知道第20万条数据的id,查询时间为0.34s。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
21天前
|
SQL 关系型数据库 MySQL
案例剖析,MySQL共享锁引发的死锁问题!
案例剖析,MySQL共享锁引发的死锁问题!
|
4天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
29 9
|
4天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
21 5
|
2天前
|
缓存 关系型数据库 MySQL
MySQL执行计划选择策略:揭秘查询优化的艺术
【10月更文挑战第15天】 在数据库性能优化中,选择最优的执行计划是提升查询效率的关键。MySQL作为一个强大的关系型数据库管理系统,提供了复杂的查询优化器来生成执行计划。本文将深入探讨如何选择合适的执行计划,以及为什么某些计划更优。
13 2
|
4天前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
14 3
|
7天前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
9天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
15 1
|
9天前
|
缓存 关系型数据库 MySQL
一文彻底弄懂MySQL优化之深度分页
【10月更文挑战第24天】本文深入探讨了 MySQL 深度分页的原理、常见问题及优化策略。首先解释了深度分页的概念及其带来的性能和资源问题。接着介绍了基于偏移量(OFFSET)和限制(LIMIT)以及基于游标的分页方法,并分析了它们的优缺点。最后,提出了多种优化策略,包括合理创建索引、优化查询语句和使用数据缓存,帮助提升分页查询的性能和系统稳定性。
|
12天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万数据量的优化实录
【10月更文挑战第6天】 在现代互联网应用中,随着用户量的增加和业务逻辑的复杂化,数据量级迅速增长,这对后端数据库系统提出了严峻的挑战。尤其是当数据量达到百万级别时,传统的数据库解决方案往往会遇到性能瓶颈。本文将分享一次使用MySQL与Redis协同优化大规模数据统计的实战经验。
55 3
|
12天前
|
NoSQL 关系型数据库 BI
记录一次MySQL+Redis实现优化百万数据统计的方式
【10月更文挑战第13天】 在处理百万级数据的统计时,传统的单体数据库往往力不从心,这时结合使用MySQL和Redis可以显著提升性能。以下是一次实际优化案例的详细记录。
66 1