关于MS SQLSERVER索引优化问题:
有表Stress_test(id int key char())
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]id 上有普通索引;
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]key 上有簇索引;
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]id 有有限量的重复;
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]key 有无限量的重复;
现在我需要按逻辑与查询表中key=Az AND key=Bw AND key=Cv 的id
求教高手最有效的查询语句
测试环境
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Hardware:P +M+G
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Software:windows server (Enterprise Edition)+Sqlserver +spa
[$nbsp][$nbsp]首先我们建立一个测试的数据为使数据尽量的分布和随即我们通过RAND()来随机产生个随机数再组合成一个字符串首先插入的数据是条记录然后在循环插入到条记录
[$nbsp][$nbsp][$nbsp]因为是随机产生的数据所以如果你自己测试的数据集和我测试的会不一样但对索引的优化和运行的效率是一样的
[$nbsp][$nbsp][$nbsp]下面的//测试脚本是产生测试数据的脚本你可以根据需要修改 @maxgroup @maxLoop的值比如测试百万的记录可以:
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxgroup=
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxLoop=
如果要测试千万
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxgroup=
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxLoop=
所以如果你的SERVER或PC比较慢请耐心等待
（在我的PC上运行的速度是插入百万条的时间是m插入千八百万条的时间是m重新建立INDEX的时间是m)
作为一般的开发人员很容易就想到的语句
[$nbsp][$nbsp][$nbsp]语句
[$nbsp][$nbsp][$nbsp][$nbsp]select a[id] from
[$nbsp][$nbsp][$nbsp][$nbsp](select distinct [id] from stress_test where [key] = Az) a
[$nbsp][$nbsp][$nbsp][$nbsp](select distinct [id] from stress_test where [key] = Bw) b
[$nbsp][$nbsp][$nbsp][$nbsp](select distinct [id] from stress_test where [key] = Cv) c
[$nbsp][$nbsp][$nbsp][$nbsp]where aid = bid and aid = cid
[$nbsp][$nbsp][$nbsp]语句
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]select [id]
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]from stress_test
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]where [key]=Az or [key]=Bw or [key]=Cv
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]group by id having(count(distinct [key])=)
[$nbsp][$nbsp][$nbsp]语句
[$nbsp][$nbsp][$nbsp][$nbsp]SELECT distinct a[id] FROM stress_test AS astress_test AS bstress_test AS c
[$nbsp][$nbsp][$nbsp][$nbsp]WHERE a[key]=Az AND b[key]=Bw AND c[key]=Cv
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]AND a[id]=b[id] AND a[id]=c[id]
但作为TSQL的所谓高手可能会认为这种写法很土也显得没有水平所以会选择一些子查询和外连接的写法按常理子查询的效率是比较高的
[$nbsp][$nbsp][$nbsp]语句
[$nbsp][$nbsp][$nbsp][$nbsp]select distinct [id] from stress_test A where
[$nbsp][$nbsp][$nbsp][$nbsp]not exists (
[$nbsp][$nbsp][$nbsp][$nbsp]select from
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp](select Az as k union all select Bw union all select Cv) B
[$nbsp][$nbsp][$nbsp][$nbsp]left join stress_test C on Cid=Aid and B[k]=C[key]
[$nbsp][$nbsp][$nbsp][$nbsp]where Cid is null)
[$nbsp][$nbsp][$nbsp]语句
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]select distinct aid from stress_test a
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]where not exists
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]( select * from keytb c
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]where not exists
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]( select * from stress_test b
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]where
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]bid = aid
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]and
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]ckf = b[key]
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp])
[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp])
我们先分析这几条语句（针对千百万条数据进行分析）
请大家要特别留心Estimated row count的值
语句:从执行规划中我们可以看出MSSQLSERVER选择的索引优化非常有规律先通过CLUSTERED INDEX筛选出符合[KEY]=Az条件的ID然后进行HASH MATCH在找出ID相等的依次类推最终检索到符合所有条件的记录中间的Estimated row count的值都不大
语句:从执行规划中我们可以看出是先通过CLUSTERED INDEX筛选出符合 [key]=Az or [key]=Bw or [key]=Cv 符合所有条件的ID然后分组进行次HASH MATCH 所有的ID我们可以看出Estimated row count的值是越来越少从最初的到最后排序的只有
语句:从执行规划中我们可以看是非常复杂的是先通过组 通过CONSTANT SCAN和NONCLUSTERED INDEX检索出符合 AID=CID AND [key]=** 的记录组然后分组进行外键匹配再将组的数据合并排序然后再和一个NONCLUSTERED INDEX检索出的记录集进行外键匹配我们可以看出MSSQLSERVER会对所有的记录（千万条）记录进行分组Estimated row count的值是所以这句TSQL的瓶颈是对千万条记录进行分组
语句:从执行规划中我们可以看和语句有相似之处都要对所有的记录（千万条）记录进行分组所以这是检索的瓶颈而且使用的索引都是NONCLUSTERED INDEX
语句从执行规划中我们可以看出先通过CLUSTERED INDEX检索出符合[Key]=Az的记录集然后进行HASH MATCH和SORTS因为数量少所以是非常会的在和通过NONCLUSTERED INDEX检索[KEY]=Bw的记录进行INNER JOIN在和通过CLUSTERED INDEX检索[KEY]=Cv的记录进行合并最后是对百万条数据进行分组检索如果是列我们可以看出Estimated row count的值是递增越来越大最后的分组检索的Estimated row count的值是E+这已经形成巨大的瓶颈
我们可以先测试一下小的数据量（条）
大家可以下面测试脚本的
[$nbsp][$nbsp][$nbsp]Select @maxgroup=
[$nbsp][$nbsp][$nbsp]Select @maxLoop=
|语句 语句 语句 语句 语句 |
| 万（列） ms ms ms ms ms
| 万（列） ms ms ms ms ms
从测试的的数据来看语句的效率是最高的几乎没有花费时间而语句的效率只能说是一般如果测试到这里就结束了我们可以毫不犹豫的选择语句 :(继续进行下面的测试
我们测试百万条以上的记录
先对百万条记录进行测试（选取列）
先对百万条记录进行测试（选取列）
对千万条数据测试（选取列）
对千万条数据测试（选取列）
统计表
|语句 语句 语句 语句 语句 |
| 百万（列） % % % % %
| 百万（列） % % % % %
| 千万（列） % % % % %
| 千万（列） % % % % %
统计表
|语句 语句 语句 语句 语句 |
| 百万（列） ms ms ms ms ms
| 百万（列） ms ms ms ms ms
| 千万（列） ms ms ms ms ms
| 千万（列） ms ms ms ms m以上
测试总结（我们可以比较关注语句 和语句 ）
在百万条记录的情况下语句 是最快的但在千万条记录下是最慢的这说明INDEX的优化一定的情况下数据量不同检索的效率也是不同的我们
