SQL关联查询直接join 和子查询的区别

最新推荐文章于 2025-07-01 10:09:33 发布

H_MZ

最新推荐文章于 2025-07-01 10:09:33 发布

阅读量5.2k

点赞数 5

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://blog.51cto.com/10705830/2315247

本文通过一个实际案例，展示了如何通过优化SQL查询语句，将原本执行速度极慢的查询提升至几秒内返回，揭示了不同SQL写法对执行效率的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

运营组的同事最近提出一个需求，希望可以统计出用系统用户及订单情况，于是乎我们很想当然的写出了一个统计SQL，用户表user和行程表直接join，并且针对行程做了group，但SQL执行速度出奇的慢。

explain select  users.`mobile_num`, concat(users.`lastName` ,users.`firstName`) as userName, users.`company`,
  (case `users`.`idPhotoCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `idPhotoCheckStatus`,
  (case `users`.`driverLicenseCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `driverLicenseCheckStatus`,
  (case `users`.`companyCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `companyCheckStatus`,
  (case `users`.`unionCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `unionCheckStatus`,
  count(passenger_trip.id) as ptrip_num
from users
left join passenger_trip on passenger_trip.userId = users.id  and passenger_trip.status != 'cancel'
left join driver_trip on driver_trip.`userId`=users.`id` and driver_trip.`status` != 'cancel'
where company != '本公司名' and company != '本公司昵称'

当时的第一反应是数据库挂住了，因为用户表的数据量10W左右，行程表的数据也是10W左右，不可能这么慢！通过explain查看分析计划，并且查看过关联字段的索引情况，发现这是一个最常见的关联查询，当然是通过join实现。

SQL关联查询直接join 和子查询的区别

转而一想，10W*10W，经过笛卡尔集之后，这不是百亿级的数据筛选吗？！于是换了一种写法进行尝试。

explain select  users.`mobile_num`, concat(users.`lastName` ,users.`firstName`) as userName, users.`company`,
  (case `users`.`idPhotoCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `idPhotoCheckStatus`,
  (case `users`.`driverLicenseCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `driverLicenseCheckStatus`,
  (case `users`.`companyCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `companyCheckStatus`,
  (case `users`.`unionCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `unionCheckStatus`,
  (select count(passenger_trip.id) from  passenger_trip where  passenger_trip.userId = users.id  and passenger_trip.status != 'cancel') as ptrip_num,
  (select count(driver_trip.id) from  driver_trip where  driver_trip.userId = users.id  and driver_trip.status != 'cancel') as dtrip_num
from users
where company != '本公司名' and company != '公司昵称'

这样的效果居然比直接join快了N倍，执行速度从未知到10秒内返回，查看执行计划：

SQL关联查询直接join 和子查询的区别

进一步调整SQL进行尝试：

explain select  users.`mobile_num`, concat(users.`lastName` ,users.`firstName`) as userName, users.`company`,
  (case `users`.`idPhotoCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `idPhotoCheckStatus`,
  (case `users`.`driverLicenseCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `driverLicenseCheckStatus`,
  (case `users`.`companyCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `companyCheckStatus`,
  (case `users`.`unionCheckStatus` when '2' then '已认证' when '3' then '已驳回' else '待认证' end) as `unionCheckStatus`,
 ptrip_num, dtrip_num
from users 
 left  join 
 (select count(passenger_trip.id)  as ptrip_num, passenger_trip.`userId` from  passenger_trip where  passenger_trip.status != 'cancel' group by passenger_trip.`userId` ) as ptrip
 on ptrip.userId = users.id
 left join 
 (select count(driver_trip.id)  as dtrip_num, driver_trip.`userId` from  driver_trip where  driver_trip.status != 'cancel' group by driver_trip.`userId` ) as dtrip
 on dtrip.userId = users.id
where company != '本公司名' and company != '公司昵称'

居然5秒内返回，这才是正常的预期，10W级的数据筛选，应该是几秒内返回的！

SQL关联查询直接join 和子查询的区别