手机搭建网站,网站建设保报价文档,seo快速排名利器,商机创业网2023创业1 基础
全网最全 | MySQL EXPLAIN 完全解读
1.1 MySQL中EXPLAIN命令提供的字段包括#xff1a;
id#xff1a;查询的标识符。select_type#xff1a;查询的类型#xff08;如SIMPLE, PRIMARY, SUBQUERY等#xff09;。table#xff1a;查询的是哪个表。partitions
id查询的标识符。select_type查询的类型如SIMPLE, PRIMARY, SUBQUERY等。table查询的是哪个表。partitions查询中涉及的分区。typejoin类型如ALL, index, range等。possible_keys可能用于此查询的索引。key实际使用的索引。key_len使用的索引的长度。ref哪些列或常量被用于查找索引列上的值。rows估计要检查的行数。filtered按表条件过滤的行百分比。Extra关于查询执行的额外信息。
2 关于id字段的疑惑
2.1 id越大越先执行吗不完全对
记住id字段更多地表示查询中的逻辑顺序而不一定完全代表物理执行顺序。在涉及复杂查询尤其是嵌套子查询和UNION的情况下理解id字段对于分析查询性能至关重要。
在MySQL的EXPLAIN命令中id字段表示查询中各个SELECT子句的执行顺序或层级。理解id字段有时可能会有些复杂但以下是一些基本的规则和示例 相同的id具有相同id值的行表示它们是同一层级的查询部分通常是因为JOIN操作或UNION。 递增的id通常id值较小的SELECT会先执行id值较大的后执行。但在涉及子查询的情况下子查询较大的id实际上会先执行因为它们的结果通常需要被外层查询较小的id所使用。 子查询子查询的id通常大于主查询的id。子查询先执行因为外层查询依赖它们的结果。 UNION在UNION中第一个SELECT的id最小随后的SELECTid逐渐增大。
示例 简单的JOIN查询 EXPLAIN SELECT * FROM table1 JOIN table2 ON table1.id table2.id;这里可能会看到两行输出每行的id都是1表示这是一个单一层级的查询。 包含子查询 EXPLAIN SELECT * FROM (SELECT * FROM table1) AS subquery JOIN table2 ON subquery.id table2.id;这里可能会看到两行输出子查询SELECT * FROM table1的id可能是2而外层的JOIN查询的id可能是1。尽管id值较大子查询实际上会先执行。 UNION查询 EXPLAIN SELECT * FROM table1 UNION SELECT * FROM table2;在这种情况下第一个SELECT的id可能是1第二个SELECT的id可能是2。
3 关于select_type字段的疑惑
3.1 select_type有哪些字段值
在MySQL的EXPLAIN命令输出中select_type字段用于描述SELECT语句的类型。以下是select_type可能的值及其含义 SIMPLE简单的SELECT不涉及子查询或UNION。 PRIMARY查询中最外层的SELECT。 SUBQUERYSELECT中的子查询。 DEPENDENT SUBQUERY子查询中的SELECT其结果依赖于外部查询。 UNIONUNION操作中的第二个或后续的SELECT。 DEPENDENT UNIONUNION中的第二个或后续的SELECT其结果依赖于外部查询。 UNION RESULTUNION查询的结果。 DERIVED派生表的SELECT例如出现在FROM子句中的子查询。
这些不同的select_type值有助于理解复杂查询的执行过程和性能表现。
3.2 针对select_type的字段值你能各给一个例子吗
当然以下是select_type字段的各种值及其相应的SQL查询示例 SIMPLE SELECT * FROM table1 WHERE column1 value;这是一个没有子查询或UNION的简单查询。 PRIMARY SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2);这里最外层的SELECT是PRIMARY。 SUBQUERY SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2);这里(SELECT column1 FROM table2)是一个SUBQUERY。 DEPENDENT SUBQUERY SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2 WHERE table2.column2 table1.column2);子查询依赖于外部查询的结果。 UNION SELECT * FROM table1 UNION SELECT * FROM table2;第二个SELECT和任何后续的SELECT被视为UNION。 DEPENDENT UNION 这种情况较为复杂通常涉及到外部查询影响UNION部分的子查询。 UNION RESULT SELECT * FROM (SELECT * FROM table1 UNION SELECT * FROM table2) AS union_result;这里UNION的结果被作为派生表处理。 DERIVED SELECT * FROM (SELECT * FROM table1) AS derived_table;这里内部的SELECT是一个派生表查询。
这些例子展示了不同select_type值在实际查询中的应用。理解这些类型有助于分析和优化复杂的SQL查询。
4 索引type字段
4.1 explain的type字段有哪些
在MySQL的EXPLAIN命令输出中type字段是一个非常重要的指标它显示了MySQL决定使用哪种方式来访问表。以下是type字段可能的值按性能从好到坏排序
system表只有一行数据等同于系统表是const类型的一个特例。const表示通过索引一次就找到了适用于主键或唯一索引的等值查询const 查询速度非常快因为最多只返回一行数据。eq_ref一对一关系唯一索引扫描用于主键或唯一索引的关联查询。ref非唯一索引扫描返回匹配某个单个值的所有行。fulltext全文索引。ref_or_null类似ref但是MySQL会额外搜索包含NULL值的行。index_merge表示使用了索引合并优化。unique_subquery在IN子句中用到的唯一索引查询该类型和eq_ref类似但是使用了IN查询且子查询是主键或者唯一索引。index_subquery在IN子句中用到的非唯一索引查询和unique_subquery类似只是子查询使用的是非唯一索引。range只检索给定范围的行使用一个索引来选择行。范围扫描表示检索了指定范围的行主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有、、、、IS NULL、、BETWEEN、LIKE、IN()等操作符。index全索引扫描。all 查询条件没有索引需要全表扫描 index全索引扫描和ALL类似只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时可使用此类型。有两种场景会触发
如果索引是查询的覆盖索引并且索引查询的数据就可以满足查询中所需的所有数据则只扫描索引树。此时explain的Extra 列的结果是Using index。index通常比ALL快因为索引的大小通常小于表数据。按索引的顺序来查找数据行执行了全表扫描。此时explain的Extra列的结果不会出现Uses index
ALL全表扫描性能最差。
4.2 以下是针对MySQL EXPLAIN中type字段的每个值的SQL查询示例
当然以下是针对MySQL EXPLAIN中type字段的每个值的SQL查询示例
system-- 假设table1只有一行数据
SELECT * FROM table1;const-- 假设id为主键
SELECT * FROM table WHERE id 1;eq_ref-- 假设table2的id是table1的外键
SELECT * FROM table1 JOIN table2 ON table1.id table2.id;ref-- 假设column是非唯一索引
SELECT * FROM table WHERE column value;fulltext-- 假设对column列有全文索引
SELECT * FROM table WHERE MATCH(column) AGAINST(text);ref_or_null-- 假设column是非唯一索引且可能包含NULL
SELECT * FROM table WHERE column value OR column IS NULL;index_merge-- 假设对column1和column2有单独的索引
SELECT * FROM table WHERE column1 value1 OR column2 value2;unique_subquery-- 假设id为唯一索引
SELECT * FROM table WHERE id IN (SELECT id FROM table2 WHERE column value);index_subquery-- 假设column为非唯一索引
SELECT * FROM table WHERE column IN (SELECT column FROM table2 WHERE column2 value);range-- 假设对column有索引
SELECT * FROM table WHERE column BETWEEN value1 AND value2;index-- 假设对column有索引
SELECT column FROM table;ALL-- 无索引的全表扫描
SELECT * FROM table;这些示例演示了如何在不同情况下生成特定的type值帮助理解EXPLAIN输出中的这些类型。
4.3 如何理解这里的index
index全索引扫描和ALL类似只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时可使用此类型。有两种场景会触发
如果索引是查询的覆盖索引并且索引查询的数据就可以满足查询中所需的所有数据则只扫描索引树。此时explain的Extra 列的结果是Using index。index通常比ALL快因为索引的大小通常小于表数据。按索引的顺序来查找数据行执行了全表扫描。此时explain的Extra列的结果不会出现Uses index。
当然这里是两个触发index查询类型的MySQL示例 使用覆盖索引的查询 假设user表有一个覆盖索引idx_name_age包含name和age列。查询只涉及到这两列因此MySQL可以只扫描索引而不是整个表SELECT name, age FROM user USE INDEX (idx_name_age);这个查询中EXPLAIN的结果可能会在Extra列显示Using index。 按索引顺序的全表扫描 假设user表有一个索引idx_age在age列上。这个查询虽然使用了age索引但需要返回表中的其他未索引列导致全表扫描SELECT name, age FROM user ORDER BY age;在这种情况下EXPLAIN的Extra列不会显示Using index因为它需要访问表中未被索引的数据。
在第一个例子中查询可以完全通过索引来满足而在第二个例子中虽然查询利用了索引的顺序但仍然需要访问表中的额外数据。这两种情况都会触发index类型的查询但由于不同的使用方式它们在性能上可能有所差异。
5 一些其他的结果列
5.1 possible_keys中有哪些值
展示当前查询可以使用哪些索引这一列的数据是在优化过程的早期创建的因此有些索引可能对于后续优化过程是没用的。
5.1.1 key
表示MySQL实际选择的索引
5.1.2 key_len
索引使用的字节数。由于存储格式当字段允许为NULL时key_len比不允许为空时大1字节。 key_len计算公式 https://www.cnblogs.com/gomysql/p/4004244.html
5.1.3 ref
表示将哪个字段或常量和key列所使用的字段进行比较。 如果ref是一个函数则使用的值是函数的结果。要想查看是哪个函数可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。
5.1.4 rows
MySQL估算会扫描的行数数值越小越好。
5.1.5 filtered
表示符合查询条件的数据百分比最大100。用rows × filtered可获得和下一张表连接的行数。例如rows 1000filtered 50%则和下一张表连接的行数是500。
TIPS 在MySQL 5.7之前想要显示此字段需使用explain extended命令 MySQL.5.7及更高版本explain默认就会展示filtered
6 extra列
6.1 比较关键的几个信息字段
在MySQL的EXPLAIN命令输出中Extra列提供了关于查询执行的附加信息这些信息有助于理解MySQL是如何处理查询的。以下是一些关键的Extra字段及其含义的详细介绍 Using index这表示查询仅通过索引来获取所需的数据而无需额外读取表中的数据行。这通常发生在覆盖索引的情况下即查询的所有字段都包含在索引中。 Using where这表示MySQL服务器在从存储引擎接收到数据行后使用了额外的WHERE条件来进一步过滤结果。 Using index condition这种情况下查询的某些列可能未被完全覆盖在索引中。MySQL会首先使用索引来过滤数据然后使用WHERE子句中的其余条件进一步筛选这些已过滤的数据行。 Using temporary这表示MySQL为了处理查询需要创建临时表。这种情况常见于需要对结果进行排序或分组的查询如使用DISTINCT、GROUP BY或某些JOIN操作。 Using filesortMySQL使用了外部排序方法来对结果进行排序而不是直接通过索引排序。当数据量较小时排序可能在内存中完成但较大数据量可能需要磁盘排序。出现这种情况时通常考虑使用索引来优化排序操作。 Select tables optimized away这通常发生在使用聚合函数如MAX()或MIN()查询索引列时。MySQL优化器能够直接从索引中获取所需的最大或最小值而无需实际扫描表中的行。
这些Extra字段值提供了查询优化和性能调整的重要线索。通过理解这些值的含义可以更好地分析查询效率并在必要时进行相应的优化。
6.2 分别给我举一个例子
当然这里是MySQL EXPLAIN命令中Extra列关键字段的示例 Using index -- 假设在name字段上有索引
EXPLAIN SELECT name FROM users WHERE name Alice;
-- 这个查询可能显示Using index因为它只涉及到索引字段Using where EXPLAIN SELECT * FROM users WHERE age 25 AND name Alice;-- 假设在name字段上有索引但没有在age字段上。这里MySQL会使用索引找到所有名为Alice的行然后在返回的结果上应用age 25的条件。Using index condition索引下推技术 -- 假设在nameage)字段上有联合索引
EXPLAIN SELECT name, age FROM users WHERE name LIKE A% AND age 18;
-- 这个查询可能显示Using index conditionUsing temporary -- 假设name字段没有索引
EXPLAIN SELECT DISTINCT name FROM users;
-- 这个查询可能显示Using temporary因为需要创建临时表来处理DISTINCTUsing filesort -- 假设name字段没有索引
EXPLAIN SELECT * FROM users ORDER BY name;
-- 这个查询可能显示Using filesort因为需要对结果进行排序Select tables optimized away -- 假设id字段是主键
EXPLAIN SELECT MIN(id) FROM users;
-- 这个查询可能显示Select tables optimized away因为可以直接从索引中获得最小值这些示例展示了不同Extra字段值的典型应用场景。通过这些示例您可以更好地理解EXPLAIN输出中这些值的含义及其对查询优化的指导意义。
6.3 对于index级别以上的是不是都会导致extra的using index
在MySQL中EXPLAIN命令的type字段描述了表的访问类型而Extra字段则提供了关于查询执行的额外信息。type字段中index级别以上的访问类型并不总是导致Extra字段显示Using index。Using index在Extra字段中出现的具体情况取决于查询是否满足某些特定条件 Using index出现在Extra字段中的条件是 查询操作仅涉及索引中的列即是所谓的“覆盖索引”。即使查询类型是index全索引扫描但如果查询的列不完全在索引中Using index就不会出现。 对于type字段中不同级别的类型Using index的出现情况如下 const、eq_ref这些类型通常表示索引被有效利用但是否使用Using index取决于查询的列是否完全由索引覆盖。ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range这些类型中的Using index出现也同样依赖于查询是否为覆盖索引的情况。index尽管表示全索引扫描但Using index仅当查询列完全由索引覆盖时才会出现。ALL此类型通常表示全表扫描不会出现Using index因为它不依赖于索引来检索数据。
因此并非type字段中的index级别以上的访问类型都会导致Extra字段出现Using index。是否出现Using index取决于查询是否能够仅通过索引来获取所需数据而不需要访问表的数据行。
