如何领会MySQL执行计划中的各个参数及意义
发布时间：2022-01-13 14:45:10 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家介绍如何理解MySQL执行计划中的各个参数及含义，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。 本文是对于MySQL执行计划的解析，主要解释了MySQL执行计划中的各个参数及含义。 possible_keys 显示可能应用在这张表中
|
这篇文章给大家介绍如何理解MySQL执行计划中的各个参数及含义，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。 本文是对于MySQL执行计划的解析，主要解释了MySQL执行计划中的各个参数及含义。 possible_keys 显示可能应用在这张表中的索引,一个或者多个； 查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用(可能自己创建了4个索引,在执行的时候, 可能根据内部的自动判断,只使用了3个)。 先创建索引 CREATE INDEX IDX_EMP_01 ON employee(dep_id); CREATE INDEX IDX_EMP_02 ON employee(dep_id,NAME); 查看索引 mysql> show index from employee; +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | employee | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | | BTREE | | | YES | NULL | | employee | 1 | IDX_EMP_01 | 1 | dep_id | A | 4 | NULL | NULL | YES | BTREE | | | YES | NULL | | employee | 1 | IDX_EMP_02 | 1 | dep_id | A | 4 | NULL | NULL | YES | BTREE | | | YES | NULL | | employee | 1 | IDX_EMP_02 | 2 | name | A | 8 | NULL | NULL | YES | BTREE | | | YES | NULL | +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 4 rows in set (0.06 sec) 可以看到表上有3个索引。 进行查询，并且查看执行计划： EXPLAIN SELECT ID FROM EMPLOYEE WHERE DEP_ID = 1 AND NAME = '鲁班'; +----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | EMPLOYEE | NULL | ref | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_02 | 68 | const,const | 1 | 100.00 | Using index | +----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.05 sec) 表上有三个索引，其中索引IDX_EMP_01是dep_id的单列索引，IDX_EMP_02是列dep_id和列name的复合索引， 在进行查询时，可能会用到索引，因为有过滤条件dep_id=1，所以会考虑使用这两个索引，因为索引的第一列都是dep_id， 此时的possiable_keys为IDX_EMP_01和IDX_EMP_02。 key 实际使用的索引,如果为NULL,则没有使用索引 ; 查询中若使用了覆盖索引 ,则该索引仅出现在key列表中。 possible_keys与key关系：possiable_keys表示理论应该用到哪些索引，key表示实际用到了哪些索引。 还是和上面一样： EXPLAIN SELECT ID FROM EMPLOYEE WHERE DEP_ID = 1 AND NAME = '鲁班'; +----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | EMPLOYEE | NULL | ref | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_02 | 68 | const,const | 1 | 100.00 | Using index | +----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.05 sec) 在possiable_keys里显示了两个索引，但是呢，并不是这两个索引都会使用，用哪一个需要由优化器自己决定。 通过key可以发现，使用的是IDX_EMP_02这个索引，因为where里同时有dep_id和name条件， 因此使用这个索引最为合理，效率最高。 通过key可以发现，对该表的真实的索引的使用情况。 key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。 EXPLAIN SELECT ID FROM EMPLOYEE WHERE DEP_ID = 1; +----+-------------+----------+------------+------+-----------------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+-----------------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | EMPLOYEE | NULL | ref | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_01 | 5 | const | 3 | 100.00 | Using index | +----+-------------+----------+------------+------+-----------------------+------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 可以看到只使用索引的第一列时，长度为5个字节。 EXPLAIN SELECT ID FROM EMPLOYEE WHERE DEP_ID = 1 AND NAME = '鲁班'; +----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | EMPLOYEE | NULL | ref | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_02 | 68 | const,const | 1 | 100.00 | Using index | +----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.05 sec) 对于使用了整个索引时，显示的是该索引的大小，key_len由5变成了68，说明name这个字段在索引里占用的字节数为63。 并且，key_len显示的值是where里的索引的具体大小，不包括order by或者group by时使用的索引。 ref 索引是否被引入到, 到底引用到了哪几个索引。 ref列用来显示使用哪个列或常数与key一起从表中选择相应的行。它显示的列的名字（或const），此列多数时候为null。 EXPLAIN SELECT D.ID ,D.ADDRESS FROM DEPARTMENT D ,EMPLOYEE E WHERE D.ID = E.DEP_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 | ref | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_01 | 5 | demo.D.id | 2 | 100.00 | Using index | +----+-------------+-------+------------+------+-----------------------+------------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.01 sec) Ref是用来表示索引是否被使用，到底用了哪个索引，从执行计划可看到，对表d进行扫描时，没有使用索引，type为all， 是一个全表扫描，因此ref值为null。对表e的扫描时，type为ref，表示使用了索引，并且通过key可以看到确实使用了索引， 因此在ref列需要被标记，索引被引用了，并且把该索引引用了到了d表的id字段上。 Rows rows列显示的是mysql解析器认为执行此SQL时必须扫描的行数。此数值为一个预估值，不是具体值，通常比实际值小。 没有建索引前： EXPLAIN SELECT D.ID ,E.DEP_ID FROM DEPARTMENT D ,EMPLOYEE E WHERE D.ID = E.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 | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) 通过执行计划可以看到对表d采用索引全扫的扫描方式，需要对整个索引进行扫描；对表e采用全表扫描的方式， 因此需要对整个数据集进行扫描，d表有5行，e表有8行，因此在rows里显示为5和8. 对e表的dep_id列创建索引： create index idx_employee_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_employee_01 | idx_employee_01 | 5 | demo.D.id | 2 | 100.00 | Using index | +----+-------------+-------+------------+-------+-----------------+-----------------+---------+-----------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) 通过执行计划可以看到，对d表采用索引全扫的方式，需要对整个索引进行扫描；对表e采用ref的非唯一性索引扫描， 只需要扫描索引的部分数据，d表有5行数据，所以rows为5，对于e表，扫描满足条件的数据，rows为2。 表d的数据： +----+----------------+---------+ | id | deptName | address | +----+----------------+---------+ | 1 | 研发部(RD) | 2层 | | 2 | 人事部(HR) | 3层 | | 3 | 市场部(MK) | 4层 | | 4 | 后勤部(MIS) | 5层 | | 5 | 财务部(FD) | 6层 | +----+----------------+---------+ 5 rows in set (0.07 sec) 表e的数据： +----+-----------+--------+------+---------+--------+ | id | name | dep_id | age | salary | cus_id | +----+-----------+--------+------+---------+--------+ | 1 | 鲁班 | 1 | 10 | 1000.00 | 1 | | 2 | 后裔 | 1 | 20 | 2000.00 | 1 | | 3 | 孙尚香 | 1 | 20 | 2500.00 | 1 | | 4 | 凯 | 4 | 20 | 3000.00 | 1 | | 5 | 典韦 | 4 | 40 | 3500.00 | 2 | | 6 | 貂蝉 | 6 | 20 | 5000.00 | 1 | | 7 | 孙膑 | 6 | 50 | 5000.00 | 1 | | 8 | 蔡文姬 | 30 | 35 | 4000.00 | 1 | +----+-----------+--------+------+---------+--------+ 8 rows in set (0.00 sec) 对d表的id和e表的dep_id查询： +----+--------+ | ID | DEP_ID | +----+--------+ | 1 | 1 | | 1 | 1 | | 1 | 1 | | 4 | 4 | | 4 | 4 | +----+--------+ 5 rows in set (0.00 sec) Ref是对传过来的数据进行索引的扫描，d表的id有5行，e的dep_id值有多个，在进行等值匹配时，只有1和4满足条件。 对表d先进性操作，先扫描这5行数据，然后把1和4传给e表，所以对于e表只需要对1和4进行索引的扫描，只需要扫描两行。 Filtered 满足查询的记录数量的比例，注意是百分比，不是具体记录数；值越大越好，filtered列的值依赖统计信息，并不十分准确。 对全表进行扫描时： 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) 进行全表扫描时，会对所有的数据进行扫描，此时filtered为100. 当进行范围查询时： EXPLAIN SELECT * FROM EMPLOYEE WHERE DEP_ID > 4; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 | 33.33 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 对dep_id大于4的数据进行查询，也是全表扫描，返回的数据为3行，优化器估计返回了三分之一的数据，标记为33.33。 再一次进行查询： EXPLAIN SELECT * FROM EMPLOYEE WHERE DEP_ID > 1; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 | 33.33 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 对dep_id大于1的数据进行查询，也是全表扫描，返回的数据为5行，但是还是被认定filterd为33.33。 关于如何理解MySQL执行计划中的各个参数及含义就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。 （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