专栏首页小强的进阶之路一文看懂如何分析MySQL Explain(2/3)

一文看懂如何分析MySQL Explain(2/3)

接上文“一文看懂如何分析MySQL Explain(1/3)”

⑨ range:如果使用索引获取某些范围区间的记录,那么就可能使用到range访问方法,比如:

EXPLAIN EXTENDED select * from t_classes where id in (1,2,3); show WARNINGS;
+-------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
| id    | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+-------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1     | SIMPLE      | t_classes | range | PRIMARY       | PRIMARY | 4       | NULL | 3    | 100.00   | Using where |
+-------+-------------+-----------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t_classes`.`id` AS `id`,`test`.`t_classes`.`classes_name` AS `classes_name`,`test`.`t_classes`.`grade` AS `grade`,`test`.`t_classes`.`student_num` AS `student_num`,`test`.`t_classes`.`head_teacher_id` AS `head_teacher_id`,`test`.`t_classes`.`status` AS `status`,`test`.`t_classes`.`create_time` AS `create_time`,`test`.`t_classes`.`update_time` AS `update_time` from `test`.`t_classes` where (`test`.`t_classes`.`id` in (1,2,3)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

⑩ index:当我们可以使用索引覆盖,但需要扫描全部的索引记录时,该表的访问方法就是index,比如:

EXPLAIN EXTENDED select id_card from t_student where student_name = '张三';show WARNINGS;
+-------+-------------+-----------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
| id    | select_type | table     | type  | possible_keys | key                      | key_len | ref  | rows | filtered | Extra                    |
+-------+-------------+-----------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
| 1     | SIMPLE      | t_student | index | NULL          | idx_classes_name_card_no | 1542    | NULL | 8    | 100.00   | Using where; Using index |
+-------+-------------+-----------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t_student`.`id_card` AS `id_card` from `test`.`t_student` where (`test`.`t_student`.`student_name` = '张三') |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------+

⑪ all:全表扫描,就不多赘述了

5 possible_keys列

在EXPLAIN语句输出的执行计划中,possible_keys列表示可能用到的索引有哪些,如:

EXPLAIN EXTENDED select id from t_student where classes_id = 1 ;SHOW WARNINGS;
+-------+-------------+-----------+------+-----------------------------------------+----------------+---------+-------+------+----------+-------------+
| id    | select_type | table     | type | possible_keys                           | key            | key_len | ref   | rows | filtered | Extra       |
+-------+-------------+-----------+------+-----------------------------------------+----------------+---------+-------+------+----------+-------------+
| 1     | SIMPLE      | t_student | ref  | idx_classes_id,idx_classes_name_card_no | idx_classes_id | 4       | const | 8    | 100.00   | Using index |
+-------+-------------+-----------+------+-----------------------------------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
+-------+------+------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t_student`.`id` AS `id` from `test`.`t_student` where (`test`.`t_student`.`classes_id` = 1) |
+-------+------+------------------------------------------------------------------------------------------------------------+

6 key列

在EXPLAIN语句输出的执行计划中,keys列表示经过查询优化器计算使用不同索引成本后决定使用的索引名,如上例

7 key_len列

key_len列表示当优化器决定使用某个索引执行查询时,该索引记录的最大长度,它是由这三个部分构成的:

① 对于使用固定长度类型的索引列来说,它实际占用的存储空间的最大长度就是该固定值,对于指定字符集的变长类型的索引列来说,比如某个索引列的类型是VARCHAR(255),使用的字符集是utf8(MySQL utf8字符集占用3个字节长度),那么该列实际占用的最大存储空间就是255 × 3 + 2= 767个字节。

② 如果该索引列可以存储NULL值,则key_len比不可以存储NULL值时多1个字节。

③ 对于变长字段来说,都会有2个字节的空间来存储该变长列的实际长度。

例1:classes_id字段是int类型,非空,占用了四个字节,长度是4

EXPLAIN EXTENDED select id from t_student where classes_id = 1; SHOW WARNINGS;
+----+-------------+-----------+------+-----------------------------------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table     | type | possible_keys                           | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------+-----------------------------------------+----------------+---------+-------+------+----------+-------------+
| 1  | SIMPLE      | t_student | ref  | idx_classes_id,idx_classes_name_card_no | idx_classes_id | 4       | const | 8    | 100.00   | Using index |
+----+-------------+-----------+------+-----------------------------------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
+-------+------+------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t_student`.`id` AS `id` from `test`.`t_student` where (`test`.`t_student`.`classes_id` = 1) |
+-------+------+------------------------------------------------------------------------------------------------------------+

