解释SELECT * from user_info WHERE id 300;

准备

为了方便演示EXPLAIN的使用，首先我们需要创建两张表用于测试，并添加相应的数据：

创建表`user_info` ( `id` BIGINT(20) NOT NULL AUTO_INCRMENT, `name` VARCHAR(50) NOT NULL DEFAULT '', `age` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY ` name_index` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8INSERT INTO user_info (name,age) VALUES ('xys', 20);INSERT INTO user_info (name,age) VALUES ('a', 21);INSERT INTO user_info (姓名， 年龄) VALUES ('b', 23);INSERT INTO user_info (姓名， 年龄) VALUES ('c', 50);INSERT INTO user_info (姓名， 年龄) VALUES ('d', 15);INSERT INTO user_info (姓名， 年龄) VALUES ('e', 20);INSERT INTO user_info (姓名， 年龄) VALUES ('f', 21);INSERT INTO user_info (姓名， 年龄) VALUES ('g', 23); INSERT INTO user_info (name,age) VALUES ('h', 50);INSERT INTO user_info (name,age) VALUES ('i', 15);CREATE TABLE `order_info` ( `id` BIGINT(20) NOT NULL AUTO_INCRMENT , `user_id` BIGINT(20) DEFAULT NULL, `product_name` VARCHAR(50) NOT NULL DEFAULT '', `productor` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_product_detail_index` (`user_id` , `product_name`, `productor`)) ENGINE=InnoDB DEFAULT CHARSET=utf8INSERT INTO order_info (user_id,product_name,productor) VALUES (1, 'p1', 'WHH');INSERT INTO order_info (user_id,product_name,productor) VALUES (1, 'p2', 'WL');插入order_info (用户ID, 产品名称， 生产者) VALUES (1, 'p1', 'DX');插入order_info (用户ID, 产品名称， 生产者) 值(2, ' p1', 'WHH');插入order_info (用户ID, 产品名称， 生产者) VALUES (2, 'p5', 'WL');插入order_info (用户ID, 产品名称， 生产者) 值(3, 'p3', ' MA');INSERT INTO order_info (user_id,product_name,productor) VALUES (4,'p1','WHH');INSERT INTO order_info (user_id,product_name,productor)VALUES (6,'p1','WHH'); INSERT INTO order_info (user_id、product_name、productor) VALUES (9, 'p8', 'TE');

EXPLAIN 输出格式

EXPLAIN命令的输出大致如下：

mysql 解释select * from user_info where id=2\G**************************** 1.行***** ***** ******************** id: 1 select_type: SIMPLE table: user_info 分区： NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 Filtered: 100.00 Extra: NULL 1 行set, 1 warning (0.00 sec) 各列含义如下：

id: SELECT 查询的标识符。每个SELECT 都会自动分配一个唯一的标识符。 select_type: SELECT 查询的类型。 table: 查询哪个表partitions: 匹配分区类型： 连接类型possible_keys: 本次查询可能选择的索引键： 这次查询中使用的确切索引。 ref: 哪个字段或常量与键配合使用rows: 显示此查询总共扫描了多少行。这是一个估计。 Filtered: 表示通过该查询条件过滤的数据百分比extra: 附加信息访问接下来我们重点关注一些比较重要的字段。

select_type

select_type 表示查询的类型。其常见值为：

SIMPLE，表示该查询不包含UNION查询或子查询PRIMARY，表示该查询是最外层查询UNION，表示该查询是UNION的第二个或后续查询UNION RESULT，UNION SUBQUERY的结果，第三个查询在子查询中SELECT 最常见的查询类型应该是SIMPLE。例如，当我们的查询没有子查询或UNION查询时，通常是SIMPLE类型，比如：

mysql 解释select * from user_info where id=2\G**************************** 1.行***** ***** ******************** id: 1 select_type: SIMPLE table: user_info 分区： NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 Filtered: 100.00 Extra: NULL 1 行set, 1 warning (0.00 sec) 如果我们使用UNION 查询，那么EXPLAIN 输出的结果类似于以下：

mysql EXPLAIN (SELECT * FROM user_info WHERE id IN (1, 2, 3)) - UNION - (SELECT * FROM user_info WHERE id IN (3, 4, 5));+----+----- --------+----------------+----------------+-------- +-------- --------+---------+---------+--------+-------- +-------- ---+----------------+|编号|选择类型|表|隔断|类型|可能的键|关键| key_len |参考|行|过滤|额外|+-- --+--------------+------------+------------+- ----- -+----------------+---------+---------+-----+ ----- -+----------+-----------------+| 1 |小学|用户信息|空|范围|小学|小学| 8 |空| 3 | 100.00 |使用哪里|| 2 |联盟|用户信息|空|范围|小学|小学| 8 |空| 3 | 100.00 |使用哪里||空|联盟结果|联合1,2 |空|全部|空|空|空|空|空|空|使用临时|+----+-------------+------------+---- --------+ -------+----------------+----------+-------- --+----- -+------+----------+------------------+ set 中的3 行，1 个警告（0.00 秒）

table

表示查询涉及的表或派生表

type

type字段比较重要。它为判断查询是否高效提供了重要依据。通过type字段，我们判断查询是全表扫描还是索引扫描等。

type 常用类型

型常用值为：

system:表中只有一条数据。该类型是一种特殊的const 类型。 const: 相当于主键或唯一索引的查询扫描最多只返回一行数据。 const 查询非常快，因为它只读取一次。例如，以下此查询使用主键索引，因此类型为const。 mysql 解释select * from user_info where id=2\G************************ ****** 1.行******** ******************** id: 1 select_type: 简单表： user_info 分区： NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 Filtered: 100.00 Extra: NULL 1 行一组，1 条警告（ 0.00 sec)eq_ref: 这种类型通常出现在多表连接查询中，即前表中的每条结果只能匹配后表中的一行结果。并且查询的比较操作通常为=，查询效率较高。例如，mysql EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id=order_info.user_id\G******************** ************ ** 1.行**************************** id: 1 select_type: SIMPLE table: order_info 分区： NULL type: indexpossible_keys: user_product_detail_index key: user_product_detail_index key_len: 314 ref: NULL rows: 9 Filtered: 100.00 Extra: 使用位置；使用索引**************************** 2.行** **************** ********* id: 1 select_type: SIMPLE table: user_infopartitions: NULL type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: test.order_info.user_id rows: 1filtered: 100.00额外： 集合中有NULL2 行，1 个警告（0.00 秒）ref: 这type 通常出现在多表连接查询中，用于非唯一或非主键索引，或者使用最左前缀规则索引的查询。例如，在以下示例中，ref 类型查询：mysql EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id=order_info.user_id AND order_info.user_id=5\G**************** **** ********* 1.行**************************** id: 1 select_type: 简单表： user_info 分区： NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 Filtered: 100.00 Extra: NULL**************************** 2.行*** ***** ******************** id: 1 select_type: SIMPLE table: order_infopartitions: NULL type: refpossible_keys: user_product_detail_index key: user_product_detail_index key_len: 9 ref: const rows: 1 Filtered: 10 0. 00 Extra: 使用索引2 rows in set, 1 warning ( 0.01 sec)range: 表示使用索引范围查询，通过索引字段范围获取表中的部分数据记录。该类型通常出现在=、=、=、IS NULL、=、BETWEEN、IN()操作中。当type 为range 时，那么EXPLAIN 输出的ref 字段为NULL，而key_len 字段是本次查询中使用的最长索引。例如下面的例子是范围查询：

mysql EXPLAIN SELECT * - FROM user_info - WHERE id BETWEEN 2 AND 8 \G******************************** 1.行* *** ********************** id: 1 select_type: 简单表： user_info 分区： NULL type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 7 Filtered: 100.00 Extra:使用地点1 row in set , 1 warning (0.00 sec)index: 表示全索引扫描，与ALL类型类似，只不过ALL类型是全表扫描，而索引类型只扫描所有索引，不扫描数据。索引类型通常需要查询的数据出现在：中，可以直接在索引树中获取，无需扫描数据。在这种情况下，“额外”字段将显示“使用索引”。例如，

mysql EXPLAIN SELECT name FROM user_info \G******************************** 1.行********* *** **************** id: 1 select_type: 简单表： user_info 分区： NULL type: indexpossible_keys: NULL key: name_index key_len: 152 ref: NULL rows: 10 Filtered: 100.00 Extra:使用集合中的索引1 行，1 个警告( 0.00 sec) 上面的例子中，我们查询的name字段恰好是一个索引，所以我们直接从索引获取数据就可以满足查询需求，而不需要查询表中的数据。因此，本例中type 的值为index，Extra 的值为Usingindex。

ALL:表示全表扫描。这种类型的查询是性能最差的查询之一。一般来说，我们的查询不应该有ALL类型的查询，因为这样的查询在数据量较大时对数据库的性能有负面影响。这是一场巨大的灾难。如果查询是ALL类型的查询，一般来说，可以通过在对应的字段上添加索引来避免。以下是全表扫描的示例。可以看到在全表扫描的时候，possible_keys和key fields都是NULL，也就是说没有使用索引，而且行数非常巨大，所以整个查询效率非常低。 mysql解释从user_info中选择年龄WHERE年龄=20 \ G**************** ************* 1.行******** ******************** id: 1 select_type: SIMPLE table: user_infopartitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Filtered: 10.00 Extra: 使用where1行一组中，1 个警告（ 0.00秒)

type 类型的性能比较

一般来说，不同类型的性能关系如下：ALL索引范围~index_merge ref eq_ref const 因为systemALL类型是全表扫描，所以在相同查询条件下是最慢的。索引类型查询虽然不是全表扫描，但是会扫描所有索引，所以比ALL类型稍微快一点。后面类型都是使用索引来查询数据，因此可以过滤部分或大部分数据，因此查询效率比较高。

possible_keys

possible_keys表示MySQL查询时可以使用的索引。请注意，即使某些索引出现在possible_keys中，也不意味着该索引实际上会被MySQL使用。 MySQL在查询时专门使用哪些索引？由关键字段决定。

key

该字段是MySQL在当前查询中实际使用的索引。

key_len

表示查询优化器使用的索引的字节数。该字段可以评估组合索引是否被完全使用，或者仅使用最左边的字段。 key_len的计算规则如下：

String char(n): n 字节长度varchar(n): 如果是utf8编码则为3 n + 2 字节；如果是utf8mb4编码，则为4 n + 2字节。数字类型：TINYINT: 1 字节SMALLINT: 2 字节MEDIUMINT: 3 字节INT: 4 字节BIGINT: 8 字节时间类型DATE: 3 字节TIMESTAMP: 4 字节DATETIME: 8 字节字段属性： NULL 属性占用1 个字节。如果某个字段不为NULL，则没有此属性。举两个简单的栗子：

mysql EXPLAIN SELECT * FROM order_info WHERE user_id 3 AND Product_name='p1' AND Productor='WHH' \G**************************** * 1.行************************** id: 1 select_type: SIMPLE table: order_info 分区： NULL type: rangepossible_keys: user_product_detail_index key: user_product_detail_index key_len: 9 ref: NULL rows: 5 Filtered: 11.11 Extra: 使用位置； using index1 row in set, 1 warning (0.00 sec) 上面的例子是从表order_info中查询指定的内容，从该表的建表语句中我们可以知道表order_info有一个联合索引：

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`) 但是，在这个查询语句中WHERE user_id 3 AND Product_name='p1' AND Productor='WHH'，因为先进行了user_id 的范围查询，并且基于最左边的前缀匹配原则，当遇到范围查询时，停止索引匹配。因此，我们实际使用的索引的字段只有user_id。因此，在EXPLAIN中，显示的key_len为9。因为user_id字段为BIGINT，占用8个字。节，NULL属性占1个字节，所以总共是9个字节。如果我们将user_id 字段更改为BIGINT(20) NOT NULL DEFAULT '0'，则key_length 应为8。

由于最左前缀匹配原则，我们的查询只使用了联合索引的user_id字段，所以效率不高。

接下来我们看看下一个例子：

mysql EXPLAIN SELECT * FROM order_info WHERE user_id=1 AND Product_name='p1' \G;******************************** 1 .行***************************** id: 1 select_type: 简单表： order_info 分区： NULL 类型： refpossible_keys: user_product_detail_index key: user_product_detail_index key_len: 161 ref: const,const rows: 2filtered333 60 100.00 Extra:Usingindex1rowinset,1warning(0.00sec)在这个查询中，我们没有使用范围查询，key_len的值为161。为什么？因为我们的查询条件WHERE user_id=1 AND Product_name='p1' ，只使用了联合索引中的前两个字段，所以keyLen(user_id) + keyLen(product_name)=9 + 50 * 3 + 2=161

rows

行也是一个重要的字段。 MySQL查询优化器根据统计信息估计SQL需要扫描和读取以查找结果集的数据行数。这个值非常直观的体现了SQL的效率。原则上行数越少。越好。

Extra

EXplain 中的很多附加信息都会显示在Extra 字段中。常见的有以下：

Using filesort 当Extra中出现Using filesort时，说明MySQL需要额外的排序操作，无法通过索引顺序达到排序效果。一般如果有Using filesort，建议优化去掉，因为这样的查询会消耗大量的CPU资源。例如下面的例子：

mysql EXPLAIN SELECT * FROM order_info ORDER BY Product_name \G******************************** 1.行****** *** ****************** id: 1 select_type: SIMPLE table: order_info 分区： NULL type: indexpossible_keys: NULL key: user_product_detail_index key_len: 253 ref: NULL rows: 9 Filtered: 100.00 Extra3 3 360 使用索引；使用filesort1 row in set, 1 warning (0.00 sec) 我们的索引是