前言:在搬砖道路上,写了多年的sql,一直认为的优化原来大跌所望,直到最近上线遇到问题,感谢同事的讲解,让我重新认识。

今天带来的是in和inner join之间的优化,来司时候,服务端曾告知我服务端拒绝使用关联查

关于他们两使用这里就不讲了,直接sql贴上来
这里需求是:求今天的玩家进房间并且下注和昨日进房间并且下注的玩家 交集 并去重
源sql

SELECT count(*) INTO fourteenStay from
(select DISTINCT account_id from data_tslog.guess_user_room_flow a INNER JOIN data_tslog.guess_user_bet_flow b USING(account_id)
where a.type = 1 and a.event_time BETWEEN eventTime and endTime
and b.event_time BETWEEN eventTime and endTime
) A INNER JOIN
(select DISTINCT account_id from data_tslog.guess_user_room_flow a INNER JOIN data_tslog.guess_user_bet_flow b USING(account_id)
where a.type = 1 and a.event_time BETWEEN fourteenStayStartTime and fourteenStayEndTime
and b.event_time BETWEEN fourteenStayStartTime and fourteenStayEndTime
) B USING(account_id);

这个sql在生产环境千万数据中 执行一次 耗费了将近一个小时

测试

1、数据准备

SELECT * from guess_user_room_flow;
SELECT * from guess_user_bet_flow;

我这里数据不多,下面两个查询的时间和条数,看出差别就行
image.png
image.png

2、我准备了使用in和inner join来查询最近一个月数据(因为数据比较少)

select DISTINCT account_id from data_tslog.guess_user_room_flow a INNER JOIN data_tslog.guess_user_bet_flow b USING(account_id)
where a.type = 1 and a.event_time BETWEEN UNIX_TIMESTAMP('2020-05-01') and UNIX_TIMESTAMP('2020-05-30 23:59:59')
and b.event_time BETWEEN UNIX_TIMESTAMP('2020-05-01') and UNIX_TIMESTAMP('2020-05-30 23:59:59');

select DISTINCT account_id from data_tslog.guess_user_room_flow a WHERE a.account_id in (
	SELECT DISTINCT account_id FROM data_tslog.guess_user_bet_flow b WHERE b.event_time BETWEEN UNIX_TIMESTAMP('2020-05-01') and UNIX_TIMESTAMP('2020-05-30 23:59:59')
) and a.type = 1 and a.event_time BETWEEN UNIX_TIMESTAMP('2020-05-01') and UNIX_TIMESTAMP('2020-05-30 23:59:59');

image.png
image.png
image.png
从上面可以看出来这速度不只是快整整一倍,几乎三倍,数据更多时候差距更明显
使用EXISTS速度更差,实时证明EXISTS和inner join差不多。

3、最上面sql解析以及修改后时间对比

image.png
image.png
如上面所见,sql 经过in处理过后,由于他们是并列关系所以使用并列and来关联。速度明显快多了,在线上今天第一种执行了一个小时,后面那条最终几秒就跑完了。

原因解析

1、in是子查询的结果作为父查询的条件,inner join是联合两张表去对比所有数据。
2、子表数据少,用in的效率高于关联,如果子表比较大的情况下则用join的效率高于用in
3、EXISTS也多用于子表大情况下

自己的话:其实嘛,我以前一直认为in超级慢,我也在考虑子表数据很大情况下,因为出于in 和 = 的比较,但是和inner join比较,在子表小的情况下还是挺受益的。事实证明双表数据都大的情况下请使用in,因为可以去出一部分。小的时候使用关联查询吧

后语
如果本文对你哪怕有一丁点帮助,请帮忙点好看。你的好看是我坚持写作的动力。