分享
 
 
 

一句T-SQL语句引发的思考 转帖

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

关于MS SQLSERVER索引优化问题:

有表Stress_test(id int, key char(2))

[$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:P4 2.6+512M+80G

[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Software:windows server 2003(Enterprise Edition)+Sqlserver 2000 +sp3a

[$nbsp][$nbsp]首先我们建立一个测试的数据,为使数据尽量的分布和随即,我们通过RAND()来随机产生2个随机数再组合成一个字符串,首先插入的数据是1,000,000条记录,然后在循环插入到58,000,000条记录。

[$nbsp][$nbsp][$nbsp]因为是随机产生的数据,所以假如你自己测试的数据集和我测试的会不一样,但对索引的优化和运行的效率是一样的。

[$nbsp][$nbsp][$nbsp]下面的“--//测试脚本”是产生测试数据的脚本,你可以根据需要修改 @maxgroup, @maxLoop的值,比如测试1百万的记录可以:

[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxgroup=1000

[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxLoop=1000

假如要测试5千万:

[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxgroup=5000

[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]Select @maxLoop=10000

所以假如你的SERVER或PC比较慢,请耐心等待.....,

(在我的PC上运行的速度是插入1百万条的时间是1.14m,插入5千八百万条的时间是19.41m,重新建立INDEX的时间是34.36m)

作为一般的开发人员很轻易就想到的语句:

[$nbsp][$nbsp][$nbsp]--语句1

[$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 a.id = b.id and a.id = c.id

[$nbsp][$nbsp][$nbsp]--语句2

[$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])=3)

[$nbsp][$nbsp][$nbsp]--语句5

[$nbsp][$nbsp][$nbsp][$nbsp]SELECT distinct a.[id] FROM stress_test AS a,stress_test AS b,stress_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]

但作为T-SQL的所谓“高手”可能会认为这种写法很“土”,也显得没有水平,所以会选择一些子查询和外连接的写法,按常理子查询的效率是比较高的:

[$nbsp][$nbsp][$nbsp]--语句3

[$nbsp][$nbsp][$nbsp][$nbsp]select distinct [id] from stress_test A where

[$nbsp][$nbsp][$nbsp][$nbsp]not exists (

[$nbsp][$nbsp][$nbsp][$nbsp]select 1 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 C.id=A.id and B.[k]=C.[key]

[$nbsp][$nbsp][$nbsp][$nbsp]where C.id is null)

[$nbsp][$nbsp][$nbsp]--语句4

[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp]select distinct a.id 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]b.id = a.id

[$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]c.kf1 = b.[key]

[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp][$nbsp])

[$nbsp][$nbsp][$nbsp][$nbsp][$nbsp])

我们先分析这几条语句(针对5千8百万条数据进行分析):

请大家要非凡留心Estimated row count的值。

语句1:从执行规划中我们可以看出,MSSQLSERVER选择的索引优化非常有规律,先通过CLUSTERED INDEX筛选出符合[KEY]='Az'条件的ID,然后进行HASH MATCH,在找出ID相等的;依次类推最终检索到符合所有条件的记录。中间的Estimated row count的值都不大。

语句2:从执行规划中我们可以看出,是先通过CLUSTERED INDEX筛选出符合 [key]='Az' or [key]='Bw' or [key]='Cv' 符合所有条件的ID,然后分组进行2次HASH MATCH 所有的ID。我们可以看出Estimated row count的值是越来越少,从最初的369,262到最后排序的只有402。

语句3:从执行规划中我们可以看是非常复杂的,是先通过3组 通过CONSTANT SCAN和NON-CLUSTERED INDEX检索出符合 A.ID=C.ID AND [key]='**' 的记录3组,然后分组进行外键匹配,再将3组的数据合并,排序,然后再和一个NON-CLUSTERED INDEX检索出的记录集进行外键匹配,我们可以看出MSSQLSERVER会对所有的记录(5千万条)记录进行分组,Estimated row count的值是:58,720,000,所以这句T-SQL的瓶颈是对5千万条记录进行分组。

语句4:从执行规划中我们可以看和语句3有相似之处,都要对所有的记录(5千万条)记录进行分组,所以这是检索的瓶颈,而且使用的索引都是NON-CLUSTERED INDEX。

语句5:从执行规划中我们可以看出,先通过CLUSTERED INDEX检索出符合[Key]='Az'的记录集,然后进行HASH MATCH和SORTS,因为数量少所以是非常会的,在和通过NON-CLUSTERED INDEX检索[KEY]='Bw'的记录进行INNER JOIN,在和通过CLUSTERED INDEX检索[KEY]='Cv'的记录进行合并,最后是对4百万条数据进行分组检索,假如是6列,我们可以看出Estimated row count的值是递增,越来越大,最后的分组检索的Estimated row count的值是3.46E+15,这已经形成巨大的瓶颈。

我们可以先测试一下小的数据量(50000条);

大家可以下面测试脚本的:

[$nbsp][$nbsp][$nbsp]Select @maxgroup=500

[$nbsp][$nbsp][$nbsp]Select @maxLoop=100

----------------------------------------------------------------------

------------------语句 1----语句 2----语句 3----语句 4----语句 5----

5万(3列) 5ms 19ms 37ms 59ms 0ms

5万(6列) 1ms 26ms 36ms 36ms 1ms

从测试的的数据来看,语句5的效率是最高的,几乎没有花费时间,而语句2的效率只能说是一般。假如测试到这里就结束了,我们可以毫不犹豫的选择语句 5 :-(,继续进行下面的测试.....

我们测试百万条以上的记录:

1.先对1百万条记录进行测试(选取3列)

2.先对1百万条记录进行测试(选取6列)

3.对5千万条数据测试(选取3列)

4.对5千万条数据测试(选取6列)

统计表1:

----------------------------------------------------------------------

------------------语句 1----语句 2----语句 3----语句 4----语句 5----

1百万(3列) 0.77% 0.41% 49.30% 48.99% 0.52%

1百万(6列) 1.61% 0.81% 48.99% 47.44% 1.14%

5千万(3列) 0.14% 0.18% 48.88% 48.86% 1.93%

5千万(6列) 0.00% 0.00% 0.00% 0.00% 100.00%

统计表2:

----------------------------------------------------------------------

------------------语句 1----语句 2----语句 3----语句 4----语句 5----

1百万(3列) 9ms 22ms 723ms 753ms 4ms

1百万(6列) 15ms 38ms 764ms 773ms 11ms

5千万(3列) 575ms 262ms 110117ms 110601ms 12533ms

5千万(6列) 1070ms 576ms 107988ms 109704ms 10m以上

测试总结:(我们可以比较关注:语句 2和语句 5)

1.在1百万条记录的情况下,语句 5是最快的,但在5千万条记录下是最慢的。这说明INDEX的优化一定的情况下,数据量不同,检索的效率也是不同的。我们

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有