PostgreSQL全表扫描与索引扫描对比

PostgreSQL全表扫描与索引扫描基础概念

全表扫描

全表扫描（Full Table Scan，FTS），从字面意思理解，就是数据库系统遍历表中的每一行数据，以满足查询的条件。当数据库执行全表扫描时，它会按照物理存储顺序，逐块读取表中的数据页，对每一页中的每一条记录进行检查，看其是否符合查询的WHERE子句条件。

这种扫描方式在一些场景下是非常有用的。比如，当表的数据量非常小，数据库优化器会认为全表扫描的成本比使用索引更低。又或者查询条件涉及到表中大部分数据时，全表扫描可能是更高效的选择。因为如果使用索引，虽然索引能快速定位到符合条件的索引项，但从索引项到实际数据行的回表操作（如果索引不是覆盖索引）可能会带来额外的I/O开销，而全表扫描可以顺序读取数据页，减少随机I/O。

在PostgreSQL中，全表扫描的执行计划会明确显示 Seq Scan。例如，有一张简单的员工表 employees，包含字段 id、name、age 等，执行如下查询：

SELECT * FROM employees WHERE age > 30;

如果此时 employees 表数据量较小，或者 age 字段没有合适的索引，PostgreSQL可能会选择全表扫描，执行计划类似如下：

QUERY PLAN
---------------------------------------------------------------------
Seq Scan on employees  (cost=0.00..10.25 rows=25 width=104)
  Filter: (age > 30)

索引扫描

索引扫描（Index Scan）则依赖于数据库中创建的索引结构。索引就像是一本书的目录，它提供了一种快速定位数据的方式。PostgreSQL支持多种类型的索引，如B - Tree索引、Hash索引、GiST索引等，不同类型的索引适用于不同的查询场景。

以最常用的B - Tree索引为例，B - Tree索引将索引键值按照一定的顺序存储，形成一种树形结构。当执行一个带有条件的查询时，数据库可以利用索引的有序性，通过比较索引键值，快速定位到符合条件的索引项，然后通过索引项找到对应的实际数据行。

例如，还是在 employees 表上，如果在 age 字段上创建了B - Tree索引：

CREATE INDEX idx_employees_age ON employees (age);

此时执行同样的查询 SELECT * FROM employees WHERE age > 30;，PostgreSQL可能会选择索引扫描，执行计划类似如下：

QUERY PLAN
--------------------------------------------------------------------
Index Scan using idx_employees_age on employees  (cost=0.29..8.31 rows=25 width=104)
  Index Cond: (age > 30)

可以看到，执行计划中明确显示了使用了 idx_employees_age 索引进行扫描，这表明数据库利用索引来快速定位符合条件的数据。

全表扫描与索引扫描的成本分析

全表扫描成本因素

1. 数据量：全表扫描的成本与表的数据量直接相关。数据量越大，需要读取的数据块就越多，I/O操作也就越频繁。假设一个表占用了N个数据块，全表扫描就需要读取这N个数据块。如果每个数据块的读取时间为t，那么全表扫描的I/O时间成本大致为N * t。
2. 数据分布：如果数据在物理存储上分布不均匀，可能会导致全表扫描时读取的数据块较为分散，增加了磁盘寻道时间。例如，某些数据行被频繁插入或删除，可能会使表的物理存储变得碎片化，全表扫描时就需要花费更多时间在磁盘寻道上，从而增加了整体的扫描成本。
3. 查询条件选择性：当查询条件的选择性较低时，即符合条件的数据占表中数据的比例较大时，全表扫描可能是一个较好的选择。因为即使使用索引，回表操作带来的开销可能会超过全表扫描的成本。例如，查询 SELECT * FROM employees WHERE age > 18;，如果大部分员工年龄都大于18岁，全表扫描可能比使用索引扫描更高效。

索引扫描成本因素

1. 索引结构：不同类型的索引结构，其扫描成本有所不同。B - Tree索引适用于范围查询和等值查询，其查找时间复杂度为O(log n)，n为索引项的数量。Hash索引则适用于等值查询，查找时间复杂度接近O(1)，但不支持范围查询。如果选择了不适合查询条件的索引类型，会导致索引扫描成本增加。
2. 索引维护成本：索引不是免费的，在表上创建索引后，插入、更新和删除操作都会涉及到索引的维护。每次数据修改时，不仅要更新表中的数据行，还要更新相关的索引项。这会增加数据库操作的额外开销。例如，在一个频繁插入数据的表上，如果索引过多，插入操作的性能会明显下降。
3. 回表操作：如果查询需要获取的列不是索引中包含的列（即非覆盖索引情况），那么通过索引定位到符合条件的索引项后，还需要根据索引项中的指针去实际数据页中获取完整的数据行，这个过程称为回表。回表操作可能会导致额外的I/O开销，尤其是当索引项与实际数据行不在同一数据块时，可能需要多次随机I/O，从而增加索引扫描的成本。

