索引#

#索引

刷电商APP搜小众零食、登录后台查上周订单——这些高频场景的背后，都藏着索引这个性能“加速外挂”。relational-database的索引就像实体书的目录：如果书没有目录，找知识点就得逐页翻；数据库没有索引，查数据就只能全表扫描，百万级数据可能卡几秒甚至更久，完全不适合生产环境。

创建索引确实会消耗磁盘和内存（就像目录占书的篇幅、也得印在脑子里方便查），但“用空间换查询时间”这笔账，在绝大多数读多写少的业务场景下，绝对是稳赚不赔的。

#MySQL InnoDB 的核心索引：B+Tree

MySQL 8.0 InnoDB 支持 B+Tree、全文、R-Tree 三种索引，但 99% 的业务场景都在靠 B+Tree ——原因很简单，它是目前磁盘海量数据存储与排序领域效率天花板级别的数据结构。

#B+Tree 的基本结构

它是一棵严格的平衡树（所有叶子节点到根节点的路径长度完全一致），树高通常只有 3~4 层，却能装下百万到十亿级的数据。查单条记录只需要 3~4 次磁盘 I/O——机械硬盘一次 I/O 是毫秒级，3 次就是 3ms 以内，SSD 更能降到微秒级，完美符合“毫秒级响应”的业务要求。

B+Tree 按角色分成三层结构（数据极少时只有一层根节点）：

1. 根节点：不存实际数据，只存“下层节点的指针 + 范围边界值”，用来快速定位目标落在哪个中间/叶子节点。
2. 中间节点：作用与根节点类似，也是“边界值 + 指针”，进一步缩小查找范围。
3. 叶子节点：唯一存实际数据的地方，而且所有叶子节点按索引列的升序（默认） 用双向链表连接——这让范围查询（例如查价格在 100~200 的商品）变得极快：不用再跳回上层节点反复查找，直接顺链表扫描即可。

另外，叶子节点内部数据也是按索引列排好序的，因此查单条数据时可以用二分查找，效率远高于顺序扫描。

#聚集索引 vs 非聚集索引

InnoDB 是索引组织表（数据本身按主键索引的顺序存储），因此索引被分成两类，这也是它与 MyISAM 等堆组织表最本质的区别：

#1. 聚集索引（Clustered Index）

• 定义：表的主键默认就是聚集索引（如果没显式指定主键，InnoDB 会自动找第一个非空唯一索引当聚集索引；还找不到，就生成一个 6 字节的隐藏自增 ID）。
• 特点：叶子节点存的是整行完整数据——就像是“书的正文完全按目录顺序排”，通过聚集索引查找，等于直接翻到正文，不需要回表。
• 限制：一张表只能有一个聚集索引，否则整行数据要存多份，既浪费空间又破坏一致性。

#2. 非聚集索引（二级索引 / 非主键索引）

• 定义：我们自己手动创建的索引（比如按学生姓名、商品分类建的）都是非聚集索引。
• 特点：叶子节点存的是索引列的值 + 主键值——相当于“目录里只标了知识点在第几章（主键），想看正文还得回到聚集索引的目录找到对应页码（主键定位整行）”。这个“翻回聚集索引”的动作就叫回表。
• 性能：通常比聚集索引慢，因为多了一次回表 I/O。

#索引实战：从 EXPLAIN 看性能变化

光说原理太抽象。我们就用按学生姓名查学生这个高频场景，结合 MySQL 自带的 EXPLAIN 命令（查看 SQL 执行计划的利器），看看索引到底是怎么“加速”的。

#1. 无索引：全表扫描

假设 tb_student 表只有主键，没有给 stuname 加索引：

EXPLAIN SELECT * FROM tb_student WHERE stuname = '林震南'\G

输出（只保留核心字段）：

*************************** 1. row ***************************
         type: ALL               -- 重点：ALL = 全表扫描！
possible_keys: NULL              -- 没有可用索引
          key: NULL              -- 实际也没用索引
         rows: 11                -- 预估扫描 11 行
        Extra: Using where       -- 只能逐行匹配过滤

这种计划非常糟糕——如果表里有 100 万行数据，就得扫整整 100 万行。

#2. 加普通索引：快速定位

给 stuname 建一个普通非聚集索引：

CREATE INDEX idx_student_name ON tb_student(stuname);

