营销型网站手机端,网站服务体系,php 做网站xml地图,wordpress小程序文章目录 一、前言二、如何查看执行计划三、执行计划各字段解释四、select_type4.1、SIMPLE#xff08;简单查询#xff09;4.1.1、简单的单表查询4.1.2、多表连接查询 4.2、PRIMARY#xff08;主查询#xff09;4.2.1、包含复杂子查询的外层查询4.2.2、UNION语句中的第一个… 文章目录 一、前言二、如何查看执行计划三、执行计划各字段解释四、select_type4.1、SIMPLE简单查询4.1.1、简单的单表查询4.1.2、多表连接查询 4.2、PRIMARY主查询4.2.1、包含复杂子查询的外层查询4.2.2、UNION语句中的第一个查询 4.3、SUBQUERY子查询、DEPENDENT SUBQUERY依赖子查询、UNCACHEABLE SUBQUERY不可缓存子查询4.3.1、SUBQUERY子查询4.3.2、DEPENDENT SUBQUERY依赖子查询4.3.3、UNCACHEABLE SUBQUERY不可缓存子查询 4.4、UNION合并查询、UNION RESULT合并查询结果、DEPENDENT UNION依赖合并查询、UNCACHEABLE UNION不可缓存合并查询4.4.1、UNION合并查询、UNION RESULT合并查询结果4.4.2、DEPENDENT UNION依赖合并查询4.4.3、UNCACHEABLE UNION不可缓存合并查询 4.5、DERIVED派生表4.6、MATERIALIZED物化 五、type5.1、system5.2、const5.3、eq_ref5.4、ref5.5、fulltext5.6、ref_or_null5.7、index_merge5.8、unique_subquery5.9、index_subquery5.10、range5.11、index5.12、ALL 六、Extra6.1、Using index6.2、Using where6.3、Using temporary6.4、Using filesort6.5、Using index condition 一、前言
每次遇到 sql 优化查看执行计划其中的字段是什么含义总是会忘所以有了这篇文章方便查阅。
二、如何查看执行计划
第一种方法在 SELECT 语句前面加上 EXPLAIN 关键字 第三种方法如果用 Navicat 数据库工具的话点击上面的解释按钮 三、执行计划各字段解释
先贴上 Mysql 官方文档中关于执行计划字段解释的地址
官网MySQL 8.0 Reference Manual / Optimization / Understanding the Query Execution Plan / EXPLAIN Output Format
中文网MySQL 8.0 参考手册 / 第8章优化 / 8.8 了解查询执行计划 / 8.8.2 EXPLAIN 输出格式
字段说明id操作的唯一标识符select_type操作的类型table涉及的表名partitions操作涉及的分区type表示使用的连接类型或扫描类型possible_keys可能使用的索引列表key实际选择使用的索引key_len索引键的长度ref连接条件所使用的列或常量rows估计的扫描行数filtered从结果中过滤返回行的百分比Extra额外的信息
下面就结合一些实际例子看下 select_type、type、Extra
准备脚本如下 – 创建用户表 CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT , name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , age int(11) NULL DEFAULT NULL , dept_id int(11) NULL DEFAULT NULL , remark text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , PRIMARY KEY (id), INDEX index_name_age (name, age) USING BTREE , INDEX index_dept_id (dept_id) USING BTREE , FULLTEXT INDEX index_remark (remark) ) ENGINEInnoDB DEFAULT CHARACTER SETutf8 COLLATEutf8_general_ci AUTO_INCREMENT38 ROW_FORMATDYNAMIC ; – 初始化用户数据 INSERT INTO user (id, name, age, dept_id, remark) VALUES (‘1’, ‘张三’, ‘20’, ‘1’, ‘abc’); INSERT INTO user (id, name, age, dept_id, remark) VALUES (‘2’, ‘李四’, ‘18’, ‘1’, ‘bcd’); INSERT INTO user (id, name, age, dept_id, remark) VALUES (‘3’, ‘王五’, ‘22’, ‘1’, ‘cde’); INSERT INTO user (id, name, age, dept_id, remark) VALUES (‘4’, ‘赵六’, ‘23’, ‘2’, ‘def’); INSERT INTO user (id, name, age, dept_id, remark) VALUES (‘5’, ‘孙七’, ‘25’, ‘2’, ‘efg’); INSERT INTO user (id, name, age, dept_id, remark) VALUES (‘6’, ‘周八’, ‘16’, ‘2’, ‘fgh’); INSERT INTO user (id, name, age, dept_id, remark) VALUES (‘7’, ‘吴九’, ‘19’, ‘3’, ‘ghi’); INSERT INTO user (id, name, age, dept_id, remark) VALUES (‘8’, ‘郑十’, ‘13’, ‘3’, ‘hij’); – 创建部门表 CREATE TABLE dept ( id int(11) NOT NULL , name varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , PRIMARY KEY (id), INDEX index_name (name) USING BTREE ) ENGINEInnoDB DEFAULT CHARACTER SETutf8 COLLATEutf8_general_ci ROW_FORMATDYNAMIC ; – 初始化部门数据 INSERT INTO dept (id, name) VALUES (‘1’, ‘人事部’); INSERT INTO dept (id, name) VALUES (‘2’, ‘财务部’); INSERT INTO dept (id, name) VALUES (‘3’, ‘技术部’); 四、select_type
select_type说明SIMPLE简单查询PRIMARY主查询外部查询SUBQUERY子查询DEPENDENT SUBQUERY依赖子查询UNCACHEABLE SUBQUERY不可缓存子查询UNION合并查询UNION RESULT合并查询结果DEPENDENT UNION依赖合并查询UNCACHEABLE UNION不可缓存合并查询DERIVED派生表MATERIALIZED物化
下面针对各种情况举例不用纠结sql本身的业务含义
4.1、SIMPLE简单查询
不包含UNION或者子查询
4.1.1、简单的单表查询 EXPLAIN SELECT * FROM user; 4.1.2、多表连接查询 EXPLAIN SELECT a.* , b.* FROM USER a INNER JOIN dept b ON b.id a.dept_id; 4.2、PRIMARY主查询
包含复杂子查询的外层查询或者UNION语句中的第一个查询
4.2.1、包含复杂子查询的外层查询 EXPLAIN SELECT * FROM ( SELECT * FROM USER WHERE id 1 UNION ALL SELECT * FROM USER WHERE id 2 ) x 4.2.2、UNION语句中的第一个查询 EXPLAIN SELECT * FROM USER WHERE id 1 UNION ALL SELECT * FROM USER WHERE id 2 4.3、SUBQUERY子查询、DEPENDENT SUBQUERY依赖子查询、UNCACHEABLE SUBQUERY不可缓存子查询
SUBQUERY子查询子查询是指嵌套在主查询中的查询语句。当 Mysql 优化器将子查询作为单独的查询执行并将结果作为主查询的条件之一时select_type 会被标记为 SUBQUERY 。这种情况下子查询会在主查询之前执行并将结果传递给主查询使用。DEPENDENT SUBQUERY依赖子查询当一个子查询需要根据外部查询的结果来确定查询条件或提供必要的数据时它就被称为依赖子查询。依赖子查询会在外部查询的每一行上执行并根据外部查询的结果进行计算。UNCACHEABLE SUBQUERY不可缓存子查询优化器可以根据查询中的各种条件和索引信息来对查询进行优化以获得更好的性能。然而某些类型的子查询由于其特殊的性质无法被优化器缓存。
4.3.1、SUBQUERY子查询
如果是SUBQUERY子查询需要满足以下条件
子查询不能转化为半连接关于半连接可参考8.2.2.1 使用半连接转换优化 IN 和 EXISTS 子查询谓词子查询是不相关子查询
这里补充下不相关子查询和相关子查询的含义 不相关子查询子查询与主查询之间没有依赖关系独立于主查询执行只执行一次然后将结果用于主查询的条件或者计算 相关子查询子查询与主查询之间存在依赖关系子查询的结果依赖于主查询的每一行数据 EXPLAIN SELECT * FROM USER WHERE age (SELECT avg(age) FROM USER) 4.3.2、DEPENDENT SUBQUERY依赖子查询
如果是DEPENDENT SUBQUERY依赖子查询需要满足以下条件
子查询不能转化为半连接子查询是相关子查询
只要把上面例子里的 改成 IN子查询就会变成相关子查询 EXPLAIN SELECT * FROM USER WHERE age IN (SELECT avg(age) FROM USER) 4.3.3、UNCACHEABLE SUBQUERY不可缓存子查询
当子查询中使用了不支持查询缓存的函数会导致该子查询被标记为不可缓存 EXPLAIN SELECT ( SELECT id FROM USER ORDER BY RAND() LIMIT 1 ) AS random_user FROM DUAL; 4.4、UNION合并查询、UNION RESULT合并查询结果、DEPENDENT UNION依赖合并查询、UNCACHEABLE UNION不可缓存合并查询
UNION合并查询在 UNION 语句中第二个及之后的 select 会标记成 UNION。UNION RESULT合并查询结果当执行 UNION 操作时MySQL 可能会生成一个 UNION RESULT 来存储合并后的结果集以供后续处理使用这个结果集就会标记为 UNION RESULT。DEPENDENT UNION依赖合并查询依赖于外部查询的 UNION 操作UNION 的结果取决于外部查询的结果无法独立地进行计算通常表现为子查询中第二个及之后的 select。UNCACHEABLE UNION不可缓存合并查询和 UNCACHEABLE SUBQUERY不可缓存子查询类似表示一个无法通过查询缓存来进行优化的 UNION 操作。
4.4.1、UNION合并查询、UNION RESULT合并查询结果 EXPLAIN SELECT * FROM USER WHERE id 1 UNION SELECT * FROM USER WHERE id 2 4.4.2、DEPENDENT UNION依赖合并查询
当UNION作为子查询时第二个或者后面的查询语句 EXPLAIN SELECT * FROM USER WHERE id IN ( SELECT id FROM USER WHERE NAME ‘张三’ UNION ALL SELECT id FROM USER WHERE NAME ‘李四’ ); 4.4.3、UNCACHEABLE UNION不可缓存合并查询 EXPLAIN SELECT * FROM USER WHERE id IN ( SELECT id FROM USER WHERE NAME ‘张三’ UNION SELECT id FROM USER WHERE NAME ‘张三’ ORDER BY RAND() ) 4.5、DERIVED派生表
在查询中使用子查询生成的临时表 EXPLAIN SELECT u.dept_id, u.avgAge FROM ( SELECT dept_id, avg(age) avgAge FROM USER WHERE id 3 GROUP BY dept_id ) AS u WHERE u.avgAge 20; 4.6、MATERIALIZED物化
当查询包含子查询或派生表并且优化器认为将其结果保存到临时表中会更有效率时就会使用物化表。使用物化表可以避免在每次引用子查询或派生表时都需要重新计算结果集从而提高查询性能。特别是当子查询或派生表的结果集较大或计算复杂时。
五、type
type说明system该表只有一行相当于系统表system是const类型的特例const键或唯一索引的等值查询最多只返回一行数据eq_ref当使用了索引的全部组成部分并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型ref如果仅使用了索引的最左边前缀或者索引不是PRIMARY KEY或UNIQUEfulltext全文索引ref_or_null和ref类似但是会额外搜索哪些行包含了NULLindex_merge使用索引合并优化unique_subquery和eq_ref类似但是使用了IN查询且子查询是主键或者唯一索引index_subquery和unique_subquery类似只是子查询使用的是非唯一索引range仅检索给定范围内的行使用索引来选择行可以在使用 , , , , , , IS NULL, , BETWEEN, LIKE, 或 IN()等操作符。index与ALL类似只是扫描了索引树ALL全表扫描
性能从优到劣依次为
system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
5.1、system
该表只有一行相当于系统表system是const类型的特例
5.2、const
键或唯一索引的等值查询最多只返回一行数据 EXPLAIN SELECT * FROM USER WHERE id 1 5.3、eq_ref
当使用了索引的全部组成部分并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型 EXPLAIN SELECT u.* , d.* FROM USER u INNER JOIN dept d ON d.id u.dept_id WHERE u. NAME ‘张三’; 5.4、ref
如果仅使用了索引的最左边前缀或者索引不是PRIMARY KEY或UNIQUE EXPLAIN SELECT * FROM USER WHERE NAME ‘张三’; 5.5、fulltext
全文索引 EXPLAIN SELECT * FROM USER WHERE MATCH (remark) AGAINST (‘c*’ IN boolean MODE); 5.6、ref_or_null
和ref类似但是会额外搜索哪些行包含了NULL EXPLAIN SELECT * FROM USER WHERE NAME ‘zhangsan’ OR NAME IS NULL; 5.7、index_merge
使用索引合并优化 EXPLAIN SELECT * FROM USER WHERE id ‘1’ OR NAME ‘张三’; 5.8、unique_subquery
和eq_ref类似但是使用了IN查询且子查询是主键或者唯一索引
5.9、index_subquery
和unique_subquery类似只是子查询使用的是非唯一索引
5.10、range
仅检索给定范围内的行使用索引来选择行可以在使用 , , , , , , IS NULL, , BETWEEN, LIKE, 或 IN()等操作符。 EXPLAIN SELECT * FROM USER WHERE id 5 5.11、index
与ALL类似只是扫描了索引树
5.12、ALL
全表扫描
六、Extra
这里列出一些常见的其他的参考官方文档
6.1、Using index
查询使用了覆盖索引查询的所有列都可以从索引中获取而不需要回表查询数据行。 EXPLAIN SELECT id FROM USER WHERE id 1; 6.2、Using where
在执行查询时会使用WHERE子句对结果进行进一步的筛选或者全表扫描 EXPLAIN SELECT * FROM USER WHERE age 18; 6.3、Using temporary
为了解析查询MySQL 需要创建一个临时表来保存结果。 EXPLAIN SELECT NAME FROM USER WHERE id 1 UNION SELECT NAME FROM USER WHERE id 2 6.4、Using filesort
无法使用索引或其他优化方式直接按照查询语句中的顺序返回结果而是需要额外的排序操作。会对性能产生一定影响特别是对大数据量的查询语句。 EXPLAIN SELECT * FROM USER ORDER BY age LIMIT 10; 6.5、Using index condition
表示查询使用了索引条件过滤数据通常表示使用了索引下推是一个好的优化迹象 索引下推指 MySQL 将 WHERE 条件中可用于索引的部分推到存储引擎层级进行处理而不是等到存储引擎返回数据行后再应用 WHERE 条件进行过滤。这样可以减少 IO 操作和数据传输提高查询性能。 EXPLAIN SELECT * FROM USER WHERE NAME ‘张三’ AND age 18;
