开发者社区> noobwu> 正文

SqlServer(索引)--创建复合索引时,复合索引列顺序对查询的性能影响[转]

简介:
+关注继续查看

http://www.cnblogs.com/wy123/p/5604400.html
SQL Server创建复合索引时,复合索引列顺序对查询的性能影响

说说复合索引
写索引的博客太多了,一直不想动手写,有一下两个原因:
一是觉得有炒剩饭的嫌疑,有兄弟曾说:索引吗,只要在查询条件上建索引就行了,真的可以这么暴力吗?
二来觉得,索引是个非常大的话题,很难概括出所有的情况,你不整出点新意来,倒是有抄袭照搬的嫌疑
既然写了,就写一点稍微不一样的东西出来,
好了,废话打住,
/*
  20160814备注:今天发现一个类似的文章:http://www.cnblogs.com/fly_zj/archive/2012/08/11/2633629.html
  可以理解为:添加组合索引时,做相等运算字段应该放在最前面
  但这么说也不完全准确,应该说是,将选择性高的字段,放在最前面,通俗说就是,将最有效的过滤条件,方式复合索引的第一位
*/

搭建测试环境:
创建一张表,模拟实际业务中的一个表,往里面填入数据,时间字段上,相对按照时间均匀地填充,其他字段以GUID填充

Create table BusinessInfoTable
(
    BuniessCode1 varchar(50),
    BuniessCode2 varchar(50),
    BuniessCode3 varchar(50),
    BuniessCode4 varchar(50),
    BuniessStatus1 tinyint,
    BuniessStatus2 tinyint,
    BuniessDateTime1 Datetime,
    BuniessDateTime2 Datetime,
    OtherColumn1 varchar(50),
    OtherColumn2 varchar(50),
    OtherColumn3 varchar(50)
)

declare @i int=0
while @i<1000000
begin
    insert into BusinessInfoTable 
    values 
    (
        NEWID(),NEWID(),NEWID(),NEWID(),RAND()*100,RAND()*100,
        DATEADD(MI,@i,GETDATE()),DATEADD(MI,@i,GETDATE()),NEWID(),NEWID(),NEWID()
    )
    set @i=@i+1
end

现在有这么一个查询(实际上查询远比这个复杂,简化一点,不要刻意造环境)

DECLARE @startDate DATETIME;
DECLARE @endDate DATETIME;
SET @startDate = CONVERT(CHAR(10), GETDATE(), 120);
SET @endDate = DATEADD(dd, 7, @startDate);
SELECT OtherColumn2,
     BuniessStatus1,
     BuniessStatus2,
     BuniessDateTime1,
     BuniessDateTime2
FROM BusinessInfoTable
WHERE BuniessDateTime1 BETWEEN @startDate AND @endDate
    AND BuniessDateTime2 BETWEEN @startDate AND @endDate
    AND BuniessStatus1 = 55
    AND BuniessStatus2 = 66;

1

说明一点:
暂时不考虑聚集索引,毕竟一个表上只能有一个聚集索引,
别人也不是傻子,不会轻易去建聚集索引,聚集索引早被占用了
既然被占用了,原则是一般不去动别人现有的东西的,比如别人建了聚集索引,你给人家删了,根据自己的情况建聚集索引,这不是找*么

有经验的你一定考虑符合索引了,同时考虑到为避免Key Lookup导致的书签查找,我们把查询索要的OtherColumn2列include进来
比如这样

CREATE NONCLUSTERED INDEX IDX_1 ON BusinessInfoTable 
**(BuniessStatus1,BuniessStatus2,BuniessDateTime1,BuniessDateTime2)**
INCLUDE(OtherColumn2)

或者这样,只是索引前导列顺序不一样

CREATE NONCLUSTERED INDEX IDX_2 ON BusinessInfoTable
**(BuniessDateTime1,BuniessDateTime2,BuniessStatus1,BuniessStatus2)**
INCLUDE(OtherColumn2)

  当然可以随意调整四个列的顺序,我就不过多地做演示了,有兴趣的自己试
  这里的前导列的顺序并不会影响到索引的使用,查询的时候都是非聚集索引Seek,绝对的
  那么问题来了,完全一样的查询条件,结果一样,使用不同的索引,索引的区别仅仅是列顺序不一样,其代价一样吗,有区别吗?

  同样查询,使用不同索引的结果(分别是上面的IDX_1和IDX_2):
  下面看图说话

DECLARE @startDate DATETIME;
DECLARE @endDate DATETIME;
SET @startDate = CONVERT(CHAR(10), GETDATE(), 120);
SET @endDate = DATEADD(dd, 7, @startDate);
SELECT OtherColumn2,
       BuniessStatus1,
       BuniessStatus2,
       BuniessDateTime1,
       BuniessDateTime2
FROM BusinessInfoTable WITH(INDEX(IDX_1))
WHERE BuniessDateTime1 BETWEEN @startDate AND @endDate
      AND BuniessDateTime2 BETWEEN @startDate AND @endDate
      AND BuniessStatus1 = 55
      AND BuniessStatus2 = 66;

     SELECT OtherColumn2,
       BuniessStatus1,
       BuniessStatus2,
       BuniessDateTime1,
       BuniessDateTime2
