MySQL索引设计与优化全攻略：从B+树到三星索引

关键词：MySQL索引, B+树索引, 聚簇索引, 联合索引, 覆盖索引, 前缀索引, 三星索引, 索引优化

你是否在面试中被问过"聚簇索引和非聚簇索引有什么区别？"、“什么是回表？如何避免？”、"如何设计一个高性能的索引？"这些问题看似简单，但涉及MySQL索引的核心机制和优化策略。本文将从InnoDB索引的数据结构出发，深入剖析各种索引类型的特点，详解索引设计的最佳实践和三星索引理论，帮你彻底掌握MySQL索引设计与优化。

---

目录

1. MySQL索引类型概述
2. B+树索引详解
   • 聚簇索引（聚集索引）
   • 辅助索引（二级索引）
   • 回表与覆盖索引
   • 联合索引
3. 哈希索引
4. 全文索引
5. 高性能索引创建策略
   • 索引列类型尽量小
   • 索引的选择性
   • 前缀索引
   • 只为必要列创建索引
   • 多列索引设计
6. 三星索引理论

---

1. MySQL索引类型概述

InnoDB存储引擎支持以下几种常见的索引：

索引类型	特点	适用场景
B+树索引	最常用，支持范围查询和排序	绝大多数查询场景
哈希索引	自适应生成，等值查询快	等值查询，不支持范围查询
全文索引	支持全文检索	文本搜索（功能较弱）

其中，B+树索引是最关键的索引类型，也是本文的重点。

---

2. B+树索引详解

InnoDB中的索引按照B+树来组织。B+树的叶子节点用来放数据，但放什么数据取决于索引类型。

2.1 聚簇索引（聚集索引）

核心特点：

• 将表的主键用来构造一棵B+树
• 将整张表的行记录数据存放在该B+树的叶子节点中
• 索引即数据，数据即索引

重要限制：

• 每张表只能拥有一个聚簇索引
• 聚簇索引利用表的主键构建

优点：

1. 通过聚簇索引能获取完整的整行数据（叶子节点就是数据页）
2. 主键的排序查找和范围查找速度非常快（B+树本身有序）

如果没有定义主键怎么办？

1. MySQL会使用唯一性索引作为主键
2. 如果没有唯一性索引，MySQL会创建一个隐含列RowID来做主键
3. 然后用这个主键来建立聚簇索引

示例：

-- 主键id就是聚簇索引
CREATE TABLE user (
    id INT PRIMARY KEY,  -- 聚簇索引
    name VARCHAR(50),
    email VARCHAR(100)
);

2.2 辅助索引（二级索引）

为什么需要辅助索引？

聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。如果想以别的列作为搜索条件，就需要建立辅助索引（也称二级索引、非聚集索引）。

核心特点：

• 每建立一个索引，就有一棵B+树
• 叶子节点不包含行记录的全部数据
• 叶子节点包含键值 + 书签（bookmark）
• 书签就是相应行数据的聚集索引键（主键值）

工作原理：

辅助索引index(note)的叶子节点包含：note字段值 + 主键值

示例：

-- name列创建辅助索引
CREATE INDEX idx_name ON user(name);

-- 查询时：先通过idx_name找到主键值，再通过聚簇索引找到完整记录
SELECT * FROM user WHERE name = '张三';

2.3 回表与覆盖索引

什么是回表？

当通过辅助索引来寻找数据时：

1. InnoDB遍历辅助索引，获得主键值
2. 通过主键值在聚簇索引中找到完整的行记录

这个过程称为回表。根据辅助索引的值查询一条完整的用户记录，需要使用2棵B+树——一次辅助索引，一次聚簇索引。

为什么需要回表？

为什么不把完整的用户记录放到辅助索引的叶子节点？

• 存储空间浪费：每建立一棵B+树都要拷贝所有用户记录
• 维护成本高：数据变化时要在所有索引中修改

回表的性能影响：

• 回表记录越少，性能提升越高
• 回表记录越多，使用二级索引的性能越低
• 某些查询宁愿使用全表扫描也不使用二级索引

什么是覆盖索引？

覆盖索引（Covering Index）：从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录（不需要回表）。

优点：

• 辅助索引不包含整行记录的所有信息，大小远小于聚集索引
• 可以减少大量的IO操作
• 性能几乎提升一倍

示例：

-- 创建联合索引
CREATE INDEX idx_name_email ON user(name, email);

-- 覆盖索引查询：只需要扫描索引，不需要回表
SELECT name, email FROM user WHERE name = '张三';

-- 非覆盖索引查询：需要回表获取其他字段
SELECT name, email, age FROM user WHERE name = '张三';

2.4 联合索引

基本概念

