还是LIKE+order by查询的效率问题,关于怎么确定使用哪个索引,怎么决定是否使用全表扫描

[wubaozhang]

基本问题如题,具体如下
当前有一个表(500万记录)
tableA(id as int,c1 as int,lastUpdate as dateTime,ct1 as nvchar(300),ct2 as nvchar(5000),ct3 as nvchar(5000))
表上建立有索引:
IX_tableA_ID on id
IX_tableA_c1 on c1
IX_tableA_lastUpdate on lastUpdate desc
IX_tableA_ct1 on ct1
IX_tableA_search on c1,lastUpdate desc,ct1
现在需要做以下查询:
select id from tableA
where c1<>1 and c1<>100 and isNull(ct1,'')+isNull(ct2,'')+isNull(ct3,'') like '%keyStr%'
order by lastUpdate desc
从我的看法来说,我认为这里最合适的索引是IX_tableA_search
但是执行计划看是IX_tableA_c1被启用,从而造成了TABLE SCAN记录集50W(所有符合的)
即使我使用with(index(IX_tableA_search))强制索引,从执行计划看还是无效,优先使用的还是IX_tableA_c1

不知道是什么原因呢?又能怎么解决呢?

[SQLnewlearner]

这是个好问题。标记一下再看高手们的解答。

[nzperfect]

[Quote=引用 24 楼 wubaozhang 的回复:]
引用 21 楼 perfectaction 的回复:
这中多like的搞成全文索引试试呢


like '%%' 不是不使用索引吗,搞成全文索引有用吗?
[/Quote]

全文索引就不是按like去查询了，你查查资料吧。

[wubaozhang]

[Quote=引用 26 楼 perfectaction 的回复:]
引用 24 楼 wubaozhang 的回复:
引用 21 楼 perfectaction 的回复:
这中多like的搞成全文索引试试呢


like '%%' 不是不使用索引吗,搞成全文索引有用吗?


全文索引就不是按like去查询了，你查查资料吧。
[/Quote]
不好意思,因为我不是做DBA的,所以这些方面的概念不是很清晰
全文索引的话因为需要另建立文件,管理\备份起来比较烦琐,涉及的面也相对较大,如何建立有效的全文索引也没有特别的研究过,试一下吧先
能介绍一下这方面的东西吗 或者推荐一个值得读的文章,毕竟现在的信息是浩如烟海,找有用的东西还真不容易 谢谢

[nzperfect]

[Quote=引用 27 楼 wubaozhang 的回复:]
引用 26 楼 perfectaction 的回复:
引用 24 楼 wubaozhang 的回复:
引用 21 楼 perfectaction 的回复:
这中多like的搞成全文索引试试呢


like '%%' 不是不使用索引吗,搞成全文索引有用吗?


全文索引就不是按like去查询了，你查查资料吧。

不好意思,因为我不是做DBA的,所以这些方面的概念不是很清晰
全文索引的话因为需要另建立文件,管理\备份起来比较烦琐,涉及的面也相对较大,如何建立有效的全文索引也没有特别的…
[/Quote]

csdn常贴的是这个，可以试试先：

一个完整的SQL SERVER数据库全文索引的示例。（以pubs数据库为例） 

首先，介绍利用系统存储过程创建全文索引的具体步骤： 

1) 启动数据库的全文处理功能          (sp_fulltext_database) 
2) 建立全文目录                      (sp_fulltext_catalog) 
3) 在全文目录中注册需要全文索引的表  (sp_fulltext_table) 
4) 指出表中需要全文索引的列名        (sp_fulltext_column) 
5) 为表创建全文索引                  (sp_fulltext_table) 
6) 填充全文目录                      (sp_fulltext_catalog) 


---------********示例********------------- 
以对pubs数据库的title和notes列建立全文索引，之后使用索引查询title列或notes列中包含有datebase 或computer字符串的图书名称： 

在这之前，需要安装Microsoft Search服务，启动SQL server全文搜索服务 


user pubs --打开数据库 
go 
--检查数据库pubs是否支持全文索引，如果不支持 
--则使用sp_fulltext_database 打开该功能 
if(select databaseproperty('pubs','isfulltextenabled'))=0  
  execute sp_fulltext_database 'enable' 

--建立全文目录FT_PUBS 
execute sp_fulltext_catalog 'FT_pubs','create' 

--为title表建立全文索引数据元 
execute sp_fulltext_table 'title','create','FT_pubs','UPKCL_titleidind' 

--设置全文索引列名 
execute sp_fulltext_column 'title','title','add' 
execute sp_fulltext_column 'title','notes','add' 

