Laravel中含JOIN和UNION的MySQL订单查询性能优化求助

1. SQL语句原生优化 #

• 替换UNION为UNION ALL：当前两个子查询分别对应订阅订单、即时订单两类完全不重叠的订单数据，UNION自带的全局去重排序逻辑完全多余，替换为UNION ALL可直接减少30%以上的执行开销。
• 调整GROUP BY执行逻辑：当前SQL先关联所有表再做分组，会产生大量临时关联数据再做聚合，性能极低。改为先聚合子表再关联主表：提前统计subscription_items/order_items每个订单对应的商品数量，生成临时聚合结果后再和主表关联，避免无效数据扫描。
• 移除无效关联：第一个子查询中关联了subscribe_order_items表，但查询字段完全没有用到该表数据，直接删除即可减少一次表关联开销。
• 分页逻辑下沉：当前逻辑是拼接两类所有订单数据后再分页，相当于每次查询都要扫描全量订单表。改为两个子查询各自先做分页（比如取前10条），拼接后再做一次排序分页，避免全表扫描。
• 避免外层套SELECT *：直接指定需要查询的字段，减少临时表存储的无效数据量。

优化后SQL参考示例：

select * from ((
select s.id, s.order_id, u.name as user_name, s.new_dated, s.rescheduling_delivery_date as expected_date, si.items, s.od_dis_total as total, s.order_discount, a.area_name, so.action as ordertype, d.name as driver_name, 'NA' as payment_mode, ps.status_name as payment_status, ss.status_name as order_status, s.od_payment_method, si.product_id, s.delivered_dated
from subscription s
left join (select subscription_id, count(product_id) as items, product_id from subscription_items group by subscription_id) si on s.id = si.subscription_id
left join users u on s.user_id = u.id
left join users d on s.driver_id = d.id
left join ycias_address ya on s.add_id = ya.id
left join area a on ya.area_id = a.id
left join subscribe_orders so on s.subscribe_order_id = so.id
left join payment_status ps on s.payment_status = ps.id
left join subscription_status ss on s.od_status = ss.id
limit 10 offset 0)
union all
(select o.id, o.order_id, u.name as user_name, o.new_dated, o.expected_delivery_date as expected_date, oi.items, o.od_dis_total as total, o.order_discount, a.area_name, 'Instant Order' as ordertype, d.name as driver_name, o.payment_mode, ps.status_name as payment_status, os.status_name as order_status, o.od_payment_method, oi.product_id, o.delivered_dated
from orders o
left join (select order_id, count(product_id) as items, product_id from order_items group by order_id) oi on o.id = oi.order_id
left join users u on o.user_id = u.id
left join users d on o.driver_id = d.id
left join ycias_address ya on o.add_id = ya.id
left join area a on ya.area_id = a.id
left join payment_status ps on o.payment_status = ps.id
left join order_status os on o.od_status = os.id
limit 10 offset 0)) as all_orders
order by new_dated desc
limit 10 offset 0

2. 索引优化 #

• 所有关联字段加普通索引：subscription.user_id、subscription.driver_id、subscription.add_id、subscription.subscribe_order_id、orders表对应的所有关联字段、subscription_items.subscription_id、order_items.order_id、ycias_address.area_id等JOIN关联字段全部建索引。
• 建覆盖索引减少回表：subscription_items建联合索引idx_subid_pid(subscription_id, product_id)，order_items建联合索引idx_oid_pid(order_id, product_id)，统计商品数量时直接走索引无需回表查数据。
• 排序筛选字段加联合索引：如果Datatable有按时间、订单状态筛选/排序的需求，给subscription.new_dated、orders.new_dated、od_status等字段建联合索引，避免文件排序开销。

3. Laravel业务层优化 #

• 下推Datatable的筛选排序逻辑：不要在服务端内存中做筛选排序，直接把筛选、排序条件传到SQL层执行，利用数据库索引提升查询效率。
• 增加查询缓存：如果订单列表实时性要求不高，用Redis缓存高频查询的结果，缓存周期可按业务需求设1-5分钟，减少重复查库的开销。
• 只读查询走从库：配置数据库主从分离，订单列表这类只读查询全部走从库，减轻主库的访问压力。

4. 架构层面优化 #

• 新增订单汇总中间表：业务允许的前提下，新增一个全量订单汇总表，每次生成订阅订单、即时订单时同步写入汇总数据到该表，后续订单列表直接查该中间表，无需每次查询关联10余张表再做UNION，性能可提升10倍以上。
• 替换共享主机：GoDaddy共享主机的数据库资源限制极高，本身性能极差，有条件可更换为云服务器或者独立RDS服务，基础查询性能至少提升数倍。

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