我问一个问题：关于索引的问题。

[114]

我的数据库表a有10几个字段（id (primary key),col1,col2 …………）；
28多万条数据；
经常用于检索的字段为：col1 ,col2；
其中col1 检索方式：col1 like '%检索关键字%'
col2 检索方式：col2='检索关键字'； （常用检索方法）

由于检索速度慢，我加了这样的索引：在col2上建了族索引；
我测试加索引前后的检索速度：
加索引前:select * from a order by col2 (在查询分析器中执行时间：2分14秒)
加索引后:select * from a order by col2 (在查询分析器中执行时间：2分9秒)
这个时间差为什么这么小？

我还有一个问题：如果不建立以上的索引 而在id上建unique 索引用 这样如何了？

最后，a表上建立什么样的索引效果会最好？！！！！！

[txlicenhe]

当然在col2上建索引较好。在col1上也可以建索引。

[pengdali]

测试加索引,要用where测。


测试过程：

环境：
PIII900 128M
win2000+sql server2000企业版
table name :t_data
CLUSTERED PRIMARY KEY :id
Rows:441000

语句1：
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 60000 id from t_data order by id asc)
order by id desc)
order by id asc

时间：390ms

语句2：
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 100000 id from t_data order by id asc)
order by id desc)
order by id asc

时间：4s

语句3：
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 150000 id from t_data order by id asc)
order by id desc)
order by id asc

时间：5s

语句4：
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 200000 id from t_data order by id asc)
order by id desc)
order by id asc

时间：6s

语句5：
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 250000 id from t_data order by id asc)
order by id desc)
order by id asc

时间：过了15m没有出来，中断

语句6：
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 300000 id from t_data order by id asc)
order by id desc)
order by id asc

时间：过了20m没有出来，中断

语句7：
select top 60000 id into #a from t_data order by id asc

create index ind_a_base_id on #a(id)

select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc

时间：20s

语句8：
select top 100000 id into #a from t_data order by id asc

create index ind_a_base_id on #a(id)

select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc

时间：21s

语句9：
select top 150000 id into #a from t_data order by id asc

create index ind_a_base_id on #a(id)

select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc

时间：23s

语句10：
select top 200000 id into #a from t_data order by id asc

create index ind_a_base_id on #a(id)

select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc

时间：24s

语句11：
select top 250000 id into #a from t_data order by id asc

create index ind_a_base_id on #a(id)

select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc

时间：26s

语句12：
select top 300000 id into #a from t_data order by id asc

create index ind_a_base_id on #a(id)

select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc

时间：28s

语句13：
select top 400000 id into #a from t_data order by id asc

create index ind_a_base_id on #a(id)

