MySQL 5.6 long WHERE IN查询非常慢

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 5.6 long WHERE IN查询非常慢，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4097字，纯文字阅读大概需要6分钟。

内容图文

从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,并且内联第一个查询并没有使事情变快.)在我的示例14240中,IN子句中的值数量可以变得非常大.这没问题,只需要十分之一的时间即可.秒左右.那是执行计划：

| 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 |

交换了ALL和ref的range和eq_ref查找,切换了表的顺序,尽管它是这两个表之间的外键,但不使用elements_categories.category_id作为ref.我不明白为什么计划会像这样改变.

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

我的猜测是我在这里超出了一些大小限制,但无法确定是哪一个.另外,我一直没有更改MySQL 5.5安装中的任何内容,因为MySQL 5.5安装始终坚持以前的执行计划.

解决方法:

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

>添加一个index hint：… JOIN elements_categories FORCE INDEX(element_id)…
>将表交换一下,并将类别设为LEFT JOIN(假设每个elements_categories都有一个类别).这不是通用解决方案,但在这种情况下应该可以使用.
>使用element_id创建一个临时表,并在所有查询中将其加入,而不使用IN(1,2,3 …).您还应该能够使用IN(SELECT id FROM< temp table>)代替文字.

内容总结

以上是互联网集市为您收集整理的MySQL 5.6 long WHERE IN查询非常慢全部内容，希望文章能够帮你解决MySQL 5.6 long WHERE IN查询非常慢所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端