--建立全文索引 
--activate，是激活表的全文检索能力，也就是在全文目录中注册该表 
execute sp_fulltext_table 'title','activate' 

--填充全文索引目录 
execute sp_fulltext_catalog 'FT_pubs','start_full' 
go 

--检查全文目录填充情况 
While fulltextcatalogproperty('FT_pubs','populateStatus') <> 0 
begin 

--如果全文目录正处于填充状态，则等待30秒后再检测一次 
waitfor delay '0:0:30' 
end 

--全文目录填充完成后，即可使用全文目录检索 

select title 
form 
where CONTAINS(title,'database') 
or CONTAINS(title,'computer') 
or CONTAINS(notes,'database') 
or CONTAINS(notes,'database') 


'--------------以下介绍一下全文操作类的系统存储过程 
过程名称：sp_fulltext_service 
执行权限：serveradmin或系统管理员 
作    用：设置全文搜索属性 


过程名称：sp_fulltext_catalog 
执行权限：db_owner及更高角色成员 
作    用：创建和删除一个全文目录，启动或停止一个全文目录的索引操作 


过程名称：sp_fulltext_database 
执行权限：db_owner角色成员 
作    用：初始化全文索引或删除数据库中所有全文目录 


过程名称：sp_fulltext_table 
执行权限：db_ddladnmin或db_owner角色成员 
作    用：将一个表标识为全文索引表或非全文索引表 


过程名称：sp_fulltext_column  
执行权限：db_ddladnmin角色成员 
作    用：指出一个全文索引表中的那些列假如或退出全文索引 

[wubaozhang]

谢谢
但是我开了服务，建了全文索引，也进行了填充，但是在用CONTAINS(title,'computer') ，语法检查通过
运行查询的时候提示：
全文操作运行失败，查询字句只包含被忽略的词．

．．．．．．．．．．．．．．
不明白，极度的不明白
那位高人顺手解释下啊

[nzperfect]

[Quote=引用 29 楼 wubaozhang 的回复:]
谢谢
但是我开了服务，建了全文索引，也进行了填充，但是在用CONTAINS(title,'computer') ，语法检查通过
运行查询的时候提示：
全文操作运行失败，查询字句只包含被忽略的词．

．．．．．．．．．．．．．．
不明白，极度的不明白
那位高人顺手解释下啊
[/Quote]


试两个：
1.用CONTAINS(title,'computer*')

2.(sql server 2k)把你windows\system32\Noise.chs 复制到sql server安装目录下一个名叫 FTDATA\Config 下面.

[kldxdybb]

学习一下

[tushadongjing]

學習

[wubaozhang]

在是在，可是在需求一定的情况下好象没什么能让人满意的结果
虽然大家都说什么什么不能使用索引，其实这些我也是知道的，但是没有可替代的方案又能叫我怎么办呢
其实这个ＳＱＬ我都处理一两个月了，除了表结构的变更加快速度以外，改来改去又改回去了，现在我只能相信不是查询ＳＱＬ的问题了，致力于修改索引了

本来以为沉了都打算结帖了，突然发现还有人感兴趣，再等一天吧．．．

[Garnett_KG]

[Quote=引用 14 楼 wubaozhang 的回复:]
实际上我已经改成了
select id from tableA
where c1 <>1 and c1 <>100 and (ct1 like '%keyStr%' or ct2 like '%keyStr%' or ct3 like '%keyStr%' )
order by lastUpdate desc
filter是LIKE查询，成本0% 这个没错
但是之前的bookmark lookup是什么操作操作呢？居然77%成本
怎么能减少这个趁本开支呢？
[/Quote]

帖子没太仔细看，我就谈一点个人的看法，对或不对，仅供参考吧。
bookmark lookup操作是搭配Index seek使用的,顾名思议，是书签查找。
你可以想像一本书的目录，目录中的条目就是索引内容，找到感兴趣的条目後，你会根据
条目後面的页码去翻相关的页，这个过程就叫Bookmark lookup.

c1上建的索引,选取性应该不高,c1<>1 AND c1<>100就筛选出50W的记录,这50W的记录
要根据索引上的键(Clustered Key 或是Row ID )去定位Table中的记录,这个定位的次数要做50W次，每一次的数据都有可能在不同的数据页中，这个代价很贵, 所以会占到77%的成本。


所以，建议是将IX_tableA_c1索引删掉，然后在ID上建立聚集索引。
做全表的Clustered Index 扫描，效果应该会好过在IX_tableA_C1上的索引查找。

[nzperfect]

