mysql初始化三部曲_MySQL调优三部曲（二）EXPLAIN

最新推荐文章于 2024-08-13 01:02:58 发布

彭仕安

最新推荐文章于 2024-08-13 01:02:58 发布

阅读量115

点赞数

文章标签： mysql初始化三部曲

本文链接：https://blog.csdn.net/weixin_42099936/article/details/113400867

版权

MySQL调优三部曲(二)EXPLAIN

EXPLAIN

MySQL Query Optimizer通过执行EXPLAIN命令来告诉我们它将使用一个怎样的执行计划优化Query。所以，通过Explain可以帮助我们选择更好的索引和写出更优化的查询语句

Explain各种信息的解释

PS：下面列举的例子有些是无意义的，只是为了展示explain的效果

1. id

查询序列号，id大的先执行，相同的id按从上往下顺序依次执行，id列为NULL表示一个结果集，不是查询

2. select_type(查询中每个select子句的类型)

simple：除子查询或UNION之外的其他查询

mysql> explain select * from focus;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

primary： UNION或者含有子查询的select，位于最外层的查询就是primary

mysql> explain select object_id from focus a where id = 1 union select object_id from focus b where id = 3;

+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

| 1 | PRIMARY | a | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

| 2 | UNION | b | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

3 rows in set, 1 warning (0.00 sec)

union： UNION语句第二个select开始后面所有的select，第一个select是primary

mysql> explain select object_id from focus a where id = 1 union select object_id from focus b where id = 3;

+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

| 1 | PRIMARY | a | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

| 2 | UNION | b | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

3 rows in set, 1 warning (0.00 sec)

dependent union：和 union 类似，出现在UNION语句中，但是这个查询依赖于外部查询的结果集。在下面的sql语句中，依赖于外部查询的结果集的意思是，MySQL会先执行select * from focus，得到所有结果之后，再一条一条地去与子查询SQL组成新的查询语句，可想而知，这种查询类型是非常慢的

mysql> explain select status from focus a where id in (select id from favour where object_id = 19931224 union select id from favour where object_id = 19931226);

+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+

+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+

| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 33 | 100.00 | Using where |

+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+

4 rows in set, 1 warning (0.00 sec)

union result： union的结果，因为它不需要参与查询，所以id字段为null

mysql> explain select status from focus a union select status from favour where object_id = 19931224;

+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 33 | 100.00 | NULL |

+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

3 rows in set, 1 warning (0.01 sec)

subquery：子查询内层查询的第一个select，结果不依赖于外部查询结果集

mysql> explain select status from focus a where id = (select id from favour where object_id = 19931224);

+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------------+

+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------------+

+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------------+

2 rows in set, 1 warning (0.01 sec)

dependent subquery：子查询内层查询的第一个select，结果依赖于外部查询结果集(比如SELECT XXX FROM TABLE1 WHERE status = 1 AND id IN (SELECT XXX FROM TABLE2 WHERE SID IN (1,3,5,7,9))，在这条语句中，table2的查询就是dependent subquery，MySQL首先根据SELECT XXX FROM TABLE1 WHERE status = 1得到一个大的结果集，再将大的结果集中的每一条记录，都与子查询SQL组成新的查询语句，这就是结果依赖于外部查询结果的意思，慢查优化-DEPENDENT SUBQUERY)

uncacheable subquery：结果集无法缓存的子查询

derived：用于from子句里有子查询的情况，MySQL会递归执行这些子查询，把结果放在临时表中(例：SELECT g1.gid,count(1) FROM shop_goods g1, (select gid from shop_goods WHERE sid in (1519066,1453929)) g2 where g1.status=0 and g1.gid=g2.gid GROUP BY g1.gid;)

3. table：

查询的表名

4. type (表示MySQL在表中找到所需行的方式，又称“访问类型”，从最佳类型到最差类型依次列举)

const：用到 primary key 或者unique 索引(表最多只有一个匹配行)，const是最优化的

mysql> explain select * from focus where id = 1;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

eq_ref：唯一索引或者主键索引作为两个表的联接方式，可以使用=比较两个索引列

mysql> explain select a.id from focus a,favour b where a.id = b.id;

+----+-------------+-------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+

+----+-------------+-------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+

| 1 | SIMPLE | b | NULL | index | PRIMARY | infoByUser | 13 | NULL | 31 | 100.00 | Using index |

| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | drama.b.id | 1 | 100.00 | Using index |

