SQLServer索引的四个高级特性

标签: sqlserver 索引 | 发表时间:2013-03-25 23:33 | 作者:yzsind
出处:http://blog.csdn.net

SQLServer索引的四个高级特性

 

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

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

SQLServer
支持,语法示例:

create index idx_3 on order(status) where status=’running’

MySQL:不支持

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


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

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

Select * from order where member_id=? and order_date between ? and ?

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

create index idx_1 on order(member_id,order_date);

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

select order_id,status from order where member_id=?


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

create index idx_2 on order(member_id,order_id,status);


 

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

SQLServer

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

create index idx_2 on order(member_id) include(order_id,status);

其中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选项):

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

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

CREATE UNIQUE CLUSTERED INDEX idx_4 ON V2 (sum_amount)

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

SELECT  sum_amount FROM  v2 WITH(NOEXPAND)

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

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

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


2013/3/25

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

作者:yzsind 发表于2013-3-25 23:33:35 原文链接
阅读:127 评论:0 查看评论

相关 [sqlserver 索引] 推荐:

SQLServer索引的四个高级特性

- - CSDN博客数据库推荐文章
SQLServer索引的四个高级特性. 一、Index Building Filter(索引创建时过滤).         有一些索引非常低效的,比如经常查询状态为进行中的订单,订单有99%的状态是完成,1%是进行中 ,因此我们在订单状态字段上建了一个索引,性能是提高了,但是感觉索引中保存了99%的完成状态数据是永远不会查询到的,很浪费空间.

SqlServer索引的原理与应用 - 张龙豪

- - 博客园_首页
索引的用途:我们对数据查询及处理速度已成为衡量应用系统成败的标准,而采用索引来加快数据处理速度通常是最普遍采用的优化方法. 索引是什么:数据库中的索引类似于一本书的目录,在一本书中使用目录可以快速找到你想要的信息,而不需要读完全书. 在数据库中,数据库程序使用索引可以重啊到表中的数据,而不必扫描整个表.

B-Tree索引在sqlserver和mysql中的应用

- - CSDN博客数据库推荐文章
在谈论数据库性能优化的时候,通常都会提到“索引”,但很多人其实没有真正理解索引,并没有搞清楚索引为什么能加快检索速度,以至于在实践中并不能很好的应用索引. 事实上,索引可以说是最廉价而且十分有效一种优化手段,一般而言,设计优良的索引对查询性能优化确实能起到立竿见影的效果. 相信很多读者,都了解和使用过索引,可能也看过或者听过”新华字典“、”图书馆“之类比较通俗描述,但是对索引的存储结构和本质任然还比较迷茫.

oracle、mysql和sqlserver分页

- - Oracle - 数据库 - ITeye博客
sql server row number分页:. mysql limit分页:. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

SQLServer 镜像功能完全实现

- Bloger - 博客园-首页原创精华区
折腾SQLServer 镜像搞了一天,终于有点成果,现在分享出来,之前按网上做的出了很多问题. 现在尽量把所遇到的问题都分享出来. 在域环境下我没配置成果,也许是域用户的原因,因为我在生产环境下搞的,更改域用户需要重启SQLServer ,所以这个方法放弃了,只能用证书形式. 主机:192.168.10.2  (代号A).

Oracle、Db2、SqlServer、MySQL 数据库插入当前系统时间

- - CSDN博客推荐文章
例如有表table,table 中有两个字段:name 、makedate. 插入系统时间应为sysdate:. insert into table (name,makedate) values('测试',sysdate);. 插入系统时间应为current timestamp并且makedate数据类型为timestamp.

sqlserver数据库大型应用解决方案总结

- - 博客园_首页
随着互联网应用的广泛普及,海量数据的存储和访问成为了系统设计的瓶颈问题. 对于一个大型的互联网应用,每天百万级甚至上亿的PV无疑对数据库造成了相当高的负载. 对于系统的稳定性和扩展性造成了极大的问题. 负载均衡集群是由一组相互独立的计算机系统构成,通过常规网络或专用网络进行连接,由路由器衔接在一起,各节点相互协作、共同负载、均衡压力,对客户端来说,整个群集可以视为一台具有超高性能的独立服务器.

sqlserver定位消耗资源比较高的sql

- - CSDN博客数据库推荐文章
sqlserver查看消耗资源的sql ,具体如下:. 可以使用SQL 快速定位数据库里CPU,IO 消耗比较高的SQL:. 定位消耗cpu,io 最高的进程信息. 定位该进程执行的 SQL 语句.      到查到的 db 里执行:. 也可以从事件探查器查看sql的执行计划. 作者:lihaiwenshu 发表于2014-10-21 17:06:52 原文链接.

SQLserver, Oracle 限制层数 递归查询和反向查询父记录

- - Oracle - 数据库 - ITeye博客
以前使用Oracle,觉得它的递归查询很好用,就研究了一下SqlServer,发现它也支持在Sql里递归查询. SqlServer2008版本的Sql如下:. 比如一个表,有id和pId字段,id是主键,pid表示它的上级节点,表结构和数据:. --下面的Sql是查询出1结点的所有子结点. select * from my1 --结果包含1这条记录,如果不想包含,可以在最后加上:where id <> 1.

我是如何在SQLServer中处理每天四亿三千万记录的

- - 博客 - 伯乐在线
首先声明,我只是个程序员,不是专业的DBA,以下这篇文章是从一个问题的解决过程去写的,而不是一开始就给大家一个正确的结果,如果文中有不对的地方,请各位数据库大牛给予指正,以便我能够更好的处理此次业务. 这是给某数据中心做的一个项目,项目难度之大令人发指,这个项目真正的让我感觉到了,商场如战场,而我只是其中的一个小兵,太多的战术,太多的高层之间的较量,太多的内幕了.