Oracle（74）如何避免全表扫描？

避免全表扫描（Full Table Scan）是优化SQL查询性能的关键。全表扫描会遍历表中的每一行，当表很大时，会导致性能问题。以下是一些常见的方法来避免全表扫描，并结合代码进行详细说明。

1. 创建并使用索引

索引是提高查询性能的最有效方法之一。通过创建合适的索引，可以显著减少查询需要访问的行数，从而避免全表扫描。

示例

假设有一个名为employees的表：

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50),
    emp_department VARCHAR(50)
);

INSERT INTO employees (emp_name, emp_department)
VALUES ('Alice', 'IT'), ('Bob', 'HR'), ('Charlie', 'IT'), ('David', 'Finance');

查询emp_department为’IT’的员工：

SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

创建索引：

CREATE INDEX idx_emp_department ON employees(emp_department);

执行查询并查看执行计划：

EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

示例输出（MySQL）

+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_emp_department   | idx_emp_department   | 53      | const |    2 |   100.00 | Using where |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+

2. 使用适当的查询条件

确保查询条件能够有效利用索引。例如，使用=、IN、BETWEEN等操作符，而避免使用不支持索引的操作符如LIKE '%pattern%'。

示例

SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

这个查询可以利用索引idx_emp_department，避免全表扫描。

3. 覆盖索引（Covering Index）

覆盖索引是指索引包含了查询所需的所有列，查询无需回表（访问实际数据行），只需访问索引即可满足查询需求。

示例

假设查询不仅需要emp_department，还需要emp_id和emp_name：

SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

创建覆盖索引：

CREATE INDEX idx_emp_department_cover ON employees(emp_department, emp_id, emp_name);

执行查询并查看执行计划：

EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

4. 分区表（Partitioning）

对于非常大的表，可以将表按某个列进行分区，查询时只扫描相关分区而不是整个表。

示例

假设有一个大表orders，按年份进行分区：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

查询2020年的订单：

SELECT order_id, customer_id, amount
FROM orders
WHERE YEAR(order_date) = 2020;

执行查询并查看执行计划：

EXPLAIN
SELECT order_id, customer_id, amount
FROM orders
WHERE YEAR(order_date) = 2020;

5. 避免函数操作和计算

在查询条件中避免对列进行函数操作或计算，因为这会导致索引失效。

示例

避免以下查询：

SELECT emp_id, emp_name
FROM employees
WHERE UPPER(emp_department) = 'IT';

改为：

SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

6. 使用适当的联接（Join）

确保联接条件能够有效利用索引，避免不必要的全表扫描。

示例

假设有两个表employees和departments：

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- 添加索引
CREATE INDEX idx_dept_name ON departments(dept_name);

-- 联接查询
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.emp_department = d.dept_name
WHERE d.dept_name = 'IT';

执行查询并查看执行计划：

EXPLAIN
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.emp_department = d.dept_name
WHERE d.dept_name = 'IT';

7. 更新统计信息

确保数据库的统计信息是最新的，查询优化器可以基于准确的统计信息生成高效的执行计划。

示例（PostgreSQL）

ANALYZE employees;

总结

通过创建并使用索引、使用适当的查询条件、覆盖索引、分区表、避免函数操作和计算、使用适当的联接、以及更新统计信息，可以显著减少全表扫描，提高查询性能。

示例代码总结

创建表和插入数据：

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50),
    emp_department VARCHAR(50)
);

INSERT INTO employees (emp_name, emp_department)
VALUES ('Alice', 'IT'), ('Bob', 'HR'), ('Charlie', 'IT'), ('David', 'Finance');

创建索引：

CREATE INDEX idx_emp_department ON employees(emp_department);

执行查询并查看执行计划：

EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

通过这些方法，可以有效避免全表扫描，提高查询性能。