Sql Server查询性能优化之创建合理的索引(下篇)

最近貌似比较忙，突发现好久木有写博客了。。。

续上一篇 Sql Server查询性能优化之创建合理的索引(上篇)

　　数据库索引分为聚集索引和非聚集索引，聚集索引就是物理索引，也就是数据的物理的存储顺序，聚集索引的叶子节点就是数据行本身；非聚集索引是逻辑索引，也可以简单的认为是对聚集索引建立的索引，一般来说聚集索引的键就是非聚集索引的叶子节点(在不使用include时)。

关于索引的选择

　　对于索引类型来说没什么好选的，一般来说聚集索引是必须的（有特殊需要的另说），非聚集索引看实际需要灵活建立。因此对于索引来说主要是决定在那些列上建立索引，尤其是对于聚集索引这点非常重要。

聚集索引

　　聚集索引作为最重要的索引往往被我们所忽略，而其最大的优势就是大范围数据查询有着较高的效率，因此聚集索引列的选择往往对数据库性能有着灰常大的影响。为了尽量发挥聚集索引在大范围数据查找上的优势，推荐按以下顺序选择聚集索引列。

聚集索引字段选择优先级：时间字段>>会进行大范围查询的列>>具有唯一值的有实际意义的字段>>自增列ID

　　1.时间字段：若表里面有时间列，并且时间是按照数据插入顺序增长时（时间无需唯一即可有重复值，哪怕是大范围重复），建议采用时间列作为聚集索引的第一选择。理由：聚集索引有一个巨大的优势就是进行大范围数据查找，而且这个优势会随着数据量的增加而越来越明显，一般来说我们需要进行大数据量范围查询时都会用时间列围作为筛选条件，由于聚集索引不存在书签查找而且可以进行连续扫描，因此查询速度会非常快。时间列数据最好是顺序插入的这样可以尽量减少磁盘碎片，是数据存储相对集中，便于连续数据读取。

　　2.会进行大范围查询的列：若表里面没有时间字段或者时间字段不适合做聚集索引，可以选择那些在建表时就明确知道会经常进行大范围数据筛选的列，而且最好是选择性较低的列(即有较多重复值的列，性别这种列不算啦)，如有必要可以使用组合索引。理由：聚集索引在数据查询的优势主要在于范围数据查找，把聚集索引弄成唯一的把这个大好优势给白白浪费了。

　　3.具有唯一值的有实际意义的字段：若找不到适合条件1、2的列，那还是乖乖的把聚集索引列建立在唯一列上吧，最好找那种有实际意义的具有唯一性的列，比如订单表可以用订单号作聚集索引，订单明细表使用订单号和产品编号做联合聚集索引。理由：找不到合适的时间字段和较低选择性字段的话，把主键建成聚集索引是我们大多情况下的选择。

　　这里建议把唯一性的聚集索引顺便建成主键，和编码时方法、变量命名一样，推荐列名自解释，即看到列名就知道它就是主键，省得你再去猜，比如订单表你来个自增ID列做主键，再建一个OrderCode列做订单号，用这个表时你得怀疑这个OrderCode是不是唯一滴呢，有木有建立唯一约束呢，同理在订单明细表来个自增列ID也会产生如此疑问，产生疑问还是小事，若是你忘记了在应该唯一的列上建立约束，没准哪天程序控制不好给你个巨大的惊喜。

　　4.自增列ID：前面3中条件都找不到合适的列了还是使用我们的神器自增列ID吧，自增列ID也是我们使用最多的主键(顺便也就成聚集索引了)，而且能较好满足我们大多数需求。自增ID列堪称无所不能，int类型只占用4个字节完全满足窄索引要求，绝对的顺序存储可以有效降低索引碎片，完全符合我们的见表习惯，有用没用来个自增ID列做主键总是没错滴。

