编程 PostgreSQL & MySQL 数据库性能优化实战：从查询计划到高并发调优的完全指南（2026版）

2026-05-19 06:15:56 +0800 CST views 17

PostgreSQL & MySQL 数据库性能优化实战：从查询计划到高并发调优的完全指南（2026版）

本文深入剖析 PostgreSQL 和 MySQL 两大主流关系型数据库的性能优化技术，涵盖查询计划分析、索引设计、配置调优、高并发处理等核心主题。无论你是初创公司后端工程师，还是大型互联网企业的 DBA，这篇文章都将帮助你系统性地提升数据库性能。

引言：为什么数据库性能优化至关重要
性能诊断工具箱
- PostgreSQL 性能诊断
- MySQL 性能诊断
查询计划深度解析
- EXPLAIN 完全指南
- 识别慢查询的根本原因
索引设计与优化实战
- B-tree 索引策略
- 复合索引设计黄金法则
- 部分索引与表达式索引
- GiST/GIN 索引（PostgreSQL 专属）
配置参数调优
- PostgreSQL 关键配置
- MySQL InnoDB 调优
高并发场景优化
- 连接池配置
- 锁竞争处理
- 事务隔离级别选择
分区表与分片策略
实战案例：电商平台数据库优化
监控与运维最佳实践
2026 年数据库技术趋势
总结与展望

引言

在 2026 年的今天，数据量呈现指数级增长。一个中型电商平台的日订单量可能突破百万，社交应用的消息流水更是达到亿级。在这样的背景下，数据库性能往往成为整个系统的瓶颈。

一个真实的案例：某独角兽企业在 B 轮融资后用户量激增，核心交易库的查询响应时间从 50ms 飙升至 2.5s，直接导致用户体验下降，日活下跌 15%。经过为期两周的紧急优化（索引重构 + 查询重写 + 配置调优），响应时间回落至 80ms，系统重新企稳。

这个案例告诉我们：数据库性能优化不是"可选项"，而是"必选项"。

本文将从实战角度出发，系统性地讲解 PostgreSQL 和 MySQL 的性能优化技术。我们不会堆砌枯燥的理论，而是结合真实的代码示例和测试用例，让你能够即学即用。

性能诊断工具箱

PostgreSQL 性能诊断

1. pg_stat_activity：实时活动监控

pg_stat_activity 是 PostgreSQL 的"任务管理器"，可以实时查看所有数据库连接的状态。

-- 查找当前运行时间超过 5 秒的查询
SELECT 
    pid, 
    usename, 
    datname, 
    client_addr, 
    application_name,
    state,
    backend_start,
    xact_start,
    query_start,
    now() - query_start AS query_duration,
    LEFT(query, 100) AS current_query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '5 seconds'
ORDER BY query_duration DESC;

输出示例：

 pid  | usename | datname | client_addr | application_name | state | backend_start       | query_duration | current_query
------+---------+---------+-------------+------------------+-------+---------------------+----------------+----------------------
 1234 | appuser | mydb    | 10.0.0.15  | OrderService     | active| 2026-05-18 14:23:15 | 00:00:08.342   | SELECT * FROM orders WHERE...

如果发现有长时间运行的查询，可以通过 pg_cancel_backend(pid) 取消，或者用 pg_terminate_backend(pid) 强制终止。

2. pg_stat_statements：查询性能统计

这个扩展模块记录了所有 SQL 语句的执行统计信息，是找出"最耗时查询"的利器。

-- 启用 pg_stat_statements（需提前在 shared_preload_libraries 中加载）
CREATE EXTENSION pg_stat_statements;

-- 找出平均执行时间最长的 10 条查询
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

关键指标解读：

calls：查询执行次数
total_time：总执行时间（毫秒）
mean_time：平均执行时间（毫秒）
hit_percent：缓冲池命中率（越接近 100% 越好）

3. EXPLAIN ANALYZE：查询计划实战

-- 查看查询计划（不实际执行）
EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

-- 查看查询计划并实际执行（最真实）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;

