不懂数据库索引原理？你写的SQL跑的慢如老牛，就等着挨骂吧
一、索引底层原理：B+树是如何吊打其他数据结构的？
1.1 为什么不用哈希表？
- 哈希索引：精确查询O(1)，但范围查询、排序操作直接崩盘
- B+树：平衡多路搜索树，保证查询、范围、排序全能打
1.2 B+树核心设计
- 非叶子节点只存键值，大幅降低树高度（1000万数据只需3~4层）
- 叶子节点双向链表链接，范围查询如丝般顺滑
- 所有数据存于叶子节点，查询稳定性极强（任何查询IO次数相同）
1.3 磁盘IO才是瓶颈
- 机械磁盘随机IO：10ms/次
- B+树10M数据：3次IO → 30ms
- 全表扫描：10000次IO → 100秒
- 性能差3000倍以上！
二、索引四大使用原则：违反一条性能血崩！
2.1 最左前缀原则
- 索引
(a, b, c) - ✅ 能用：
a=?、a=? and b=?、a=? and b=? and c=? - ❌ 不能用：
b=?、c=?、b=? and c=? - 原理：B+树按索引定义顺序构建，跳字段如同查字典跳过拼音首字母
2.2 避免索引失效
- ❌ 对索引列计算：
WHERE age+1>20 - ❌ 前导模糊匹配：
WHERE name LIKE '%张' - ❌ 隐式类型转换：
WHERE varchar_col=123（应写’123’） - ❌ OR一侧无索引：
WHERE a=1 OR b=2（若b无索引，全表扫描）
2.3 索引选择性原则
- 公式：索引选择性 = 不重复值数量 / 总记录数
- 性别（男/女）：选择性≈0.5 →不值得单独建索引
- 手机号：选择性≈0.99 →极品索引字段
- 技巧：低选择性字段可搭配高选择性字段建联合索引
2.4 覆盖索引优先
- ❌
SELECT *→ 大概率回表查询 - ✅
SELECT 索引包含字段→ 无需回表，性能翻倍 - 效果：减少50%磁盘IO，速度提升100%
三、六大优化实战：从青铜到王者的秘诀
3.1 EXPLAIN命令必看字段
- type：至少达到
ref（索引访问），杜绝ALL（全表扫描） - key：确认实际使用的索引
- rows：预估扫描行数（超过1000需优化）
- Extra：杜绝
Using filesort、Using temporary
3.2 联合索引优化技巧
- 场景：查询
WHERE a=? and b=?，排序ORDER BY c - 方案：建
(a, b, c)，同时优化查询和排序 - 原理：B+树叶子节点按索引排序，避免额外排序操作
3.3 大数据分页优化
❌
LIMIT 100000,20：先扫描100020行，再丢100000行✅子查询优化：
SELECT * FROM table
INNER JOIN (
SELECT id FROM table
WHERE condition
ORDER BY index_field
LIMIT 100000,20
) AS tmp USING(id)效果：100ms → 2ms，提升50倍
3.4 索引碎片定期维护
- 频繁增删导致索引碎片增多，性能下降
- 每月执行：
ALTER TABLE table REBUILD INDEX index_name
3.5 杜绝过度索引
每个索引：写操作变慢 + 占用磁盘
排查无用索引：
SELECT * FROM sys.schema_unused_indexes;
维护成本：索引数不宜超过表字段数的30%
3.6 热点数据分离
- 超大表（十亿级）采用分区表+局部索引
- 冷热数据分离：热数据索引内存加载，冷数据索引磁盘存放
四、血泪案例：这些坑踩过才知道痛
4.1 隐式转换灾难
- 字段：
phone VARCHAR(20) - 错误：
WHERE phone = 13800138000（未加引号） - 结果：索引失效，全表扫描，数据库CPU100%持续2小时
4.2 联合索引顺序错误
- 索引：
(age, city) - 查询：
WHERE city='北京' AND age>25 - 结果：仅能用到age索引，city条件依旧全表扫描
4.3 OR条件未优化
查询：
WHERE a=1 OR b=2错误：仅a有索引
优化：改为
UNION ALL：SELECT * FROM table WHERE a=1
UNION ALL
SELECT * FROM table WHERE b=2效果：5秒 → 0.1秒
结语：索引玩得溜，升职加薪快！
- 初级程序员：疯狂写SQL
- 高级程序员：疯狂优化SQL
- 架构师：设计让SQL跑得快的库表结构
现在行动起来：
- 打开慢查询日志
- 用EXPLAIN分析每个慢查询
- 遵循索引四大原则
- 定期监控索引使用情况
数据库不会说谎，性能说明一切！
PS：在评论区说出你被索引坑得最惨的一次经历，点赞送《分布式索引设计精髓》电子书！