杂记

发布日期: 2026-02-26

文章字数: 3.7k

数据库性能瓶颈初步诊断

概述

数据库性能瓶颈通常表现在以下几个方面：

慢查询 - SQL 执行时间过长
连接数过多 - 连接池耗尽
锁等待 - 事务锁竞争
I/O 瓶颈 - 磁盘读写慢
CPU 瓶颈 - 复杂计算或全表扫描
内存不足 - 缓存命中率低

本章涵盖 MySQL、PostgreSQL、Redis、MongoDB 等主流数据库。

一、MySQL 性能诊断

1.1 快速诊断 - 查看数据库状态

-- 查看数据库运行状态
SHOW STATUS;

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 查看慢查询数量
SHOW STATUS LIKE 'Slow_queries';

-- 查看 QPS (每秒查询数)
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Uptime';
-- QPS = Questions / Uptime

-- 查看 TPS (每秒事务数)
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';

1.2 慢查询诊断

开启慢查询日志：

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（临时生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询

永久配置（my.cnf）：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

分析慢查询日志：

# 使用 mysqldumpslow 分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: 按查询时间排序
# -t 10: 显示前10条

# 按查询次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 使用 pt-query-digest（更强大）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

1.3 实时查看正在执行的查询

-- 查看当前所有连接和正在执行的查询
SHOW PROCESSLIST;

-- 查看完整 SQL（不截断）
SHOW FULL PROCESSLIST;

-- 查看执行时间超过2秒的查询
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' AND TIME > 2 
ORDER BY TIME DESC;

-- 杀死慢查询
KILL <process_id>;

1.4 SQL 执行计划分析

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- 更详细的分析
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = '[email protected]';

-- 查看实际执行统计（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

EXPLAIN 关键字段解读：

字段	含义	优化建议
`type`	连接类型	ALL(全表扫描)最差，const/eq_ref最好
`possible_keys`	可能使用的索引	NULL 说明没有可用索引
`key`	实际使用的索引	NULL 说明未使用索引
`rows`	扫描的行数	越少越好
`Extra`	额外信息	Using filesort/Using temporary 需要优化

type 类型从好到坏：

system > const > eq_ref > ref > range > index > ALL

1.5 索引分析

-- 查看表的索引
SHOW INDEX FROM table_name;

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 查看索引统计信息
SHOW TABLE STATUS LIKE 'table_name';

-- 分析表（更新索引统计信息）
ANALYZE TABLE table_name;

1.6 锁等待诊断

-- 查看当前锁等待情况（MySQL 5.7+）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 查看 InnoDB 锁等待
SHOW ENGINE INNODB STATUS\G

-- 查看事务信息
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待超时设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

1.7 连接数诊断

-- 查看最大连接数配置
SHOW VARIABLES LIKE 'max_connections';

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看历史最大连接数
SHOW STATUS LIKE 'Max_used_connections';

-- 查看连接来源
SELECT USER, HOST, COUNT(*) 
FROM information_schema.PROCESSLIST 
GROUP BY USER, HOST;

-- 调整最大连接数（临时）
SET GLOBAL max_connections = 500;

1.8 缓冲池（Buffer Pool）分析

-- 查看缓冲池大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 查看缓冲池使用情况
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 计算缓冲池命中率
-- 命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100
-- 正常应该 > 99%

-- 查看缓冲池详细信息
SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;

1.9 MySQL 性能监控工具

使用 mysqladmin：

# 查看状态
mysqladmin -u root -p status

# 持续监控
mysqladmin -u root -p -i 1 extended-status | grep -E "Threads_connected|Questions|Slow_queries"

# 查看变量
mysqladmin -u root -p variables

使用 mytop：

# 安装
yum install mytop  # CentOS
apt install mytop  # Ubuntu

# 运行（类似 top 的界面）
mytop -u root -p password

使用 Percona Toolkit：

# 安装
yum install percona-toolkit

# 分析慢查询
pt-query-digest /var/log/mysql/slow.log

# 查找重复索引
pt-duplicate-key-checker

