1
SELECT name, age FROM users WHERE age > 18;

1
-- 假设 active_users 视图定义为：
2
-- SELECT * FROM users WHERE status = 'active'
3

4
-- 原始查询
5
SELECT name FROM active_users WHERE age > 18;
6

7
-- 重写后
8
SELECT name FROM users WHERE status = 'active' AND age > 18;

1
-- 原始：相关子查询（每行都执行一次子查询）
2
SELECT * FROM orders o
3
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.city = '北京');
4

5
-- 重写后：Semi Join（一次扫描完成）
6
SELECT o.* FROM orders o
7
SEMI JOIN users u ON u.id = o.user_id AND u.city = '北京';

1
-- 原始
2
SELECT * FROM users WHERE age > 10 + 8 AND 1 = 1;
3

4
-- 重写后
5
SELECT * FROM users WHERE age > 18;

1
-- 优化前：先 Join 再过滤（中间结果大）
2
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id
3
WHERE u.city = '北京';
4

5
-- 优化后：先过滤再 Join（中间结果小）
6
SELECT o.* FROM orders o JOIN (SELECT * FROM users WHERE city = '北京') u
7
ON o.user_id = u.id;

1
-- 查看 MySQL 代价常量
2
SELECT * FROM mysql.server_cost;
3
SELECT * FROM mysql.engine_cost;

1
+------------------------------+------------+---------------------+---------+
2
| cost_name                    | cost_value | default_value       | comment |
3
+------------------------------+------------+---------------------+---------+
4
| disk_temptable_create_cost   |       NULL | 20.0                |         |
5
| disk_temptable_row_cost      |       NULL | 0.5                 |         |
6
| key_compare_cost             |       NULL | 0.05                |         |
7
| memory_temptable_create_cost |       NULL | 1.0                 |         |
8
| memory_temptable_row_cost    |       NULL | 0.1                 |         |
9
| row_evaluate_cost            |       NULL | 0.1                 |         |
10
+------------------------------+------------+---------------------+---------+

1
# 选择率估算示例（简化版）
2
def estimate_selectivity(column, predicate, stats):
3
    if predicate.type == "equality":
4
        # 等值条件：1/NDV（均匀分布假设）
5
        return 1.0 / stats.ndv[column]
6
    elif predicate.type == "range":
7
        # 范围条件：用直方图估算
8
        return stats.histogram[column].fraction_in_range(
9
            predicate.lower, predicate.upper
10
        )
11
    elif predicate.type == "in_list":
12
        # IN 列表：每个值的频率之和
13
        return sum(stats.mcv[column].get(v, 1.0/stats.ndv[column])
14
                   for v in predicate.values)

1
-- MySQL 收集统计信息
2
ANALYZE TABLE users;
3

4
-- 查看索引的 Cardinality
5
SHOW INDEX FROM users;
6

7
-- 查看统计信息详情
8
SELECT * FROM information_schema.STATISTICS
9
WHERE TABLE_NAME = 'users';

1
-- PostgreSQL：创建多列统计信息（捕获 city 和 age 的相关性）
2
CREATE STATISTICS s1 (ndistinct, dependencies, mcv) ON city, age FROM users;
3
ANALYZE users;
4

5
-- 查看统计信息
6
SELECT * FROM pg_stats WHERE tablename = 'users';

1
# Volcano 模型的 Hash Join 伪代码
2
class HashJoin(Operator):
3
    def open(self):
4
        self.hash_table = {}
5
        self.build_child.open()
6
        while (row := self.build_child.next()) is not None:
7
            self.hash_table[self.key(row)] = row
8
        self.build_child.close()
9
        self.probe_child.open()
10

11
    def next(self):
12
        while (row := self.probe_child.next()) is not None:
13
            if self.key(row) in self.hash_table:
14
                return self.emit(self.hash_table[self.key(row)], row)
15
        return None

1
EXPLAIN SELECT u.name, COUNT(*) AS order_count
2
FROM users u
3
JOIN orders o ON u.id = o.user_id
4
WHERE u.city = '北京' AND o.status = 'completed'
5
GROUP BY u.name;

1
+----+-------------+-------+------------+------+-------------------+-------------------+---------+----------------+------+----------+-------------------------------------------+
2
| id | select_type | table | type       | key  | key_len | ref     | rows    | filtered | Extra                                     |
3
+----+-------------+-------+------------+------+-------------------+-------------------+---------+----------+-------------------------------------------+
4
|  1 | SIMPLE      | u     | ref        | idx_city | 152   | const   |     500 |   100.00 | Using index condition                     |
5
|  1 | SIMPLE      | o     | ref        | idx_user_status | 12 | test.u.id |    10 |    33.33 | Using where; Using index; Using temporary |
6
+----+-------------+-------+------------+------+-------------------+-------------------+---------+----------+-------------------------------------------+

1
EXPLAIN ANALYZE
2
SELECT u.name, COUNT(*) AS order_count
3
FROM users u
4
JOIN orders o ON u.id = o.user_id
5
WHERE u.city = '北京' AND o.status = 'completed'
6
GROUP BY u.name;

