問題描述

檢查問題此 SELECT 查詢需要 180 秒才能完成(檢查問題本身的評論).
IN只能與一個值進行比較，但時間差異仍然很大.
為什么會這樣?

總結:這是一個 MySQL 中的>已知問題，并在 MySQL 5.6.x 中修復.該問題是由于使用 IN 的子查詢被錯誤地識別為依賴子查詢而不是獨立子查詢時缺少優化.

當您對原始查詢運行 EXPLAIN 時，它會返回:

當您將 IN 更改為 = 時，您會得到:

每個依賴子查詢在它所包含的查詢中的每一行運行一次，而子查詢只運行一次.當存在可以轉換為連接的條件時，MySQL 有時可以優化依賴子查詢，但這里并非如此.

現在這當然留下了為什么 MySQL 認為 IN 版本需要是依賴子查詢的問題.我制作了一個簡化版的查詢來幫助調查這個問題.我創建了兩個表foo"和bar"，其中前者只包含一個 id 列，后者包含一個 id 和一個 foo id(盡管我沒有創建外鍵約束).然后我用 1000 行填充了兩個表:

CREATE TABLE foo (id INT PRIMARY KEY NOT NULL);CREATE TABLE bar (id INT PRIMARY KEY, foo_id INT NOT NULL);-- 在每個表中填充 1000 行選擇 ID從 foo在哪里(選擇最大(foo_id)發件人欄);

這個簡化的查詢有和之前一樣的問題——內部選擇被當作依賴子查詢處理，沒有進行優化，導致內部查詢每行運行一次.查詢幾乎需要一秒鐘才能運行.再次將 IN 更改為 = 幾乎可以立即運行查詢.

我用來填充表格的代碼如下，以防有人希望重現結果.

CREATE TABLE 填充符 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT) 引擎=內存；分隔符 $$創建程序 prc_filler(cnt INT)開始聲明 _cnt INT;設置_cnt = 1;而 _cnt <= cnt 做插入INTO填料選擇_cnt;SET _cnt = _cnt + 1;結束時；結尾$$分隔符；呼叫 prc_filler(1000);INSERT foo SELECT id FROM fills;INSERT bar SELECT id, id FROM 填充符；

Check the question This SELECT query takes 180 seconds to finish (check the comments on the question itself).
The IN get to be compared against only one value, but still the time difference is enormous.
Why is it like that?

Summary: This is a known problem in MySQL and was fixed in MySQL 5.6.x. The problem is due to a missing optimization when a subquery using IN is incorrectly indentified as dependent subquery instead of an independent subquery.

When you run EXPLAIN on the original query it returns this:

1  'PRIMARY'             'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
2  'DEPENDENT SUBQUERY'  'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
3  'DEPENDENT SUBQUERY'  'question_law'          'ALL'  ''  ''  ''  ''  10040  'Using where'

When you change IN to = you get this:

1  'PRIMARY'   'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
2  'SUBQUERY'  'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
3  'SUBQUERY'  'question_law'          'ALL'  ''  ''  ''  ''  10040  'Using where'

Each dependent subquery is run once per row in the query it is contained in, whereas the subquery is run only once. MySQL can sometimes optimize dependent subqueries when there is a condition that can be converted to a join but here that is not the case.

Now this of course leaves the question of why MySQL believes that the IN version needs to be a dependent subquery. I have made a simplified version of the query to help investigate this. I created two tables 'foo' and 'bar' where the former contains only an id column, and the latter contains both an id and a foo id (though I didn't create a foreign key constraint). Then I populated both tables with 1000 rows:

CREATE TABLE foo (id INT PRIMARY KEY NOT NULL);
CREATE TABLE bar (id INT PRIMARY KEY, foo_id INT NOT NULL);

-- populate tables with 1000 rows in each

SELECT id
FROM foo
WHERE id IN
(
    SELECT MAX(foo_id)
    FROM bar
);

This simplified query has the same problem as before - the inner select is treated as a dependent subquery and no optimization is performed, causing the inner query to be run once per row. The query takes almost one second to run. Changing the IN to = again allows the query to run almost instantly.

The code I used to populate the tables is below, in case anyone wishes to reproduce the results.

CREATE TABLE filler (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

DELIMITER $$

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
$$

DELIMITER ;

CALL prc_filler(1000);

INSERT foo SELECT id FROM filler;
INSERT bar SELECT id, id FROM filler;

這篇關于為什么 IN 條件比“="慢?在 sql 中?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！