输出示例：

                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..12486.29 rows=5 width=244) (actual time=0.423..12.337 rows=3 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on orders  (cost=0.00..11485.79 rows=2 width=244) (actual time=6.107..9.871 rows=1 loops=3)
         Filter: (user_id = 12345)
         Rows Removed by Filter: 299999
 Planning Time: 0.098 ms
 Execution Time: 12.401 ms

问题诊断：

Parallel Seq Scan 表示全表扫描（性能杀手！）
Rows Removed by Filter: 299999 表示扫描了 30 万行才找到 1 行
解决方案：在 user_id 上创建索引

MySQL 性能诊断

1. SHOW PROCESSLIST：实时连接监控

-- 查看所有活跃连接
SHOW FULL PROCESSLIST;

-- 查找执行时间超过 5 秒的查询
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) AS current_query
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
  AND TIME > 5
ORDER BY TIME DESC;

2. Performance Schema：深度性能分析

MySQL 的 Performance Schema 提供了更详细的性能数据。

-- 启用 Statements Instrumentation
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

-- 查找最耗时的 SQL 语句
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 AS avg_time_sec,
    SUM_TIMER_WAIT/1000000000 AS total_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

3. EXPLAIN FORMAT=JSON：详细查询计划

-- 查看 JSON 格式的查询计划（比默认格式更详细）
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 12345;

查询计划深度解析

EXPLAIN 完全指南

要优化查询，首先要读懂数据库的执行计划。我们以一个电商订单查询为例：

场景：查找用户 ID=12345 的所有订单，按创建时间倒序排列。

PostgreSQL 查询计划解读

EXPLAIN ANALYZE
SELECT o.id, o.order_no, o.total_amount, o.status, o.created_at
FROM orders o
WHERE o.user_id = 12345
ORDER BY o.created_at DESC
LIMIT 20;

优化前的查询计划（无索引）：

Sort  (cost=21486.29..21486.34 rows=20 width=40) (actual time=125.33..125.34 rows=20 loops=1)
  Sort Key: created_at DESC
  Sort Method: top-N heapsort  Memory: 26kB
  ->  Seq Scan on orders o  (cost=0.00..21485.50 rows=315 width=40) (actual time=0.023..124.98 rows=312 loops=1)
        Filter: (user_id = 12345)
        Rows Removed by Filter: 999688
 Planning Time: 0.098 ms
 Execution Time: 125.41 ms

问题分析：

Seq Scan（全表扫描）：扫描了 100 万行
Rows Removed by Filter: 999688：过滤掉了 99% 的行
Execution Time: 125.41 ms：执行时间 125ms（不达标）

优化方案：创建复合索引

-- 创建复合索引（注意字段顺序！）
CREATE INDEX idx_orders_user_created 
ON orders (user_id, created_at DESC);

优化后的查询计划：

Index Scan using idx_orders_user_created on orders o  (cost=0.42..123.45 rows=20 width=40) (actual time=0.023..0.456 rows=20 loops=1)
  Index Cond: (user_id = 12345)
  Limit: 20
 Planning Time: 0.312 ms
 Execution Time: 0.471 ms

性能提升：125ms → 0.47ms，提升 265 倍！

MySQL 查询计划解读

EXPLAIN FORMAT=JSON
SELECT o.id, o.order_no, o.total_amount, o.status, o.created_at
FROM orders o
WHERE o.user_id = 12345
ORDER BY o.created_at DESC
LIMIT 20;

关键字段解读：

select_type：查询类型（SIMPLE、PRIMARY、SUBQUERY 等）
type：访问类型（重要！）
- ALL：全表扫描（最差）
- index：全索引扫描
- range：索引范围扫描
- ref：索引查找（较好）
- eq_ref：唯一索引查找（最好）
key：实际使用的索引
rows：预估扫描行数
filtered：过滤比例

索引设计与优化实战

B-tree 索引策略

B-tree 是 PostgreSQL 和 MySQL 的默认索引类型，适用于等值查询和范围查询。

单字段索引

-- PostgreSQL / MySQL 通用语法
CREATE INDEX idx_users_email ON users (email);

-- 如果是低频查询，使用 CREATE INDEX CONCURRENTLY（不锁表）
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

适用场景：

高频等值查询：WHERE email = '...'
高频范围查询：WHERE created_at > '2026-01-01'

不适用场景：

低基数列（如 gender 只有 'M'/'F' 两个值）
频繁更新的列（索引维护成本高）

复合索引设计黄金法则

最左前缀匹配原则：复合索引只能从最左列开始匹配。

-- 创建复合索引
CREATE INDEX idx_orders_user_status_time 
ON orders (user_id, order_status, created_at);

以下查询可以用到索引：

-- ✅ 用到 (user_id)
SELECT * FROM orders WHERE user_id = 12345;