将表上的多个列组合起来进行索引，称为联合索引或复合索引。

重要区别：

• index(a,b)：建立1棵B+树，组合索引
• index(a) + index(b)：建立2棵B+树，独立索引

排序规则：

1. 先把各个记录按照**第一列（a）**进行排序
2. 在第一列相同的情况下，采用**第二列（b）**进行排序

示例：

-- 联合索引
CREATE INDEX idx_a_b ON table_name(a, b);

-- 查询时遵循最左前缀法则
SELECT * FROM table_name WHERE a = 1;           -- 可以使用索引
SELECT * FROM table_name WHERE a = 1 AND b = 2; -- 可以使用索引
SELECT * FROM table_name WHERE b = 2;           -- 无法使用索引

最左前缀法则

联合索引的使用必须遵循最左前缀法则：

• 查询条件必须从索引的最左列开始
• 不能跳过中间的列
• 范围查询右边的列无法使用索引

---

3. 哈希索引

3.1 自适应哈希索引

B+树的查找次数：取决于B+树的高度，生产环境中一般为3-4层，需要3-4次IO查询。

自适应哈希索引（Adaptive Hash Index, AHI）：

• InnoDB内部自动监控索引表
• 如果某个索引经常使用，认为是热数据
• 内部自动创建哈希索引
• 下次查询时直接通过hash算法推导出记录地址
• 一次查询就能查到数据，比B+树的3-4次查询效率高

3.2 哈希索引的特点

特性	说明
创建方式	数据库自动创建，用户无法干预
哈希函数	采用除法散列方式
冲突解决	采用链表方式
适用查询	只能用于等值查询（=）
不适用	范围查询（<, >, BETWEEN等）

查看自适应哈希索引状态：

SHOW ENGINE INNODB STATUS\G

关注指标：hash searches vs non-hash searches

• hash searches比例高，说明AHI效率高
• non-hash searches比例高，说明范围查询多，AHI无法发挥作用

控制参数：

-- 查看自适应哈希索引是否开启（默认开启）
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

-- 禁用自适应哈希索引
SET GLOBAL innodb_adaptive_hash_index = OFF;

---

4. 全文索引

4.1 什么是全文检索

全文检索（Full-Text Search）：将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。可以根据需要获得全文中有关章、节、段、句、词等信息。

常见全文检索引擎：Elasticsearch、Solr（基于Apache Lucene）

4.2 倒排索引

问题场景：查找包含"望"字的诗词

-- 使用LIKE查询需要全表扫描
SELECT 诗词全文 FROM 诗词表 WHERE 诗词全文 LIKE '%望%';

倒排索引原理：

1. 将文档中包含的关键字全部提取出来
2. 保存关键字和文档的对应关系
3. 对关键字本身做索引排序
4. 用户检索时，先对关键字索引进行查找，再通过对应关系找到所在文档

倒排索引表示例：

关键字	蜀道难	静夜思	春台望	鹤冲天
望	有	有	有	有
上	有			有

4.3 MySQL全文索引的局限性

支持版本：InnoDB从1.2.x版本（MySQL 5.6.x）开始支持全文检索。

限制：

• 每张表只能有一个全文检索的索引
• 不支持没有单词界定符的语言（中文、日语、韩语等）
• 整体架构上对全文检索支持并不好

结论：MySQL中的全文索引功能比较弱，生产环境建议使用Elasticsearch等专业搜索引擎。

---

5. 高性能索引创建策略

5.1 索引列类型尽量小

原则：在表示的整数范围允许的情况下，尽量让索引列使用较小的类型。

推荐顺序：

• 能使用TINYINT就不要使用SMALLINT
• 能使用MEDIUMINT就不要使用INT
• 能使用INT就不要使用BIGINT

原因：

1. 比较操作更快：数据类型越小，CPU层次的比较操作越快
2. 占用存储空间少：索引占用的存储空间越少
3. 更多记录放入数据页：一个数据页内可以放下更多的记录
4. 减少磁盘IO：减少磁盘IO带来的性能损耗
5. 更多数据缓存在内存：加快读写效率

对主键尤其重要：

• 聚簇索引中存储主键值
• 所有二级索引的节点处都会存储一份记录的主键值
• 主键使用更小的数据类型，节省更多存储空间和IO

5.2 索引的选择性

索引的选择性（离散性）：不重复的索引值（基数，cardinality）和数据表的记录总数（N）的比值。

公式：

选择性 = COUNT(DISTINCT 列) / COUNT(*)

范围：从1/N到1之间

原则：

• 选择性越高，查询效率越高
• 唯一索引的选择性是1（最好）
• 避免选择性很低的列（如性别字段只有男女两种值）

计算示例：

