最近在进行MySQL的调优工作,针对MySQL调优进行了更加详细的深入了解。
在 MySQL 中,EXPLAIN
语句可以用于分析 SQL 查询的执行计划。执行 EXPLAIN
后,MySQL 会返回一张表,其中每一列都提供了关于查询执行方式的信息。
以下是每一列表示的含义,以及每列中可能出现的情况:
1. id
- 表示查询中 SELECT 的序列号,表示查询中各个部分的执行顺序。
- 值为数字:数字越大优先级越高,先执行。
- 相同的 id:这些查询可以并行执行。
- id 不同:按照 id 大小顺序依次执行。
- 子查询或联合查询时:复杂查询可能会有多个 id。
优化思路:
- 相同的 id:当多个查询具有相同的 id 时,表示它们可以并行执行,通常更高效。
- id 数字越小:虽然大 id 的优先级更高,但小 id 的操作通常是简单的查询或过滤,执行更快。
2. select_type
- 表示查询的类型,描述查询是简单查询还是复杂查询(如子查询等)。
- SIMPLE:简单的 SELECT 查询,不包含子查询或 UNION。
- PRIMARY:查询中的最外层 SELECT。
- UNION:UNION 中的第二个或后续的 SELECT 查询。
- DEPENDENT UNION:类似 UNION,但依赖于外部查询。
- SUBQUERY:子查询中的 SELECT。
- DEPENDENT SUBQUERY:子查询中的 SELECT,依赖于外部查询。
- DERIVED:用于 FROM 子句中的子查询。
优化思路:
- SIMPLE:表示简单查询,不包含子查询或 UNION,执行效率高。
- PRIMARY:最外层查询,通常是主要的查询部分。
- 复杂查询(如
DEPENDENT SUBQUERY
、DEPENDENT UNION
)效率较低,因为它们依赖外部查询。
3. table
- 表示输出行中对应的表名,表明 MySQL 正在处理的表。如果是子查询,这里可能会显示临时表的别名。
优化思路:
- 表名:如果是实际表而不是临时表,通常执行效率更高。使用临时表(
DERIVED
或tmp
)的查询效率较低。
4. partitions
- 表示查询涉及到的分区。如果未分区,该列显示
NULL
。
5. type
- 表示表的访问方式,反映了查询中使用的连接类型或扫描方法。性能从好到差的类型如下:
- system:表中只有一行(常数表)。
- const:最多有一个匹配行,用于主键或唯一索引。
- eq_ref:对于每个来自前一个表的行,最多只匹配一行。用于主键或唯一索引扫描。
- ref:对于每个来自前一个表的行,可能会找到多行匹配。常用于非唯一索引扫描。
- fulltext:使用全文索引进行查询。
- ref_or_null:类似于
ref
,但也查找NULL
值。 - index_merge:使用索引合并优化。
- unique_subquery:优化
IN
子查询时使用的类型。 - range:使用索引扫描,仅扫描指定范围的行。
- index:全索引扫描,扫描索引树中的所有叶节点。
- ALL:全表扫描,性能最差。
这个列最能直接反映查询的执行效率,不同的 type
反映了表的访问方式。按效率从高到低排序:
- system:效率最高,表示表中只有一行。
- const:效率高,用于主键或唯一索引的查询。
- eq_ref:效率高,表示通过主键或唯一索引查找每个记录。
- ref:效率较高,使用非唯一索引扫描,可以匹配多行。
- range:索引范围扫描,效率不错,但取决于扫描范围大小。
- index:扫描整个索引,效率一般,性能比全表扫描好,但仍可能读取大量数据。
- ALL:全表扫描,效率最低,通常需要优化。
6. possible_keys
- 表示 MySQL 执行查询时可能使用的索引。如果没有可能的索引,这列会显示
NULL
。
优化思路:
- 列出多个可能的索引:如果有多个可能的索引,表示查询可以选择高效的索引来执行。最好能明确指定最优的索引。
7. key
- 表示 MySQL 实际使用的索引。如果未使用索引,这列显示
NULL
。
8. key_len
- 表示 MySQL 使用的索引的长度(字节数)。该值越小,效率越高。计算方式与索引的字段类型和长度有关。
优化思路:
- 较小的 key_len 值:表示使用的索引长度较短,通常会提高效率。越短的索引键查找效率越高。
9. ref
- 表示使用哪一列或常量与
key
中的索引列进行比较。可能的值:- const:与常量比较。
- 字段名:与特定字段进行比较。
优化思路:
- 常量或主键:如果该列的值为
const
或指向主键列,表示查询效率更高。
10. rows
- 估计要读取的行数。这个值是基于统计信息的估计值,并不一定是精确的。
优化思路:
- 较小的 rows 值:表示 MySQL 估计需要处理的行数越少,查询效率越高。这个值反映了需要扫描的行数,越少越好。
11. filtered
- 估算通过条件过滤后剩余的行数百分比。值越大,意味着更多的行满足条件,反之亦然。
优化思路:
- 较高的 filtered 值:表示经过
WHERE
子句过滤后剩余的行数比例越高,意味着查询效率较高。低过滤率意味着大量无用行被扫描。
12. extra
- 额外的信息,提供了关于执行查询的详细信息。常见值有:
- Using index:表示 MySQL 仅使用索引中的信息查询,而不需要访问表中的数据行。
- Using where:MySQL 需要通过
WHERE
子句过滤结果。 - Using temporary:使用临时表保存中间结果,通常出现在排序和分组操作中。
- Using filesort:MySQL 使用外部排序,而不是按索引顺序排序。
- Using join buffer:表明 MySQL 在连接操作中使用了连接缓冲区。
- Using index condition:表明使用了索引条件下推(Index Condition Pushdown, ICP)优化。
- Impossible WHERE:
WHERE
子句导致查询不可能返回任何行。 - Distinct:MySQL 找到第一个匹配的行后不再查找更多的行,用于
DISTINCT
关键字。
优化思路:
- Using index:MySQL 只从索引中读取数据,不访问数据行,这是非常高效的查询方式。
- Using where:MySQL 使用
WHERE
子句来过滤数据,属于比较正常的情况,但效率比单纯使用索引稍低。 - Using index condition:索引条件下推(ICP)优化,减少了对表行的访问,提高效率。
- 避免这些情况:
- Using temporary:使用临时表会降低查询效率,尽量避免。
- Using filesort:外部排序会降低效率,尽量避免。
- Impossible WHERE:虽然查询不会返回任何行,但这表示查询条件设置不当,需要检查逻辑。
要点总结:
- 尽量使用索引,尤其是主键或唯一索引。
- 避免全表扫描(ALL),而使用更高级的连接类型如
const
、eq_ref
、ref
。 - 减少处理行数(rows 列尽量小),并利用
WHERE
子句或索引来减少不必要的行扫描。 - 避免使用临时表和外部排序,优化查询以避免
Using temporary
和Using filesort
的出现。