SQL 性能第 4 篇：其他注意事项

今天我们简要概述我们尚未讨论的 SQL 优化的其他几个方面。以及对我们 SQL 性能和优化的四个部分文章进行总结。希望对大家在 SQL 性能优化方面有所帮助。

今天的文章总结了我们对 SQL 性能和优化的四个部分。 第一部分介绍并解释了关系优化；第二部分回顾了查询分析，介绍了单表访问路径的制定方法；第三部分介绍了多表 SQL 优化……今天我们简要概述了我们尚未讨论的 SQL 优化的其他几个方面。

1 使用索引避免排序

为了满足特定类型的 SQL 请求，DBMS 可能需要对数据进行排序。但是排序的成本太高，应该尽可能避免。关系优化过程理解排序的开销，并将其转化为优化决策。

也就是说，可以做一些事情来帮助优化器。dba 可以通过在需要排序的列上创建索引来设计索引，从而避免排序。当索引存在时，关系优化器将尽量使用这些索引来避免排序。当指定下列子句时，可能会发生排序：

• DISTINCT：当指定了这个子句时，DBMS 要求结果数据的每一列都按顺序排列，以便从结果集中删除重复的行。

• UNION：这个操作要求对每个选择列表中的列进行排序，因为结果集不能有重复的行。如果 DBMS 支持 INTERSECT 和 EXCEPT，那么同样的考虑也适用于这些操作。

• GROUP BY：当指定这个子句时，DBMS 要求按照指定的列对数据进行排序，以便聚合数据。

• ORDER BY：当指定这个子句时，DBMS 将确保按照指定的列对结果集进行排序。

考虑以下 SQL 语句：

SELECT   last_name, first_name, middle_initial, empno, positionFROM     employeeWHERE    position in ('MANAGER', 'DIRECTOR', 'VICE PRESIDENT')ORDER BY last_name;

如果 last_name 列上存在索引，查询可以使用该索引并避免排序。使用索引来避免排序可以抵消排序所需的额外 CPU 成本和索引访问所需的额外 I/O 成本。当然，如果无论如何都要使用索引，这个选择是显而易见的。使用索引实际上是否比扫描数据和排序快取决于几个因素，包括符合条件的行数、排序的速度和索引的特性（例如集群）。

此外，在编写指定 UNION 操作的查询代码时，要注意检查应用程序的需求。如果没有重复的行，或者不关心结果集中是否存在重复，可以指定 UNION ALL，以避免重复删除的排序。

2 为什么没有选择索引？

有时会出现这样的情况，你认为优化器应该选择索引，但是它没有选择。任何原因都可能导致优化器避免使用索引。

请参考以下建议选择索引的方法：

• 查询是否指定了搜索参数？如果没有谓词使用搜索参数，优化器就不能使用索引来满足查询。

• 你加入了大量的表？某些 DBMS 中的优化器在加入大量表时可能会产生不可预测的查询计划结果。

• 统计当前？如果插入、更新和/或删除了大量数据，则应该重新捕获数据库统计信息，以确保优化器拥有最新的信息，可以根据这些信息制定查询计划。

• 你正在访问一个小表吗？对于非常小的表，简单地访问所有数据并进行排序可能比使用索引更有效。

• 你正在使用存储过程吗？有时候 DBMS 提供了一些选项，这样一个存储过程在编译后就不会为后续的执行重新制定查询计划。你可能需要重新编译或优化存储过程，以利用最新的统计数据、新索引或任何其他相关的数据库更改。

• 是否需要其他谓词？不同的 WHERE 子句可能使优化器能够考虑不同的索引。

有些表可能根本没有定义任何索引。每个表必须至少有一个索引，这是一个神话。下面是一些在表上不定义索引的情况：

• 当所有访问都检索表的每一行时。因为每次想要使用该表时都会检索每一行，所以索引（如果使用）只会添加额外的 I/O，并且会降低而不是提高性能。虽然不是很常见，但你可能会在组织中遇到这样的表。

