第13章 乱用索引

“嗨！你有没有时间？我需要你的帮助。”带有俄克拉何马口音的人在电话那头对着你喊，他的声音也顺着数据中心的通风管道传过来。这是你们公司的数据库管理员的头儿。

“当然。”你有点心虚地回答道。他想干嘛？

“是这样，有一个你们的数据库服务在运行，它占了太多资源。”这个DBA继续说道，“我进去看了一下，然后发现了问题。在有些表上完全没有使用索引，而在其他表上索引又多得用不了。

“我们必须解决这个问题，或者给你们一台独立服务器，因为这台机器上的其他服务都无法正常使用了！”

“我很抱歉。事实上，我对数据库了解得不多。”你小心地回答，尽量让这个DBA冷静下来，“我们已经尽了最大的努力来猜测哪些是可以优化的，但显然这些工作只有你们这些专家才能做得好。有没有什么你能做的调整？”

“孩子，我做了所有能做的调整，这就是为什么到目前为止它还没有挂掉，”这个DBA回答道，“留给我的唯一选择是限制你们的程序对数据库的访问量，但我认为你们并不想要这样。我们必须停止猜测，首先我们要弄明白你们的程序到底需要数据库做什么。”

你觉得头都大了，小心谨慎地问道：“你有什么想法吗？坦白地说，我们组里并没有数据库方面的专家。”

“这不成问题。”这个DBA笑道，“你们确实对你们的程序了如指掌，对吧？而这部分是关键内容，不过我可帮不上忙。我会派一个手下到你们那去帮忙安装一些正确的工具，然后我们将找到并消除你们的瓶颈。你们只需要一点点的指导。你很快就会明白的。”

性能是我从那些数据库开发人员那里听到的一个最普遍的问题。只要看一下任何技术会议的议题你即可明白：到处都充斥着工具和技术来让数据库再多工作一点。当我要做的讲座涉及如何规划数据库或写出更为可靠、安全、正确的SQL方面内容时，有可能他们唯一的疑问在于：“好吧，但是这些对性能有什么影响？”而我对此并不感到吃惊。

改善性能最好的技术就是在数据库中合理地使用索引。索引也是数据结构，它能使数据库将指定列中某个值快速定位在相应的行。索引提供了一种简单而高效的途径让数据库能够快速地找到需要的值，而不是野蛮地进行一次自上而下的全表遍历。

软件开发人员通常并不理解如何或何时使用索引。关于何时使用索引这个问题，数据库相关文档和书籍也很少或根本没有清晰的说明，开发人员通常只能猜测如何有效地使用索引。

当我们通过猜测来选择索引时，不可避免地会犯一些错误。对何时使用索引的误解可能会导致如下三种类型的错误：

我们通常都知道，数据库在保持索引同步的时候会有额外的开销。我们每次使用INSERT、UPDATE，或者DELETE时，数据库就不得不更新索引的数据结构来使得所记录的表数据是一致的，然后我们使用这个索引进行查询时就能准确地找到所需要的记录。

我们已经习惯性地将额外开销认为是浪费。因此当我们知道数据库在保持索引同步的时候会造成额外的开销，就想要消除它。一些开发人员于是得出结论，终极的解决方案就是不使用索引。这个建议很常见，但它忽略了一个事实，那就是索引能够通过带给你更多的好处来抵销它的额外开销。

索引不是标准

你知道ANSI SQL的标准中没有任何和索引相关的描述吗？数据存储方面的实现和优化在SQL语言中并没有明确的说明，因此每个数据库产品在实现索引的方式上都有很高的自由度。

大多数数据库产品都使用相似的CREATE INDEX语句，但每个产品都有一定的灵活度来修改并加入一些它们自己的技术。索引没有标准的兼容模式。同样地，也没有索引维护、自动化查询优化、查询计划报表的标准，或者类似于EXPLAIN这样的指令。

要尽可能地了解索引，你只能仔细阅读你所使用的数据库产品的文档。具体的语法和特性对于不同的产品来说有很大的区别，但基本的逻辑和理论都是通用的。

