MySQL 索引优化实战——让查询速度提升100倍
后端开发中，SQL 慢查询是性能问题的头号杀手。90% 的性能问题都能通过合理的索引解决。这篇文章从实战出发，帮你搞懂 MySQL 索引的原理和优化方法。
一、为什么索引能提速
没有索引时，MySQL 要逐行扫描整张表才能找到数据（全表扫描）。有索引后，通过 B+Tree 结构直接定位到目标数据。
| 数据量 | 无索引（全表扫描） | 有索引（B+Tree） |
|---|---|---|
| 1万行 | ~10ms | <1ms |
| 100万行 | ~500ms | ~1ms |
| 1000万行 | ~5秒 | ~2ms |
二、索引类型
-- 1. 主键索引（自动创建）CREATETABLEuser(idBIGINTPRIMARYKEYAUTO_INCREMENT);-- 2. 普通索引CREATEINDEXidx_nameONuser(name);-- 3. 唯一索引CREATEUNIQUEINDEXidx_emailONuser(email);-- 4. 联合索引CREATEINDEXidx_name_ageONuser(name,age);-- 5. 全文索引（用于大文本搜索）CREATEFULLTEXTINDEXidx_contentONarticle(content);三、最左前缀原则
联合索引(name, age, city)相当于创建了三个索引：
-- ✅ 用到索引WHEREname='张三'WHEREname='张三'ANDage=25WHEREname='张三'ANDage=25ANDcity='郑州'-- ❌ 用不到索引WHEREage=25WHEREcity='郑州'WHEREage=25ANDcity='郑州'核心：联合索引从最左列开始匹配，跳过任何一列，后面的列就失效了。
四、常见索引失效场景
1. 对索引列做了运算
-- ❌ 失效SELECT*FROMuserWHEREage+1=20;-- ✅ 有效SELECT*FROMuserWHEREage=19;2. 使用了 LIKE 前置模糊匹配
-- ✅ 有效SELECT*FROMuserWHEREnameLIKE'张%';-- ❌ 失效SELECT*FROMuserWHEREnameLIKE'%三';SELECT*FROMuserWHEREnameLIKE'%三%';3. 使用了函数
-- ❌ 失效SELECT*FROMuserWHERESUBSTR(name,1,1)='张';-- ✅ 有效SELECT*FROMuserWHEREnameLIKE'张%';4. 类型不一致
-- 假设 phone 是 VARCHAR 类型-- ❌ 失效（隐式类型转换）SELECT*FROMuserWHEREphone=13800008888;-- ✅ 有效SELECT*FROMuserWHEREphone='13800008888';五、通过 EXPLAIN 分析慢查询
EXPLAINSELECT*FROMuserWHEREname='张三'\G重点看这几列：
| 列 | 好 | 坏 |
|---|---|---|
| type | const, ref, range | ALL（全表扫描） |
| rows | 越小越好 | 几十万就要注意 |
| Extra | Using index | Using filesort（需要优化） |
| possible_keys | 有值 | null（没用到索引） |
实战分析
-- 先创建一个模拟表CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(64),user_idBIGINT,statusTINYINT,amountDECIMAL(10,2),created_atDATETIME);-- 插入10万条测试数据-- （省略插入语句，实际可以用存储过程）-- 检查慢查询EXPLAINSELECT*FROMordersWHEREstatus=1;-- type: ALL, rows: 100000 → 全表扫描，需要优化-- 加索引后CREATEINDEXidx_statusONorders(status);EXPLAINSELECT*FROMordersWHEREstatus=1;-- type: ref, rows: 50000 → 用了索引，扫描行数减半六、优化实战场景
场景1：分页查询太慢
-- 慢：OFFSET 越大越慢SELECT*FROMordersORDERBYidLIMIT10000,20;-- 快：用上一页的最大 ID 做条件SELECT*FROMordersWHEREid>10000ORDERBYidLIMIT20;| 方式 | 100页 | 1000页 | 10000页 |
|---|---|---|---|
| OFFSET | ~30ms | ~200ms | ~2s |
| ID条件 | ~5ms | ~5ms | ~5ms |
场景2：排序导致文件排序
-- 需要建立 (status, created_at) 联合索引-- 避免 Using filesortSELECT*FROMordersWHEREstatus=1ORDERBYcreated_atDESCLIMIT10;CREATEINDEXidx_status_createONorders(status,created_at);场景3：分组统计优化
-- 给 group by 的列加索引SELECTuser_id,COUNT(*)FROMordersGROUPBYuser_id;CREATEINDEXidx_user_idONorders(user_id);七、索引设计原则
1. 不是越多越好
一张表的索引数量建议控制在5个以内。索引太多会导致：
- 插入/更新变慢（每次都要维护索引）
- 占用磁盘空间
- 查询优化器可能选错索引
2. 高区分度的列才适合建索引
-- 区分度差（只有0和1两个值），索引意义不大-- 索引扫描也要扫一半数据，不如全表扫描CREATEINDEXidx_statusONorders(status);-- 区分度高（每个值都不同），索引效果最好CREATEINDEXidx_order_noONorders(order_no);3. 优先给 Where 和 Join 的列建索引
-- WHERE 条件列SELECT*FROMuserWHEREname='张三';-- JOIN 关联列SELECT*FROMorderoLEFTJOINuseruONo.user_id=u.id;-- user.id 要有索引总结
索引优化是后端开发的核心技能，记住三条：
- 联合索引遵循最左前缀原则—— 把最常查询的列放最左边
- 用 EXPLAIN 分析慢查询—— 重点关注 type 和 rows
- 不是越多越好—— 一张表 5 个以内索引足矣
建议在日常开发中，每个 SQL 都养成用 EXPLAIN 看一眼的习惯。
如果对你有帮助，欢迎点赞、评论、关注【张老师技术栈】，持续分享 Java/Python/爬虫 实战干货。