MySQL 5.6 long WHERE IN查询非常慢
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 5.6 long WHERE IN查询非常慢,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4097字,纯文字阅读大概需要6分钟。
内容图文
![MySQL 5.6 long WHERE IN查询非常慢](/upload/InfoBanner/zyjiaocheng/882/abf1027782a14f5eabda66928437d450.jpg)
从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 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。