多表（百万级）关联查询效率问题和大字段建立索引问题求教

[wubaozhang]

一个多表关联的查询（每个表都是百万级的，１１对应关系，表结构无法改动）
类似与这样的结构：
select a.id from
(selece a.id,a.username,a.last_update from tableA (nolock)) a
inner join (select b.username,keyword from tableB b(nolock) where XXXXX) b on a.username=b.username
inner join (select b.username,keyword from tableC c (nolock)where XXXXX) c on a.username=c.username
where (b.keyword like '%xx%' or c.keyword like '%xx%')
order by a.last_update

完整的查询根本动不了
分开单独查询ＩＮＮＥＲ语句和单独查询ＬＩＫＥ语句，分别需要２０秒和１５秒，请问从ＳＱＬ　ＳＥＲＶＥＲ分析角度来讲，这个ＳＱＬ用什么样的结构会查询更快呢？（理论上的就可以）
我尝试过这样的结构：
select a.id from tabelA INER JOIN TABLE B ON xxxx WHERE A.USERNAME IN (SELECT USERNAME FROM TABLEB WHERE KEYWORD LIKE '%XX%') order by xxxxxx

select a.id from tableA where a.username in (select SELECT USERNAME FROM TABLEB WHERE XXXX and KEYWORD LIKE '%XX%') order by xxxxxx

select a.id from (tableA INNER JOIN tableB where like xx) order by last_update

效率上都没有明显改善，还有什么办法呢？


另外还请问，ＮＶＣＨＡＲ字段超过９００个字节就无法建立索引，改为ＴＥＸＴ后只能建立全文索引但执行效率没有明显改变
那么大的字段（长度超过５０００个字节）各位是怎么执行ＬＩＫＥ查询的呢？

[gutlgutl]

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

下面的查询也将导致全表扫描：
select id from t where name like '%abc%'

强制查询使用索引：
select id from t with(index(索引名)) where num=@num

[hery2002]

显式指定索引,
参考楼上

[wubaozhang]

[Quote=引用 11 楼 gutlgutl 的回复:]
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索…
[/Quote]

谢谢，领教了
那么ＬＩＫＥ语句该怎么优化呢？总不能说为了避免全表扫描而放弃关键字的搜索吧？
强制索引也是需要本身有索引存在才行啊，但是超过９００字节的字段无法建立索引，那就谈不上强制使用哪个索引了啊

[gutlgutl]

超900用全文吧， like 非要用，也不能用 %like 可以考虑用 like%

你试一下吧把%like% 拆成二个，或改下条件的

[wubaozhang]

[Quote=引用 15 楼 gutlgutl 的回复:]
超900用全文吧， like 非要用，也不能用 %like 可以考虑用 like%

你试一下吧把%like% 拆成二个，或改下条件的
[/Quote]
不太明白，能举例说明不

[utpcb]

[Quote=引用 8 楼 wubaozhang 的回复:]
感谢关注
但是你这样查询应该并不比我原来的快吧？至少我首先把数据做了过滤再关联，你的ＳＱＬ相当于关联后再做过滤？不知道我看法对不对
２．ＬＩＫＥ语句怎么会不需要索引呢？ＳＱＬ任何查询都会需要有一个索引的吧？有些是系统自建的，有些是我们为了满足特殊需求在表上建的
[/Quote]
看看楼主没理解概念啊！不知道索引的原理！
你用数据库引擎优化顾问 帮你吧 傻瓜型的！ 在2000中视乎是叫索引微调精灵

[wubaozhang]

[Quote=引用 17 楼 utpcb 的回复:]
引用 8 楼 wubaozhang 的回复:
感谢关注
但是你这样查询应该并不比我原来的快吧？至少我首先把数据做了过滤再关联，你的ＳＱＬ相当于关联后再做过滤？不知道我看法对不对
２．ＬＩＫＥ语句怎么会不需要索引呢？ＳＱＬ任何查询都会需要有一个索引的吧？有些是系统自建的，有些是我们为了满足特殊需求在表上建的

看看楼主没理解概念啊！不知道索引的原理！
你用数据库引擎优化顾问 帮你吧 傻瓜型的！ 在2000中视乎是…
[/Quote]

谢谢，我试了一下，建议只是让我删除没用到的索引，可是我又不是专门为这个建立的索引，其他地方还要用的啊
或许我没看懂？

[jhwcd]

学习中！！！

[liangCK]

like导致索引失败.

[liangCK]

试试用全文索引.

[wubaozhang]

已经尝试过全文索引了，本来ＮＶＣＨＡＲ的ＬＩＫＥ，不带索引耗时１９＇，改为ＴＥＸＴ建立全文索引后耗时３０＇，头疼啊

[Herb2]

select a.id from   tableA a,tableB b ,tableC  c
where  a.username=b.username  and a.username=c.username and b.xxxx and c.xxxx
and  (b.keyword like '%xx%' or c.keyword like '%xx%') 
order by a.last_update

上面是改进后的语句。
有几个关键：
1、b.keyword like '%xx%' 基本不会用到索引，所以没必要建该字段的索引。
2、如果你的XXX是过滤记录的有效条件，想办法在其上建索引，并使用索引
2、在A表一定要建username 应该要建索引，这很关键，对于A表来说，只有这一个过滤条件。

[Herb2]

如果你主要依靠(b.keyword like '%xx%' or c.keyword like '%xx%') 来过滤数据。
那，没救了。会形成100万×100万的笛卡儿扫描。

[Herb2]

不会，你还有一个username过滤。

[-狙击手-]

强制索引试试

[wubaozhang]

感谢关注
但是你这样查询应该并不比我原来的快吧？至少我首先把数据做了过滤再关联，你的ＳＱＬ相当于关联后再做过滤？不知道我看法对不对
２．ＬＩＫＥ语句怎么会不需要索引呢？ＳＱＬ任何查询都会需要有一个索引的吧？有些是系统自建的，有些是我们为了满足特殊需求在表上建的

[wubaozhang]

感谢关注
怎么会没救了呢，我现在把ＳＱＬ改成了这种结构，虽然还是得１分多钟，至少能动了不是：
select a.id from
(selece a.id,a.username,a.last_update from tableA (nolock)) a
inner join (select b.username,keyword from tableB b(nolock) where XXXXX) b on a.username=b.username
inner join (select b.username,keyword from tableC c (nolock)where XXXXX) c on a.username=c.username
inner join
(
select distinct username from (
(select username from tableB where b.keyword like '%xx%' )
union all
(select username from tableC where c.keyword like '%xx%')
) k
) k on a.username=k.username
order by a.last_update

[wubaozhang]

[Quote=引用 7 楼 happyflystone 的回复:]
强制索引试试
[/Quote]

强烈请教：怎么强制建立索引？　谢谢

[wubaozhang]

ding

[hwei199]

这么好的帖子没有了下文？可惜啊。楼主，你最后的问题是怎么解决的，望不吝赐教！我的邮箱[e-mail]