全表扫描与索引扫描的性能测试与对比

测试环境搭建

1. 硬件环境：测试服务器配置为CPU：Intel Xeon E5 - 2620 v4 @ 2.10GHz，内存：16GB，硬盘：500GB SSD。操作系统为Ubuntu 20.04 LTS。
2. 软件环境：安装PostgreSQL 13版本。创建一个测试数据库 test_db，并在其中创建一张测试表 test_table：

CREATE TABLE test_table (
    id serial PRIMARY KEY,
    value integer,
    data text
);

为了模拟不同的数据量，使用如下脚本向表中插入不同数量的数据：

-- 插入10000条数据
DO $$
BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO test_table (value, data) VALUES (i, 'data_' || i);
    END LOOP;
END $$;

3. 索引创建：在 value 字段上创建B - Tree索引：

CREATE INDEX idx_test_table_value ON test_table (value);

全表扫描性能测试

1. 查询编写：编写一个全表扫描的查询，例如查找 value 大于5000的数据：

EXPLAIN ANALYZE SELECT * FROM test_table WHERE value > 5000;

2. 测试结果分析：多次执行上述查询，并记录执行时间。在数据量为10000条时，执行计划如下：

QUERY PLAN
--------------------------------------------------------------------
Seq Scan on test_table  (cost=0.00..184.00 rows=4999 width=44)
  Filter: (value > 5000)
  Planning Time: 0.123 ms
  Execution Time: 1.542 ms

可以看到，执行时间较短，因为数据量相对较小，全表扫描能够快速完成。随着数据量的增加，例如插入100000条数据后，再次执行相同查询，执行计划类似，但执行时间会显著增加：

QUERY PLAN
--------------------------------------------------------------------
Seq Scan on test_table  (cost=0.00..1840.00 rows=49999 width=44)
  Filter: (value > 5000)
  Planning Time: 0.156 ms
  Execution Time: 12.345 ms

索引扫描性能测试

1. 查询编写：同样查找 value 大于5000的数据，但利用索引：

EXPLAIN ANALYZE SELECT * FROM test_table WHERE value > 5000;

2. 测试结果分析：在数据量为10000条时，执行计划如下：

QUERY PLAN
--------------------------------------------------------------------
Index Scan using idx_test_table_value on test_table  (cost=0.29..117.24 rows=4999 width=44)
  Index Cond: (value > 5000)
  Planning Time: 0.145 ms
  Execution Time: 0.876 ms

可以看到，索引扫描的执行时间比全表扫描更短。当数据量增加到100000条时，再次执行相同查询：

QUERY PLAN
--------------------------------------------------------------------
Index Scan using idx_test_table_value on test_table  (cost=0.29..1172.40 rows=49999 width=44)
  Index Cond: (value > 5000)
  Planning Time: 0.178 ms
  Execution Time: 3.456 ms

虽然索引扫描时间也会随着数据量增加而上升，但增长幅度相对全表扫描较小。在这种情况下，索引扫描的性能优势更加明显。

影响全表扫描与索引扫描选择的因素

查询条件

1. 等值条件：当查询条件是简单的等值条件，如 WHERE id = 10，如果在 id 字段上有合适的索引（如B - Tree索引或Hash索引），数据库通常会选择索引扫描。因为索引可以快速定位到符合条件的索引项，然后通过索引项找到对应的实际数据行。但如果表数据量非常小，数据库优化器可能会认为全表扫描成本更低，而选择全表扫描。
2. 范围条件：对于范围查询，如 WHERE age > 30，B - Tree索引是比较合适的。数据库可以利用B - Tree索引的有序性，快速定位到满足范围条件的索引项。然而，如果范围条件的选择性较低，即符合条件的数据占表中数据的比例较大，全表扫描可能会更高效。例如，WHERE age > 18，如果大部分员工年龄都大于18岁，全表扫描可能会避免回表操作带来的额外开销。
3. 复杂条件：当查询条件涉及多个字段的逻辑组合，如 WHERE age > 30 AND salary < 50000，如果在 age 和 salary 字段上分别有索引，数据库优化器需要评估使用多个索引的成本，以及是否可以通过索引合并等技术来提高查询效率。在某些情况下，可能会选择全表扫描，特别是当索引的组合使用不能有效减少需要扫描的数据量时。

