Sql Server之旅——第九站 看公司这些DBA们设计的这些复合索引

这一篇再说下索引的最后一个主题，索引覆盖，当然学习比较好的捷径是看看那些大师们设计的索引，看从中能提取些什么营养的东西，下面我们看

看数据库中一个核心的Orders表。

　　

一：查看表的架构

<1> 先查看这个表的大概架构信息

1 --查看表的架构信息
2 SELECT c.column_id,c.name,t.name FROM sys.columns AS c 
3 JOIN sys.types t
4 ON c.system_type_id=t.system_type_id
5 WHERE c.object_id=object_id('O_Orders') 
6 ORDER BY c.column_id

 

从这个订单表来看大概有89个字段。。。还是蛮多的，可能有太多的历史原因吧，下面就有一个疑问来了，针对这么多的字段加上五花八门的类型，如何规划

好单列索引和复合索引。。。下面我们来看看这些专家们怎么设计的。

 

<2> 复合索引

　　首先声明一下，由于我的权限有限，不能进行DBCC IND，PAGE等命令，所以我没有能力判断下面的索引是include索引还是复合索引，所以这里统一叫成

复合索引吧。

1 SELECT name,type_desc FROM sys.indexes WHERE object_id=object_id('O_Orders')

从上面可以看到，有9个非聚集索引，1个聚集索引，然后可以通过 SHOW_STATISTICS 抽查几个索引看看到底关联了哪些字段，找到其中的二个索引，

覆盖多达6列，如索引"idx_order_status_2","IX_O_OrdersUID"。

DBCC SHOW_STATISTICS(O_Orders,idx_order_status_2)
DBCC SHOW_STATISTICS(O_Orders,IX_O_OrdersUID)

 

从这两个索引中关联的字段大概可以看出两点信息：

①：这些字段都比较小，为char(1)，smallint，bit这样的，自然表示的状态会比较少。

②：将表中多个状态少的字段挑选几个按照访问频率组合在一起做一个索引。

 

但是仔细想想，虽然原则上说状态少的字段不合适建索引，但是类似“订单状态(OrderStatus”这种字段，肯定是一个被频繁查询的列。。。既然是频繁的列，

肯定就要想办法优化，方法就是建复合索引，这样在复杂的sql中更加容易被撞上索引覆盖。

比如下面这样：

1 SET STATISTICS IO ON 
2 SELECT OrderStatus, ProcessStatus, SendTicketCity, FlightAgency, Eticket, OrderID
3 FROM dbo.Orders WHERE OrderStatus='P' AND ProcessStatus='1' AND SendTicketCity=1

然后继续挑选几个索引瞄一瞄。。。一般来说，覆盖1到2个列的索引都叫小索引。

1 DBCC SHOW_STATISTICS(O_Orders,idx_eid_orderdate)
2 DBCC SHOW_STATISTICS(O_Orders,IX_O_Order_FinishDate)

通过上面的索引大概可以看到，Eid和FinishDate这两列，一眼扫过就知道应该是一个唯一性比较高的列了，至于为什么要覆盖2列，那这个就是根据业务

和生产的滚动数据来决定了，那这样的索引有什么好处呢？同样更容易会撞到索引链接，也就是多条件中会走到多个索引，每个索引中贡献一些列刚好可以

满足select中的所有列。。。比如下面这样。

1 -- 可以看到，select中的所有列都是有idx_eid_orderdate 和 IX_O_Order_FinishDate 贡献
2 SELECT OrderID,FinishDate,PrepayType,Eid,OrderDate
3 FROM  dbo.O_Orders WHERE Eid='cctv1' AND FinishDate>2015-1-1

 

好了，就像园友说的，索引就是拆东墙补西墙，每建一个索引都需要评估它的利弊。

 

分类:  sql server

好文要顶  关注我  收藏该文   

一线码农
关注 - 56
粉丝 - 6493

荣誉： 推荐博客

+加关注

3

0

关注我

« 上一篇： Sql Server之旅——第八站 复合索引和include索引到底有多大区别？
» 下一篇： Sql Server之旅——第十站 看看DML操作对索引的影响

posted @  2015-02-03 14:24  一线码农 阅读( 2544) 评论( 6)  编辑  收藏

评论列表

  

#1楼 2015-02-03 14:57  dgdyq   

好像那里看过，生产表的索引一般不要超过6个？楼主怎么看？

支持(0) 反对(0)

  

#2楼 [ 楼主]  2015-02-03 15:56  一线码农   

@ dgdyq
这个没有标准答案吧，我觉得应该是：基础原则+跟业务结合。

支持(0) 反对(0)

  

#3楼 2015-02-03 17:27  wy123   

楼主截图的那些信息应该都是符合索引，如果是包含索引的话
比如create index idx_t1 on t_index2(col1)include(col2,col3,col4)
dbcc show_statistics得到的统计信息只有一行数据
你可以试一下就清楚了

另外，关于符合索引和包含索引的区别，CareySon大神说的应该比较清楚了
另外我测试了，在每个字段都是36字节newid()长度测试的情况下，
分别是两个表
分别建立如下两个索引，表数据一样，数量超过百万的情况下，索引树的高度差别就出来了，分别是三层和四层(查询同样一条数据，多一次逻辑读)，也就是说，符合索引使得B树变更高
create index idx_t1 on t_index2(col1)include(col2,col3,col4)
create index idx_t1 on t_index2(col1,col2,col3,col4)
这里并不是表达说复合索引不好之类的，只是说明一下索引树逻辑结构的区别，这两种情况各有各的应用场景吧

参考 http://www.cnblogs.com/CareySon/archive/2011/12/27/2303508.html

支持(0) 反对(0)

  

#4楼 2015-02-03 17:29  要有好的心情   

我给SQL Server实例添加一个登录用户User5，User5有 db_ddladmin、db_securityadmin、db_datareader、db_datawriter 角色，现在我用User5访问数据库myDB5，提示没有 执行 EXEC 语句的权限。
我想写一条 sql 语句来授权， GRANT EXECUTE .......，我查文档一直没有成功，请教这条sql语句应该怎样写，或者 给User5 再添加什么角色，也能起到作用， 非常感谢。

用工具改的话，是这样：
http://tech.cncms.com/shujuku/mssql/97116.html

支持(0) 反对(0)

  

#5楼 2015-02-04 09:30  过错   

@ 要有好的心情
可以用工具去授权，然后比较下两个数据库，会生成差异的sql语句吧。
我sql不熟，经常这么干。