mysql慢查询优化

作者：你好赵伟 | 2024-05-02 05:13:29

踩

mysql慢查询优化

定位低效

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;
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 ;
1
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(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'))
1

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 ; 
1
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(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 子句

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/522353