postgressql添加联合索引 sql server 创建联合索引

SQL Server索引管理——索引创建建议和经验（四）

在前文的基础上，本文将阐述一些高级的索引技术。

高级索引技术

• 覆盖索引
• 索引交叉：使用多个非聚集索引满足单个查询需要的所有列
• 索引连接： 使用索引交叉和覆盖索引技术避免使用基础表
• 过滤索引：为在奇异分布或稀疏字段上创建索引，可以在索引上应用过滤，使得仅仅为某些数据创建索引
• 索引视图：这实现磁盘数据的视图输出

覆盖索引

创建测试表和测试数据

CREATE TABLE Member
(
       MemberNo INT IDENTITY,
       FirstName VARCHAR(30)  NULL,
       LastName VARCHAR(30)  NULL,
       EmailPromotion INT
);
INSERT INTO Member(FirstName,LastName,EmailPromotion)
SELECT FirstName,LastName,EmailPromotion
FROM Person.Person;
CREATE CLUSTERED INDEX clx_Member_No ON Member(MemberNo);
CREATE NONCLUSTERED INDEX ix_Member_FirstName ON Member(FirstName);

执行如下脚本，并查看执行计划和逻辑读情况

SET STATISTICS IO ON;
SELECT
       LastName,EmailPromotion
FROM Member
WHERE FirstName='Lolan';

由于非聚集索引ix_Member_FirstName仅仅包含FirstName列，没有包含在索引中查询需要的数据需要从表中（聚集索引的叶）中获取，索引查询计划使用了键查找获取数据。修改上面的非聚集索引，使其包含所有列，如下：

CREATE NONCLUSTERED INDEX ix_Member_FirstName ON Member(FirstName) INCLUDE(LastName,EmailPromotion)
WITH(DROP_EXISTING=ON);

再执行查询，并查看结果：

可以看到，查询计划变为简单索引查找，逻辑读次数由原来的6次变为2次

 

INCLUDE最好的使用情景：

• 你不想增加索引键的大小，但是你想要索引变为覆盖索引
• 你想在一个不能创建索引的列上创建索引（text, ntext 和 images）
• 你已经超出了索引键列的最大列数（这种情况最好避免）

覆盖索引可以看做伪聚集索引

覆盖索引以序列顺序物理的组织所有索引数据。从磁盘I/O方面看，一个不使用包含列的覆盖索引，对于索引的列能完全满足查询情景来说，其变为一个聚集索引。如果查询结果集需要排序输出，那么覆盖索引可以按照结果集的需求，维持列的物理顺序和其一致。

建议

为利用覆盖索引的优点，注意SELECT后面的结果列。尽可能保证覆盖索引键列尽可能的小。使用INCLUDE增加列变得很有意义。因为覆盖索引包含查询中的所有列，它可能非常宽，增加维护覆盖索引的成本。你必须平衡维护成本和覆盖索引带来的性能优化。如果索引中所有列的字节数少于表中一行数据的字节，并且你确定采用覆盖索引的查询频繁的被执行，那么使用覆盖索引将非常有意义。

在创建大量的覆盖索引之前，考虑SQL Server 怎样有效的、自动的使用交叉索引创建覆盖索引使得查询飞起来。

交叉索引

如果表有多个索引，那么SQL Server 可能使用多个索引执行查询。SQl Server 可以利用多个索引的优点，选择基于每个索引较小的数据集，然后执行两个子集的交叉（即返回满足所有准则的行）。SQL Server可以利用一个表上的多个索引，然后使用join算法获得两个表的交集。

在如下的查询语句中，WHERE条件列 SalesPersonID上没有非聚集索引，OrderDate列上没有索引：

SELECT *
FROM Sales.SalesOrderHeader
WHERE  OrderDate BETWEEN '2011-06-04' AND '2011-07-07'
       AND SalesPersonID=276;

在OrderDate字段上创建非聚集索引，脚本如下：

CREATE NONCLUSTERED INDEX IX_test ON Sales.SalesOrderHeader(OrderDate);

再执行上述查询脚本，并检查执行计划和I/O情况：

正如你所看到的，SQL Server 使用了两个非聚集索引查找（而不是扫描），并且使用了交叉算法获得两个子集的交集。然后在从前两子集的交集中，进行键查找，检索其余不包含在索引中的数据。从逻辑的次数来看，交叉查找显著提升了性能（聚集索引扫描逻辑查找698次，交叉索引仅用了34次）。

       为改进查询性能，SQL Server 可以使用表中的多个索引。因此，可以考虑创建多个窄索引替代创建的宽索引。当需要的时候，SQL Server可以一起使用它们。当不需要的时候，使用窄索引的查询也会受益。当创建覆盖索引时，确定索引的宽度是否被接受，是否使用包含列就可以完成任务。如果不行，找出已经存储的包含需要的覆盖索引的大部分列的非聚集索引。如果可能，重新组织已经存在的非聚集索引列的适当的顺序，运行优化器考虑使用两个非聚集索引的交叉。

       有时，可能因为如下原因，你必须创建独立的非聚集索引：

