問題描述

我有一個大約有一億行的表，列 'id' 是主鍵，它是表中唯一的鍵.

I have a table which has about one hundred million rows, and the column 'id' is the primary key, and it is the only key in the table.

我進行了如下查詢:

SELECT id,name FROM table WHERE id IN (id1, id2, id3, id4, ..., id1000);

IN"中的這 1000 個 id 實際上是由程序預先計算的常量整數.

These 1000 ids inside "IN" are actually const integers which are pre-caculated by a program.

但是Mysql每次查詢都要花一分鐘左右的時間.它并不慢，但它非常慢.條款有什么問題?非常感謝！

But Mysql spends about one minute to do the query every time. It is not slow, but it is incredibly slow. What's wrong with the clause? Thank you very much!

表定義:

CREATE TABLE mytable
(
    id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    catid smallint(5) unsigned NOT NULL DEFAULT '0',
    name char(39) NOT NULL,
    originalname varchar(255) NOT NULL,
    thumb varchar(255) NOT NULL DEFAULT '',
    description varchar(255) NOT NULL DEFAULT '',
    status tinyint(2) unsigned NOT NULL DEFAULT '1',
    creationtime int(11) unsigned NOT NULL DEFAULT '0',
    updatetime int(11) unsigned NOT NULL DEFAULT '0',
    score int(11) unsigned NOT NULL
    PRIMARY KEY (id)
)
ENGINE=MyISAM
AUTO_INCREMENT=13074618
DEFAULT CHARSET=utf8

推薦答案

在 IN 'list' 達到一定大小后，MySQL 將切換到 TABLE/INDEX SCAN，這可能會非常慢.

After the IN 'list' reaches a certain size, MySQL will swap to a TABLE/INDEX SCAN, this has the possibility of being terribly slow.

您可以重寫查詢以使用 TEMPORARY TABLE 或 JOIN (SELECT UNION)，看看這是否有助于提高性能.

You can rewrite the query to use a TEMPORARY TABLE, or JOIN (SELECT UNION), to see if that helps performance.

考慮運行 EXPLAIN EXTENDED 看看是什么地方變慢了下來.

Consider running EXPLAIN EXTENDED to see what slows it down.

這篇關于為什么“where id in (n1, n2, n3, ..., n2000)"慢得難以置信?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！