-- ✅ 用到 (user_id, order_status)
SELECT * FROM orders WHERE user_id = 12345 AND order_status = 'PAID';

-- ✅ 用到 (user_id, order_status, created_at)
SELECT * FROM orders 
WHERE user_id = 12345 AND order_status = 'PAID' 
  AND created_at > '2026-01-01';

以下查询无法用到索引：

-- ❌ 缺少最左列 user_id
SELECT * FROM orders WHERE order_status = 'PAID';

-- ❌ 跳过了 order_status
SELECT * FROM orders WHERE user_id = 12345 AND created_at > '2026-01-01';

字段顺序选择原则：

等值查询的列放前面（user_id = 12345）
范围查询的列放后面（created_at > '...'）
区分度高的列放前面（user_id 比 order_status 区分度高）

部分索引（PostgreSQL 专属）

部分索引只索引表的一部分数据，可以大幅减少索引大小。

-- 只索引"未支付"和"已支付"的订单（占比 20%）
CREATE INDEX idx_orders_active 
ON orders (user_id, created_at)
WHERE order_status IN ('UNPAID', 'PAID');

效果：

索引大小减少 80%
查询未支付/已支付订单时性能不变
查询"已取消"订单时走全表扫描（但可以接受）

表达式索引

如果查询中使用了函数，普通索引会失效。

-- ❌ 普通索引无法加速这个查询
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- ✅ 创建表达式索引
CREATE INDEX idx_users_email_lower 
ON users (LOWER(email));

GiST/GIN 索引（PostgreSQL 专属）

对于数组、JSONB、地理空间数据，B-tree 索引无能为力，需要使用 GiST 或 GIN 索引。

GIN 索引：加速数组和 JSONB 查询

-- 创建测试表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[],  -- 数组类型
    content JSONB  -- JSONB 类型
);

-- 插入测试数据
INSERT INTO articles (title, tags, content)
VALUES 
('PostgreSQL 性能优化', ARRAY['数据库', 'PostgreSQL', '性能'], '{"author":"茄子","views":1000}'),
('MySQL 实战', ARRAY['数据库', 'MySQL', '实战'], '{"author":"茄子","views":2000}');

-- ❌ 没有索引，全表扫描
SELECT * FROM articles WHERE tags @> ARRAY['数据库'];

-- ✅ 创建 GIN 索引
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- ✅ JSONB 查询也需要 GIN 索引
CREATE INDEX idx_articles_content ON articles USING GIN (content);

-- 现在这些查询都能走索引
SELECT * FROM articles WHERE tags @> ARRAY['数据库'];
SELECT * FROM articles WHERE content @> '{"author":"茄子"}';

GiST 索引：地理空间查询

-- 需要安装 PostGIS 扩展
CREATE EXTENSION postgis;

-- 创建带有地理位置的表
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOGRAPHY(Point)
);

-- 创建 GiST 索引
CREATE INDEX idx_stores_location ON stores USING GIST (location);

-- 查找距离用户 5 公里内的商店
SELECT * FROM stores
WHERE ST_DWithin(
    location::geography,
    ST_MakePoint(116.404, 39.915)::geography,
    5000  -- 5 公里
);

配置参数调优

PostgreSQL 关键配置

编辑 postgresql.conf（通常在 /etc/postgresql/16/main/postgresql.conf 或 $PGDATA/postgresql.conf）。

1. 内存配置

# 共享缓冲池（推荐：物理内存的 25%）
shared_buffers = 4GB

# 用于复杂排序和哈希操作的内存（推荐：物理内存的 4%-16%）
work_mem = 64MB

# 维护操作（如 VACUUM、CREATE INDEX）的内存
maintenance_work_mem = 512MB

# 总缓存大小（推荐：物理内存的 50%-75%）
effective_cache_size = 12GB

2. 并行查询配置

# 启用并行查询
max_parallel_workers_per_gather = 4  # 每个查询最多 4 个并行 worker
max_parallel_workers = 16            # 系统总共最多 16 个并行 worker
parallel_setup_cost = 1000           # 并行启动成本（调大可减少并行）
parallel_tuple_cost = 0.1            # 并行 tuple 传输成本

3. WAL 配置（影响写入性能）

# WAL 缓冲区大小
wal_buffers = 16MB

# WAL 写入策略（性能 vs 安全性权衡）
synchronous_commit = off  # 关闭同步提交（性能提升 30%，但可能丢 1 秒数据）
wal_writer_delay = 200ms  # WAL writer 刷盘间隔