-- 计算各列的选择性
SELECT COUNT(DISTINCT name)/COUNT(*) AS name_selectivity FROM person;
SELECT COUNT(DISTINCT sex)/COUNT(*) AS sex_selectivity FROM person;
SELECT COUNT(DISTINCT age)/COUNT(*) AS age_selectivity FROM person;
SELECT COUNT(DISTINCT area)/COUNT(*) AS area_selectivity FROM person;

建议：

• 姓名字段适合做索引（数据不重复，选择性高）
• 性别字段最不适合做索引（数据重复度高，选择性低）

5.3 前缀索引

适用场景：针对BLOB、TEXT、很长的VARCHAR字段，MySQL不支持索引全部长度，需要建立前缀索引。

创建语法：

ALTER TABLE table_name ADD KEY (column(X));
-- X表示索引前X个字符

确定前缀长度：

-- 计算不同前缀长度的选择性
SELECT 
    COUNT(DISTINCT LEFT(order_note, 3))/COUNT(*) AS sel3,
    COUNT(DISTINCT LEFT(order_note, 4))/COUNT(*) AS sel4,
    COUNT(DISTINCT LEFT(order_note, 5))/COUNT(*) AS sel5,
    COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) AS sel10,
    COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) AS sel15,
    COUNT(DISTINCT order_note)/COUNT(*) AS total
FROM order_exp;

选择标准：

• 选择性提升幅度很小即可（如第13到第15个字符选择性已接近整列）
• 选择能区分度高且长度适中的前缀

创建示例：

-- 建立前缀索引（前14个字符）
ALTER TABLE order_exp ADD KEY (order_note(14));

-- 查询时不需要更改语句
SELECT * FROM order_exp WHERE order_note = 'xxxx';

前缀索引的缺点：

• ❌ 无法使用前缀索引做ORDER BY和GROUP BY
• ❌ 无法使用前缀索引做覆盖扫描

后缀索引：

• 用于查找某个域名的所有电子邮件地址等场景
• MySQL原生不支持反向索引
• 可以把字符串反转后存储，基于建立前缀索引

5.4 只为必要列创建索引

原则：只为出现在以下场景的列创建索引：

1. WHERE子句中的列

   -- 只为条件中的列建立索引
   SELECT order_note FROM ... WHERE condition_column = 'xxx';
   

2. 连接子句中的连接列

   -- 连接列需要索引
   SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
   

3. ORDER BY或GROUP BY子句中的列

   -- 联合索引可以用于排序
   SELECT * FROM order_exp 
   ORDER BY insert_time, order_status, expire_time;
   
   -- 如果联合索引是(insert_time, order_status, expire_time)
   -- 可以直接从索引中提取数据，避免排序
   

不需要索引的列：

• 查询列表（SELECT）中的列（除非需要覆盖索引）
• 很少用于查询条件的列

5.5 多列索引设计

常见错误：

• 为每个列创建独立的索引
• 按照错误的顺序创建多列索引

多列B+树索引的排序规则：

1. 首先按照最左列进行排序
2. 其次是第二列
3. 以此类推

索引列顺序的经验法则：

1. 将选择性最高的列放到索引最前列

   • 当不需要考虑排序和分组时适用
   • 索引作用只是优化WHERE条件的查找

2. 根据运行频率最高的查询调整

   • 如果查询经常有排序和分组
   • 让索引的顺序满足ORDER BY需求

3. 使用相同列但顺序不同的索引

   • 满足不同类型的查询需求
   • 如：(a,b,c)和(a,c,b)

示例分析：

-- 查询SQL
SELECT user_name, sex, age 
FROM test 
WHERE user_name LIKE 'test%' AND sex = 1 
ORDER BY age;

-- 方案1：索引(user_name, sex, age)
-- 优点：第一颗星（窄索引片）满足，第三颗星（覆盖索引）满足
-- 缺点：user_name采用范围匹配，sex是过滤列，age无法保证有序（不满足第二颗星）

-- 方案2：索引(sex, age, user_name)
-- 优点：第二颗星（排序）满足，第三颗星满足
-- 缺点：只匹配到sex（选择性差），是宽索引片（不满足第一颗星）

-- 结论：无法同时满足三颗星，需要根据业务侧重选择

---

6. 三星索引理论

6.1 三星索引的概念

对于一个查询而言，三星索引可能是其最好的索引。

三颗星的定义：

星级	条件	权重	说明
一星	索引将相关的记录放到一起	27%	索引片尽量变窄
二星	索引中的数据顺序和查找中的排列顺序一致	23%	避免额外排序
三星	索引中的列包含了查询中需要的全部列	50%	覆盖索引，避免回表

