当我们谈论数据库性能优化时，EXPLAIN 是一个非常有用的工具，用于分析查询语句的执行计划。它能帮助我们理解数据库是如何执行查询的，以及是否能有效利用索引和其他优化策略。下面是一些关键的概念和术语，帮助你理解如何分析 EXPLAIN 的输出以优化查询性能：
1. 执行计划基础
执行 EXPLAIN 查询的基本语法是：
EXPLAIN SELECT ...... ;
这会返回一个描述查询执行计划的结果集。每一行都代表执行计划中的一步操作，通常是从最内层到最外层。
2. 关键字段解释
在分析 EXPLAIN 输出时，需要注意以下关键字段：
- id: 操作的唯一标识符，通常从 1 开始递增。多个操作可能共享相同的 id，表示它们在同一层级。
- id不同，在所有组中,id值越大,优先级越高,越先执行 。
- id相同，都是1，从上到下顺序执行。
- select_type: 操作的类型，例如
SIMPLE、PRIMARY、SUBQUERY等。不同类型反映了不同的查询结构和优化方式。- 查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询结果值。
- SIMPLE： 简单select查询,查询中不包含子查询或者UNION。
- PRIMARY： 查询中若包含任何复杂的子查询,最外层查询则被标记为primary
- SUBQUERY： 在select或where中包含了子查询。
- DERIVED： 在from列表中包含的子查询被标记为derived(衍生)把结果放在临时表当。
- UNION： 若第二个select出现的union之后,则被标记为union ，若union包含在from子句的子查询中,外层select将被标记为deriver
- UNION RESULT： 从union表获取结果select,两个UNION合并的结果集在最后。
- table: 操作涉及的表。
- type: 访问类型，表示如何访问表，常见的类型有
ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。- 结果值:(最好到最差) system > const > eq_ref > ref > range > index > ALL
- system 表中有一行记录(系统表) 这是const类型的特例,平时不会出现。
- const 表示通过索引一次就找到了，const用于比较primary 或者 unique索引。
- eq_ref 唯一性索引扫描 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或唯一索引扫描
- ref 非唯一性索引扫描。
- range 只检索给定范围的行,使用一个索引来选择行 一般就是在你的where语句中出现between<>\ in等查询。
- index 与 All 区别为 index 类型只遍历索引树,通常比 All 要快,因为索引文件通常比数据文件要小，all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取。
ALL将全表进行扫描,从硬盘当中读取数据,如果出现了All 切数据量非常大, 一定要去做优化。
- possible_keys: 可能使用的索引列表。
- key: 实际使用的索引。
- key_len: 索引字段的长度。
- ref: 显示索引如何与表中的列值匹配。
- 索引是否被引入到, 到底引用到了哪几个索引。
- rows: 预计要检索/扫描的行数。
- 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,每长表有多少行被优化器查询过。
- filtered：满足查询的记录数量的比例，注意是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不十分准确。
- Extra: 额外信息，如
Using where（表示使用了 WHERE 条件过滤结果）、Using index（表示查询使用了覆盖索引）等。- Using Filesort 和 Using Temporary：说明没有使用到索引。
- impossible where：说明条件永远不成立。
- use index：表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好
- using where：表明使用了where过滤。
- using join buffer：使用了连接缓存。
3. 分析常见问题和优化建议
在分析 EXPLAIN 输出时，可以根据以下常见问题和优化建议进行评估：
-
全表扫描（ALL）：如果
type列显示为ALL，表示查询执行全表扫描，这通常是性能问题的原因。可以考虑优化查询或添加合适的索引来避免全表扫描。 -
索引未使用（NULL key）：如果
key列为NULL，表示查询没有使用索引，可能需要调整查询或者添加新的索引。 -
索引选择不当：
type列显示range或ref时，可能表示索引选择不当，考虑是否需要调整索引以提高效率。 -
额外信息（Extra）：
Extra列提供了额外的执行信息，如Using where、Using index、Using temporary等，这些信息可以帮助理解查询的具体执行情况。
4. 优化查询的步骤
基于 EXPLAIN 的输出，优化查询的一般步骤包括：
- 确认查询是否使用了预期的索引。
- 确保统计信息是最新的，以便优化器可以做出准确的执行计划选择。
- 根据
EXPLAIN输出中的访问类型和扫描行数来调整索引设计或者查询本身的结构。 - 避免在大表上进行全表扫描，尽可能使用索引来减少数据检索的成本。
通过理解和分析 EXPLAIN 的输出，可以有针对性地改进查询性能，使得数据库操作更加高效和可靠。