索引优化

1. 创建合适的B-Tree索引

CREATE INDEX idx_users_email ON users (email);

原因：B-Tree适合等值查询（WHERE column = value）和范围查询（WHERE column > value），是最常用的索引类型。

2. 使用GIN/GIST索引处理JSON/数组

CREATE INDEX idx_users_tags_gin ON users USING GIN (tags);  -- 数组或JSONB类型
CREATE INDEX idx_points_gist ON points USING GIST (geom);   -- 空间数据

原因：GIN/GIST索引专门优化复杂数据类型（如JSON、数组、几何数据）的查询。

3. 创建覆盖索引（Covering Index）

CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (order_date, amount);

原因：查询所需的所有列都在索引中，避免回表读取数据行，减少I/O。

4. 表达式索引

CREATE INDEX idx_lower_email ON users (LOWER(email));

原因：当查询条件包含函数或表达式时（如WHERE LOWER(email) = 'test@example.com'），普通索引无效，需创建表达式索引。

查询语句优化

1. 避免全表扫描

-- 低效：全表扫描
SELECT * FROM users WHERE age > 30;

-- 高效：添加索引后使用索引扫描
CREATE INDEX idx_users_age ON users (age);

原因：全表扫描（Sequential Scan）在大表上性能极差，索引扫描可快速定位数据。

2. 优化JOIN顺序和条件

-- 确保JOIN条件有索引
SELECT * FROM orders 
JOIN users ON orders.user_id = users.id  -- user_id和id应有索引
WHERE users.status = 'active';

原因：优化器会优先处理数据量小的表，合理的JOIN条件索引可减少中间结果集。

3. 用EXISTS替代IN/NOT IN

-- 低效：
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 高效：
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

原因：EXISTS在找到第一个匹配项后即停止扫描，而IN可能需要扫描子查询的全部结果。

4. 避免SELECT *

-- 低效：
SELECT * FROM users;

-- 高效：
SELECT id, name, email FROM users;

原因：减少不必要的列读取，降低内存和I/O开销。

配置参数优化

1. 调整shared_buffers

-- postgresql.conf
shared_buffers = '4GB'  -- 通常设置为内存的25%

原因：shared_buffers缓存数据页，增加该参数可减少磁盘I/O。

2. 优化work_mem

-- 临时提高排序/哈希表内存
SET work_mem = '64MB';

原因：较大的work_mem可减少排序和哈希操作的磁盘临时文件，提升复杂查询性能。

3. 调整maintenance_work_mem

-- postgresql.conf
maintenance_work_mem = '1GB'  -- 用于VACUUM、CREATE INDEX等操作

原因：增加该参数可加速索引创建和VACUUM操作。

4. 启用并行查询

-- postgresql.conf
max_parallel_workers_per_gather = 4  -- 每个查询最多使用4个并行工作进程

原因：并行查询可利用多核CPU加速大型表的扫描和聚合。

5. 避免子查询嵌套

-- 低效：
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 高效：
SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 1000;

原因：JOIN通常比子查询更高效，尤其是在大数据集上。

表结构优化

1. 垂直分区（将宽表拆分为多个表）

-- 原表：
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    bio TEXT,
    profile_picture BYTEA
);

-- 拆分为两个表：
CREATE TABLE users_core (id, name, email);
CREATE TABLE users_profile (id, bio, profile_picture);

原因：经常访问的列与不常用列分离，减少I/O和内存占用。

2. 水平分区（大表拆分为子表）

-- 按日期Range分区：
CREATE TABLE logs_2023 PARTITION OF logs FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

原因：减少单表数据量，通过约束排除（Constraint Exclusion）加速查询。

3. 使用TOAST压缩大字段

ALTER TABLE messages ALTER COLUMN content SET STORAGE EXTERNAL;

原因：TOAST技术将大字段（如TEXT、BYTEA）存储在单独的页中，减少主表数据页的膨胀。

执行计划分析

使用 EXPLAIN 和 ANALYZE 的基本概念

• EXPLAIN：用于显示查询的执行计划，描述 PostgreSQL 将如何执行一个 SQL 查询，包括使用的索引、连接类型、预计的行数等信息。
• ANALYZE：在执行查询的同时，收集实际的执行统计信息，包括实际的行数、执行时间等。与EXPLAIN一起使用，可以提供更详细的性能分析。

