Oracle（73）什么是索引扫描（Index Scan）和全表扫描（Full Table Scan）？

索引扫描（Index Scan）和全表扫描（Full Table Scan）是数据库系统用来检索数据的两种主要方法。了解它们的区别和使用场景对于优化SQL查询性能至关重要。

全表扫描（Full Table Scan）

全表扫描是指数据库系统读取表中的每一行，检查是否符合查询条件。这种方法在没有索引或者查询条件涉及大部分数据时使用。

优点：

• 简单直接，适用于小表或需要访问大部分记录的查询。

缺点：

• 对于大表，性能较差，因为需要读取所有行。

示例

假设有一个名为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');

执行以下查询：

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

如果没有索引，数据库会执行全表扫描：

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       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   25.00  | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

索引扫描（Index Scan）

索引扫描是指数据库系统利用索引来快速查找符合查询条件的记录。索引类似于书的目录，可以显著提高数据检索的速度。

优点：

• 速度快，特别是对于大表和选择性高的查询条件。

缺点：

• 需要额外的存储空间来维护索引。

示例

为employees表的emp_department列创建一个索引：

CREATE INDEX idx_emp_department ON employees(emp_department);

执行以下查询：

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

数据库会使用索引扫描：

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 |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+

深入分析

全表扫描（Full Table Scan）

全表扫描的执行步骤如下：

1. 读取表中的每一行。
2. 检查每一行是否符合查询条件。
3. 返回符合条件的行。

适用场景：

• 小表或数据量较小的表。
• 查询需要访问表中的大部分数据。
• 表上没有适合的索引。

索引扫描（Index Scan）

索引扫描的执行步骤如下：

1. 利用索引查找符合条件的索引项。
2. 根据索引项定位到表中的数据行。
3. 返回符合条件的行。

适用场景：

• 大表或数据量较大的表。
• 查询条件选择性较高（即查询结果只占表中一小部分）。
• 表上有适合的索引。

示例代码总结

创建表和插入数据

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');

全表扫描示例

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

-- 查看执行计划（MySQL）
EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

创建索引和索引扫描示例

-- 创建索引
CREATE INDEX idx_emp_department ON employees(emp_department);

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

-- 查看执行计划（MySQL）
EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

总结

全表扫描和索引扫描是数据库系统中两种主要的检索数据的方法。全表扫描适用于小表或需要访问大部分数据的查询，而索引扫描适用于大表和选择性高的查询。通过创建合适的索引，可以显著提高查询性能。了解如何生成和分析执行计划，可以帮助识别查询的执行路径和性能瓶颈，从而采取相应的优化措施。