关于SQL性能优化，你只会简单的,,也能够看懂

本文同时发表在知乎和我的个人网站上。 欢迎关注

我之所以将其限制为“入门”，是因为我不是SQL专家，但最近在性能优化方面积累了一些经验。 虽然不深，但是对于我们日常需要解决的一些SQL语句的性能问题来说已经足够了，所以分享一下。 出来供大家参考。 自私一点，即使你很长时间不使用这个技能，在不熟悉之后通过查看自己的文章也能很快上手。

之所以仅限于“查询语句”，是因为SQL中的内存使用、查询编译、死锁等方面还有优化的空间，这不是本文涉及的内容。

“入门”的另一个含义是我会重点讲解原理。 我发现无论是前端性能优化还是SQL性能优化都无法与所见即所得的编码相比。 可能是因为工具提供的信息有限，也可能是因为性能瓶颈是祖传代码狗屎山。 大多数时候，你需要适应。 有时你可以通过剥茧找到问题的根源，有时你只能通过彻底重写代码才能得到一些缓解。 无论你使用什么方法，你都需要了解其背后的工具原理。即使你只知道一个简单的、

本文涵盖两部分：索引和执行计划。 虽然索引可以解决我们90%以上的性能问题，但是我们仍然需要知道何时何地添加索引，所以我们需要通过阅读执行计划来找到这方面的技巧。

为了说明问题，本文将使用官方提供的示例数据库以及其中的三个表。 三个表都有字段，我们可以通过字段将同一个人的信息关联到一起。

仔细扫描

简单地将数据库比作一本书并不夸张。 想象一下，如果您需要在一本没有目录的书中查找一行文本。 你唯一能做的就是逐页搜索。数据库也是这样工作的。 对于没有任何索引的表，只能通过扫描全表的数据来找到匹配的数据。

例如，我删除表中的所有索引，然后搜索特定的电话号码：

  SELECT *
  FROM Person.PersonPhone
  WHERE PhoneNumber = '156-555-0199';

执行计划向我们展示了如下流程：

由于后面我们会讲执行计划，所以现在可以把执行计划看成是SQL语句的执行过程。 上图中的Table Scan告诉我们它扫描了整个表。 并且在整个执行过程中，这一步占用的资源最多：Cost：100%。 这里的成本只是一个抽象单位。 它并不代表CPU或I/O单一维度的消耗，而是对各种资源的统计结果。

其实上面过程中的100%并不意味着扫描操作效率低下，因为它只涉及到单表的查询操作。 即使对于索引表上这样一个简单的查询，您也可以看到 Also Cost: 100%。 例如，如果我查询带有 [] 索引的表：

  SELECT *
  FROM Person.Person
  WHERE BusinessEntityID = 10;

结果执行流程如下：

非扫描类型的Index Seek（我稍后会解释，这里你可以认为它是一种优于扫描的操作类型）的消耗也是100%。

但如果我们对sum表进行联合查询，查询效率会明显更高：

  SELECT *
  FROM Person.PersonPhone AS PersonPhone
  JOIN Person.Person AS Person ON PersonPhone.BusinessEntityID = Person.BusinessEntityID
  WHERE PhoneNumber = '156-555-0199';

扫描消耗了所有操作的 91%

所以scan是我们可以识别的一个优化点。 当您发现表缺少索引，或者在执行计划中看到扫描操作时，请尝试通过添加索引来修复性能问题。

重点读物

通常SQL查询数据时，会首先从内存中的缓存中查找。 如果没有找到，就会继续在磁盘上寻找。 前者叫读，后者叫读。 鉴于从内存读写比磁盘更高效，我们当然希望尽可能避免任何读取。

read 到底是读什么、写什么？ 是页。 页是数据库中数据组织的最小单位。 我们只需要了解这个深度即可。 至于页面如何组织，页面的数据结构并不重要。 因此，读取次数应尽可能少。 默认情况下，您不会看到读取指示器的输出。 您可以使用 SET IO ON 打开此监控。 比如查询一个没有索引的表，我们的查询语句如下：

SET STATISTICS IO ON
GO
  SELECT *
  FROM Person.PersonPhone
  WHERE PhoneNumber = '156-555-0199';
SET STATISTICS IO OFF
GO

读取到的信息如下：

(1行)表''。 扫描计数 1、读取 158、读取 0、预读读取 0、lob 读取 0、lob 读取 0、lob 预读读取 0。

一旦我添加了Index作为key（如果你对index一无所知，这里可以将其理解为一种优化方法），上面语句的执行结果就变成了：

(1行)表''。 扫描计数 1、读取 2、读取 0、预读读取 0、lob 读取 0、lob 读取 0、lob 预读读取 0。

如果读数太高，可能（但不一定）表明存在一些问题：

选择读取的另一个好处是，作为衡量性能的指标之一，它不会像 CPU 时间那样波动那么大。

不过read的参考价值没有执行计划高。 一方面，它是单向的，即可以通过SQL语句获取读取的值，但不能通过值反向读取该值。 从这一点来看，执行计划更适合我们排查问题； 另一方面，它并不总是准确地反馈问题。 如果去掉上面查询中的where语句，你会发现添加索引前后的读取变化不大。

