数据库索引创建原则

Ahuuua

于 2025-04-13 15:58:08 发布

阅读量1k

点赞数 11

文章标签：数据库 sql

本文链接：https://blog.youkuaiyun.com/Ahuuua/article/details/147192842

版权

一、索引的创建原则

1. 明确索引的目标

为高频查询服务：优先为 WHERE 条件、JOIN 关联字段、ORDER BY/GROUP BY 字段 创建索引。
避免“无头苍蝇”式索引：不要为所有字段盲目建索引，需结合具体查询场景。

2. 索引的选择性

高区分度的字段优先：索引列的唯一性越高，过滤效果越好（如身份证号 > 性别）。

-- 低区分度字段（如性别）索引效果差
SELECT * FROM users WHERE gender = 'male'; -- 可能仍需扫描50%数据

3. 控制索引数量

写操作成本增加：每个索引会降低 INSERT/UPDATE/DELETE 的速度。
维护成本高：索引需要占用存储空间，且需定期维护。

建议：单表索引数量一般不超过 5个，复杂业务场景可适当放宽。

二、索引类型的选择

1. 基础索引类型

索引类型	适用场景	示例
B-Tree 索引	等值查询、范围查询、排序	`WHERE id = 100`, `WHERE age > 20`
哈希索引	精确等值查询（不支持范围查询）	Memory引擎表、Redis
全文索引	文本内容的模糊匹配（如关键词搜索）	`WHERE MATCH(content) AGAINST('keyword')`
空间索引	地理空间数据（GIS）	`WHERE ST_Contains(polygon, point)`

2. 复合索引（联合索引）

最左前缀原则：索引 (a, b, c) 可支持 a、a,b、a,b,c 的查询，但无法支持 b 或 c 单独查询。

-- 有效使用索引的情况
SELECT * FROM table WHERE a = 1;
SELECT * FROM table WHERE a = 1 AND b = 2;

-- 无法使用索引的情况
SELECT * FROM table WHERE b = 2;

字段顺序策略：
1. 高频查询条件字段放在最左侧。
2. 高区分度字段优先靠左。

3. 覆盖索引

索引包含查询所需的所有字段：避免回表查询，显著提升性能。

-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(user_id, status, amount);

-- 查询可直接从索引中获取数据
SELECT user_id, status, amount FROM orders WHERE user_id = 100;

三、需要避免的索引陷阱

1. 冗余索引

重复索引：如已存在 (a, b)，再单独创建 (a) 的索引。
无效索引：从未被查询使用的索引。

检查方法：
使用数据库提供的工具分析索引使用情况（如 MySQL 的 sys.schema_unused_indexes）。

2. 长字段索引

文本或大字段索引：会占用大量空间，效率低下。
优化方案：
- 使用前缀索引（MySQL）：
```
CREATE INDEX idx_prefix ON articles(title(20)); -- 仅索引前20个字符
```
- 使用哈希摘要字段（如 MD5 值）并为其建索引。

3. 函数或表达式索引

隐式转换导致索引失效：

-- 假设 phone 是字符串类型，但查询时使用数字
SELECT * FROM users WHERE phone = 13800138000; -- 索引失效

解决方案：保持查询条件与索引字段类型一致。

四、索引的维护与优化

1. 定期维护

重建索引：解决索引碎片化问题（如 MySQL 的 OPTIMIZE TABLE）。
监控索引大小：避免索引占用过多存储空间。

2. 分析执行计划

使用 EXPLAIN 或数据库的性能分析工具，确认索引是否被正确使用：

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

关注关键指标：type（访问类型）、key（使用的索引）、rows（扫描行数）。

3. 动态调整

删除无用索引：根据监控结果清理冗余索引。
适时调整索引策略：随着业务变化，查询模式可能改变，需重新评估索引。

五、不同数据库的注意事项

数据库	索引特性
MySQL	默认使用 B+Tree；全文索引仅限 MyISAM/InnoDB；支持自适应哈希索引（AHI）。
PostgreSQL	支持 B-Tree、Hash、GIN、GiST 等多种索引；表达式索引功能强大。
Oracle	支持位图索引（适用于低区分度字段）、函数索引、反向键索引等。
SQL Server	支持包含列（INCLUDE）的覆盖索引、过滤索引（WHERE 条件过滤）。