FROM BusinessInfoTable WITH(INDEX(IDX_2))
WHERE BuniessDateTime1 BETWEEN @startDate AND @endDate
      AND BuniessDateTime2 BETWEEN @startDate AND @endDate
      AND BuniessStatus1 = 55
      AND BuniessStatus2 = 66;

2
看看IO情况

3

4

原因分析:
  看来是有点差别吧,好似乎这个差别还真不小。
  究竟原因在何?
  索引是以平衡树(B树)的方式存在的,复合索引的列的顺序决定了B树的信息的存储的顺序
  如果是以BuniessStatus1列为前导列,因为BuniessStatus1分布的范围(相对)较小,
  这样在查询的时候通过BuniessStatus1=55就可以过滤出来一个比较小的结果集,后面依次用其他条件过滤就相对较快了
  比如BuniessStatus1=55过滤出来符合条件的数据有5条,加上BuniessStatus2 BuniessDateTime1 BuniessDateTime2 这三个条件再过滤,出来一条数据。
  如果BuniessDateTime1 是索引的前导列,用BuniessDateTime1 between @startDate and @endDate 过滤 ,可能会有10000条数据,
  然后依次再用 BuniessDateTime2,BuniessStatus1, BuniessStatus2过滤,最后也只有一条符合条件的数据。
  差别就在于:一开始的过滤条件,决定了查询多少page初步确定满足条件的数据,再进一步的进行过滤
  如果最开始就相对精确地确定了满足查询条件的数据范围,后面可以通过相对较小的代价来最终确认出满足条件的数据
  如果最开始相对模糊地却确定了满足查询条件的数据范围,那么这个过程的代价就相对比较大,虽然后面通过每一个条件的过,结果是一样的
  当然这种索引的建立跟数据分布有关,但是,这里没有下结论说,复合索引一定要按照什么什么顺序来是最好的
  还是那句话:具体问题具体分析,避免经验主义,没有一刀切的手段可以解决所有的问题。

总结:
  本文通过一个简单的例子,分析了创建符合索引时,列的顺序对查询的影响,
  说明在创建索引的时候,不仅仅要考虑在哪些列上创建索引,同时也要注意到,索引列的顺序,是否会对查询产生影响。
  避免一说到索引,就是“在查询条件上建索引”的暴力做法。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
php连接sqlserver,php连接sql server数据库,php查询sqlserver数据库,php用sqlserver数据库
php连接sqlserver,php连接sql server数据库,php查询sqlserver数据库,php用sqlserver数据库
71 0
Python+数据库:Python连接sqlserver数据库,执行原生sql语句,并输出查询结果
Python+数据库:Python连接sqlserver数据库,执行原生sql语句,并输出查询结果
215 0
SQLServer 数据库字符集、版本号sql语句查询语法
SQLServer 数据库字符集、版本号sql语句查询语法
206 0
使用sqlserver 链接远程服务器进行查询
使用sqlserver 链接远程服务器进行查询复制代码 1 --创建链接服务器 2 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 3 exec sp_addlinkedsrvlogin 'ITSV ', 'fa...
2140 0
SQLServer中查询的数字列前面补0返回指定长度的字符串
原文:SQLServer中查询的数字列前面补0返回指定长度的字符串 SQLServer中查询的数字列前面补0返回指定长度的字符串:   如:   角本如下: /****** Script for SelectTopNRows command from SSMS ******/ SE...
2527 0
sql server 查询本年的每个月的数据
原文:sql server 查询本年的每个月的数据 一、以一行数据的形式,显示本年的12月的数据,本示例以2017年为例,根据CreateDate字段判断,计算总和,查询语句如下: select sum(case when datepart(month,CreateDate)=1 then ...
1747 0
sql server 查询本周、本月所有天数的数据
原文:sql server 查询本周、本月所有天数的数据 查询本月所有的天数: --本月所有的天数 select convert(varchar(10),dateadd(DAY,t2.number,t1.
1415 0
sql server 运维时CPU,内存,操作系统等信息查询(用sql语句)
原文:sql server 运维时CPU,内存,操作系统等信息查询(用sql语句) 我们只要用到数据库,一般会遇到数据库运维方面的事情,需要我们寻找原因,有很多是关乎处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统的,这时我们就需要查询他们的一些设置和内容,下面讲的就是如何查询它们的相关信息。
941 0
关于sqlserver字符类型查询条件区分大小写
原文:关于sqlserver字符类型查询条件区分大小写 在写sql的查询时 如下:  select * from Users where username='WangE'  select * from Users where username='wange' 上面两条查询语句的结果是一样的,这说明username的条件并没有区分大小写。
952 0
SqlServer中的UNION操作符在合并数据时去重的原理以及UNION运算符查询结果默认排序的问题
原文:SqlServer中的UNION操作符在合并数据时去重的原理以及UNION运算符查询结果默认排序的问题   本文出处:http://www.cnblogs.com/wy123/p/7884986.
1728 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
RowKey与索引设计:技巧与案例分析
立即下载
Phoenix 全局索引原理与实践
立即下载
事务、全局索引、透明分布式
立即下载