SQLServer索引维护（1）系统常见的索引问题_MySQL

前言：

在很多系统中，比如本人目前管理的数据库，索引经常被滥用，甚至使用DTA（数据库引擎优化顾问）来成批创建索引（DTA目前个人认为它的真正用处应该是在发现缺失的统计信息，在以前的项目中，用过一次DTA，里面提示了很多列缺少统计信息，后来在不改动其他操作的前提下，把这些统计信息手动建上去，性能提升非常明显。关于统计信息将另开文章介绍）。一个表甚至有20多个索引（索引的数量并没有标准，但是要尽量合理，每个索引都应该能支撑大量查询或者增删改中的查询功能才有存在价值）。索引过多带来了服务器的沉重压力，有这么一句话：不合理的索引比没有索引更加惨。可见索引不能随便建。

那如何知道哪些列需要索引？哪些索引可以删除或合并或修改？虽然网上有不少类似文章和方法，但通常比较零散，用起来比较费力。本文结合个人工作经历和网上资料，经过实践，得出一个个人认为行之有效的方法。在后续的工作和学习中，若有改进，将同步修改本文。

注：如非特殊说明，这里说的索引都是非聚集索引。

介绍：

绝大部分人都知道，索引的好处，但是极其少人深刻感受到它的坏处。我一直这么认为，对于SQL Server中的索引（甚至一切数据库管理系统，再甚至世间绝大部分事物）而言，并没有绝对的好或者绝对的坏。就好像给你一把斧头和一个刀片，让你砍一棵树和切割一张纸，你非要用刀片砍树，斧头切纸，然后说刀片和斧头都是烂东西，显然不合理（这不是段子也不是脑筋急转弯，别纠结太多）。

SQL Server既然提供了一些供你选择的功能，那么就有它的适用场景和不适用场景。用好了，查询能从几个小时提升到几秒，用不好，可以把一个查询从几秒降到几个小时。本文不打算过于深入地讨论索引内部机制，更详细的信息可以翻阅《SQL Server 性能优化与管理的艺术》中第六章的内。这里只是想表达，关于索引：建要建得有理有据。删/合并同样也要有理有据，看到不合适就删，觉得有效果就加，往往只能得到反效果。

所以合理使用索引，本文内容分几部分：

第一部分介绍索引过多的问题；第二部分介绍索引不足的问题；第三部分是索引不合理的问题；最后一部分是对前面三个问题进行实操的方法演示。但是由于本部分篇幅较长，截图较多，为了避免阅读疲劳，所以独立一篇演示。

本篇的初衷如下：可以看到这是个死循环，由于下面的原因导致表上的索引混乱，从而影响整个系统的效率。下面将一一介绍。

 

索引不足：

索引不足很好理解，要么除了聚集索引（有时甚至没有）之外完全没有索引，要么有索引，但又不能很好地支持表上的操作。

先说只有聚集索引的情况（对于堆表，现实环境下一般都很少出现，出现了自己思考一下是否有必要存在堆表）。这种情况通常出现在刚创建的表，或者使用次数较少的表。但是随着后续功能的扩展和表上数据的增加（这里假设表本身列数就比较多），每次都使用聚集索引会变得越来越低效。因为很多查询仅需要少数几列，而聚集索引会加载表的所有列，同样消耗了内存资源。此时需要添加合适的非聚集索引来支持查询（这里的查询不仅仅包含SELECT）。那么就进入下一个情况：非聚集索引不能很好地支持表上的操作。

当使用非聚集索引时，可能由于某些网上广为流传的所谓“军规”、“铁律”，把非聚集索引的列限制在很少的数量。这样在系统的发展过程中，很容易出现某些重要且大数据量查询所需的列并没有包含在非聚集索引中，导致优化器又选择聚集索引或者键查找（key lookup）。这两种查找并不代表有问题，但是到了一定程度，这两种查找反而没有直接从非聚集索引中获取数据来的高效。

索引不足说白了就是没有覆盖查询所需的列，这种覆盖有视乎现实情况而定，并不一定每个系统都能有完整覆盖全部查询的索引，特别是对核心表，上面的查询可能各式各样，针对所有查询建立索引会导致下面所说的索引过多的情况，最终进入一种恶性循环。