• 不允许调整已经存在索引中列的顺序
• 某些覆盖索引所需要的列不存在于已经存在的非聚集索引中
• 两个已经存在的非聚集索引列的总和超出覆盖聚集索引所需要的列

这种情形下，你可以在其余的列上创建一个非聚集索引。如果新索引的组合列顺序和存在的非聚集索引满足覆盖索引需求，优化器将能够适应交叉索引。辨识新索引的列和它们的顺序，关注其他查询，尽量最大化索引的效用。

Index Joins

索引连接是索引交叉的一个变化，是覆盖索引在交叉索引上的应用。如果没有单一的索引覆盖查询所有的列，但多个索引结合起来可以覆盖查询的所有列，SQL Server可以使用索引连接的方式完全满足查询，而不去检索基础表

让我们在实践中检验这个索引技术。将“索引交叉”的查询像这样修改：

USE AdventureWorks2016CTP3
GO
SET STATISTICS IO ON;
SELECT SalesPersonID,OrderDate
FROM Sales.SalesOrderHeader
WHERE  OrderDate BETWEEN '2011-06-04' AND '2011-07-07'
       AND SalesPersonID=276;

其查询计划和读如下：

从执行计划来看，优化器没有使用列SalesPersonId 上存在的非聚集索引。因为查询还需要OrderDate列值，优化器选择聚集索引检索查询所需要的所有列值。如果我们在OrderDate列上创建像下面的索引：

CREATE NONCLUSTERED INDEX IX_test ON Sales.SalesOrderHeader(OrderDate);

再执行上述查询脚本，查看执行计划和读如下：

两个索引联合，就像一个覆盖索引，使用两个索引查找操作连接一起，而不再使用聚集索引，将表的读从698降低到4

索引过滤

过滤索引是使用过滤器的非聚集索引，基于WHERE语句，在列或列集上创建高选择性索引，不进行筛选，可能不具有高选择性。例如，一个拥有大量NULL值的列，可能以稀疏列的形式存储，以减少NULL值的消耗。在这样的列上增加过滤索引，允许你拥有一个非NULL数据的可用索引。理解这个的最好方式是看其在实践中应用。

Sales.SalesOrderHeader 表中有3万多条记录，其中PurchaseOrderNumber 和SalesPersonId列均有27000多个NULL值，如下面的查询结果：

USE AdventureWorks2016CTP3
GO
SELECT COUNT(1),COUNT(PurchaseOrderNumber),COUNT(SalesPersonID)
FROM Sales.SalesOrderHeader;

如果你想要获得简单购买订单数量列表，可能使用如下的脚本：

SELECT
       PurchaseOrderNumber
       ,OrderDate
       ,ShipDate
       ,SalesPersonID
FROM Sales.SalesOrderHeader
WHERE PurchaseOrderNumber LIKE 'PO5%'
       AND SalesPersonID IS NOT NULL;

为解决这个问题，可能创建一个索引，包含查询结果的某些列，如下：

CREATE INDEX IX_Test ON Sales.SalesOrderHeader(PurchaseOrderNumber,SalesPersonID)
INCLUDE(OrderDate,ShipDate)
WITH(DROP_EXISTING=ON);

当你返回执行查询的时候，发现性能改进非常明显，如下所示：

正如你所看到的，逻辑读从696次降低到5次，通常，这已经足够了。假设这个查询被频繁调用，非常频繁。现在你能从中挤出来的每个位速度提升，都将给你带来红利。我们知道，索引列中的很多数据都是NULL，你可以调整索引，过滤掉NULL值，这些NULL值不再在索引中，减少索引树的大小，因此减少了搜索：

CREATE INDEX IX_Test ON Sales.SalesOrderHeader(PurchaseOrderNumber,SalesPersonID)
INCLUDE(OrderDate,ShipDate)
WHERE PurchaseOrderNumber IS NOT NULL
       AND SalesPersonID IS NOT NULL
WITH(DROP_EXISTING=ON);

尽管从绝对数上来看，读从5降到4不是很多，但从比例上看，其减少了查询I/O消耗的20%，如果这个查询每分钟运行成百上千次，20%的降低将是很大的提升。

过滤索引在如下多种情景下应用会有较好的效果：

