不懂数据库索引原理？你写的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跑得快的库表结构

现在行动起来：

1. 打开慢查询日志
2. 用EXPLAIN分析每个慢查询
3. 遵循索引四大原则
4. 定期监控索引使用情况

数据库不会说谎，性能说明一切！

PS：在评论区说出你被索引坑得最惨的一次经历，点赞送《分布式索引设计精髓》电子书！