索引

基于MySQL，涵盖索引概念、B+Tree 结构、常见索引类型、SQL 示例与索引使用原则。

---

一、索引的基本概念

• 索引（Index）：帮助 MySQL 快速定位数据行的有序数据结构，类似于书的目录。
• 核心目的：减少磁盘 IO 次数、降低扫描行数，从而加快 SELECT / UPDATE / DELETE。

优点：

• 加速数据检索（避免全表扫描）。
• 支持排序、分组优化（ORDER BY / GROUP BY 可以“顺带”用索引顺序）。
• 有助于约束实现（唯一索引、主键索引）。

缺点：

• 占用额外磁盘空间。
• 写入变慢：INSERT / UPDATE / DELETE 需要维护索引结构。
• 设计不当会导致索引失效或优化器不选用索引，白白增加负担。

---

二、MySQL 常见索引类型与 SQL 写法

2.1 按约束语义划分

• 主键索引（PRIMARY KEY）：唯一且非空，InnoDB 中同时是聚簇索引的载体。
• 唯一索引（UNIQUE）：列值唯一，但允许多个 NULL。
• 普通索引（INDEX / KEY）：只加速查询，无唯一性约束。
• 组合索引（联合索引）：多个列组合成一个索引，遵循最左前缀原则。

-- 创建表时定义主键、唯一与普通索引
CREATE TABLE user (
  id        BIGINT PRIMARY KEY,          -- 主键索引
  email     VARCHAR(255) UNIQUE,         -- 唯一索引
  username  VARCHAR(50),
  status    TINYINT,
  created_at DATETIME,
  INDEX idx_user_name_status (username, status)  -- 组合索引
) ENGINE = InnoDB;

-- 为已有表增加索引
ALTER TABLE user ADD INDEX idx_user_created (created_at);
ALTER TABLE user ADD UNIQUE idx_user_email (email);
ALTER TABLE user ADD INDEX idx_user_name_status (username, status);

-- 使用 CREATE INDEX 语句
CREATE INDEX idx_user_status ON user (status);
DROP INDEX idx_user_status ON user;

组合索引最左前缀原则示例：

-- 组合索引 (username, status)
-- 能用到索引的 SQL：
SELECT * FROM user WHERE username = 'alice';
SELECT * FROM user WHERE username = 'alice' AND status = 1;
SELECT * FROM user WHERE username IN ('alice', 'bob') AND status = 1;

-- 无法完整利用该索引（仅可能用到 username 部分，甚至不用）：
SELECT * FROM user WHERE status = 1;                -- 跳过了最左 username
SELECT * FROM user WHERE status = 1 AND username LIKE '%ice';  -- 前缀模糊也会导致问题

2.2 按底层结构划分

• BTREE 索引：InnoDB、MyISAM 默认；实际是 B+Tree 结构。
• HASH 索引：Memory 引擎支持；InnoDB 有自适应哈希索引（由存储引擎自动维护）。
• FULLTEXT 全文索引：适用于大文本字段的全文搜索。

常见 USING 写法：

CREATE INDEX idx_user_email_btree ON user (email) USING BTREE;
CREATE INDEX idx_tmp_hash USING HASH ON tmp_table (token);  -- Memory 引擎
CREATE FULLTEXT INDEX idx_post_content ON post (content);   -- 全文索引

---

三、B+Tree 索引结构与 InnoDB 聚簇索引

3.1 B+Tree 索引的层次结构

B+Tree 是一种多路平衡查找树，所有数据都存放在叶子节点，内节点只存 key + 指针。

flowchart TB
    subgraph BPTree["B+Tree 索引结构（简化示意）"]
        direction TB
        R[内部节点: key=50]
        L1[内部节点: key=20,35]
        L2[内部节点: key=70,90]
        L1L[叶子: 10 15]
        L1M[叶子: 20 30]
        L1R[叶子: 35 45]
        L2L[叶子: 50 60]
        L2M[叶子: 70 80]
        L2R[叶子: 90 100]

        R --> L1
        R --> L2
        L1 --> L1L
        L1 --> L1M
        L1 --> L1R
        L2 --> L2L
        L2 --> L2M
        L2 --> L2R
    end

关键特性：

• 所有真实数据行指针都在叶子节点中，叶子之间一般是链表相连，便于范围扫描（BETWEEN、>、<）。
• 树高一般不大（3～4 层），一次查找最多几次磁盘 IO。

3.2 聚簇索引与二级索引（InnoDB）

