微信订阅号做微网站,广州花都区网站建设,怀化工程建设信息网老网站,公司刚做网站在那里找图片做有了慢查询语句后#xff0c;就要对语句进行分析。一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划#xff0c;这个执行计划展示了接下来具体执行查询的方式#xff0c;比如多表连接的顺序是什么#xff0c;对于每个表采用什么访…有了慢查询语句后就要对语句进行分析。一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划这个执行计划展示了接下来具体执行查询的方式比如多表连接的顺序是什么对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN 语句来能够查看某个查询语句的具体执行计划要搞懂 EPLATN 的各个输出项都有什么作用从而可以有针对性的提升查询语句的性能。
通过使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或是表结构的性能瓶颈。
EXPLAIN 可以得到以下结果
表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询
EXPLAIN 基本语法
执行计划的语法非常简单在 SQL 查询的前面加上 EXPLAIN 关键字。比如EXPLAIN select * from tableName;
除了以 SELECT 开头的查询语句其余的 DELETE、INSERT、UPOATE 语句前边都可以加上 EXPLAIN用来查看这些语句的执行计划只不过大多数情况下都会对 SELECT 语句更感兴趣。
EXPLAIN 详解
执行 sql explain SELECT * FROM order_test; 输出结果如图 每列的含义
id
查询语句一般都以 SELECT 关键字开头比较简单的查询语句里只有一个 SELECT 关键字稍微复杂一点的连接查询中也只有一个 SELECT 关键字比如:
explain SELECT * FROM test1 t1 INNER JOIN test2 t2 ON t1.idt2.id ;
但是下边两种情况下在一条查询语句中会出现多个 SELECT 关键字 查询中包含子查询的情况explain SELECT * FROM test1 WHERE id IN(SELECT id FROM test2); 查询中包含 UNION / UNION ALL 语句的情况EXPLAIN SELECT * FROM test1 UNION ALL SELECT * FROM test2;
查询语句中每出现一个 SELECT 关键字MySQL 就会为它分配一个唯一的 id 值。这个 id 值就是 EXPLAIN 语句的第一列的值并且 id 的顺序是按 SELECT 出现的顺序增长的id 列越大执行优先级越高id 相同则从上往下执行id 为 NULL 最后执行。
单 SELECT 关键字
比如下边这个查询中只有一个 SELECT 关键字所以 EXPLAIN 的结果中也就只有一条 id 列为 1 的记录。 连接查询 对于连接查询来说一个 SELECT 关键字后边的 FROM 子句中可以跟随多个表所以在连接查询的执行计划中每个表都会对应一条记录但是这些记录的 id 值都是相同的。 可以看到上述连接查询中参与连接的 test1 和 test2 表分别对应一条记录但是这两条记录对应的 id 值都是 1在连接查询的执行计划中每个表都会对应一条记录这些记录的 id 列的值是相同的。
包含子查询
对于包含子查询的查询语句来说就可能涉及多个 SELECT 关键字所以在包含子查询的查询语句的执行计划中每个 SELECT 关键字都会对应一个唯一的 id 值。 但是这里需要特别注意查询优化器可能对涉及子查询的查询语句进行重写从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写直接查看执行计划。 可以看到虽然查询语句是一个子查询但是执行计划中 test1 和 test2 表对应的记录的 id 值全部是 1这就表明了查询优化器将子查询转换为了连接查询。
包含 UNION \ UNION ALL 子句
对于包含 UNION 子句的查询语句来说每个 SELECT 关键字对应一个 id 值也是没错的不过还是有点儿特别。 这个语句的执行计划的第三条记录是因为 UNION 子句会把多个查询的结果集合并起来并对结果集中的记录进行去重MySQL 使用的是内部的临时表。UNION 子句是为了把 id 为 1 的查询和 id 为 2 的查询的结果集合并起来并去重所以在内部创建了一个名为 union12 的临时表就是执行计划第三条记录的 table 列的名称id 为 NULL 表明这个临时表是为了合并两个查询的结果集而创建的。
跟 UNION 对比起来 UNION ALL 就不需要为最终的结果集进行去重它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户所以也就不需要使用临时表。所以在包含 UNION ALL 子句的查询的执行计划中就没有那个 id 为 NULL 的记录。 select_type
一条大的查询语句里边可以包含若干个 SELECT 关键字每个 SELECT 关键字代表着一个小的查询语句而每个 SELECT 关键字的 FROM 子句中都可以包含若干张表每一张表都对应着执行计划输出中的一条记录对于在同一个 SELECT 关键字中的表来说它们的 id 值是相同的。
MySQL 为每一个 SELECT 关键字代表的小查询都定义了一个称之为select_type 的属性意思是我们只要知道了某个小查询的 select_type 属性就知道了这个小查询在整个大查询中扮演了一个什么角色select_type 取值如下
SIMPLE简单的 SELECT 查询不使用 union 及子查询PRIMARY最外层的 SELECT 查询UNIONUNION 中的第二个或随后的 SELECT 查询不依赖于外部查询的结果集UNION RESULTUNION 结果集SUBQUERY子查询中的第一个 SELECT 查询,不依赖于外部查询的结果集DERIVED 用于 FROM 子句里有子查询的情况MySQL 会递归执行这些子查询把结果放在临时表里
SIMPLE
简单的 select 查询查询中不包含子查询或者 UNION。 连接查询也算是 SIMPLE 类型。 PRIMARY
对于包含 UNION、UNION ALL 或者子查询的大查询来说它是由几个小查询组成的其中最左边的那个查询的 select_type 值就是 PRIMARY。 从结果中可以看到最左边的小查询 SELECT * FROMN test1 对应的是执行计划中的第一条记录它的 select_type 值就是 PRIMARY。
UNION
对于包含 UNION 或者 UNION ALL 的大查询来说它是由几个小查询组成的其中除了最左边的那个小查询以外其余的查询的 select_type 值就是 UNION。 UNION RESULT
MySQL 选择使用临时表来完成 UNION 查询的去重工作针对该临时表的查询的 select_type 就是 UNION RESULT如上图。
SUBQUERY
包含在 SELECT 中的子查询不在 FROM 子句中。 DERIVED
包含在 FROM 子句中的子查询。MySQL 会将结果存放在一个临时表中也称为派生表。 从执行计划中可以看出 id 为 2 的记录就代表子查询的执行方式它的 select_type 是 DERIVED 说明该子查询是以派生表的方式执行的。id 为 1 的记录代表外层查询注意看它的 table 列显示的是 derived2表示该查询是针对将派生表之后的表进行查询的。
table
不论我们的查询语句有多复杂里边包含了多少个表到最后也是需要对每个表进行单表访问的MySQL 规定 EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法该条记录的 table 列代表着该表的表名。 只涉及对 test1 表的单表查询所以 EXPLAIN 输出中只有一条记录其中的 table 列的值是 test1 连接查询的执行计划中有两条记录这两条记录的 table 列分别是 test1和 test2。
partitions
和分区表有关一般情况下查询语句的执行计划的 partitions 列的值都是NULL。
type
执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法或访问类型其中的 type 列就表明了这个访问方法或访问类型是较为重要的一个指标结果值从最好到最坏依次是system const eq_ref ref range index ALL。
一般来说得保证查询至少达到 range 级别最好能达到 ref。
system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的比如MyISAM、Memory那么对该表的访问方法就是 system。 如果改成使用 InnoDB 存储引擎type 的值就是 all。 const
根据主键或者唯一二级索引列与常数进行等值匹配时对单表的访问方法就是 const。因为只匹配一行数据所以很快。 B Tree 叶子节点中的记录是按照索引列排序的对于的聚簇索引来说它对应的B树叶子节点中的记录就是按照id列排序的。B树矮胖所以这样根据主键值定位一条记录的速度很快。类似的我们根据唯一二级索引列来定位一条记录的速度也很快的比如下边这个查询
eq_ref
在连接查询时如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的如果该主键或者唯一二级索引是联合索引的话所有的索引列都必须进行等值比较则对该被驱动表的访问方法就是eq_ref。 从执行计划的结果中可以看出MySQL 打算将 test2 作为驱动表test1 作为被驱动表重点关注 test1 的访问方法是 eq_ref表明在访问 test1 表的时候可以通过主键的等值匹配来进行访问。
驱动表与被驱动表A 表和 B 表 join 连接查询如果通过 A 表的结果集作为循环基础数据然后一条一条地通过该结果集中的数据作为过滤条件到 B 表中查询数据然后合并结果。那么我们称 A 表为驱动表B 表为被驱动表。
ref
当通过普通的二级索引列与常量进行等值匹配时来查询某个表那么对该表的访问方法就可能是 ref。
本质上也是一种索引访问它返回所有匹配某个单独值的行它可能会找到多个符合条件的行所以他属于查找和扫描的混合体。 对于这个查询可以选择全表扫描来逐一对比搜索条件是否满足要求也可以先使用二级索引找到对应记录的 id 值然后再回表到聚簇索引中查找完整的用户记录。
由于普通二级索引并不限制索引列值的唯一性所以可能找到多条对应的记录也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。
如果匹配的记录较少则回表的代价还是比较低的所以 MySQL 可能选择使用索引而不是全表扫描的方式来执行查询。这种搜索条件为二级索引列与常数等值比较采用二级索引来执行查询的访问方法称为ref。
对于普通的二级索引来说通过索引列进行等值比较后可能匹配到多条连续的记录而不是像主键或者唯一二级索引那样最多只能匹配 1 条记录所以这种 ref 访问方法比 const 要差些但是在二级索引等值比较时匹配的记录数较少时的效率还是很高的如果匹配的二级索引记录太多那么回表的成本就太大了。
对于某个包含多个索引列的二级索引来说只要是最左边的连续索引列是与常数的等值比较就可能采用 ref 的访问方法。
range
如果使用索引获取某些范围区间的记录那么就可能使用到 range 访问方法一般就是在 where 语句中出现了 between、、、in 等的查询。
这种范围扫描索引比全表扫描要好因为它只需要开始于索引的某一点而结束语另一点不用扫描全部索引。 这种利用索引聚簇索引、二级索引进行范围匹配的访问方法称之为range。
index
可以使用索引覆盖但需要扫描全部的索引记录时该表的访问方法就是index。 ALL
最熟悉的全表扫描将遍历全表以找到匹配的行。 possible_keys 和 key
在 EXPLAIN 语句输出的执行计划中possible_keys 列表示在某个查询语句中对某个表执行单表查询时可能用到的索引有哪些。key 列表示实际用到的索引有哪些如果为 NULL则没有使用索引。 上述执行计划的 possible_keys 列的值表示该查询可能使用到 u_idx_day_status、idx_insert_time 两个索引然后 key 列的值是 u_idx_day_status表示经过查询优化器计算使用不同索引的成本后最后决定使用 u_idx_day_status 来执行查询比较划算。
key_len
key_len 列表示当优化器决定使用某个索引执行查询时该索引记录的最大长度计算方式如下
对于使用固定长度类型的索引列来说它实际占用的存储空间的最大长度就是该固定值对于指定字符集的变长类型的索引列来说如某个索引列的类型是VARCHAR(100)使用的字符集是 utf8那么该列实际占用的最大存储空间就是100x3300个字节如果该索引列可以存储 NULL 值则 key_len 比不可以存储 NULL 值时多 1 个字节对于变长字段来说都会有 2 个字节的空间来存储该变长列的实际长度 由于 id 列的类型是 bigint并且不可以存储 NULL 值所以在使用该列的索引时 key_len 大小就是 8。
对于可变长度的索引列来说。 由于 order_no 列的类型是 VARCHAR(50)所以该列实际最多占用的存储空间就是 50*3 字节又因为该列是可变长度列所以 key_len 需要加2所以最后ken_len 的值就是 152。
执行计划的生成是在 MySQL server 层中的功能并不是针对具体某个存储引擎的功能MySQL 在执行计划中输出 key_len 列主要是为了区分某个使用联合索引的查询具体用了几个索引列而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用几个字节。
key_len 表示索引中使用的字节数可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下长度越短越好。
key_len 显示的值为索引字段的最大可能长度并非实际使用长度即 key_len 是根据表定义计算而得不是通过表内检索出的。
注char 和 varchar 跟字符编码也有密切的联系比如 latin1 占用 1 个字节gbk 占用 2 个字节utf8 占用 3 个字节。
ref
当使用索引列等值查询时与索引列进行等值匹配的对象信息 可以看到 ref 列的值是 const表明在使用 idx_order_no 索引执行查询时与 order_no 列作等值匹配的对象是一个常数。
复杂的情况。 可以看到对被驱动表 t1 的访问方法是 eg_ref而对应的 ref 列的值是 test.t2.id这说明在对被驱动表进行访问时会用到 PRIMARY 索引也就是聚簇索引与一个列进行等值匹配的条件与 t2 表的 id 作等值匹配的对象就是 test.t2.id列格式数据库名称.表名.字段。
rows
如果查询优化器决定使用全表扫描的方式对某个表执行查询时执行计划的 rows 列就代表预计需要扫描的行数如果使用索引来执行查询时执行计划的 rows 列就代表预计扫描的索引记录行数。 filtered
某个表经过搜索条件过滤后剩余记录条数的百分比 从执行计划的 key 列中可以看出来该查询使用 PRIMARY 索引来执行查询从 rows 列可以看出满足 id5890 的记录有 5177 条。执行计划的 filtered 列就代表查询优化器预测在这 5177 条记录中有多少条记录满足其余的搜索条件也就是 order_notea 这个条件的百分比。此处 filtered 列的值是 10.0说明查询优化器预测在 5177 条记录中有 10.00% 的记录满足 order_notea 这个条件。
Extra
Extra 列是用来说明一些额外信息的可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。MySQL 提供的额外信息很多常见的重要值如下
Using index当我们的查询列表以及搜索条件中只包含属于某个索引的列也就是在可以使用索引覆盖的情况下在 Extra 列将会提示该额外信息。 这个查询中只需要用到 u_idx_day_status 而不需要回表操作。
Using where当我们使用全表扫描来执行对某个表的查询并且该语句的 WHERE 子句中有针对该表的搜索条件时在 Extra 列中会提示。 Using where 只是表示 MySQL 使用 where 子句中的条件对记录进行了过滤。
Using index condition有些搜索条件中虽然出现了索引列但却不能使用到索引。 其中的 order_no z 可以使用到索引但是 order_no LIKE %a 却无法使用到索引在以前版本的 MySQL 中是按照下边步骤来执行这个查询的
先根据 order_noz 这个条件从二级索引 idx_order_no 中获取到对应的二级索引记录根据上一步骤得到的二级索引记录中的主键值进行回表找到完整的用户记录再检测该记录是否符合 order_no LIKE %a 这个条件将符合条件的记录加入到最后的结果集
虽然 order_no LIKE %a 不能组成范围区间参与 range 访问方法的执行但这个条件毕竟只涉及到了 order_no 列MySQL 把上边的步骤改进了一下。
索引条件下推
先根据 order_noz 这个条件定位到二级索引 idx_order_no 中对应的二级索引记录对于指定的二级索引记录先不着急回表而是先检测一下该记录是否满足 order_no LIKE %a 这个条件如果这个条件不满足则该二级索引记录压根儿就没必要回表对于满足 order_no LIKE %a 这个条件的二级索引记录执行回表操作回表操作其实是一个随机 IO 比较耗时
所以上述修改可以省去很多回表操作的成本这个改进称之为索引条件下推。
如果在查询语句的执行过程中将要使用索引条件下推这个特性在 Extra 列中将会显示 Using index condition。
Using temporary在许多查询的执行过程中MySQL 可能会借助临时表来完成一些功能比如去重、排序之类的比如在执行许多包含 DISTINCT、GROUPBY、UNION 等子句的查询过程中如果不能有效利用索引来完成查询MySQL 很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表在执行计划的 Extra 列将会显示 Using temporary。 上边的 GROUP BY 的执行计划的 Extra 列不仅仅包含 Using temporary 提示还包含 Using filesort 提示可是查询语句中明明没有写 ORDER BY 子句这是因为 MySQL 会在包含 GROUP BY 子句的查询中默认添加上 ORDER BY 子句。
如果不想为包含 GROUP BY 子句的查询进行排序需要显式的写上 ORDER BY NULL。 Using filesort有一些情况下对结果集中的记录进行排序是可以使用到索引的。 这个查询语句可以利用 idx_order_no 索引直接取出 order_no 列的 10 条记录然后再进行回表操作。但是很多情况下排序操作无法使用到索引只能在内存中或者磁盘中进行排序MySQL 把这种在内存中或者磁盘上进行排序的方式统称为文件排序。如果某个查询需要使用文件排序的方式执行查询就会在执行计划的Extra 列中显示 Using filesort。 Select tables optimized away使用某些聚合函数比如max、min 来访问存在索引的某个字段是。