# 在线修改表结构（不锁表）
pt-online-schema-change --alter "ADD INDEX idx_name (name)" D=db,t=table --execute

二、PostgreSQL 性能诊断

2.1 查看数据库状态

-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 查看最大连接数
SHOW max_connections;

-- 查看数据库大小
SELECT pg_database.datname, 
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;

-- 查看表大小
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

2.2 慢查询诊断

开启慢查询日志（postgresql.conf）：

log_min_duration_statement = 1000  # 记录超过1秒的查询
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'all'  # 记录所有SQL

查看正在执行的查询：

-- 查看当前活动查询
SELECT pid, usename, datname, state, query, 
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 终止慢查询
SELECT pg_terminate_backend(pid);

-- 查看长时间运行的事务
SELECT pid, usename, datname, state, 
       now() - xact_start AS duration
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC;

2.3 执行计划分析

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- 查看详细执行计划
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM users WHERE email = '[email protected]';

-- 查看 JSON 格式
EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE email = '[email protected]';

2.4 索引分析

-- 查看表的索引
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- 查看索引使用情况
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- 查看未使用的索引
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%_pkey';

-- 查看索引大小
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

2.5 锁等待诊断

-- 查看锁等待
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

2.6 缓存命中率

-- 查看缓存命中率
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;
-- 命中率应该 > 0.99

-- 查看索引缓存命中率
SELECT 
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit) as idx_hit,
    sum(idx_blks_hit) / (sum(idx_blks_hit) + sum(idx_blks_read)) as ratio
FROM pg_statio_user_indexes;

2.7 VACUUM 和 ANALYZE

-- 查看表的膨胀情况
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
       n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 手动 VACUUM
VACUUM ANALYZE table_name;

-- 查看自动 VACUUM 配置
SHOW autovacuum;
SHOW autovacuum_naptime;

三、Redis 性能诊断

3.1 查看 Redis 状态

# 连接 Redis
redis-cli

# 查看服务器信息
INFO

# 查看特定部分
INFO stats        # 统计信息
INFO memory       # 内存信息
INFO replication  # 复制信息
INFO cpu          # CPU信息
INFO clients      # 客户端信息

3.2 慢查询诊断

# 查看慢查询配置
CONFIG GET slowlog-log-slower-than  # 微秒，默认10000（10ms）
CONFIG GET slowlog-max-len          # 慢查询日志长度

# 设置慢查询阈值（临时）
CONFIG SET slowlog-log-slower-than 1000  # 1ms

# 查看慢查询日志
SLOWLOG GET 10  # 获取最近10条慢查询

# 查看慢查询日志长度
SLOWLOG LEN

# 清空慢查询日志
SLOWLOG RESET

3.3 实时监控命令

# 实时监控所有命令
MONITOR

# 查看客户端连接
CLIENT LIST

# 查看当前连接数
INFO clients | grep connected_clients

# 查看阻塞的客户端
CLIENT LIST | grep blocked

3.4 内存分析

# 查看内存使用
INFO memory

# 查看大 key
redis-cli --bigkeys

# 查看内存使用详情（Redis 4.0+）
MEMORY STATS

# 查看某个 key 的内存占用
MEMORY USAGE key_name

# 查看内存碎片率
INFO memory | grep mem_fragmentation_ratio
# 正常值：1.0 - 1.5
# > 1.5：内存碎片较多
# < 1.0：可能发生了 swap

3.5 性能测试

# 使用 redis-benchmark 测试
redis-benchmark -h 127.0.0.1 -p 6379 -c 100 -n 100000
# -c: 并发连接数
# -n: 请求总数

# 测试特定命令
redis-benchmark -t set,get -n 100000 -q

# 测试 pipeline
redis-benchmark -n 100000 -P 16

3.6 持久化影响

# 查看 RDB 配置
CONFIG GET save

# 查看 AOF 配置
CONFIG GET appendonly
CONFIG GET appendfsync

# 查看最后一次 save 时间
INFO persistence | grep rdb_last_save_time

# 手动触发 save（会阻塞）
SAVE

# 后台 save（不阻塞）
BGSAVE

四、MongoDB 性能诊断

