标签: 性能调优

一、SQL 优化概述

1.1 为什么需要 SQL 优化

1.2 SQL 优化的一般流程

二、慢查询日志

2.1 配置慢查询日志

-- 查看慢查询日志状态
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询记录

-- 记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL log_throttle_queries_not_using_indexes = 100;  -- 每分钟最多记录100条

HSXISAWD2026/3/20大约 13 分钟

MySQL 性能调优

一、性能调优概述

1.1 调优层次

1.2 调优流程

二、系统层优化

2.1 操作系统配置

# /etc/sysctl.conf

# 网络优化
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 10
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_keepalive_time = 120

# 内存优化
vm.swappiness = 1                    # 尽量不使用 swap
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5

# 文件句柄
fs.file-max = 65535
fs.aio-max-nr = 1048576

# 应用配置
sysctl -p

HSXISAWD2026/3/20大约 8 分钟

PostgreSQL 查询优化

一、执行计划基础

1.1 EXPLAIN 命令

EXPLAIN 是分析查询性能的最重要工具，它展示了查询优化器选择的执行计划。

-- 基本用法
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 显示实际执行信息
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- 完整选项
EXPLAIN (
    ANALYZE,      -- 实际执行并显示统计
    BUFFERS,      -- 显示缓冲区使用情况
    COSTS,        -- 显示成本估算（默认开启）
    TIMING,       -- 显示实际时间
    FORMAT TEXT   -- 输出格式：TEXT, XML, JSON, YAML
) SELECT * FROM users WHERE id = 1;

-- 不实际执行（只看计划）
EXPLAIN (COSTS, VERBOSE) SELECT * FROM users WHERE id = 1;

HSXISAWD2026/3/20大约 16 分钟

PostgreSQL 性能调优与运维

一、性能调优概述

1.1 性能优化层次

1.2 性能问题诊断流程

-- 1. 检查系统整体状态
SELECT
    numbackends AS connections,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    blks_read AS disk_reads,
    blks_hit AS cache_hits,
    round(100.0 * blks_hit / NULLIF(blks_read + blks_hit, 0), 2) AS cache_hit_ratio,
    tup_returned AS rows_returned,
    tup_fetched AS rows_fetched,
    tup_inserted AS rows_inserted,
    tup_updated AS rows_updated,
    tup_deleted AS rows_deleted
FROM pg_stat_database
WHERE datname = current_database();

-- 2. 检查活动连接
SELECT
    state,
    COUNT(*) AS count,
    MAX(NOW() - query_start) AS max_duration
FROM pg_stat_activity
GROUP BY state;

-- 3. 检查慢查询
SELECT
    pid,
    NOW() - query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < NOW() - INTERVAL '5 seconds'
ORDER BY duration DESC;

-- 4. 检查锁等待
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON
    blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

HSXISAWD2026/3/20大约 16 分钟