本文从三个角度梳理 MySQL 索引：底层实现（B+ 树、Hash 等）、物理存储（聚簇 / 二级）、逻辑用途（主键、联合、覆盖等），并单列 常见失效场景 便于对照 SQL；最后给出设计与排查上的实践要点。默认以 InnoDB 为主（MySQL 8.x 常见默认存储引擎）。
1. 底层实现：索引「长什么样」
1.1 常见实现一览
| 类型 | 典型引擎 / 场景 | 适用查询 | 备注 |
|---|---|---|---|
| B+Tree | InnoDB 默认、多数 OLTP | 等值、范围、排序、最左前缀 | 本文重点 |
| Hash | MEMORY；InnoDB 有自适应哈希（AHI，不可手动建） | 等值（=、IN） | 不支持范围扫描 |
| 全文索引 | InnoDB 5.6+、MyISAM | MATCH ... AGAINST | 替代低效 LIKE '%x%' |
| 空间索引（R-Tree） | 支持 GIS 的类型与函数 | 地理范围、MBR 等 | POINT、LINESTRING 等 |
1.2 B+Tree（InnoDB 默认）
特点简述：
- 非叶子节点只存键，不存完整行数据，扇出大，树高通常 2～4 层即可支撑大量数据。
- 叶子节点存数据（聚簇索引存整行；二级索引存主键），叶子间双向链表，便于范围查询与顺序扫描。
- 从根到叶路径长度一致，单次查找的 IO 次数约等于树高，延迟相对稳定。
为什么不用二叉树 / 红黑树？
数据量大时树高过高，磁盘以页为单位随机读，树越高 IO 越多。B+ 树是多路平衡树，单层能挂更多子节点，从而降低树高。
为什么 InnoDB 常用 B+ 树而不是 B 树？
B 树非叶子也会带数据记录，单页能存的键变少，扇出下降、树变高；范围扫描时 B 树需要在不同层间跳转，而 B+ 树只需沿叶子链表扫描，更友好。
1.3 Hash 索引
原理： 对索引列求哈希，映射到桶；桶内再精确比较。
特点：
- 只擅长 等值（
=、IN），不支持范围（>、BETWEEN、ORDER BY利用索引排序等场景受限）。 - 无法按前缀匹配联合索引中「部分列」的典型用法（与 B+ 树最左前缀不同维度的限制）。
- 哈希冲突多时，桶内扫描变多，性能会下降。
适用： 以键值精确查找为主的场景（如 MEMORY 引擎缓存表）；InnoDB 的 AHI 由引擎根据热点自动维护，不能当普通 Hash 索引来设计。
1.4 全文索引
用于文本关键词检索，底层多为倒排索引结构。
- InnoDB 从 MySQL 5.6 起支持全文索引，语法为
MATCH(col1,...) AGAINST('keyword' IN NATURAL LANGUAGE MODE)等。 - 相比
LIKE '%keyword%'（无法走普通 B+ 树前缀），全文索引在大量文本检索上通常更高效；支持自然语言、布尔模式等。
1.5 空间索引（R-Tree）
用于 GEOMETRY 等空间类型，结构多为 R 树，配合 MBRContains 等空间函数做范围查询。
2. 物理存储：数据与索引怎么放
2.1 聚簇索引（Clustered Index）
在 InnoDB 中，表数据按主键顺序存放在 B+ 树的叶子节点，这张「以主键为键的 B+ 树」就是聚簇索引。
聚簇索引是谁？（优先级）
- 有显式主键 → 主键即聚簇索引。
- 无主键 → 选第一个非空唯一约束对应的列。
- 以上都没有 → InnoDB 内部生成 隐藏主键
row_id作为聚簇索引。
意义： 按主键访问、主键范围扫描时，少一次「指针跳转」，与数据存储天然一致。
2.2 二级索引（Secondary Index）
定义： 除聚簇索引以外的索引（唯一索引、普通索引、联合索引等）。
结构： 叶子节点存 主键值，不存完整行（与 MyISAM 不同）。
回表（Bookmark Lookup）：
先在二级索引上找到 主键，再到聚簇索引 B+ 树上按主键取整行。
示例： 表 t 主键 id，二级索引 name。
SELECT * FROM t WHERE name = 'Tom';
执行顺序可理解为：在 name 索引上找到 id = 100 → 再到主键索引读取 id = 100 的完整行。
若查询列全部在二级索引内（含主键若二级索引叶子已含），则可能不回表，见下文「覆盖索引」。
2.3 InnoDB 与 MyISAM 对比（理解「回表」）
| 项目 | InnoDB | MyISAM |
|---|---|---|
| 数据与索引 | 聚簇：数据在主键索引叶子 | 数据在 .MYD，索引在 .MYI |
| 主键索引叶子 | 完整行 | 行在数据文件中的物理地址 |
| 二级索引叶子 | 主键值 → 需回表到聚簇索引 | 物理地址 → 通常不回表（直接读堆文件） |
| 事务 / 行锁 | 支持 | 不支持（表锁为主） |
生产环境新建表一般优先 InnoDB；了解 MyISAM 有助于读老文档或对比「为什么 InnoDB 总说回表」。
3. 逻辑分类：业务上怎么建索引
3.1 主键索引（PRIMARY KEY）
- 唯一且非空，一张表只有一个。
- 在 InnoDB 中即 聚簇索引。
- 语法：
PRIMARY KEY (col)。
3.2 唯一索引（UNIQUE）
- 列值必须唯一；NULL 是否允许多个 NULL 与版本、引擎有关，设计时建议查官方文档并显式
NOT NULL若业务不允许空。 - 语法：
UNIQUE INDEX idx_name (col)。
3.3 普通索引（INDEX）
- 无唯一约束，仅加速查询。
- 语法：
INDEX idx_name (col)。
3.4 联合索引（复合索引）
多列组成一个 B+ 树键，语法示例：
INDEX idx_multi (a, b, c)
最左前缀原则： 条件从最左列连续匹配时，才能尽量用上该索引；跳过左侧列则右侧列通常无法单独走该索引。
| 示例条件 | 能否较好利用 (a,b,c) |
|---|---|
WHERE a = 1 | 能（用到 a） |
WHERE a = 1 AND b = 2 | 能（a,b） |
WHERE a = 1 AND b = 2 AND c = 3 | 能（a,b,c） |
WHERE a = 1 AND c = 3 | 通常只用 a，c 无法跳过 b 使用该复合索引 |
WHERE b = 2 或 WHERE b = 2 AND c = 3 | 不符合最左前缀，一般不走该联合索引 |
范围条件后的列： 例如 WHERE a = 1 AND b > 2 AND c = 3，常见情况下 **a、b 可走索引，b 为范围后 c 往往无法再用于索引内过滤**（以 EXPLAIN 为准）。
排序： ORDER BY a, b 常与 (a,b,...) 方向一致时可减少 filesort；ORDER BY a, c（跳过 b）则往往难以完全利用索引有序性。
设计提示： 区分度高的列、过滤性强的条件尽量靠前；在列顺序允许的前提下，让高频查询形成覆盖索引可减少回表。
3.5 前缀索引
只对字符串前 n 个字符建索引，节省空间：
INDEX idx_name (col(10))
缺点： 不利于 ORDER BY / GROUP BY 完全依赖该索引；覆盖能力也受限。
长度 n： 可用选择性估算，例如 COUNT(DISTINCT LEFT(col, n)) / COUNT(*)，越接近全列选择性越好，再结合长度与存储折中。
3.6 覆盖索引（Covering，概念而非独立索引类型）
若 SELECT 的列与 WHERE 用到的列都包含在某个索引中，优化器可能只读索引树不回表，EXPLAIN 的 Extra 常出现 **Using index**。
示例： 联合索引 (name, age)。
SELECT name, age FROM t WHERE name = 'Tom';
name、age 均在索引中即可覆盖；若 SELECT * 则多半要回表取其余列。
4. 常见失效场景
下列情况容易导致无法按预期走索引、或只用到索引的一部分；是否全表扫描以 **EXPLAIN / EXPLAIN ANALYZE** 为准，优化器版本与成本估算不同，结论可能变化。
4.1 对索引列做「计算」或函数包装
索引按列的原始值组织；对列套函数、运算后，条件往往无法与 B+ 树键直接匹配。
| 典型写法 | 说明与规避 |
|---|---|
WHERE YEAR(create_time) = 2024 | 无法走 create_time 上普通索引的常见形式；改为范围：create_time >= '2024-01-01' AND create_time < '2025-01-01'。 |
WHERE id + 1 = 100 | 列参与运算；改为 WHERE id = 99。 |
WHERE UPPER(name) = 'TOM' | 若必须大小写不敏感，可考虑统一存放大写、函数索引（MySQL 8.0+）或生成列 + 索引。 |
4.2 隐式类型转换
比较两侧类型不一致时，MySQL 可能把列转成另一侧的类型，导致无法使用该列上的索引（常见于字符串列与数字比较）。
| 典型情况 | 说明与规避 |
|---|---|
phone 为 VARCHAR，写 WHERE phone = 13800138000 | 可能触发对 phone 的转换；改为字符串字面量：WHERE phone = '13800138000'。 |
| 连接条件两侧类型、**排序规则（collation）**不一致 | 可能无法利用索引；建表与 JOIN 条件保持同类型、同 collation。 |
4.3 LIKE 与前缀模糊
| 写法 | 说明 |
|---|---|
LIKE 'abc%' | 通常可走前缀扫描（列上有合适 B+ 树索引时）。 |
LIKE '%abc'、LIKE '%abc%' | 无法用普通 B+ 树做「整列有序」定位，易退化为大范围扫描；大文本检索考虑全文索引或搜索引擎。 |
4.4 OR、<>、NOT IN 等条件
**OR两侧若不能同时用索引（不同列、或一侧无索引），优化器常选合并多次范围或直接全表**；可改为**UNION ALL拆两条各自走索引，或在业务允许下改写为**IN（同列多值）。**!=、<>、NOT IN** 往往对应大范围区间扫描或全表；有时可用**IN正向枚举**、标记位、或反范式冗余减轻（需权衡一致性）。- 部分场景下优化器会使用
**index_merge**（多索引合并），不一定更优，仍需看执行计划与耗时。
4.5 联合索引与最左前缀
WHERE b = 2 而索引为 (a,b,c) 等跳过最左列的情况，通常不能利用该联合索引；前文 3.4 节已展开，设计时优先把高选择性、等值条件放在左侧。
4.6 优化器「认为」全表更便宜
即使 SQL 形式「可索引」，仍可能不走索引，例如：
- 表很小：全表读几页即可完成，索引回表反而更贵。
- 范围条件过宽：如时间跨度极大，
type=range扫描行数接近全表时，可能选 ALL。 - 统计信息不准：
ANALYZE TABLE、检查直方图（高版本）、大变更后更新统计。
4.7 其他易忽略点
| 场景 | 说明 |
|---|---|
| 索引列隐式参与字符集/排序规则转换 | 连接或 WHERE 中与字面量/另一列 collation 不一致时，可能无法走索引。 |
**ORDER BY 与索引顺序不一致** | 如降序/多列顺序与索引定义不一致，可能额外排序（filesort），不等同于「WHERE 不走索引」，但影响性能。 |
| 非最左前缀、前缀索引 | 前缀索引无法用于部分 GROUP BY / ORDER BY；见 3.5 节。 |
排查时建议：固定一条慢 SQL → 看 EXPLAIN 的 type、key、rows、Extra → 对照上表逐项排除，再决定改 SQL、改索引或改统计信息。
5. 最佳实践与排查
| 方向 | 建议 |
|---|---|
| 主键 | 尽量短、有序（如整型自增或有序 UUID），减少二级索引叶子主键占用与页分裂随机性。 |
| 建索引 | 为高频、高选择性条件建索引；避免低区分度列单独建索引。 |
| 联合索引 | 合并单列索引为少数联合索引，遵守最左前缀，避免冗余索引。 |
| 覆盖与回表 | 在业务允许下，用覆盖索引减少回表；避免 SELECT * 成习惯。 |
| 表达式 | 避免对索引列套函数、隐式类型转换、运算（如 WHERE YEAR(col)=2024），易导致索引失效。 |
| 数量与维护 | 索引会拖慢写入、占空间；单表索引数不宜盲目堆叠（常见经验值 5 个左右量级需结合表与写入压测）。 |
| 验证 | 变更后用 **EXPLAIN / EXPLAIN ANALYZE（视版本）看是否走预期索引；结合慢查询日志**持续治理。 |
