MySQL主流社区版本线上生产环境深度调优指南

# 准备测试数据
sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=root \
--mysql-password=password --mysql-db=sbtest --table-size=1000000 \
--tables=10 --threads=8 --events=0 --time=300 oltp_read_write prepare

# 运行测试
sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=root \
--mysql-password=password --mysql-db=sbtest --table-size=1000000 \
--tables=10 --threads=8 --events=0 --time=300 --report-interval=1 \
oltp_read_write run

# /etc/fstab 中的优化选项
/dev/sdb1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0

# 针对SSD推荐使用none或kyber调度器
echo 'none' > /sys/block/sdb/queue/scheduler
# 或
echo 'kyber' > /sys/block/sdb/queue/scheduler

# /etc/sysctl.conf 中的优化设置

# 增加TCP最大缓冲区大小
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

# 增加文件描述符限制
fs.file-max = 65536

# 减少TCP连接等待时间
net.ipv4.tcp_fin_timeout = 15
net.ipv4.tcp_tw_reuse = 1

# 增加最大连接数
net.core.somaxconn = 65535

# 内存过量使用设置（谨慎调整）
vm.overcommit_memory = 0
vm.swappiness = 1

# 增加内存映射数量
vm.max_map_count = 262144

# 启动MySQL时使用numactl干预内存分配
numactl --interleave=all /usr/sbin/mysqld

# 或在BIOS中禁用NUMA（不推荐，会降低内存带宽）

# InnoDB缓冲池大小，通常设置为可用内存的50%-80%
innodb_buffer_pool_size = 128G

# 缓冲池实例数，减少锁争用（每实例至少1GB）
innodb_buffer_pool_instances = 16

# 在MySQL 8.0中，默认使用原生InnoDB缓冲池管理
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

# 重做日志大小，更大的日志减少检查点频率
innodb_log_file_size = 4G
innodb_log_files_in_group = 3

# 日志缓冲区大小
innodb_log_buffer_size = 64M

# 刷新日志策略
innodb_flush_log_at_trx_commit = 1  # 完全ACID兼容，最安全
# innodb_flush_log_at_trx_commit = 2  # 折中方案，每秒刷新
# innodb_flush_log_at_trx_commit = 0  # 最佳性能，但可能丢失最多1秒数据

# 异步I/O，通常启用以提高性能
innodb_use_native_aio = ON

# 刷新邻居页，SSD上建议关闭
innodb_flush_neighbors = 0

# I/O容量设置，根据存储性能调整
innodb_io_capacity = 2000      # 对于SATA SSD
innodb_io_capacity_max = 4000  # 突发I/O容量

# 预读设置，SSD上可以减少或关闭
innodb_read_ahead_threshold = 0

# InnoDB线程并发
innodb_thread_concurrency = 0  # 0表示无限制，让操作系统调度

# 连接数设置
max_connections = 512          # 根据应用需求调整
thread_cache_size = 100        # 缓存线程数，减少连接创建开销

# MySQL 8.0线程池插件（Percona Server和MySQL企业版）
# thread_handling = pool-of-threads

# 排序和临时表
sort_buffer_size = 4M          # 每个会话，不宜设置过大
join_buffer_size = 4M          # 每个联接操作，不宜设置过大
tmp_table_size = 256M          # 内存临时表最大大小
max_heap_table_size = 256M     # 内存表最大大小

# 查询缓存（MySQL 8.0已移除）
# query_cache_type = 0
# query_cache_size = 0

# 启用性能schema，但注意开销
performance_schema = ON

# 选择性启用收集器，减少开销
performance_schema_consumer_events_statements_current = ON
performance_schema_consumer_events_statements_history = ON
performance_schema_consumer_events_waits_current = OFF
performance_schema_consumer_events_waits_history = OFF

# 启用慢查询日志
slow_query_log = ON
long_query_time = 1            # 超过1秒的查询记录
log_queries_not_using_indexes = OFF  # 避免日志过大
slow_query_log_file = /var/log/mysql/slow.log

# 使用Percona的微秒精度支持
log_timestamps = SYSTEM

-- 优化前
CREATE TABLE users (
  id VARCHAR(36) PRIMARY KEY,  -- 使用UUID作为主键
  name VARCHAR(100),
  email VARCHAR(255),
  created_at TIMESTAMP,
  status ENUM('active','inactive','pending','suspended','deleted')
);

-- 优化后
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 使用整数主键
  uuid BINARY(16) UNIQUE,          -- 如果需要UUID，使用二进制存储
  name VARCHAR(64) NOT NULL,       -- 限制长度
  email VARCHAR(128) NOT NULL,     -- 限制长度
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status TINYINT UNSIGNED NOT NULL DEFAULT 0,  -- 使用整数代替ENUM
  INDEX (uuid),
  INDEX (email),
  INDEX (created_at)
);

-- 创建高效复合索引
CREATE INDEX idx_user_activity ON users (status, created_at, country);

