坑爹的MYSQL IN关键字中的子查询

xinzaibing 发布于 2012/08/20 14:26
阅读 2K+
收藏 1

今天发现有个表的索引可以改进一下,原来的索引是PRIMARY KEY(TIMESTAMP, ID1, ID2);

由于ID1和ID2都是指定值查找,TIMESTAMP都是范围查找,当范围查找在第一个时,只能用到TIMESTAMP这个列。

然后我就把这个索引改成PRIMARY KEY(ID1, ID2,TIMESTAMP); 当执行指定ID1,ID2值的查询中,例如:select * from ID1=1 AND ID2=2 AND TIMESTAMP<'2012-8-20' AND TIMESTAMP>'2012-7-20' ,性能提高了很多。

此时突然发现有些查询是 select * from ID1 in (select ID1 from other_table) AND ID2=2 AND TIMESTAMP<'2012-8-20' AND TIMESTAMP>'2012-7-20' 类似这样的在IN中包含子查询的语句,执行起来非常慢...

一看查询计划就发现是坑爹了...

但是如果是这种查询:select * from ID1 in (1,2,3,4) AND ID2=2 AND TIMESTAMP<'2012-8-20' AND TIMESTAMP>'2012-7-20'  又可以完全使用索引

有人说可以改成join,改成join后,索引利用率跟以前没区别..太低了。

现在改成先用游标保存子查询的结果,然后用拼接字符串,拼好后,再进行查询操作。。唉!

加载中
0
sxgkwei
sxgkwei
select t1.* from t1,other_table t2 where t1.ID1=t2.ID1 AND t1.ID2=2 AND t1.TIMESTAMP<'2012-8-20' AND t1.TIMESTAMP>'2012-7-20'
0
游侠
游侠
有人说可以改成join,改成join后,索引利用率跟以前没区别..太低了。

是这样么?贴下你的sql看看。
游侠
游侠
回复 @xinzaibing : 再加一个索引(ID2,TIMESTAMP)试试
xinzaibing
xinzaibing
图在回复中
xinzaibing
xinzaibing
图在恢复中,join和直接指定in中的值时主键索引使用是不一样的
0
idea_biu
idea_biu
用explain 把语句打印出来看看
xinzaibing
xinzaibing
打印出来了,看回复
0
酒逍遥
酒逍遥

in 中如果有 子查询是没办法用索引的..

把子查询的结果拼成字符串放到in 中效率是最高的.

join的话 要看join的另一张的表的结构了

xinzaibing
xinzaibing
是啊,用join的话,只能用到前缀索引,由于数据表非常大,用到一个前缀索引还是很慢,所以比较坑。。。
0
idea_biu
idea_biu

1.in 查询中使用了EQUIPMENTID做为索引,其索引结果集17577行。(一般结果集不超过总结果的10%,为较优索引)

2.join 两尝试方法:用force index(字段)指定索引,

join方法2:使用排序字段做为索引 order by 字段 (这个要自己尝试一下,挑选rows集少的字段)

0
xinzaibing
xinzaibing

引用来自“Richardx”的答案

1.in 查询中使用了EQUIPMENTID做为索引,其索引结果集17577行。(一般结果集不超过总结果的10%,为较优索引)

2.join 两尝试方法:用force index(字段)指定索引,

join方法2:使用排序字段做为索引 order by 字段 (这个要自己尝试一下,挑选rows集少的字段)

我把两个查询profiles都答应出来了,性能上几乎没有区别,不知道是不是数据不够平均的问题

返回顶部
顶部