第2章：数据库索引优化实战

各位同学，今天我们来聊聊数据库索引优化。说实话，我在ERP行业摸爬滚打这么多年，见过太多系统因为索引设计不合理而崩溃的案例。有一次，某家制造企业的订单查询页面，每次打开要等30秒——嗯，就是那种让人想砸电脑的体验。后来一查，全表扫描，索引完全没起作用。

所以这一章，我打算把B+树索引原理、复合索引设计、以及EXPLAIN分析执行计划这三个核心点，一次性讲透。你想想看，搞懂了这些，ERP系统里那些高频查询——订单查询、库存查询——基本就能做到毫秒级响应。

2.1 B+树索引原理：为什么它最适合ERP？

先问个问题：为什么MySQL的InnoDB引擎选择B+树，而不是二叉树、哈希表？

我个人习惯从磁盘I/O的角度来理解。ERP系统的数据量动不动就上千万行，内存装不下，大部分数据在磁盘上。磁盘读写一次，大概要10毫秒——这比内存慢了几万倍。所以，减少磁盘I/O次数，就是优化的核心。

B+树有几个关键特性：

  • 非叶子节点只存索引，不存数据——这样每个节点能存更多索引，树的高度就低。比如一个3层的B+树，能存上千万条记录。查询任何一条数据，最多3次磁盘I/O。
  • 叶子节点形成有序链表——范围查询特别快。ERP里经常要查「本月所有订单」，B+树直接顺着链表扫就行，不用回表。
  • 数据只在叶子节点——每次查询的路径长度都一样，性能稳定。不会像二叉搜索树那样，最坏情况退化成链表。

核心结论：B+树用「空间换时间」，通过增加索引存储空间，换来了极致的查询效率。对于ERP这种读写比例大约8:2的系统，B+树是最优解。

我在项目中遇到过一件事：有个同事给订单表建了哈希索引，觉得查询快。结果一做范围查询「订单时间在2024-01-01到2024-01-31」，直接全表扫描。哈希索引只支持等值查询，不支持范围查询——这就是典型的选错数据结构。

2.2 复合索引设计：ERP高频查询的实战案例

复合索引，说白了就是把多个字段拼成一个索引。但怎么拼，顺序怎么排，这里头门道很多。

先看一个ERP里最常见的查询：

-- 查询某个客户在某个时间段的订单
SELECT * FROM orders 
WHERE customer_id = 1001 
  AND order_date BETWEEN '2024-01-01' AND '2024-01-31'
  AND status = '已完成';

如果只建单列索引，MySQL只能用到其中一个索引，其他字段还得回表过滤。效率很低。这时候就需要复合索引。

设计复合索引的核心原则：

  1. 等值条件放前面——比如 customer_id = 1001 这种精确匹配的字段，放在最左边。
  2. 范围条件放后面——比如 order_date BETWEEN 这种范围查询，放在等值条件后面。
  3. 区分度高的字段优先——比如 customer_id 能过滤掉90%的数据，就比 status 更适合放前面。

所以，上面这个查询，我建议的复合索引是：

CREATE INDEX idx_customer_date_status ON orders(customer_id, order_date, status);

你可能会问：为什么 status 放最后？因为 status 的区分度低（只有几个值），放前面会导致索引树的前缀区分度不够，扫描的行数还是很多。

避坑指南：我曾经犯过一个错误——给订单表建了 (status, customer_id, order_date) 的索引。结果查询时，MySQL先按 status 过滤，发现匹配了50%的数据，然后才用 customer_id 过滤。效率还不如全表扫描。后来改成 (customer_id, order_date, status)，查询时间从2秒降到了20毫秒。

再来看库存查询的典型场景：

-- 查询某个仓库中，库存数量低于安全库存的商品
SELECT * FROM inventory 
WHERE warehouse_id = 10 
  AND quantity < safety_stock;

这个查询里，warehouse_id 是等值条件，quantity < safety_stock 是范围条件。但注意，safety_stock 是字段，不是常量。所以索引设计要稍微调整：

CREATE INDEX idx_warehouse_qty ON inventory(warehouse_id, quantity);

这样，MySQL可以先通过 warehouse_id 定位到具体仓库，然后按 quantity 顺序扫描，找到所有低于安全库存的记录。因为B+树的叶子节点是有序的，所以 quantity < safety_stock 这个范围查询，只需要扫描一小段数据。

2.3 利用EXPLAIN分析执行计划

索引建得好不好，不能靠猜。得用 EXPLAIN 来看MySQL到底是怎么执行的。

我每次优化SQL，第一步就是跑 EXPLAIN。它就像医生的CT扫描，能告诉你：

  • 用了哪个索引？
  • 扫描了多少行？
  • 有没有回表？
  • 有没有文件排序？

来看一个实际例子：

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 1001 
  AND order_date BETWEEN '2024-01-01' AND '2024-01-31';

输出结果的关键列：

列名 值 说明
type ref 使用了非唯一索引的等值查找，不错
key idx_customer_date 实际使用的索引名
rows 500 预估扫描的行数，越小越好
Extra Using index condition 使用了索引下推，减少了回表次数

重点看 type 列：

  • const：主键或唯一索引等值查询，最快
  • ref：普通索引等值查询，很快
  • range：范围查询，还行
  • index：扫描了整个索引树，有点慢
  • ALL：全表扫描，最慢——看到这个就要警惕了

注意：如果 Extra 列出现 Using filesort，说明MySQL在内存或磁盘上做了排序。对于ERP里的分页查询（比如 ORDER BY order_date LIMIT 20），如果索引没有包含排序字段，就会触发文件排序。我曾经优化过一个报表查询，加上排序字段到复合索引后，查询时间从8秒降到了0.3秒。

还有一个容易被忽略的点：possible_keys 和 key 不一致时，说明MySQL没有选对索引。这时候可以用 FORCE INDEX 强制指定索引，但更好的做法是调整索引设计，让MySQL自己选对。

2.4 实战总结：索引优化的三个层次

做了这么多年优化，我总结出三个层次：

  1. 第一层：建对索引——根据查询条件，设计合适的复合索引，遵循等值在前、范围在后、区分度优先的原则。
  2. 第二层：用对索引——通过EXPLAIN验证，确保查询走了正确的索引，没有全表扫描、没有文件排序。
  3. 第三层：维护索引——定期检查索引碎片，删除无用索引。我见过一个ERP系统，因为历史原因积累了20多个冗余索引，插入性能下降了30%。

最后说一句：索引不是越多越好。每个索引都会增加写入开销。对于ERP这种高频写入的系统（订单创建、库存更新），索引数量控制在5-8个比较合理。超过10个，就要考虑是否有冗余了。

好了，这一章就到这里。下一章我们聊聊SQL语句本身的优化——有时候，改一行SQL比加三个索引还管用。

← 上一章 📖 返回目录 下一章 →