TNotes.sql

Appearance

to top

0038. 执行计划 EXPLLAIN ​

1. 📝 概述 ​

  • EXPLAIN 是分析 SQL 执行路径的重要工具，通过查看 type、key 和 Extra 字段，可以判断查询是否使用了索引、是否存在性能瓶颈，并指导后续的索引优化策略。
  • EXPLAIN
    • EXPLAIN 是 MySQL 中用于查看 SQL 查询执行计划的关键字。
    • 它可以帮助你了解：
      • MySQL 是如何访问表和索引的
      • 是否使用了索引
      • 查询是全表扫描还是范围扫描
      • 是否需要临时表或排序等
    • 对优化查询性能、调整索引设计非常关键。
  • EXPLAIN 基本语法示例
    • EXPLAIN SELECT * FROM employees WHERE salary > 5000;
    • 只显示执行计划，不真正执行查询。
  • 输出字段详解
字段名描述
id查询中每个 SELECT 的唯一标识符（复杂查询中有多个 id）
select_type查询类型
table正在访问的表名
partitions匹配的分区（如果使用了分区表）
type表连接类型
possible_keys可能用到的索引
key实际使用的索引
key_len使用的索引长度
ref显示哪些列或常量被用来和索引比较
rowsMySQL 认为执行查询需要扫描的行数（估算）
Extra包含额外信息
  • select_type
    • SIMPLE：简单查询，没有子查询或子查询的 FROM 列表。
    • PRIMARY：主查询，即最外层的 SELECT。
    • SUBQUERY：子查询
    • DERIVED：衍生表 - 子查询的 FROM 列表
  • type
    • type 字段反映了 MySQL 访问表的方式。理想情况下，至少要达到 range 或 ref；如果出现 ALL，应优先考虑添加合适的索引以提升查询性能。
类型描述性能
system表中只有一行记录（系统表）⭐⭐⭐⭐⭐
const通过主键或唯一索引定位一行⭐⭐⭐⭐
eq_ref唯一索引等值匹配，通常出现在 JOIN 中⭐⭐⭐⭐
ref非唯一索引等值匹配⭐⭐⭐
range使用索引进行范围查找（如 >, <, BETWEEN）⭐⭐
index扫描整个索引树，但未进行条件过滤⭐
ALL全表扫描（最慢，通常应避免，除非表非常小或命中大量数据）❌
  • Extra
    • Using where：使用了 WHERE 条件过滤
    • Using index：使用覆盖索引（索引包含查询的所有列）
    • Using filesort：使用了临时表或排序，需要进行额外排序（性能差）
    • Using temporary：使用了临时表，需要进行排序（性能差）

2. 🤔 如何判断索引是否生效？ ​

  • 判断方法：
    • 查看 key 字段是否使用了索引
    • 查看 Extra 是否有以下提示：
      • Using index → 使用了覆盖索引（最好）
      • Using where; Using index condition pushdown → 使用了索引下推（较好）
    • 查看 type 是否为 range、ref、eq_ref 等高效类型
  • 索引失效常见情况：
    • 使用函数或表达式操作索引列：WHERE YEAR(create_time) = 2024
    • 使用 OR 并且部分条件没有索引支持
    • 使用 LIKE '%abc' 进行前缀模糊匹配
    • 查询字段与索引字段顺序不一致（复合索引时）
    • 查询命中数据比例过高（超过 20%-30%），优化器放弃索引改走全表扫描
sql
EXPLAIN SELECT * FROM employees WHERE salary > 5000;

-- 输出结果：

-- +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
-- | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
-- +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
-- | 1  | SIMPLE      | employees | NULL       | ALL  | idx_salary    | NULL | NULL    | NULL | 1000 |   100.00 | Using where |
-- +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

-- 分析：

-- `type: ALL` → 全表扫描
-- `key: NULL` → 未使用索引
-- `Extra: Using where` → 有 WHERE 条件过滤

-- 虽然 `salary` 上有索引，但命中数据过多导致 MySQL 放弃使用索引
sql
EXPLAIN SELECT * FROM employees WHERE salary > 10000;

-- 输出结果：

-- +----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
-- | id | select_type | table     | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
-- +----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
-- | 1  | SIMPLE      | employees | NULL       | range | idx_salary    | idx_salary   | 5       | NULL |  100 |   100.00 | Using index condition |
-- +----+-------------+-----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

-- 分析：

-- `type: range` → 使用了范围扫描
-- `key: idx_salary` → 使用了索引
-- `Extra: Using index condition` → 使用了索引下推

-- 由于命中数据较少，MySQL 选择使用索引来加速查询