例2:classes_id字段是int类型,字段改为可为空,占用了四个字节,可为空加一个字节,长度是5

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| classes_id   | int(11)      | YES  | MUL | NULL    |                |
| student_name | varchar(255) | NO   |     | NULL    |                |
| age          | int(11)      | NO   |     | NULL    |                |
| id_card      | varchar(255) | NO   | UNI | NULL    |                |
| address      | varchar(255) | NO   |     | NULL    |                |
| no           | int(11)      | NO   |     | NULL    |                |
| status       | tinyint(4)   | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
EXPLAIN EXTENDED select id from t_student where classes_id = 1; SHOW WARNINGS;
+----+-------------+-----------+------+-----------------------------------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table     | type | possible_keys                           | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------+-----------------------------------------+----------------+---------+-------+------+----------+-------------+
| 1  | SIMPLE      | t_student | ref  | idx_classes_id,idx_classes_name_card_no | idx_classes_id | 5       | const | 8    | 100.00   | Using index |
+----+-------------+-----------+------+-----------------------------------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
+-------+------+------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t_student`.`id` AS `id` from `test`.`t_student` where (`test`.`t_student`.`classes_id` = 1) |
+-------+------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

例3: student_name字段是varchar(255),255 * 3 + 2= 767

EXPLAIN EXTENDED select id from t_student where student_name = ''; SHOW WARNINGS;
+----+-------------+-----------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
| 1  | SIMPLE      | t_student | ref  | idx_student_name | idx_student_name | 767     | const | 1    | 100.00   | Using where; Using index |
+----+-------------+-----------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t_student`.`id` AS `id` from `test`.`t_student` where (`test`.`t_student`.`student_name` = '') |
+-------+------+---------------------------------------------------------------------------------------------------------------+

8 ref列

当使用索引列等值匹配的条件去执行查询时,也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时,ref列展示的就是与索引列作等值匹配的条件,如常数或某个列。

例1: 过滤条件是常数

EXPLAIN EXTENDED select id from t_student where student_name = '';
+----+-------------+-----------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------+------------------+------------------+---------+-------+------+----------+--------------------------+
| 1  | SIMPLE      | t_student | ref  | idx_student_name | idx_student_name | 767     | const | 1    | 100.00   | Using where; Using index |
+----+-------------+-----------+------+------------------+------------------+---------+-------+------+----------+--------------------------+

例2:过滤条件是字段,下例过滤条件是test数据库的t_student表的classes_id字段

EXPLAIN EXTENDED select t_student.id from t_student inner join t_classes on t_classes.id = t_student.classes_id; 
+----+-------------+-----------+--------+-----------------------------------------+----------------+---------+---------------------------+------+----------+--------------------------+
| id | select_type | table     | type   | possible_keys                           | key            | key_len | ref                       | rows | filtered | Extra                    |
+----+-------------+-----------+--------+-----------------------------------------+----------------+---------+---------------------------+------+----------+--------------------------+
| 1  | SIMPLE      | t_student | index  | idx_classes_id,idx_classes_name_card_no | idx_classes_id | 5       | NULL                      | 8    | 100.00   | Using where; Using index |
| 1  | SIMPLE      | t_classes | eq_ref | PRIMARY                                 | PRIMARY        | 4       | test.t_student.classes_id | 1    | 100.00   | Using index              |
+----+-------------+-----------+--------+-----------------------------------------+----------------+---------+---------------------------+------+----------+--------------------------+

9 rows列

该列是查询优化器预计的扫描记录数。

例1:使用idx_classes_id索引,代表扫描的索引记录数

EXPLAIN EXTENDED select t_student.id from t_student ; 
+----+-------------+-----------+-------+---------------+----------------+---------+------+------+----------+-------------+
| id | select_type | table     | type  | possible_keys | key            | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+-------+---------------+----------------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | t_student | index | NULL          | idx_classes_id | 5       | NULL | 8    | 100.00   | Using index |
+----+-------------+-----------+-------+---------------+----------------+---------+------+------+----------+-------------+

