索引优化与 SQL 分析

索引原理

索引是帮助 MySQL 高效获取数据 的数据结构。

B+ 树索引

InnoDB 默认使用 B+ 树，特点：

• 非叶子节点只存储键值，不存数据
• 叶子节点存储完整数据（聚簇索引）或主键值（二级索引）
• 叶子节点形成有序双向链表，支持范围查询
• 高度通常为 2~4 层，查询稳定在 2~4 次 I/O

           [50]
         /      \
      [20, 30]  [70, 90]
      /    |    |    \
    [1-10] [21-25] [51-69] [71-99]
    (叶子节点，存数据或主键)

哈希索引

• 仅 Memory 引擎默认支持；InnoDB 有自适应哈希索引（AHI）
• 等值查询极快（O(1)），但不支持范围查询
• 无法用于排序

全文索引

• 用于大文本字段的关键词搜索（MATCH ... AGAINST）
• 基于倒排索引实现
• MyISAM 原生支持，InnoDB 5.6+ 支持

---

索引类型

聚簇索引（Clustered Index）

• InnoDB 中主键即为聚簇索引
• 叶子节点存储整行数据
• 一张表只能有一个聚簇索引
• 如果没有主键，InnoDB 会：
  1. 选择第一个 UNIQUE NOT NULL 列
  2. 或隐式生成 ROW_ID

二级索引（Secondary Index）

• 非主键索引，叶子节点存储主键值
• 查询时先通过二级索引找到主键，再回表查聚簇索引（回表查询）

联合索引

• 多个字段组合成一个索引
• 遵循最左前缀原则

CREATE INDEX idx_a_b_c ON t (a, b, c);

-- 走索引：
WHERE a = 1                  -- ✅ 用第 1 列
WHERE a = 1 AND b = 2        -- ✅ 用第 1、2 列
WHERE a = 1 AND b = 2 AND c = 3  -- ✅ 全用
WHERE a = 1 AND c = 3        -- ✅ 用第 1 列（c 无法用到）

-- 不走索引：
WHERE b = 2                  -- ❌ 未从第一列开始
WHERE c = 3                  -- ❌ 未从第一列开始

覆盖索引（Covering Index）

• 索引中已包含查询所需的所有字段
• 无需回表，直接返回结果
• EXPLAIN 中 Extra 显示 Using index

索引下推（ICP，Index Condition Pushdown）

• MySQL 5.6+ 优化
• 将 WHERE 条件中部分过滤下推到存储引擎层
• 减少回表次数
• EXPLAIN 中 Extra 显示 Using index condition

---

EXPLAIN 执行计划解读

EXPLAIN SELECT * FROM users WHERE age > 18;

列名	说明	常见值
id	执行顺序，越大越先执行	1, 2, ...
select_type	查询类型	SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION
table	访问的表	表名或别名
type	访问类型（重点）	system > const > eq_ref > ref > range > index > ALL
possible_keys	可能用到的索引	索引名列表
key	实际用到的索引	索引名
key_len	索引使用的字节数	数值越大说明用的索引列越多
rows	预估扫描行数	数值越小越好
Extra	额外信息	Using index, Using where, Using filesort 等

type 详解

type	说明	性能
system	表中只有一行	最好
const	主键或唯一索引等值查询	极好
eq_ref	JOIN 中使用主键/唯一索引关联	很好
ref	非唯一索引等值查询	好
range	索引范围查询（>、<、BETWEEN、IN）	较好
index	扫描全索引（比全表快）	一般
ALL	全表扫描	最差

Extra 常见值

Extra	含义
Using index	覆盖索引，无需回表
Using where	从索引回表后过滤（或用不到索引时 Using where + ALL）
Using index condition	使用了索引下推（ICP）
Using filesort	需要额外排序（性能杀手，应避免）
Using temporary	使用了临时表（性能杀手，常见于 GROUP BY 未走索引）

---

SQL 性能分析

慢查询日志

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询日志（动态生效）
SET GLOBAL slow_query_log = ON;

-- 设置阈值（单位：秒）
SET GLOBAL long_query_time = 1;

-- 查看日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

optimizer_trace

-- 开启优化器追踪
SET OPTIMIZER_TRACE = "enabled=on";
EXPLAIN SELECT * FROM users WHERE age > 18;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET OPTIMIZER_TRACE = "enabled=off";

SHOW PROFILE

-- 查看是否支持
SELECT @@have_profiling;

-- 开启 profiling
SET profiling = 1;

-- 执行查询
SELECT * FROM users WHERE age > 18;

-- 查看所有查询的耗时
SHOW PROFILES;

-- 查看具体查询的详细耗时
SHOW PROFILE FOR QUERY 1;

---

常见 SQL 优化场景

1. 避免 SELECT *

-- ❌ 差
SELECT * FROM users WHERE age > 18;

-- ✅ 好（覆盖索引）
SELECT id, name FROM users WHERE age > 18;

2. 索引列避免函数操作

-- ❌ 不走索引
WHERE DATE(create_time) = '2024-01-01';

-- ✅ 走索引
WHERE create_time >= '2024-01-01 00:00:00'
  AND create_time < '2024-01-02 00:00:00';

3. 避免隐式类型转换

-- ❌ phone 是 VARCHAR，不走索引
WHERE phone = 13800138000;

-- ✅ 走索引
WHERE phone = '13800138000';

4. LIKE 模糊匹配

-- ❌ 不走索引
WHERE name LIKE '%张%';

-- ✅ 走索引（前缀匹配）
WHERE name LIKE '张%';

5. 使用 JOIN 替代子查询

-- ❌ 子查询可能产生临时表
SELECT * FROM a WHERE id IN (SELECT id FROM b WHERE status = 1);

-- ✅ JOIN 更高效
SELECT a.* FROM a INNER JOIN b ON a.id = b.id WHERE b.status = 1;

6. 分页优化（延迟关联）

-- ❌ 深分页，前 10000 条被丢弃
SELECT * FROM users ORDER BY id LIMIT 100000, 20;

-- ✅ 延迟关联：先查主键，再关联
SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 100000, 20) tmp
ON u.id = tmp.id;

7. 利用覆盖索引

-- 建立联合索引 (status, create_time)
CREATE INDEX idx_status_time ON orders (status, create_time);

-- 查询可以直接从索引返回，无需回表
SELECT status, create_time FROM orders WHERE status = 1;