SQL 性能优化：慢索引-2

前面章节介绍了如何通过修改现有字段的索引顺序获得好处，不过这一示例考量的只有两个 SQL 语句。然而，修改索引可能影响所索引的表的所有查询。本章解释了数据库如何选择索引并演示了修改现有索引可能产生的副作用。

使用的 employees_pk 索引改善了只通过 subsidiary 搜索的所有查询。然而，它可用于所有按 SUBSIDIARY_ID 搜索的查询，而不考虑是否有任何其他搜索条件。这意味着该索引可用于那些使用 where 子句另一部分的另一个索引的查询。在这种情况下，如果有多个可用的访问路径，优化器的工作就是选择最佳的一个。

查询优化器

查询优化器(或者查询规划器)是用于将 SQL 语句转换成执行计划的数据库组件。这一过程也叫编译或解析。有两种不同的优化器类型。

基于成本的优化器(Cost-based optimizer, CBO)生成许多执行计划变体，并为每个计划计算成本值。成本计算是基于正在使用的操作和估计的行数。最终，成本值作为选择“最佳”执行计划的基准。

基于规则的优化器(Rule-based Optimizer, RBO)使用硬编码的规则集生成执行计划。基于规则的优化器灵活性较低，目前很少使用。

更改索引也可能产生令人不快的副作用。在我们的示例中，是内部电话簿应用程序自合并以来变得非常缓慢。第一次分析将以下查询确定为速度减慢的原因：

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30

执行计划为：

示例 2.1 使用改过的主键索引的执行计划

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |   30 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |   30 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK |   40 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)

执行计划使用一个索引，总成本值为 30。到目前为止，一切都很好。然而，值得怀疑的是，它使用了我们刚刚更改的索引，这足以让人怀疑我们的索引更改导致了性能问题，尤其是当考虑到旧的索引定义时，它是从 EMPLOYEE_ID 列开始的，而这根本不是 where 子句的一部分。之前这一查询无法使用该索引。

为了进行进一步的分析，最好比较一下变更前后的执行计划。为了获得原始的执行计划，我们可以再次部署旧的索引定义，但是大多数数据库提供了一种更简单的方法来防止对特定查询使用索引。以下示例为此使用了 Oracle 优化器提示。

SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEES_PK) */ 
       first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30

很可能在索引修改之前使用的这个执行计划根本没有使用索引

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30)

尽管T ABLE ACCESS FULL 必须读取并处理整个表，但在这种情况下，它似乎比使用索引更快。这是特别不寻常的，因为查询只匹配一行。使用索引查找单行应该比全表扫描快得多，但在这种情况下却不是。索引似乎很慢。

在这种情况下，最好是检查的执行计划的每一步。第一步是对 EMPLOYEES_PK 索引进行 INDEX RANGE SCAN。该索引不包括 LAST_NAME 列，INDEX RANGE SCAN 只能考虑 SUBSIDIARY_ID 筛选器；Oracle 数据库在执行计划的 “Predicate Information” 区域 —— 条目 “2” 中显示了这一点。在那里，你可以看到每个操作所应用的条件。

Tip

附录 A, “执行计划”，解释如何在其他数据库中找到 “Predicate Information” 

操作 ID 为 2 的 INDEX RANGE SCAN(示例 2.1)仅应用 SUBSIDIARY_ID=30 过滤器。这意味着它遍历索引树以找到 SUBSIDIARY_ID 30 的第一个条目。接下来，它沿着叶节点链查找该子节点的所有其他条目。INDEX RANGE SCAN 的结果是满足 SUBSIDARY_ID 条件的 ROWID 列表：根据子公司的大小，可能只有几个，也可能有数百个。

下一步是 TABLE ACCESS BY INDEX ROWID 操作。它使用上一步中的 ROWID 从表中获取行种的所有列。一旦 LAST_NAME 列可用，数据库就可以计算 where 子句的剩余部分。这意味着数据库必须获取 SUBSIDIARY_ID=30 的所有行，然后才能应用 LAST_NAME 筛选器。

语句的响应时间并不取决于结果集的大小，而是取决于特定子公司的员工人数。如果子公司只有几个成员，INDEX RANGE SCAN 可以提供更好的性能。尽管如此，对于大型子公司来说，TABLE ACCESS FULL 可以更快，因为它可以一次读取表格中的大部分内容（请参阅“全表扫描”）。

查询很慢，因为索引查找会返回许多 ROWID，原来公司的每个员工都有一个 ROWID，并且数据库必须单独获取它们。正是这两个因素的完美结合使索引速度变慢：数据库读取了较大的索引范围，并且必须单独获取许多行。

选择最佳执行计划也取决于表的数据分布，因此优化器使用有关数据库内容的统计信息。在我们的例子中，使用了一个包含员工在子公司之间分布的直方图。这允许优化器评估从索引查找返回的行数——其结果用于成本计算。

统计

基于成本的优化器使用有关表、列和索引的统计信息。大多数统计数据都是在列级别收集的：distinct值的数量、最小值和最大值（数据范围）、NULL出现的次数以及列直方图（数据分布）。表最重要的统计值是它的大小（以行和块为单位）。

最重要的索引统计数据是树的深度、叶节点的数量、不同键的数量和聚类因子（见第5章“聚类数据”）。
优化器使用这些值来估计where子句谓词的选择性。

如果没有可用的统计数据，例如因为它们被删除了，优化器将使用默认值。Oracle 数据库的默认统计数据建议小索引和中等选择性。他们估计 INDEX RANGE SCAN 将返回 40 行。执行计划在“行”列中显示了这一估计（再次参见示例 2.1）。显然，这是一个严重的低估，因为该子公司有 1000 名员工。

如果我们提供了正确的统计数据，优化器会做得更好。以下执行计划显示了新的估计：INDEX RANGE SCAN 有 1000 行。因此，它为后续的表访问计算了更高的成本值。

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |  680 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |  680 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK | 1000 |    4 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)

680 的成本值甚至高于使用全表扫描的执行计划的成本值(477)。因此，优化器将自动偏好 FULL TABLE SCAN。

这个慢速索引的例子不应该掩盖这样一个事实，即正确的索引是最好的解决方案。当然，搜索 last_name 最佳的支持来自于 last_name 上的索引：

CREATE INDEX emp_name ON employees (last_name)

使用新的索引，优化器计算成本值 3:

示例 2.2 专用索引的执行计划

--------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Cost |
--------------------------------------------------------------
|  0 | SELECT STATEMENT            |           |    1 |    3 |
|* 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    1 |    3 |
|* 2 |   INDEX RANGE SCAN          | EMP_NAME  |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SUBSIDIARY_ID"=30)
   2 - access("LAST_NAME"='WINAND')

根据优化器的评估，索引访问只提供一行。因此，数据库只需要从表中提取那一行：这肯定比 FULL table SCAN 快。定义良好的索引仍然比原始的全表扫描要好。

例 2.1 和实施例 2.2 的两个执行计划几乎相同。数据库执行相同的操作，优化器计算出类似的成本值，但第二个计划执行得更好。INDEX RANGE SCAN 的效率可能在很宽的范围内变化，尤其是当后面是表访问时。使用索引并不意味着语句会以尽可能好的方式执行。