mysql中的in问题

0 0

mysql中的in问题

环境：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;

mysql 性能

11 years, 11 months ago

ldsai

ldsai 11 years, 11 months ago

这个问题的产生原因是由于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

answered 11 years, 11 months ago

ViRyo

ViRyo answered 11 years, 11 months ago