无论如何，reads 都可以作为我们的参考指标之一。

指数 指数 & 指数

最后我们可以进入主要主题索引了。 Index的工作原理非常简单。 如果我们把数据库比作一本书，那么索引就是这本书的目录，它可以帮助你快速定位数据。

在上表中，如果我们想要查找某个公司的行，我们需要检查表的每一行，看看它是否与预期值匹配。 这是全表扫描操作，效率很低。 如果表很大，只有几行符合搜索条件，那么整个扫描过程的效率会极低。

我们可以给这个表添加一个索引：

该索引包含广告表中每一行的一个条目，并且索引条目按值排序。 现在，我们可以使用此索引，而不是逐行搜索整个表来查找匹配项。 假设，我们要查找公司编号为 13 的所有行。我们开始扫描索引，发现 3 个值属于该公司。 然后我们得到 14 号公司的索引值，该值比我们要查找的值稍大一些。 由于索引值已经按顺序排列，因此当我们读取包含 14 的索引行时，我们知道无法找到更多 13 的匹配项，因此可以退出搜索过程。 可见，使用索引提高效率的一种方法是我们可以知道匹配行在哪里结束，从而跳过其余的； 使用索引提高效率的另一种方法是使用定位算法，无需从索引开始位置的线性扫描，将直接找到第一个匹配项（例如，二分查找比扫描快得多）。 这样我们就可以快速定位到第一个匹配值，从而节省大量的搜索时间。

但在SQL中，索引分为几类。 索引是最常用的：表中的数据会根据索引进行物理排序。 由于物理顺序关系只有一种，所以一张表只允许有一个索引。 当你给表添加键约束时，数据库会自动根据键为你创建索引。

我们可以添加一列作为key的索引，然后再次执行上面的查询语句

  SELECT *
  FROM Person.PersonPhone
  WHERE PhoneNumber = '156-555-0199';

可以看到执行计划变成了Index Seek，如下图

寻求是最有效率的。 我们应该尽量让查询语句执行seek操作。 它不再像扫描那样逐行扫描，而是像书籍目录一样直接到目的地检索所需的数据。

但索引查找在任何情况下都不会生效。 例如上面的索引情况，根据条件查询：

  SELECT *
  FROM Person.PersonPhone
  WHERE BusinessEntityID = 4511

你会发现执行计划是Index Scan

索引扫描是指数据库通过索引获取所有行，然后进行扫描。 如果比较索引扫描和表扫描，两者的读取是相似的。

配置索引和索引没有区别。 使用时，你还会在执行计划中看到Non-Index Seek。 明显的区别是不会影响原表的顺序。 虽然看似相同，但背后其实有着千丝万缕的联系。 了解这些联系将有助于我们判断应该在何时适当添加哪个索引。

指数如何运作

想象一下，有一组27行的单列数据，由于页面大小有限，分为9页。

为它们添加索引后，索引的数据结构如下

当您想要查找值 5 时，将从顶部节点开始搜索。 由于 5 介于 1 和 10 之间，因此搜索将继续到左侧分支的下一个节点。 又因为5落在4和7之间，搜索就会到下一层以4开头的节点。 最后从叶子节点中找到5

事实上，我们忽略了一些细节。 索引结构如下：

从图中不难看出，每一级的节点都是双向链表，叶子节点存储的是表的真实数据。

但索引的存储结构略有不同。 叶子节点由索引信息（索引页）而不是数据信息（数据页）组成。 索引需要使用row来定位对应的数据行（你可以理解为指针）。 对于堆表（没有索引的表），row指向每行数据的RID（行）； 对于非堆表， row 指向索引。

空间有限。 根据以上知识点，我们可以总结一下什么时候应该使用什么索引：

指数

清除所有索引后，将其添加为索引，然后执行初始查询语句：

  SELECT *
  FROM Person.PersonPhone
  WHERE PhoneNumber = '156-555-0199';

你将得到如下的执行计划：

除了Index Seek之外，右下的操作占比最大。 触发的原因很简单：当数据库决定使用索引进行查询，而需要查询的列信息不在索引中（既不是索引的键，也不是列表中）时，该操作就会被触发。 这意味着它会根据索引（非堆表为索引，堆表为RID）关联的行找到对应的行数据，然后从中读取你想要查询的列数据。 整个过程除了消耗在索引页上的读操作外，还消耗了额外的数据页上的读操作。 可以想象，如果数据库在查询过程中使用了索引，则永远不需要它，因为索引的叶子节点就是数据页。

如果索引能够提供查询所需的全部列信息，就意味着可以省略访问数据页的操作，这种类型的索引就可以称为索引。

我们可以将除索引键之外的需要查询的列放入列表中，这样也可以解决上面的问题：

总结

本来想写一下join效率的问题（对比hash join/loop/merge join），但是思考数据库采用什么样的join后，就不是我们能控制的了。 事实上，数据库是否会真正使用我们的索引是我们无法控制的。 执行计划是内部计算的。 残酷地说，每个执行计划可能会根据资源、数据和索引状态的不同而有所不同。 。 但至少索引更可控。大多数性能问题都可以通过索引解决