自 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 as ref 尽管它是这两个表之间的外键。我不明白为什么计划会这样改变。

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

我的猜测是我超出了这里的某个大小限制，但无法确定是哪一个。此外，我没有更改 MySQL 5.5 安装的任何内容，它一直坚持以前的执行计划。

最佳答案

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

1. 添加 index hint : ... 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>)而不是文字。

关于MySQL 5.6 长 WHERE IN 查询非常慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33871822/