如何创建基于子查询的索引？实现近24小时订单关联货运发票查询

要高效查询近24小时订单对应的freight_invoices数据，需结合三张表的关联逻辑，针对性创建索引以避免不必要的回表操作。

第一步：明确核心查询逻辑 #

先写出基础查询语句，后续索引均围绕该逻辑优化：

SELECT fi.*
FROM orders o
JOIN invoices i ON o.id = i.order_id
JOIN freight_invoices fi ON i.id = fi.invoice_id
WHERE o.order_date > NOW() - INTERVAL '24 hours';

第二步：创建针对性索引 #

1. 优化orders表的过滤与关联 #

给orders表创建联合索引，包含过滤字段order_date和关联字段id。数据库过滤出近24小时的订单后，可直接拿到关联所需的id，无需回表查询主键数据：

CREATE INDEX idx_orders_order_date_id ON orders (order_date, id);

2. 优化invoices表的关联 #

给invoices表创建联合索引，包含关联字段order_id和后续关联需要的id，同样避免回表操作：

CREATE INDEX idx_invoices_order_id_id ON invoices (order_id, id);

3. 优化freight_invoices表的关联 #

给freight_invoices表创建基于invoice_id的索引，用于快速匹配对应的货运发票：

CREATE INDEX idx_freight_invoices_invoice_id ON freight_invoices (invoice_id);

补充说明 #

• 如果查询不需要freight_invoices的所有字段，可把需要的字段加到idx_freight_invoices_invoice_id的联合索引中，实现覆盖索引进一步提升性能，示例：

  CREATE INDEX idx_freight_invoices_invoice_id_fields ON freight_invoices (invoice_id, id, amount, create_time); -- 替换为实际需要的字段
  

• 针对“近24小时”这类动态过滤条件，上述索引方案比物化视图更灵活，无需定时刷新数据。

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