-- 分析索引使用情况
EXPLAIN SELECT user_id, COUNT(*) 
FROM user_actions 
WHERE action_date >= CURDATE() - INTERVAL 7 DAY
AND action_type = 'login'
GROUP BY user_id
HAVING COUNT(*) > 5;

-- 使用不可见索引测试效果
ALTER TABLE users ADD INDEX idx_email_domain (email_domain) INVISIBLE;
ALTER TABLE users ALTER INDEX idx_email_domain VISIBLE;

-- 检查索引使用情况
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

-- 重建碎片化索引
ALTER TABLE table_name ENGINE=InnoDB;  -- 重建表
-- 或使用pt-online-schema-change在线操作

-- 按日期范围分区
CREATE TABLE logs (
    id INT NOT NULL,
    log_date DATETIME NOT NULL,
    message TEXT,
    PRIMARY KEY (id, log_date)
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 分区维护
ALTER TABLE logs DROP PARTITION p2020;  -- 删除旧数据
ALTER TABLE logs REORGANIZE PARTITION pfuture INTO (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 基本EXPLAIN
EXPLAIN SELECT * FROM users WHERE status = 1 AND country = 'US';

-- EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 1 AND country = 'US';

-- JSON格式输出，包含更多信息
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE status = 1 AND country = 'US';

-- 分析连接查询
EXPLAIN 
SELECT u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'US'
AND o.status = 'completed'
ORDER BY o.order_date DESC
LIMIT 10;

-- 优化前：全表扫描
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 优化后：范围查询
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- 优化前：低效JOIN
SELECT u.*, o.* 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'US';

-- 优化后：先过滤再JOIN
SELECT u.*, o.* 
FROM (SELECT * FROM users WHERE country = 'US') u
JOIN orders o ON u.id = o.user_id;

-- 或使用STRAIGHT_JOIN指导连接顺序
SELECT STRAIGHT_JOIN u.*, o.* 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'US';

-- 优化前：相关子查询
SELECT * FROM users u 
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.total > 1000
);

-- 优化后：使用JOIN
SELECT DISTINCT u.* 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;

-- 或使用派生表
SELECT u.* 
FROM users u
JOIN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total > 1000
) o ON u.id = o.user_id;

-- 优化前：OFFSET效率低
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10 OFFSET 10000;

-- 优化后：使用游标分页
SELECT * FROM orders 
WHERE order_date < '2023-06-01'  -- 上一页最后一条记录的日期
ORDER BY order_date DESC 
LIMIT 10;

-- 或使用覆盖索引优化
SELECT o.* 
FROM orders o
JOIN (
    SELECT id 
    FROM orders 
    ORDER BY order_date DESC 
    LIMIT 10 OFFSET 10000
) tmp ON o.id = tmp.id;

-- 获取每个用户最近一次订单
SELECT user_id, order_date, amount
FROM (
    SELECT 
        user_id, 
        order_date, 
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rn
    FROM orders
) ranked
WHERE rn = 1;

-- 计算移动平均
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM orders;

-- 使用CTE简化复杂查询
WITH user_totals AS (
    SELECT user_id, SUM(amount) as total_spent
    FROM orders
    WHERE order_date >= '2023-01-01'
    GROUP BY user_id
),
top_users AS (
    SELECT user_id, total_spent
    FROM user_totals
    WHERE total_spent > 1000
    ORDER BY total_spent DESC
    LIMIT 100
)
SELECT 
    u.name,
    u.email,
    tu.total_spent,
    COUNT(o.id) as order_count
FROM top_users tu
JOIN users u ON tu.user_id = u.id
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email, tu.total_spent;

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别（需要在会话或全局设置）
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- 不同隔离级别的特点：
-- READ UNCOMMITTED: 最高性能，但可能脏读
-- READ COMMITTED:   平衡性能与一致性（推荐）
-- REPEATABLE READ:  默认级别，保证可重复读
-- SERIALIZABLE:     最严格，性能最低

-- 死锁检测和回滚配置
innodb_deadlock_detect = ON      # 启用死锁检测
innodb_lock_wait_timeout = 50    # 锁等待超时（秒）

-- 分析死锁信息
SHOW ENGINE INNODB STATUS;       # 查看最新死锁信息

-- 应用程序中应实现重试逻辑处理死锁

-- 创建全文索引
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content)
) ENGINE=InnoDB;

-- 配置全文索引参数
innodb_ft_min_token_size = 3     # 最小词元长度
ft_min_word_len = 3              # MyISAM最小词长度
innodb_ft_enable_stopword = ON   # 启用停用词

-- 自然语言模式搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('mysql optimization' IN NATURAL LANGUAGE MODE);

-- 布尔模式搜索（更灵活）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+mysql -oracle* optimization' IN BOOLEAN MODE);

-- 查询扩展搜索（增加相关结果）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);

-- 创建生成列并建立索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    profile JSON,
    -- 提取JSON中的email字段并创建索引
    email VARCHAR(255) AS (profile->>'$.email') STORED,
    INDEX idx_email (email)
);