不是所有的额外开销都是浪费。你的公司所雇佣的管理层、法律顾问、会计，那些与办公设施有关的开销，甚至那些对公司收入没有直接帮助的开销都是浪费吗？不，因为这些人和物都从不同的方面为公司做了重要的贡献。

在传统的软件中，每一次更新都会执行上百次表查询操作。每次当你执行一个使用索引的查询时，你就抵消了一部分由于维护索引而造成的额外开销。

索引也能帮助UPDATE或者DELETE语句快速地找到对应的记录。比如，bug_id这个主键上的索引能有效提升下面这条语句的效率：

Index-Shotgun/anti/update.sql
UPDATE Bugs SET status = 'FIXED' WHERE bug_id = 1234;

一条在非索引列上执行的语句会导致数据库执行全表遍历来查找匹配记录。

Index-Shotgun/anti/update-unindexed.sql
UPDATE Bugs SET status = 'OBSOLETE' WHERE date_reported < '2000-01-01';

你只能在使用索引进行查询时才能受益。对于那些你不使用的索引，你无法获得任何好处。这里有一些例子：

Index-Shotgun/anti/create-table.sql
CREATE TABLE Bugs (
        bug_id SERIAL PRIMARY KEY,
        date_reported DATE NOT NULL,
        summary VARCHAR(80) NOT NULL,
        status VARCHAR(10) NOT NULL,
        hours NUMERIC(9,2),
 ①     INDEX (bug_id),
 ②     INDEX (summary),
 ③     INDEX (hours),
 ④     INDEX (bug_id, date_reported, status)
      );

在之前的例子里，有这么几个无用的索引：

有些人为每一列——以及每个组合列——都创建索引，因为他们不知道哪个索引对查询有帮助。如果你对数据库中的每张表每个列都做索引，就造成了很多无法确定收益的额外开销。

接下来常犯的一个错误就是进行一个无法使用索引的查询。开发人员创建了越来越多的索引，尝试着去发现一个神奇的组合方式或者索引选项来加速他们的查询。

我们可以想象数据库的索引使用了类似于电话号码簿的结构。如果我让你去电话号码簿里查一下有哪些人姓Charles，这会是一个很简单的任务。所有同姓的人都列在了一起，因为这就是电话号码簿排序的方式。

然而，如果我让你去查一下谁的名字叫做Charles，电话簿的名字排序方式就帮不上忙了。任何人都可以叫Charles，而不管他姓什么，因此你必须一行行地在号码簿中查找。

电话号码簿是按照先姓后名的方式对联系人进行排序的，就像一个按照last_name、first_name顺序创建的联合索引一样。这种索引不能帮你按照名来进行快速查找。

Index-Shotgun/anti/create-index.sql
CREATE INDEX TelephoneBook ON Accounts(last_name, first_name);

下面有一些无法使用索引进行查询的例子。

如下几点是使用了“乱用索引”反模式的特征。

如果你需要设计一个普通用途的数据库，不了解哪些查询是需要重点优化的，你就不能确定哪些索引是最好的。你可能需要做一些有根据的猜测。你有可能会漏掉一些能有所帮助的索引，也有可能会创建了一些没用的索引。但你必须尽量去尝试。

低分离率索引

分离率是衡量数据库索引的一个指标。它是一张表中，所有不重复的值的数量和总记录条数之比：

SELECT COUNT(DISTINCT status) / COUNT(status) AS selectivity FROM Bugs;

分离率的值越低，索引的效率就越低。为什么？我们可以想象下面这样的情况。

这本书有一个关于不同数据类型的索引：索引中的每一条记录都给出这个单词出现的页的列表。如果一个单词在这本书中频繁地出现，就可能会有很多页码。要找到所需要找到的部分，你就不得不翻到这个列表中的每一页去查看。

索引本身并不会觉得存储那些出现频率很高的单词有什么负担。但如果你需要频繁地在索引和页面间来回查找所需要的内容，你可能就跟从头到尾读了这本书没什么区别。