这中多like的搞成全文索引试试呢

[wubaozhang]

分有所值,不加对不起大家啊
继续关注 呵呵

[wubaozhang]

[Quote=引用 30 楼 perfectaction 的回复:]
试两个：
1.用CONTAINS(title, 'computer* ')

2.(sql server 2k)把你windo…
[/Quote]


两个都试过了，ＳＱＬ　２Ｋ的环境，还是一样的报错

[wubaozhang]

诶~~兄弟,你是不是忘了说这个啊:
在SQL Server2000安装目录下的
\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config文件夹中找到文件noise.chs
并拷贝到\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config下，编辑noise.chs，清空文档，并输入@(其他的偏僻符号也可以），保存文件退出。
现在语法通过了,但是却搜不出结果来,重做填充,等会儿继续
昨天没时间,今天继续研究

[nzperfect]

你那破电脑怎么这么多毛病啊。。

[wubaozhang]

终于可以继续发贴了,破csdn居然限制我不能连续发三贴
顺便贴个contains的语法:

sql 中contains的使用例子，——CONTAINS 语法
我们通常在 WHERE 子句中使用 CONTAINS ，就象这样：SELECT * FROM table_name WHERE CONTAINS(fullText_column,'search contents')。
我们通过例子来学习，假设有表 students，其中的 address 是全文本检索的列。
1. 查询住址在北京的学生
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, 'beijing' )
remark: beijing是一个单词，要用单引号括起来。
2. 查询住址在河北省的学生
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, '"HEIBEI province"' )
remark: HEBEI province是一个词组，在单引号里还要用双引号括起来。
3. 查询住址在河北省或北京的学生
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, '"HEIBEI province" OR beijing' )
remark: 可以指定逻辑操作符(包括 AND ，AND NOT，OR )。
4. 查询有 '南京路' 字样的地址
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, 'nanjing NEAR road' )
remark: 上面的查询将返回包含 'nanjing road'，'nanjing east road'，'nanjing west road' 等字样的地址。
A NEAR B，就表示条件： A 靠近 B。
5. 查询以 '湖' 开头的地址
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, '"hu*"' )
remark: 上面的查询将返回包含 'hubei'，'hunan' 等字样的地址。
记住是 *，不是 %。
6. 类似加权的查询
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, 'ISABOUT (city weight (.8), county wright (.4))' )
remark: ISABOUT 是这种查询的关键字，weight 指定了一个介于 0~1之间的数，类似系数(我的理解)。表示不同条件有不同的侧重。
7. 单词的多态查询
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, 'FORMSOF (INFLECTIONAL,street)' )
remark: 查询将返回包含 'street'，'streets'等字样的地址。
对于动词将返回它的不同的时态，如：dry，将返回 dry，dried，drying 等等。
以上例子都使用英文，不使用中文是因为有的查询方式中文不支持，而且我的计算机是英文系统。
全文索引——CONTAINS 语法
我们通常在 WHERE 子句中使用 CONTAINS ，就象这样：SELECT * FROM table_name WHERE CONTAINS(fullText_column,'search contents')。

如果你在选定字段中查询一个匹配的直接使用
如：
从company中检查是否有test1的则：
select * from company
where contains(*,'test1')
如果要检查的是两个关键字，如是或地关系：
select * from company
where contains(*,'"北京" or "tttt"')
注意：关键字“北京”和“tttt”必须用"",包括起来，or代表两个关键字之间是"或"的关系
如果是与的关系：
select * from company
where contains(*,'"北京" and "tttt"')
如果是三个关键字则：
关键字前的那个or或者and 表示跟其他关键字的关系
and 表示两个词是靠近的




为节省帖子,报告当前进度:
能搜出来了,不过才一个,不知道全文索引执行到那里了,继续等待

[nzperfect]

等待楼主的测试结果。

[wubaozhang]

结果是:到现在还没填充完.....
标记是:只有停止填充,不能选全量和增量填充

还是只能 搜一条出来.......

[nzperfect]

全文索引，很占硬盘。
其实我也没用过全文索引，我一般都避免去like。
如果实在不行要like，那我就加一堆其它条件限制，让其它条件走索引，最后查出来的数据再like
这样就好多了

不知道你是2000还是2005,2005的话性能比2000提高很多，当然要你硬件要好些。

[wubaozhang]

你也没用过....
其实我平常也会先限制,但是这回这个需求属于极品了,不许改...

我们用的2000,暂时改不动了,硬件就不必提了 呵呵

关于全文索引还是研究下再另开贴吧,反正我可用分还够