[mysqld]
# 基础配置
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# 连接数配置
max_connections = 500
max_connect_errors = 10000

# 缓存配置（关键！）
innodb_buffer_pool_size = 4G  # 物理内存的50-70%
query_cache_size = 256M  # 查询缓存（读多写少场景）
thread_cache_size = 128

# 日志配置
log-error = /var/log/mysqld.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# InnoDB优化
innodb_flush_log_at_trx_commit = 2  # 性能优先（0=最快但不安全，1=最安全但慢）
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000  # SSD设置2000+，HDD设置200
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 表结构优化
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_large_prefix = 1

-- 全表扫描（性能杀手）
SELECT * FROM users WHERE email = 'user@example.com';

-- 复合索引顺序错误
CREATE INDEX idx_status_created ON orders(status, created_at);
-- 查询条件顺序不匹配，索引失效
SELECT * FROM orders WHERE created_at > '2026-01-01' AND status = 'paid';

-- 为email字段添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 复合索引顺序正确（区分度高的放前面）
CREATE INDEX idx_created_status ON orders(created_at, status);
-- 查询条件顺序匹配，索引生效
SELECT * FROM orders WHERE created_at > '2026-01-01' AND status = 'paid';

# 连接配置
max_connections = 200
superuser_reserved_connections = 3

# 内存配置（关键！）
shared_buffers = 4GB  # 物理内存的25-40%
effective_cache_size = 12GB  # 物理内存的75%
work_mem = 64MB  # 每个连接的工作内存
maintenance_work_mem = 1GB  # 维护操作内存

# WAL配置
wal_level = replica
wal_buffers = 64MB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9

# 查询优化
random_page_cost = 1.1  # SSD设置为1.1，HDD设置为4.0
effective_io_concurrency = 200  # SSD设置200+，HDD设置2

# 日志配置
log_min_duration_statement = 1000  # 记录执行时间> 1秒的查询
log_checkpoints = on
log_connections = on
log_disconnections = on

# autovacuum配置
autovacuum = on
autovacuum_max_workers = 10
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.02  # 表大小2%的死元组触发VACUUM
autovacuum_analyze_scale_factor = 0.01  # 表大小1%的变化触发ANALYZE

# 内存配置
maxmemory 4gb
maxmemory-policy allkeys-lru  # 内存满时淘汰最近最少使用的键

# 持久化配置（性能 vs 安全权衡）
appendonly yes
appendfsync everysec  # 折中方案：每秒fsync一次

# 网络配置
tcp-backlog 511
timeout 0
tcp-keepalive 300

# 慢查询日志
slowlog-log-slower-than 10000  # 记录执行时间> 10毫秒的命令
slowlog-max-len 128

import redis
import json
from functools import wraps

# 连接Redis
redis_client = redis.Redis(host='localhost', port=6379, db=0)

def cache_it(expire_seconds=300):
    """缓存装饰器"""
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            # 生成缓存键
            cache_key = f"{func.__name__}:{str(args)}:{str(kwargs)}"

            # 尝试从缓存获取
            cached = redis_client.get(cache_key)
            if cached:
                return json.loads(cached)

            # 缓存未命中，执行函数
            result = func(*args, **kwargs)

            # 写入缓存
            redis_client.setex(cache_key, expire_seconds, json.dumps(result))

            return result
        return wrapper
    return decorator

# 使用示例
@cache_it(expire_seconds=600)  # 缓存10分钟
def get_user_by_id(user_id):
    # 模拟数据库查询（耗时操作）
    time.sleep(0.5)  # 模拟0.5秒查询时间
    return {"id": user_id, "name": "Test User"}

                  [应用服务器]
                        |
            +-----------+-----------+
            |                       |
      [主数据库]              [从数据库1]
      (写操作)                  (读操作)
            |
      [从数据库2]
        (读操作)

-- 配置读写分离规则
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES
(1, 1, '^SELECT.*FOR UPDATE$', 1, 1),  -- 写操作到主库（hostgroup 1）
(2, 1, '^SELECT', 2, 1);                 -- 读操作到从库（hostgroup 2）

-- 加载配置到运行时
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    metrics_path: /metrics