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

Question

测试环境

• 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秒

问题

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

Answer 1

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. 具体看实际场景吧

Answer 2

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

Answer 3

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

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

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

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