MySQL左连接查询极慢，如何重构优化？

哇，9小时的查询耗时确实太夸张了，核心问题几乎肯定出在和items表的左连接逻辑以及缺失关键索引上。咱们一步步来拆解和优化：

1. 先补全必要的索引（最核心的优化） #

没有合适的索引，数据库会对80万条的order表和30万条的items表做全表扫描，再做笛卡尔积式的连接，这会产生海量临时数据，自然慢到离谱。

针对order表创建联合索引 #

CREATE INDEX idx_order_status_promo_coupon ON `order` (status, promo_code, coupon_code, id, name, date, custid);

• 把过滤条件status和promo_code放在最前面，MySQL会先快速筛选出符合status=1000且promo_code IN (1,2)的订单，减少后续连接的数据量
• 包含连接字段coupon_code，方便快速匹配items表
• 最后加上查询需要返回的id, name, date, custid，实现覆盖索引，不用再回表查询原数据，大幅提升速度

针对items表创建索引 #

CREATE INDEX idx_items_coupon_newcode ON items (coupon_code, coupon_new_code);

• 先按coupon_code匹配order表的连接字段，再快速筛选coupon_new_code IS NULL的记录，避免全表扫描items

2. 修正查询逻辑的潜在歧义 #

你当前用了LEFT JOIN items但在WHERE子句里加了items.coupon_new_code IS NULL，这里有个小细节需要注意：

• 如果你的需求是获取「没有匹配到items记录」或者「匹配到的items记录中coupon_new_code为空」的订单，那当前逻辑是对的，但MySQL可能会把左连接优化成类似内连接的执行计划，不过有了上面的索引后会好很多。
• 如果你的需求只是获取没有匹配到items记录的订单，建议把items.coupon_new_code IS NULL移到LEFT JOIN的ON子句里，这样更符合左连接的语义，也能让优化器更准确地选择执行计划：

SELECT 
  `order`.id, `order`.name, `order`.date, customer.name, items.coupon_code 
FROM `order` 
LEFT JOIN customer ON `order`.custid = customer.id 
LEFT JOIN items ON items.coupon_code = `order`.coupon_code AND items.coupon_new_code IS NULL 
WHERE `order`.status = 1000 AND `order`.promo_code IN (1,2);

3. 用EXPLAIN验证优化效果 #

创建完索引后，一定要用EXPLAIN命令查看执行计划，确认索引是否被正确使用：

EXPLAIN SELECT 
  `order`.id, `order`.name, `order`.date, customer.name, items.coupon_code 
FROM `order` 
LEFT JOIN customer ON `order`.custid = customer.id 
LEFT JOIN items ON items.coupon_code = `order`.coupon_code 
WHERE items.coupon_new_code IS NULL AND `order`.status = 1000 AND `order`.promo_code IN (1,2);

重点看输出中的type列：

• 如果是ALL说明还是全表扫描，索引没生效，需要检查索引创建是否正确
• 如果是ref、range或eq_ref，说明索引正常工作了

4. 其他小优化 #

• 确认customer表的id字段是主键（一般主键默认有索引，这里连接order.custid = customer.id应该没问题，但如果不是主键，记得给customer.id加索引）
• 检查MySQL配置参数，比如join_buffer_size、tmp_table_size是否足够，避免因为内存不足导致磁盘临时表的生成，拖慢速度

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