MySQL EXPLAIN语句的使用示例

原作者: [db:作者] 来自: [db:来源] 收藏邀请

在MySQL优化的环节上，我们首先需要知道的就是我们当前的这句SQL语句在实际的数据库中究竟是怎么执行的，才能谈要如何优化它。而在MySQL中，就给我们提供了模拟语句执行的一个非常好用的关键字：EXPLAIN。EXPLAIN可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。因此今天我们就来讲一讲这个关键字的一些基础的用法与应用。

一、使用方法

EXPLAIN的使用方法非常简单：

mysql> EXPLAIN SELECT * FROM user;

简单来说，就是在原有的SQL语句前面加上EXPLAIN关键字，或者说是在EXPLAIN关键字后跟这你要检查的SQL语句。

二、输出结果

EXPLAIN语句的输出结果才是我们想要的数据，也是我们分析的重点。
我们先来看看上面的语句所给到的对应的结果的形式：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE  | user | NULL  | ALL | NULL   | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

EXPLAIN语句给到我们的数据总共有10列，接下来我们看一下一些在性能优化上有比较重要作用的数据列所代表的意思。

1.id

这个是select查询的序列号。

2.select_type

当我们的SQL语句是非select语句的时候（即delete，update...），这个字段的值就是对应的操作类型（delete，update...）。

mysql> EXPLAIN INSERT INTO user VAULES(2,'ahong','31');

此时的输出select_type就是我们对应的INSERT：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | INSERT  | user | NULL  | ALL | NULL   | NULL | NULL | NULL | NULL |  NULL | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

而当SQL语句时select语句的时候，他就是对应的一些详细的select的类型，可以有如下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

下面就是一个最简单的SIMPLE查询的例子：

mysql> EXPLAIN SELECT * FROM user;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE  | user | NULL  | ALL | NULL   | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3.table

显示这一步操作所访问的数据是关于哪一张表的。

4.partitions

显示表所使用的分区，如果要统计十年公司订单的金额，可以把数据分为十个区，每一年代表一个区。这样可以大大的提高查询效率。

5.type

这是最重要的一列。显示了连接使用了哪种类别，有无使用索引。是分析查询性能的关键。
结果性能从优到差分别有以下的情况：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

而这几种情况所代表的意义如下：

system、const：可以将查询的变量转为常量. 如id=1; id为主键或唯一键.
eq_ref：访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或惟一键)
ref：访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况(注:不一定好于index)
index：以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描
ALL：全表扫描，应该尽量避免_

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

6.possible_key

显示查询语句有可能会使用到的索引列。取值可能为一个，多个或者null。

7.key

key列显示的是该查询语句实际使用的索引列。如为null，则表示没有使用索引。
展示一下possible_key和key的实际效果：
下面是一个在age列上建立索引的数据表，我们进行以下的查询

mysql> explain select * from user where age = 1;

会得到以下的结果：

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE  | user | NULL  | ref | age   | age | 5  | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

8.key_len

显示的是当前的查询语句所使用的索引的长度。在不损失精确性的情况下，长度越短越好.

9.ref

引用到的上一个表的列。

10.rows

根据表的情况和查询语句的情况，MySQL会估算出返回最终结果所必须检查的行的数量。该列的值越大查询效率越差。

11.filtered

一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数。

12.extra

关于MySQL如何解析查询的额外信息，主要有以下几种：

Extra中包含的值：

using index：只用到索引,可以避免访问表，性能很高。
using where：使用到where来过滤数据，不是所有的where clause都要显示using where. 如以=方式访问索引。
using tmporary：用到临时表去处理当前的查询。
using filesort：用到额外的排序，此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。(当使用order by v1,而没用到索引时,就会使用额外的排序)。
range checked for eache record(index map:N)：没有好的索引可以使用。
Using index for group-by：__表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。explain select user_id from t_order group by user_id;_

以上就是MySQL EXPLAIN语句的使用示例的详细内容，更多关于MySQL EXPLAIN语句的资料请关注极客世界其它相关文章！