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)
blank

相关推荐

  • 闫学灿acwing_用标号法求网络最大流

    闫学灿acwing_用标号法求网络最大流给定一个包含 n 个点 m 条边的有向图,并给定每条边的容量,边的容量非负。图中可能存在重边和自环。求从点 S 到点 T 的最大流。输入格式第一行包含四个整数 n,m,S,T。接下来 m 行,每行三个整数 u,v,c,表示从点 u 到点 v 存在一条有向边,容量为 c。点的编号从 1 到 n。输出格式输出点 S 到点 T 的最大流。如果从点 S 无法到达点 T 则输出 0。数据范围2≤n≤1000,1≤m≤10000,0≤c≤10000,S≠T输入样例:7 14 1 71 2

  • 简单选择排序 C语言

    简单选择排序 C语言简单选择排序(SimpleSelectionSort)也称作直接选择排序。算法步骤:1)设待排序的记录存放在数组Data[1…n]中。第一趟从Data[1]开始,通过n-1次比较,从n个记录中选出关键字最小的记录,记为Data[k],交换Data[1]和Data[k]。2)第二趟从Data[2]开始,通过n-2次比较,从n-1个记录中选出关键字最小的记录,记为Data[k],交换Data[2]和Data[k]。3)依次类推,第i趟从Data[i]开始,通过n-i次比较,从n-i

  • T-SQL基础–TOP

    T-SQL基础–TOP

    2021年11月26日
  • 手把手撸个博客网站

    手把手撸个博客网站node-webserver-blog-public源码地址博客地址CSDN运行项目前必读三个项目中各种各样的授权参数已全部修改成自己的授权参数,忘悉知!!!!忘悉知!!!!忘悉知!!!!自己创建一个数据库名称就可以了,表是运行node时候自动创建好以myblog3为数据库名称,admin登录页面有个一键生成地方生成账号:admin密码:123,只能生成一次,因…

  • phpmyadmin设置自动登录和取消自动登录

    phpmyadmin设置自动登录和取消自动登录

    2021年10月15日
  • Velocity常用语法「建议收藏」

    Velocity常用语法「建议收藏」一、基本语法1、"#"用来标识Velocity的脚本语句,包括#set、#if、#else、#end、#foreach、#end、#iinclude、#parse、#macro等;如:#if($info.imgs)&lt;imgsrc="$info.imgs"border=0&gt;#else&lt;imgsrc="noPhoto.jpg"&gt;#end2、"$"用来标识一个对象(或理解

发表回复

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

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