4. 自动 VACUUM 配置

# 启用自动 VACUUM
autovacuum = on

# 当表更新/删除行数超过 (reltuples * 0.2 + 50) 时触发 VACUUM
autovacuum_vacuum_scale_factor = 0.2

# 当表更新/删除行数超过 (reltuples * 0.1 + 50) 时触发 ANALYZE
autovacuum_analyze_scale_factor = 0.1

# 最少的 VACUUM 间隔（避免频繁 VACUUM）
autovacuum_vacuum_threshold = 50

MySQL InnoDB 调优

编辑 my.cnf（通常在 /etc/mysql/my.cnf 或 /etc/my.cnf）。

1. InnoDB 缓冲池

[mysqld]
# InnoDB 缓冲池大小（推荐：物理内存的 70%-80%）
innodb_buffer_pool_size = 12G

# 缓冲池实例数（提高并发性，推荐：缓冲池大小 / 1GB）
innodb_buffer_pool_instances = 12

# 预热缓冲池（重启后快速恢复性能）
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

2. 日志与刷盘策略

# Redo log 大小（增大可减少刷盘频率）
innodb_log_file_size = 1G
innodb_log_files_in_group = 2

# 刷盘策略（性能 vs 安全性权衡）
innodb_flush_log_at_trx_commit = 2  
# 0 = 每秒刷盘（最快，可能丢 1 秒数据）
# 1 = 每次提交刷盘（最安全，最慢）
# 2 = 每次提交写 OS cache，每秒刷盘（推荐）

# 双写缓冲区（保护数据页完整性）
innodb_doublewrite = ON

3. 连接与线程配置

# 最大连接数
max_connections = 500

# 线程缓存大小
thread_cache_size = 50

# 连接超时（秒）
wait_timeout = 300
interactive_timeout = 300

高并发场景优化

连接池配置

问题：每一个数据库连接都消耗内存（PostgreSQL 约 10MB/连接，MySQL 约 5MB/连接）。如果应用层没有连接池，每次请求都新建连接，会导致：

连接建立/销毁开销大（TCP 握手 + 认证）
并发连接数爆炸（可能超过 max_connections）

解决方案：使用连接池（如 HikariCP、PgBouncer）。

HikariCP 配置示例（Java）

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
config.setUsername("appuser");
config.setPassword("secret");

// 连接池大小（推荐：CPU 核心数 * 2）
config.setMaximumPoolSize(32);
config.setMinimumIdle(10);

// 连接超时（毫秒）
config.setConnectionTimeout(30000);

// 连接最大存活时间（防止连接老化）
config.setMaxLifetime(1800000);

// 连接空闲超时
config.setIdleTimeout(600000);

HikariDataSource ds = new HikariDataSource(config);

PgBouncer 配置（PostgreSQL 专用）

PgBouncer 是 PostgreSQL 的轻量级连接池，支持三种模式：

session：会话级复用（最兼容）
transaction：事务级复用（推荐）
statement：语句级复用（最激进）

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# 连接池模式
pool_mode = transaction

# 连接池大小
max_client_conn = 1000
default_pool_size = 50

# 连接生命周期
server_lifetime = 3600
server_idle_timeout = 600

锁竞争处理

场景：高并发下，多个事务同时更新同一行数据，会导致锁等待。

-- 查找当前锁等待情况（PostgreSQL）
SELECT 
    blocked_pid,
    blocked_user,
    blocking_pid,
    blocking_user,
    blocked_query,
    blocking_query
FROM pg_locks
WHERE NOT granted;

解决方案：

缩短事务时间：避免在事务中执行耗时的非数据库操作（如调用外部 API）
按固定顺序加锁：防止死锁
使用 SELECT ... FOR UPDATE NOWAIT：避免无限等待

-- ❌ 可能无限等待
BEGIN;
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
-- 如果其他事务持锁，会一直等待...

-- ✅ 立即返回（如果锁冲突则报错）
BEGIN;
SELECT * FROM orders WHERE id = 123 FOR UPDATE NOWAIT;
-- 或设置锁等待超时
SET lock_timeout = '5s';
SELECT * FROM orders WHERE id = 123 FOR UPDATE;

事务隔离级别选择

