前言

在搬砖道路上，写了多年的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;

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

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');

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

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

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

原因解析

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

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

二、嵌套查询优化

背景

最近写一个存储过程，执行速度大于几个小时，导致其他同事的存储过程都停止了
，今天用了嵌套查询优化好了

第一版：初始脚本

SELECT startTime,b.room_id,b.vip,b.user_type,b.factor,b.account_id,2,
       IFNULL(SUM(IF(t.mfr_id = 268,t.quantity,0)),0) socks,IFNULL(SUM(IF(t.mfr_id = 283,t.quantity,0)),0) hat
FROM data_bi.`bi_user_catch_spec_fish_statistics` b left JOIN data_tslog.`inc_prop_flow` t on b.account_id = t.account_id
WHERE t.event_time BETWEEN startTime and endTime
GROUP BY b.room_id,b.vip,b.user_type,b.factor,b.account_id

第二版：临时表拆分

CREATE TEMPORARY TABLE `tempTableCatsh`
    (
		 `room_id` INT(11) NOT NULL DEFAULT 0,
		 `vip` INT(11) NOT NULL DEFAULT 0,
		 `user_type` INT(11) NOT NULL DEFAULT 0,
		 `factor` VARCHAR(10) NOT NULL DEFAULT 0,
     `account_id` BIGINT(20) NOT NULL DEFAULT 0,
		 `current_factor` INT(11) NOT NULL DEFAULT 0,
        PRIMARY KEY (room_id, vip, user_type, `factor`, account_id)
    );
CREATE TEMPORARY TABLE `tempTableIncProp`
    (
		 `account_id` BIGINT(20) NOT NULL DEFAULT 0,
		 `mfr_id` INT(10) NOT NULL DEFAULT 0,
		 `quantity` BIGINT(20) NOT NULL DEFAULT 0,
        PRIMARY KEY (`account_id`,`mfr_id`)
    );

-- 临时表新增数据
-- 查询今天数据并分组
insert into tempTableCatsh(current_factor,room_id,vip,user_type,factor,account_id)
SELECT SUM(current_factor),room_id,vip,user_type,factor,account_id
from data_bi.`bi_user_catch_spec_fish_statistics`  where event_time BETWEEN startTime and endTime AND fish_id = 141
GROUP BY room_id,vip,user_type,factor,account_id
    ON DUPLICATE KEY UPDATE current_factor=(current_factor);
-- 查询今天圣诞帽袜道具信息
insert into tempTableIncProp(account_id,mfr_id,quantity)
SELECT account_id,mfr_id,SUM(quantity)
FROM data_tslog.`inc_prop_flow` WHERE event_time BETWEEN startTime and endTime AND mfr_id in (268, 283)
GROUP BY account_id
    ON DUPLICATE KEY UPDATE quantity=(quantity);

-- -- 单玩家统计
insert into data_bi.`bi_chris_prop_statistics`(event_time,room_id,vip,user_type,factor,account_id,flag_type,socks_num,hat_num)
SELECT startTime, a.room_id,a.vip,a.user_type,a.factor,a.account_id,2,
       IFNULL(SUM(IF(b.mfr_id = 268,b.quantity,0)),0) socks,IFNULL(SUM(IF(b.mfr_id = 283,b.quantity,0)),0) hat
FROM tempTableCatsh a LEFT JOIN tempTableIncProp b ON a.account_id = b.account_id
GROUP BY a.room_id,a.vip,a.user_type,a.factor,a.account_id
HAVING a.room_id is NOT null and a.vip is NOT null and a.user_type is NOT null and a.factor is NOT null
  ON DUPLICATE KEY UPDATE socks_num=(socks_num), hat_num=(hat_num)

第三版：嵌套查询

建标就参考第二版的建表

-- 查询今天数据并分组
insert into tempTableCatsh(current_factor,room_id,vip,user_type,factor,account_id)
SELECT SUM(current_factor),room_id,vip,user_type,factor,account_id
FROM (
SELECT current_factor,room_id,vip,user_type,factor,account_id
from data_bi.`bi_user_catch_spec_fish_statistics` where event_time BETWEEN startTime and endTime AND fish_id = 141
) t
GROUP BY room_id,vip,user_type,factor,account_id
ON DUPLICATE KEY UPDATE current_factor=(current_factor);
-- 查询今天圣诞帽袜道具信息
insert into tempTableIncProp(account_id,mfr_id,quantity)
SELECT account_id,mfr_id,sum(quantity)
FROM (
SELECT account_id,mfr_id,quantity
FROM data_tslog.`inc_prop_flow`
WHERE event_time BETWEEN startTime and endTime AND mfr_id in (268, 283)
) t
GROUP BY account_id,mfr_id
ON DUPLICATE KEY UPDATE quantity=(quantity);

-- 捕获统计
insert into data_bi.`bi_chris_prop_statistics`(event_time,room_id,vip,user_type,factor,account_id,flag_type,socks_num,hat_num)
SELECT startTime, room_id,vip,user_type,factor,0,1,
  IFNULL(SUM(IF(mfr_id = 268,quantity,0)),0) socks,IFNULL(SUM(IF(mfr_id = 283,quantity,0)),0) hat
FROM ( 
	SELECT a.room_id,a.vip,a.user_type,a.factor,b.mfr_id,b.quantity,a.account_id
	FROM tempTableCatsh a LEFT JOIN tempTableIncProp b ON a.account_id = b.account_id
	WHERE a.room_id is NOT null and a.vip is NOT null and a.user_type is NOT null and a.factor is NOT null
	) t
GROUP BY room_id,vip,user_type,factor
ON DUPLICATE KEY UPDATE socks_num=(socks_num), hat_num=(hat_num);

-- 单个玩家
insert into data_bi.`bi_chris_prop_statistics`(event_time,room_id,vip,user_type,factor,account_id,flag_type,socks_num,hat_num)
SELECT startTime, room_id,vip,user_type,factor,account_id,1,
  IFNULL(SUM(IF(mfr_id = 268,quantity,0)),0) socks,IFNULL(SUM(IF(mfr_id = 283,quantity,0)),0) hat
FROM ( 
	SELECT a.room_id,a.vip,a.user_type,a.factor,b.mfr_id,b.quantity,a.account_id
	FROM tempTableCatsh a LEFT JOIN tempTableIncProp b ON a.account_id = b.account_id
	WHERE a.room_id is NOT null and a.vip is NOT null and a.user_type is NOT null and a.factor is NOT null
	) t
GROUP BY room_id,vip,user_type,factor,account_id
ON DUPLICATE KEY UPDATE socks_num=(socks_num), hat_num=(hat_num);

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

纯洁的麦田

MYSQL存储过程 (二)｜速度优化篇

前言