测试环境
- 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秒
问题
为什么用笛卡尔积查询反而比左连接效率更高?