如何设计索引优化跨三表查询指定商品近24小时订单的性能

优化方案 #

方案1：标准索引优化（无需修改表结构）

你不需要跨表索引，通过两个单表联合索引+覆盖索引设计就能达到接近理想的查询效率：

1. 给关联表orderProducts建联合前缀索引，优先按商品筛选，同时覆盖关联订单ID避免回表：

CREATE INDEX idx_orderproducts_product_order ON orderProducts (product, "order");

2. 给orders表建联合覆盖索引，拿到订单ID后可以直接在索引内完成时间过滤和排序，不需要访问主表数据：

CREATE INDEX idx_orders_id_createdat ON orders (id, created_at);

优化后的执行逻辑：

• 直接通过idx_orderproducts_product_order索引快速查出所有product='banana'的关联订单ID，全程不需要读orderProducts主表
• 用这批订单ID走idx_orders_id_createdat索引，直接在索引内过滤24小时内的记录，同时天然满足created_at排序需求，不需要额外排序
• products表name是主键自带索引，取name和price的开销可以忽略

你也可以手动把查询条件里的products.name='banana'改成orderProducts.product='banana'，帮助优化器更直接的选择执行路径。

方案2：冗余字段优化（适合读多写少的场景）

如果这个查询的并发要求很高，可以做少量字段冗余，完全消除跨表过滤的开销：

1. 在orderProducts表新增created_at字段，插入关联记录的时候同步写入对应订单的创建时间
2. 给orderProducts建联合索引：

CREATE INDEX idx_orderproducts_product_time_order ON orderProducts (product, created_at DESC, "order");

这个索引完全匹配你的理想需求：先按商品名称筛选，再按订单创建时间倒序过滤，直接就能拿到所有符合条件的关联记录，关联orders和products表只需要取返回字段，性能比方案1高30%以上。

内容的提问来源于stack exchange，提问作者user2741831