再次执行 EXPLAIN：

*************************** 1. row ***************************
         type: ref               -- 非唯一索引等值查询，比 ALL 快 N 倍
possible_keys: idx_student_name  -- 有候选索引了
          key: idx_student_name  -- 实际选中该索引
         rows: 1                 -- 只预估扫描 1 行
        Extra: NULL              -- 没有额外开销，完美

速度直接起飞！扫描行数从 11 降到 1，Using where 也消失了。

#3. 前缀索引：空间与时间的取舍

如果 stuname 是 VARCHAR(200) 这种长字符串，建完整索引会占用较多磁盘和内存。这时可以考虑前缀索引，只取前 N 个字符建索引：

-- 假设“林震南”、“林平之”等姓林的学生不多，取前 1 个字符就能区分大部分数据
CREATE INDEX idx_student_name_1 ON tb_student(stuname(1));

查看执行计划：

*************************** 1. row ***************************
         type: ref
possible_keys: idx_student_name,idx_student_name_1
          key: idx_student_name_1  -- 优先使用了更短的前缀索引（省内存）
      key_len: 5                   -- 索引长度明显变小
         rows: 2                   -- 预估扫 2 行（比如有两个姓林的）
        Extra: Using where         -- 仍需用完整姓名再筛选一次

前缀索引省了空间，但为了精确匹配剩余的字符串，可能要多扫几行并触发 Using where 过滤。选多长的前缀很看业务数据分布：前缀太短，区分度低，效果接近全表扫描；前缀太长，节省空间的意义又不大。这就是计算机世界里经典的“时间-空间不可调和矛盾”，需要根据实际数据做权衡。

#4. 删除不再需要的索引

不需要的索引一定要及时删除，它会拖慢写操作（增、删、改数据时，所有索引都要同步更新，好比书的章节调整了，所有目录都得重印）。删除索引有两种写法：

-- 方式一：ALTER TABLE
ALTER TABLE tb_student DROP INDEX idx_student_name;

-- 方式二：DROP INDEX（语法更清晰，推荐）
DROP INDEX idx_student_name_1 ON tb_student;

#索引设计的核心原则（避坑指南）

很多人误以为“索引越多越好”，其实大错特错——错误的索引不仅没用，还会拖慢系统。记住下面 5 条原则，可以避开 80% 的索引坑：

1. 选对列建索引：优先给 WHERE、JOIN、ORDER BY、GROUP BY 里出现的列建索引。
2. 选基数大的列：基数 = 索引列唯一值的数量（性别只有男/女，基数很小；手机号、身份证号基数接近表行数）。基数越大，索引区分度越高，效果越好。
3. 合理使用前缀索引：长字符串列优先考虑前缀索引，根据业务数据分布调整前缀长度。
4. 索引不是越多越好：一般单表索引控制在 5 个以内。过多的索引会：① 占用大量磁盘和内存；② 严重拖慢增删改操作；③ 让优化器在选择索引时“犹豫”，反而可能选错。
5. 主键尽量短且有序：InnoDB 的非聚集索引叶子节点都会存主键值，主键越短（比如用 INT 自增，别用长 UUID），非聚集索引就越小，内存里能缓存的数据就越多，查询就越快。

#InnoDB B+Tree 索引的“生效边界”

并非所有带索引列的 SQL 都能用到索引，以下情况索引会失效（大概率退化为全表扫描）：

• 数值类型：只有 =、>、<、>=、<=、BETWEEN...AND...、IN 能正常走索引；NOT IN、<>（不等于）通常会导致失效（除非基数极大）。
• 字符串类型：只有前缀不模糊的查询（例如 stuname LIKE '林%'）会走索引；LIKE '%林'、LIKE '%林%' 都会失效。
• 索引列参与运算或函数：例如 WHERE YEAR(create_time) = 2024、WHERE age + 1 = 18 都会失效。应该改成 WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'、WHERE age = 17。
• 复合索引不遵循最左前缀原则：如果建了 idx_name_age_gender，只查 age 或只查 gender，又或者先查 age 再查 name，索引都会失效——必须从索引的第一个列（最左前缀）开始连续匹配。

掌握上面这些 MySQL InnoDB B+Tree 索引的核心知识，就足以应付绝大多数业务场景的性能问题了！