select * from t_data where
id in (select top 10 id from #a order by id desc)
order by id asc

时间：29s

语句14：
select * from t_data where
id in
(select top 10 id from t_data where id in
(select top 250000 id from t_data order by id asc)
order by id desc)
order by id asc

时间：过了25m没有出来，还没有中断

[yujohny]

这样的查询，col2肯定要建索引
至于其它字段要不要建，关键看你自己实际需要

[dlkfth]

在 Microsoft® SQL Server ™ 2000 数据库内，对象作为 8 KB 页的集合存储。本主题描述表和索引页的组织方式。

SQL Server 2000 支持视图上的索引。视图上第一个允许的索引是聚集索引。在视图上执行 CREATE INDEX 语句时，视图的结果集被具体化，并且存储在与有聚集索引的表具有相同结构的数据库中。存储的结果集与下面的语句所产生的结果集相同：

SELECT * FROM ViewName

每个表或索引视图的数据行存储在 8 KB 数据页集合中。每个数据页都有一个 96 字节的页头，其中包含象拥有该页的表的标识符 (ID) 这样的系统信息。如果页链接在列表中，则页头还包含指向下一页及前面用过的页的指针。在页尾有行偏移表。数据行填充页的剩余部分。



数据页的组织方式
SQL Server 2000 中的表使用下面两种方法组织其数据页。

聚集表是有聚集索引的表。
基于聚集索引键按顺序存储数据行。索引按 B 树索引结构实现，B 树索引结构支持基于聚集索引键值对行进行快速检索。每级索引中的页（包括叶级中的数据页）链接在双向链接列表中，但使用键值在各级间导航。

堆集是没有聚集索引的表。
不按任何特殊顺序存储数据行，数据页序列也没有任何特殊顺序。数据页不在链表内链接。

索引视图与聚集索引表具有相同的存储结构。

SQL Server 在每个表或索引视图上还支持多达 249 个非聚集索引。非聚集索引有一个与聚集索引中相似的 B 树索引结构。不同的是非聚集索引对数据行的顺序不起作用。聚集索引表和索引视图基于聚集索引键按顺序存储数据行。如果为表定义了非聚集索引，对堆集的数据页集合没有影响。除非定义了聚集索引，否则数据页保留在堆集内。

包含 text、ntext 和 image 数据的页作为每个表的单个单元进行管理。一个表的所有 text、ntext 和 image 数据存储在一个页集合内。

sysindexes 表内的页指针定位表、索引和索引视图的所有页集合。每个表和索引视图有一个数据页集合，以及其它一些实现为这个表或视图定义的各个索引的页集合。

每个表、索引和索引视图在 sysindexes 内有一行，由对象标识符 (id) 列和索引标识符 (indid) 列的组合唯一标识。IAM 页链管理分配给表、索引和索引视图的页。sysindexes.FirstIAM 列指向 IAM 页链的 IAM 首页，IAM 页链管理分配给表、索引或索引视图的空间。

每个表在 sysindexes 内有一个行集：

一个堆集在 sysindexes 内有一行，其 indid = 0。
FirstIAM 列指向表的数据页集合的 IAM 链。服务器使用 IAM 页查找数据页集合内的页，因为这些页不链接在一起。

某个表或视图上的一个聚集索引在 sysindexes 内有一行，其 indid = 1。
root 列指向聚集索引 B 树的顶端。服务器使用索引 B 树查找数据页。

为某个表或视图创建的每个非聚集索引在 sysindexes 内有一行。
每个非聚集索引的行内的 indid 值都从 2 到 250。root 列指向非聚集索引 B 树的顶端。

对于每个至少有一个 text、ntext 或 image 列的表，在 sysindexes 内也有一行，其 indid = 255。
FirstIAM 列指向管理 text、ntext 和 image 页的 IAM 页链。

[aierong]

id是主键,系统已经自动在id上建立索引啦


你最好在col2上面再建立一个索引

我个人意见,一个表,索引不要太多,2,3个就够啦,根据你的使用情况

[aierong]

对具有复杂访问模式的大型架构，最简单的设计索引的方法是使用索引优化向导。

[LoveSQL]

应该在col2上建索引。

[114]

是在col2上建族索引？·！

[114]

补充：col2有重复值，但是不是很多！
到底在col2上建镞索引还是非镞索引啊！！！！！！

[txlicenhe]

若有重复值，你想建聚族索引也建不了啊，只能建普通索引。

[aierong]

在唯一列、非空列或 IDENTITY 列上创建聚集索引可以获得性能收益


在查询经常用到的列上创建非聚集索引,这可以最大程度地利用隐蔽查询。

当然具体情况具体分析

使用索引优化向导分析查询并获得索引建议,你可以试试








.
|
\ * ./
. * * * .
-=* POP! *=-
. .* * * .
/ * .\
|
.
.
|
\ * ./
. * * * .
-=* POP! *=-
. .* * * .
/ * .\
|
.
.
|
\ * ./
. * * * .
-=* POP! *=-
. .* * * .
/ * .\
|
.
先放三个礼花,看看,庆祝节日!!!
/*
祝福你好运
明天过节
*/

[pengdali]

如果id是主键，建议对你的两个列建立非簇索引。你不要用select * 来测试速度，你应该用where，因为where才会真正体现索引的优势。你也可以通过：把你的代码放到查询分析器，按下ctrl+L 观察执行计划，比较各种方案的异同，取出最优的。