设计索引的原则有哪些？
MySQL 索引设计的核心原则是 在查询性能与存储成本之间取得平衡。以下是经过实践验证的 10 大设计原则及具体实现策略：
一、基础原则
| 原则 | 说明 | 示例/反例 |
|---|---|---|
| 1. 高频查询优先 | 为 WHERE、JOIN、ORDER BY、GROUP BY 频繁出现的列建索引 | ✅ SELECT * FROM orders WHERE user_id=100 → 为 user_id 建索引 |
| 2. 高区分度优先 | 选择区分度高的列（唯一值比例 ≈1） | ✅ 身份证号 > 性别 ❌ 在 gender（仅2种值）建索引效果差 |
| 3. 最左前缀匹配 | 联合索引按查询顺序从左到右排列 | 索引 (a,b,c) 生效场景：✓ WHERE a=1✓ WHERE a=1 AND b=2✗ WHERE b=2 |
二、字段选择原则
| 原则 | 说明 | 最佳实践 |
|---|---|---|
| 4. 短字段优先 | 更小的索引 → 更高缓存命中率 | 用 SMALLINT 代替 INT用 CHAR(10) 代替 VARCHAR(100) |
| 5. 整型优于字符型 | 整型比较比字符串快，且节省空间 | 用 IP 转 INT (INET_ATON()) 代替字符串存储 IP |
| 6. 避免 NULL 列 | NULL 增加索引复杂度 | 建表时设置 NOT NULL DEFAULT '' |
三、索引类型选择
| 场景 | 推荐索引类型 | 优势 |
|---|---|---|
| 7. 精确匹配 | B+Tree 索引 | 标准场景，支持 =, >, <, BETWEEN |
| 8. 全文搜索 | FULLTEXT 索引 | 对 TEXT 内容高效搜索 (MATCH AGAINST) |
| 9. 空间数据 | SPATIAL 索引 | 地理位置计算 (GIS) |
| 10. 哈希去重 | 唯一索引 (UNIQUE) | 强制业务唯一性（如用户名） |
四、联合索引设计策略
1. 列顺序决策公式
优先级 = 查询频率 × 区分度
- 正确示例：
表orders的查询模式：
→ 联合索引应设为SELECT * FROM orders WHERE status='paid' -- 区分度低 (3种状态)AND create_time > '2023-01-01' -- 区分度高(create_time, status)
2. 覆盖索引优化
-- 未优化
SELECT name, email FROM users WHERE age>30; -- 优化方案：创建覆盖索引
ALTER TABLE users ADD INDEX idx_age_name_email (age, name, email);
- ✅ 效果：索引覆盖所有查询字段，避免回表
五、避坑指南（常见错误）
| 错误做法 | 问题 | 改进方案 |
|---|---|---|
| 盲目创建索引 | 写性能下降 30%~50% | 用慢查询日志定位真正需要的索引 |
| 无效索引 | WHERE status=1 (status=1 占比 95%) | 删除低区分度索引 |
| 冗余索引 | 已有 (a,b) 又建 (a) | 删除单列索引 (a) |
| 索引列参与运算 | WHERE YEAR(create_time)=2023 | 改范围查询：WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' |
六、高级优化技巧
1. 索引下推 (ICP)
- 启用条件：MySQL 5.6+，联合索引部分条件过滤
- 效果：
-- 索引 (city, age) SELECT * FROM users WHERE city='杭州' AND age>20;-- 5.6 前：先取所有 city='杭州' 数据 → 回表 → 过滤 age>20 -- 5.6+：在索引层直接过滤 age>20 → 仅回表匹配行
2. 索引合并优化
-- 存在索引 (a) 和 (b)
SELECT * FROM table WHERE a=1 OR b=2;-- 优化器可能合并索引扫描 (Index Merge)
七、索引监控与维护
1. 分析索引使用率
-- 查看未使用索引
SELECT * FROM sys.schema_unused_indexes;-- 索引统计信息
SHOW INDEX FROM orders;
2. 碎片整理
-- InnoDB 索引重建
ALTER TABLE orders ENGINE=InnoDB; -- 优化索引页
OPTIMIZE TABLE orders;
八、设计流程图
九、总结：黄金准则
- 必要性原则：只为必要的查询建索引
- 左前缀原则：联合索引严格按查询顺序设计
- 覆盖索引优先：避免
SELECT *回表开销 - 短小精悍：整型优于字符串，小字段优于大字段
- 持续监控：定期清理无效索引（写代价 > 读收益）
📊 数据佐证：根据阿里云数据库团队统计，合理索引设计可使查询性能提升 10~100 倍，降低 70% 的数据库负载。