SQL Server非聚集索引能给我们带来什么?

数据库 SQL Server 数据库运维
最近在做的一个项目,页面访问的时候很慢(大概几秒钟的样子),然后用日志记录的方式,来排查这个问题,最后发现是Entity Framework初始化的一个坑(大概要花 6-7 秒),详见:《来,给Entity Framework热热身》,但是除了这个问题,还发现当一些用户数据量很大的时候,访问也是有些慢,这个就不是 Entity Framework 的问题了(因为初始化已完成)。下面是笔者解决问题的过程。

用 Sql Server Profiler 来跟踪页面访问的时 SQL 的执行情况,因为应用程序很简单,页面加载的时候,跟踪检测到三个 SQL 执行,看了下也没什么问题(两个获取数量,一个获取列表),数量获取的 SQL,这个应该执行会很快,所以把分析焦点放在了那个获取列表的 SQL 上,因为 SQL 没什么问题,那应该是关于这条 SQL 建的索引有问题。注:上面所说项目中大概有 100 万的数据。

[[118947]]

关于数据库中的索引概念,记得在很早之前整理了一篇博文《T-Sql(八)字段索引和数据加密》,现在来看,写的真是一坨屎,概念讲的再多没个毛用,关键在于对实际应用中产生问题的分析。在研究这个问题之前,搜了一些相关资料,主要来自园中的几位 SQL Server 大神(CareySon、桦仔、听风吹雨等),稍微看了下,关于索引,主要是一些数据库专业术语,看的不是很明白,作为程序员,我们知道索引分为聚集性索引和非聚集性索引,聚集性索引一般为主键(也可以不是),在创建表的时候会自动创建,针对上面我那个应用查询问题,查询条件是一些非主键字段,所以这边探讨下非聚集性索引。

我不会说一些数据库概念,所以只能用做一些实践来理解概念的意义,以下应用场景中的用例是虚拟出来的,只是作为个人研究使用。

程序员应该有刨根问底的怪癖,虽然这是个数据库问题。

应用场景

有一个 Product 表,字段如下:

数据添加脚本:

  1. begin tran 
  2. declare @index int 
  3. set @index=0 
  4. while(@index<1000000
  5.   begin 
  6.    insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])  
  7.    values('我是测试标题1','我是测试备注1我是测试备注1我是测试备注1我是测试备注1我是测试备注1我是测试备注1',1,GETDATE(),0) 
  8.    insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])  
  9.    values('我是测试标题2','我是测试备注2我是测试备注2我是测试备注2我是测试备注2我是测试备注2我是测试备注2',1,GETDATE(),1) 
  10.    insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])  
  11.    values('我是测试标题3','我是测试备注3',3,GETDATE(),1) 
  12.    insert into [dbo].[Product]([Name],Remarks,ProviderID,[Time],[State])  
  13.    values('我是测试标题4','我是测试备注4我是测试备注4我是测试备注4我是测试备注4我是测试备注4我是测试备注4',4,GETDATE(),1) 
  14.    set @index=@index+1 
  15.   end 
  16. commit 

Product 表中插入了四百万的数据,为了接近我们现实生产环境,所以对数据进行了不同插入。

一般应用环境查询,有时候我们会针对一个字段进行 where 查询,有时候也会 and 另一个字段进行查询,这个时候,关于这两个字段的索引怎么建?还是不需要建?是分别建两个?还是建一个组合的?其实说真的,可能看到这的数据库大神会莞尔一笑,但是作为程序员,这些我真不知道,搜索的资料中也并没有对这些鸡毛蒜皮进行的说明,没办法,只能自己瞎折腾下。我们下面要做是 ProviderID 和 State 的查询操作,有分别查询,也有组合查询,然后我们再对 Product 表建立这两个字段的索引,看看有什么不同之处?还有就是针对不同的索引方式,查询又会有什么不同?我们睁大眼睛来看一下。

问题分析

我再对上面的分析进行说明下,首先,查询主要为2种:

  1. where ProviderID=?

  2. where ProviderID=? and State=?

非聚集性索引的创建主要为3种:

  1. 不创建索引

  2. ProviderID 字段索引

  3. ProviderID 和 State 字段索引

针对这个应用场景和上面的分析,会得出 3*2 六种结果,其实我最想知道的是下面的第三种,即创建一个组合字段索引,对单个字段的查询会不会有影响?还有就是反过来,单个字段的索引创建,对组合字段查询会不会有影响?当然试过了才知道,看一下执行结果。

执行结果

测试脚本:

  1. declare @begin_date datetime 
  2. declare @end_date datetime 
  3. select @begin_date = getdate() 
  4. select * from [dbo].[Product] where ... 
  5. select @end_date = getdate() 
  6. select datediff(ms,@begin_date,@end_date) as '用时/毫秒' 

为了接近测试结果,每次语句执行三次,然后再取平均值,截图太麻烦了,这边就直接贴下执行结果。

不创建索引

  1. where ProviderID=1(二百万数据)
    执行结果:13806毫秒,13380毫秒,12730毫秒
    平均结果:13305毫秒

  2. where ProviderID=1 and State=1(一百万数据)
    执行结果:6556毫秒,6613毫秒,6706毫秒
    平均结果:6625毫秒

创建索引字段 ProviderID

  1. where ProviderID=1
    执行结果:13986毫秒,13810毫秒,15853毫秒
    平均结果:14549毫秒

  2. where ProviderID=1 and State=1
    执行结果:7153毫秒,7190毫秒,13950毫秒
    平均结果:7122毫秒

创建索引字段 ProviderID 和 State

  1. where ProviderID=1
    执行结果:13840毫秒,14163毫秒,15853毫秒
    平均结果:14618毫秒

  2. where ProviderID=1 and State=1
    执行结果:7033毫秒,7220毫秒,7023毫秒
    平均结果:7152毫秒

结果分析

虽然测试的有些不完整,但是看到结果,哥有些凌乱了(建了索引,性能反而会降低?),难道是我插入的数据有问题?还是创建索引有问题?还是我人品有问题???坐等数据库大神指教。

原文链接:http://www.cnblogs.com/xishuai/p/3922964.html

 

责任编辑:彭凡 来源: 博客园
相关推荐

2021-03-09 10:11:26

区块链技术数据

2010-09-28 10:17:53

WiFi

2010-07-19 16:26:05

SQL Server非

2022-06-05 15:02:57

边缘计算云计算

2011-06-29 15:29:59

关键词

2010-07-20 12:46:23

SQL Server聚

2015-06-17 12:25:29

云计算

2013-01-08 14:11:14

JavaJDK8lambda

2013-01-09 09:38:34

Java 8JDK8新版Java

2011-04-22 14:45:45

SQL索引

2013-09-29 16:31:22

浏览器Chrome

2010-07-07 11:20:02

SQL Server聚

2020-04-08 17:34:37

5G4G网络

2021-03-17 14:22:09

VRAR虚拟现实技术

2022-11-28 07:25:52

MySQL聚集索引

2010-07-20 13:20:26

SQL Server聚

2010-07-19 16:17:41

SQL Server聚

2016-12-20 15:42:47

AR教育

2011-03-30 11:28:31

SQL Server聚集索引

2010-05-21 15:51:32

点赞
收藏

51CTO技术栈公众号