如何定位慢查询SQL以及优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 如何定位慢查询SQL以及优化

慢查询日志记录慢SQL


定位慢SQL可以通过慢查询日志来查看慢SQL,默认的情况下,MySQL数据库不开启慢查询日志(slow query log),需要手动把它打开

SET GLOBAL slow_query_log = ‘ON’;

ca5a435941b34b31ac9d3667f4b1796f.png

查看下慢查询日志配置

SHOW VARIABLES LIKE ‘slow_query_log%’


slow_query_log:表示慢查询开启的状态

slow_query_log_file:表示慢查询日志存放的位置


查看超过多少时间,才记录到慢查询日志

SHOW VARIABLES LIKE ‘long_query_time’


注意: 这样配置是临时的如果需要永久修改需要去配置文件(/etc/my.cnf


explain查看分析SQL执行计划


通过慢查询日志定位出查询效率较低的SQL,可以使用explain查看SQL的执行计划363d74d85a864fafb0c7dc973c65b02f.png

id

1. id 值相同时,被视为一组从上向下执行。

2. 如果是子查询,id 值会递增,id 值越高,优先级越高

3. id为NULL最后执行

select_type


1. simple: 简单的select, 查询中不包含子查询或者 union。例如: select name from student where id= 100

2. primary: 子查询中最外层查询, 查询中若包含任何复杂的子部分, 最外层的select被标记为primary

3. derived:在 from 的列表中包含的子查询被标记成 derived(派生表)。例如: explain select id from (select id,name from student) student1 where name= ‘name100’

4. subquery:在 select 或 where 列表中包含了子查询,则子查询被标记成 subquery。例如: explain select id from student where score = (select score from student where

name=‘name100’);

5. union: union中的第二个或后面的select语句. 例如: EXPLAIN select id from student where id<12691055 UNION all select id from student where id<12691060;


table


显示这一步所访问数据库中表名称. 有时候不是真实的表名, 可能是简称


partitions


该字段看table所在的分区, 值为NULL表示表未被分区


possible_keys


可能会使用到的索引(ps.其实不太重要)


重点关注的字段

type


表示连接类型,查看索引执行情况的一个重要指标 以下性能从好到坏依次:system > const > eq_ref > ref >

ref_or_null > index_merge > unique_subquery > index_subquery > range >

index > ALL

system:这种类型要求数据库表中只有一条数据,是const类型的一个特例,一般情况下是不会出现的

const:通过一次索引就能找到数据,一般用于主键或唯一索引作为条件,这类扫描效率极高,速度非常快

eq_ref:常用于主键或唯一索引扫描,一般指使用主键的关联查询 ref : 常用于非主键和唯一索引扫描

ref_or_null:这种连接类型类似于ref,区别在于MySQL会额外搜索包含NULL值的行

index_merge:使用了索引合并优化方法,查询使用了两个以上的索引

unique_subquery:类似于eq_ref,条件用了in子查询

index_subquery:区别于unique_subquery,用于非唯一索引,可以返回重复值

range:常用于范围查询,比如:between … and 或 In 等操作 index:全索引扫描 ALL:全表扫描


key


实际使用到的索引


key_len


实际使用到的索引的长度


rows


该列表示MySQL估算找到我们所需的记录,需要读取的行数


filtered


该列是一个百分比,是满足条件的记录数量与我们查询了多少记录数量的比值


extra


该字段包含有关MySQL如何解析查询的其他信息,它一般会出现这几个值:

● Usingfilesort:表示按文件排序,一般是在指定的排序和索引排序不一致的情况才会出现,一般见于order by语句

● Using index:表示是否用了覆盖索引

● Using temporary: 表示是否使用了临时表,性能特别差,需要重点优化,一般多见于groupby语句,或者union语句

● Using where : 表示使用了where条件过滤

● Using index condition:MySQL5.6之后新增的索引下推,在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据减少回表的数据


profile分析执行耗时


explain只是看到SQL预估的执行计划,如果要了解SQL真正的执行线程状态及消耗的时间,需要使用profiling,开启profiling参数后,后续执行的SQL语句都会记录其资源开销,包括IO,上下文切换,CPU,内存等等,可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化c556a8c892074bc08daf7cac58ba8d5f.png


Optimizer Trace分析详情


profile只能查看到SQL的执行耗时,但无法看到SQL真正执行的过程信息,不知道MySQL优化器是如何选择执行计划,这时候,可以使用Optimizer
Trace,它可以跟踪执行语句的解析优化执行的全过程

f1315716958849f9a15717c99201a7f0.png

三个阶段分为对应:准备阶段、分析阶段、执行阶段


确定问题采用响应措施


● 多数慢SQL都跟索引有关,比如不加索引,索引不生效、不合理等,这时候,可以优化索引

● 还可以优化SQL语句,比如一些in元素过多问题(分批),深分页问题(基于上一次数据过滤等),进行时间分段查询

● SQL没办法很好优化,可以改用ES的方式,或者数仓

● 如果单表数据量过大导致慢查询,可以考虑分库分表

● 如果数据库在刷脏页导致慢查询,考虑是否可以优化一些参数

● 如果存量数据量太大,考虑是否可以让部分数据归档


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
109 2
|
7天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
16天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
60 10
|
15天前
|
SQL 存储 缓存
SQL Server 数据太多如何优化
11种优化方案供你参考,优化 SQL Server 数据库性能得从多个方面着手,包括硬件配置、数据库结构、查询优化、索引管理、分区分表、并行处理等。通过合理的索引、查询优化、数据分区等技术,可以在数据量增大时保持较好的性能。同时,定期进行数据库维护和清理,保证数据库高效运行。
|
29天前
|
SQL 资源调度 分布式计算
如何让SQL跑快一点?(优化指南)
这篇文章主要探讨了如何在阿里云MaxCompute(原ODPS)平台上对SQL任务进行优化,特别是针对大数据处理和分析场景下的性能优化。
|
1月前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
1月前
|
SQL 关系型数据库 PostgreSQL
遇到SQL 子查询性能很差?其实可以这样优化
遇到SQL 子查询性能很差?其实可以这样优化
87 2
|
1月前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
1月前
|
SQL 数据处理 数据库
SQL语句优化与查询结果优化:提升数据库性能的实战技巧
在数据库管理和应用中,SQL语句的编写和查询结果的优化是提升数据库性能的关键环节
|
1月前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化策略
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生不利影响

热门文章

最新文章