组合索引(复合索引——多个索引列、覆盖索引——INCLUDE包含列):
也就是说把常用的where后面的条件查询的字段作为索引的键列,而需要返回的字段就作为索引包含的非键列。
如果where的是两个或两个以上的谓词的话,这个索引就可以创建为复合索引了。
create index idx_nc_cid_od_i_oid_eid_sid on dbo.orders(custid,orderdate) include(orderid,empid,shipperid)
select orderid,empid,shipperid from dbo.orders with(nolock) where custid=5 order by orderdate
dbo.orders后面括号里的就是复合索引的键列,而include后面括号里的就是复合索引的非键列。
使用INCLUDE修改原来非聚集索引:
CREATE NONCLUSTERED INDEX IX_Address_StateProvinceID ON Person.(StateProvinceID ASC)
INCLUDE (PostalCode) WITH(DROP_EXISTING=ON)
注意:WITH用法
INCLUDE建议:
不增加索引大小,但仍然希望有一个覆盖索引
索引一种不能为索引的数据类型(除了文本,NTEXT和图像)
已经超过了一个索引的关键字列的最大数量
覆盖索引能帮助解决阻塞和死锁,并能有效解决书签查找(RID查找、键查找)。
过滤索引:
是使用过滤器的非聚集索引,这个过滤器基本上是一个WHERE子句,用来在可能没有很好选择性的一个或多个列上创建一个高选择性的关键字组。
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_1 ON Sales.SalesOrderHeader(PurchaseOrderNumber,SalesPersonID)
INCLUDE (OrderDate,ShipDate)
WHERE PurchaseOrderNumber IS NOT NULL
AND SalesPersonID IS NOT NULL
该索引用来将为NULL的值过滤在索引之外,也就是为NOT NULL的值建立索引。
过滤索引的建议:
减小索引尺寸从而增加查询效率
建立更小的索引降低索引开销
因为尺寸减小,降低了索引维护的成本
过滤索引需要在访问或者创建时的一组特殊ANSI设置:
ON:ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,ARITHABORT,CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER
OFF:NUMERIC_ROUNDABORT
禁用索引:
ALTER INDEX PROD_SERVICE_IDX3 ON dbo.PROD_SERVICE REBUILD --重建指定索引
ALTER INDEX ALL ON dbo.PROD_SERVICE REBUILD –-重建表的全部索引
--重新生成表上所有的索引
ALTER INDEX ALL ON 表名
REBUILD
--禁用表上索引
ALTER INDEX 索引名 ON 表名
DISABLE ;
--禁用表上所有的索引--这个没试过
ALTER INDEX ALL ON 表名
DISABLE ;
如果禁用聚集索引.那整个表都不可以访问的。
如果禁用非聚焦索引.那索引不可以访问.会在表上加一个共享锁.但可以访问表。
2 个回复
gogodiy - 天善智能数据库专家、Tableau爱好者 2013-07-09 回答
赞同来自: 梁勇 、冰咖啡 、dc_c 、rollin
也就是说把常用的where后面的条件查询的字段作为索引的键列,而需要返回的字段就作为索引包含的非键列。
如果where的是两个或两个以上的谓词的话,这个索引就可以创建为复合索引了。
create index idx_nc_cid_od_i_oid_eid_sid on dbo.orders(custid,orderdate) include(orderid,empid,shipperid)
select orderid,empid,shipperid from dbo.orders with(nolock) where custid=5 order by orderdate
dbo.orders后面括号里的就是复合索引的键列,而include后面括号里的就是复合索引的非键列。
使用INCLUDE修改原来非聚集索引:
CREATE NONCLUSTERED INDEX IX_Address_StateProvinceID ON Person.(StateProvinceID ASC)
INCLUDE (PostalCode) WITH(DROP_EXISTING=ON)
注意:WITH用法
INCLUDE建议:
不增加索引大小,但仍然希望有一个覆盖索引
索引一种不能为索引的数据类型(除了文本,NTEXT和图像)
已经超过了一个索引的关键字列的最大数量
覆盖索引能帮助解决阻塞和死锁,并能有效解决书签查找(RID查找、键查找)。
索引交叉:
自动使用索引交叉来为查询即时创建覆盖索引。
为了增加查询的性能,SQL Server可以在表上使用多个索引。因此可以考虑创建多个窄索引来代替宽索引的索引键。SQL Server能在需要的时候一起使用它们。如果不需要时,查询也能从窄索引中受益。
索引交叉建议:
重新排列现有索引列不被允许
覆盖索引所需要的一些列不能被包含在现有的非聚集索引中
两个现有非聚集索引中的总列数可能多余覆盖索引所需要的列数
索引连接:
是索引交叉的变种,将覆盖索引的技术应用到索引交叉。如果没有单个覆盖查询的索引而多个索引一起可以覆盖查询,SQL Server可以使用索引连接来满足查询而不用转到基本表。
可以考虑创建多个窄索引,而不是创建宽的覆盖索引,SQL Server可以一起使用它们作为覆盖索引,也可以单独使用它们。
注意:非聚集索引不是越多越好。
过滤索引:
是使用过滤器的非聚集索引,这个过滤器基本上是一个WHERE子句,用来在可能没有很好选择性的一个或多个列上创建一个高选择性的关键字组。
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_1 ON Sales.SalesOrderHeader(PurchaseOrderNumber,SalesPersonID)
INCLUDE (OrderDate,ShipDate)
WHERE PurchaseOrderNumber IS NOT NULL
AND SalesPersonID IS NOT NULL
该索引用来将为NULL的值过滤在索引之外,也就是为NOT NULL的值建立索引。
过滤索引的建议:
减小索引尺寸从而增加查询效率
建立更小的索引降低索引开销
因为尺寸减小,降低了索引维护的成本
过滤索引需要在访问或者创建时的一组特殊ANSI设置:
ON:ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,ARITHABORT,CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER
OFF:NUMERIC_ROUNDABORT
gogodiy - 天善智能数据库专家、Tableau爱好者 2013-07-09 回答
赞同来自: 梁勇 、dc_c 、rollin
数据库视图可以通过在视图上创建唯一的聚集索引在磁盘上物理化,这样一个索引被称为索引视图(indexed view)或实物化视图(materialized view)。
好处:
聚合(Aggregations)可以预先计算并被保存在索引视图中,以在查询执行期间最小化昂贵的计算
表可以预先连接,结果数据集可以实物化
连接或聚合的组合可以被实物化
开销:
基本表中的任何修改必须执行视图的SELECT语句反映到索引视图中
对索引视图定义的基本表上的任何修改可能发起索引视图的非聚集索引中的修改,如果聚集键被更新,聚集索引也将必须更新
索引视图增加数据库的维护开销
数据库中需要更多的存储
限制:
视图的第一个索引必须是唯一聚集索引
索引视图上的非聚集索引只可以在唯一聚集索引创建之后创建
视图定义必须是确定性的,即它对一个给定的查询只能返回一个可能的结果(确定性和非确定性函数在联机丛书中提供)
索引视图必须只引用相同数据库中的基本表,而不是其他视图
索引视图可以包含浮点列但是这样的列不能包含在聚集索引键中
索引视图必须是绑定到所引用表的一个架构(schema),以避免表架构的修改
视图定义的语法有多种限制(参见联机丛书)
定义索引视图的 SELECT 语句不得包含 TOP、DISTINCT、COMPUTE、HAVING 和 UNION 关键字。也不能包含子查询。
SELECT 列表中不得包含星号 ()、'table.' 通配符列表、DISTINCT、COUNT(*)、COUNT(<expression>)、基表中的计算列和标量聚合。
非聚合 SELECT 列表中不能包含表达式。聚合 SELECT 列表(包含 GROUP BY 的查询)中可能包含 SUM 和 COUNT_BIG(<expression>);它一定包含 COUNT_BIG(*)。不允许有其它聚合函数(MIN、MAX、STDEV,...)。
使用 AVG 的复杂聚合无法参与索引视图的 SELECT 列表。不过,如果查询使用这样的聚合,则优化程序将能使用该索引视图,用 SUM 和 COUNT_BIG 的简单聚合组合代替 AVG。
索引的创建者必须拥有表。所有表、视图和索引必须在同一数据库中创建。
定义索引视图的 SELECT 语句不得包含视图、行集函数、行内函数或派生表。同一物理表在该语句中只能出现一次。
在任何联接表中,均不允许进行 OUTER JOIN 操作。
搜索条件中不允许使用子查询或者 CONTAINS 或 FREETEXT 谓词。
如果视图定义包含 GROUP BY 子句,则视图的 SELECT 列表中必须包含所有分组依据列及 COUNT_BIG(*) 表达式。此外,CREATE UNIQUE CLUSTERED INDEX 子句中必须只包含这些列。
必须确定的SET选项列表如下:
ON:ARITHABORT,CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER,ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS
OFF:NUMERIC_ROUNDABORT
显示声明:
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
使用情景:
报告系统从索引视图中获益最多。
频繁写入的OLTP系统不适合索引视图,会增加更新和维护的成本。
索引视图提供的性能净增长是视图提供的总查询执行节约和保存/维护视图开销之间的差值。
索引压缩:
从2008开始。
压缩一个索引意味着将在一个页面中获得更多的关键字信息,存储索引需要的页面和索引级别更少。
因为索引中的键值被压缩和解压缩,将造成CPU和内存的开销,所以不适合所有索引。
默认情况下,索引将不会被压缩。必须明确的在创建索引时要求索引被压缩。
索引中的非叶子页面不接受页面类型下的压缩。
类型:
行级压缩
页面级压缩
禁用索引:
ALTER INDEX PROD_SERVICE_IDX3 ON dbo.PROD_SERVICE REBUILD --重建指定索引
ALTER INDEX ALL ON dbo.PROD_SERVICE REBUILD –-重建表的全部索引
--重新生成表上所有的索引
ALTER INDEX ALL ON 表名
REBUILD
--禁用表上索引
ALTER INDEX 索引名 ON 表名
DISABLE ;
--禁用表上所有的索引--这个没试过
ALTER INDEX ALL ON 表名
DISABLE ;
如果禁用聚集索引.那整个表都不可以访问的。
如果禁用非聚焦索引.那索引不可以访问.会在表上加一个共享锁.但可以访问表。