西北电力建设第一工程公司网站,广州网站建设oem,rust做网站,做自己的网站不是免费的今天闲来学习了一下SQL性能优化方面的知识#xff0c;有以下学习收获#xff0c;欢迎大家指点。 测试环境#xff1a;90W#xff0c;单条记录约3KB#xff0c;数据库#xff1a;MSSQL2005 测试前清除缓存 DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERS 一、翻页性能测试 1、T…今天闲来学习了一下SQL性能优化方面的知识有以下学习收获欢迎大家指点。 测试环境90W单条记录约3KB数据库MSSQL2005 测试前清除缓存 DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERS 一、翻页性能测试 1、Topselect top 10 * from message where id not in (select top 20 id frommessage where classid77 order by id desc ) and classid77 order by id desc2、Max/Topselect top 10 * from message where id (select min(id) from messagewhere id in(select top 20 id from message where classid77 order by iddesc) ) and classid77 order by id desc3、row_numberselect top 10 * from (select row_number()over(order by id desc) rownumber,*from message where classid77)a where classid77 and rownumber20 MsSql翻页性能测试 ID列索引 Top Max/Top row_number() 无索引 cpu reads duration 0 893 65 cpu reads duration 0 590 70 cpu reads duration 0 512 67 聚焦索引 cpu reads duration 0 37 66 cpu reads duration 0 98 64 cpu reads duration 0 28 67 非聚焦索引 cpu reads duration 0 895 63 cpu reads duration 0 592 66 cpu reads duration 0 514 66 结论 1从以上测试结果可以看出不论是否索引排序字段也不管是何种索引row_number都能得到最高的性能其次Max/Top的方式测试性能也不错。 2在使用非聚焦索引的情况下性能并无任何提示甚至要慢于无索引的情况可能是因为SQL先要去查找索引表然后根据索引结果再去查找实体表在这过程浪费了资源。 3聚焦索引也的正确应用才能发挥其该有的优势啊 综合结果row_number max/top top 二、in、or、union关键字性能测试 介于网上有很多关于in/or/union等关键字的性能讨论本人也小试了一把测试结果如下。 1、in select * from video where id in(100,101,102,103,104,105,106,107,108,109) 2、union select * from video where id 100 union all select * from video where id 101 union all select * from video where id 102 union all select * from video where id 103 union all select * from video where id 104 union all select * from video where id 105 union all select * from video where id 106 union all select * from video where id 107 union all select * from video where id 108 union all select * from video where id 109 3、or select * from video where id100 or id101 or id102 or id103or id104 or id105 or id106 or id107 or id108 or id109 in PK or PK union ID列索引 in union or 无索引 cpu reads duration 0 37 54 cpu reads duration 0 58 104 cpu reads duration 0 41 56 聚焦索引 cpu reads duration 0 44 54 cpu reads duration 0 54 58 cpu reads duration 0 40 54 非聚焦索引 cpu reads duration 0 43 53 cpu reads duration 16 61 62 cpu reads duration 0 43 54 结论 1 网上很多资料说union的性能要高于in/or但从我这测试的结果来看不论是有无索引union的性能都是最低的不知是何原因 2 网上流传mssql会自己把in解析成or查询从这份测试结果来看貌似不假 3 虽然in/or会引起全表扫描但别无选择的情况下也是是能胜任很多工作的。转载于:https://www.cnblogs.com/haiyabtx/archive/2012/11/22/2783223.html
