数据库 3 索引原理实战#

一、B+树、聚簇与非聚簇索引#

绝大多数关系数据库默认索引结构为B+树。

• B+树特点：所有数据存放在叶子节点，叶子节点链表相连，范围查询高效；非叶子节点仅保存索引键，内存占用小。

1
-- MySQL 创建普通二级索引
2
CREATE INDEX idx_user_name ON user(name);

二、联合索引与最左匹配原则#

联合索引：多个字段组合建立索引。 最左匹配：查询条件必须匹配索引最左侧起始字段，索引才能生效。

1
-- 联合索引：(name,age)
2
CREATE INDEX idx_name_age ON user(name,age);
3

4
-- 走索引（满足最左前缀）
5
SELECT * FROM user WHERE name='test';
6
SELECT * FROM user WHERE name='test' AND age=20;
7

8
-- 索引失效（跳过最左列）
9
SELECT * FROM user WHERE age=20;

三、各类特殊索引#

• 唯一索引：索引列值不可重复，允许单个NULL

1
CREATE UNIQUE INDEX idx_phone ON user(phone);

• 覆盖索引：查询字段全部包含在索引内，无需回表，性能最优

1
-- 索引(name,age)，查询只取name、age，触发覆盖索引
2
SELECT name,age FROM user WHERE name='test';

• 全文索引：用于文本模糊检索，替代低效like模糊查询

1
-- MySQL全文索引示例
2
CREATE FULLTEXT INDEX idx_content ON article(content);

四、三大数据库索引特色差异#

1. MySQL(InnoDB) 独有聚簇索引；不支持位图索引；依靠undo log实现MVCC配合索引优化。

2. Oracle 支持位图索引，适合低基数数据（性别、状态）；无聚簇索引。

   位图索引不适合并发DML，容易触发锁冲突。

3. SQLServer 支持包含列索引，把非索引字段附加到索引，实现覆盖索引，无需拓展联合索引。

1
-- SQLServer 包含列索引示例
2
CREATE INDEX idx_name ON user(name) INCLUDE(age,email);

小结#

1. B+树是通用主流索引结构；联合索引严格遵守最左匹配；
2. 覆盖索引是日常优化首选，尽量避免回表；
3. 选型区分数据库特性：聚簇索引认准InnoDB，位图索引使用Oracle，包含列索引为SQLServer方案。