• 聚簇索引（Clustered Index）：InnoDB 将整行数据存储在主键 B+Tree 的叶子节点中。
  • 主键索引叶子：(主键值, 整行记录)。
• 二级索引（Secondary Index）：非主键索引的叶子节点存储的是 (索引列值, 主键值)。
  • 根据二级索引查询时，需要先在二级索引树中找到主键值，再回到聚簇索引树查整行，称为回表。

-- 示例：id 为主键，email 上有二级索引
CREATE TABLE user (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255),
  name VARCHAR(50),
  INDEX idx_user_email (email)   -- 二级索引
) ENGINE = InnoDB;

-- 使用 email 查询
SELECT * FROM user WHERE email = '[email protected]';
-- 步骤：
-- 1）在 idx_user_email 的 B+Tree 中定位 email='[email protected]' 的叶子，得到主键 id
-- 2）拿着 id 再到聚簇索引（PK B+Tree）里查出整行

覆盖索引（Covering Index）： 若查询的列都包含在同一个二级索引的叶子节点中，则无需回表，直接从索引中返回结果。

-- 组合索引 (email, name)
CREATE INDEX idx_user_email_name ON user (email, name);

-- 覆盖索引：只查 email 和 name，不查其他列
SELECT email, name FROM user WHERE email = '[email protected]';
-- 可以只在 idx_user_email_name 上完成查找，无需回表，IO 更少。

---

四、EXPLAIN 与索引使用情况

使用 EXPLAIN 可以查看 SQL 是否走到预期索引：

EXPLAIN SELECT * FROM user WHERE email = '[email protected]';

常见字段含义（简要）：

• type：访问类型，const > ref > range > index > ALL，越靠前越好；ALL 表示全表扫描。
• key：实际使用到的索引名。
• rows：预估扫描行数。
• Extra：如 Using index（使用覆盖索引）、Using where（还需在服务器端过滤）等。

示例对比：

-- 好：使用二级索引 + 覆盖索引
EXPLAIN SELECT email FROM user WHERE email = '[email protected]';

-- 一般：使用二级索引，但需要回表
EXPLAIN SELECT * FROM user WHERE email = '[email protected]';

-- 坏：未使用索引，ALL 扫描
EXPLAIN SELECT * FROM user WHERE function(email) = '[email protected]';

---

五、索引使用的 SQL 写法与常见失效场景

5.1 能利用索引的典型写法

• 等值匹配：=、IN。
• 范围查询：>、>=、<、<=、BETWEEN。
• 前缀匹配 LIKE：LIKE 'abc%' 可以利用索引（相当于范围查询）。

-- 等值与范围
SELECT * FROM user WHERE id = 100;                     -- 主键 O(log n)
SELECT * FROM user WHERE created_at >= '2024-01-01';   -- 若有 created_at 索引，可走 range

-- LIKE 使用索引
SELECT * FROM user WHERE username LIKE 'abc%';         -- 使用 username 上的索引

5.2 常见索引失效写法

1. 在索引列上使用函数或表达式

-- 索引列上使用函数，索引失效
SELECT * FROM user WHERE DATE(created_at) = '2024-01-01';
-- 改写为：
SELECT * FROM user
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

2. 类型不一致导致隐式转换

-- id 是 INT，却传字符串，可能导致隐式转换而不走索引
SELECT * FROM user WHERE id = '100';

3. 前置通配符 LIKE

SELECT * FROM user WHERE username LIKE '%abc';   -- 无法用 B+Tree 索引（只能扫描）

4. 在组合索引中破坏最左前缀

-- 索引 (username, status)
SELECT * FROM user WHERE status = 1;  -- 跳过 username，无法利用组合索引

5. OR 条件混合可索引与不可索引列

SELECT * FROM user WHERE id = 1 OR email LIKE '%@example.com';
-- 一端可用索引，一端无法使用，会导致优化器选择全表扫描。

---

六、索引设计与实践建议

1. 只为高选择性列建索引：例如用户表的性别（只有 M/F）选择性低，一般不单独建索引。
2. 优先考虑覆盖常用查询的组合索引：根据业务高频 WHERE / ORDER BY / GROUP BY 设计多列索引。
3. 控制索引数量：每个额外索引都会拖慢写入；只保留能带来明显收益的索引。
4. 注意主键选择：InnoDB 的聚簇索引主键会出现在所有二级索引的叶子上，主键过大会让所有索引变胖。
5. 定期用 EXPLAIN 与 SHOW INDEX 回顾现有索引，清理长期不用或设计不合理的索引。

-- 查看表上的索引
SHOW INDEX FROM user;

