大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新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'))
-
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账号...