此 SELECT 查询需要 180 秒才能完成

2021-12-19 00:00:00 sql select mysql

更新:

只是在更显眼的地方提及它.当我将 IN 更改为 = 时，查询执行时间从 180 秒减少到 0.00008 秒.可笑的速度差异.

Just to mention it on a more visible place. When I changed IN for =, the query execution time went from 180 down to 0.00008 seconds. Ridiculous speed difference.

这个 SQL 查询需要 180 秒才能完成！这怎么可能?有没有办法优化它以使其更快?

This SQL query takes 180 seconds to finish! How is that possible? is there a way to optimize it to be faster?

SELECT IdLawVersionValidFrom 
FROM question_law_version 
WHERE IdQuestionLawVersion IN 
  (
  SELECT MAX(IdQuestionLawVersion) 
  FROM question_law_version 
  WHERE IdQuestionLaw IN 
    (
    SELECT MIN(IdQuestionLaw) 
    FROM question_law 
    WHERE IdQuestion=236 AND IdQuestionLaw>63
    )
  )

每个表中只有大约 5000 行，所以速度应该不会太慢.

There are only about 5000 rows in each table so it shouldn't be so slow.

推荐答案

(发布我的评论作为答案，显然它确实有所作为！)

(Posting my comment as an answer as apparently it did make a difference!)

如果您更改 IN 有什么不同=?

Any difference if you change the IN to =?

如果有人想进一步调查这个问题，我刚刚做了一个测试，发现它很容易重现.

If anyone wants to investigate this further I've just done a test and found it very easy to reproduce.

创建表

CREATE TABLE `filler` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)

创建过程

CREATE PROCEDURE `prc_filler`(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END

填充表

  call prc_filler(5000)

查询 1

SELECT id 
FROM filler 
WHERE id =  (SELECT MAX(id) FROM filler  WHERE id =   
 ( SELECT MIN(id) 
    FROM filler
    WHERE id between 2000 and 3000
    )
  )

Equals 解释输出 http://img689.imageshack.us/img689/5592/equals.png

查询 2(同样的问题)

Query 2 (same problem)

SELECT id 
FROM filler 
WHERE id in  (SELECT MAX(id) FROM filler  WHERE id in   
 ( SELECT MIN(id) 
    FROM filler
    WHERE id between 2000 and 3000
    )
  )

解释输出 http://img291.imageshack.us/img291/8129/52037513.png

相关文章