为什么“where id in (n1, n2, n3, ..., n2000)"难以置信的慢? [英] Why does the "where id in (n1, n2, n3, ..., n2000)" incredibly slow?

问题描述

我有一个大约有一亿行的表，列 '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)"难以置信的慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！