为什么“where id in (n1, n2, n3, ..., n2000)"慢得难以置信?

2021-09-24 00:00:00 where-clause mysql

我有一个大约有一亿行的表，列 '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.

相关文章