提高模糊查询速度！求大神指点

[zshsuming]

使用数据库工具 SQL2000
TABLE_1为主表
TABLE_2为从表
关联字段为ID
查询字段COLUMN_1
对从表的字段进行全模糊查询，从表有大概200W条记录
从表查询字段COLUMN_1建立非聚集索引

SELECT ID FROM TABLE_1 WHERE EXISTS(SELECT ID FROM TABLE_2 WHERE TABLE_1.ID=TABLE_2.ID AND TABLE_2.COLUMN_1 LIKE '%1234567%')
查询分析器显示为3秒

SELECT ID FROM TABLE_1 WHERE ID IN (SELECT ID FROM TABLE_2 WHERE TABLE_2.COLUMN_1 LIKE '%1234567%' GROUP BY ID)
查询分析器显示为3秒

SELECT ID FROM TABLE_2 WHERE TABLE_2.COLUMN_1 LIKE '%1234567%'
查询分析器显示为1秒

提问：
1，上面2个语句时间相同，哪种理论上效率更高？应该是EXISTS吧？
2，上面2个语句是否有优化空间，让查询变快？
3，有文章说索引对前模糊和全模糊是不起作用的，可是第3条语句执行很快，是否是索引在起作用？还是表结构和数据量起主要原因？

[--小F--]

%xx%这样格式的索引都利用不到 所以IN和EXISTS的效率应该是差不多的。
TABLE_2.COLUMN_1上面加索引的话 不走SCAN 但是扫描到结果以后就会返回值
比全表扫描或许要快。
楼主可以自己去看看执行计划这些

[快溜]

测试这条
SELECT ID FROM TABLE_1 join TABLE_2 on TABLE_1.ID=TABLE_2.ID
where TABLE_2.COLUMN_1 LIKE '%1234567%'

[以学习为目的]

SET SHOWPLAN_TEXT ON
再执行查询，看看执行计划

1、
用模糊查询like不能利用索引，所以关于in、exists的效率，楼主看看执行计划。个人感觉差不多
2、
你的两天语句都可以改成2楼的联合查询看看效率

[汤姆克鲁斯]

怎么还在用2000

id 上面都有索引吗？

个人没有优化的空间了。

[zshsuming]

修改成联合查询后为2秒，能秒杀不？个人觉得好像才200W条，不算特别多的数据量吧？

[發糞塗牆]

200W2秒够了，别要求太多，你的模糊查询%xxx%本来就不会用到索引，

[gogodiy]

如果主表大，从表小，可以使用IN；
如果主表小，从表大，最好使用EXISTS。

[汤姆克鲁斯]

[Quote=引用 5 楼 的回复:]

修改成联合查询后为2秒，能秒杀不？个人觉得好像才200W条，不算特别多的数据量吧？
[/Quote]
你这一对多，join 结果有差异吧

[昵称被占用了]

1，上面2个语句时间相同，哪种理论上效率更高？应该是EXISTS吧？
应该是EXISTS，但是差别很小，大部分情况是一样的

2，上面2个语句是否有优化空间，让查询变快？
基本没有了，分表是方法

3，有文章说索引对前模糊和全模糊是不起作用的，可是第3条语句执行很快，是否是索引在起作用？还是表结构和数据量起主要原因？
不是索引的作用，而是第三个语句少了连接操作