同样的语句，查询效率相差万倍的SQL，为什么呢，我该如何处理

问题描述：

今天发现一个SQL执行的特别慢，就在mysql客户端上运行了一些相关的sql语句，结果如下

1：/*[20:11:25][ 187 ms]*/

select count(*) from MD_VENDOR_MATERIAL ;

结果为：353329条记录，

2：/*[20:10:51][ 15 ms]*/

select distinct a.org_id from SYS_USER a, REMIND_PERSONAL_SETTING b where a.USER_ID = b.USER_ID and b.ITEM_ID = -79 and REMIND_VALUE = 1 ;

结果为：868,4974,5006,754,147,42,727,349,5368,117,5001

3：/*[20:29:10][ 15 ms]*/

select count(*) from MD_VENDOR_MATERIAL where VENDOR_ID in (868,4974,5006,754,147,42,727,349,5368,117,5001) ;

结果为：17048条记录

4：/*[20:07:10][250812 ms]*/

select count(*) from MD_VENDOR_MATERIAL where VENDOR_ID in (select distinct a.org_id from SYS_USER a, REMIND_PERSONAL_SETTING b where a.USER_ID = b.USER_ID and b.ITEM_ID = -79 and REMIND_VALUE = 1) ;

结果为：17048条记录

3和4的区别就是in中一个是查询后的值，另一个直接是子查询

两者的效率竟然相差了万倍以上，差异

请大家帮忙看看，因为程序中还有很多in中直接是放入sql语句的，要不要更改。这到底是怎么回事

mysql Ver 14.12 Distrib 5.0.72, for pc-solaris2.10 (x86_64) using readline 5.1

答

用in(select ...)方式，会导致查询计划只能由主表开始，即先全表扫描MD_VENDOR_MATERIAL再挨个检查是否符合where条件，所以很慢。
有两种方法：
1.用两台SQL查询，即先查出你的SQL 2 的结果，再把它当常数传给SQL 1
2.改写SQL为
select count(*) from MD_VENDOR_MATERIAL m, SYS_USER a, REMIND_PERSONAL_SETTING b where m.VENDOR_ID = a.org_id and a.USER_ID = b.USER_ID and b.ITEM_ID = -79 and REMIND_VALUE = 1 ;

这样MySQL能自动选择更优的查询计划，而不是象之前一样必须从主表MD_VENDOR_MATERIAL开始扫描。

答

去搜索一下，sql性能方面的资料吧。

这里把你这个问题给解决了，以后遇到别的性能问题，你也还要问。

看看sql效率的资料会好点。

答

250812除以17048的结果是什么呢？约等于15ms。也就是说实际上你每条结果都执行了一次“select distinct a.org_id from SYS_USER a, REMIND_PERSONAL_SETTING b where a.USER_ID = b.USER_ID and b.ITEM_ID = -79 and REMIND_VALUE = 1 ; “。
数据库引擎没有先求出in里面的结果然后在查询MD_VENDOR_MATERIAL表。而是从MD_VENDOR_MATERIAL拿出一条记录，然后查询select distinct a.org_id from SYS_USER a, REMIND_PERSONAL_SETTING b where a.USER_ID = b.USER_ID and b.ITEM_ID = -79 and REMIND_VALUE = 1 ; “来进行比对。
建议你这么写试试
[code="java"]
select count(m.*) from MD_VENDOR_MATERIAL as m，（select distinct a.org_id as oid from SYS_USER a, REMIND_PERSONAL_SETTING b where a.USER_ID = b.USER_ID and b.ITEM_ID = -79 and REMIND_VALUE = 1） as ids where m.VENDOR_ID = ids.oid ;

[/code]
把IN变成内连接试试。

同样的语句，查询效率相差万倍的SQL，为什么呢，我该如何处理

相关推荐