INSERT SRELECT 的瓶颈在哪儿?
zjcxc 2017-09-07 03:24:27 近 3000 万的数据,分布在 128 个表,不是平均分布,最大的表记录数 140 万
表结构:
CREATE TABLE tb ( a int, b int, c int, d varchar(40), e int )
查询语句:
SELECT a, b, c, d, COUNT(DISTINCT e) FROM tb GROUP BY a, b, c, d
记录最多的表上执行耗时 2 秒
程序使用 32 个线程并行查询 128 个表,总共花费的时间不足 5 秒(线程数为 8 和 16 时也差不多,稍微多一点时间)
问题:
把 SELECT 的数据写入表, 尝试了 INSERT SELECT 和 CREATE TABLE AS SELECT
用 32 个线程需要 25 秒左右, 8个线程也要近 20 秒,性能下降非常厉害
对于最大的表,单独测试 INSERT SELECT,比仅查询多按一秒(仅查询 2 秒,INSERT SELECT 约 2.8秒)
单表测试来看, INSERT SELECT 确实降低性能,但并行查询后的总时间开销也太离谱了,求解是什么原因导致的
注:
这个数据原来是弄成128个分区的分区表, 原来以为是 INSERT SELECT 锁表而不是分区导致的,所以分成 128 个表了,结果仍然出现这种并行 INSERT SELECT 极慢的情况
另外做了另一种测试,把 SELECT 的结棍拼成 INSERT VALUES + PREPARE STATMENT + EXECUTE STATMENT,这种处理的性能与直接 SELECT 几乎相同,所以应该可以排除是写目标表的锁(也测试过 INSERT 到不同的表,仍然是极慢)
INSERT SELECT 和 直接的 SELECT 的差别应该在于锁,所以测试了 SELECT FOR UPDATE 和 LOCK IN SHARE MODE,结果发现确实加锁后也极慢,但是锁在不同的表上,所以各查询之间的锁不冲突,不应该影响有这么大,而且单独查询最大的表验证,加锁导致的发损失不大,为什么并发的情况下会这么严重?