MySQL -其中IN导致100%cpu且无结果

Question

此查询耗时极短

select * from tbl1 limit 0,1

这个查询需要一秒钟

SELECT Distinct(col2) FROM tbl2 WHERE col3 = 2

此查询占用100%的cpu，并花费190秒的时间(0次提取)来返回结果

select * from tbl1 WHERE ID IN (SELECT Distinct(col2) FROM tbl2 WHERE col3 = 2) limit 0,1

我尝试在整个数据集上运行这个查询(而不仅仅是限制到一条记录)。

是什么导致了性能问题，我的表结构？

Answer 1

众所周知，MySQL中的子查询速度很慢。您可以使用join来加速此过程：

SELECT A.*
FROM
    tbl1 A
    INNER JOIN (
        SELECT DISTINCT col2
        FROM
            tbl2
        WHERE
            col3 = 2
    ) X ON X.col2 = A.ID
LIMIT 0, 1

更新tbl1的步骤

UPDATE
    tbl1 A
    INNER JOIN (
        SELECT DISTINCT col2
        FROM
            tbl2
        WHERE
            col3 = 2
    ) X ON X.col2 = A.ID
SET
    A.SomeCol = 'value'

Answer 2

在tbl2、col3或(甚至更好) (col3,col2)上添加索引。

然后使用这个(这里不需要DISTINCT)：

SELECT * 
FROM tbl1 
WHERE ID IN (SELECT col2 FROM tbl2 WHERE col3 = 2) 
LIMIT  0,1

或者更好的是，为了避免IN (SELECT ...)，这个版本：

SELECT * 
FROM tbl1 t
WHERE EXISTS 
      ( SELECT * 
        FROM tbl2 t2
        WHERE t2.col3 = 2 
          AND t2.col2 = t.ID
      ) 
LIMIT  0,1