1
HashAggregate  (cost=1250.00..1260.00 rows=200 width=20)
2
               (actual time=8.234..8.456 rows=150 loops=1)
3
  Group Key: u.name
4
  Batches: 1  Memory Usage: 40kB
5
  ->  Hash Join  (cost=450.00..1200.00 rows=5000 width=20)
6
                  (actual time=3.123..7.890 rows=4800 loops=1)
7
        Hash Cond: (o.user_id = u.id)
8
        ->  Seq Scan on orders o  (cost=0.00..350.00 rows=5000 width=12)
9
                                   (actual time=0.012..2.345 rows=4800 loops=1)
10
              Filter: (status = 'completed'::text)
11
              Rows Removed by Filter: 5200
12
        ->  Hash  (cost=200.00..200.00 rows=500 width=12)
13
                   (actual time=0.789..0.789 rows=500 loops=1)
14
              Buckets: 1024  Batches: 1  Memory Usage: 28kB
15
              ->  Seq Scan on users u  (cost=0.00..200.00 rows=500 width=12)
16
                                        (actual time=0.008..0.567 rows=500 loops=1)
17
                    Filter: (city = '北京'::text)
18
Planning Time: 0.234 ms
19
Execution Time: 8.567 ms

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
# 按总耗时排序，取 Top 10
2
mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log
3

4
# 按次数排序（找出频繁执行的查询）
5
mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.log
6

7
# 按返回行数排序
8
mysqldumpslow -s r -t 10 /var/lib/mysql/mysql-slow.log

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

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
-- 问题：扫描 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
-- 优化：添加联合索引（参考[索引原理与失效分析](./mysql-索引原理与失效分析.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
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
3
-- type: ALL, rows: 1000000, Extra: Using where
4

5
-- 快：传入字符串，走索引
6
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';
7
-- type: ref, key: idx_phone, rows: 1

1
-- 慢：索引在 (status)，但 ORDER BY created_at 无索引
2
EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 10;
3
-- Extra: Using where; Using filesort
4

5
-- 快：建立覆盖排序的联合索引
6
CREATE INDEX idx_status_created ON orders(status, created_at);
7
EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 10;
8
-- type: ref, key: idx_status_created, Extra: Using index condition

1
-- 慢：大表做外表，扫描 100 万行
2
EXPLAIN SELECT * FROM large_table l JOIN small_table s ON l.key = s.key;
3
-- Nested Loop, 驱动表 large_table
4

5
-- 快：小表做外表，只扫描 1000 行
6
EXPLAIN SELECT /*+ QB_NAME(main) */ * FROM small_table s
7
JOIN large_table l ON l.key = s.key;
8
-- Hash Join, build 侧 small_table

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
-- MySQL：USE INDEX / FORCE INDEX
2
SELECT * FROM users USE INDEX(idx_city) WHERE city = '北京';
3
SELECT * FROM users FORCE INDEX(idx_city) WHERE city = '北京';
4

5
-- USE INDEX：建议使用，优化器仍可忽略
6
-- FORCE INDEX：强制使用，除非无法使用（如索引不包含所需列）

1
-- PostgreSQL：通过 pg_hint_plan 扩展
2
/*+ SeqScan(users) HashJoin(users orders) */
3
SELECT u.name, COUNT(*)
4
FROM users u JOIN orders o ON u.id = o.user_id
5
WHERE u.city = '北京' GROUP BY u.name;

1
-- 第一次执行：city = '北京' 返回 50 万行，优化器选择 Seq Scan
2
PREPARE get_users(VARCHAR) AS SELECT * FROM users WHERE city = $1;
3
EXECUTE get_users('北京');
4

5
-- 第二次执行：city = '拉萨' 返回 100 行，但复用了 Seq Scan 的计划
6
EXECUTE get_users('拉萨');

1
spring:
2
  datasource:
3
    druid:
4
      # 核心配置
5
      initial-size: 5                # 初始化连接数
6
      min-idle: 5                    # 最小空闲连接
7
      max-active: 20                 # 最大活跃连接
8
      max-wait: 60000                # 获取连接超时（ms）
9

10
      # 连接保活
11
      validation-query: SELECT 1
12
      test-while-idle: true          # 空闲时检测
13
      time-between-eviction-runs-millis: 60000  # 检测间隔
14
      min-evictable-idle-time-millis: 300000    # 最小空闲时间
15

16
      # 监控与防火墙
17
      filters: stat,wall             # 统计 + SQL 防火墙
18
      connection-properties: druid.stat.slowSqlMillis=1000  # 慢 SQL 阈值

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   ; 等待服务端连接超时（秒）

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
34

35
# ===== 排序与 Join 缓冲 =====
36
# 每个线程的排序缓冲区（按连接分配，注意总内存）
37
sort_buffer_size = 4M
38
# 每个 Join 的缓冲区（无索引 Join 时使用）
39
join_buffer_size = 4M
40

41
# ===== 慢查询日志 =====
42
slow_query_log = 1
43
long_query_time = 1
44
log_queries_not_using_indexes = 1

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 }} 秒"