文章

MySQL 索引原理与优化

#mysql#索引#性能优化

MySQL 索引原理与优化

概述

索引是数据库性能优化的核心手段。MySQL InnoDB 默认使用 B+Tree 索引。本文覆盖索引数据结构、执行计划分析、常见优化策略和避坑指南。

B+Tree 核心原理

为什么是 B+Tree 而不是二叉树/红黑树/Hash？

结构优点缺点
二叉树简单可能变成链表，IO 次数 = 树高度
红黑树自平衡高度仍过高（千万级数据 ~24 层，24 次 IO）
B+Tree矮胖、叶子有序链表插入删除有页分裂/合并开销
HashO(1) 等值查询无序 → 范围查询无效、最左前缀无效

B+Tree 的关键特性：

  • 非叶子节点只存索引键 + 指针，一个页能存更多索引 → 树更矮
  • 叶子节点存完整数据，且形成有序双向链表 → 范围查询高效
  • 千万级数据，树高度通常 3-4 层，只需 3-4 次 IO

聚集索引 vs 二级索引

聚集索引（Clustered Index）
┌────┬──────┬──────┬──────┬──────┐
│ id │ name │ age  │ ...  │ ...  │  ← 叶子节点 = 完整行数据
└────┴──────┴──────┴──────┴──────┘

二级索引（Secondary Index）
┌────┬──────────┐
│ age│ 主键 id   │  ← 叶子节点 = 索引列 + 主键值
└────┴──────────┘
                    ↓ 回表 (Bookmark Lookup)
              聚集索引 → 完整行数据

核心结论：

  • InnoDB 一定有聚集索引（主键 > 第一个非空唯一索引 > 隐式 row_id）
  • 二级索引查到后 回表 取完整数据（除非覆盖索引）
  • 建议用自增主键：顺序插入减少页分裂，主键短则二级索引叶子节点小

EXPLAIN 执行计划速查

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

关键字段解读

字段含义理想值警告值
type访问类型const/eq_ref/ref/rangeALL（全表扫描）
key实际使用的索引有索引名NULL（未用索引）
rows预估扫描行数越小越好与总行数接近
Extra额外信息Using index（覆盖索引）Using filesort（文件排序）、Using temporary（临时表）

type 性能排序（从好到差）

system > const > eq_ref > ref > range > index > ALL
                                          ↑ 全索引扫描（不如全表）
  • const：主键/唯一索引等值查询 → 1 行
  • eq_ref：Join 时用主键/唯一键匹配 → 1 行
  • ref：普通索引等值查询 → 可能多行
  • range：索引范围扫描 → BETWEEN/>/</IN
  • ALL：全表扫描 → 必须优化

Extra 关键标识

Extra 值含义行动
Using index覆盖索引，不回表最优 ✅
Using index condition索引下推（ICP），在引擎层过滤好 ✅
Using whereServer 层过滤正常，关注 rows
Using filesort额外排序操作考虑索引排序 ⚠️
Using temporary使用临时表必须优化 ❌
Using join bufferJoin 没有合适索引加索引 ❌

五大优化策略

1. 最左前缀原则

-- 联合索引：INDEX(a, b, c)
WHERE a = 1               -- ✅ 走索引（a 是最左列）
WHERE a = 1 AND b = 2     -- ✅ 走索引
WHERE b = 2               -- ❌ 不走索引（跳过了 a）
WHERE a = 1 AND c = 3     -- ✅ 只用 a（c 被跳过）
WHERE a = 1 AND b > 2 AND c = 3  -- ✅ 用 a + b range，c 无法用于等值

实战： 建联合索引时，等值查询列放前面，范围查询列放后面。

2. 覆盖索引（Covering Index）

-- 需要回表
SELECT * FROM users WHERE age = 25;  -- 索引中包含 age，但 SELECT * 需要所有列

-- 覆盖索引：不需要回表
INDEX idx_age_name(age, name)        -- 联合索引
SELECT age, name FROM users WHERE age = 25;  -- ✅ Extra: Using index

3. 索引下推（ICP, Index Condition Pushdown）

MySQL 5.6+ 特性：把 WHERE 过滤下推到存储引擎层，减少回表次数。

INDEX idx_name_age(name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age = 25;
-- 无 ICP：引擎取所有 name LIKE '张%' 的行 → Server 层过滤 age=25
-- 有 ICP：引擎直接过滤 name+age → 只有符合两条件的才回表

4. 避免索引失效的常见场景

-- ❌ 函数/计算破坏索引
WHERE YEAR(create_time) = 2026    -- 改成:
WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'

-- ❌ 隐式类型转换
WHERE phone = 13800138000          -- phone 是 VARCHAR → 索引失效
-- 改成 WHERE phone = '13800138000'

-- ❌ 前导模糊查询（除非用全文索引）
WHERE name LIKE '%张'              -- 索引失效
-- FTS 全文索引或 ElasticSearch

-- ❌ OR 条件中有非索引列
WHERE a = 1 OR b = 2               -- 如果 b 无索引 → 全表
-- 改成 UNION ALL

5. 前缀索引

-- 长字符串列（URL、TEXT）建前缀索引
ALTER TABLE urls ADD INDEX idx_url(url(20));

-- 前缀长度选择：区分度 > 95%
SELECT COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) AS selectivity FROM urls;
前缀长度区分度
100.92
150.97 ✅
200.98

索引设计 Checklist

  • 主键用自增 BIGINT，不用 UUID/随机字符串
  • WHERE / JOIN / ORDER BY 列建索引
  • 联合索引遵循最左前缀，等值在前、范围在后
  • 高频查询使用覆盖索引减少回表
  • 区分度低的列（性别、状态）不单独建索引
  • 避免冗余索引：INDEX(a) 和 INDEX(a, b) 有重叠
  • 定期分析：ANALYZE TABLE tablename 更新统计信息
  • 监控未使用索引：sys.schema_unused_indexes

常见问题 / 坑点

问题原因解决方案
明明有索引却走全表扫描优化器认为回表代价 > 全表扫描FORCE INDEX 或调整优化器成本参数
ORDER BY 导致 filesort排序列未包含在所用索引中建立合理的联合索引包含排序列
COUNT(*) 大表慢InnoDB 不存行数，需要扫描用估算值 SHOW TABLE STATUS 或 Redis 计数
LIMIT 1000000, 10 深度分页慢MySQL 需要扫描前 100 万行改成基于主键的延迟关联
索引碎片化大量删除/更新导致页分裂OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB
联合索引 a,b,c 但查询 b,c 不走索引违反最左前缀单独建 INDEX(b,c) 或调整索引列顺序

深度分页优化示例

-- ❌ 慢：扫描 1000010 行，返回 10 行
SELECT * FROM orders WHERE user_id = 100 ORDER BY id LIMIT 1000000, 10;

-- ✅ 延迟关联：先通过覆盖索引找到 id，再回表
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders WHERE user_id = 100 ORDER BY id LIMIT 1000000, 10
) tmp ON o.id = tmp.id;

关联知识

参考资源

学习时间

阶段时间备注
初次学习2026-07-14EXPLAIN + 五大策略 + 深度分页
深入理解待定优化器成本模型、MRR、索引合并

状态: 📖 已掌握 下次复习日期: 2026-08-14