database-optimization

用途

数据库优化技能用于诊断和修复慢查询、优化索引并调优数据库性能。它面向需要排查数据库性能问题的开发与运维场景，提供针对 PostgreSQL 和 MySQL 的具体命令与检查方法。

主要能力

EXPLAIN 分析

支持对 PostgreSQL 和 MySQL 执行执行计划分析。

PostgreSQL 示例：

# 基础 explain
psql $DATABASE_URL -c "EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';"

# 带实际执行统计
psql $DATABASE_URL -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE email = 'test@example.com';"

# 供工具使用的 JSON 格式
psql $DATABASE_URL -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM users WHERE status = 'active';"

MySQL 示例：

# 基础 explain
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';"

# 扩展 explain
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';"

分析时需关注的信号：

大表上的 Seq Scan 表示缺少索引
高行数的 Nested Loop 可考虑 JOIN 优化
无索引的 Sort 应在 ORDER BY 列上添加索引
大数据集上的 Hash Join 需检查内存设置
行数估算与实际相差过大时，运行 ANALYZE 更新统计信息

索引管理

PostgreSQL 索引相关命令：

# 列出所有索引
psql $DATABASE_URL -c "SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename;"

# 查找未使用的索引
psql $DATABASE_URL -c "SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;"

# 查找缺失的索引（存在顺序扫描的表）
psql $DATABASE_URL -c "SELECT relname, seq_scan, seq_tup_read, idx_scan FROM pg_stat_user_tables WHERE seq_scan > 1000 ORDER BY seq_scan DESC LIMIT 20;"

# 创建索引
psql $DATABASE_URL -c "CREATE INDEX CONCURRENTLY idx_users_email ON users(email);"

# 创建复合索引
psql $DATABASE_URL -c "CREATE INDEX CONCURRENTLY idx_orders_user_date ON orders(user_id, created_at DESC);"

# 索引大小
psql $DATABASE_URL -c "SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;"

慢查询检测

PostgreSQL：

# 当前正在运行的查询（按持续时间排序）
psql $DATABASE_URL -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND query NOT ILIKE '%pg_stat%' ORDER BY duration DESC LIMIT 10;"

# 启用慢查询日志（在 postgresql.conf 中或按会话设置）
psql $DATABASE_URL -c "SET log_min_duration_statement = 1000;"  -- 记录超过 1 秒的查询

# 表统计信息
psql $DATABASE_URL -c "SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_analyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"

MySQL：

# 显示慢查询日志状态
mysql -e "SHOW VARIABLES LIKE 'slow_query%';"

# 当前正在运行的查询
mysql -e "SHOW FULL PROCESSLIST;" | grep -v Sleep

表维护

# PostgreSQL — 更新统计信息
psql $DATABASE_URL -c "ANALYZE users;"
psql $DATABASE_URL -c "ANALYZE;"  -- 所有表

# PostgreSQL — 回收死行
psql $DATABASE_URL -c "VACUUM ANALYZE users;"

# 表大小
psql $DATABASE_URL -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;"

连接池检查

# PostgreSQL — 当前连接数
psql $DATABASE_URL -c "SELECT count(*), state FROM pg_stat_activity GROUP BY state;"

# 最大连接数
psql $DATABASE_URL -c "SHOW max_connections;"

# 连接时长
psql $DATABASE_URL -c "SELECT pid, usename, application_name, now() - backend_start AS connection_age FROM pg_stat_activity ORDER BY connection_age DESC LIMIT 10;"

使用方式

该技能以命令行方式使用，通过 psql 或 mysql 客户端连接数据库并执行上述 SQL 语句。命令中通过环境变量（如 $DATABASE_URL、$DB_USER、$DB_PASS、$DB_NAME）传入连接信息。

适用场景

排查和修复慢查询
分析执行计划以定位性能瓶颈
查找未使用或缺失的索引并创建索引
检测慢查询与当前运行查询
执行表维护（ANALYZE、VACUUM）
检查数据库连接数与连接池状态

限制与注意事项

应在 staging 环境而非生产环境运行 EXPLAIN ANALYZE，因为它会实际执行查询。
CREATE INDEX CONCURRENTLY 可避免表锁，但耗时更长。
死行（n_dead_tup）会拖慢查询，应定期运行 VACUUM 或确保 autovacuum 已调优。
复合索引的列顺序很重要：等值列放前面，范围列放最后。
不要为所有列建索引——每个索引都会拖慢写入，只为实际查询的列建索引。