　　这里考虑聚集索引的键列主要为查询考虑，有些观点认为应该总是把聚集索引建立唯一列上，这里不敢苟同，诚然有些特殊情况下确实需要这么做，但大说情况下还是建立在选择性较低的列、时间列上比较好，这样才能发挥聚集索引在范围数据查找方面的巨大优势。关于聚集索引在列上重复数据SQL Server需要额外的建立唯一标示用以定位造成查询时的额外开销非常小，小到与其带来范围查找的优势而言完全可以忽略。

　　当然了在选择列时要尽量使用窄索引，也只是尽量而已，主要还是看付出的代价还获得的收益，若有足够的收益啥玩意都可以滴。记住我们滴目标是利用聚集索引提高大范围查询效率。

非聚集索引

　　与聚集索引不同，非聚集索引可以建立多个，这也给我们带来了很大的灵活，毕竟聚集索引就那么一个不可能靠它满足所有需求，更多的我们得依赖非聚集索引。记住非聚集索引不是大白菜，你想键多少就建多少，建立索引是有代价的，任何涉及到索引列的数据修改都会导致索引的修改，索引越多数据的曾、删、改的额外代价也就越大。对于非聚集索引来说，我们的目标是用尽可能少的索引覆盖尽可能多的查询。

非聚集索引的列选择顺序(组合索引):经常被使用为查询条件列>>具有较高选择性的列(选择性越高越好，唯一最好)>>经常排序的列

　　1.经常被使用为查询条件列：我们的查询千变万化，建立索引时要首先考虑有哪些列被经常性的用于各种查询，把使用频率较高的列作为组合索引的第一列(先导列)，若一个查询中没有用到组合索引中的先导列，多数情况下这个索引就不会被使用，因此为了尽可能多的复用组合索引把使用较多的查询列作为组合索引的第一列吧。(关于这点对于聚集索引的组合索引同样适用)

　　2.具有较高选择性的列：这点很简单尽量使用高选择性列作为先导列，如果可以通过第一个条件过滤(随便什么判定逻辑=、>、<、like)，只要能大幅减少数据范围，就把它作为先导列。

　　3.条件1、2、3都确定不了时那就用经常被排序的列吧，我们的很多操作都需要先进行排序才可以进行进一步查询，比如group by，like等操作都是要先进行排序操作才可以完成下一步查询。

　　补充一点：可以把经常被返回的列放到索引的include里面去，在不增加索引键大小的情况下尽可能覆盖尽可能多的列，这样当遇到某些查询，没有用到组合索引的先导列，但又感觉不值得为其建立索引时，若此查询用到得字段被组合索引实现了索引覆盖，可以进行非聚集索引扫描完成查询（当非聚集索引实现了索引覆盖时，进行非聚集索引扫描有着比聚集索引扫描更好的效率）。

　　下面我们用一些示例来简单说明下　　

CREATE TABLE Orders
(
    ID INT IDENTITY PRIMARY KEY,--自增列ID做主键，这样创建默认就成了聚集索引
    OrderCode BIGINT NOT NULL,--订单号
    Price DECIMAL(18,2) NOT NULL,--订单金额
    UserID INT NOT NULL,--用户ID
    [Status] INT NOT NULL,--订单状态
    PostTime DATETIME NOT NULL,--下单时间
    Moblie CHAR(11) NOT NULL,--用户手机号
    [Address] NVARCHAR(200) NOT NULL--收获地址
)

创建一个订单表，按照我们的习惯有用没用来个自增列ID做主键，随随便便也就建立了聚集索引，现在我们来看一下，对于订单表来我们一般都会一组规则生成订单号，而不是简单的使用自增ID，因此我们创建了OrderCode用作订单号，当然了订单号必须是唯一的，因此需要创建唯一约束，过了些日子有其它人用到订单表或你自己用这个表，难免就会有些疑惑，OrderCode需要唯一，在这个表里到底是不是唯一的呢，于是乎你首先查看OrderCode上面是否建立了唯一约束，然后知道OrderCode就是唯一的，这也没啥，但是来个人都要查一遍，过段时间忘了还得再确认一次，很是麻烦，再看看我们那个主键ID，他神马都没干，就在那里呆着，现在拿掉它，于是表变为

