在上一篇文章我们介绍了mysql如何开启慢日志查询,那么当我们从日志中取出来那些mysql又如何做sql优化呢?下面我们挨个介绍下来。
一、挑选一个sql进行执行
explain select * from sys_dept ORDER BY dept_id desc;
这里我们使用一个普通sql来进行讲解分析explain的思路。上面这个sql里面我们可以看到如下图的结果
二、分析explain的字段
我们从上诉1里面可以看到,在explain的查询结果里面会涉及到如下字段:
序号 | 字段 | 含义 |
1 | id | 这个id是指select的序列号,序列号越大,则先执行,如果序列号相同,则从上往下依次执行。id的顺序是按select出现的顺序增长的 |
2 | select_type | 表示当前查询是简单查询还是复杂查询 |
3 | table | 表示当前的sql正在访问哪张表 |
4 | partitions | 分区的概念,一般为空 |
5 | type | 表示关联字段的类型或访问的类型 |
6 | possible_keys | 表示当前查询可能会使用到哪些索引来进行查找 |
7 | key | 表示当前查询中,mysql采用哪个索引来优化对表的访问。 |
8 | key_len | 表示mysql在索引中使用的字节数长度,通过这个值可以算出具体使用了索引中的哪些列 |
9 | ref | 在key列的索引中,查找值使用到的列或者常量 |
10 | rows | mysql需要读取多少行的数据来满足查询需求 |
11 | filtered | 这个代表存储引擎返回的数据再server层过滤后,剩下多少满足查询的记录数量的比例,这个值是一个百分比,一般忽略掉。 |
12 | Extra | 额外的信息 |
三、explain里面我们最需要关心哪些字段
上诉二我们列举了explain的所有返回字段,但是这里不是所有的字段都需要我们关注的,我们重点关注的字段有:
序号 | 字段 |
1 | select_type |
2 | type |
3 | possible_keys |
4 | key |
5 | Extra |
这里由于是重点关注的字段,所以在这里解释下。
3.1、select_type列字段的值
在select_type列字段里面,他的值包含如下信息
序号 | 值 | 解释 |
1 | SIMPLE | 简单查询(不使用关联查询或子查询) |
2 | PRIMARY | 如果包含关联查询或子查询,则最外层的查询部分标记为primary |
3 | UNION | 联合查询中第二个及后面的查询 |
4 | DEPENDENT UNION | 满足依赖外部的关联查询中第二个及以后的查询 |
5 | UNION RESULT | 联合查询的结果 |
6 | SUBQUERY | 子查询中的第一个查询 |
7 | DEPENDENT SUBQUERY | 子查询中的第一个查询,并且依赖外部查询 |
8 | DERIVED | 用到派生表的查询 |
9 | MATERIALIZED | 被物化的子查询 |
10 | UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存,必须重新评估外层查询的每一行 |
11 | UNCACHEABLE UNION | 关联查询第二个或后面的语句属于不可缓存的子查询 |
3.2、type列字段的值
在type列的字段里面,他的值包含如下信息
序号 | 值 | 解释 |
1 | system | 查询对象表只有一行数据,且只能用于MySAM和Memory引擎的表,这是最好的情况。 |
2 | const | 基于主键或唯一索引查询,最多返回一条结果 |
3 | eq_ref | 表连接时基于主键或非NULL的唯一索引完成扫描 |
4 | ref | 基于普通索引的等值查询,或者表之间的等值连接 |
5 | fulltext | 全文检索 |
6 | ref_or_null | 表连接类型是ref,但进行扫描的索引列中可能包含null值 |
7 | index_merge | 利用多个索引 |
8 | unique_subquery | 子查询中使用唯一索引 |
9 | index_subquery | 子查询中使用普通索引 |
10 | range | 利用索引进行范围查询 |
11 | index | 全索引扫描 |
12 | ALL | 全表扫描 |
13 | NULL | 代表当前sql不需要访问表或者索引 |
备注:
1、在type列的所有值里面,以上诉表格为例,从上到下代表的是sql查询性能逐渐递减,也就是system是最优的,all是最差的,如果type的值是all的话,说明当前的sql语句需要进行优化。
2、在上面第13个的值是null,代表的是当前的sql不需要访问表或者索引,直接就能得到结果,例如:explain select sum(1+1)。
3.3、possible_keys列
上面介绍过possible_keys列代表的是当前sql可能会用到的索引列。
3.4、key列
key代表的是实际用到的索引列,在查看sql的话,我们要根据实际的key进行优化操作。
3.5、extra列
这是额外的索引,例如:使用到的索引,然后这些索引的具体情况,例如:
序号 | 值 | 解释 | 例子 |
1 | Using filesort | 将使用外部排序而不是索引排序,数据较少时从内存排序,否则需要在磁盘上完成排序 | explain select * from table order by cts |
2 | Using temporary | 需要创建一个临时表来存储结构,通常发生在对没有索引的列进行group by操作的时候 | explain select * from table group by cts |
3 | Using index | 使用覆盖索引 | explain select a from table where a = 1 |
4 | Using where | 使用where语句来处理结果 | explain select * from table where cts = '2022-09-23 15:58:00' |
5 | Impossible WHERE | 对where子语句判断的结果总是false而不能选择任何数据 | explain select * from table where 1< 0 |
6 | Using join buffer(Block Nested Loop) | 关联查询中,被驱动表的关联字段没有索引 | explain select * from table1 join table2 on table1.cts = table2.cts |
7 | Using index condition | 先条件过滤索引,再查数据 | explain select * from tablename where (name = '123' and id = 1) |
8 | Select tables optimized away | 使用某些聚合函数(例如:max,min)来访问存在索引的某个字段 | explain select max(a) from table |
备注:
1、出现using index condition的时候,需要核对下后面的字段类型,例如案例中的id如果在表中是varchar类型,如果用int类型去查询就会出现Using index confition,但是如果用 id = '1' 查询,则不会出现Using index condition。
2、如果extra列中出现了Using filesort或者Using temporary,那么久代表当前mysql根本不能使用索引,此时效率非常低,应当尽可能的去优化。
以上就是mysql在使用explain进行分析慢sql的时候,通过哪些列,并且出现哪些值,根据具体情况就可以分析出造成慢sql的原因。
还没有评论,来说两句吧...