MySQL执行计划解析
发布时间：2022-03-29 11:38:55 所属栏目：编程 来源：互联网
导读：本文是对于MySQL执行计划的解析，主要解释了MySQL执行计划中的各个参数及含义。 产生的值 存在六种情况： Using filesort、Using temporary、use index、using where、using join buffer、impossible where 1、Using filesort 说明mysql会对数据使用一个外部
|
本文是对于MySQL执行计划的解析，主要解释了MySQL执行计划中的各个参数及含义。 产生的值 存在六种情况： Using filesort、Using temporary、use index、using where、using join buffer、impossible where 1、Using filesort 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行， Mysql中无法利用索引完成排序操作称为"文件排序"。 EXPLAIN SELECT * FROM EMPLOYEE ORDER BY SALARY; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 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 | Using filesort | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.02 sec) 根据salary字段进行排序，并且salary字段上没有索引，那么此时会使用文件排序，extra为using filesort。 2、Using temporary 使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,常见于排序orderby 和分组查询group by。 查看dep_id的数据分布： EXPLAIN SELECT DEP_ID ,COUNT(*) FROM EMPLOYEE GROUP BY DEP_ID; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 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 | Using temporary | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.01 sec) 对dep_id进行了group by，此时需要对中间的结果进行保存，所以会使用临时表，extra为using temporary。 3、use index 表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好。 如果同时出现using where，表明索引被用来执行索引键值的查找； 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。 对工资升序查找数据（有索引）： EXPLAIN SELECT SALARY FROM EMPLOYEE ORDER BY SALARY; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 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 | Using filesort | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) 使用salary进行排序，并且有索引，那么通过该列的索引即可查出数据，索引只是用来读取数据的，也避免了排序， 此时extra显示为using index。 4、using where 表明使用了where过滤（过滤字段没有索引或者不能使用索引）。 Dep_id字段没有索引。 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 | 12.50 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 当有使用where进行过滤时，在extra里使用using where表示。 dep_id创建索引： CREATE INDEX idx_emp_02 ON employee ( dep_id ); 当可用正常使用索引时： 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 | range | idx_emp_02 | idx_emp_02 | 5 | NULL | 5 | 100.00 | Using index condition | +----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) 通过索引进行访问，标记为Using index condition。 不可以使用索引时： EXPLAIN SELECT * FROM EMPLOYEE WHERE DEP_ID + 1 > 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 | 100.00 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 标记为Using where 5、Using index condition 在使用where条件的索引时，会标记为Using index condition。 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 | range | idx_emp_02 | idx_emp_02 | 5 | NULL | 5 | 100.00 | Using index condition | +----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) 通过索引进行访问，标记为Using index condition。 6、impossible where where条件导致没有返回的行。 EXPLAIN SELECT * FROM EMPLOYEE WHERE ID IS NULL; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.00 sec) Id是主键，所以不会有空值，在进行一个id为空的查询时，是没有结果的，因此extra会表示为impossible where。 （编辑：河北网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读