为什么sql进行count统计时笛卡尔积比左连接效率高?

测试环境

  • X表数据量30万
  • Y表数据量2000
  • X表索引:verify_user_id,state,verify_user_id_state(联合索引)
  • Y表索引:user_id

测试1(左连接)

  • sql
select count(*) from X A left join Y B on A.verify_user_id=B.user_id where A.state='2'
  • explain分析
1    SIMPLE    A        ref    state    state    5    const    258460    100    Using index condition
1    SIMPLE    B        ref    user_id_index    user_id_index    5    db.A.verify_user_id    1    100    Using index
  • 执行用时
    2.4秒

测试2(笛卡尔积)

  • sql
select count(*) from X A, Y B where A.verify_user_id=B.user_id and A.state='2'
  • explain分析
1    SIMPLE    B        index    user_id_index    user_id_index    5        1833    100    Using where; Using index
1    SIMPLE    A        ref    verify_user_id,state,verify_user_id_state    verify_user_id_state    10    db.B.user_id,const    264    100    Using where; Using index
  • 执行用时
    0.6秒

问题

为什么用笛卡尔积查询反而比左连接效率更高?

阅读 4.1k
3 个回答
  1. sql尽量使用数据量小的表做主表,这样效率高,如果使用数据量大的表做主表,此时使用left join 就会比较慢,即使关联条件有索引。但如果使用inner join速度就较快。因为inner join 在执行的时候回自动选择最小的表做基础表,效率高,总之相比之下inner join不管从效率还是速度上都优于left join,毕竟left join 会多一部分逻辑运算
  2. 选择inner join还有个好处,不会产生null,有些表我们在定义的时候某些字段不允许存在null,如果用left join就可能会产生null,此时软件就会报错,而inner join可以避免
  3. 具体看实际场景吧

从explain的结果来看,应该是笛卡尔积用上了联合索引,使得效率更高。而左连接查询是on之后再接where,此时只能在on的时候用一下索引user_id_index,where那里无法利用索引state。而联合索引是user_id+state,它的区分度应该比仅用user_id_index高

如果我没有记错的话,大学学数据库的时候,表连接都是在笛卡尔积的基础上附加条件筛出来的。

一个前端基于影像中的知识,给你查到了 from A,B 应该等效于cross join,也就是全笛卡尔积,A有三条,B有三条,结果就是九条,然后再根据where条件过滤,再求count。

left join应该是A中每条数据按照on条件在B中逐条匹配,形成一个规模稍微小点儿的笛卡尔积子集。

理论上cross join确实应该比left join要慢,因为结果是个满集,但是就实际情况来看,有那么一丢丢可能是因为索引的原因,在左连接进行比较的时候拖慢了结果。这完全是瞎猜的。但是前面应该是大差不差的,要是错了就怪我大学没有好好学哈哈哈哈。

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题