MySQL InnoDB索引详解

InnoDB索引类型包括主键索引、辅助索引、唯一索引、组合索引等，底层采用B+树结构，主键索引存储整行数据，辅助索引存储主键值需"回表"查询。优化建议包括：主键使用自增整型、高选择性字段优先索引、遵循最左前缀原则构建联合索引、避免索引失效场景等。提供了索引创建、查看和删除等实用命令，以及EXPLAIN分析SQL执行计划的方法。

onlymscn

297人浏览 · 2025-09-16 00:54:18

onlymscn · 2025-09-16 00:54:18 发布

一、InnoDB 索引类型

1. 主键索引（PRIMARY KEY）

每个 InnoDB 表必须有一个主键，没有则会自动创建一个隐藏主键。
主键索引是聚簇索引（Clustered Index），索引的叶子节点存储整行数据。
主键索引决定了数据在物理页上的存储顺序。

2. 辅助索引（Secondary Index）

又叫非聚簇索引，可以是普通索引、唯一索引、组合索引等。
辅助索引的叶子节点存储的是主键值，需要通过主键值“回表”获取完整行数据。

3. 唯一索引（UNIQUE INDEX）

保证索引列的值唯一。
实现方式和辅助索引类似，只是加了唯一性约束。

4. 组合索引（联合索引）

多个列组成一个索引，支持多条件查询。
遵循最左前缀原则，只有从最左字段开始的连续条件才能用到索引。

5. 全文索引（FULLTEXT）

用于对大文本字段（如文章内容）进行分词、全文检索。
InnoDB 从 MySQL 5.6 开始支持 FULLTEXT 索引。

6. 空间索引（SPATIAL INDEX）

用于地理空间数据（GIS），如点、线、面等。

二、InnoDB 索引底层原理

1. B+树结构

InnoDB 所有索引（包括主键、辅助索引）都采用B+树结构。
B+树特点：有序、层次化，每个节点可以存储多个键值，便于范围查询。
主键索引叶子节点存储整行数据，辅助索引叶子节点存储主键值。

2. 聚簇索引与非聚簇索引

聚簇索引：主键索引，叶子节点是整行数据。
非聚簇索引：辅助索引，叶子节点是主键值，查完整数据需回表。

3. 回表查询

通过辅助索引查找时，先定位到主键值，再通过主键索引查找整行数据，称为“回表”。

三、InnoDB 索引的特性

自动主键管理：没有主键时会自动创建隐藏主键。
支持多列联合索引：优化多条件查询。
支持前缀索引：对长字符串字段只索引前 N 个字符。
支持唯一约束：保证数据唯一性。
支持全文检索：适合文本搜索场景。
支持自适应哈希索引：InnoDB 会自动维护一份哈希索引（仅用于内存加速，用户不可见）。

四、索引使用场景与优化建议

1. 主键设计建议

主键尽量使用自增整型，避免业务字段（如手机号、身份证号）做主键。
主键字段应短小、稳定、不变更。

2. 索引字段选择建议

WHERE、ORDER BY、JOIN、GROUP BY 中频繁出现的字段建议加索引。
高选择性（区分度高）的字段优先加索引。
低选择性（如性别、状态）一般不加索引。

3. 联合索引优化

多条件查询优先考虑联合索引。
联合索引字段顺序遵循“最左前缀原则”和“高选择性字段在前”原则。

4. 覆盖索引

查询字段都在索引中时，可避免回表，提高查询效率。

5. 避免冗余索引

避免重复或无效索引，减少写入开销。

6. 索引失效场景

在索引字段上使用函数或表达式（如 WHERE YEAR(create_time)=2023）会导致索引失效。
LIKE ‘%xxx’ 不能用索引，LIKE ‘xxx%’ 可以用索引。
数据类型不一致（如字符串与数字比较）会导致索引失效。

五、实用命令与案例

1. 添加索引

ALTER TABLE user ADD INDEX idx_email(email);
ALTER TABLE orders ADD INDEX idx_user_status(user_id, status);

2. 查看索引

SHOW INDEX FROM user;

3. 删除索引

DROP INDEX idx_email ON user;

4. 使用 EXPLAIN 分析 SQL

EXPLAIN SELECT * FROM user WHERE email='abc@xx.com';

六、索引设计建议

主键选择
- 优先选择自增整型（如 BIGINT UNSIGNED）为主键，短小且无业务含义。
- 避免用业务字段（手机号、身份证号等）做主键。
高选择性字段优先建索引
- 选择性 = 不同值数量 / 总行数，越高越适合建索引。
- 例如用户ID、订单号等。
WHERE、ORDER BY、JOIN、GROUP BY 常用字段要建索引
- 优化常用查询路径，减少全表扫描。
优先考虑联合索引，而非多个单列索引
- 多条件查询建议用联合索引。
- 联合索引字段顺序遵循“最左前缀”原则和“高区分度字段在前”原则。
覆盖索引提升查询效率
- 查询字段全部在索引中，无需回表。
- 例如 SELECT id, name WHERE name=‘xxx’，有 (name, id) 索引。
合理使用前缀索引
- 对长字符串字段（如 VARCHAR），只索引前 N 个字符（如 name(20)），减少索引体积。
唯一性约束用 UNIQUE 索引
- 保证数据唯一性，如邮箱、用户名等。

七、避免索引失效

避免在索引字段上使用函数或表达式
- 如 WHERE DATE(created_at) = ‘2023-01-01’，应改为 WHERE created_at BETWEEN ‘2023-01-01 00:00:00’ AND ‘2023-01-01 23:59:59’。
LIKE 查询避免使用前置通配符
- LIKE ‘%abc’ 无法用索引，LIKE ‘abc%’ 可以。
类型一致性
- 查询条件类型应与字段类型一致，避免隐式转换导致索引失效。
避免对低选择性字段建索引
- 如性别、状态等枚举值，通常不建索引。