刷网站跳出率,tp5做企业类网站,室内设计培训学费,十大网络推广公司概述 能做什么#xff1f; 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 每张表有多少行被优化器查询 官网介绍
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/8.0/…概述 能做什么 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 每张表有多少行被优化器查询 官网介绍
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html 版本情况 MySQL 5.6.3以前只能 EXPLAIN SELECT MYSQL 5.6.3以后就可以 EXPLAIN SELECTUPDATE DELETE 在5.7以前的版本中想要显示 partitions 需要使用 explain partitions 命令想要显示 filtered 需要使用 explain extended 命令。在5.7版本后默认explain直接显示partitions和 filtered中的信息。 基本语法
EXPLAIN 或 DESCRIBE语句的语法形式如下 EXPLAIN SELECT select_options 或者 DESCRIBE SELECT select_options 如果我们想看看某个查询的执行计划的话可以在具体的查询语句前边加一个 EXPLAIN 就像这样
mysql EXPLAIN SELECT 1; EXPLAIN 语句输出的各个列的作用如下 EXPLAIN各列作用
table 不论我们的查询语句有多复杂里边儿 包含了多少个表 到最后也是需要对每个表进行 单表访问 的所 以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法该条记录的table列代表着该 表的表名有时不是真实的表名字可能是简称。 mysql EXPLAIN SELECT * FROM s1; 这个查询语句只涉及对s1表的单表查询所以 EXPLAIN 输出中只有一条记录其中的table列的值为s1表明这条记录是用来说明对s1表的单表访问方法的。下边我们看一个连接查询的执行计划可以看出这个连接查询的执行计划中有两条记录这两条记录的table列分别是s1和s2这两条记录用来分别说明对s1表和s2表的访问方法是什么。 mysql EXPLAIN SELECT * FROM s1 INNER JOIN s2; id
我们写的查询语句一般都以 SELECT 关键字开头比较简单的查询语句里只有一个 SELECT 关键字比如下边这个查询语句
SELECT * FROM s1 WHERE key1 a;
稍微复杂一点的连接查询中也只有一个 SELECT 关键字比如
SELECT * FROM s1 INNER JOIN s2
ON s1.key1 s2.key1
WHERE s1.common_field a;
但是下边两种情况下在一条查询语句中会出现多个SELECT关键字 当查询语句有子查询时用union 来连接时 mysql EXPLAIN SELECT * FROM s1 WHERE key1 a; 对于连接查询来说一个SELECT关键字后边的FROM字句中可以跟随多个表所以在连接查询的执行计划中每个表都会对应一条记录但是这些记录的id值都是相同的比如
mysql EXPLAIN SELECT * FROM s1 INNER JOIN s2; # 查询优化器可能对涉及子查询的查询语句进行重写转变为多表查询的操作。
mysql EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field a); 可以看到虽然我们的查询语句是一个子查询但是执行计划中s1和s2表对应的记录的id值全部是1这就表明查询优化器将子查询转换为了连接查询。
对于包含UNION子句的查询语句来说每个SELECT关键字对应一个id值也是没错的不过还是有点儿特别的东西比方说下边的查询 使用union是把多个查询的结果集合起来并对结果集中的记录进行去重使用临时表的方式对结果集进行去重所以在查询执行计划时就多了一条id为空的记录表示这是为了合并两个查询的结果集而创建的。 # Union去重
mysql EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; mysql EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2; 小结: id如果相同可以认为是一组从上往下顺序执行 在所有组中id值越大优先级越高越先执行 关注点id号每个号码表示一趟独立的查询, 一个sql的查询趟数越少越好 select_type SIMPLE
查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型比方说下边这个单表查询select_type的值就是SIMPLE:
mysql EXPLAIN SELECT * FROM s1; PRIMARY 对于包含UNION、UNION ALL或者子查询的大查询来说它是由几个小查询组成的其中最左边的那个查询的select_type的值就是PRIMARY。从结果中可以看到最左边的小查询SELECT * FROM s1对应的是执行计划中的第一条记录它的select_type的值就是PRIMARY。 mysql EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; UNION 对于包含UNION或者UNION ALL的大查询来说它是由几个小查询组成的其中除了最左边的那个小查询意外其余的小查询的select_type值就是UNION可以对比上一个例子的效果。 UNION RESULT MySQL 选择使用临时表来完成UNION查询的去重工作针对该临时表的查询的select_type就是UNION RESULT, 例子上边有。 SUBQUERY 如果包含子查询的查询语句不能够转为对应的semi-join的形式并且该子查询是不相关子查询并且查询优化器决定采用将该子查询物化的方案来执行该子查询时该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY mysql EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 a; DEPENDENT SUBQUERY 当子查询是相关子查询的时候即子查询内部与外部的表是有关联的 mysql EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 s2.key2) OR key3 a;
DEPENDENT UNION 同关联子查询 mysql EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 a UNION SELECT key1 FROM s1 WHERE key1 b);
DERIVED 从执行计划中可以看出id为2的记录就代表子查询的执行方式它的select_type是DERIVED, 说明该子查询是以物化的方式执行的。id为1的记录代表外层查询大家注意看它的table列显示的是derived2表示该查询时针对将派生表物化之后的表进行查询的。 mysql EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c 1; MATERIALIZED 当查询优化器在执行包含子查询的语句时选择将子查询物化之后的外层查询进行连接查询时该子查询对应的select_type属性就是DERIVED比如下边这个查询 mysql EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); type
执行计划的一条记录就代表着MySQL对某个表的 执行查询时的访问方法 , 又称“访问类型”其中的 type 列就表明了这个访问方法是啥是较为重要的一个指标。比如看到type列的值是ref表明MySQL即将使用ref访问方法来执行对s1表的查询。 完整的访问方法如下 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL 。 system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的比如MyISAM、Memory那么对该表的访问方法就是system。比方说我们新建一个MyISAM表并为其插入一条记录
mysql CREATE TABLE t(i int) EngineMyISAM;
Query OK, 0 rows affected (0.05 sec)mysql INSERT INTO t VALUES(1);
Query OK, 1 row affected (0.01 sec)
然后我们看一下查询这个表的执行计划可以看到type列的值就是system了
mysql EXPLAIN SELECT * FROM t; const
当我们根据主键或者唯一二级索引列与常数进行等值匹配时对单表的访问方法就是const, 比如
mysql EXPLAIN SELECT * FROM s1 WHERE id 10005; eq_ref 在连接查询时如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的如果该主键或者唯一二级索引是联合索引的话所有的索引列都必须进行等值比较。则对该被驱动表的访问方法就是eq_ref。从执行计划的结果中可以看出MySQL打算将s2作为驱动表s1作为被驱动表重点关注s1的访问 方法是 eq_ref 表明在访问s1表的时候可以 通过主键的等值匹配 来进行访问。 mysql EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id s2.id; ref
当通过普通的二级索引列与常量进行等值匹配时来查询某个表那么对该表的访问方法就可能是ref比方说下边这个查询
mysql EXPLAIN SELECT * FROM s1 WHERE key1 a; fulltext 全文索引 ref_or_null 当对普通二级索引进行等值匹配查询该索引列的值也可以是NULL值时那么对该表的访问方法就可能是ref_or_null mysql EXPLAIN SELECT * FROM s1 WHERE key1 a OR key1 IS NULL; index_merge 一般情况下对于某个表的查询只能使用到一个索引但单表访问方法时在某些场景下可以使用Interseation、union、Sort-Union这三种索引合并的方式来执行查询。我们看一下执行计划中是怎么体现MySQL使用索引合并的方式来对某个表执行查询的。从执行计划的 type 列的值是 index_merge 就可以看出MySQL 打算使用索引合并的方式来执行 对 s1 表的查询。 mysql EXPLAIN SELECT * FROM s1 WHERE key1 a OR key3 a; unique_subquery 类似于两表连接中被驱动表的eq_ref访问方法unique_subquery是针对在一些包含IN子查询的查询语句中如果查询优化器决定将IN子查询转换为EXISTS子查询而且子查询可以使用到主键进行等值匹配的话那么该子查询执行计划的type列的值就是unique_subquery比如下边的这个查询语句 mysql EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 s2.key1) OR key3 a; index_subquery index_subquery 与 unique_subquery 类似只不过访问子查询中的表时使用的是普通的索引 range
mysql EXPLAIN SELECT * FROM s1 WHERE key1 IN (a, b, c); 或者
mysql EXPLAIN SELECT * FROM s1 WHERE key1 a AND key1 b; index 当我们可以使用索引覆盖但需要扫描全部的索引记录时该表的访问方法就是index mysql EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 a; ALL 最熟悉的全表扫描就不多说了直接看例子
mysql EXPLAIN SELECT * FROM s1; 小结: 结果值从最好到最坏依次是 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL 其中比较重要的几个提取出来见上图中的粗体。SQL 性能优化的目标至少要达到 range 级别要求是 ref 级别最好是 consts级别。阿里巴巴 开发手册要求 possible_keys和key 在EXPLAIN语句输出的执行计划中possible_keys列表示在某个查询语句中对某个列执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询使用。key列表示实际用到的索引有哪些如果为NULL则没有使用索引。执行计划的possible_keys列的值是idx_key1, idx_key3表示该查询可能使用到idx_key1, idx_key3两个索引然后key列的值是idx_key3表示经过查询优化器计算使用不同索引的成本后最后决定采用idx_key3。 mysql EXPLAIN SELECT * FROM s1 WHERE key1 z AND key3 a; key_len
实际使用到的索引长度 (即字节数)帮你检查是否充分的利用了索引值越大越好主要针对于联合索引有一定的参考意义。
mysql EXPLAIN SELECT * FROM s1 WHERE id 10005; int 占四个字节 mysql EXPLAIN SELECT * FROM s1 WHERE key2 10126; key2上有一个唯一性约束是否为NULL占用一个字节那么就是5个字节 mysql EXPLAIN SELECT * FROM s1 WHERE key1 a; key1 VARCHAR(100) 一个字符占3个字节100*3是否为NULL占用一个字节varchar的长度信息占两个字节。 mysql EXPLAIN SELECT * FROM s1 WHERE key_part1 a; mysql EXPLAIN SELECT * FROM s1 WHERE key_part1 a AND key_part2 b; ref ref列展示得就是与索引列作等值匹配的数据结构是什么到底是常数还是 mysql EXPLAIN SELECT * FROM s1 WHERE key1 a; 可以看到ref列的值是const表明在使用idx_key1索引执行查询时与key1列作等值匹配的对象是一个常数当然有时候更复杂一点:
mysql EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id s2.id; mysql EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 UPPER(s1.key1); rows
预估的需要读取的记录条数值越小越好。
mysql EXPLAIN SELECT * FROM s1 WHERE key1 z; filtered 某个表经过搜索条件过滤后剩余记录条数的百分比如果使用的是索引执行的单表扫描那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。 mysql EXPLAIN SELECT * FROM s1 WHERE key1 z AND common_field a; 对于单表查询来说这个filtered的值没有什么意义我们更关注在连接查询中驱动表对应的执行计划记录的filtered值它决定了被驱动表要执行的次数 (即: rows * filtered)
mysql EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 s2.key1 WHERE s1.common_field a; 从执行计划中可以看出来查询优化器打算把s1作为驱动表s2当做被驱动表。我们可以看到驱动表s1表的执行计划的rows列为9688filtered列为10.00这意味着驱动表s1的扇出值就是9688 x 10.00% 968.8这说明还要对被驱动表执行大约968次查询。 Extra 顾名思义Extra列是用来说明一些额外信息的包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个我们就不一个一个介绍了所以我们只挑选比较重要的额外信息介绍给大家。 No tables used
当查询语句没有FROM子句时将会提示该额外信息比如
mysql EXPLAIN SELECT 1; Impossible WHERE
当查询语句的WHERE子句永远为FALSE时将会提示该额外信息
mysql EXPLAIN SELECT * FROM s1 WHERE 1 ! 1; Using where 表示该查询语句使用了非索引字段的查询条件即使使用了索引有非索引的查询条件也是如此。 mysql EXPLAIN SELECT * FROM s1 WHERE key1 a AND common_field a; No matching min/max row 当查询列表处有MIN或者MAX聚合函数但是并没有符合WHERE子句中的搜索条件的记录时。 mysql EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 abcdefg; Using index 当我们的查询列表以及搜索条件中只包含属于某个索引的列也就是在可以使用覆盖索引的情况下在Extra列将会提示该额外信息。比方说下边这个查询中只需要用到idx_key1而不需要回表操作。 mysql EXPLAIN SELECT key1 FROM s1 WHERE key1 a; Using index condition
有些搜索条件中虽然出现了索引列但却不能使用到索引比如下边这个查询
SELECT * FROM s1 WHERE key1 z AND key1 LIKE %a; 模糊查询会使索引失效之前的处理是先处理key1这个范围查询然后立马根据范围查询所得到的二级索引去进行回文匹配。现在的处理逻辑是处理完范围查询后就对得到的二级索引判断是否满足模糊查询的条件如果不符合的就不进行回表操作这样省略了大量回表操作减少随机IO。 这个改进就是索引条件下推当mysql使用了索引条件下推就会显示Using index condition Using join buffer (Block Nested Loop) 在连接查询执行过程中当被驱动表不能有效的利用索引加快访问速度MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度也就是我们所讲的基于块的嵌套循环算法。 mysql EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field s2.common_field; Not exists 当我们使用左(外)连接时如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件而且那个列是不允许存储NULL值的那么在该表的执行计划的Extra列就会提示这个信息。 mysql EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 s2.key1 WHERE s2.id IS NULL; Using intersect(...) 、 Using union(...) 和 Using sort_union(...) 如果执行计划的Extra列出现了Using intersect(...)提示说明准备使用Intersect索引合并的方式执行查询括号中的...表示需要进行索引合并的索引名称如果出现Using union(...)提示说明准备使用Union索引合并的方式执行查询;如果出现Using sort_union(...)提示说明准备使用Sort-Union索引合并的方式执行查询。 mysql EXPLAIN SELECT * FROM s1 WHERE key1 a OR key3 a; Zero limit
当我们的LIMIT子句的参数为0时表示压根儿不打算从表中读取任何记录将会提示该额外信息
mysql EXPLAIN SELECT * FROM s1 LIMIT 0; Using filesort 有一些情况下对结果集中的记录进行排序是可以使用到索引的。如果在内存或者磁盘中进行排序那就统称为文件排序。需要注意的是如果查询中需要使用filesort的方式进行排序的记录非常多那么这个过程是很耗费性能的我们最好想办法将使用文件排序的执行方式改为索引进行排序。 mysql EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10; mysql EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10; Using temporary mysql EXPLAIN SELECT DISTINCT common_field FROM s1; mysql EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field; 执行计划中出现Using temporary并不是一个好的征兆因为建立与维护临时表要付出很大的成本的所以我们最好能使用索引来替代掉使用临时表比方说下边这个包含GROUP BY子句的查询就不需要使用临时表 mysql EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