PostgreSQL 和 MySQL 都支持四种隔离级别：

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	❌ 可能	❌ 可能	❌ 可能	最快
READ COMMITTED	✅ 不可能	❌ 可能	❌ 可能	快
REPEATABLE READ	✅ 不可能	✅ 不可能	❌ 可能（MySQL 已解决）	中
SERIALIZABLE	✅ 不可能	✅ 不可能	✅ 不可能	慢

推荐配置：

Web 应用：READ COMMITTED（PostgreSQL 默认）
金融交易：REPEATABLE READ 或 SERIALIZABLE
数据分析：READ COMMITTED（允许不可重复读可接受）

-- 设置事务隔离级别（PostgreSQL）
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 执行查询...
COMMIT;

-- 设置全局默认隔离级别（MySQL）
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

分区表与分片策略

PostgreSQL 分区表

PostgreSQL 10+ 原生支持声明式分区。

范围分区（按时间）

-- 创建主表
CREATE TABLE orders (
    id BIGSERIAL,
    user_id INTEGER,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 创建分区表（按月分区）
CREATE TABLE orders_2026_05 PARTITION OF orders
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE orders_2026_06 PARTITION OF orders
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- 自动创建下个月的分区（通过 cron 或 pg_partman 扩展）

优势：

查询特定月份数据时，自动跳过其他分区（分区裁剪）
删除旧数据只需 DROP TABLE orders_2025_01（比 DELETE 快 100 倍）

MySQL 分区表

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    user_id INT,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at DATETIME,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202605 VALUES LESS THAN (202606),
    PARTITION p202606 VALUES LESS THAN (202607),
    PARTITION p202607 VALUES LESS THAN (202608)
);

分片（Sharding）

当单表数据量超过 5000 万行时，考虑分片。

分片策略：

范围分片：user_id 1-1000000 → Shard 1，1000001-2000000 → Shard 2
- 优点：容易扩展
- 缺点：可能产生数据热点
哈希分片：shard_id = hash(user_id) % 16
- 优点：数据分布均匀
- 缺点：扩缩容需要数据迁移
一致性哈希：解决扩缩容时的数据迁移问题

工具推荐：

PostgreSQL：使用 Citus 扩展（开源分布式数据库）
MySQL：使用 Vitess（YouTube 开源的 MySQL 分片方案）

实战案例：电商平台数据库优化

背景

某电商平台（日订单量 50 万）反馈：

订单查询页面加载时间 > 3 秒
数据库 CPU 使用率持续 > 80%
偶尔出现死锁错误

诊断过程

1. 找出慢查询

-- PostgreSQL: 启用 pg_stat_statements
SELECT 
    query,
    calls,
    mean_time,
    total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

发现：以下查询平均执行时间 2.5 秒，每天执行 80 万次：

SELECT o.*, u.username, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = ?
ORDER BY o.created_at DESC
LIMIT 20;

2. 分析查询计划

EXPLAIN ANALYZE
SELECT o.*, u.username, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = 12345
ORDER BY o.created_at DESC
LIMIT 20;

问题：

orders 表全表扫描（300 万行）
users 表走主键索引（没问题）
排序操作使用临时磁盘文件（Sort Method: external merge Disk: 1234kB）

3. 优化方案

步骤 1：创建复合索引

CREATE INDEX CONCURRENTLY idx_orders_user_created 
ON orders (user_id, created_at DESC);

步骤 2：优化查询语句

-- ❌ 原查询（SELECT * 返回所有字段，包括不需要的大字段）
SELECT o.*, u.username, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = ?
ORDER BY o.created_at DESC
LIMIT 20;

-- ✅ 优化后（只查询需要的字段）
SELECT o.id, o.order_no, o.total_amount, o.status, o.created_at,
       u.username, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = ?
ORDER BY o.created_at DESC
LIMIT 20;

步骤 3：调整配置参数

# postgresql.conf
work_mem = 64MB  # 增加排序内存，避免磁盘排序
shared_buffers = 4GB  # 增加缓冲池

4. 优化结果

指标	优化前	优化后	提升
查询响应时间	2500ms	15ms	166 倍
数据库 CPU 使用率	82%	35%	-47%
死锁频率	3 次/天	0 次/天	-

监控与运维最佳实践

关键指标监控

PostgreSQL 监控指标

-- 1. 缓冲池命中率（应 > 99%）
SELECT 
    100.0 * blks_hit / (blks_hit + blks_read) AS hit_rate
FROM pg_stat_database
WHERE datname = 'mydb';