数据库的索引机制也是类似的，如果一个给定的值出现在这张表的很多条记录中，查询索引比简单地扫描一遍整张表更麻烦。事实上，使用这个索引的开销可能比不使用它还大。

你需要时刻关注你的数据库中索引的分离率，并且抛弃那些低效的索引。

“乱用索引”这个反模式是关于随意创建或抛弃索引的，因此，让我们来分析一下一个数据库，并且找一些好的理由来使用或者丢弃索引。

你可以使用好记的MENTOR方法来分析数据库索引的使用：测量（Measure），解释（Explain），挑选（Nominate），测试（Test），优化（Optimize）和重建（Rebuild）。

数据库不一定是瓶颈

软件开发人员通常的经验是数据库总是程序中最慢的部分，并且是性能问题的根源。然而，事实并非如此。

举例来说，在我曾经参与过的一个项目中，我的经理让我找出为什么程序跑得这么慢，并且他坚持认为是数据库的错。然而在我用了一个性能测评工具去检测程序代码之后，我发现它用了80%的时间来解析HTML，然后找出表单字段并往里面填入对应的值。这个性能问题和数据库完全无关。

在对性能问题下结论之前，先用一些分析工具来测试一下。否则你可能就在做一些过度优化。

你不能在没有信息的情况下做出决定。大多数数据库都提供了一些方法来记录执行SQL查询的时耗，因此可以以此来定位最耗时的查询。

一旦你知道程序中哪些查询耗时最多，就知道该专注于哪方面的优化才能获得最大的效果。你甚至可能发现除了某一个特定的查询比较慢之外，其他的都很快，那这个查询就是性能的瓶颈。这个查询就是你该优化的对象。

如果一个查询很少被调用，那即使它是单次调用耗时最多的一个，也不见得是最耗时间的查询。其他更简单一点的查询可能被调用得很频繁，比你所预期的还要频繁，因此它们所花费的总时间就会更多。专注于这些能够让你事半功倍的查询优化。

记住在做查询性能测试的时候要禁止所有的查询结果缓存。这些缓存被设计用来绕过查询过程和索引使用，因此，如果不禁止这些缓存，你是得不到准确信息的。

在部署程序之后，你可通过进行profile分析来得到更准确的信息。要在实际用户的使用中收集综合数据来查看到底在哪些地方所花的时间是最长的。你应该时刻监控着这些profile数据，以防止不小心造成了一个新的瓶颈。

记住在分析完了之后要关闭profiler或者降低profiler运行的频率，因为这些工具也会造成额外的开销。

已经找到耗时最多的查询请求了，接下来要做的事情就是找出它之所以会这么慢的原因。每个数据库都使用一种优化工具为每次查询选择合适的索引。你可以让数据库生成一份它所做分析的报表，我们称之为查询执行计划（QEP）。

每种数据库的请求QEP的语法都不尽相同。

QEP的报表中包含什么或者报表的形式是什么，没有统一标准。通常来说，QEP会告诉你在一个查询中需要用到哪些表，优化工具是怎么选择索引的，以及按照什么顺序访问这些表。报表可能也会包含一些统计信息，比如每一阶段查询产生多少行记录等。

让我们来看一个简单的SQL查询并请求QEP报表：

Index-Shotgun/soln/explain.sql
EXPLAIN SELECT Bugs.* FROM Bugs
 JOIN (BugsProducts JOIN Products USING (product_id)) USING (bug_id)
 WHERE summary LIKE '%crash%'
   AND product_name = 'Open RoundFile'
 ORDER BY date_reported DESC;

图13-1为MySQL的QEP报告，key这一列表明这个查询仅使用了BugsProducts表的主键索引。同时，最后一列的额外信息表明这个查询会在一张临时表中对数据进行排序，没有任何索引优化。

LIKE表达式强制在Bugs表中进行全表遍历，在Products.product_name列上没有索引。我们可以通过在product_name上创建一个新的索引以及改用全文搜索的方案来优化这个查询。参考第17章内容。