CREATE TABLE Orders
(
    OrderCode BIGINT NOT NULL PRIMARY KEY,--订单号
    Price DECIMAL(18,2) NOT NULL,--订单金额
    UserID INT NOT NULL,--用户ID
    [Status] INT NOT NULL,--订单状态
    PostTime DATETIME NOT NULL,--下单时间
    Moblie CHAR(11) NOT NULL,--用户手机号
    [Address] NVARCHAR(200) NOT NULL--收获地址
)

现在不管谁、什么时候看到这个表基本上不会怀疑OrderCode是否唯一了，一个不起眼的小改进，带来了很大的便利，所以主键自解释很有必要的

我们看一下以下几个可能经常用到的查询

--查询1：指定用户特定时间内的所有订单
SELECT * FROM dbo.Orders WHERE UserID=1 AND PostTime BETWEEN '2012-6-1' AND '2012-6-30'
--查询2：指定用户的单个订单
SELECT * FROM dbo.Orders WHERE UserID=1 AND OrderCode=22222222222
--查询3：指定用户特定时间内特定状态的订单
SELECT * FROM dbo.Orders WHERE UserID=1 AND Status=1 AND PostTime BETWEEN '2012-6-1' AND '2012-6-30'
--查询4：指定时间内所有的订单
SELECT * FROM dbo.Orders WHERE PostTime BETWEEN '2012-6-1' AND '2012-6-30' AND Status=1

为了最优查询速度有可能会创建以下索引

索引1：
CREATE INDEX IX_UserIDPostTime ON dbo.Orders(UserID,PostTime)
索引2：
CREATE INDEX IX_UserIDOrderCode ON dbo.Orders(UserID,OrderCode)
索引3：
CREATE INDEX IX_UserIDStatusPostTime ON dbo.Orders(UserID, Status,PostTime)
索引4：
CREATE INDEX IX_PostTimeStatus ON dbo.Orders(PostTime,Status)

最悲观的情况下上面4个索引可能同时存在，为每一个查询建立对应的索引固然可行，但代价未免太大，别忘了索引不是大白菜。因此我们应尽可能的用少的索引覆盖多的查询。来看下上面的索引，如果只创建了索引1，那么只有查询1、3能从索引1受益，查询4没用用到索引1的先导列故不会用到索引1，查询2由于聚集索引存在根本不需要额外的非聚集索引。而索引2由于聚集索引的存在更是完全没必要存在，因此首先干掉索引2。再看索引3，索引3可以覆盖查询1和查询3、查询4，但由于索引列顺序问题使其在应对查询4时基本无效，对查询1虽然有效但效果不尽如人意，我们对索引3做下简单调整，把PostTime列和Status列顺序互换，修改后索引3对原查询3基本没影响，而且对查询1的效率提升也达到最大化

修改后的索引3：
CREATE INDEX IX_UserIDPostTimeStatus ON dbo.Orders(UserID,PostTime,Status)

现在索引3可以很好的完成查询1和查询3，因此索引1现在可以删除掉，现在只剩索引3和索引4了，我们可以看到修改后的索引3由于先导列问题依然无法用于查询4，为了使索引3用于查询4我们再次修改索引3，把PostTime放到索引的第一列，其它列保持顺序不变

再次修改后的索引3：
CREATE INDEX IX_PostTimeUserIDStatus ON dbo.Orders(PostTime,UserID,Status)

可以看到现在索引3也可以有效的用于查询4了，但是由于先导列原因若将Status列和UserID列换货，固然可以提高查询4效率但是会影响查询1，我们考虑到Status列一般也就几种状态，多了也就几十种，相对于UserID来说选择性低高，因此还是把选择性较高的UserID列放在前面，最大化查询1和查询3查询效率。再来看最后一个索引4，索引4和对查询1和查询4起效果，由于查询1已有索引3可用，故忽略对查询1的作用，现在只剩下对查询4的起作用，我们看查询4，索引3和索引4都对其产生效果，毫无疑问索引4对查询4效果更大一些，但考虑到Status列的低选择性和多维护一个索引的代价，索引3已能较好的完成查询4，所以删除索引4。

