一、复合索引为什么需要讲究字段顺序
大家可能都听说过数据库索引能加速查询，但很少有人真正理解复合索引中字段顺序的重要性。举个生活中的例子：你要在一本按"省份+城市"排序的电话黄页里找"杭州市"的联系方式。如果直接翻到"浙江"章节再找"杭州"，速度会很快；但如果这本书是先按"城市"排序再按"省份"排序，你就得翻遍所有"杭州"条目才能找到属于浙江的那个——这就是字段顺序的魔力。
在OceanBase中，复合索引遵循最左前缀原则。比如我们有个用户表：
CREATE TABLE users (
province VARCHAR(20),
city VARCHAR(20),
district VARCHAR(20),
INDEX idx_area (province, city, district)
);
当执行WHERE province='浙江' AND city='杭州'时，索引可以被完整利用；但如果是WHERE city='杭州'，这个索引就失效了。就像你不能用字典的拼音索引来查偏旁部首一样。
二、索引查找 vs 索引扫描的实战分析
索引查找(Index Seek)和索引扫描(Index Scan)的区别就像精确导航和全地图搜索。我们通过一个订单表的例子来说明：
-- 技术栈：OceanBase 3.x
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT,
order_date DATETIME,
status TINYINT COMMENT '0未支付 1已支付 2已发货',
INDEX idx_user_status (user_id, status)
);
-- 案例1：索引查找（精准定位）
EXPLAIN SELECT * FROM orders
WHERE user_id = 10086 AND status = 1;
/* 执行计划显示：
| ====== PLAN ======
| OUTPUT
| TABLE SCAN orders USING INDEX idx_user_status
| RANGE SCAN (user_id=10086,status=1)
*/
-- 案例2：索引扫描（范围遍历）
EXPLAIN SELECT * FROM orders
WHERE status = 1;
/* 执行计划显示：
| ====== PLAN ======
| OUTPUT
| TABLE SCAN orders USING INDEX idx_user_status
| FULL SCAN
*/
第一个查询能精确定位到索引节点，第二个却要扫描整个索引树。就像快递员知道你的楼栋+门牌号时可以直接上门，如果只知道小区名就得挨户敲门。
三、字段顺序的优化策略
字段顺序不是随便排的，需要遵循三个黄金法则：
- 区分度优先原则：把区分度高的字段放前面。比如身份证号比性别更适合作为首列。
-- 不好的设计
INDEX idx_gender_age (gender, age)
-- 优化后的设计
INDEX idx_age_gender (age, gender)
- 查询频率原则：经常作为查询条件的字段要前置。比如电商平台按用户ID查订单比按状态查更频繁：
-- 订单查询场景优化
INDEX idx_user_status (user_id, status) -- 优于 idx_status_user
- 避免排序原则：如果查询需要排序，把排序字段放在索引最后：
-- 需要按时间倒序查用户订单
INDEX idx_user_date (user_id, order_date DESC)
-- 这样可以直接利用索引排序
SELECT * FROM orders
WHERE user_id = 10086
ORDER BY order_date DESC LIMIT 10;
四、特殊场景下的索引设计
有些特殊查询模式需要特别对待：
- 覆盖索引优化：当索引包含所有查询字段时，性能最佳：
-- 创建包含所有查询字段的索引
INDEX idx_covering (user_id, status, order_date)
-- 查询可以直接从索引获取数据
SELECT user_id, status FROM orders
WHERE user_id = 10086 AND status = 1;
- IN查询优化：对于IN条件，OceanBase可以智能选择索引：
-- 这个查询仍然可以利用(user_id, status)索引
SELECT * FROM orders
WHERE user_id IN (10086, 10010) AND status = 1;
- 前缀索引技巧：对长字符串可以使用前缀索引：
-- 对地址前20字符建立索引
INDEX idx_address (address(20))
五、实战中的避坑指南
我在实际项目中总结的这些经验教训，可能会帮你少走弯路：
避免过度索引：每个额外索引都会增加写入开销。曾经有个表建了15个索引，导致INSERT速度下降70%。
注意NULL值陷阱：OceanBase中NULL值不会被存储在普通索引里：
-- 查询status为NULL的记录会导致全表扫描
SELECT * FROM orders WHERE status IS NULL;
- 索引合并的代价：当优化器选择合并多个单列索引时，性能往往不如复合索引：
-- 这两个单列索引
INDEX idx_user (user_id)
INDEX idx_status (status)
-- 不如一个复合索引高效
INDEX idx_user_status (user_id, status)
- 定期维护很重要：索引碎片率超过30%就需要重建：
-- 查看索引统计信息
SHOW INDEX FROM orders;
-- 重建索引
ALTER TABLE orders REBUILD INDEX idx_user_status;
六、从原理到实践
理解OceanBase的存储引擎实现能帮助我们更好地设计索引：
LSM-Tree的影响：OceanBase基于LSM-Tree的存储结构使得索引更新是追加式的，这解释了为什么索引太多会影响写入性能。
MemTable与SSTable：热点索引会常驻内存，因此高频查询的索引应该尽量精简。
编码优化：OceanBase会对索引键自动编码压缩，但字段顺序仍影响编码效率：
-- 这个设计编码效率更高
INDEX idx_compact (tinyint_col, int_col)
-- 比这个更好
INDEX idx_less_compact (varchar_col, text_col)
七、总结与最佳实践
经过以上分析，我们可以得出这些结论：
设计复合索引时，应该像设计字典的目录结构一样思考——把最常用的检索路径放在最前面。
对于高频查询，优先考虑创建覆盖索引，虽然会占用更多空间，但可以避免回表操作。
定期使用EXPLAIN分析执行计划，OceanBase 4.0开始提供的Outline功能可以强制索引选择。
在TP（事务处理）场景中，建议每个表的索引数量控制在5个以内；AP（分析处理）场景可以适当放宽。
最后记住，索引设计是门艺术，需要根据实际查询模式不断调整。就像裁缝量体裁衣，没有放之四海而皆准的完美方案，只有最适合当前业务场景的平衡选择。
评论