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;

---

八、设计流程图

九、总结：黄金准则

1. 必要性原则：只为必要的查询建索引
2. 左前缀原则：联合索引严格按查询顺序设计
3. 覆盖索引优先：避免 SELECT * 回表开销
4. 短小精悍：整型优于字符串，小字段优于大字段
5. 持续监控：定期清理无效索引（写代价 > 读收益）

📊 数据佐证：根据阿里云数据库团队统计，合理索引设计可使查询性能提升 10~100 倍，降低 70% 的数据库负载。