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

这篇具有很好参考价值的文章主要介绍了MySQL-如何定位慢查询SQL以及优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

慢查询日志记录慢SQL

定位慢SQL可以通过慢查询日志来查看慢SQL,默认的情况下,MySQL数据库不开启慢查询日志(slow query log),需要手动把它打开
SET GLOBAL slow_query_log = ‘ON’;
MySQL-如何定位慢查询SQL以及优化

查看下慢查询日志配置
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的执行计划
MySQL-如何定位慢查询SQL以及优化

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的瓶颈再进一步进行优化

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

Optimizer Trace分析详情

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

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

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

确定问题采用响应措施

● 多数慢SQL都跟索引有关,比如不加索引,索引不生效、不合理等,这时候,可以优化索引
● 还可以优化SQL语句,比如一些in元素过多问题(分批),深分页问题(基于上一次数据过滤等),进行时间分段查询
● SQL没办法很好优化,可以改用ES的方式,或者数仓
● 如果单表数据量过大导致慢查询,可以考虑分库分表
● 如果数据库在刷脏页导致慢查询,考虑是否可以优化一些参数
● 如果存量数据量太大,考虑是否可以让部分数据归档文章来源地址https://www.toymoban.com/news/detail-472930.html

到了这里,关于MySQL-如何定位慢查询SQL以及优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【MYSQL高级】Mysql找出执行慢的SQL【慢查询日志使用与分析】

    慢查询的开启并捕获:开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,至少跑1天,看看生产的慢SQL情况,并将它抓取出来 explain + 慢SQL分析 show Profile。(比explain还要详细,可以查询SQL在MySQL数据库中的执行细节和生命周期情况) 运维经理 OR DBA,进行MySQL数据库服务

    2024年02月13日
    浏览(51)
  • Mysql找出执行慢的SQL【慢查询日志使用与分析】

    慢查询的开启并捕获:开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,至少跑1天,看看生产的慢SQL情况,并将它抓取出来 explain + 慢SQL分析 show Profile。(比explain还要详细,可以查询SQL在MySQL数据库中的执行细节和生命周期情况) 运维经理 OR DBA,进行MySQL数据库服务

    2024年02月11日
    浏览(58)
  • MySQL 联表查询重复数据并删除(子查询删除记录) SQL优化

    数据库表介绍: table_a :主表(小表,表数据不可重复) table_b :流水表(大表,记录审核流水数据) 注:两表表结构大致一致,流水表增加一个审核状态的字段 业务逻辑: 主表保存唯一数据,流水表记录审核流水数据,用于后续展示,并在审核成功后插入主表,在插入流

    2023年04月08日
    浏览(63)
  • 玩转MySQL数据库之SQL优化之慢查询

    本系列为:MySQL数据库详解,为千锋资深教学老师独家创作,致力于为大家讲解清晰MySQL数据库相关知识点,含有丰富的代码案例及讲解。如果感觉对大家有帮助的话,可以【关注】持续追更~ 文末有本文重点总结,技术类问题,也欢迎大家和我们沟通交流! 从今天开始本系列

    2024年02月06日
    浏览(91)
  • 如何针对慢查询的sql进行优化?

    优化慢查询的 SQL 可以从多个方面入手,以下是一些常用的优化方式和示例: 在表中添加合适的索引可以显著提升查询效率。可以通过 EXPLAIN 命令来查看查询计划,判断是否使用了索引,如果没有使用索引,就需要考虑添加索引。 示例: 如果查询计划中 type 列为 ALL,说明没

    2024年02月15日
    浏览(51)
  • MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点4:慢速查询日志(slow query log)

    慢速查询日志(slow query log) 慢速查询日志(slow query log)主要用于查找超过指定时间、执行时间很长的SQL。 默认情况下这个功能是无效的,所以要启用慢速查询日志需要设置slow_query_log 为1(ON)。 慢速查询日志(slow query log)相关参数 下面我们看看慢速查询日志(slow query l

    2023年04月22日
    浏览(70)
  • mysql查询当天、近一周、近一个月及近一年的数据以及各种报表查询sql

    以下是一些常见的MySQL查询语句,用于查询当天、近一周、近一个月和近一年的数据,以及一些常见的报表查询。 查询当天的数据: 查询近一周的数据: 查询近一个月的数据: 查询近一年的数据: 报表查询示例: 这些查询语句可以根据您的具体表结构和需求进行调整和修

    2024年02月02日
    浏览(44)
  • MySQL 如何优化慢查询?

    在日常开发中,我们往往会给表加各种索引,来提高 MySQL 的检索效率。 但我们有时会遇到明明给字段加了索引,并没有走索引的Case。 进而导致 MySQL 产生慢查询。 严重场景下,甚至出现主从延迟、数据库拖垮的极端事故。 本文梳理出索引失效的几种常见场景给大家参考。

    2024年02月05日
    浏览(44)
  • Mysql如何优化数据查询方案

    mysql做读写分离 读写分离是提高mysql并发的首选方案。 Mysql主从复制的原理 mysql的主从复制依赖于binlog,也就是记录mysql上的所有变化并以二进制的形式保存在磁盘上,复制的过程就是将binlog中的数据从主库传输到从库上。 主从复制过程详细分为3个阶段: 第一阶段:主库写

    2024年02月21日
    浏览(43)
  • 【JAVA面试】Mysql慢查询如何优化

    提示:文章先作为初版,等后续时间充足后,补充更深的内容 MySQL的慢查询是 指执行时间较长的SQL语句 ,可以根据执行时间超过预设阈值(如1秒)或返回的记录数超过预设阈值(如1000条)来进行定义。一般情况下**,慢查询是由于查询语句中使用了不恰当的索引、数据库结

    2024年02月03日
    浏览(41)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包