SQLServer索引的四个高级特性_sql

概述来自：http://blog.csdn.net/yzsind/article/details/8719594 SQLServer索引的四个高级特性一、Index Building Filter(索引创建时过滤) 有一些索引非常低效的，比如经常查询状态为进行中的订单，订单有99%的状态是完成，1%是进行中，因此我们在订单状态字段上建了一个索引，性能是提高了，但是感觉

来自：http://blog.csdn.net/yzsind/article/details/8719594

sqlServer索引的四个高级特性

一、Index Building Filter(索引创建时过滤)

有一些索引非常低效的，比如经常查询状态为进行中的订单，订单有99%的状态是完成，1%是进行中，因此我们在订单状态字段上建了一个索引，性能是提高了，但是感觉索引中保存了99%的完成状态数据是永远不会查询到的，很浪费空间。如果我们的索引在建立的时间就不保存完成状态的数据，那不是更好。 Index Building Filter就是用来解决这个问题。

sqlServer
支持，语法示例：

[sql] view plain copy create index IDx_3 on order(status) where status=’running’

MysqL:不支持

Oracle:不支持，可以考虑用分区解决

二、Index Include Column(索引包含列)

我们经常需要建一些组合索引，一般有两种原因：
1.通过组合索引可以提高索引过滤度
比如订单表有会员ID和订单日期2个字段，如果我们经常要按会员和订单时间查询，

copy Select * from order where member_ID=? and order_date between ? and ?

那建立会员ID+订单日期的索引很合适。

copy index IDx_1 order(member_ID,order_date);

2.索引覆盖读取
比如我们需要读取一个会员订单的订单ID+状态列表，sql如下：

copy select order_ID,status where member_ID=?

如果我们的索引中只有member_ID字段，那么还需要回表查询order_ID和status数据才能返回结果，如果建一个member_ID+order_ID+status的组合索引:

copy index IDx_2

那只要访问索引就可以返回数据了，这种虽然性能提高了，但是由于索引多了字段，因此增加了索引建立成本和索引空间。

sqlServer

sqlServer除了支持组合索引外，还支持Index Include Column特性，Index Include Column是组合索引的一种变种，它的特点是可以指定组合索引中哪些列是排序列，哪些列只是把内容存储在索引中，这个特性不仅可以满足索引覆盖读取，而且可以减少索引对DML的性能影响。语法如：

copy order(member_ID) include(order_ID,43); Font-family:Arial; Font-size:14px; line-height:26px; text-align:left"> 其中member_ID字段是普通索引列，order_ID和status列是内容include列。

普通组合索引数据存储结构示例:

Include Column组合索引数据存储结构示例:

sqlServer管理器的sql优化自动索引推荐就经常看到推荐Include Column方式。

MysqL:不支持，只能用组合索引代替
Oracle:不支持，只能用组合索引代替

三、聚集索引(Cluster Index)

数据库通常用两种存储方式，一种是堆表，即表中的数据是基本无序的，像往一个房间(数据块)堆箱子(记录)一样，只要有空间就往里面放，放满了就准备一个新房间再放。

另外一种就是聚集存储，数据按表中一个或几个字段排序存储，如下图所示。

由于要排序，需要索引来保证效率，所以聚集存储和聚集索引存储通常指一个意思。

sqlServer
        如果表没有主键默认为堆表，如果有主键默认为按主键聚集存储。sqlServer支持非主键索引聚集存储，这个特性非常有意义，比如订单表有订单ID(主键)和会员ID，如果按订单ID聚集存储，由于订单ID一般都是随机访问，返回单条记录，所以对按订单ID查询没有什么性能提高。假设需按会员ID查询，一个会员有许多订单，分页一次返回20条，那就需要20次离散数据访问。
如果可以按会员ID聚集存储，那用会员ID查询可能只需要1次离散数据访问就可以，性能可以提升很多，这种方式对订单插入有一些性能影响，如果订单插入不多，按会员查询频繁，那按会员ID建聚集索引给用订单ID聚集效果很好。
MysqL
       MysqL MYISAM存储引擎只支持堆存储，不支持聚集索引。
       MysqL INNODB存储引擎只能按主键聚集，如果没有主键就用一个内部隐藏主键代替。

Oracle
Oracle默认是堆存储，如果建成索引组织表则按主键聚集存储。Oracle还有一个种更高级的聚集存储，概念叫簇(Cluster)，可以定义一个簇对象，然后将一个或多个表按字段顺序聚集的存储在这个簇中，从而实现多个表聚集存储，适用于一些主从表，如订单与订单明细，它们的数据是按关联字段聚集的存储在一个数据块中，订单与订单明细经常一起查询，所以这种逻辑只要读取一次数据块即可，如果用非Cluster，那需要读取多个数据块才OK。

四、VIEW INDEX(视图索引)

在视图上建索引，感觉没有意义，因为视图本身就是一个逻辑的概念，并不存储物理数据，何来索引之说。

sqlServer
支持。视图上建索引首先视图需要绑定架构。视图上需要先建一个唯的聚集索引，把数据持久化，持久化后还可以建其它新的索引，像普通表一样处理了。

视图上建索引可以让数据持久化，一般有两种用途
1. 统计类数据查询性能优化
如经常要做select sum(amount) from t2这样的 *** 作，性能不好优化，并且t表数据变化不多，那么可以建一个视图(注意：需要加上with schemabinding选项):

copy CREATE VIEW V2 with schemabinding AS SELECT SUM(amount) as sum_amount,COUNT_BIG (*) as cnt FROM t2

然后在这个视图上建一个唯一聚集索引，数据就持久化了。

copy UNIQUE CLUSTERED INDEX IDx_4 ON V2 (sum_amount)

然后我们用noexpand方式查询v2索引视图，如下：

copy SELECT sum_amount FROM v2 WITH(NOEXPAND)

性能会非常好，因为视图里只有一行数据，直接读取即可，不需要再从t2全表扫描汇总。

2.自动实现多维度聚集存储
数据库的表一般只能设计为按一种方式聚集存储(只允许有一个聚集索引)，但在实际业务中存在一些多个维度的查询，比如交易表，需要按买家维度查询，也需要按卖家维度查询。普通表只能选择一种，如果要两种维度性能都很好很难，有时只能人工的分为两张表，一张表按买家聚集，一张表按卖家聚集，用程序或触发器维护两张表数据的一致性，这样看起来很别扭。采用视图索引后可以在主表(买家维度表)上建个视图，然后在视图上用卖家维度建聚集索引，以后如果要按买家查询则查询主表，如果按卖家查询才查询索引视图。
这种性能优化方式只是一个方案设计，实践中没有经过验证。

MysqL:不支持
Oracle:不支持，物化视图可以起到类似的作用，并提供了更多的数据同步控制特性。

2013/3/25

我的新浪微博(http://weibo.com/yzsind)

@H_275_404@ 总结

以上是内存溢出为你收集整理的SQLServer索引的四个高级特性全部内容，希望文章能够帮你解决SQLServer索引的四个高级特性所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/1174938.html