MySQL-mysql中的in问题

发布于 2017-03-11 08:00:24 字数 577 浏览 1237 评论 3

环境:mysql=>myisam
x_base_goods:goodsID(int) 且是主键
x_backpack_sub:createRole(int)非索引

1.SELECT goodsname FROM x_base_goods WHERE goodsid IN(SELECT goodsid FROM x_backpack_sub WHERE createrole=231);
2.SELECT goodsid FROM x_backpack_sub WHERE createrole=231
执行1非常慢,卡半天[给createRole加索引后就没问题]
执行2非常快。

求解释[我觉得2很快的话,1也应该很快]

补充1:第1个语句profiles显示瓶颈在sending data,%99的时间

补充2:第1个语句转换以下语句时,瞬时完成,很快[createRole未加索引]
SELECT goodsname FROM x_base_goods a inner JOIN x_backpack_sub b ON a.goodsID=b.goodsID WHERE createrole=231;

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

归属感 2017-10-04 02:43:53 3 楼

1、可能与执行顺序有关,执行了第一条语句(用explain解析一下这条sql语句,可以看到是分两步执行的,其中包括了第二条sql语句)之后,第二条语句已经在query_cache中
2、查看sql语句执行过程中的profiles信息
set profiling = 1;
执行第一条sql语句
执行第二条sql语句
show profiles;
show profiles CPU,BLOCK_IO io for query 1;
查看相应的信息,找出性能瓶颈
这样也许能找到答案

甜柠檬 2017-08-20 21:51:08 2 楼

这个问题的产生原因是由于MySQL的查询优化器对IN这种子查询的优化不够好,很多时候会产生与人们想像中相反的效果。
我们可以利用EXPLAIN EXTENDED对第一条语句具体分析如下:

可以看到子查询“SELECT goodsid FROM x_backpack_sub WHERE createrole=231”与外部查询产生了依赖关系,而不是按照我们的想法是独立的一个查询。到底MySQL内部是如何执行整条语句的呢,我们用SHOW WARNINGS看一下:

结果很清楚了,原来的SQL语句被重写成了:

select `goodsname` from `x_base_goods`
where exists(
select 1 from `x_backpack_sub` where `x_backpack_sub`.`createrole` = 231 and `x_base_goods`.`goodsid` =`x_backpack_sub`.`goodsid`)

第一条语句的真实执行过程为:对x_base_goods表执行全表扫描,并对每一行都执行一次子查询。在大数据量以及createrole字段没有索引的情况下焉能不慢?
对表x_packpack_sub的字段createrole建立索引后再次explain:

子查询使用了索引,因此速度提升。
最后再贴上补充2中的语句分析:

两表按顺序执行各自的查询语句,a表使用了主键索引,b表进行了全表扫描,数据量不大时与b表加索引的情况没有明显的速度差异。

参考文献:
1、《高性能MySQL》(中文第2版)139页
2、http://www.mysqlperformanceblog.com/2006/07/24/extended-explain/
3、http://hidba.org/?p=260

想挽留 2017-03-17 22:07:10 1 楼

SELECT goodsname FROM x_base_goods WHERE goodsid IN(SELECT goodsid FROM x_backpack_sub WHERE createrole=231);

可能跟执行顺序有关,这条语句有可能是先执行SELECT goodsname FROM x_base_goods
再把结果集跟SELECT goodsid FROM x_backpack_sub WHERE createrole=231做比较。这样有可能会变慢了。

如果SELECT goodsid FROM x_backpack_sub WHERE createrole=231 的结果集不是特别大,就拆分成二句吧执行吧

当然createRole字段要加索引

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击“接受”或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。