SQL Server设定过滤条件提高索引效率

数据库 SQL Server
以下的文章主要向大家爱描述的是SQL Server设定过滤条件对索引进行效率的提高,以下就是文章的主要内容的描述。

主要是想大家讲述的是通过SQL Server设定过滤条件来对索引进行提高的实际操作方案,这种独特的数据库优化方法也是具有自己比较雨中不同的一面,下面大家一起来学习一下。

低碳指数:在这里为了方便计算和直观,我们以Intel至强X7500处理器的TDP为标准计算能耗(TDP=130W/h=2.167W/m=0.036W/s)。另外根据中国林业局的数据,一棵树一天吸收二氧化碳量为5.023kg,每一度电产生0.785公斤二氧化碳。

如果按照本文方法优化后数据库执行时间由27秒缩减到14秒,也就是单位时间少47.8%的能量消耗。那么在一天里将减少1.486kw电能消耗,约合1.167kg二氧化碳排放,按我们的计算是一天减少0.232棵树二氧化碳吸收量。

51CTO数据库频道向您推荐《数据库性能优化与调试》和《SQL Server 2008/2005全解》专题,以便于您更好的理解本文。

SQL Server设定过滤条件提高索引效率

优秀的索引是SQL Server数据库性能的关键,然而高效的索引都是经过精心设计而成的。众所周知,主键是储存数据对象的唯一标识,如果数据表中没有聚簇索引,为了维护主键的唯一性,SQL Server数据库在默认情况下将为主键创建聚簇索引(Clustered index),除非用户特别指定将索引创建为非聚簇索引(Non-clustered index)。

毫无疑问,我们应当为频繁访问的数据创建聚簇索引,当然频繁访问的字段应当经过详细的分析和慎重选择,并且索引值应当尽可能短。提到创建索引,大家往往首先想到主键,但是主键的数据并不一定被频繁访问,而且很多时候为了保证主键的唯一性,主键的数值往往不是很短。

比如我们经常会选择全局唯一标识符(GUID)类型作为主键的数据类型,唯一标识符的长度一般是16个字节,就长度而言,这种数据类型并不是最理想的聚簇索引选项,在这种情况下,可以为主键创建非聚簇索引,因为主键值在WHERE语句中用来查询特定的记录是非常高效的,创建非聚簇索引可以将查询的效率再上一个台阶。如果您选择了整型作为主键的数据类型,那就可以考虑将为主键生成聚簇索引。

SQL Server 2008为我们提供了另外一种索引——SQL Server设定过滤条件索引(Filtered index),一个设定过滤条件索引是一个特殊的非聚簇索引,它是某些字段的特定子集。换句话说,设定过滤条件索引是基于一部分选定的字段生成的。

比如说,在销售业绩数据表中,分公司所在城市的数据存储在City字段,如果我们创建一个非聚簇索引,那么所有的分公司所在的城市,都会被纳入索引当中。但是如果我们使用设定过滤条件索引,我们就可以只选择一部分城市被索引,比如北京,上海和广州,代码如下:

CREATE NONCLUSTERED INDEX FilteredCities ON Sales(City) WHERE City IN ('北京','上海','广州') 与常规索引的区别在于,我们使用了WHERE语句来设定我们的过滤条件。假定公司的绝大部分收入都是来自于这三个城市的,那么我们的数据库查询会经常访问到在这三个城市产生的销售记录,在这种情况下,设定过滤条件索引会占据较少的磁盘空间,因为只有City字段的数值是北京,上海和广州的记录会被索引,这些记录只是整个销售数据表格中的一部分。

利用设定过滤条件索引可以提升数据库的性能,首先,只有被索引到记录发生变化的时候,才需要重建索引。比如,某一条在北京发生的销售记录需要调整,在更新操作之后,索引也要随之更新,这跟其他的索引是一样的。

但如果发生在西安的销售记录发生了变化,无论添加或删除了多少条记录,我们之前建立的设定过滤条件索引都是不需要任何操作的,因为只有位于北京、上海和广州分公司的销售记录有影响到这个索引。SQL Server设定过滤条件索引的另外一个优势是可以减少磁盘读写操作,比如我们要查询所有北京分公司的销售记录,那么使用刚才建立的设定过滤条件索引比常规的非聚簇索引要减少很多不必要的磁盘操作。

为了验证SQL Server设定过滤条件索引所带来的性能优势,我们进行了对比测试。

首先,我们在VirtualBox虚拟机里安装Windows Server 2008 R2与SQL Server 2008 R2中文版,顺便说一下,我们安装的都是可以试用180天的试用版,在微软官方网站可以直接下载,而且现在试用版也不需要申请序列号了,在安装过程中可以直接选择安装180天试用,就可以直接安装,这位实验和学习带来了不少便利。

我们在数据库中创建了一个500万条记录的销售数据表,当然,销售金额都是随机产生的,而city字段,我们随机产生1到9这9个不同的数字,然后再根据需要将它们在替换为不同的城市,在这个实验中,我们把北京、上海和广州的销售记录总比例设定为67%。

【编辑推荐】

  1. SQL Server删除重复数据的方案有哪些?
  2. 对SQL Server 2005删除用户失败的破解
  3. SQL Server还原数据库操作步骤的详细解析
  4. 转移SQL Server数据的实现
  5. SQL Server数据库的收缩与设定每日自动备份
责任编辑:佚名 来源: 清华大学出版社
相关推荐

2010-05-26 08:47:00

索引SQL Server

2010-06-30 13:49:02

SQL Server数

2010-07-07 10:02:46

SQL Server数

2010-11-12 11:25:44

SQL SERVER视

2010-07-15 15:42:38

2011-04-02 13:37:05

SQL Server 索引视图

2011-04-01 15:36:24

索引SQL Server

2011-08-10 15:11:23

SQL Server整理索引碎片重建索引

2011-07-27 17:22:10

mysql极限测试索引

2010-10-25 10:55:11

Oracle函数索引

2010-07-26 09:34:24

SQL Server性

2010-07-20 13:20:26

SQL Server聚

2010-09-16 13:42:55

SQL SERVER索

2011-03-21 15:51:27

SQL执行效率

2010-06-10 13:54:10

MySQL全文搜索

2010-07-07 10:54:22

SQL Server索

2010-09-09 16:51:50

2021-01-14 05:13:34

倒排索引搜索

2010-04-07 17:45:22

Oracle位图索引

2010-07-19 16:36:13

SQL Server视
点赞
收藏

51CTO技术栈公众号