• 一个只有几页数据、没有主键或惟一性要求的非常小的表不需要索引。小型表（可能有 20 到 30 页左右）可能不需要索引，因为简单地读取所有页面已经非常有效。

• 当性能无关紧要且表仅很少被访问时。但是，在现实世界中，你什么时候有过这种需求呢？

除了这些情况，你很可能希望在每个表上构建一个或多个索引，并尝试让 DBMS 使用这些索引。

3 散列访问

一些数据库系统还支持散列。哈希是一种算法，它将一个键（一个或多个列）转换成一个小数字，通常是磁盘上的存储位置。哈希函数返回的值称为哈希值、哈希和或简单的哈希。键值由哈希算法（也称为哈希函数、哈希例程或随机化器）处理并转换为存储位置。当插入数据时，哈希算法告诉 DBMS 在哪里物理存储数据；当关键字访问数据时，算法会精确地告诉 DBMS 在哪里可以找到数据。

散列甚至比直接索引查找更有效，因为它通常导致更少的 I/O。不需要遍历多个索引页（从根到非叶页，再到叶页，然后到数据），散列将键转换为磁盘上的特定位置。在最佳情况下，这将导致一个 I/O。如果哈希算法为多个键生成相同的位置，就会发生哈希冲突，这需要额外的 I/O。

哈希主要用于为少量数据优化随机 I/O，比如查找代码表值或根据其主键值访问一行。然而，散列在关系系统中并不常见。

如果存在散列，并且当散列存在时，优化器将在制定访问路径时考虑使用任何散列结构。

4 并行访问

关系优化器可以选择并行运行查询。当 DBMS 调用查询并行性时，将调用多个并发任务来访问数据。DBMS 支持三种基本类型的并行：

• I/O 并行性允许为单个查询启动并发 I/O 流。运行并行 I/O 任务可以显著提高 I/O 绑定查询的性能。将查询的数据访问分解为并行执行的并发 I/O 流可以减少查询的总运行时间。

• CPU 并行性支持在查询中处理 CPU 的多任务处理。经常调用 CPU 并行性还会调用 I/O 并行性，以使每个 CPU 引擎能够使用自己的 I/O 流。CPU 并行性将查询分解为多个较小的查询，这些查询可以在多个处理器上并发执行。CPU 并行性可以进一步减少查询的运行时间。

• 最后，DBMS 可以部署系统并行性来进一步增强并行查询操作。系统并行性允许一个查询被分解，并在多个 DBMS 实例之间运行。允许单个查询利用多个 DBMS 实例的处理能力可以进一步减少复杂查询的总体运行时间。

并行不是万能的。通常，优化器将必须制定多个访问路径（一个用于并行，一个用于单流），以便在无法使用适当的系统资源进行并行处理时拥有回退方法。也就是说，使优化器能够为 I/O 和/或 CPU 绑定的 SQL 构建并行访问路径是有意义的。

总结

该系列文章旨在提供与 DBMS 无关的 SQL 性能和优化介绍。当然，还有大量额外的细节需要理解和消化，才能使主题正确。在这些细节中，最重要的是你正在使用的 DBMS 的确切特性和支持。

确保使用正确的索引和访问方法制定正确的查询计划是一个耗时的过程，但是可以通过提高性能的形式获得巨大的收益。dba 应该训练他们的应用程序开发人员理解关系优化并创建最优 SQL。应用程序开发人员有责任编写高效的 SQL 和程序逻辑。

然而，DBA 是关系数据库性能的哨兵。当出现性能问题时，DBA 必须寻找问题的原因并提出解决方法。此外，DBA 应该进行设计评审，在将次优访问路径和程序迁移到生产状态之前，找出并调优低效的 SQL。

本文转载自公众号 360 云计算（ID：hulktalk）。

原文链接：

https://mp.weixin.qq.com/s/SyZWIZVqPm7lsC6eTrppNw