-- 2. 长事务（可能阻塞 VACUUM）
SELECT 
    pid,
    usename,
    query_start,
    now() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '5 minutes';

-- 3. 死锁检测
SELECT 
    schemaname,
    relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / (n_live_tup + n_dead_tup), 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_percent DESC;

MySQL 监控指标

-- 1. 缓冲池命中率（应 > 99%）
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 计算：1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

-- 2. 慢查询统计
SHOW STATUS LIKE 'Slow_queries';

-- 3. 连接数使用率
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
-- 计算：Threads_connected / max_connections

自动化运维脚本

自动 VACUUM 脚本（PostgreSQL）

#!/bin/bash
# /opt/scripts/auto_vacuum.sh

DB_NAME="mydb"
THRESHOLD=0.2  # 死元组比例超过 20% 时触发 VACUUM

psql -d $DB_NAME -t -A -c "
SELECT schemaname, relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
  AND n_dead_tup::float / (n_live_tup + n_dead_tup) > $THRESHOLD
" | while read line; do
    SCHEMA=$(echo $line | cut -d'|' -f1)
    TABLE=$(echo $line | cut -d'|' -f2)
    echo "$(date): Vacuuming $SCHEMA.$TABLE"
    psql -d $DB_NAME -c "VACUUM VERBOSE ANALYZE $SCHEMA.$TABLE;"
done

添加到 crontab：

0 3 * * * /opt/scripts/auto_vacuum.sh >> /var/log/auto_vacuum.log 2>&1

2026 年数据库技术趋势

1. AI 驱动的数据库优化

2026 年，AI 已经开始深度参与数据库优化：

索引推荐：如阿里云 RDS PostgreSQL 的"自动索引推荐"功能，基于 AI 分析慢查询，自动推荐最优索引。
参数调优：如 OtterTune（CMU 开源项目），使用机器学习自动调优数据库配置参数。

2. 云原生数据库

存算分离架构：如 Amazon Aurora、Alibaba PolarDB，计算节点和存储节点独立扩展。
Serverless 数据库：如 Neon（PostgreSQL Serverless），按需计费，自动扩缩容。

3. 向量数据库崛起

随着 AI 应用的爆发，向量数据库（如 Pinecone、Milvus、pgvector）成为新宠。PostgreSQL 通过 pgvector 扩展也支持向量检索。

-- 安装 pgvector
CREATE EXTENSION vector;

-- 创建向量字段
CREATE TABLE embeddings (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)  -- OpenAI embedding 维度
);

-- 创建向量索引（IVFFlat 或 HNSW）
CREATE INDEX idx_embeddings_vector 
ON embeddings 
USING ivfflat (embedding vector_cosine_ops);

-- 向量相似度搜索
SELECT * FROM embeddings
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

总结与展望

本文系统性地讲解了 PostgreSQL 和 MySQL 的性能优化技术，从诊断工具、查询计划、索引设计、配置调优、高并发处理、分区分片到实战案例，涵盖了数据库优化的核心领域。

关键要点回顾：

先诊断，后优化：使用 pg_stat_activity、EXPLAIN ANALYZE、pg_stat_statements 等工具找出瓶颈。
索引是双刃剑：创建合适的索引可以提升查询性能 100 倍，但过多的索引会降低写入性能。
配置参数要调优：shared_buffers、work_mem、innodb_buffer_pool_size 等关键参数对性能影响巨大。
高并发需要连接池：使用 HikariCP 或 PgBouncer 避免连接风暴。
监控是保障：定期监控缓冲池命中率、长事务、死元组比例等指标，防患于未然。

展望 2026 年及未来：

AI 将深度参与数据库优化（自动索引推荐、参数调优、异常检测）
云原生数据库成为主流（存算分离、Serverless、多租户隔离）
向量数据库与关系型数据库融合（如 PostgreSQL + pgvector）

数据库性能优化是一场持久战，需要持续学习、实践、总结。希望本文能成为你优化数据库性能的有力武器。

参考文献：

PostgreSQL Official Documentation: https://www.postgresql.org/docs/
MySQL Official Documentation: https://dev.mysql.com/doc/
Use The Index, Luke: https://use-the-index-luke.com/
PostgreSQL Performance Tuning: https://wiki.postgresql.org/wiki/Performance_Optimization

复制全文生成海报 PostgreSQL MySQL 数据库性能优化查询计划索引设计高并发