　　这样一来针对Orders表的4个查询，我们经过对原来4个索引的优化调整后只保留了修改后的索引3，索引从4个变成一个，而查询效率方面却没有受较大的影响，达到了用尽可能少的索引完成尽可能多的查询的目的。

 

　　在上面的演示中我们使用了OrderCode做为聚集索引，通过对非聚集索引的调整较好的完成了查询，在大说数情况下这样就可以ok了，现在我们考虑下若Orders表数据量较大，执行我们的查询4若返回结果达到几万、几十万甚至更多的时候，很可能会导致索引失效从而发生表扫描，这时除非我们队查询4使用的索引实现索引覆盖，不过这基本上不大现实。那如何解决这个问题呢？这时时间列上的聚集索引就开始显示威力了，修改我们的Orders表将聚集索引建立到PostTime列上

--删除原来的聚集索引主键 PK_Orders
ALTER TABLE dbo.Orders DROP CONSTRAINT PK_Orders

--创建非聚集索引主键 PK_Orders 
ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(OrderCode)

--在时间列PostTime上创建聚集索引
CREATE CLUSTERED INDEX IX_PostTime ON dbo.Orders(PostTime)

修改后我们担心的书签查找问题彻底消失了，因为通常导致索引失效的原因都是过多的书签查找，发生大量书签查找时基本上和范围查询脱不开关系，大范围数据查询又肯定会用到时间列，所以推荐时间列做聚集索引。至于说修改了聚集索引后更新，订单查询效率会不会下降，对于这点基本上可以忽略，进行订单查询时一般数据量都会较小，那点书签查找开销完全可以忽略掉，比如我们的查询2。

　　这时我们可能发现我们上面修改的索引3有个尴尬的现状，索引3的先导列PostTime现在成了聚集索引，那么索引3对查询1、查询3、查询4还有木有用，索引3还有木有存在的必要，关于这点一般来说呢非聚集索引最好不要用聚集索引做先导列，很可能建了等于白建这里只是个建议，那么若删除了索引3，查询1、查询3、查询4效率会如何，查询4效率肯定是会提升的、查询1、查询3需要实际测试，当然若可以的话把聚集索引建成联合的，把UserID放进去就不会有此问题了

--在时间列PostTime和UserID列上创建聚集索引
CREATE CLUSTERED INDEX IX_PostTimeUserID ON dbo.Orders(PostTime,UserID)

当然了这种修改还得小心进行，根据实际需求灵活修改，理想的情况下是建立专门的只读数据库复制，在只读库上建立最适合范围查找的聚集索引，在主库上建立最有利于增、删、改的聚集索引，对实时性要求不高的查询全部转移到只读库上执行，相对而言需要大范围数据筛选的查询都不需要多好的实时性，尽可到只读库执行，而主库呢肯定是主要执行实时性要求高的小数据量查询。

 

　　总结：

　　聚集索引的优势在于大范围数据查询效率，因此需要将聚集索引建立在时间列、选择性相对较低并且经常会用于范围查询的列(选择性过低的如性别列肯定不行，过低的选择性列索引建了等于白建，比如你在性别列上集索引以为通过性别列起码一下过滤掉一半数据，范围大大减小你就大错特错了，这点选择性通常查询优化器会直接忽略掉，还不如个表扫描来的快），充分发挥聚集索引大范围数据查询优势。

　　非聚集索引要尽量使用选择性较高的列以尽可能减少返回的数据量，利用组合索引提高索引的复用率，不要建过多的无用索引，如果发现某个表建了很多的非聚集索引，不妨把那些索引、查询摘出来分析合并下，减少没用索引的数量，以提高整体性能。

　　索引建立还需根据实际需要进行选择，本文所述观点在能够适用于大多数情况，但建立好的索引不是一朝一夕能够做到的，理论上成立的事实际应用中往往会事与愿违，索引的有效性还要依靠数据库统计信息等综合考虑，故每当建立索引后一定要查看下查询计划，查看下IO开销，看看查询优化器是否按照我们预期的方式使用了索引。