1
没有量化 → 凭感觉优化 → 可能优化了不重要的路径
2
有了量化 → 精准定位瓶颈 → 每次优化都有据可循

1
-- 启用慢查询日志
2
SET GLOBAL slow_query_log = ON;
3
SET GLOBAL long_query_time = 1;        -- 超过 1 秒的查询
4
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询
5
SET GLOBAL min_examined_row_limit = 100;        -- 至少扫描 100 行才记录
6

7
-- 查看慢查询日志位置
8
SHOW VARIABLES LIKE 'slow_query_log_file';

1
-- 启用扩展
2
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
3

4
-- 查找最耗时的 Top 10 查询
5
SELECT query,
6
       calls,
7
       round(total_exec_time::numeric, 2) AS total_ms,
8
       round(mean_exec_time::numeric, 2)  AS avg_ms,
9
       round((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct_total
10
FROM pg_stat_statements
11
ORDER BY total_exec_time DESC
12
LIMIT 10;

1
# 基本用法：分析慢查询日志
2
pt-query-digest /var/lib/mysql/mysql-slow.log
3

4
# 输出按总耗时排序的查询指纹
5
# Rank Query ID           Response time  Calls  R/Call  V/M
6
# ==== ================== ============== ====== ======= ====
7
#    1 0x3F8E1A2B4C5D6E7F  1200.1234 62.5%   3421  0.3508  0.01
8
#    2 0x7A8B9C0D1E2F3A4B   450.5678 23.5%   1205  0.3736  0.02
9

10
# 只分析最近 1 小时的慢查询
11
pt-query-digest --since '1h' /var/lib/mysql/mysql-slow.log
12

13
# 将结果保存到数据库
14
pt-query-digest --review h=localhost,D=percona,t=query_review \
15
  /var/lib/mysql/mysql-slow.log

1
-- 问题：扫描 280 万行，返回 10 行
2
SELECT * FROM orders
3
WHERE status = 'pending' AND created_at > '2026-04-01'
4
ORDER BY amount DESC LIMIT 10;
5
-- Query_time: 3.52s  Rows_examined: 2847293  Rows_sent: 10
6

7
-- 分析：EXPLAIN 显示 type=ALL（全表扫描）
8
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-04-01';
9

10
-- 优化：添加联合索引（参考[索引原理](./03-索引原理.md)的最左前缀原则）
11
CREATE INDEX idx_status_created_amount ON orders(status, created_at, amount);
12

13
-- 优化后：索引范围扫描 + 覆盖索引
14
-- Query_time: 0.003s  Rows_examined: 156  Rows_sent: 10

1
-- 问题：phone 字段是 VARCHAR，但查询用了数字
2
SELECT * FROM users WHERE phone = 13800138000;
3
-- MySQL 对 phone 列做了隐式转换，导致索引失效
4
-- Query_time: 2.1s  Rows_examined: 1500000
5

6
-- 优化：使用字符串常量
7
SELECT * FROM users WHERE phone = '13800138000';
8
-- Query_time: 0.002s  Rows_examined: 1

1
-- 问题：循环中执行单条查询，1000 次查询
2
-- 应用层伪代码：
3
-- for order in orders:  # 1000 条
4
--     SELECT * FROM users WHERE id = order.user_id;
5

6
-- 优化：批量查询
7
SELECT * FROM users WHERE id IN (1, 2, 3, ..., 1000);
8

9
-- 更优：JOIN 查询
10
SELECT o.*, u.name, u.email
11
FROM orders o
12
JOIN users u ON o.user_id = u.id
13
WHERE o.status = 'pending';

1
; pgbouncer.ini
2
[databases]
3
mydb = host=127.0.0.1 port=5432 dbname=mydb
4

5
[pgbouncer]
6
; 池化模式选择
7
pool_mode = transaction    ; session / transaction / statement
8

9
; 连接数配置
10
max_client_conn = 1000     ; 最大客户端连接数
11
default_pool_size = 25     ; 每个数据库/用户对的默认池大小
12
min_pool_size = 5          ; 最小池大小
13
reserve_pool_size = 5      ; 预留池大小（突发流量）
14
reserve_pool_timeout = 3   ; 等待预留连接超时（秒）
15

16
; 超时配置
17
server_idle_timeout = 600  ; 服务端空闲连接超时（秒）
18
client_idle_timeout = 0    ; 客户端空闲超时（0=不超时）
19
query_timeout = 30         ; 查询超时（秒）
20
query_wait_timeout = 120   ; 等待服务端连接超时（秒）
21

22
; 日志
23
log_connections = 0
24
log_disconnections = 0
25
stats_period = 60

1
最优连接数 = CPU 核心数 × (1 + 等待时间 / 计算时间)
2

3
示例：
4
- 8 核 CPU
5
- 查询计算时间 5ms，I/O 等待时间 45ms
6
- 最优连接数 = 8 × (1 + 45/5) = 80

1
# 解决方案一：布隆过滤器（推荐）
2
# 在缓存层前加一层布隆过滤器，不存在的 key 直接拦截
3
import pybloom_live
4

5
bloom = pybloom_live.ScalableBloomFilter(initial_capacity=1000000)
6

7
# 启动时加载所有合法 key
8
for user_id in db.query("SELECT id FROM users"):
9
    bloom.add(f"user:{user_id}")
10

11
def get_user(user_id):
12
    key = f"user:{user_id}"
13
    if key not in bloom:          # 布隆过滤器说不存在，直接返回
14
        return None
15
    return cache_aside_get(key)   # 可能存在，走正常缓存流程
16

17
# 解决方案二：缓存空值（短 TTL）
18
def get_user_with_null_cache(user_id):
19
    key = f"user:{user_id}"
20
    data = redis.get(key)
21
    if data is not None:
22
        return None if data == "NULL" else deserialize(data)
23
    user = db.query("SELECT * FROM users WHERE id = %s", user_id)
24
    redis.setex(key, 300 if user is None else 3600,
25
                "NULL" if user is None else serialize(user))
26
    return user

1
# 解决方案：互斥锁（只允许一个请求回源）
2
import redis
3

4
r = redis.Redis()
5

6
def get_user_with_mutex(user_id):
7
    key = f"user:{user_id}"
8
    lock_key = f"lock:{key}"
9
    data = r.get(key)
10
    if data is not None:
11
        return deserialize(data)
12
    acquired = r.set(lock_key, "1", nx=True, ex=10)  # 互斥锁，10 秒超时
13
    if acquired:
14
        try:
15
            user = db.query("SELECT * FROM users WHERE id = %s", user_id)
16
            r.setex(key, 3600, serialize(user))
17
            return user
18
        finally:
19
            r.delete(lock_key)
20
    else:
21
        time.sleep(0.1)
22
        return get_user_with_mutex(user_id)  # 等待并重试

1
# 解决方案一：随机过期时间
2
def set_with_jitter(key, value, base_ttl=3600, jitter_range=300):
3
    jitter = random.randint(-jitter_range, jitter_range)
4
    r.setex(key, base_ttl + jitter, value)
5

6
# 解决方案二：多级缓存（本地缓存 + Redis）
7
from cachetools import TTLCache
8
local_cache = TTLCache(maxsize=10000, ttl=60)  # 本地缓存 60 秒
9

10
def get_user_multi_level(user_id):
11
    key = f"user:{user_id}"
12
    if key in local_cache: return local_cache[key]          # L1
13
    data = r.get(key)
14
    if data is not None:
15
        result = deserialize(data)
16
        local_cache[key] = result
17
        return result                                        # L2
18
    user = db.query("SELECT * FROM users WHERE id = %s", user_id)
19
    r.setex(key, 3600, serialize(user))
20
    local_cache[key] = user
21
    return user                                              # L3

1
# String：简单 KV 缓存
2
SET user:1001 '{"name":"张三","age":28}' EX 3600
3

4
# Hash：对象缓存（比 String 更节省内存，支持部分读取）
5
HSET user:1001 name "张三" age 28 city "北京"
6
HGET user:1001 name
7

8
# ZSet：排行榜缓存（利用跳表的有序特性）
9
ZADD leaderboard 9500 "player:A" 8800 "player:B" 9200 "player:C"
10
ZREVRANGE leaderboard 0 9 WITHSCORES  # Top 10
11

12
# Bitmap：用户签到（极致节省内存）
13
SETBIT sign:uid:1001:202604 20 1     # 4 月 20 日签到
14
BITCOUNT sign:uid:1001:202604         # 本月签到次数

1
# my.cnf — MySQL 8.0 性能调优配置
2

3
[mysqld]
4
# ===== InnoDB 缓冲池 =====
5
# 核心参数：缓存数据和索引的内存区域
6
# 建议设为物理内存的 60%~80%（独占服务器）
7
innodb_buffer_pool_size = 8G
8

9
# 缓冲池实例数（减少锁竞争）
10
# 建议：每个实例 1G 以上
11
innodb_buffer_pool_instances = 8
12

13
# ===== I/O 能力 =====
14
# 每秒后台刷新的页数（SSD 建议 10000+，HDD 建议 200）
15
innodb_io_capacity = 10000
16
innodb_io_capacity_max = 20000
17

18
# 刷新邻接页（SSD 关闭，HDD 开启）
19
innodb_flush_neighbors = 0
20

21
# ===== 日志与持久化 =====
22
# binlog 刷盘策略
23
# 0=依赖 OS 刷盘  1=每次提交刷盘（最安全）  N=每 N 次提交刷盘
24
sync_binlog = 1
25

26
# redo log 刷盘策略
27
# 1=每次提交刷盘（最安全）  2=每次提交写 OS 缓存，每秒刷盘
28
innodb_flush_log_at_trx_commit = 1
29

30
# ===== 并发与连接 =====
31
innodb_thread_concurrency = 0   # 0=不限制（推荐，InnoDB 自管理）
32
max_connections = 500
33
thread_cache_size = 100

1
# postgresql.conf — PostgreSQL 16 性能调优配置
2

3
# ===== 共享缓冲区 =====
4
# 数据库共享内存，缓存数据页
5
# 建议设为物理内存的 25%（不超过 40%）
6
shared_buffers = 4GB
7

8
# ===== 查询规划器缓存估计 =====
9
# 规划器假设可用于缓存的内存（不实际分配）
10
# 建议设为物理内存的 50%~75%
11
effective_cache_size = 12GB
12

13
# ===== 排序与哈希操作 =====
14
# 每个操作的最大内存（按连接分配，注意总内存）
15
work_mem = 64MB
16

17
# 维护操作内存（VACUUM、CREATE INDEX）
18
maintenance_work_mem = 1GB
19

20
# ===== WAL 配置 =====
21
# WAL 写入策略
22
# fsync = on（安全）/ off（危险但快）
23
fsync = on
24
synchronous_commit = on         # on（安全）/ off（性能）
25
wal_buffers = 64MB
26

27
# ===== 检查点与自动清理 =====
28
max_wal_size = 4GB              # WAL 最大大小
29
autovacuum = on
30
autovacuum_max_workers = 4

1
# docker-compose.yml — 添加 Exporter
2
services:
3
  mysql-exporter:
4
    image: prom/mysqld-exporter
5
    environment:
6
      DATA_SOURCE_NAME: "exporter:password@(mysql:3306)/"
7
    ports: ["9104:9104"]
8
  postgres-exporter:
9
    image: prometheuscommunity/postgres-exporter
10
    environment:
11
      DATA_SOURCE_NAME: "postgresql://exporter:password@postgres:5432/postgres?sslmode=disable"
12
    ports: ["9187:9187"]

1
# MySQL 缓冲池命中率
2
rate(mysql_global_status_buffer_pool_read_requests[5m])
3
  / (rate(mysql_global_status_buffer_pool_read_requests[5m])
4
  + rate(mysql_global_status_buffer_pool_reads[5m]))
5

6
# MySQL 活跃连接数
7
mysql_global_status_threads_running
8

9
# PostgreSQL 缓存命中率
10
rate(pg_stat_database_blks_hit{datname="mydb"}[5m])
11
  / (rate(pg_stat_database_blks_hit{datname="mydb"}[5m])
12
  + rate(pg_stat_database_blks_read{datname="mydb"}[5m]))

1
# alert_rules.yml — 数据库告警规则
2
groups:
3
  - name: database_alerts
4
    rules:
5
      # 慢查询激增
6
      - alert: HighSlowQueryRate
7
        expr: rate(mysql_global_status_slow_queries[5m]) > 10
8
        for: 5m
9
        labels:
10
          severity: warning
11
        annotations:
12
          summary: "MySQL 慢查询激增"
13
          description: "慢查询速率 {{ $value }} 次/秒，持续 5 分钟"
14

15
      # 连接数接近上限
16
      - alert: ConnectionPoolExhaustion
17
        expr: mysql_global_status_threads_running / mysql_global_variables_max_connections > 0.8
18
        for: 3m
19
        labels:
20
          severity: critical
21
        annotations:
22
          summary: "MySQL 连接池即将耗尽"
23
          description: "活跃连接占比 {{ $value | humanizePercentage }}"
24

25
      # PostgreSQL 复制延迟
26
      - alert: PostgreSQLReplicationLag
27
        expr: pg_replication_lag > 30
28
        for: 5m
29
        labels:
30
          severity: critical
31
        annotations:
32
          summary: "PostgreSQL 复制延迟超过 30 秒"
33
          description: "当前延迟 {{ $value }} 秒"