1. 基本的 EXPLAIN 使用

示例：考虑一个简单的查询，从employees表中检索所有在某个部门工作的员工。

EXPLAIN SELECT * FROM employees WHERE department_id = 3;

解释：

• 该命令将返回查询的执行计划，但不会实际执行查询。
• 结果可能包括Seq Scan（顺序扫描）或Index Scan（索引扫描），显示 PostgreSQL 将如何访问数据。

输出示例：

Seq Scan on employees  (cost=0.00..35.50 rows=10 width=244)
  Filter: (department_id = 3)

分析：

• Seq Scan表示 PostgreSQL 将对employees表进行顺序扫描，这在数据量较大时可能导致性能问题。
• cost表示执行该查询的预估成本，rows表示预计返回的行数。

2. 使用 ANALYZE 进行性能分析

示例：结合ANALYZE使用，获取实际的执行统计信息。

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 3;

解释：

• 该命令不仅显示查询计划，还实际执行查询并返回执行时间和实际行数。

输出示例：

Seq Scan on employees  (cost=0.00..35.50 rows=10 width=244) (actual time=0.020..0.025 rows=10 loops=1)
  Filter: (department_id = 3)
  Rows Removed by Filter: 90
Planning Time: 0.150 ms
Execution Time: 0.050 ms

分析：

• actual time显示实际执行的时间，rows显示实际返回的行数。
• Rows Removed by Filter表示被过滤掉的行数，有助于理解查询的选择性。

3. 优化查询示例

场景：假设employees表没有针对department_id列的索引，导致查询性能较差。

步骤 1：创建索引

CREATE INDEX idx_department_id ON employees(department_id);

步骤 2：再次分析查询计划

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 3;

输出示例：

Index Scan using idx_department_id on employees  (cost=0.15..8.20 rows=10 width=244) (actual time=0.010..0.015 rows=10 loops=1)
  Index Cond: (department_id = 3)
Planning Time: 0.120 ms
Execution Time: 0.040 ms

分析：

• 现在查询使用Index Scan，表示 PostgreSQL 使用了索引来快速定位相关行。
• cost和actual time都显著降低，表明查询性能得到了优化。

其他优化技巧

1. 合理使用VACUUM和ANALYZE

VACUUM FULL ANALYZE users;  -- 清理死元组并更新统计信息

原因：频繁更新/删除的表会产生大量死元组，VACUUM可回收空间并提升查询性能。

2. 使用CTE（公共表表达式）优化复杂查询

WITH monthly_sales AS (
    SELECT EXTRACT(MONTH FROM sale_date) AS month, SUM(amount) AS total
    FROM sales
    GROUP BY month
)
SELECT * FROM monthly_sales WHERE total > 10000;

原因：CTE可将复杂查询分解为多个逻辑部分，便于优化和理解。

常见扫描方式对比（Seq Scan vs Index Scan）

1. 顺序扫描 (Seq Scan)

• 工作原理：逐行读取整个表的所有数据页

• 执行特点：

• 线性I/O操作，从第一个数据块顺序读到最后一个

• 无任何过滤条件，读取所有行后再应用WHERE条件

• 使用共享缓冲区，可能受益于操作系统缓存

2. 索引扫描 (Index Scan)

• 工作原理：通过索引结构定位目标数据

• 执行特点：

• 先读取索引页，再通过指针访问堆表数据

• 随机I/O为主，可能触发大量磁盘寻道

• 索引条件先过滤，再访问表数据

性能特征对比

执行计划深度解析

Seq Scan示例：

EXPLAIN ANALYZE SELECT * FROM orders WHERE total_amount > 1000;
Seq Scan on orders  (cost=0.00..1254.30 rows=30230 width=112)
              Filter: (total_amount > 1000)

Index Scan示例：

EXPLAIN ANALYZE SELECT * FROM customers WHERE customer_id = 1001;
Index Scan using customers_pkey on customers  (cost=0.29..8.30 rows=1 width=72)
              Index Cond: (customer_id = 1001)