MYSQL高级之explain

简介

1. 在sql语句前增加explain关键字，会展示出sql的执行信息，而不是sql执行的结果，如下

EXPLAIN SELECT * FROM student JOIN score ON student.`id` = score.`student_id`;

返回如下

2. 下面我们一次介绍查询结果中代表的含义

查询结果

id

• id你可以看做是执行的顺序，id越大优先级越高，当id相同的时候，则从上往下一次执行
• 如果id为空则从上外下依次执行

select_type

用于表示简单查询还是复杂查询，他有一下几个值

1. simple：简单查询，不包含子查询和union查询，如下

EXPLAIN SELECT * FROM student WHERE id = 3;

2. primary：复杂查询的最外层的select，在有子查询的sql中最外层的查询就是primary
3. union：union语句的第二个select或者说后面的一个,UNION RESULT为合并结果如下

EXPLAIN SELECT * FROM student WHERE id = 3 
UNION 
SELECT * FROM student WHERE id = 4;

4. subquery：表示select中的子查询，不在from语句中

EXPLAIN SELECT * FROM student WHERE id = (SELECT student_id FROM score WHERE id=2);

5. DERIVED：派生表的select的，from后面的子查询，

EXPLAIN SELECT * FROM (
	SELECT * FROM score WHERE id IN (1,3,5)
) t WHERE t.id =1;

在某些版本中也会显示为SIMPLE

table

• 表示数据来源于那个表
• 有时候不是真实表的名字，而是一个虚拟表，虚拟表的最后一位是数字，代表id为多少的查询

type

• 连接类型，这个比较困难，且是我们优化的重要关注点，直接反应了我们的sql语句是否高效
• 这个的字段的值比较多，我们主要关注这几个字段system,const,eq_ref,ref,range,index,all
  • 性能由好到查依次为:system>const>eq_ref>ref>range>index>all(重要)

system

表仅有一行，这个是const类型的特例，平时不会出现，所以这个字段可以忽略不计

const

• 表示通过索引一次就找到了，const用于比较primary key 或者 unique索引，因为只匹配一行数据，所以很快
• 需要注意的是在查询中用到了primary key或者unique索引，所以说查询到一行数据，不一定就是const

EXPLAIN SELECT * FROM attend_schedule_shift WHERE id = 12;

id一般为主键，所以这里查询的type就是const

eq_ref

唯一性索引扫描，表中只有一条记录预置匹配。一般是两表关联，关联条件中的字段是主键或者是唯一索引

EXPLAIN SELECT * FROM attend_schedule_shift 
JOIN attend_schedule_section 
ON attend_schedule_shift.id = attend_schedule_section.`sche_shift_id`;

上面sql语句中使用attend_schedule_shift的主键id去关联其他表，所以attend_schedule_shift这个表的查询就是eq_ref

ref

• 非唯一索引扫描，返回某个单独值的所有行
• 本质上也是一种索引访问，他返回所有匹配某个单独值的行，他可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体

explain select * from time_attend_data where company_id = 420;

1. 复合索引 time_attend_data 里面有一个复合索引是company_id 与date共同组成的
2. 只用到了一个索引值
3. 这个单独值是company_id = 420

range

本质上也是一种索引查找，这个索引必须是单独的索引

• 检索给定范围的行，使用一个索引来选择行，key列显示了使用那个索引。
• 一般条件查询中出现了 > 、< 、in、 between等查询，但是出现了也不一定是

explain select * from time_attend_data where `id` between 300 and 600;

index

遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。all和index都是读全表，但是index是从索引中检索的，而all是从硬盘中检索的。

EXPLAIN SELECT `company_id`,`date` FROM time_attend_data;

一般select后跟的直接就是索引

ALL

• 全表扫描

possible_keys

• 显示可能应用在这张表中的索引，但不一定被查询实际使用。如果该类为NULL，那么没有相关的索引。
• 可以通过检查where自己看能否添加一个适当的索引来提高性能

key

• 实际使用的索引。
• 如果possible_keys有值，但是key为null，那么这种情况可能是谁表中的数据不多，mysql认为对于当前的查询帮助不大而选择了全表查询
• 如果想要强制使用mysql使用或者忽视possible_keys中的索引，那么在查询的时候使用force index、ignore index

key_len

• 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
• 一般来说，索引长度越长表示精度越高，效率偏低；长度越短，效率高，但精度就偏低。
• 并不是真正使用索引的长度，是个预估值

索引最大长度为768字节，当长度过大时，mysql会做一个类似最左前缀处理，将前半部分字符提取出做索引。当字段为null时，还需要1个字节去记录。

计算规则