---

七、进阶：文档之外的一些索引话题

7.1 前缀索引与长字符串

对于很长的 VARCHAR 或 TEXT 字段，直接建全列索引会让索引非常大，可以考虑前缀索引：

-- 只索引 email 前 10 个字符
CREATE INDEX idx_user_email_prefix ON user (email(10));

优点：索引体积更小，提升缓存命中率与构建速度。 缺点：前缀相同的值会更多，区分度变差，可能增加回表次数，需要在「索引大小」与「选择性」之间权衡。

可以通过统计不同前缀长度的 COUNT(DISTINCT LEFT(email, N)) 来决定合适的前缀长度。

7.2 降序索引与排序优化（MySQL 8）

MySQL 8 开始支持显式降序索引：

CREATE INDEX idx_order_user_created ON orders (user_id ASC, created_at DESC);

在以下查询中，优化器可以直接用索引顺序完成 ORDER BY，避免额外排序：

SELECT * FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;

设计组合索引时，可以根据常见的 ORDER BY 顺序，合理安排各列的升/降序，以减少排序成本。

7.3 函数/表达式索引（通过生成列）

MySQL 8 支持函数索引，但在很多场景下也可以通过生成列 + 索引实现「函数索引」效果：

-- 对 email 转小写后建立索引，支持不区分大小写的精确匹配
ALTER TABLE user
  ADD COLUMN email_lower VARCHAR(255)
    GENERATED ALWAYS AS (LOWER(email)) STORED,
  ADD INDEX idx_user_email_lower (email_lower);

SELECT * FROM user WHERE email_lower = LOWER('[email protected]');

这样避免了在 WHERE 里写函数（导致索引失效），又保留了表达式的效果。

7.4 Index Condition Pushdown（ICP）

MySQL 在使用二级索引做范围扫描时，可以把部分 WHERE 条件下推到存储引擎层，在读取行之前利用索引中的列先过滤一轮，减少回表次数，这就是 Index Condition Pushdown：

EXPLAIN SELECT * FROM user
WHERE email LIKE 'a%' AND status = 1;

当 email、status 都在同一个索引上时，InnoDB 可以在索引层面先判断 email LIKE 'a%' AND status = 1，只对少量候选主键做回表，提高效率。 可以在 EXPLAIN 的 Extra 字段中看到 Using index condition，表示启用了 ICP。

7.5 隐藏索引（Invisible Index，MySQL 8）

为了安全地删除索引，可以先把索引标记为不可见（INVISIBLE），观察一段时间后再决定是否真正删除：

-- 将索引设为不可见，优化器不会再使用
ALTER TABLE user ALTER INDEX idx_user_email INVISIBLE;

-- 恢复可见
ALTER TABLE user ALTER INDEX idx_user_email VISIBLE;

这适合在生产环境验证「某个索引是否真的没用」，避免直接 DROP INDEX 带来的不可逆风险。

7.6 索引 Hint：USE / FORCE / IGNORE INDEX

在少数情况下，优化器可能没有选到你期望的索引，可以用 Hint 做临时强制，但不要过度依赖：

-- 建议使用某索引
SELECT * FROM user USE INDEX (idx_user_email)
WHERE email = '[email protected]';

-- 强制使用某索引，否则报错
SELECT * FROM user FORCE INDEX (idx_user_email)
WHERE email = '[email protected]';

-- 忽略某个索引
SELECT * FROM user IGNORE INDEX (idx_user_created)
WHERE created_at >= '2024-01-01';

Hint 会与具体数据分布、版本和执行计划绑定得比较紧，一般只在特别确定优化器选错索引、且查询非常关键时使用。

---

八、小结

• 索引是 MySQL 中最重要的性能工具之一，本质是各种有序结构（主要是 B+Tree）。
• InnoDB 使用聚簇索引 + 二级索引；非主键查询通常要先走二级索引再回表，覆盖索引可以避免回表。
• JDK HashMap 用“数组 + 链表 + 红黑树”控制哈希冲突；MySQL 则用 B+Tree 控制磁盘 IO 与范围扫描，两者都靠“结构 + 策略”来换取查询性能。
• 实际写 SQL 时，需要结合 最左前缀、函数/表达式、类型转换、LIKE 模式 等因素，避免索引失效；同时结合 EXPLAIN 观察执行计划，不断调整索引设计。
• 在此基础上，还可以利用 前缀索引、生成列表达式索引、降序索引、ICP、隐藏索引与 Hint 等进阶能力，在真实业务场景里更细致地打磨查询性能。