MySQL 5.6长WHERE IN查询非常缓慢

Question

自 MySQL 5.6 版本以来，一个非常简单但很长的查询所需的时间比 5.4 版本慢了几个数量级。

数据库结构：三个表，一个包含元素，一个包含类别，以及一个连接这两者的 M:N 表。创建语句如下：

CREATE TABLE element (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4257455 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE category (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE elements_categories (
  id int(11) NOT NULL AUTO_INCREMENT,
  element_id int(11) NOT NULL,
  category_id int(11) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY element_id (element_id,category_id),
  KEY elements_categories_element_id (element_id),
  KEY elements_categories_category_id (category_id),
  CONSTRAINT D7d489b06a407a0c1c70f108712c815e FOREIGN KEY (category_id) REFERENCES category (id),
  CONSTRAINT co_element_id_57f4f2ec0db9441c_fk_element_id FOREIGN KEY (element_id) REFERENCES element (id)
) ENGINE=InnoDB AUTO_INCREMENT=88131737 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

查询语句：

SELECT elements_categories.element_id, category.id, category.name
FROM category
    INNER JOIN elements_categories
        ON category.id = elements_categories.category_id
WHERE elements_categories.element_id IN (1, 2, 3, ...)

因此，在这个查询中，元素表甚至没有起到作用，我已经从之前的一个查询中得到了大量的ID。(免责声明：我正在使用ORM，即使是内联第一个查询也没有使事情变得更快。)IN子句中的值的数量可能会变得非常大，在我的例子中是14240。这不是问题，只需要花费十分之一秒左右。这是执行计划：

| id | select_type | table               | type   | possible_keys                                                             | key        | key_len | ref                             | rows  | Extra                    |
+----+-------------+---------------------+--------+---------------------------------------------------------------------------+------------+---------+---------------------------------+-------+--------------------------+
|  1 | SIMPLE      | elements_categories | range  | element_id,elements_categories_element_id,elements_categories.category_id | element_id | 4       | NULL                            | 42720 | Using where; Using index |
|  1 | SIMPLE      | category            | eq_ref | PRIMARY                                                                   | PRIMARY    | 4       | elements_categories.category_id |     1 | NULL                     |

当我添加一个元素时，执行时间激增到60秒加上200秒的提取时间。执行计划也会改变如下：

| id | select_type | table               | type | possible_keys                                                             | key                             | key_len | ref         | rows | Extra       |
+----+-------------+---------------------+------+---------------------------------------------------------------------------+---------------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | category            | ALL  | PRIMARY                                                                   | NULL                            | NULL    | NULL        |   75 | NULL        |
|  1 | SIMPLE      | elements_categories | ref  | element_id,elements_categories_element_id,elements_categories_category_id | elements_categories_category_id | 4       | category.id |  760 | Using where |

range和eq_ref查询变为ALL和ref，表的顺序改变了，虽然elements_categories.category_id是这两个表之间的外键，但并没有将它作为ref。我不明白为什么执行计划会改变。

有75个类别、4,300,000个元素和1,600,000个分配。

我猜测我在某个方面超过了一些大小限制，但无法确定是哪个。此外，我没有对MySQL 5.5安装进行任何更改，它一直使用旧的执行计划。

- Tim-Erwin

---

第一个查询计划对于您发布的查询没有意义。这意味着where子句是elements_categories.element_id IN (1, 2, 3, ...)或者有其他的where/join条件。您确定它是category.element_id IN ...吗？ - Vatev

非常敏锐，谢谢你。你的第一个假设是正确的，我已经相应地改变了查询。 - Tim-Erwin

能否为这两个表格“显示创建表格”？我对索引很感兴趣。表格的实际大小的一些指示也可能很有趣。 - Samuel Åslund

我发布了CREATE TABLE语句。有趣的是，MySQL 5.5和MSQL 5.6为空表提供的执行计划与上面的“坏”执行计划相同。 - Tim-Erwin

Answer 1

有几种方法可以欺骗优化器使用正确的执行计划：

1. 添加索引提示：... JOIN elements_categories FORCE INDEX (element_id)...
2. 交换表并使category成为LEFT JOIN（假设每个elements_categories都有一个category）。这不是通用解决方案，但在这种情况下应该有效。
3. 创建一个包含element_id的临时表，并在所有查询中使用JOIN它，而不是使用IN (1,2,3...)。您还应该能够使用IN (SELECT id FROM <temp table>)而不是文字。

- Vatev

---

非常感谢，问题在于我正在使用（真正使用，并进行了详细的过滤而不仅仅是方便），一个ORM，很难让它说服交换表等。 - Tim-Erwin

Answer 2

优化器在不同的参数时选择另一个计划的原因是它查看表格的统计信息并猜测哪个索引将删除最多的行，但这只是一种猜测，往往是错误的。 如果您知道更好的方法，您需要像@Vatev举的第一个示例那样告诉优化器该怎么做。

有趣的是，由于索引添加了额外的间接层，因此可能会增加更多的读取负担，所以优化器必须删除表格的超过一半以上才能被认为是有用的。（我不记得超过一半是多少了...）

另一个有趣的优化器特性是，如果索引包含表格所需的所有信息，它可以避免查找实际行，因此根据您的情况，通过向索引添加额外的列，您也许可以获益。这种优化在第一个查询计划中“使用索引”中使用，但在第二个查询计划中则没有使用。因此，向您的索引“elements_categories_category_id”中添加“element_id”可能会加速事情。请参见http://dev.mysql.com/doc/refman/5.6/en/explain-output.html。

- Samuel Åslund

---

关于索引...索引"elements_categories_element_id"是一个重复的索引，因为"UNIQUE KEY element_id"包含了该信息的超集。 - Samuel Åslund

没错，这就是ORM的缺点之一，会自动为所有外键创建索引。我会尝试调整索引，看看是否能提高速度。 - Tim-Erwin