索引是MySQL优化的核心，但设计不当会导致查询变慢、写入卡顿、空间浪费。下面按最实用、最容易踩坑的维度整理，直接用于业务开发。
一、基础原则：什么时候该建索引？
1. 必须建索引的场景
- WHERE 条件频繁使用的列（等值查询、范围查询）
- JOIN 关联条件列（内外键都建议建索引）
- ORDER BY / GROUP BY 排序列
- DISTINCT 去重列
- 覆盖查询（查询只返回索引列，不用回表）
2. 绝对不要建索引的场景
- 列值重复率极高（性别、状态0/1，区分度<20%）
- 数据量极小（百行以内）
- 频繁修改、极少查询的表（写入性能会暴跌）
- 单独的大字段（text、blob、长varchar）
二、最关键：联合索引设计规则（70%的坑都在这）
1. 最左前缀原则（必须死记）
联合索引(a, b, c)能命中的查询：
where a=?where a=? and b=?where a=? and b=? and c=?
不能命中：
where b=?where c=?where a=? and c=?（只能命中a，b断层）
2. 联合索引顺序口诀
等值在前，范围在后；区分度高在前，低在后
- 第1列：高频等值查询（=、in）
- 第2列：次高频条件
- 最后一列：范围查询（> < between like）
错误示例：idx(created_time, status)（范围在前，索引失效）
正确示例：idx(status, created_time)
3. 控制联合索引列数
建议2~4列最佳
列越多：索引体积越大、写入越慢、优化器越难选择。
三、索引字段设计规范
优先使用小数据类型
int < bigint，tinyint < int，short string < long string
索引越小，内存命中率越高，速度越快。禁止在索引列上做计算/函数操作
会导致索引完全失效
❌ 错误：where YEAR(create_time)=2025
✅ 正确：where create_time >= '2025-01-01'字符串必须加长度前缀索引
避免整字符串建索引，浪费空间-- 只索引前10个字符createindexidx_usernameonuser(username(10));允许 NULL 的列不适合建索引
NULL 会让索引效率下降，建议用默认值（空字符串/0）替代。
四、查询与索引匹配注意事项
like 以通配符开头会失效
❌where name like '%张三'
✅where name like '张三%'隐式类型转换会导致索引失效
字符串列传数字、数字列传字符串
❌where phone=13800138000（phone是varchar）
✅where phone='13800138000'or 连接会导致索引失效
or 前后必须全部有索引，否则全表扫描
尽量用 union all 替代。not in / != / is not null 容易导致索引失效
尽量避免，或改用范围查询。
五、写入性能：索引不是越多越好
- 每增一个索引，insert/update/delete 就多一次索引维护
- 单表索引数量建议：3~5个以内
- 频繁写入的表：少建索引
- 读多写少的表：可以合理建索引
六、高级优化：覆盖索引（性能神器）
查询的字段 = 索引字段，不需要回表查数据行。
示例：
-- 索引idx(status,created_time,nickname)-- 查询直接从索引返回，无需回表selectnicknamefromuserwherestatus=1andcreated_time>'2025-01-01'覆盖索引能让查询速度提升5~100倍。
七、必须避免的反模式（高频坑）
- 单表建十几个索引→ 写入卡死
- 每个字段单独建索引→ 优化器无法选择，查询变慢
- 联合索引顺序乱排→ 最左前缀失效
- 大字段建索引→ 空间爆炸
- 低区分度列建索引（性别、状态）→ 索引无用
- 查询条件用函数/运算→ 索引直接失效
八、索引维护与检查
- 用
explain检查是否命中索引 - 删除重复、冗余、从未使用的索引
- 大表加索引要在低峰期执行
- 定期优化表（optimize table）
总结
- 最左前缀是联合索引的灵魂
- 等值在前，范围在后
- 索引列不计算、不函数、不隐式转换
- 单表索引不超过5个
- 优先覆盖索引，避免回表
