56,675
社区成员
发帖
与我相关
我的任务
分享
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT a.*
from `bigTable` AS a
WHERE a.`id` in (SELECT pkValue
FROM `smallTable`
WHERE isProccessing = 1
AND finished = 0
);
COMMIT;
-- 上面的代码大约需要 8 分钟才能跑完;
--1. 改成 Exists 或 inner join 都还是很慢;
--2. 改成 in ( 'aaa','bbb','ccc' ) 实际值就快了;
--3. 大表表数据量:6909383 ,主键id数据类型: varchar(32), data_free: 8388608;
--4. 小表数据量 十条数据, 最多 100 条
WHERE FIND_IN_SET(a.`id`,(SELECT GROUP_CONCAT(pkValue)
FROM `smallTable`
WHERE isProccessing = 1
AND finished = 0
))
WHERE a.`id` in (SELECT * FROM(SELECT pkValue
FROM `smallTable`
WHERE isProccessing = 1
AND finished = 0
LIMIT 500
) A
);