MySQL 索引

🧱 1. 索引的核心概念（必须理解）

索引是数据库里用于 加速查找 的数据结构，本质类似“目录”。

InnoDB 使用 B+Tree 作为索引结构（核心关键点）。

索引的目标是：

更快查找（减少 IO）
提升排序性能
加速 JOIN
减少锁范围（行锁不退化为表锁）

🧱 2. InnoDB 索引结构（底层原理）

InnoDB 存储引擎中索引的本质是：

B+Tree（Mysql 的核心索引结构）

特征：

所有数据存储在叶子节点
叶子节点之间有链表 -> 适合范围扫描
非叶节点仅存 key，不存数据

优点：

IO 次数可控（树高度小）
有序 -> 支持范围查询、高效排序

🧱 3. 索引类型（完整体系）

3.1 主键索引（Clustered Index）

数据行与主键索引存储在一起（聚簇）
每张表必须且只有一个

结构：

B+Tree
 └─ 叶子节点 = 实际行数据

主键选择影响数据组织，非常重要。

建议：

尽量使用自增主键（最稳定、最不容易分裂）

3.2 二级索引（Secondary Index）

叶子节点存储：

二级索引 key -> 主键值

查找流程：

找二级索引
回表（根据主键）到主键索引取完整行

3.3 唯一索引（UNIQUE）

与普通索引结构相同，只是多了唯一校验。

3.4 覆盖索引（Covering Index）

查询所需字段全部在索引中，不需要回表，例如：

SELECT id, name FROM user WHERE name='Alice';

优势：

无需回表（性能质变）
更少 IO
减少锁范围

3.5 联合索引（Composite Index）

例如：

INDEX (a, b, c)

遵循最左前缀原则：

(a)
(a, b)
(a, b, c)
有效

(b), (c), (b, c) 无效

3.6 全文索引（FULLTEXT）

适用于文本搜索：MATCH AGAINST

3.7 哈希索引（Memory 表）

用于内存表，不在 InnoDB 中使用。

🧱 4. 索引最佳实践（最终总结版）

4.1 单列索引 vs 联合索引

优先用联合索引，而不是单列索引堆积。

4.2 主键一定要设计好

建议：

使用自增 BIGINT
避免 UUID（导致 B+Tree 不连续 -> 分裂 -> 性能差）

4.3 覆盖索引优先

设计 SELECT 高频字段时，尽量让查询只走索引。

🧱 5. 什么时候索引会失效？（最重要）

以下情况会导致索引无法使用：

❌ 5.1 LIKE ‘%xxx’ 前导模糊

WHERE name LIKE '%abc'

解决：

建反向存储字段
使用全文索引

❌ 5.2 在列上进行计算

WHERE DATE(create_time) = '2025-01-01'

解决：

改写为：

WHERE create_time >= '2025-01-01'
  AND create_time < '2025-01-02'

❌ 5.3 隐式类型转换

WHERE phone = 1234567   -- 字符类型字段匹配数字

❌ 5.4 OR 条件未全部使用索引

WHERE a = 1 OR b = 2

❌ 5.5 联合索引不符合最左前缀

INDEX(a, b)
WHERE b=1           -- 不能用
WHERE a=1 AND b=2   -- 能用

❌ 5.6 对索引字段使用函数/表达式

WHERE UPPER(name) = 'AAA'

❌ 5.7 NOT IN / != / <> 低概率使用索引

🧱 6. 索引与锁的关系（回复2内容融合）

索引决定锁的范围：

找到目标记录 -> 加精确的 record lock
未找到记录 -> 加 gap lock
范围查询 -> next-key lock
不走索引 -> 锁全表（行锁退化为表锁）

例子（灾难级）：

SELECT * FROM user WHERE email LIKE '%xxx%' FOR UPDATE;

-> 索引失效 -> 锁全表

🧱 7. 索引设计方法论（最实用）

按照业务落地：

7.1 读多写少场景

尽量使用覆盖索引
尽量使用联合索引减少回表
避免宽行（字段太多）

7.2 写多读少（插入频繁）

减少冗余索引（每个索引都要维护）
使用自增主键减少页面分裂

7.3 高并发 OLTP 系统

严格控制索引数量
单表索引不超过 5~8 个
索引覆盖关键查询
事务中的 SQL 必须走索引

🧱 8. 索引调优：如何判断是否走索引？

使用 EXPLAIN

EXPLAIN SELECT ...

关键字段：

type（连接类型：ALL=全表扫描）
key（使用的索引）
rows（扫描行数）
Extra（Using filesort、Using temporary）

🧱 9. 实战案例（最重要部分）

📌 案例 1：联合索引生效与失效

索引：(a, b, c)

查询：

WHERE a=1 AND c=5

有效利用：

a
c 不能使用范围扫描，需要 b 覆盖

优化：

WHERE a=1 AND b=xx AND c=5

📌 案例 2：索引字段计算导致失效

WHERE YEAR(create_time) = 2024

优化：

WHERE create_time >= '2024-01-01'
AND   create_time < '2025-01-01'

📌 案例 3：索引覆盖减少回表

表有 1000 万行

查询：

SELECT id FROM orders WHERE status=1;

仅访问索引 -> 极快

如果 SELECT * -> 必须回表 -> 性能下降 5～20 倍

🧱 10. 索引最终总结（极速复盘）

MySQL 使用 B+Tree 索引（最核心）
主键索引包含数据，二级索引需要回表
索引结构决定锁范围
联合索引优于多个单列索引
覆盖索引是性能神器
索引失效常见 7 大原因必须牢记
高频业务查询一定要设计专用索引
索引数量不是越多越好（写入代价很高）