SQL优化策略：如何避免不必要的回表操作

什么是回表

回表是指在使用索引查询数据库时，由于索引本身不包含所需的所有列，数据库需要根据索引中存储的rowid去表中读取完整数据行的过程。回表通常会造成额外的I/O操作，因为每个rowid对应一个具体的数据块，导致SQL性能降低。

回表对性能的影响

• I/O次（读块）数：回表是一个单块读操作。每次回表都会增加I/O操作次数，影响查询性能。
• 查询性能：如果需要回表的次数过多，可能会使索引扫描的优势丧失，反而不如直接进行全表扫描。

如何判断回表对性能的影响

从执行计划中可以看到，TABLE ACCESS BY INDEX ROWID步骤标识了回表操作。这种操作在实际应用中，需要评估回表次数对性能的影响。

SQL执行计划分析

以下是执行计划中的一些关键部分：

SQL> select * from test where owner='SYS';

30808 rows selected.

Execution Plan
--------------------------------
Plan hash value: 3932013684

--------------------------------------------------------
| Id  | Operation                    | Name  | Rows | ... |
--------------------------------------------------------
|  0  | SELECT STATEMENT             |       | 2499 | ... |
|  1  | TABLE ACCESS BY INDEX ROWID  | TEST  | 2499 | ... |
| *2  | INDEX RANGE SCAN             | IDX_OWNER | 2499 | ... |
--------------------------------------------------------

1. INDEX RANGE SCAN：这是索引范围扫描，通过索引快速定位相关记录的rowid。
2. TABLE ACCESS BY INDEX ROWID：这是回表操作，根据rowid从表中读取所需的完整行数据。

为什么会有回表

• 查询中使用了SELECT *：需要返回整行数据。
• 索引列不包含查询的所有需要列：索引中可能缺少查询中所需的列。

SQL执行计划性能分析

从执行计划中的性能统计可以看出，这个SQL查询回表占用了较多的资源，尤其是在以下几个方面：

• consistent gets：一致性读操作的次数。在第一次查询中，consistent gets为74次，而设置arraysize=5000后为877次，说明通过优化arraysize可以减少传输开销。
• rows processed：处理的行数。说明查询结果包含30808行数据。

详细举例

为了更好地理解回表的影响，下面以两个例子来说明如何优化SQL以减少回表：

例子1：使用更少的列

有一个订单表orders，有以下字段：order_id，customer_id，order_date，total_amount。而且customer_id上有索引。

原始查询：

SELECT * FROM orders WHERE customer_id = 12345;

• 问题：查询使用SELECT *，需要返回整行数据，导致需要回表获取完整记录。

优化查询：

SELECT order_id, order_date, total_amount FROM orders WHERE customer_id = 12345;

• 仅选择需要的列，减少不必要的I/O开销。

例子2：创建复合索引

有一个员工表employees，包含字段：employee_id，department_id，hire_date，salary。查询中经常需要根据department_id和hire_date筛选数据，而这两个字段没有同时出现在一个索引中。

原始查询：

SELECT employee_id, salary FROM employees WHERE department_id = 10 AND hire_date > DATE '2020-01-01';

• 问题：查询中有多个条件，但只有department_id有索引，导致可能需要回表。

优化方案：创建复合索引

CREATE INDEX idx_emp_dept_date ON employees(department_id, hire_date);

• 通过创建复合索引，能够覆盖查询中所需的条件，减少回表操作。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

无论你是AI新手还是AI专家，学习最前沿的AI技术，AI创富俱乐部你值得拥有！

本文由mdnice多平台发布