MySQL执行策划中的各个参数及含义是什么
发布时间：2022-01-14 13:05:25 所属栏目：MySql教程 来源：互联网
导读：本篇文章给大家分享的是有关MySQL执行计划中的各个参数及含义是什么，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 TABLE 显示这一行的数据是关于哪张表的。 EXPLAIN SELECT * FROM EM
|
本篇文章给大家分享的是有关MySQL执行计划中的各个参数及含义是什么，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 TABLE 显示这一行的数据是关于哪张表的。 EXPLAIN SELECT * FROM EMPLOYEE E ,DEPARTMENT D ,CUSTOMER C WHERE 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 | NULL | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | E | NULL | ALL | NULL | NULL | NULL | 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) Id完全相同，都是1，并且select_type的类型为simple，所以是对单表进行扫描，第一步执行时，操作的数据是c的， 第二步在执行时，操作的数据是e的，最后执行时，操作的数据是d的。 Partitions 如果查询是基于分区表的话, 会显示查询访问的分区。 Type Type表示访问类型，以下为最好的到最差的访问类型的排列 结果值最好到最差： Type：null、system、const、eq_ref、ref、range、index、ALL type要求： 一般来说,保证查询至少达到range级别； 最好能达到ref。 NULL Null是最好的访问方式，MySQL对语句进行优化分解后，在执行阶段用不着访问表或者索引（在索引列中查询最小值）。 EXPLAIN SELECT 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类型的特例。 EXPLAIN SELECT * FROM (SELECT * FROM CUSTOMER LIMIT 1) A; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | <derived2> | 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 D WHERE 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_ID FROM EMPLOYEE E ,DEPARTMENT D WHERE 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，当使用=、<>、>、>=、<、<=、IS NULL、<=>、 BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range。 这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点而结束语另一点，不用扫描全部索引。 EXPLAIN SELECT * FROM EMPLOYEE E WHERE ID BETWEEN 1 AND 3; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | E | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) 查询id在1到3之间的数据，由于id有索引，因此是对该字段的索引的范围扫描，type为range。 Index Full Index Scan 索引全扫描，index与All区别为index类型只遍历索引树,通常比All要快,因为索引文件通常比数据文件要小， all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取。 EXPLAIN SELECT ID FROM EMPLOYEE; +----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | EMPLOYEE | NULL | index | NULL | idx_emp_01 | 5 | NULL | 8 | 100.00 | Using index | +----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 查询表里id字段，需要对整个表进行扫描，但是由于id有字段，所以不用从磁盘去扫描全数据， 只需要对整个索引进行全扫描，此时type值为index，表示对索引的全扫描。 All 将全表进行扫描,从硬盘当中读取数据。 EXPLAIN SELECT * FROM EMPLOYEE; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | EMPLOYEE | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) 对全表数据的查询，此时只能对全表进行扫描，采用全表扫描的方式，此时type的值为all，表示全表扫描。 在对表进行扫描的方式有8种，性能依次为null、system、const、eq_ref、ref、range、index、ALL。 其中all是全表扫描，性能最差；index是索引全扫描，性能也不好，因此，尽量想办法时扫描的type值至少是 变为range以上，走索引，并且是高效的索引。 以上就是MySQL执行计划中的各个参数及含义是什么，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