4.1 查看数据库状态

// 查看服务器状态
db.serverStatus()

// 查看数据库统计
db.stats()

// 查看集合统计
db.collection_name.stats()

// 查看当前操作
db.currentOp()

// 查看连接数
db.serverStatus().connections

4.2 慢查询诊断

// 开启 profiling（级别0-2）
// 0: 关闭
// 1: 记录慢查询
// 2: 记录所有查询
db.setProfilingLevel(1, { slowms: 100 })  // 记录超过100ms的查询

// 查看 profiling 状态
db.getProfilingStatus()

// 查看慢查询
db.system.profile.find().limit(10).sort({ ts: -1 }).pretty()

// 查看最慢的查询
db.system.profile.find({ millis: { $gt: 100 } }).sort({ millis: -1 }).limit(10)

4.3 执行计划分析

// 查看执行计划
db.collection.find({ field: value }).explain("executionStats")

// 查看详细执行计划
db.collection.find({ field: value }).explain("allPlansExecution")

// 关键指标：
// - executionTimeMillis: 执行时间
// - totalDocsExamined: 扫描的文档数
// - totalKeysExamined: 扫描的索引键数
// - stage: COLLSCAN（全表扫描）需要优化

4.4 索引分析

// 查看集合的索引
db.collection.getIndexes()

// 查看索引使用情况
db.collection.aggregate([{ $indexStats: {} }])

// 创建索引
db.collection.createIndex({ field: 1 })

// 删除索引
db.collection.dropIndex("index_name")

// 重建索引
db.collection.reIndex()

4.5 锁等待诊断

// 查看当前锁
db.currentOp({ $or: [{ waitingForLock: true }, { lockStats: { $exists: true } }] })

// 杀死慢操作
db.killOp(opid)

// 查看锁统计
db.serverStatus().locks

五、数据库性能优化通用策略

5.1 SQL 优化

避免全表扫描：

-- 不好：全表扫描
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 好：使用索引
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

**避免 SELECT ***：

-- 不好：查询所有字段
SELECT * FROM users WHERE id = 1;

-- 好：只查询需要的字段
SELECT id, name, email FROM users WHERE id = 1;

使用 LIMIT：

-- 不好：返回所有结果
SELECT * FROM users WHERE status = 'active';

-- 好：限制返回数量
SELECT * FROM users WHERE status = 'active' LIMIT 100;

批量操作：

-- 不好：逐条插入
INSERT INTO users (name) VALUES ('user1');
INSERT INTO users (name) VALUES ('user2');

-- 好：批量插入
INSERT INTO users (name) VALUES ('user1'), ('user2'), ('user3');

5.2 索引优化

创建合适的索引：

-- 单列索引
CREATE INDEX idx_email ON users(email);

-- 复合索引（注意顺序）
CREATE INDEX idx_status_created ON users(status, created_at);

-- 覆盖索引（包含查询所需的所有字段）
CREATE INDEX idx_cover ON users(status, id, name);

索引使用原则：

在 WHERE、JOIN、ORDER BY 的字段上建索引
选择性高的字段优先（值分布均匀）
复合索引遵循最左前缀原则
避免在低选择性字段建索引（如性别）
定期删除未使用的索引

5.3 连接池优化

连接池配置建议：

# 最小连接数
minPoolSize=10

# 最大连接数（根据数据库 max_connections 设置）
maxPoolSize=50

# 连接超时时间
connectionTimeout=30000

# 空闲连接超时时间
idleTimeout=600000

# 最大生命周期
maxLifetime=1800000

5.4 缓存策略

多级缓存架构：

应用层
  ↓
本地缓存（Caffeine/Guava）
  ↓
分布式缓存（Redis）
  ↓
数据库

缓存更新策略：

Cache Aside（旁路缓存）：先更新数据库，再删除缓存
Write Through（写穿）：同时更新缓存和数据库
Write Behind（写回）：先更新缓存，异步更新数据库

5.5 分库分表

垂直拆分：

按业务模块拆分（用户库、订单库、商品库）
将大字段拆分到独立表