1. 字符串
   1. char(n)：n个数字或者字母占n个字节，汉字占3n个字节
   2. varchar(n):n个数字或者字母占n个字节，汉字占3n+2个字节(+2用来存储字符串长度)
2. 数字类型
   1. tinyint：1字节
   2. smallint：2字节
   3. int：4字节
   4. bigint：8字节
3. 时间类型
   1. date：3字节
   2. timestamp：4字节
   3. datetime：8字节

ref

表示哪一列被使用了，常数表示这一列等于某个常数。

rows

大致找到所需记录需要读取的行数。

filtered

表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%

在 SQL 的 EXPLAIN 输出中，filtered 列通常表示预计有多少比例的行满足 WHERE 子句中的过滤条件。它可以帮助你了解查询过滤操作的效果。

filtered 列的值通常是一个百分比，表示预计有多少行会通过过滤条件。如果 filtered 列的值接近 100%，则表示大部分行都满足过滤条件。如果 filtered 列的值较低，则表示只有少数行满足过滤条件。

请注意，filtered 列的值是基于查询优化器的估计，实际结果可能会有所不同。要获得更准确的性能指标，建议使用执行计划分析工具或监控查询的实际执行情况。

以下是一个示例查询和 EXPLAIN 输出中 filtered 列的示例：

EXPLAIN SELECT * FROM employees WHERE age > 30;

假设输出中的 filtered 列显示为：

| filtered |                                  condition                                  |
|----------|---------------------------------------------------------------------------|
|   90%    | const_value = <const_value> AND age > 30 |

在这个例子中，filtered 列显示了预计有 90% 的行满足过滤条件 age > 30。这意味着查询优化器估计将有大约 90% 的员工年龄大于 30 岁。

请注意，具体的输出格式和列名称可能会因数据库的不同而有所差异。因此，建议查阅特定数据库的文档或咨询数据库管理员以获取准确的解释和帮助。

extra

这里是一些，额外的比较重要的信息

Using filesort

• 使用了外部索引排序，而不是按照表内的索引顺序进行读取(一般需要优化)
• MYSQL无法利用索引完成的排序 称为文件排序

优化方式

当查询执行计划显示“Using filesort”时，表示需要进行排序操作，但没有使用索引或者无法使用索引进行排序。针对“Using filesort”的优化方法如下：

1. 增加索引：

   • 检查查询中涉及的字段是否有合适的索引。
   • 考虑为经常用于排序或筛选的字段创建索引。
   • 如果有多个排序和筛选条件，可以考虑复合索引。

2. 调整查询方式：

   • 尝试重写查询，减少排序的必要性。
   • 考虑将部分查询分解为多个小查询，然后使用 UNION 或 JOIN 将结果组合起来。

3. 调整数据库配置：

   • 增大 sort_buffer_size 的值。这可以让 MySQL 尽量减少在排序过程中对需要排序的数据进行分段。
   • 调整 max_length_for_sort_data 参数，以便更好地适应返回的字段长度。

4. 优化SQL语句结构：

   • 去掉不必要的返回字段，以减少排序操作的负担。
   • 考虑将部分复杂查询分解为多个简单查询，然后使用程序逻辑组合结果。

5. 其他优化方法：

   • 考虑增加内存缓存，以便更好地支持排序操作。
   • 如果可能，将部分数据存储在内存中，这样可以更快地访问和排序这些数据。

在尝试上述优化方法之后，务必重新运行查询并查看执行计划，以确定“Using filesort”问题是否得到解决。如果仍然存在问题，可能需要进一步分析查询和数据库结构，或考虑使用其他数据库优化工具和策略。

Using temporary

使用了临时表保存中间结果。常见于排序 order by和分组查询 group by(最好优化)

using index

表示select语句中，使用了覆盖索引，直接从索引中取值，不需要从磁盘中读取数据。

using where

使用了where过滤

using index condition

5.6之后新增的，表示查询的列有费索引的列，先判断索引的条件

当你在MySQL中执行一个查询时，为了找到你需要的行，MySQL需要扫描整个表或索引。但是，如果你已经知道某些过滤条件，并且这些条件与索引相关，那么MySQL可以仅扫描索引而不是整个表来更快地找到结果。

例如，假设你有一个名为users的表，该表有一个名为email的索引。如果你执行以下查询：

SELECT * FROM users WHERE email='john@example.com' AND age>30;

如果你经常基于email和age来查询数据，那么MySQL可能会选择使用email索引来加速查询。但是，只有当满足以下条件时，它才会选择这样做：

1. email列上的值是已知的，并且该值在索引中。
2. 查询中的其他条件（在本例中为age>30）可以在索引扫描期间进行过滤。

这种策略允许MySQL仅扫描满足条件的索引条目，而不是扫描整个表。这可以大大减少需要检查的行数，从而加速查询。