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';
通过这些方法，可以有效避免全表扫描，提高查询性能。