MySQL - WHERE 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

在 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

Answer 2

MySQL 中的子查询是出了名的慢。您可以通过连接来加快速度：

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'