QEP的信息是由数据库开发商决定的。在本例中，你应该仔细阅读MySQL手册中“Optimizing Queries with EXPLAIN”一章中的说明来理解对应的报表的含义。 http://dev.mysql.com/doc/refman/5.1/en/using-explain.html。

现在已经有了查询优化工具的QEP报表，你应该仔细地查找那些没有使用索引的查询操作。

有些数据库会用一些工具来做这件事，它们会收集查询统计信息并且提出一系列的修改建议，包括创建那些被你漏掉但能起到较好效果的索引。比如：

即使没有自动化工具，你也可以学习如何辨识一个索引是否有利于提高搜索效率。你需要仔细阅读你所使用数据库的手册来更好地理解QEP报告。

索引覆盖

如果一个索引包含了我们所需要的所有列，那就不需要再从表中获取数据了。

想象一下，如果电话簿的条目中只包含一个页码，在你找到一个名字之后，你不得不翻到对应的页上才能得到所需要的号码。如果将这个过程整合为单步操作会更合情合理。由于电话簿是排序的，所以查找一个名字是很快的，然后在一个条目中可以再包含一个字段存储电话号码，甚至存储地址。

这就是索引覆盖的作用。你可以定义让一个索引包含额外的列，即使这些列对于这个索引来说并不是必须包含的。

CREATE INDEX BugCovering ON Bugs (status, bug_id, date_reported, reported_by, summary);

如果你查询的列都包含在这个索引的数据结构中，数据库就可以通过只查询索引生成结果。

SELECT status, bug_id, date_reported, summary    FROM Bugs WHERE status = 'OPEN';

数据库并不需要去查询对应的表。虽然你不能在任何地方都使用索引覆盖，但只要使用它，对性能来说就是一个极大的提升。

这一步非常重要：在创建完索引之后，需要重新跟踪那些查询。需要确认你的改动确实提升了性能，然后就能确定工作完成了。

你可以使用这一步骤来给老板留下好印象，并证明你所作的优化是有效的。你肯定不希望周报上写道：“我尝试了每个我想到的办法来解决程序的性能问题，然后我们只能等等看反馈……”相反，你现在有机会这么写周报：“我发现可以在一个高活跃度的表上创建一个新的索引，并且我将核心查询性能提高了38%。”

索引是小型的、频繁使用的数据结构，因而很适合将它们常驻在内存中。内存操作的性能是磁盘I/O操作的好几倍。

数据库服务允许你配置缓存所需要的系统内存大小。大多数数据库的默认配置都很小，从而能保证数据库在大部分操作系统上都正常工作。通常情况下我们需要调高这个缓存大小的设置。

需要使用多少内存做为索引缓存？这个问题没有标准答案，因为它取决于你的数据库的规模和服务器的内存大小。

使用索引预载入的方法可能要比通过数据库活动本身将最频繁使用的数据与索引放入缓存更有效一点。比如，在MySQL中，使用LOAD INDEX INTO CACHE语句。

索引在平衡的时候其效率最高，当你更新或者删除记录时，索引就逐渐变得不平衡，就如同文件系统随着时间的推移会产生很多磁盘碎片一样。在实际运行中，你可能看不出一个平衡索引和一个有些不平衡的索引的区别。但我们想要最大限度地使用索引，因此要定期对索引进行维护。

就像索引的很多其他特性一样，每个不同的数据库都使用自己特有的术语、语法和功能。

多久需要重建一次索引？你可能听到诸如“每周一次”这样空泛的回答，但事实上对于不同的程序来说并没有统一的答案。具体的时间取决于你对指定的表所做的会引起不平衡操作的频率。同时也取决于这张表有多大以及理想状态的索引是否那么的重要。花上几个小时重建一张很大但很少使用的表的索引，只获得了1%的性能提升，这样做值得吗？所有的判断都需要你来做决定，因为你是最了解这些数据和数据操作需求的人。

很多关于最优化使用索引的知识都是根据不同数据库而论的，因此你需要仔细研究所使用的数据库。你手上所有的资源包括数据库手册、书籍和杂志、博客文章和邮件列表，以及很多自己的经验。最重要的规则是千万别瞎猜索引的使用方法。