mysql慢查询优化方法_MySQL查询优化

mysql慢查询优化方法_MySQL查询优化定位低效SQL执行慢有两种情况:偶尔慢:DB在刷新脏页redolog写满了内存不够用,要从LRU链表中淘汰MySQL认为系统空闲的时候MySQL关闭时一直慢的原因:索引没有设计好、SQL语句没写好、MySQL选错了索引’mysql慢查询优化第一步:开启mysql慢查询日志,通过慢查询日志定位到执行较慢的SQL语句。第二步:利用explain关键字可以模拟优化器执行SQL查询语句,来分析SQL查询语句。第三步:通过查询的结果进行优化。优化方式(1)首先分

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

定位低效

SQL 执行慢有两种情况:

  • 偶尔慢:DB 在刷新脏页
    • redo log 写满了
    • 内存不够用,要从 LRU 链表中淘汰
    • MySQL 认为系统空闲的时候
    • MySQL 关闭时
  • 一直慢的原因:索引没有设计好、SQL 语句没写好、MySQL 选错了索引

’mysql慢查询优化
第一步:开启mysql慢查询日志,通过慢查询日志定位到执行较慢的SQL语句。
第二步:利用explain关键字可以模拟优化器执行SQL查询语句,来分析SQL查询语句。
第三步:通过查询的结果进行优化。

优化方式

(1)首先分析语句,看看是否包含了额外的数据,可能是查询了多余的行并抛弃掉了,也可能是加了结果中不需要的列,要对SQL语句进行分析和重写。
(2)分析优化器中索引的使用情况,要修改语句使得更可能的命中索引。比如使用组合索引的时候符合最左前缀匹配原则。not in,not like都不会走索引,可以优化为in.
(3)如果对语句的优化已经无法执行,可以考虑表中的数据是否太大,如果是的话可以横向和纵向的切表。

EXPLAIN

执行计划

通过 EXPLAIN 命令获取执行 SQL 语句的信息,包括在 SELECT 语句执行过程中如何连接和连接的顺序,执行计划在优化器优化完成后、执行器之前生成,然后执行器会调用存储引擎检索数据

查询 SQL 语句的执行计划:

EXPLAIN SELECT * FROM table_1 WHERE id = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oj8fOeWd-1637292608952)(https://gitee.com/seazean/images/raw/master/DB/MySQL-explain查询SQL语句的执行计划.png)]

字段 含义
id select查询的序列号,表示查询中执行select子句或操作表的顺序
select_type 表示 SELECT 的类型
table 输出结果集的表,显示这一步所访问数据库中表名称,有时不是真实的表名字,可能是简称
type 表示表的连接类型
possible_keys 表示查询时,可能使用的索引
key 表示实际使用的索引
key_len 索引字段的长度
ref 列与索引的比较,表示表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows 扫描出的行数,表示 MySQL 根据表统计信息及索引选用情况,估算的找到所需的记录扫描的行数
filtered 按表条件过滤的行百分比
extra 执行情况的说明和描述

MySQL 执行计划的局限:

  • 只是计划,不是执行 SQL 语句,可以随着底层优化器输入的更改而更改
  • EXPLAIN 不会告诉显示关于触发器、存储过程的信息对查询的影响情况
  • EXPLAIN 不考虑各种 Cache
  • EXPLAIN 不能显示 MySQL 在执行查询时的动态,因为执行计划在执行查询之前生成
  • EXPALIN 部分统计信息是估算的,并非精确值
  • EXPALIN 只能解释 SELECT 操作,其他操作要重写为 SELECT 后查看执行计划
  • EXPLAIN PLAN 显示的是在解释语句时数据库将如何运行 SQL 语句,由于执行环境和 EXPLAIN PLAN 环境的不同,此计划可能与 SQL 语句实际的执行计划不同

环境准备:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M7M1AyNV-1637292608955)(https://gitee.com/seazean/images/raw/master/DB/MySQL-执行计划环境准备.png)]


id

SQL 执行的顺序的标识,SQL 从大到小的执行

  • id 相同时,执行顺序由上至下

    EXPLAIN SELECT * FROM t_role r, t_user u, user_role ur WHERE r.id = ur.role_id AND u.id = ur.user_id ;
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KHmaiveK-1637292608956)(https://gitee.com/seazean/images/raw/master/DB/MySQL-explain之id相同.png)]

  • id 不同时,id 值越大优先级越高,越先被执行

    EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))
    

    mysql慢查询优化方法_MySQL查询优化

  • id 有相同也有不同时,id 相同的可以认为是一组,从上往下顺序执行;在所有的组中,id 的值越大的组,优先级越高,越先执行

    EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ; 
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PG8Bw4qL-1637292608959)(https://gitee.com/seazean/images/raw/master/DB/MySQL-explain之id相同和不同.png)]


select

表示查询中每个 select 子句的类型(简单 OR 复杂)

select_type 含义
SIMPLE 简单的 SELECT 查询,查询中不包含子查询或者 UNION
PRIMARY 查询中若包含任何复杂的子查询,最外层查询标记为该标识
SUBQUERY 在 SELECT 或 WHERE 中包含子查询,该子查询被标记为:SUBQUERY
DEPENDENT SUBQUERY 在 SUBQUERY 基础上,子查询中的第一个SELECT,取决于外部的查询
DERIVED 在 FROM 列表中包含的子查询,被标记为 DERIVED(衍生),MYSQL会递归执行这些子查询,把结果放在临时表中
UNION UNION 中的第二个或后面的 SELECT 语句,则标记为UNION ; 若 UNION 包含在 FROM 子句的子查询中,外层 SELECT 将被标记为:DERIVED
DEPENDENT UNION UNION 中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT UNION 的结果,UNION 语句中第二个 SELECT 开始后面所有 SELECT

type

对表的访问方式,表示 MySQL 在表中找到所需行的方式,又称访问类型

type 含义
ALL Full Table Scan,MySQL 将遍历全表以找到匹配的行,全表扫描,如果是 InnoDB 引擎是扫描聚簇索引
index Full Index Scan,index 与 ALL 区别为 index 类型只遍历索引树
range 索引范围扫描,常见于 between、<、> 等的查询
ref 非唯一性索引扫描,返回匹配某个单独值的所有记录,本质上也是一种索引访问
eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
const 通过主键或者唯一索引来定位一条记录
system system 是 const 类型的特例,当查询的表只有一行的情况下,使用 system
NULL MySQL 在优化过程中分解语句,执行时甚至不用访问表或索引

从上到下,性能从差到好,一般来说需要保证查询至少达到 range 级别, 最好达到 ref


key

possible_keys:

  • 指出 MySQL 能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
  • 如果该列是 NULL,则没有相关的索引

key:

  • 显示MySQL在查询中实际使用的索引,若没有使用索引,显示为 NULL
  • 查询中若使用了覆盖索引,则该索引可能出现在 key 列表,不出现在 possible_keys

key_len:

  • 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
  • key_len 显示的值为索引字段的最大可能长度,并非实际使用长度,即 key_len 是根据表定义计算而得,不是通过表内检索出的
  • 在不损失精确性的前提下,长度越短越好

Extra

其他的额外的执行计划信息,在该列展示:

  • Using index:该值表示相应的 SELECT 操作中使用了覆盖索引(Covering Index)

  • Using index condition:第一种情况是搜索条件中虽然出现了索引列,但是有部分条件无法使用索引,会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件,回表查询数据;第二种是使用了索引下推

  • Using where:表示存储引擎收到记录后进行后过滤(Post-filter),如果查询操作未能使用索引,Using where 的作用是提醒我们 MySQL 将用 where 子句来过滤结果集,即需要回表查询

  • Using temporary:表示 MySQL 需要使用临时表来存储结果集,常见于排序和分组查询

  • Using filesort:对数据使用外部排序算法,将取得的数据在内存中进行排序,这种无法利用索引完成的排序操作称为文件排序

  • Using join buffer:说明在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果

  • Impossible where:说明 where 语句会导致没有符合条件的行,通过收集统计信息不可能存在结果

  • Select tables optimized away:说明仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

  • No tables used:Query 语句中使用 from dual 或不含任何 from 子句

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/183577.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • Java中Scanner的理解大总结「建议收藏」

    Java中Scanner的理解大总结「建议收藏」Scanner类常用的方法:Scnaner(Filefile);Scnaner(Stringfilename);创建一个从特定文件扫描的扫描器hasNext();还有可读取的书库返回truenext();返回下一个标志作为字符串nextLine();使用行分隔符从这个扫描器返回一个行结束nextByte();nextshort();nextInt();nextLong()

  • 白盒测试技术_静态白盒测试

    白盒测试技术_静态白盒测试覆盖率 它是度量测试完整性的一个工具,通常可以分为逻辑覆盖和功能覆盖。覆盖率=(被执行到的项数/总项数)*100%逻辑覆盖 逻辑覆盖是以程序内部的逻辑结构为基础设计测试用例的技术,属于白盒测试。      被测试模块的流程图语句覆盖 设计若干测试用例,运行被测程序,使每个可执行语句至少执行一次。 语句覆盖率=被评价到的语句数量/

  • ASP.NET里的Session详细解释

    Session模型简介Session是什么呢?简单来说就是服务器给客户端的一个编号。当一台WWW服务器运行时,可能有若干个用户浏览正在运正在这台服务器上的网站。当每个用户首次与这台WWW服务器建立连

    2021年12月25日
  • J1939 多包报文传输

    J1939 多包报文传输以J1939RC(RetarderConfigration)报文为例,19个字节,需要分3条报文发送。1、将要发送多包报文之前先会广播一条ID为0x18ECFF**形式的一条报文TPCM(以目前理解最后**为源地址,RC报文的话为0F),数据场会提示接下来将会发送多少条报文,包含什么信息(RC)。2、随后以一条ID为0x18EB00**形式TPDT发送3条报文,传输数据多于8字节的报文…

  • PostgreSQL与MySQL的区别收集(转)

    PostgreSQL与MySQL的区别收集(转)

  • 机器学*系列文章索引

    0机器学习简介概念:多领域交叉学科,涉及概率论、统计学、逼近论、凸分析、算法复杂度理论等多门学科。专门研究计算机怎样模拟或实现人类的学习行为,以获取新的知识或技能,重新组织已有的知识结构使之不断改

    2021年12月30日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号