哪颗星最重要？第三颗星（宽索引星），因为将一个列排除在索引之外可能导致很多磁盘随机读（回表操作）。

6.2 三星索引详解

第一颗星：窄索引片

让索引片尽量变窄，也就是索引的扫描范围越小越好。

实现方式：

• 所有等值谓词的列，是组合索引的开头的列
• 可以把索引片缩得很窄

第二颗星：排序星

当查询需要排序（GROUP BY、ORDER BY）时，如果查询所需的顺序与索引是一致的（索引本身是有序的），就可以不用再另外排序。

重要性：排序是影响性能的关键因素。

第三颗星：宽索引星

索引中所包含了这个查询所需的所有列（包括WHERE子句和SELECT子句中所需的列），即覆盖索引。

效果：查询不再需要回表，减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。

6.3 三星索引实战

案例1：三星索引完美示例

-- 表结构
CREATE TABLE customer (
    cno INT,
    lname VARCHAR(10),
    fname VARCHAR(10),
    sex INT,
    weight INT,
    city VARCHAR(10)
);

-- 创建索引
CREATE INDEX idx_cust ON customer (city, lname, fname, cno);

-- 查询SQL
SELECT cno, fname 
FROM customer 
WHERE lname = 'xx' AND city = 'yy' 
ORDER BY fname;

评估：

• ✅ 第一颗星：city和lname是等值谓词，是组合索引的开头列，索引片缩得很窄
• ✅ 第二颗星：ORDER BY的fname字段在组合索引中，且索引自动排序好
• ✅ 第三颗星：SELECT中的cno和fname都在组合索引中，不需要回表

这是一个完美的三星索引！

案例2：无法同时满足三星

-- 查询SQL
SELECT user_name, sex, age 
FROM test 
WHERE user_name LIKE 'test%' AND sex = 1 
ORDER BY age;

方案1：索引(user_name, sex, age)

• ✅ 第一颗星满足：窄索引片
• ✅ 第三颗星满足：覆盖索引
• ❌ 第二颗星不满足：user_name范围匹配，age无法保证有序

方案2：索引(sex, age, user_name)

• ✅ 第二颗星满足：等值sex情况下，age是有序的
• ✅ 第三颗星满足：覆盖索引
• ❌ 第一颗星不满足：只匹配到sex（选择性差），是宽索引片

结论：

• 无法同时满足三颗星
• 需要根据业务侧重选择保留哪两颗星
• 多数情况下，满足2颗星已经能缩小很大的查询范围

---

总结

本文深入讲解了MySQL索引的设计与优化，从B+树索引的数据结构到三星索引理论：

核心知识点：

1. 索引类型：

   • 聚簇索引：主键构建，叶子节点存放完整数据，每张表只有一个
   • 辅助索引：叶子节点存放键值+主键，需要回表获取完整数据
   • 覆盖索引：查询列都在索引中，避免回表
   • 联合索引：多列组合，遵循最左前缀法则

2. 其他索引：

   • 哈希索引：自适应生成，仅支持等值查询，范围查询无效
   • 全文索引：支持全文检索，但功能较弱，建议使用Elasticsearch

3. 索引设计策略：

   • 索引列类型尽量小，节省存储和IO
   • 选择选择性高的列作为索引
   • 长字符串使用前缀索引
   • 只为WHERE、JOIN、ORDER BY、GROUP BY中的列创建索引
   • 多列索引注意列的顺序

4. 三星索引理论：

   • 一星：窄索引片（等值谓词在前）
   • 二星：排序星（避免额外排序）
   • 三星：覆盖索引（避免回表）
   • 三星最重要，但通常只能满足两星，需要根据业务权衡

实际应用建议：

• 优先考虑覆盖索引（第三颗星），性能提升最明显
• 对于范围查询，第一颗星和第二颗星往往只能满足一个
• 避免在低选择性列上创建索引（如性别）
• 联合索引的列顺序：等值查询列在前，范围查询列在后
• 定期分析索引使用情况，删除无用索引

面试高频问题：

• 聚簇索引和非聚簇索引的区别？（数据存储位置不同）
• 什么是回表？如何避免？（辅助索引查聚簇索引，用覆盖索引避免）
• 最左前缀法则是什么？（联合索引从最左列开始匹配）
• 索引的选择性是什么？（不重复值/总记录数）
• 三星索引是哪三星？（窄索引片、排序、覆盖）
• 哈希索引和B+树索引的区别？（等值查询vs范围查询）

希望这篇文章能帮助你彻底掌握MySQL索引设计与优化！如果觉得有帮助，欢迎点赞、收藏、关注～

---

推荐标签：

• MySQL
• 索引优化
• B+树
• 聚簇索引
• 联合索引
• 覆盖索引
• 三星索引
• 面试