例2: 使用全表扫描,代表表记录数

EXPLAIN EXTENDED select * from t_classes where student_num > 56; 
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | t_classes | ALL  | NULL          | NULL | NULL    | NULL | 20   | 100.00   | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+

10 filtered列

filtered列在单表查询时没什么意义,但是在连接查询时意义重大,凭借该列和rows列可以粗略估算出当前连接的查询成本,MySQL连接查询采用的是嵌套循环连接算法,驱动表会被访问一次,被驱动表可能会被访问多次,所以MySQL连接查询的成功 = 单次驱动表查询成本 + 单次被驱动表查询成本 * 查询次数(MySQL执行计划估算出的驱动表查询结果集记录数,即rows列的值)

EXPLAIN EXTENDED select t_subject.id from t_subject inner join t_classes on t_classes.id = t_subject.classes_id; SHOW WARNINGS;
+----+-------------+-----------+-------+----------------+----------------+---------+-------------------+-------+----------+-------------+
| id | select_type | table     | type  | possible_keys  | key            | key_len | ref               | rows  | filtered | Extra       |
+----+-------------+-----------+-------+----------------+----------------+---------+-------------------+-------+----------+-------------+
| 1  | SIMPLE      | t_classes | index | PRIMARY        | idx_grade      | 4       | NULL              | 20    | 100.00   | Using index |
| 1  | SIMPLE      | t_subject | ref   | idx_classes_id | idx_classes_id | 4       | test.t_classes.id | 43718 | 100.00   | Using index |
+----+-------------+-----------+-------+----------------+----------------+---------+-------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t_subject`.`id` AS `id` from `test`.`t_subject` join `test`.`t_classes` where (`test`.`t_subject`.`classes_id` = `test`.`t_classes`.`id`) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

如上例,t_subject是驱动表,t_classes是被驱动表,MySQL是按照执行计划中的顺序逐条执行(id序号,如果相同则从上往下执行),首先从t_classes表中查询出所有的记录,因为可以使用覆盖索引,但是要扫描所有的记录行,所以table为t_classes的执行计划行的type是index,预计读取到20行记录(t_classes表实际有20行记录),预计符合条件的记录百分比是100.00%(filtered字段的值),把从驱动表(t_classes)中获取到的记录数和被驱动表(t_subject)中的记录做匹配,最终预计读取到43718条记录

11 Extra列

Extra列是用来说明一些额外信息的,我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个,我们就不一个一个介绍了,在这只介绍常见的一些额外信息说明

① No tables used: 当查询语句没有FROM子句时将会提示该额外信息,比如:

EXPLAIN EXTENDED select now();
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| 1  | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+

② Impossible WHERE:查询语句的WHERE子句永远为FALSE时将会提示该额外信息,如:

EXPLAIN EXTENDED select t_subject.id from t_subject where 1 = 0;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 1  | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

③ Using index:当我们的查询列表以及搜索条件中只包含属于某个索引的列,也就是在可以使用索引覆盖的情况下,在Extra列将会提示该额外信息,不会再执行回表操作。如以下这个查询中只需要用到idx_name_grade索引而不需要回表操作:

EXPLAIN EXTENDED select classes_name from t_classes where grade = 55;
+----+-------------+-----------+------+--------------------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table     | type | possible_keys            | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------+--------------------------+----------------+---------+-------+------+----------+-------------+
| 1  | SIMPLE      | t_classes | ref  | idx_grade,idx_name_grade | idx_name_grade | 4       | const | 1    | 100.00   | Using index |
+----+-------------+-----------+------+--------------------------+----------------+---------+-------+------+----------+-------------+

④ Using index condition:查找使用了索引,但是需要回表查询数据

EXPLAIN EXTENDED select * from t_student where classes_id > 10;
+----+-------------+-----------+-------+-----------------------------------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | type  | possible_keys                           | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+-------+-----------------------------------------+----------------+---------+------+------+----------+-----------------------+
| 1  | SIMPLE      | t_student | range | idx_classes_id,idx_classes_name_card_no | idx_classes_id | 5       | NULL | 1    | 100.00   | Using index condition |
+----+-------------+-----------+-------+-----------------------------------------+----------------+---------+------+------+----------+-----------------------+

⑤ Using where:当我们使用全表扫描来执行对某个表的查询,并且该语句的WHERE子句中有针对该表的搜索条件时,在Extra列中会提示上述额外信息,如:

EXPLAIN EXTENDED select * from t_student where address = '中国';
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | t_student | ALL  | NULL          | NULL | NULL    | NULL | 8    | 100.00   | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+

⑥ Using join buffer:在连接查询执行过程中,当被驱动表不能有效的利用索引加快访问速度,MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度,如下:

EXPLAIN EXTENDED select * from t_student INNER JOIN t_student_copy1 on t_student.`no` = t_student_copy1.`no` and t_student.classes_id = t_student_copy1.classes_id;show WARNINGS;
+------+-------------+-----------------+------+-----------------------------------------+------+---------+------+------+----------+-------------------------------------------------+
| id   | select_type | table           | type | possible_keys                           | key  | key_len | ref  | rows | filtered | Extra                                           |
+------+-------------+-----------------+------+-----------------------------------------+------+---------+------+------+----------+-------------------------------------------------+
| 1    | SIMPLE      | t_student_copy1 | ALL  | NULL                                    | NULL | NULL    | NULL | 1    | 100.00   |                                                 |
| 1    | SIMPLE      | t_student       | ALL  | idx_classes_id,idx_classes_name_card_no | NULL | NULL    | NULL | 8    | 75.00    | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-----------------+------+-----------------------------------------+------+---------+------+------+----------+-------------------------------------------------+

如上例,先查询出t_student_copy1表中的所有数据(只有一条),然后再循环匹配表t_student中的数据,但是因为不能使用索引,只好退而求其次,使用join buffer减少对表t_student的访问次数,提高性能

⑦ Not exists:当我们使用外连接时,如果WHERE子句中包含要求被驱动表的某个列是NULL值的搜索条件,且那个列是非NULL的,那么在该表的执行计划的Extra列就会提示Not exists额外信息,比如以下SQL,查询出所有未关联学生的班级信息:

EXTENDED select * from t_classes left join t_student on t_student.classes_id = t_classes.id where t_student.id is null;
+----+-------------+-----------+------+-----------------------------------------+------+---------+------+------+----------+-------------------------------------------------------------+
| id | select_type | table     | type | possible_keys                           | key  | key_len | ref  | rows | filtered | Extra                                                       |
+----+-------------+-----------+------+-----------------------------------------+------+---------+------+------+----------+-------------------------------------------------------------+
| 1  | SIMPLE      | t_classes | ALL  | NULL                                    | NULL | NULL    | NULL | 20   | 100.00   |                                                             |
| 1  | SIMPLE      | t_student | ALL  | idx_classes_id,idx_classes_name_card_no | NULL | NULL    | NULL | 8    | 75.00    | Using where; Not exists; Using join buffer (flat, BNL join) |
+----+-------------+-----------+------+-----------------------------------------+------+---------+------+------+----------+-------------------------------------------------------------+

上述查询中t_classes表是驱动表,t_student表是被驱动表,t_student.id列是非空的,而WHERE子句中又包含t_student.id IS NULL的搜索条件,这意味着必定是驱动表的记录在被驱动表中找不到匹配ON子句条件的记录才会把该驱动表的记录加入到最终的结果集,所以对于某条驱动表中的记录来说,如果能在被驱动表中找到1条符合ON子句条件的记录,那么该驱动表的记录就不会被加入到最终的结果集,也就是说我们没有必要到被驱动表中找到全部符合ON子句条件的记录,这样可以稍微节省一点性能。

⑧ Using filesort:如果根据索引列进行排序(order by 索引列)是可以用到索引的,SQL查询引擎会先根据索引列进行排序,然后获取对应记录的主键id执行回表操作,如果排序字段用不到索引则只能在内存中或磁盘中进行排序操作,MySQL把这种在内存或者磁盘上进行排序的方式统称为文件排序(英文名:filesort),如果某个查询需要使用文件排序的方式执行查询,就会在执行计划的Extra列中显示Using filesort,如:

例1: 未使用文件排序

EXPLAIN EXTENDED select id from t_score order by score;
+----+-------------+---------+-------+---------------+-----------+---------+------+--------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+-------+---------------+-----------+---------+------+--------+----------+-------------+
| 1  | SIMPLE      | t_score | index | NULL          | idx_score | 4       | NULL | 201062 | 100.00   | Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+--------+----------+-------------+
EXPLAIN EXTENDED select * from t_score order by id;
+----+-------------+---------+-------+---------------+---------+---------+------+--------+----------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+----------+-------+
| 1  | SIMPLE      | t_score | index | NULL          | PRIMARY | 4       | NULL | 201062 | 100.00   |       |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+----------+-------+

例2: 使用文件排序

EXPLAIN EXTENDED select * from t_score order by subject_id;
+----+-------------+---------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------+----------------+
| 1  | SIMPLE      | t_score | ALL  | NULL          | NULL | NULL    | NULL | 201062 | 100.00   | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------+----------------+

注:Using filesort比较耗费性能,如果有可能尽量优化成使用索引排序

⑨ Using temporary:许多查询的执行过程中,MySQL会借助临时表来完成一些功能,比如去重、排序之类的,比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中,如果不能有效利用索引来完成查询,MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表,在执行计划的Extra列将会显示Using temporary提示,如:

EXPLAIN EXTENDED select distinct subject_name from t_subject;
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------+-----------------+
| 1  | SIMPLE      | t_subject | ALL  | NULL          | NULL | NULL    | NULL | 786925 | 100.00   | Using temporary |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------+-----------------+

接“一文看懂如何分析MySQL Explain(3/3)”

End

本文分享自微信公众号 - 小强的进阶之路(xiaoqiang_code),作者:小强的进阶之路

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-06-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 一文看懂如何分析MySQL Explain(3/3)

    注:t_classes表增加了一个组合索引,字段名`grade`, `classes_name`, `student_num`, `head_teacher_...

    程序员小强
  • 一文看懂如何分析MySQL Explain(1/3)

    在网上经常看到一些写SQL优化经历的文章,看完文章发现懂的不用看,不懂的看不懂,大家都是都在讲调优经历,但是忽略了如何看懂执行计划,如何调优。本文不讲调优经历...

    程序员小强
  • MergeTreeBlockSizePredictor源码解析

    统计数据分布size,便于预测一些变量,如每行字节数,最大列每行字节数,filter比例等。方便计算出将要读取的最佳行数。

    程序员小强
  • mysql explain详解

    Explain简介 本文主要讲述如何通过 explain 命令获取 select 语句的执行计划,通过 explain 我们可以知道以下信息:表的读取顺序,数据...

    butterfly100
  • 一文看懂如何分析MySQL Explain(1/3)

    在网上经常看到一些写SQL优化经历的文章,看完文章发现懂的不用看,不懂的看不懂,大家都是都在讲调优经历,但是忽略了如何看懂执行计划,如何调优。本文不讲调优经历...

    程序员小强
  • 干货 | UT Austin博士生沈彦尧:基于深度主动学习的命名实体识别

    沈彦尧基于亚马逊实习项目延伸探讨了主动学习在深度学习中的应用与思考,并分享了多篇深度主动学习的 ICLR,ICML 文章。

    AI科技评论
  • vCenter 5.5升级到vCenter 6.0实战指导

     从vCenter 5.5升级到vCenter 6.0的过程是比较方便的,直接根据屏幕向导,一步一步完成就好了。不过这里有个小插曲,如何更改VC的独立磁盘模式,...

    孙杰
  • 一文详解生成对抗网络(GAN)的原理,通俗易懂

    用户1737318
  • 通俗理解生成对抗网络(GANs)【下载PDF | 长文】

    GAN网络是近两年深度学习领域的新秀,火的不行,本文旨在浅显理解传统GAN,分享学习心得。现有GAN网络大多数代码实现使用Python、torch等语言,这里,...

    智能算法
  • 一文详解生成对抗网络(GAN)的原理,通俗易懂

    GAN网络是近两年深度学习领域的新秀,火的不行,本文旨在浅显理解传统GAN,分享学习心得。现有GAN网络大多数代码实现使用Python、torch等语言,这里,...

    IT派

扫码关注云+社区

领取腾讯云代金券