另外，索引不足的解决方案通常就是建索引，而建索引这个操作最好由有经验的少数人负责，否则谁都能建索引，并且缺乏沟通，A为了他自己的查询建一个，B本身可以通过修改A创建的索引来支持自己的查询，却又单独加一个，最后又出现了索引过多和索引不合理的情况。很不幸地再次进入恶性循环。

针对索引不足的情况，有时候除了建索引之后，还可以考虑合并或修改现有索引，更有甚者，并不是索引引起的性能问题，这种情况下可以把注意力移到统计信息上或语句优化（其实语句优化和数据库设计优化应该先做，并且成效最大，但是这个主题太广，所以不打算在这里介绍，我们还是假设语句和数据库已经没有多大提升空间，把精力集中在索引上面）。

索引过多：

所谓的索引过多，是指单表上非聚集索引数量太多，至于多少才为太多，这个实际上没有标准，虽然某些书上有说过单表上不要超过6个索引，但是那些书是通常是很久远的书籍（大概10年前左右），那时候的硬件资源还没比较紧张，不可能支持太多索引。但是即使是今天的硬件资源，随着系统的复杂程度和数据量的增长，硬件资源也容易不足，同时索引过多也会带来很多风险。索引过多的风险主要有以下几个：

降低增删改操作的性能。由于增删改操作特别是删、增两个，往往引发聚集索引键的变更，标上的非聚集索引叶节点存储了聚集索引键，所以也会连带被更新。而这些非聚集索引往往是没有提高这些操作的性能，反而对操作带来负面影响。这就是所谓的牵一发而动全身。但是注意，有些增删改操作带有WHERE条件，这些情况下，有些索引能提高性能，比如百万级别的表中删除一行数据，如果没有索引（假设连聚集索引也没有），那么就只有扫描全表删除，在正式环境下试过就知道，那速度简直无法接受。过多索引还导致了数据库“体积”变大。本质上，数据库的性能问题几乎都是由于“体积”过大导致的。我们知道，聚集索引就是表本身，表多大，聚集索引几乎就有多大，根据B-Tree的层级，可能会比表更大（不过一般大不了多少）。而非聚集索引叶子节点由于存储了索引键的数据，索引也有一定体量。体积大又引起了存储压力和维护开销。由于SQL Server不直接访问磁盘，每个操作都需要先把数据从磁盘加载到内存中，内存的容量相对磁盘来说又比较小，一旦索引很大，查询过程造成的内存压力也会随之而来，对数据缓存和计划缓存都有不可忽视的影响。对聚集索引的维护，比如重建，会引发非聚集索引的连带效应。索引越多速度越慢。很多系统并没有充足的时间让你任意维护索引。加大了SQL Server优化开销。虽然通常优化引擎不会花太多时间在优化上，最起码相对于执行而言，优化的时间你可能感知不到，但是过度优化、编译重编译，都会对CPU带来压力。当索引很多时，优化器必须针对查询及上面的统计信息进行分析，如果有一些索引都能支持这个查询时，优化器又必须比对哪个索引开销更低。说白了，要优化器选择也有选择恐惧症，太多的选择它就会折中，不一定会花更多资源去选择最优方案，万一当时其他操作的影响导致优化器选择了不合理的索引，性能反而更差。统计信息更新开销，默认情况下，SQL Server会对索引定义中的列计算统计信息，这种统计信息是索引级别的，每个索引有自己的统计信息，索引越多，由于数据变更或者其他变更带来统计信息更新时，需要更新的范围就越大，时间越久。索引失效：这是我写这系列文章的原因，因为本人运维的数据库不定期出现某些功能卡的情况，通过重建索引，功能恢复一定时间的稳定，但是过一段时间后又出现了。从表象看来，这类似与索引失效的情况，但是目前官方资料中并没有出现索引失效的描述，所以可以认为索引失效其实只是个表象，更多的是优化器对索引选择的混乱。本人将抽时间再起一个系列的文章专门讨论这部分内容。通过降低索引的数量和提高每个索引的重用度，这种情况目前再无发生。

