PostgreSQL查询扫描原理

骐骥1月

已于 2025-01-16 16:01:00 修改

阅读量1.2k

点赞数 22

分类专栏： PostgreSQL/PostGIS 文章标签：学习 postgre 查询

于 2025-01-16 15:26:15 首次发布

本文链接：https://blog.youkuaiyun.com/sig321/article/details/144553892

版权

PostgreSQL/PostGIS 专栏收录该内容

23 篇文章

订阅专栏

一、扫描方法原理

扫描方法的原理包括 seqscan、index scan、index only scan、bitmap scan 等。

seqscan 是一种简单的顺序扫描方法，它按照表中数据的物理存储顺序进行扫描。它会按照磁盘上数据的物理顺序依次读取每一行记录。这种扫描方式适用于需要扫描大部分或全部数据的情况下，例如需要返回一张表中的所有记录或者需要进行全表聚合查询时。
index scan 是指使用索引进行扫描，它会根据查询条件在索引上进行查找，并且只读取满足条件的记录。这种扫描方式适用于查询条件较为严格、能够使用索引进行高效查找的情况下，例如通过主键或唯一索引进行查询。它可以利用索引快速定位数据。
index only scan 纯索引扫描。则是在索引中获取所需数据，而不需要访问数据表，因此查询速度更快。
bitmap scan 是一种基于位图的扫描方法，它可以通过对多个索引进行并集或交集操作，快速定位并返回所需数据。

二、PostgreSQL选择较慢的Seq Scan而不是Index Scan

在某些情况下，PostgreSQL会选择较慢的Seq Scan而不是Index Scan。这主要是由于以下几个因素：

1、数据分布不均匀

当数据分布不均匀时，使用索引进行扫描可能会导致访问大量磁盘块。例如，如果一个表中的某个列的值具有高度重复性，那么使用索引可能会导致多次磁盘访问，从而降低查询性能。在这种情况下，选择Seq Scan可以通过顺序读取磁盘块来提高查询性能。

2、查询结果占据大部分数据

当查询的结果占据大部分数据时，使用Seq Scan可能比使用Index Scan更高效。因为使用Index Scan需要对每一条满足条件的记录进行索引查找，而使用Seq Scan只需要顺序读取数据块并过滤出满足条件的记录。对于这种情况，Seq Scan可以通过减少索引查找次数来提高查询性能。

3、查询需要访问大量列

当查询需要访问大量列时，Seq Scan可能比Index Scan更高效。因为使用索引进行扫描需要从索引中读取满足条件的记录，然后再从磁盘中读取对应的列数据。而使用Seq Scan可以一次读取多个列数据，减少了磁盘访问次数。

三、对大数据量查询的提速

1、优化索引

索引是加快查询速度的重要因素之一。通过创建适当的索引，可以减少查询过程中需要读取的磁盘块数量。以下是一些优化索引的技巧：

选择正确的索引类型：PostgreSQL支持多种索引类型，如B-tree、哈希和GiST等。根据查询的性质选择适合的索引类型可以提高性能。
考虑使用复合索引：当多个列联合使用时，使用复合索引可以提高查询效率。注意不要创建过多的复合索引，以免导致维护开销过大。
避免对索引列进行函数操作：在查询中对索引列进行函数操作可能导致无法使用索引，影响查询性能。尽量保持索引列的原始状态来提高性能。

-- 创建单列索引
CREATE INDEX idx_column ON table_name (column_name);

-- 创建复合索引
CREATE INDEX idx_columns ON table_name (column1, column2);

2、避免全表扫描

全表扫描是指在没有使用索引的情况下对整个数据表进行遍历查询。当数据量庞大时，全表扫描会导致查询结果太慢。以下是一些避免全表扫描的技巧：

使用WHERE子句：通过在查询语句中添加WHERE子句，可以限定查询的条件，减少不必要的数据读取。尽量避免查询所有数据。
分页查询：如果查询结果只需要展示一部分数据，可以使用LIMIT和OFFSET子句进行分页查询，避免查询整个表。

下面是使用WHERE子句和分页查询的示例代码：

-- 使用WHERE子句限定查询条件
SELECT * FROM table_name WHERE column_name = 'value';

-- 分页查询
SELECT * FROM table_name LIMIT 10 OFFSET 0;

3、优化查询语句

优化查询语句可以提高查询效率，以下是一些优化查询语句的技巧：

选择合适的数据类型：合理选择适合数据类型可以减少存储空间和查询时间。例如，对于只存储年份的字段，可以选择使用整数类型代替日期类型。
合理使用JOIN操作：在多表查询时，合理使用JOIN操作可以避免产生笛卡尔积，提高查询效率。
避免使用SELECT *：只查询需要的列可以减少数据读取量，提高查询速度。

以下是优化查询语句的示例代码：

-- 选择合适的数据类型
ALTER TABLE table_name ALTER COLUMN column_name TYPE integer;

-- 合理使用JOIN操作
SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

-- 避免使用SELECT *
SELECT column1, column2 FROM table_name;

4、配置数据库参数

通过配置数据库参数，可以进一步提高PostgreSQL的性能，以下是一些常用的配置参数：

shared_buffers：设置共享内存缓冲区的大小，增加该参数可以提高缓存效果，加快查询速度。
work_mem：设置每个查询的工作内存大小，增加该参数可以提高排序和哈希操作的速度。
max_connections：设置最大并发连接数，适当增加该参数可以提高并发性能。

以下是配置数据库参数的示例代码：

-- 修改shared_buffers参数
ALTER SYSTEM SET shared_buffers = '1GB';

-- 修改work_mem参数
ALTER SYSTEM SET work_mem = '64MB';

-- 修改max_connections参数
ALTER SYSTEM SET max_connections = 100;

5、监控和优化查询计划

通过监控查询计划，可以了解查询语句的执行情况，进而进行针对性的优化。以下是一些监控和优化查询计划的工具和技巧：

EXPLAIN命令：使用EXPLAIN命令可以查看查询语句的执行计划，了解查询优化器的选择和执行情况。
自动化工具：使用PostgreSQL提供的自动化工具，如pg_stat_statements和pg_stat_monitor等，可以方便地监控和优化查询性能。

以下是使用EXPLAIN命令和自动化工具的示例代码：

-- 查看查询语句的执行计划
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

-- 使用pg_stat_statements监控查询性能
SELECT * FROM pg_stat_statements;

-- 使用pg_stat_monitor监控查询性能
SELECT * FROM pg_stat_monitor;