-- 多值索引（MySQL 8.0.17+）
CREATE TABLE products (
    id INT PRIMARY KEY,
    attributes JSON,
    INDEX idx_tags ((CAST(attributes->'$.tags' AS CHAR(255) ARRAY)))
);

-- 使用多值索引查询
SELECT * FROM products 
WHERE JSON_OVERLAPS(attributes->'$.tags', CAST('["sale","new"]' AS JSON));

-- 避免在WHERE条件中使用JSON_EXTRACT
SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.age') > 30;  -- 不推荐

-- 使用生成列和索引
ALTER TABLE users ADD COLUMN age INT AS (profile->>'$.age') STORED;
CREATE INDEX idx_age ON users(age);
SELECT * FROM users WHERE age > 30;  -- 推荐

-- 使用JSON_TABLE转换JSON为关系数据
SELECT u.id, j.*
FROM users u,
JSON_TABLE(
    u.profile,
    '$.addresses[*]' COLUMNS(
        address VARCHAR(255) PATH '$',
        type VARCHAR(20) PATH '$.type'
    )
) j
WHERE j.type = 'home';

# 主库配置
sync_binlog = 1                  # 每次提交同步binlog到磁盘
innodb_flush_log_at_trx_commit = 1 # 完全持久化

# 从库配置
skip_slave_start = 1             # 启动时不自动开始复制
read_only = 1                    # 设置从库为只读
super_read_only = 1              # 包括超级用户也只读

# 并行复制配置
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8       # 并行工作线程数
slave_preserve_commit_order = 1  # 保持提交顺序

-- 查看复制状态
SHOW SLAVE STATUS\G

-- 监控复制延迟
SELECT 
    NOW() - MAX(ts) AS replication_lag
FROM (
    SELECT 
        UNIX_TIMESTAMP(ts) AS ts 
    FROM mysql.slave_relay_log_info 
    UNION ALL 
    SELECT 
        UNIX_TIMESTAMP(LAST_QUEUED_TIMESTAMP) AS ts 
    FROM performance_schema.replication_applier_status_by_worker
) t;

-- 处理复制错误
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;  # 跳过1个错误事件
START SLAVE;

#!/bin/bash
# 每日维护脚本示例

# 备份
mysqldump --single-transaction --all-databases | gzip > /backup/full-$(date +%Y%m%d).sql.gz

# 清理旧数据
mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

# 分析表
mysql -e "ANALYZE LOCAL TABLE table1, table2, table3;"

# 检查表完整性
mysql -e "CHECK TABLE critical_table1, critical_table2 FAST;"

-- 使用事件调度器进行自监控
CREATE EVENT monitor_slow_queries
ON SCHEDULE EVERY 5 MINUTE
DO
BEGIN
    -- 检查慢查询数量
    IF (SELECT COUNT(*) FROM mysql.slow_log 
        WHERE start_time > NOW() - INTERVAL 5 MINUTE) > 100 THEN
        -- 调用外部程序发送警报
        CALL sys.execute_shell('send-alert.sh "High slow query count"');
    END IF;
END;

# 使用mysqldump进行并行备份
mysqldump --single-transaction --parallel=8 --all-databases > backup.sql

# 使用Percona XtraBackup进行热备份
xtrabackup --backup --parallel=4 --target-dir=/backup/ful
xtrabackup --prepare --target-dir=/backup/full

# 使用mydumper进行多线程备份
mydumper -u root -p password -o /backup -t 8

-- 使用ProxySQL实现智能路由
-- 写操作发送到主库
-- 读操作分发到从库
-- 基于查询规则实现自动分流

-- 配置示例
INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (10, 'master', 3306), (20, 'slave1', 3306), (20, 'slave2', 3306);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES 
(1, 1, '^SELECT.*FOR UPDATE', 10, 1),      -- 写SELECT发送到主库
(2, 1, '^SELECT', 20, 1),                  -- 读SELECT发送到从库
(3, 1, '.*', 10, 1);                       -- 其他所有查询发送到主库

# MySQL StatefulSet配置示例
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
spec:
  serviceName: "mysql"
  replicas: 3
  template:
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        resources:
          requests:
            memory: "8Gi"
            cpu: "2000m"
          limits:
            memory: "8Gi"
            cpu: "4000m"
        volumeMounts:
        - name: mysql-data
          mountPath: /var/lib/mysql
        - name: mysql-config
          mountPath: /etc/mysql/conf.d
  volumeClaimTemplates:
  - metadata:
      name: mysql-data
    spec:
      accessModes: [ "ReadWriteOnce" ]
      resources:
        requests:
          storage: 100Gi
      storageClassName: "ssd"

-- 查看当前连接状态
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep';

-- 分析连接来源
SELECT USER, HOST, COUNT(*) 
FROM information_schema.PROCESSLIST 
GROUP BY USER, HOST 
ORDER BY COUNT(*) DESC;

-- 查看连接错误数
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Connection_errors%';