简单来说，对于OLTP数据库，单表索引不应该过多。在这个快餐文化盛行的时代，很多人都希望直接得到标准值，而不去做实际分析，如果非要给出一个值，根据个人经验：

对于核心表：所有索引不要超过7个。对于普通表：所有索引不要超过5个。对于小型表：所有索引不要超过3个。

当单表有很多索引（比如上面提到的20多个）时，就应该考虑是否真有必要维持这种数量，在本系列的最后部分会给出解决方案。当然极端情况下，20多个索引的表性能表现也不一定不好。切记：具体情况具体分析。

综合前面两点，总结成一句：过犹不及！！

索引不合理：

其实上面两种情况，很多时候是因为索引设计不合理导致的。这里抛开聚集索引不说，对于非聚集索引，如果索引设计不合理，那么就没办法起到预期作用，大部分情况下表现为没有作用，这跟索引不足的结果是类似的。这时候很多人就会选择再建一个新的索引，如果对于索引的认识不足，可能建出来的新索引同样不能支持查询，导致再次创建索引，久而久之，又出现了索引过多的情况。

另外一种情况是，在中间接手的项目中，索引设计不合理，索引数量过多，那么很多人（包括我）就会选择“删”。当然既然我写这文章，就代表不会很暴力地删，我的原则是：建要有理有据，删也要有理有据，改同样也要有理有据。说不出理由，就别动它。因为存在，必然有理由，只是合理不合理而已。当删除索引之后，又可能出现了索引不足的情况，随之进入前面所说的一个循环过程中。为了避免思维混乱，对于上面的循环讨论就到此为止。下面来看看索引不合理的情况。

通常来说，索引不合理由于三种情况：

1．索引的列过多，当索引的列很多时，优化器可能会偏向于使用聚集索引，因为聚集索引本身存储了数据，而非聚集索引有时候需要二次定位，效率反而不好。同时虽然索引本身并不有序，但是聚集索引却能在某些情况下为查询排序提供帮助。另外，说白了，如果你的非聚集索引包含了大部分表的列，干嘛还要再建一个，直接用聚集索引好了。建非聚集索引很多情况下就是为了减少索引体积（因为通常列数更少），建立过多列的索引会得不偿失，哪怕性能有一点点提升，考虑到后续维护开销，也不建议这样做。

2．多列索引中，首列顺序不合理：有一定经验的SQL Server从业者应该知道索引的首列具有极其重要的地位。这要扯到统计信息，在这里暂时不详述。由于统计信息直接影响优化器在优化过程中对索引的选择，如果索引的首列使用了选择度很低（比如性别这类只有极少数不同值的列，选择度就很低）的列，那么有可能出现明明可以通过索引查找高效定位所需数据，却走了索引扫描，甚至优化器不用这个索引用其他非聚集索引或者聚集索引，使得这个索引形同虚设，失去了建立它的意义。关于这部分的讨论放到下面进行，总之，多列索引的首列，应该尽可能使用选择度高、数据类型尽可能“窄”、存储之后很少变化的列作为首列。

3．包含索引和覆盖索引：在SQL2005之前，索引只有两类，聚集索引和非聚集索引，但是从2005开始，非聚集索引就出现了很多变种，其中一个变种就是包含索引。它被广泛用于图形化执行计划中的缺少索引提示中。目前为止，本人见过的图形化执行计划中缺少索引的提示中，有且仅有包含索引，而没有其他任何类型的聚集索引和非聚集索引。因为包含索引的广泛使用，慢慢地，很多人已经忘记了其前身——覆盖索引：

a)覆盖索引指的是一个非聚集索引，这个索引包含了查询所需的所有列。

b)包含索引指的也是一个非聚集索引，但索引定义列（即在表名之后，在INCLUDE关键字之前的列集）中存储了表的一些列，而INCLUDE后面也定义了一些列。