数据量与表结构

1. 数据量大小：如前文所述，数据量是影响扫描方式选择的重要因素。当表数据量较小，全表扫描可能是更高效的选择，因为其不需要额外的索引维护成本，且数据库可以快速遍历整个表。随着数据量的增加，索引扫描的优势逐渐显现，因为索引可以快速定位到符合条件的数据，减少I/O操作。但当数据量非常大时，即使使用索引，回表操作也可能导致性能问题，此时数据库可能会根据查询条件和索引结构等因素，综合考虑选择全表扫描还是索引扫描。
2. 表结构：表的物理存储结构也会影响扫描方式。如果表是按聚簇索引存储（在PostgreSQL中，表的物理顺序默认与主键索引的顺序相同），那么对于基于主键或与聚簇索引相关的查询，索引扫描可能会更高效，因为可以利用聚簇索引的物理顺序，减少磁盘寻道时间。而对于非聚簇索引的查询，可能会涉及更多的随机I/O，从而影响索引扫描的性能。

索引设计与维护

1. 索引类型选择：不同类型的索引适用于不同的查询场景。选择合适的索引类型至关重要。例如，对于等值查询频繁的场景，Hash索引可能是更好的选择；而对于范围查询和排序查询，B - Tree索引更为合适。如果选择了错误的索引类型，不仅不能提高查询性能，反而会增加索引维护成本和查询执行成本。
2. 索引维护：索引在创建后需要进行定期维护。随着数据的插入、更新和删除，索引可能会变得碎片化，影响索引的查询性能。PostgreSQL提供了一些工具和命令来对索引进行维护，如 VACUUM 和 ANALYZE 命令。VACUUM 命令用于回收已删除数据占用的空间，并更新索引统计信息；ANALYZE 命令用于更新表和索引的统计信息，以便数据库优化器能够做出更准确的查询计划。定期执行这些维护操作，可以确保索引的性能始终处于较好状态。

实际应用中的优化策略

根据查询场景创建索引

1. 频繁查询字段：对于应用中频繁用于查询条件的字段，应该考虑创建索引。例如，在电商系统中，商品表的 category 字段经常用于查询某一类商品，那么在 category 字段上创建索引可以显著提高查询性能。

CREATE INDEX idx_products_category ON products (category);

2. 多字段组合查询：当查询条件涉及多个字段的组合时，可以创建组合索引。例如，在订单表中，经常需要查询某个时间段内某个客户的订单，那么可以在 customer_id 和 order_date 字段上创建组合索引：

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

组合索引的顺序也很重要，一般将选择性高的字段放在前面，这样可以更有效地利用索引。

避免不必要的索引

1. 低选择性字段：对于选择性很低的字段，即该字段的取值种类很少，创建索引可能不会带来性能提升，反而会增加索引维护成本。例如，在员工表中，gender 字段只有两种取值（男/女），在这个字段上创建索引通常是没有必要的，因为即使使用索引，数据库也需要扫描大量的数据来满足查询条件。
2. 小表索引：对于数据量非常小的表，创建索引可能会增加不必要的开销。因为数据库可以快速全表扫描整个小表，而索引的维护和使用也需要一定的成本。只有在小表的查询频率非常高，且查询条件较为复杂的情况下，才考虑创建索引。

定期维护数据库统计信息

1. ANALYZE命令：定期执行 ANALYZE 命令，更新表和索引的统计信息。这可以让数据库优化器根据最新的统计信息，生成更准确的查询计划。例如：

ANALYZE products;

这条命令会分析 products 表及其相关索引的统计信息。 2. VACUUM命令：结合 VACUUM 命令，回收已删除数据占用的空间，并且更新索引统计信息。VACUUM 命令有不同的选项，如 VACUUM FULL 可以更彻底地回收空间，但执行时间可能较长，会对数据库性能产生一定影响，需要在合适的时间（如业务低峰期）执行。

VACUUM products;

通过定期执行这些维护操作，可以保证数据库在全表扫描和索引扫描选择上的决策更加合理，从而提高整体的查询性能。