Sql Server之旅——第九站 看公司这些DBA们设计的这些复合索引
来源:互联网 发布:mac怎么删除abc输入法 编辑:程序博客网 时间:2024/06/11 04:40
这一篇再说下索引的最后一个主题,索引覆盖,当然学习比较好的捷径是看看那些大师们设计的索引,看从中能提取些什么营养的东西,下面我们看
看数据库中一个核心的Orders表。
一:查看表的架构
<1> 先查看这个表的大概架构信息
1 --查看表的架构信息2 SELECT c.column_id,c.name,t.name FROM sys.columns AS c 3 JOIN sys.types t4 ON c.system_type_id=t.system_type_id5 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, OrderID3 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,OrderDate3 FROM dbo.O_Orders WHERE Eid='cctv1' AND FinishDate>2015-1-1
好了,就像园友说的,索引就是拆东墙补西墙,每建一个索引都需要评估它的利弊。
» 下一篇:Sql Server之旅——第十站 看看DML操作对索引的影响
这个没有标准答案吧,我觉得应该是:基础原则+跟业务结合。
比如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
我想写一条 sql 语句来授权, GRANT EXECUTE .......,我查文档一直没有成功,请教这条sql语句应该怎样写,或者 给User5 再添加什么角色,也能起到作用, 非常感谢。
用工具改的话,是这样:
http://tech.cncms.com/shujuku/mssql/97116.html
可以用工具去授权,然后比较下两个数据库,会生成差异的sql语句吧。
我sql不熟,经常这么干。
- Sql Server之旅——第九站 看公司这些DBA们设计的这些复合索引
- Sql Server之旅——第九站 看公司这些DBA们设计的这些复合索引
- Sql Server之旅——第八站 复合索引和include索引到底有多大区别?
- Sql Server之旅——第八站 复合索引和include索引到底有多大区别?
- SQL Server学习之复合索引
- SQL Server的复合索引学习
- SQL Server的复合索引学习
- SQL Server的复合索引学习【转载】
- SQL Server的复合索引学习
- SQL Server的复合索引学习
- 这些年,看社交设计的变迁-20140218早读课
- 这些天看的片子
- 近来看的这些书
- Vertica的这些事<六>—— SQL Server、Oracle、MySQL和Vertica数据库常用函数对比
- DS结合实际找地方看到这些看这些看过的
- sql server 复合索引和include索引
- Sql Server 创建索引 、复合索引
- SQL server 索引的设计
- 列表状态自动切换
- Problem C: 小数计算——结构体
- Sql Server之旅——第八站 复合索引和include索引到底有多大区别?
- C++设计模式浅识享元模式
- C语言基础 printf将一个十六进制按照十进制输出
- Sql Server之旅——第九站 看公司这些DBA们设计的这些复合索引
- Problem D: 分数减法——结构体
- 遗传算法
- 树形结构转换为json类型的字符串
- Swift - 分段选择控件(UISegmentedControl)的用法
- Service层事务不能回滚的解决方式
- Sql Server之旅——第十站 看看DML操作对索引的影响
- Mybatis自动创建表/更新表结构/动态建表
- ReactJS学习系列课程(React Router 使用)