Shulou(Shulou.com)05/31 Report--

This article is to share with you about the various parameters and meaning of the MySQL implementation plan, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

TABLE

Shows which table the data for this row is about.

EXPLAIN SELECT * FROM EMPLOYEE E, DEPARTMENT D, CUSTOMER CWHERE E.DEP_ID = D.ID AND E.CUS_ID = C.ID +-- + -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+- -+ | 1 | SIMPLE | C | NULL | ALL | PRIMARY | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | E | | NULL | ALL | NULL | 8 | 12.50 | Using where | Using join buffer (Block Nested Loop) | | 1 | SIMPLE | D | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.E.dep_id | 1 | 100.00 | NULL | +- +- -+ 3 rows in set 1 warning (0.04 sec)

The Id is exactly the same, all 1, and the type of select_type is simple, so the single table is scanned. When the first step is performed, the data of the operation is c

When the second step is executed, the data of the operation is e, and finally, when it is executed, the data of the operation is d.

Partitions

If the query is based on a partition table, the partition accessed by the query is displayed.

Type

Type represents the access type, and the following is a list of the best to the worst access types

The result is from the best to the worst:

Type:null 、 system 、 const 、 eq_ref 、 ref 、 range 、 index 、 ALL

Type requirements:

In general, ensure that the query reaches at least the range level

It is better to reach ref.

NULL

Null is the best access method, and after MySQL optimizes the decomposition of the statement, there is no need to access the table or index at execution time (query the minimum value in the index column).

EXPLAINSELECT MIN (ID) FROM EMPLOYEE WHERE ID

< 10;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+| 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 |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+1 row in set, 1 warning (0.03 sec 可以看到此时的type的类型为null，不经过扫描表或者索引，访问的效率是最高的 SYSTEM SYSTEM表示表里只有一行记录时，const类型的特例。 EXPLAINSELECT * FROM (SELECT * FROM CUSTOMER LIMIT 1) A;+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+| 1 | PRIMARY | | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL || 2 | DERIVED | CUSTOMER | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+2 rows in set, 1 warning (0.03 sec) 先执行的id=2的，把子查询里的结果放在临时表里，该临时表只有1行记录，然后在外部查询该临时表， 此时id=1对应的type的值为system。 Const 确定只有一行匹配的时候，mysql优化器会在查询前读取它并且只读取一次，速度非常快； const用于比较primary 或者 unique索引；直接查询主键或者唯一索引。 EXPLAIN SELECT * FROM EMPLOYEE WHERE ID = 1;+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | EMPLOYEE | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec) 在查询时，使用了id列的主键索引(或者唯一索引)，是唯一的，所以type的结果为const。 System和const对应的记录都只有一条，system对应的是表的记录只有一条；const对应的是通过主键索引或者唯一索引， 只查询到表的记录的一条，两个值都对应一条记录，但是const对应的表的记录不一定只有一行，大部分情况下， const的情况很多，system很少。 Eq_ref 对于每个来自于前面的表的行组合，从该表中读取一行，常用在一个索引是unique key或者primary key； 对于每个索引键,表中只有一条记录与之匹配。 EXPLAIN SELECT *FROM EMPLOYEE E ,DEPARTMENT DWHERE E.ID = D.ID;+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+| 1 | SIMPLE | D | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | NULL || 1 | SIMPLE | E | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.D.id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+2 rows in set, 1 warning (0.00 sec) Id相同，从上往下执行，先对表d进行扫描，然后扫描e；d扫描的结果id的字段与e扫描的结果id进行连接查询， 由于e的id字段有主键索引，所以对与该表的扫描的type为eq_ref。 Ref 对于来自前面的表的行组合，所有有匹配索引值的行都从这张表中读取,如果联接只使用键的最左边的前缀， 或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。 ref可以用于使用=或操作符的带索引的列； 非唯一性索引扫描,返回匹配某个单独值的所有行； 本质上也是一种索引访问； 它返回所有匹配某个单独值的行； 可能会找到多个符合条件的行,； 所以它应该属于查找和扫描的混合体。 查询employee表dep_id和department表的id字段。SELECT E.DEP_ID AS E_DEP_ID ,D.ID AS D_IDFROM EMPLOYEE E ,DEPARTMENT DWHERE E.DEP_ID = D.ID;+----------+------+| E_DEP_ID | D_ID |+----------+------+| 1 | 1 || 1 | 1 || 1 | 1 || 4 | 4 || 4 | 4 |+----------+------+5 rows in set (0.01 sec)查看该执行计划：+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+| 1 | SIMPLE | E | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where || 1 | SIMPLE | D | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.E.dep_id | 1 | 100.00 | Using index |+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec) 对于表e的扫描的type为all，全表扫描。 在e表的dep_id字段建立索引： create index idx_emp_01 on employee(dep_id);再次查看执行计划：+----+-------------+-------+------------+-------+---------------+------------+---------+-----------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+------------+---------+-----------+------+----------+-------------+| 1 | SIMPLE | D | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using index || 1 | SIMPLE | E | NULL | ref | idx_emp_01 | idx_emp_01 | 5 | demo.D.id | 2 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+------------+---------+-----------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec) 对于e表的type由all变成了ref，表示使用了索引，但是由于dep_id的值1多个重复值，因此会根据该值查询出多个记录， 所以该type为ref，表示使用了索引，但是由于存在重复值，因此只能是ref。 Range 索引范围扫描：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。 key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL，当使用=、、>

, > =,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.