2. 设置参数log_bin_trust_function_creators
创建函数，假如报错，需开启如下命令：允许创建函数设置：
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
3. 创建存储函数
DELIMITER // CREATE FUNCTION rand_string1(n INT) RETURNS VARCHAR(255) #该函数会返回一个字符串 BEGIN DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1; END WHILE; RETURN return_str; END // DELIMITER ;
4. 创建存储过程
创建往s1表中插入数据的存储过程：
DELIMITER // CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO s1 VALUES( (min_num + i), rand_string1(6), (min_num + 30 * i + 5), rand_string1(6), rand_string1(10), rand_string1(5), rand_string1(10), rand_string1(10)); UNTIL i = max_num END REPEAT; COMMIT; END // DELIMITER ;
创建往s2表中插入数据的存储过程：
DELIMITER // CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO s2 VALUES( (min_num + i), rand_string1(6), (min_num + 30 * i + 5), rand_string1(6), rand_string1(10), rand_string1(5), rand_string1(10), rand_string1(10)); UNTIL i = max_num END REPEAT; COMMIT; END // DELIMITER ;
5. 调用存储过程
s1表数据的添加：加入1万条记录：
CALL insert_s1(10001,10000);
s2表数据的添加：加入1万条记录：
CALL insert_s2(10001,10000);
SELECT COUNT(*) FROM s1;#10000 SELECT COUNT(*) FROM s2;#10000
6.4 EXPLAIN各列作用
为了有比较好的体验，调整了下EXPLAIN输出列的顺序。
1.table
不论查询语句有多复杂，里边包含了多少个表 ，到最后也是需要对每个表进行单表访问 的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。
演示
#1. table：表名 #查询的每一行记录都对应着一个单表 EXPLAIN SELECT * FROM s1; /*单表查询返回一行 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */ #s1:驱动表 s2:被驱动表 EXPLAIN SELECT * FROM s1 INNER JOIN s2; /*查询的每一行记录都对应着一个单表: 多表查询返回多行 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ */
2.id
演示
#2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id #-->通常出现了几个select关键字就对应几个id SELECT * FROM s1 WHERE key1 = 'a';#id值1 SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';#id值1 SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2);#id值1、2 SELECT * FROM s1 UNION SELECT * FROM s2; EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; /*id值1 +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 INNER JOIN s2;#多表查询 /*id值1、1 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ */ EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; /*id值1、2 +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ */ ######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作######## EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a'); /*两个select却只出现一个id-->查询优化器将该语句转成了多表查询 +----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+------+----------+------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 1 | SIMPLE | s2 | NULL | eq_ref | idx_key2 | idx_key2 | 5 | atguigudb.s1.key1 | 1 | 10.00 | Using index condition; Using where | +----+-------------+-------+------------+--------+---------------+----------+---------+-------------------+------+----------+------------------------------------+ */ #Union去重-->UNION取并集并去重时产生一个临时表 <union1,2> Using temporary EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; /*两个select出现3行记录 +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ */ #UNION ALL不用去重，只有两个id/行 EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */
小结:
- id如果相同，可以认为是一组，从上往下顺序执行
- 在所有组中。id值越大。优先级越高。越先执行
- 关注点: id号每个号码。表示一趟独立的查询,一个sql的查询趟数越少越好
3.select_type
一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表（这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的
MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色，我们看一下select_type都能取哪些值，请看官方文档:
| 名称 | 描述 |
| SIMPLE | Simple SELECT (not using UNION or subqueries) |
| PRIMARY | Outermost SELECT |
| UNION | Second or later SELECT statement in a UNION |
| UNION | RESULT Result of a UNION |
| SUBQUERY | First SELECT in subquery |
| DEPENDENT | SUBQUERY First SELECT in subquery, dependent on outer query |
| DEPENDENT | UNION Second or later SELECT statement in a UNION, dependent on outer query |
| DERIVED | Derived table |
| MATERIALIZED | Materialized subquery |
| UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated foreach row of the outer query |
| UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
演示
#3. select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色 # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型 EXPLAIN SELECT * FROM s1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */ #连接查询也算是`SIMPLE`类型 EXPLAIN SELECT * FROM s1 INNER JOIN s2; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+ */ #对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个 #查询的`select_type`值就是`PRIMARY` #对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询 #以外，其余的小查询的`select_type`值就是`UNION` #`MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是 #`UNION RESULT` EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; /*临时表的select_type-->UNION RESULT +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ */ EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | UNION | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */ #子查询： #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是不相关子查询。 #该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY` EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ */ #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是相关子查询， #则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY` EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a'; #注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。 /* +----+--------------------+-------+------------+--------+-------------------+----------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+-------------------+----------+---------+-------------------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | eq_ref | idx_key2,idx_key1 | idx_key2 | 5 | atguigudb.s1.key2 | 1 | 10.00 | Using where | +----+--------------------+-------+------------+--------+-------------------+----------+---------+-------------------+------+----------+-------------+ */ #在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了 #最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b'); /* +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using where; Using index | | 3 | DEPENDENT UNION | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using where; Using index | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+ */ #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED` EXPLAIN SELECT * FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1; /* +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 2 | DERIVED | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ */ #当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时， #该子查询对应的`select_type`属性就是`MATERIALIZED` EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表 /* +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 303 | atguigudb.s1.key1 | 1 | 100.00 | NULL | | 2 | MATERIALIZED | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+-------------------+------+----------+-------------+ */
4. partitions (可略) :匹配的分区信息
代表分区表中的命中情况，非分区表，该项为NULL。一般情况下的查询语句的执行计划的partitions列的值都是NULL
https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
如果想详细了解，可以如下方式测试。创建分区表：
-- 创建分区表， -- 按照id分区，id<100 p0分区，其他p1分区 CREATE TABLE user_partitions (id INT auto_increment, NAME VARCHAR(12),PRIMARY KEY(id)) PARTITION BY RANGE(id)( PARTITION p0 VALUES less than(100), PARTITION p1 VALUES less than MAXVALUE );
查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则
DESC SELECT * FROM user_partitions WHERE id>200; /* +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_partitions | p1 | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where | +----+-------------+-----------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ */
5. type ☆
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称"访问类型"，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到 type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。
完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，
index_merge ， unique_subquery ， index_subquery ， range， index ， ALL 。
我们详细解释一下：
system：
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。比方说我们新建一个NyISA表，并为其后入—条记录:
演示
# 5. type：针对单表的访问方法 #当表中`只有一条记录`并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory， #那么对该表的访问方法就是`system`。 CREATE TABLE t(i INT) ENGINE=MYISAM; INSERT INTO t VALUES(1); #system-->性能最高的场景 EXPLAIN SELECT * FROM t; /* +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+ */ #再添加 INSERT INTO t VALUES(1); #EXPLAIN SELECT * FROM t; 就会变成all #换成InnoDB CREATE TABLE tt(i INT) ENGINE=INNODB; INSERT INTO tt VALUES(1); EXPLAIN SELECT * FROM tt; #all /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tt | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */ #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const` EXPLAIN SELECT * FROM s1 WHERE id = 10005; /*select_type为`const`-->访问效率为常数级 +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key2 = 10066; /* +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | idx_key2 | idx_key2 | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ */ #all EXPLAIN SELECT * FROM s1 WHERE key3 = 10066; #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的 #（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则 # 驱动表 all 对该被驱动表的访问方法就是`eq_ref` EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; /* `eq_ref` +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | atguigudb.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ */ #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref` EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */ #为什么 EXPLAIN SELECT * FROM s1 WHERE key3 = 10066; 是all 因为key3是varchar类型 10066需要函数转换，涉及函数是不会用到索引的 #加上''就是 ref EXPLAIN SELECT * FROM s1 WHERE key3 = '10066'; #当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法 #就可能是`ref_or_null` EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL; /* +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | ref_or_null | idx_key1 | idx_key1 | 303 | const | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+ */ #单表访问方法时在某些场景下可以使用`Intersection`、`Union`、 #`Sort-Union`这三种索引合并的方式来执行查询 OR的并集关系使两个索引都将用到 `index_merge` EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; /* +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 2 | 100.00 | Using union(idx_key1,idx_key3); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ */ #`unique_subquery`是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询 #转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的`type` #列的值就是`unique_subquery` EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a'; /* +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ | 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9895 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | s2 | NULL | unique_subquery | PRIMARY,idx_key1 | PRIMARY | 4 | func | 1 | 10.00 | Using where | +----+--------------------+-------+------------+-----------------+------------------+---------+---------+------+------+----------+-------------+ */ #如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法 EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c'); /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 3 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ */ #同上 EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b'; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 367 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ */ #当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index` # 索引覆盖 用到了联合索引idx_key_part EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'; /* +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | s1 | NULL | index | idx_key_part | idx_key_part | 909 | NULL | 9895 | 10.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ */ #最熟悉的全表扫描 `all` EXPLAIN SELECT * FROM s1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ */
小结：
结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL
其中比较重要的几个提取出来（见上图中的蓝色）。
SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 const级别。（阿里巴巴开发手册要求）
6. possible_keys和key
在EXPLAIN语句输出的执行计划中, possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引
比方说下边这个查询:
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'; /* +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1,idx_key3 | idx_key3 | 303 | const | 1 | 5.00 | Using where | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ */
上述执行计划的possible_keys列的值是idx_key1 , idx_key3，表示该查询可能使用到idx_key1, idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用哪一个来执行语句。
演示
#6. possible_keys和key：可能用到的索引 和 实际上使用的索引 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'; /* +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1,idx_key3 | idx_key3 | 303 | const | 1 | 5.00 | Using where | +----+-------------+-------+------------+------+-------------------+----------+---------+-------+------+----------+-------------+ */
7. key_len
演示
#7. key_len：实际使用到的索引长度(即：字节数) # 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。 EXPLAIN SELECT * FROM s1 WHERE id = 10005; # int 主键 4个字节 /* +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key2 = 10126; # int 唯一索引可能为null 4+1=5 /* +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | const | idx_key2 | idx_key2 | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; # varchar 100 utf8 100*3 +1(null) +2(实践长度） /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a'; #303 /* +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; #606 /* +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key_part | idx_key_part | 606 | const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; #909 EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a'; #没有使用索引，null #练习： #key_len的长度计算公式： #varchar(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) #varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段) #char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL) #char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
8. ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref ,ref_or_null、unique_subquery、 index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的结构是什么，比如只是一个常数或者是某个列。大家看下边这个查询:
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */
可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数，当然有时候更复杂一点
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; /* +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | atguigudb.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ */
演示
# 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。 #比如只是一个常数或者是某个列。 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ */ EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id; /* +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | atguigudb.s1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1); /* +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | func | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+ */
9. rows ☆
演示
# 9. rows：预估的需要读取的记录条数 # `值越小越好` EXPLAIN SELECT * FROM s1 WHERE key1 > 'z'; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 374 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------- */
10.filtered
越高越好，说明查的数据都是有效的
演示
# 10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比 #如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用 #到对应索引的搜索条件外的其他搜索条件的记录有多少条。 #越高越好，说明查的数据都是有效的 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a'; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 374 | 10.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+ */ #对于单表查询来说，这个filtered列的值没什么意义，我们`更关注在连接查询 #中驱动表对应的执行计划记录的filtered值`，它决定了被驱动表要执行的次数(即：rows * filtered) EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9895 | 10.00 | Using where | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | atguigudb.s1.key1 | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+ */
11.Extra☆
顾名思义，Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。可以通过这些额外信息来更准确的理辑MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑比较重要的额外信息介绍给大家。
No tables used
当直询语句的没有FROM子句时将会提示该额外信息，比如:
# 11.Extra:一些额外的信息 # 更准确的理解MySQL到底将如何执行给定的查询语句 #当查询语句的没有`FROM`子句时将会提示该额外信息 No tables used EXPLAIN SELECT 1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ */
Impossible WHERE
#查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息 Impossible WHERE EXPLAIN SELECT * FROM s1 WHERE 1 != 1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ */
Using where
#当使用全表扫描来执行对某个表的查询，并且该语句的`WHERE` #子句中有针对该表的搜索条件时，在`Extra`列中会提示上述额外信息。 Using where EXPLAIN SELECT * FROM s1 WHERE common_field = 'a'; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ */ #当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中 #有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。 USING WHERE EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ */
No matching min/max row
#当查询列表处有`MIN`或者`MAX`聚合函数，但是并没有符合`WHERE`子句中 #的搜索条件的记录时，将会提示该额外信息 No matching min/max row EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ */ # SELECT * FROM s1 LIMIT 10; EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'znOhuD'; #znOhuD 是 s1表中key1字段真实存在的数据 Select tables optimized away /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+ */
Using index
#当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以 #使用覆盖索引的情况下，在`Extra`列将会提示该额外信息 Using index 。比方说下边这个查询中只 #需要用到`idx_key1`而不需要回表操作： #覆盖索引 不需要回表操作 直接可以通过二级索引查到数据 # EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'; EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a'; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+ */
Using index condition
#Using index condition：查找使用了索引,但是需要回表查询数据 #有些搜索条件中虽然出现了索引列，但却不能使用到索引 #看课件理解索引条件下推 Using index condition #先找key1 > 'z' 再找key1 LIKE '%a'，最后回表 *， 优化 key1 > 'z' 再回表 * 最后 key1 LIKE '%a' EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a'; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | s1 | NULL | range | idx_key1 | idx_key1 | 303 | NULL | 374 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ */
using where; using index的组合:
查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using join buffer (Block Nested Loop)
#在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为 #其分配一块名叫`join buffer`的内存块来加快查询速度，也就是我们所讲的`基于块的嵌套循环算法` #见课件说明 Using where; Using join buffer (hash join) EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 10.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ */
Not exists
#当我们使用左（外）连接时，如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件， #而且那个列又是不允许存储`NULL`值的，那么在该表的执行计划的Extra列就会提示`Not exists`额外信息 Using where; Not exists EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL; /* +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | NULL | | 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | atguigudb.s1.key1 | 1 | 10.00 | Using where; Not exists | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------------------+ */
Using intersect(…) 、 Using union(…) 和 Using sort_union(…)
#如果执行计划的`Extra`列出现了`Using intersect(...)`提示，说明准备使用`Intersect`索引 #合并的方式执行查询，括号中的`...`表示需要进行索引合并的索引名称； #如果出现了`Using union(...)`提示，说明准备使用`Union`索引合并的方式执行查询； #出现了`Using sort_union(...)`提示，说明准备使用`Sort-Union`索引合并的方式执行查询。 Using union(idx_key1,idx_key3); Using where EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; /* +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ | 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 2 | 100.00 | Using union(idx_key1,idx_key3); Using where | +----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+ */
Zero limit
#当我们的`LIMIT`子句的参数为`0`时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息 Zero limit EXPLAIN SELECT * FROM s1 LIMIT 0; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Zero limit | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+ */
Using filesort
有一些情况下对结果集中的记录进行排序是可以使用到索引的，比如下边这个查询:
#有一些情况下对结果集中的记录进行排序是可以使用到索引的。 NULL #比如： EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+ | 1 | SIMPLE | s1 | NULL | index | NULL | idx_key1 | 303 | NULL | 10 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+ */
这个查询语句可以利用idx_key1索引直接取出key1列的10条记录，然后再进行回表操作就好了，但是很多情况下排序操作无法使用到索引，只能在内存中(记录较少的时候）或名磁盘中，(记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序〔英文名∶ filesort )。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示，比如这样:
#很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候） #进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：`filesort`）。 #如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的`Extra`列中显示`Using filesort`提示 EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ */
Using temporary
#在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们 #在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成 #查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行 #计划的`Extra`列将会显示`Using temporary`提示 EXPLAIN SELECT DISTINCT common_field FROM s1; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ */ #EXPLAIN SELECT DISTINCT key1 FROM s1; #同上。 EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field; /* +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 9895 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ */ #执行计划中出现`Using temporary`并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以 #我们`最好能使用索引来替代掉使用临时表`。比如：扫描指定的索引idx_key1即可 Using index EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1; /* +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9895 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ */
12.小结
- EXPLAIN不考各种Cache
- EXPLAIN不能显示MySQL在执行查询时所作的优化工作
- EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
- 部分统计信息是估算的，并非精确值
