数据库：如何设计最佳索引 / 数据库最佳索引设计指南
索引是数据库性能优化的核心武器，但“乱建索引”比“不建索引”更可怕——它会拖慢写入速度、占用额外存储空间，却对查询毫无帮助。
下面我从索引设计原则、实战场景、避坑指南三个维度，系统梳理最佳索引设计方法。
一、索引设计五大核心原则
| 原则 | 说明 | 示例 |
|---|---|---|
| 1. 最左前缀匹配 | 联合索引按定义顺序生效，查询条件必须从索引最左列开始 | 索引(a,b,c)能匹配WHERE a=1、WHERE a=1 AND b=2，但不能匹配WHERE b=2 |
| 2. 高选择性优先 | 优先为区分度高的字段建索引（区分度 = 不同值数量 / 总行数） | 性别（2种）区分度低 ×；手机号（几乎全不同）区分度高 √ |
| 3. 覆盖索引 | 索引包含查询所需的所有字段，避免回表 | SELECT id, name FROM users WHERE id=1→ 索引(id, name)可直接返回，无需回表 |
| 4. 索引下推 | 利用索引过滤更多数据，减少回表次数（MySQL 5.6+） | 联合索引(age, city)，WHERE age>18 AND city='北京'可在索引层过滤 |
| 5. 区分高频查询 | 优先为高频查询条件建索引，低频查询可以不走索引 | 主查询走索引，报表类低频查询可走全表扫描 |
二、实战索引设计方案
2.1 单列索引 vs 联合索引
| 场景 | 推荐索引 | 原因 |
|---|---|---|
| 查询条件单一 | 单列索引 | 简单、轻量 |
| 查询条件固定多个 | 联合索引 | 索引覆盖所有条件，减少回表 |
| 查询条件变化组合 | 维护 2-3 个联合索引 | 覆盖高频组合，避免过多索引 |
联合索引顺序设计口诀：
等值条件放前面，范围条件放后面
| 查询类型 | 索引列顺序 | 原因 |
|---|---|---|
WHERE a=1 AND b=2 | (a, b)或(b, a) | 等值查询顺序不重要 |
WHERE a=1 AND b>10 | (a, b) | a 等值过滤后，b 范围查询仍能利用索引 |
WHERE a>1 AND b=2 | (b, a) | b 等值过滤后，a 范围查询仍能利用索引 |
2.2 覆盖索引设计
| 查询语句 | 推荐索引 | 为什么 |
|---|---|---|
SELECT id, name FROM users WHERE age=25 | (age, name) | 索引包含age和name，无需回表 |
SELECT id, name, age FROM users WHERE age=25 | (age, name, age) | 索引覆盖所有字段，完全覆盖 |
2.3 前缀索引
对于长文本字段（如VARCHAR(255)），可以只索引前 N 个字符：
sql
CREATE INDEX idx_email_prefix ON users (email(10));
| 场景 | 推荐 | 原因 |
|---|---|---|
| 文本字段 | 前缀索引 | 节省空间，提升索引效率 |
| 区分度足够 | 前缀长度 10-15 字符 | 需验证区分度是否够高 |
2.4 索引设计速查表
| 查询场景 | 推荐索引 | 注意事项 |
|---|---|---|
WHERE id = ? | 主键索引（自动） | 不需要额外建索引 |
WHERE name = ? | (name) | 高选择性字段优先 |
WHERE status = 1 AND created_at > '2024-01-01' | (status, created_at) | 等值在前，范围在后 |
WHERE city = '北京' AND age BETWEEN 18 AND 30 | (city, age) | 高选择性优先 |
ORDER BY created_at DESC | (created_at) | 单列索引即可 |
GROUP BY category_id | (category_id) | 分组字段建索引 |
JOIN ON users.id = orders.user_id | 两张表关联字段都建索引 | 外键索引 |
LIKE '张%' | 普通索引可用 | 前缀匹配可用索引 |
LIKE '%张' | ❌ 无法使用索引 | 改为全文索引 |
LIKE '%张%' | ❌ 无法使用索引 | 改为全文索引或Elasticsearch |
三、索引失效场景与避坑指南
| 场景 | 原因 | 解决方案 |
|---|---|---|
WHERE name = '张三' OR status = 1 | OR 两侧都走索引才能生效 | 改为UNION或分别在两侧建索引 |
WHERE YEAR(created_at) = 2024 | 对索引列做函数运算 | 改为created_at BETWEEN '2024-01-01' AND '2024-12-31' |
WHERE name LIKE '%张' | 通配符在开头 | 改为WHERE name LIKE '张%'（前缀匹配） |
WHERE id + 1 = 100 | 对索引列做计算 | 改为WHERE id = 99 |
WHERE name IS NOT NULL | 索引列参与 NULL 判断 | ❌ 不会使用索引，需重写查询 |
| 索引列类型不匹配 | WHERE phone = 123（phone 是字符串） | 改为WHERE phone = '123' |
| 联合索引未遵循最左前缀 | 索引(a,b,c)，查询WHERE b=2 | 补充索引(b)或调整索引顺序 |
四、索引优化实战：从慢查询到快查询
案例：一张订单表（1000 万行数据）
sql
CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT, status TINYINT, amount DECIMAL(10,2), created_at DATETIME, updated_at DATETIME );
1️⃣ 慢查询
sql
SELECT * FROM orders WHERE user_id = 10086 AND status = 1 AND created_at > '2024-06-01' ORDER BY created_at DESC LIMIT 10;
分析：
EXPLAIN显示全表扫描（type=ALL）扫描 1000 万行，耗时 3.2 秒
2️⃣ 设计索引
sql
CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at DESC);
3️⃣ 优化后效果
| 指标 | 优化前 | 优化后 | 提升 |
|---|---|---|---|
| 扫描行数 | 10,000,000 | 50 行 | 减少 99.999% |
| 查询耗时 | 3.2 秒 | 0.01 秒 | 320 倍 |
五、索引监控与维护
| 操作 | 命令 | 作用 |
|---|---|---|
| 查看索引使用情况 | SHOW INDEX FROM table_name; | 查看当前所有索引 |
| 分析查询计划 | EXPLAIN SELECT ...; | 确认是否使用了正确的索引 |
| 查看索引使用频率 | SELECT * FROM sys.schema_index_statistics; | 找出未使用的冗余索引 |
| 重建索引 | ALTER TABLE table_name ENGINE=InnoDB; | 整理索引碎片 |
| 删除无用索引 | DROP INDEX idx_name ON table_name; | 释放存储空间，提升写入性能 |
六、总结：索引设计核心要点
| 原则 | 一句话总结 |
|---|---|
| 最左前缀 | 联合索引，查询条件必须从第一列开始 |
| 高选择性 | 优先给区分度高的字段建索引 |
| 覆盖索引 | 索引包含查询所需的所有字段 |
| 等值在前 | 联合索引中等值条件放前面，范围条件放后面 |
| 避免函数运算 | 不要在索引列上做函数运算 |
| 前缀匹配 | LIKE '张%'可用索引，LIKE '%张'不可 |
| 定期维护 | 删除无用索引，重建碎片索引 |
| 控制在5个以内 | 一张表索引数量建议控制在 5 个以内，过多会影响写入性能 |