如何优化Magento2中MySQL左连接查询，提升关联购买模块性能？

1. 简化冗余计算 #

原查询中(SUM(main_table.product_id) / main_table.product_id)完全等价于COUNT(*)——因为main_table.product_id被WHERE条件固定为1，替换后可减少不必要的计算开销：

SELECT `sub_table`.`product_id`,
       COUNT(*) AS `frequency`

2. 修正JOIN类型 #

原查询用LEFT JOIN但WHERE子句加入了sub_table的非空过滤条件，这会自动将左连接转为内连接，直接改为INNER JOIN可避免数据库做无效的左连接逻辑：

FROM `sales_order_item` AS `main_table`
INNER JOIN `sales_order_item` AS `sub_table` 
  ON `main_table`.`order_id` = `sub_table`.`order_id` 
  AND `main_table`.`product_id` != `sub_table`.`product_id`

3. 清理无效SELECT字段 #

原查询中main_table.product_id固定为1，sub_table.order_id在GROUP BY后无意义（每个分组对应多个订单ID，仅返回随机值），直接移除这两个字段，减少数据传输量。

4. 添加针对性索引 #

针对sales_order_item表创建以下复合索引，大幅提升查询效率：

• 快速定位目标商品的所有订单：

  CREATE INDEX idx_sales_order_item_product_order ON sales_order_item (product_id, order_id);
  

• 快速匹配关联订单并过滤有效商品（实现索引覆盖查询，无需回表）：

  CREATE INDEX idx_sales_order_item_order_type_parent ON sales_order_item (order_id, product_type, parent_item_id, product_id);
  

5. 优化后的完整查询 #

SELECT `sub_table`.`product_id`,
       COUNT(*) AS `frequency`
FROM `sales_order_item` AS `main_table`
INNER JOIN `sales_order_item` AS `sub_table` 
  ON `main_table`.`order_id` = `sub_table`.`order_id` 
  AND `main_table`.`product_id` != `sub_table`.`product_id`
WHERE `main_table`.`product_id` = 1
  AND `sub_table`.`product_type` NOT IN ('configurable', 'bundle')
  AND `sub_table`.`parent_item_id` IS NULL
GROUP BY `sub_table`.`product_id`
ORDER BY `frequency` DESC

额外建议 #

• 若数据量极大，可采用预计算：定时离线统计商品关联数据，存储到单独的关联表中，查询时直接读取预计算结果，避免实时关联大表。
• 利用Magento缓存机制，对相同商品的关联查询结果进行缓存，减少重复计算。

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