MySQL索引设计是数据库性能优化的核心环节，合理的索引能够大幅减少查询时的全表扫描，提升数据检索效率，同时不当的索引设计也会带来写入性能下降、存储空间浪费等问题，需要结合业务场景综合考量。
索引设计的核心原则
索引设计的首要目标是匹配业务查询需求，同时尽可能降低对写入操作的影响。核心原则包括：优先为高频查询字段创建索引，避免为低区分度字段创建索引，控制单表索引数量，合理设计联合索引的顺序。
区分度优先原则
索引字段的区分度越高，索引的过滤效果越好。区分度指的是字段中不同值的数量占总行数的比例，比例越高区分度越好。比如用户表的手机号字段区分度远高于性别字段，更适合创建索引。
可以通过如下SQL计算字段区分度：
-- 计算user表中phone字段的区分度
SELECT
COUNT(DISTINCT phone) / COUNT(*) AS phone_distinct_ratio,
COUNT(DISTINCT gender) / COUNT(*) AS gender_distinct_ratio
FROM user;
联合索引最左前缀原则
联合索引是按照索引字段的创建顺序构建的B+树结构，查询时只能从最左端的字段开始匹配。比如创建联合索引idx_a_b_c(a,b,c)，以下查询可以使用该索引：
- WHERE a = 1
- WHERE a = 1 AND b = 2
- WHERE a = 1 AND b = 2 AND c = 3
而以下查询无法使用该索引：
- WHERE b = 2
- WHERE a = 1 AND c = 3
常见索引类型选择
MySQL支持多种索引类型，需要根据字段特性和查询场景选择：
| 索引类型 | 适用场景 | 注意事项 |
|---|---|---|
| 普通索引 | 常规查询字段，无唯一性要求 | 最常见，性能稳定 |
| 唯一索引 | 需要保证字段值唯一的场景，如手机号、邮箱 | 插入时会校验唯一性，比普通索引多一次判断 |
| 前缀索引 | 长文本字段，如文章内容、地址 | 需要选择合适的前缀长度，平衡区分度和索引大小 |
| 全文索引 | 文本内容的模糊搜索场景 | 仅支持MyISAM和InnoDB引擎，中文需要配合分词插件 |
前缀索引设计示例
对于长文本字段，创建完整索引会占用大量存储空间，此时可以使用前缀索引。比如为文章的标题字段创建前缀索引，先确定合适的前缀长度：
-- 测试不同前缀长度的区分度
SELECT
COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS len10_ratio,
COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS len20_ratio,
COUNT(DISTINCT LEFT(title, 30)) / COUNT(*) AS len30_ratio
FROM article;
当前缀长度为20时区分度已经达到99%以上，就可以创建前缀索引：
-- 创建标题字段的前缀索引，前缀长度为20 CREATE INDEX idx_title_prefix ON article(title(20));
需要规避的索引设计误区
避免冗余索引
冗余索引指的是重复或者功能重叠的索引，比如已经存在联合索引idx_a_b(a,b)，再单独创建idx_a(a)就是冗余索引，因为联合索引已经可以支持a字段的单条件查询。冗余索引会增加写入时的维护成本，占用额外存储空间。
不要过度创建索引
单表的索引数量建议控制在5个以内，过多的索引会导致写入、更新、删除操作变慢，因为每次数据变更都需要同步更新所有相关索引。同时索引也会占用大量磁盘空间，影响数据库整体性能。
避免对索引字段做函数操作
如果在查询条件中对索引字段做了函数处理，索引会失效，导致全表扫描。比如以下查询无法使用phone字段的索引：
-- 错误的写法，对索引字段使用函数，索引失效 SELECT * FROM user WHERE SUBSTRING(phone, 1, 3) = '138'; -- 正确的写法，避免对索引字段做函数操作 SELECT * FROM user WHERE phone LIKE '138%';
索引设计验证方法
设计完索引后，需要使用EXPLAIN命令验证索引是否被正确使用。比如验证上述联合索引的查询效果：
-- 查看查询执行计划，验证索引使用情 EXPLAIN SELECT * FROM user WHERE a = 1 AND b = 2;
执行结果中，key字段会显示实际使用的索引，type字段显示查询类型，ref字段显示索引的匹配方式，通过这些字段可以判断索引设计是否合理。
实际业务中，还需要定期监控慢查询日志，根据慢查询的SQL语句调整索引设计，让索引始终适配业务查询的变化。