水平拆分：

按范围分片（如按日期）
按哈希分片（如按用户ID）
按地理位置分片

5.6 读写分离

应用
  ↓
写操作 → 主库（Master）
           ↓ 复制
读操作 → 从库（Slave1, Slave2, Slave3）

注意事项：

主从延迟问题
强一致性要求的读操作走主库
使用中间件（如 MyCat、ShardingSphere）

六、数据库监控工具

6.1 MySQL 监控工具

**Percona Monitoring and Management (PMM)**：

# 安装 PMM Server
docker run -d -p 80:80 --name pmm-server percona/pmm-server:2

# 安装 PMM Client
wget https://www.percona.com/downloads/pmm2/pmm-client-2.x.x.tar.gz
tar -xzf pmm-client-2.x.x.tar.gz
./pmm-admin config --server-url=http://pmm-server:80
./pmm-admin add mysql --username=root --password=password

Prometheus + Grafana：

# 使用 mysqld_exporter
docker run -d -p 9104:9104 \
  -e DATA_SOURCE_NAME="user:password@(hostname:3306)/" \
  prom/mysqld-exporter

6.2 PostgreSQL 监控工具

pg_stat_statements：

-- 安装扩展
CREATE EXTENSION pg_stat_statements;

-- 查看 SQL 统计
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

pgBadger：

# 分析 PostgreSQL 日志
pgbadger /var/log/postgresql/postgresql.log -o report.html

6.3 Redis 监控工具

RedisInsight：

官方 GUI 工具
支持实时监控、慢查询分析、内存分析

redis-stat：

# 安装
gem install redis-stat

# 运行
redis-stat --server

七、数据库性能诊断检查清单

快速检查（5分钟）

查看当前连接数是否接近上限
查看是否有慢查询
查看是否有锁等待
查看 CPU 和内存使用率
查看磁盘 I/O 是否繁忙

深入分析（30分钟）

分析慢查询日志，找出 TOP 10 慢查询
检查慢查询的执行计划
检查索引使用情况，找出未使用的索引
检查缓存命中率
检查表和索引的大小
检查是否有表锁或行锁竞争
检查连接池配置是否合理

长期优化（持续进行）

建立性能监控系统
定期分析慢查询趋势
优化高频查询的 SQL
定期清理无用数据和索引
规划分库分表策略
实施读写分离
建立数据库备份和恢复机制

八、常见数据库性能问题

问题1：查询突然变慢

可能原因：

数据量增长，索引失效
统计信息过期
锁等待
缓存失效

排查步骤：

-- 1. 查看执行计划
EXPLAIN SELECT ...;

-- 2. 更新统计信息
ANALYZE TABLE table_name;

-- 3. 查看锁等待
SHOW ENGINE INNODB STATUS;

-- 4. 查看缓存命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';

问题2：连接数耗尽

可能原因：

连接未正确释放
连接池配置过小
慢查询占用连接

解决方案：

-- 查看连接来源
SELECT USER, HOST, COUNT(*) FROM information_schema.PROCESSLIST GROUP BY USER, HOST;

-- 杀死空闲连接
KILL <process_id>;

-- 调整最大连接数
SET GLOBAL max_connections = 500;

问题3：死锁

排查：

-- 查看最近的死锁信息
SHOW ENGINE INNODB STATUS;

-- 开启死锁日志
SET GLOBAL innodb_print_all_deadlocks = ON;

预防：

保持事务简短
按相同顺序访问表和行
使用合适的隔离级别
添加合适的索引减少锁范围

总结

数据库性能诊断的关键步骤：

监控指标：QPS、TPS、连接数、慢查询数
定位问题：慢查询日志、执行计划、锁等待
分析原因：索引、SQL、配置、硬件
优化方案：SQL优化、索引优化、架构优化
持续改进：监控系统、定期分析、容量规划

以下：

80% 的性能问题来自 20% 的 SQL
索引是性能优化的第一选择
监控和分析比优化更重要

杂记

本篇

数据库性能瓶颈初步诊断

2026-02-26 杂记

杂记

2026-02-26 是阿蓁啊