c)两者最主要的区别在于包含索引中，出现在INCLUDE关键字后面的列（也称非键列）的值仅存储在非聚集索引的叶节点中，而覆盖索引中出现的所有列的键值都一直存储在每层节点中。从一定程度上，包含索引的体积更小，并且在INCLUDE后面的列顺序无关紧要，同时可以避免常规非聚集索引对16列，900bytes的固有限制。总的来说，包含索引有很多的优势。

但是恰恰因为这些优点，很多人建索引的时候都不管三七二十一，全用包含索引。在这里提出这种说法，显然是表示不能滥用。因为包含索引相对于覆盖索引而言，有以下几个不足：

a)移植性：虽然几率微乎其微，但是万一需要把数据库降级回2000，包含索引会报错。这里就引出另外一个编程规范的问题：T-SQL并不完全遵循标准SQL，其有自己独特的地方，对于一些写法，如果都能满足需求，那么优先选择标准写法而不是T-SQL自己的写法，这样也具有更好的移植性。如和!=，前者是标准语法，后者不是。此时建议使用来表达“不等于”。

b)覆盖查询的功能：使用AdventureWorks2008R2示例数据库，使用下面代码

1. 创建演示环境：

use AdventureWorks2008R2
go
--为避免影响演示环境，创建一个新表dbo.Person用于测试
select * into dbo.Person
from Person.Person
GO
--参照原表创建主键：
ALTER TABLE dbo.Person ADD CONSTRAINT
    PK_Person PRIMARY KEY CLUSTERED
    (
    BusinessEntityID
    )
GO 

2.编写查询：先不建任何索引，通过实际执行计划可以看到只走聚集索引

--写一个演示查询，由于一开始没有非聚集索引，所以查询会使用聚集索引
select Title,FirstName,MiddleName,LastName
from dbo.Person
where FirstName like 'o%'

 

3.添加一个覆盖索引，覆盖查询中WHERE条件和SELECT条件的列

--这里为了避免涉及过多内容，对索引首列做了限制，用于满足where条件
CREATE NONCLUSTERED INDEX IX_Person_FirstName
ON dbo.Person(FirstName,Title,MiddleName,LastName)
GO
 
--添加一个覆盖索引，覆盖查询中WHERE条件和SELECT条件的列
select Title,FirstName,MiddleName,LastName
from dbo.Person
where FirstName like 'o%'

从下图可见，索引可以覆盖我们的查询：

 

4.删除上面的索引，添加一个包含索引，但是WHERE条件中的FirstName列只出现在INCLUDE中：

 

--创建包含索引
CREATE NONCLUSTERED INDEX IX_Person_FirstName
ON dbo.Person(Title) INCLUDE(FirstName,MiddleName,LastName)
GO
 
--删除上面的索引，添加一个包含索引，但是WHERE条件中的FirstName列只出现在INCLUDE中
select Title,FirstName,MiddleName,LastName
from dbo.Person
where FirstName like 'o%'

查看执行计划，可以看到走的是索引扫描，因为虽然都是同样的列，但是由于INCLUDE中出现的列通常仅用于协助SELECT而不是协助WHERE，所以没有起到我们期望的作用。

 

从上面的演示中我们看到，虽然类似，但是如果设计不合理，同样会造成索引问题，读者可以自行测试包含索引中，SELECT的列出现在INCLUDE中，而WHERE的列出现在INCLUDE前的效果，可以看到这次这个包含索引就满足我们的期望了。并且你可以测试，INCLUDE中的列顺序可以和SELECT中的不一致，在本例中不影响效果。

总结：

由于索引是非常大和非常高深的话题，本人读过一本600页的全英的关于SQL Server索引的书籍，密密麻麻的600页却还没有完全解答我的疑问，所以这里我过滤了很多细节问题，把讨论和演示集中在我希望表达的主题上。下一篇文章将演示对这些索引问题的处理手段。

另外需要提醒的是，一个系统的索引问题不可能只有这么三类，但是基于实操出发，把最近个人工作过程中发现的问题整理分享，在后续过程中会不断完善这个话题。广告时间：更多信息可以阅读本人出版的书籍《SQL Server 性能优化与管理的艺术》。

下一篇文章： SQL Server 索引维护（1）——如何获取索引使用情况