最近在开发站长帮手网（www.links.cn）的百度权重查询工具，数据已达亿万级别，主表为关键词主表（包含百度指数，百度收录等字段），字表为网站排名表（1-100）的排名。根据设计惯例，查询的时候主子表通过关键词字段关联查询，查询语句如下：

 

select top 1000 a.word,a.queryurl,a.irank,a.title,a.baiduurl,a.itraffic1,a.itraffic2,b.ibaiduindex from  zibiao a

 with(nolock)

inner join zhubiao b with(nolock) on a.word=b.word

where a.queryurl='http://www.links.cn' 

 

order by b.ibaiduindex desc,a.irank

 

发现速度很慢，快的时候瞬间，慢的时候长达几分钟。分析sql server的查询执行计划如下：

 

 

 

分析这个执行计划图，主要资源开销是在主表的聚集索引查找，应该是子表根据网址找到关键词后，再到主表中查找关键词对应的指数的这一步比较耗时。

 

因这2个表的更新频率非常高，以为是更新频率太快，导致索引效率降低，于是想到用数据库读写分离的方案，专门拿了一台备用服务器，通过发布订阅的方法 ，将这两张表发布到备用服务器上，专门在备用服务器上做查询，发现速度并没有得到提升。

 

在csdn上发了一个讨论帖，网友讨论很热烈，sql server的很多版主都有参与讨论，给出的方案也很多。有人说是IO读写瓶颈，于是升级了下服务器，硬盘采用raid10的固态硬盘，内存更是升级到了128G，不过效果仍旧不明显，同样的语句，慢的时候还是需要几十秒。

 

有网友给出的方案，是建议把主表字段放到子表冗余，以减少关联的资源消耗，再结合以上的执行计划图，确实瓶颈是在主表的的聚集索引查找上，于是单独执行了下子表的irank排序语句：

 

select top 1000 a.word,a.queryurl,a.irank,a.title,a.baiduurl,a.itraffic1,a.itraffic2 from  zibiao a

 with(nolock)

where a.queryurl='http://www.links.cn' 

 

order by a.irank

 

发现执行速度非常快，几乎秒开了，当然irank字段上有建索引的。

 

测试成功，于是将主表的ibaiduindex字段放到子表冗余，并且建立好索引（这里的索引建立是有技巧的）。执行语句：

 

select top 1000 a.word,a.queryurl,a.irank,a.title,a.baiduurl,a.itraffic1,a.itraffic2,a.ibaiduindex from  zibiao a

 with(nolock)

where a.queryurl='http://www.links.cn' 

 

order by a.ibaiduindex desc,a.irank

 

查询瞬间出来结果。执行计划如下：

 

 

 

这里拿以上这个sql语句来分析，我们该如何建立索引。索引建立包括字段的先后顺序，字段的排序方法，include项都很重要，任何一个弄错，都不能达到好的优化效果。

 

1.我们肯定需要建立一个组合索引。那么应该组合字段用哪几个呢？这里我的组合字段是：queryurl（这个字段第一， 因为我们最先是根据这个字段进行筛选的）、ibaiduindex、irank

 

2.这里要注意下，因为我的固定排序就是ibaiduindex desc，irank asc，所以字段的排列顺序应该是：queryurl,ibaiduindex,irank。同时排序的类别是：ibaiduindex desc  ,irank asc。这里我一开始没注意，ibaiduindex的排序是asc，结果发现执行上面那个SQL语句仍然需要2秒。

 

3.索引包含项，也很重要，如果不用包含项，索引查找到主键后， 还要根据主键去查找其他字段。所以我们需要设置索引包含性列，把除掉索引字段中剩余的其他字段都加进去。

 

优化后的效果就如上面的执行计划图所示，一次非聚集索引查找，就找到我们的数据，而且都不需要排序耗时，因为我们的索引已经按照顺序排列好了。当然，这里要说明下，索引包含确实好用，但是代价就是磁盘的空间。加了索引包含项，数据库空间增加了几十个G。

 

 

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

作者：caoshangfei 

来源：CSDN 

原文：https://blog.csdn.net/caoshangfei/article/details/8761301 

版权声明：本文为博主原创文章，转载请附上博文链接！