• 通过减少索引的大小改进性能
• 创建较小的索引，减少存储成本
• 因为减少索引大小而降低索引维护的消耗

第一个过滤索引应用的地方像前的例子一样，从索引中排除NULL值。你也可以将频繁读取的数据集独立出来，这样针对这部分的查询将快得多。你可以使用WHERE语句以类似于创建索引视图的方式过滤数据，创建覆盖的过滤索引，而没有维护索引视图那些头痛的事情，和上面的例子一样。

       当读取或创建过滤索引时，它们需要特定ANSI设定集：

• ON：ANSI_NULLS（ON时NULL值只能使用IS NULL、IS NOT NULL 判断，否则可以使用=、<>、！=判断），ANSI_PADDING，ANSI_WARNINGS，ARITHABORT，CONCAT_NULL_YIELDS_NULL，QUOTED_IDENTIFIER（关键字使用双引号括起来，字符使用单引号）
• OFF：NUMERIC_ROUNDABOART

索引视图

创建有聚集索引的视图，在视图上创建聚集索引后，还可以创建其他非聚集索引。普通视图是虚拟的，不占用存储的；索引视图会在创建聚集索引时物化视图。

适合于数据仓库、统计库等OLAP数据库中的汇总查询，不适合OLTP系统，因为原表的数据变化，将引起索引视图同步更新；位于原表中索引视图的聚集索引键列的更新，会引起索引视图及所有索引的更新，这些在事务系统中都是重大的开销。

USE AdventureWorks2016CTP3
GO
IF EXISTS(SELECT *
                FROM sys.VIEWS
                WHERE [object_id]=OBJECT_ID(N'[Purchasing].[IndexedView]'))
       DROP VIEW [Purchasing].[IndexedView];
GO
CREATE VIEW Purchasing.IndexdView
WITH schemabinding
AS
SELECT
       ProductID
       ,SUM(OrderQty) OrderQty
       ,SUM(ReceivedQty) ReceivedQty
       ,SUM(RejectedQty) RejectedQty
       ,COUNT_BIG(*) [Count]
FROM Purchasing.PurchaseOrderDetail
GROUP BY ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX iv ON [Purchasing].[IndexdView](ProductId);

索引压缩

数据和索引压缩是在SQL Server 2008版本引入的（企业版、开发板可用）。压缩索引意味着在一页上有更多的键信息。这可能引起一系列的性能改进，因为压缩后只需要较少的索引页、索引级别存储索引。当索引中的键值被压缩或者解压时，会有CPU、内存消耗，因此这个方案可能不适合所有索引。

       默认情况下索引是不压缩的。当需要压缩索引时，需要在创建索引时显示的指定索引的压缩属性。有两个类型的压缩：行级、页级。在页级别的压缩中索引的非叶页不能压缩。为了看其表现，考虑如下索引：

--测试1.默认无压缩
CREATE NONCLUSTERED INDEX IX_Test
ON Person.Address([City],[PostalCode])
--测试2.行压缩
CREATE NONCLUSTERED INDEX IX_Comp_Test
ON Person.Address([City],[PostalCode])
WITH(data_compression=ROW);
--测试3.页压缩
CREATE NONCLUSTERED INDEX IX_Comp_Page_Test
ON Person.Address([City],[PostalCode])
WITH(data_compression=Page);

使用如下脚本查看三个索引的页数情况

SELECT
       i.name
       ,i.type_desc
       ,s.page_count
       ,s.record_count
       ,s.index_level
       ,compressed_page_count
FROM sys.indexes i
       JOIN sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2016CTP3')
              ,OBJECT_ID(N'Person.Address'),NULL,NULL,'DETAILED') AS s
       ON i.index_id=s.index_id
WHERE i.[object_id]=OBJECT_ID(N'Person.Address');

结果如下：

可以看到，索引数据页从106（无压缩）→63（行压缩）→25（页压缩）

为查看压缩索引对性能的影响，我们使用如下查询

SELECT
       a.City,a.PostalCode
FROM Person.Address AS a
WHERE a.City='Newton'
       AND a.PostalCode='V2M1N7';

当三个索引都存在时，执行上述查询，并查看执行计划和读的情况如下：

删除页压缩的索引

DROP INDEX IX_Comp_Page_Test ON Person.Address;

再执行查询，并查看执行计划和读的情况：

删除行压缩索引，

DROP INDEX IX_Comp_Test ON Person.Address;

再执行查询，并查看逻辑读如下：

比较无压缩、行压缩、页压缩三种索引的逻辑读情形，可以看到，无压缩时，逻辑读最多；行压缩逻辑读次之；页压缩逻辑读最少。