mysql初始化三部曲_MySQL调优三部曲(二)EXPLAIN

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;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | focus | NULL | ALL | NULL | NULL | NULL | NULL | 33 | 100.00 | NULL |

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

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;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 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 |

| NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |

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

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;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 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 |

| NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |

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

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);

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

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

| 2 | DEPENDENT SUBQUERY | favour | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 10.00 | Using where |

| 3 | DEPENDENT UNION | favour | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 10.00 | Using where |

| NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |

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

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;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

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

| 2 | UNION | favour | NULL | ALL | NULL | NULL | NULL | NULL | 31 | 10.00 | Using where |

| NULL | UNION RESULT | | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |

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

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);

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |

| 2 | SUBQUERY | favour | NULL | index | NULL | infoByUser | 13 | NULL | 31 | 10.00 | Using where; Using index |

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

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;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | focus | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |

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

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;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 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;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 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;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | video | NULL | index_merge | PRIMARY,up_id | up_id,PRIMARY | 4,4 | NULL | 2 | 100.00 | Using union(up_id,PRIMARY); Using where |

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

1 row in set, 1 warning (0.00 sec)

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

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

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | video | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |

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

1 row in set, 1 warning (0.00 sec)

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

mysql> explain select id from video;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | video | NULL | index | NULL | up_id | 4 | NULL | 24 | 100.00 | Using index |

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

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 ;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 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;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | focus | NULL | index | NULL | object | 9 | NULL | 35 | 100.00 | Using index |

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

1 row in set, 1 warning (0.00 sec)

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

mysql> explain select * from favour order by status;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | favour | ALL | NULL | NULL | NULL | NULL | 7 | Using filesort |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |

| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.b.status | 1 | Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | b | index | NULL | PRIMARY | 4 | NULL | 1 | NULL |

| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.b.status | 1 | Using where |

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

2 rows in set (0.00 sec)

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

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | favour | ALL | NULL | NULL | NULL | NULL | 7 | Using where |

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

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;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1 | NULL |

| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 7 | Using where; Using join buffer (Block Nested Loop) |

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

2 rows in set (0.00 sec)

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

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |

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

1 row in set (0.00 sec)

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

mysql> explain select max(id) from favour;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |

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

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、频繁查询的字段建立索引

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值