+----+-------------+-------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+

2 rows in set, 1 warning (0.00 sec)

ref：使用索引，但不是唯一索引或者主键索引

mysql> explain select * from video where up_id = 123;

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

| 1 | SIMPLE | video | NULL | ref | up_id | up_id | 4 | const | 1 | 100.00 | NULL |

+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

ref_or_null：类似于ref，还可以额外查询含有NULL值得行，多用在子查询中

上面5种是比较合理的索引使用情况

index_merge：使用多个索引查找后的交集/并集定位数据

mysql> explain select * from video where up_id = 123 or id = 3;

+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+

+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+

+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+

1 row in set, 1 warning (0.00 sec)

range：对索引列进行范围查找，如in操作，between操作，>、

mysql> explain select * from video where id > 123;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

index: 是全表扫描，但是只select索引列的值，所以只需要扫描索引树即可

mysql> explain select id from video;

+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

all：扫全表，然后再在server层进行过滤返回符合要求的记录

5. possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

6. keys

当前query实际使用的索引

7. key_len

表示使用的索引的长度，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得

8. ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

mysql> explain select * from focus a , favour b where a.id = b.id ;

+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+

+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+

| 1 | SIMPLE | a | NULL | ALL | PRIMARY | NULL | NULL | NULL | 35 | 100.00 | NULL |

| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | drama.a.id | 1 | 100.00 | NULL |

+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+

2 rows in set, 1 warning (0.00 sec)

9. rows

执行MySQL查询的行数

10. extra

distinct： select使用distinct关键字时出现(实际使用distinct操作没作用)

using index：当前的SELECT操作使用了覆盖索引，query可以直接利用索引返回SELECT的字段，而不必根据索引再去读取数据文件(覆盖索引：包含所有满足查询需要的数据的索引)

mysql> explain select id from focus;

+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

using filesort：无法利用索引完成的排序

mysql> explain select * from favour order by status;

+----+-------------+--------+------+---------------+------+---------+------+------+----------------+

+----+-------------+--------+------+---------------+------+---------+------+------+----------------+

+----+-------------+--------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00 sec)

using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果，常见于排序和分组查询，出现这种情况需要优化。如下查询，两条sql语句的区别只在于order by的字段不同，但是一条用到了临时表，一条没用到。MySQL的表关联算法是Nest Loop Join，通过驱动表的结果集作为循环基础数据，然后将该结果集中的数据作为过滤条件到下一个表中去查询数据。通过EXPLAIN的结果，第一行出现的表就是驱动表，驱动表可以直接排序，而非驱动表需要用临时表存储合并结果，然后再进行排序。

mysql> explain select a.status from favour a, favour_1 b where a.id = b.status order by a.id;

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+

2 rows in set (0.00 sec)

mysql> explain select a.status from favour a, favour_1 b where a.id = b.status order by b.id;

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

2 rows in set (0.00 sec)

using where：使用where子句匹配数据，且where条件列非索引列

mysql> explain select * from favour where status = 1;

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

Using sort_union(...), Using union(...),Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描，如将两个索引结果并集，或者两个索引结果交集

Using index for group-by：类似于访问表的Using index方式，区别是Using index采用的是紧凑索引，Using index for group-by采用的是松散索引，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表，既group by字段就是索引列。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目

Using join buffer：

该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能

mysql> explain select * from favour a join favour_1 b on a.status = b.status;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

2 rows in set (0.00 sec)

Impossible where：这个值强调了where语句会导致没有符合条件的行

mysql> explain select * from favour where 1 = 2;

+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

1 row in set (0.00 sec)

Select tables optimized away：该值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

mysql> explain select max(id) from favour;

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

1 row in set (0.00 sec)

优化总结

上文详细解释了explain各输出信息的解释，那哪些参数是我们需要重点关注的呢？

一、select_type

当select_type是dependent union、dependent subquery，这种结果需要依赖外部查询结果集的查询，就需要进行优化了，一般的优化方案有：1、使用临时表联表查询 2、分成两个查询顺序执行

二、type

当type是index_merge，range，index，all时，可以进行选择采用以下几种优化方案：1、采用联合索引 2、对全表扫描的可以采取新建索引

三、extra

当extra是using filesort，using temporary，using where，Using join buffer时，可以采取以下几种优化方案：1、排序尽量用索引字段 2、尽量用索引字段进行多表连接 3、频繁查询的字段建立索引

彭仕安

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