适用场景 & 前置条件
| 项目 |
要求 |
| 适用场景 |
日均 10万+ PV 的电商/社交应用高负载业务 |
| MySQL 版本 |
MySQL 5.7.30+ / MySQL 8.0.20+ |
| 操作系统 |
RHEL/CentOS 7.9+ 或 Ubuntu 20.04 LTS+ |
| 内核版本 |
Linux Kernel 4.18+ |
| 资源规格 |
最小 8C16G / 推荐 16C32G（数据库节点） |
| 存储 |
SSD 磁盘，单表 ≥100GB 推荐分库分表 |
| 网络 |
千兆网卡以上，同机房部署 RTT < 1ms |
| 权限要求 |
root 或 mysql 用户 +SELECT、SHOW PROCESSLIST、EXPLAIN 权限 |
| 技能要求 |
了解 MySQL 基础语法、索引原理、事务隔离级别 |
反模式警告
⚠️ 以下场景不推荐使用本方案：
- 低并发个人博客：日均 PV < 1000，引入优化反而增加维护成本，直接用默认配置足够
- 内存表 MEMORY 存储引擎：数据丢失风险高，性能优化效果反而不明显
- Windows 环境生产：MySQL 在 Windows 下性能、可靠性不如 Linux，不推荐生产使用
- 已采用 NoSQL 方案：MongoDB/Redis 主存储的架构，不适合传统 SQL 优化策略
- 缺乏监控告警基础：未部署 Prometheus/Zabbix 的环境，性能瓶颈定位困难
替代方案对比：
| 场景 |
推荐方案 |
理由 |
| 超大表（>1TB） |
Elasticsearch + MySQL |
ES 提供更好的分析查询能力 |
| 实时大数据分析 |
ClickHouse/Druid |
列式存储更适合 OLAP |
| 流式日志存储 |
ELK Stack（Elasticsearch） |
专为时序数据优化 |
环境与版本矩阵
| 组件 |
RHEL 8.5+ |
Ubuntu 22.04 LTS |
MySQL 5.7 |
MySQL 8.0+ |
测试状态 |
| 系统版本 |
RHEL 8.5+ |
Ubuntu 22.04 LTS |
- |
- |
[已实测] |
| MySQL 版本 |
8.0.32（repo） |
8.0.32（apt） |
5.7.40 |
8.0.32+ |
[已实测] |
| 内核版本 |
4.18.0+ |
5.15.0+ |
- |
- |
[已实测] |
| InnoDB 缓冲池 |
80% 总内存 |
80% 总内存 |
- |
- |
[推荐配置] |
| binlog 格式 |
ROW |
ROW |
- |
- |
[已实测] |
| 最小规格 |
8C16G / 100GB SSD |
8C16G / 100GB SSD |
支持 |
支持 |
- |
| 推荐规格 |
16C32G / 500GB SSD |
16C32G / 500GB SSD |
支持 |
支持 |
- |
关键版本差异：
- MySQL 5.7 vs 8.0：8.0 支持不可见索引、直方图统计、Window 函数，查询优化器更聪明 30-40%
- InnoDB 页大小：默认 16KB，大表可考虑 32KB 以减少 B+ 树高度
阅读导航
📖 建议阅读路径：
快速上手（20分钟）：→ 章节 6（快速清单）→ 章节 7（实施步骤 Step 1-6） → 章节 13（附录：关键脚本）
深入理解（60分钟）：→ 章节 8（最小必要原理）→ 章节 7（实施步骤完整版）→ 章节 11（最佳实践 30 条）→ 章节 12（FAQ）
故障排查（应急）：→ 章节 9（常见故障与排错）→ 章节 8（调试思路）
快速清单
- [ ] 诊断阶段
- [ ] 识别慢查询：启用慢查询日志，查询 slow_log 表
- [ ] 获取执行计划：
EXPLAIN FORMAT=JSON SELECT ...，分析 type、rows、filtered
- [ ] 分析表结构：
SHOW CREATE TABLE，确认主键、索引、数据类型
- [ ] 索引优化阶段
- [ ] 添加联合索引：针对 WHERE + ORDER BY + GROUP BY 字段
- [ ] 覆盖索引设计：SELECT 字段全部在索引中，避免回表
- [ ] 移除冗余索引：用 pt-duplicate-key-checker 检测重复索引
- [ ] SQL 改写阶段
- [ ] 避免 SELECT *：仅查询需要字段，减少数据传输
- [ ] 子查询改写：用 JOIN 替代相关子查询
- [ ] LIMIT 优化：大偏移量用“上次 ID”分页法代替 OFFSET
- [ ] 配置调优阶段
- [ ] 调大 InnoDB 缓冲池：至少 80% 物理内存
- [ ] 设置 binlog 刷盘：sync_binlog=1，durability 优先
- [ ] 启用查询缓存：MySQL 5.7 中有效（8.0 已删除）
- [ ] 架构优化阶段
- [ ] 读写分离：主从复制，从库处理 SELECT 查询
- [ ] 分库分表：单表 >100GB 按业务维度水平分片
- [ ] 数据归档：历史数据分区+冷存储，释放热数据空间
实施步骤（核心内容）
系统架构与数据流说明
应用层 → 主 MySQL（可写）
↓
binlog ────→ 从 MySQL 1（只读）
│ ↓
│ 从 MySQL 2（只读）
│
└────→ 读写分离中间件（Mycat/Sharding-JDBC）
↓
分片库 1（用户 ID 0-50w）
分片库 2（用户 ID 50w-100w）
分片库 3（用户 ID 100w-150w）
关键组件与数据流向：
- 应用层：发送 SQL 到主库（写操作）或从库（读操作）
- 主库（Master）：处理所有写入（INSERT/UPDATE/DELETE），实时生成 binlog
- binlog 传输：异步/半同步复制到从库，保证最终一致性
- 从库集群：多个从库共享负载，处理 SELECT 查询，实现读扩展
- 分片路由：根据分片键（如用户 ID）计算目标库，支持水平扩展
- 缓存层（可选）：Redis 缓存热数据，降低数据库访问频率
故障转移流程：
- 主库故障 → Keepalived/MHA 自动提升从库为新主库（2-5s 内切换）
- 读库故障 → 应用自动剔除该从库，其他从库承接流量
Step 1: 慢查询诊断与分析
目标： 识别 top 10 慢查询，获取执行计划与性能指标
RHEL/CentOS 命令：
# 1. 登录 MySQL
mysql -u root -p -h localhost
# 2. 启用慢查询日志（动态开启，重启不保留）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; # 1 秒以上查询记录
SET GLOBAL log_queries_not_using_indexes = 'ON'; # 不走索引的查询也记录
# 3. 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';
# 4. 从命令行查看慢查询（最近 50 条）
tail -50 /var/log/mysql/slow.log
# 5. 使用 pt-query-digest 分析慢查询（需安装 percona-toolkit）
yum install -y percona-toolkit
pt-query-digest /var/log/mysql/slow.log | head -100
Ubuntu/Debian 命令：
# 同上 MySQL 命令相同，日志路径可能不同
apt update && apt install -y percona-toolkit
tail -50 /var/log/mysql/mysql-slow.log
pt-query-digest /var/log/mysql/mysql-slow.log | head -100
关键参数解释：
long_query_time=1：超过 1 秒的查询记录为慢查询（生产环境建议 0.5-1s）log_queries_not_using_indexes：记录全表扫描，便于识别坏查询pt-query-digest：分析工具，输出 Query_time、Lock_time、Rows_examined 等指标
执行前验证：
# 检查慢查询日志是否启用
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log';"
# 预期输出：slow_query_log | OFF（默认关闭）
执行后验证：
# 确认慢查询日志已启用
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log';"
# 预期输出：slow_query_log | ON
# 生成示例慢查询（等待 2 秒）
mysql -u root -p -e "SELECT SLEEP(2);"
# 检查是否记录到日志
tail -5 /var/log/mysql/slow.log | grep "Query_time"
# 预期输出：# Query_time: 2.000123 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
常见错误示例：
❌ 错误 1：ERROR 1227 (42000): Access denied
原因：没有 SUPER 权限，无法设置全局变量
修复：登录时用 root 用户，或授予权限
grant SUPER on *.* to 'mysql_user'@'localhost';
❌ 错误 2：Can‘t create/write to file '/var/log/mysql/slow.log'
原因：日志目录权限不足
修复：chown mysql:mysql /var/log/mysql && chmod 755 /var/log/mysql
幂等性保障：
- 多次执行 SET 命令不会重复启用，安全可重复运行
- 日志文件自动轮转（需配置 logrotate），不会无限增长
回滚要点：
# 关闭慢查询日志
SET GLOBAL slow_query_log = 'OFF';
Step 2: 索引设计与优化
目标： 为慢查询添加合适的索引，提升查询效率
诊断现有索引：
mysql -u root -p << 'EOF'
# 查看表的所有索引
SHOW INDEXES FROM users;
# 预期输出列：Table, Non_unique, Key_name, Seq_in_index, Column_name, Index_type
# 查看表统计信息（MySQL 8.0+）
SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='prod_db' AND TABLE_NAME='users';
EOF
添加联合索引示例：
场景： 查询用户订单，WHERE 条件：user_id=100 AND status=‘paid’ ORDER BY created_at DESC
mysql -u root -p << 'EOF'
# 添加联合索引：(user_id, status, created_at)
# 顺序很关键：WHERE 字段 → ORDER BY 字段 → 覆盖字段
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);
# 验证索引是否被使用
EXPLAIN FORMAT=JSON SELECT * FROM orders
WHERE user_id=100 AND status='paid'
ORDER BY created_at DESC LIMIT 10\G
EOF
关键参数解释：
- 索引顺序：WHERE 等值条件 → WHERE 范围条件 → ORDER BY → 覆盖字段
- 覆盖索引：将 SELECT 需要的字段也放入索引，避免回表查询（性能提升 10-50 倍）
- 前缀索引：字符串字段超过 20 字符时，用前缀索引节省空间（
KEY(name(10))）
执行后验证：
mysql -u root -p << 'EOF'
# 检查执行计划
EXPLAIN SELECT * FROM orders
WHERE user_id=100 AND status='paid'
ORDER BY created_at DESC LIMIT 10;
# 关键字段解释：
# - type: range/ref/eq_ref（越靠前越好，ALL 最差）
# - key: 实际使用的索引名
# - rows: 扫描行数（越少越好）
# - filtered: 使用索引后过滤率（>70% 较好）
EOF
移除冗余索引：
# 安装 percona-toolkit
yum install -y percona-toolkit
# 检测重复索引
pt-duplicate-key-checker -h localhost -u root -p'password' | grep -A 5 "Duplicate"
常见错误示例：
❌ 错误 1：索引未被使用（type=ALL）
原因：索引顺序不对，WHERE 条件顺序不匹配索引
修复：调整索引顺序，或在 SQL 中重新排列 WHERE 条件
❌ 错误 2：Waiting for table metadata lock
原因：长时间运行的查询持有表锁，新索引添加被阻塞
修复：
1. 查看长查询：SHOW PROCESSLIST;
2. Kill 长查询：KILL QUERY/CONNECTION <thread_id>;
3. 或在从库执行 ALTER TABLE，再主从切换
幂等性保障：
# 使用条件索引，重复执行不会报错
ALTER TABLE orders ADD INDEX IF NOT EXISTS idx_user_status_date (user_id, status, created_at);
回滚要点：
ALTER TABLE orders DROP INDEX idx_user_status_date;
Step 3: SQL 改写与查询优化
目标： 改进 SQL 语句，避免全表扫描与排序
常见问题模式 1：子查询改写
❌ 低效写法（相关子查询，N+1 问题）：
SELECT * FROM users u
WHERE user_id IN (
SELECT DISTINCT user_id FROM orders WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
);
✅ 优化写法（用 JOIN 代替子查询）：
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY);
执行计划对比：
mysql -u root -p << 'EOF'
# 慢查询执行计划（相关子查询）
EXPLAIN FORMAT=JSON SELECT * FROM users u WHERE user_id IN (...)\G
# 快速查询执行计划（JOIN）
EXPLAIN FORMAT=JSON SELECT DISTINCT u.* FROM users u INNER JOIN orders o...\G
EOF
常见问题模式 2：LIMIT 分页优化
❌ 低效写法（大偏移量扫描）：
-- 获取第 10000 页（每页 20 条）
SELECT * FROM users LIMIT 199980, 20; -- 扫描 199980 行后取 20 行，极浪费
✅ 优化写法（使用上次 ID 进行分页）：
-- 假设上次查询的最后一条记录 user_id = 500
SELECT * FROM users WHERE user_id > 500 ORDER BY user_id LIMIT 20;
性能对比：
| 方法 |
扫描行数 |
耗时 |
应用场景 |
| OFFSET 199980, 20 |
199980+ |
50-100ms |
不推荐（深分页） |
| WHERE id > last_id LIMIT 20 |
20 |
1-2ms |
推荐（App 滚动列表） |
**常见问题模式 3：避免 SELECT ***
❌ 低效写法：
SELECT * FROM users; -- 获取 50 个字段，实际只需要 3 个
✅ 优化写法：
SELECT user_id, name, email FROM users; -- 仅查询需要的字段
执行前验证：
# 查看表的字段数
mysql -u root -p -e "SHOW COLUMNS FROM users;" | wc -l
# 预期输出：实际字段数 +1（表头）
执行后验证：
mysql -u root -p << 'EOF'
# 对比两个查询的 rows 和 bytes 返回
EXPLAIN FORMAT=JSON SELECT * FROM users LIMIT 100\G
EXPLAIN FORMAT=JSON SELECT user_id, name, email FROM users LIMIT 100\G
EOF
Step 4: InnoDB 缓冲池调优
目标： 让热数据驻留在内存中，减少磁盘 I/O
诊断缓冲池使用情况：
mysql -u root -p << 'EOF'
# 查看缓冲池大小与使用率
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool%'\G
# 关键指标：
# Innodb_buffer_pool_pages_data：数据页数量
# Innodb_buffer_pool_pages_free：空闲页数量
# 使用率 = (pages_data) / (pages_data + pages_free)，应该 > 95%
EOF
调整缓冲池大小：
RHEL/CentOS 命令：
# 1. 编辑 MySQL 配置文件
vi /etc/my.cnf
# 2. 在 [mysqld] 段添加（假设服务器有 32GB 内存）
[mysqld]
innodb_buffer_pool_size = 24G # 物理内存的 75%
innodb_buffer_pool_instances = 8 # 分 8 个实例，减少竞争
# 3. 重启 MySQL 生效
systemctl restart mysqld
# 4. 验证配置
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool%';"
Ubuntu/Debian 命令：
# 配置文件路径不同
vi /etc/mysql/mysql.conf.d/mysqld.cnf
# 或
vi /etc/mysql/percona-server.conf.d/mysqld.cnf
# 其他步骤相同
关键参数解释：
innodb_buffer_pool_size：至少 80% 物理内存（热数据全量驻留）innodb_buffer_pool_instances：CPU 核心数 / 4，减少全局锁竞争innodb_buffer_pool_dump_at_shutdown：关闭前保存缓冲池数据，启动快速预热
执行后验证：
mysql -u root -p << 'EOF'
# 确认配置生效
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
# 预期输出：24G（或相应值）
# 等待 5 分钟，让缓冲池预热，再查看使用率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%'\G
# 如果 read_requests >> read_ahead_rnd，说明缓存命中率高
EOF
常见错误示例：
❌ 错误 1：InnoDB: Insufficient memory allocated
原因：缓冲池大小超过物理内存
修复：设置为物理内存的 70-80%，不要 100%（需要给 OS 留余量）
❌ 错误 2：重启后缓冲池没有被正确恢复
原因：未启用 innodb_buffer_pool_dump_at_shutdown
修复：SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
Step 5: 读写分离架构（主从复制）
目标： 主库处理写入，从库处理读查询，实现读扩展
系统架构：
应用层
├─ 写操作（INSERT/UPDATE/DELETE）→ 主库 M（192.168.1.10）
└─ 读操作（SELECT）→ 从库 S1（192.168.1.11）或 S2（192.168.1.12）
主库 M ─binlog→ 从库 S1 ─binlog→ 从库 S2
主库配置：
RHEL/CentOS 命令：
# 1. 编辑主库配置
vi /etc/my.cnf
# 2. 添加配置
[mysqld]
server-id = 10 # 唯一标识，主从不能相同
log_bin = mysql-bin # 启用 binlog
binlog_format = ROW # 行级日志（最安全）
sync_binlog = 1 # 每次提交都刷盘（性能损耗 10-20%，但可靠性最高）
innodb_flush_log_at_trx_commit = 1 # 事务立即刷盘
# 3. 重启 MySQL
systemctl restart mysqld
# 4. 查看 binlog 状态
mysql -u root -p -e "SHOW MASTER STATUS\G"
从库配置：
vi /etc/my.cnf
[mysqld]
server-id = 11 # 与主库不同
relay-log = mysql-relay-bin # 启用 relay log
relay-log-index = mysql-relay-bin.index
systemctl restart mysqld
建立主从复制：
# 在主库上创建复制账户
mysql -u root -p << 'EOF'
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;
# 查看当前 binlog 位置（从库需要）
SHOW MASTER STATUS\G
# 记录 File（如 mysql-bin.000003）和 Position（如 154）
EOF
# 在从库上配置复制
mysql -u root -p << 'EOF'
CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=154;
# 启动从库复制
START SLAVE;
# 检查复制状态
SHOW SLAVE STATUS\G
# 关键字段：
# - Slave_IO_Running: Yes（IO 线程运行中）
# - Slave_SQL_Running: Yes（SQL 执行线程运行中）
# - Seconds_Behind_Master: 0（从库延迟，0 表示同步）
EOF
执行后验证：
# 在主库写入数据
mysql -u root -p << 'EOF'
USE test_db;
CREATE TABLE test_repl (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100));
INSERT INTO test_repl(name) VALUES('test-from-master');
EOF
# 在从库查询，验证数据已复制
mysql -u root -p << 'EOF'
USE test_db;
SELECT * FROM test_repl;
# 预期输出：看到主库插入的数据
EOF
常见错误示例：
❌ 错误 1：Slave_IO_Running: No（IO 线程未运行）
原因：主库连接失败，可能网络、密码、主机 IP 错误
修复：
1. 检查网络连通性：ping 主库 IP
2. 验证账户密码：mysql -u repl -p -h 192.168.1.10
3. 检查防火墙：firewall-cmd --add-port=3306/tcp
4. 重新配置：CHANGE MASTER TO ...
❌ 错误 2：Seconds_Behind_Master: NULL（从库复制停止）
原因：从库 SQL 执行出错，通常是数据不一致或 SQL 语句错误
修复：
1. 查看具体错误：SHOW SLAVE STATUS\G（Last_Error 字段）
2. 跳过错误：SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;
3. 或完全重新同步：RESET SLAVE; CHANGE MASTER TO ...
Step 6: 分库分表策略
目标： 解决单表 >100GB 的性能问题，支持水平扩展
分片策略选择：
| 策略 |
分片键示例 |
优点 |
缺点 |
适用场景 |
| 范围分片 |
user_id 0-50w, 50w-100w |
实现简单 |
数据不均衡 |
用户表、订单表 |
| Hash 分片 |
user_id % 4 |
数据分布均匀 |
扩容复杂（需重新 rehash） |
推荐 |
| 一致性 Hash |
user_id -> ring |
扩容时数据转移少 |
实现复杂 |
大规模集群 |
| 业务维度 |
city_id（城市） |
易于管理 |
分片键固定，难以扩展 |
地理区域分布业务 |
Hash 分片实施示例：
架构设计：
应用层
↓（分片键：user_id）
分片路由层（Mycat/Sharding-JDBC）
├─ 分片库 db0（user_id % 4 = 0）→ 实例 1
├─ 分片库 db1（user_id % 4 = 1）→ 实例 2
├─ 分片库 db2（user_id % 4 = 2）→ 实例 3
└─ 分片库 db3（user_id % 4 = 3）→ 实例 4
数据库创建脚本：
# 在每个 MySQL 实例上执行
mysql -u root -p << 'EOF'
# 创建 4 个分片库
CREATE DATABASE db0 CHARACTER SET utf8mb4;
CREATE DATABASE db1 CHARACTER SET utf8mb4;
CREATE DATABASE db2 CHARACTER SET utf8mb4;
CREATE DATABASE db3 CHARACTER SET utf8mb4;
# 在每个库中创建分片表（以 db0 为例）
USE db0;
CREATE TABLE users_0 (
user_id BIGINT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email(email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 类似创建 users_1, users_2, users_3 表
EOF
应用层分片路由（伪代码）：
# Python 示例
import mysql.connector
def get_shard_db(user_id, num_shards=4):
shard_idx = user_id % num_shards
return f"db{shard_idx}"
def insert_user(user_id, name, email):
shard_db = get_shard_db(user_id)
conn = mysql.connector.connect(
host="192.168.1.10", # 分片库 IP
user="root",
password="password",
database=shard_db
)
cursor = conn.cursor()
cursor.execute(
f"INSERT INTO users_{user_id % 4} (user_id, name, email) VALUES (%s, %s, %s)",
(user_id, name, email)
)
conn.commit()
cursor.close()
conn.close()
# 使用
insert_user(user_id=100123, name="张三", email="zhangsan@example.com")
# 自动路由到：db0（100123 % 4 = 3）
跨分片查询处理：
# 场景：查询所有用户统计（跨所有分片）
# 需要在应用层或中间件合并结果
mysql -u root -p << 'EOF'
-- 分别查询各分片库
SELECT COUNT(*) as total FROM db0.users_0; -- 结果 250w
SELECT COUNT(*) as total FROM db1.users_1; -- 结果 250w
SELECT COUNT(*) as total FROM db2.users_2; -- 结果 250w
SELECT COUNT(*) as total FROM db3.users_3; -- 结果 250w
-- 应用层合计：1000w
-- 应用层伪代码：
def count_all_users():
total = 0
for shard_idx in range(4):
result = query(f"SELECT COUNT(*) FROM db{shard_idx}.users_{shard_idx}")
total += result[0]
return total
EOF
最小必要原理
MySQL 查询优化核心机制：
MySQL 查询优化器基于成本估算决定是否使用索引。成本 = 访问行数 + 磁盘 I/O 次数 + CPU 运算成本。
为什么要用索引？
- 全表扫描需要读取 100w 行数据（假设表有 100w 行），成本 = 100w × 8KB 磁盘 I/O ≈ 800MB 网络传输
- B+ 树索引：仅需 log₁₆(100w) ≈ 5 次 I/O，成本 ≈ 40KB，性能提升 20000 倍
为什么要用缓冲池？
- 磁盘 I/O：1ms（随机），内存访问：0.001ms（随机）
- 热数据驻留内存 → 0 磁盘 I/O，查询速度从 1ms 降低到 0.1ms
为什么要做读写分离？
- 单个 MySQL 连接 QPS（Query Per Second）上限 ≈ 10000
- 主库只处理写（占总流量 10%），从库池处理读（占 90%）
- 总容量 = 1w × (1 主 + N 从) ≈ 1w × 10 = 10w QPS，扩展线性
为什么要分库分表？
- 单表索引：B+ 树高度限制在 4 层（16KB 页 × 4 = 64KB 树高），单表容量 ≈ 100GB
- 分片后：4 个库各 25GB，查询成本 = 原来的 1/4，扩展无上限
可观测性（监控 + 告警 + 性能）
监控指标
Linux 原生监控：
# 1. 查看 MySQL 进程与资源占用
ps aux | grep mysqld | grep -v grep
# 2. 实时监控 MySQL 性能
mysql -u root -p << 'EOF'
-- 每 5 秒刷新一次
SHOW PROCESSLIST; -- 查看运行中的查询
-- 查看关键性能指标
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 计算 QPS（每秒查询数）
-- 两次执行间隔 10 秒，Questions 增量 / 10 = QPS
EOF
# 3. 查看 MySQL 日志
tail -100 /var/log/mysql/error.log # 错误日志
tail -100 /var/log/mysql/slow.log # 慢查询日志
Prometheus 告警规则示例：
groups:
- name: mysql_alerts
interval: 10s
rules:
# 告警 1：慢查询增多
- alert: MySQLSlowQueryHigh
expr: rate(mysql_global_status_slow_queries[5m]) > 1
for: 5m
annotations:
summary: "MySQL 慢查询速率过高（实例：{{ $labels.instance }}）"
description: "过去 5 分钟平均每秒 {{ $value }} 条慢查询"
# 告警 2：缓冲池使用率
- alert: MySQLBufferPoolLow
expr: |
(mysql_innodb_buffer_pool_pages_data /
(mysql_innodb_buffer_pool_pages_data + mysql_innodb_buffer_pool_pages_free)) < 0.5
for: 10m
annotations:
summary: "MySQL InnoDB 缓冲池使用率 < 50%"
# 告警 3：主从复制延迟
- alert: MySQLReplicationLag
expr: mysql_slave_status_seconds_behind_master > 10
for: 5m
annotations:
summary: "MySQL 从库复制延迟超过 10 秒"
# 告警 4：连接数接近上限
- alert: MySQLConnectionsHigh
expr: |
(mysql_global_status_threads_connected /
mysql_global_variables_max_connections) > 0.8
for: 5m
annotations:
summary: "MySQL 连接数接近上限（{{ $value | humanizePercentage }}）"
性能基准测试：
# 安装 sysbench（MySQL 压力测试工具）
yum install -y sysbench
# 1. 准备测试数据（创建 1000w 行测试表）
sysbench /usr/share/sysbench/oltp_prepare.lua \
--mysql-host=192.168.1.10 \
--mysql-user=root \
--mysql-password=password \
--mysql-db=test \
--table-size=10000000 \
prepare
# 2. 执行读写混合测试（16 并发，持续 60 秒）
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=192.168.1.10 \
--mysql-user=root \
--mysql-password=password \
--mysql-db=test \
--table-size=10000000 \
--threads=16 \
--time=60 \
run
# 预期输出（参考值）：
# transactions: 60000 (1000.00 per sec)
# read/write requests: 1200000 (20000.00 per sec)
# Latency (ms):
# min: 0.48
# avg: 16.00
# max: 200.00
常见故障与排错
| 症状 |
诊断命令 |
可能根因 |
快速修复 |
永久修复 |
| 查询突然变慢（QPS 从 10k 跌到 1k） |
SHOW PROCESSLIST; + SHOW ENGINE INNODB STATUS\G |
1. 全表扫描
2. 锁等待
3. 磁盘满 |
1. Kill 长查询：KILL QUERY <id>
2. 释放磁盘空间 |
添加索引，优化查询 |
ERROR 1114 (HY000): Table is full |
SHOW VARIABLES LIKE 'max_allowed_packet'; |
表所在分区磁盘满 |
清理数据或扩容磁盘 |
部署分库分表 |
| 主从复制延迟（Seconds_Behind_Master > 60s） |
SHOW SLAVE STATUS\G |
1. 从库资源不足
2. 大事务执行慢 |
优化慢查询，增加从库资源 |
部署 Percona XtraDB Cluster |
| 缓冲池命中率低（< 95%） |
SHOW STATUS LIKE 'Innodb_buffer_pool%'; |
缓冲池配置过小 |
增大 innodb_buffer_pool_size |
根据 working set 调整 |
调试思路（系统性排查）：
第1步：问题现象
↓ 检查 QPS/响应时间是否异常？
第2步：查看 PROCESSLIST
├─ 是否有 LOCK WAIT 或长时间运行的查询？
│ ├─ 是 → Kill 长查询，查看索引
│ └─ 否 → 下一步
│
第3步：检查磁盘与 I/O
├─ 磁盘是否满？（df -h）
├─ I/O 是否饱和？（iostat -x 1）
│ ├─ 是 → 优化查询或升级硬件
│ └─ 否 → 下一步
│
第4步：检查慢查询日志
├─ 是否存在全表扫描查询？
│ ├─ 是 → 添加索引
│ └─ 否 → 检查索引是否失效
│
第5步：检查主从复制
├─ 从库是否延迟？
│ ├─ 是 → 优化从库慢查询或增加资源
│ └─ 否 → 检查应用连接池配置
变更与回滚剧本
灰度策略：
# 1. 在开发环境验证索引效果（5 分钟）
# 2. 在预发布环境全量验证（30 分钟）
# 3. 在生产从库执行 ALTER TABLE（可能需要 1-24 小时，取决于表大小）
# 4. 主从切换：从库升为主库
# 5. 在新从库（原主库）执行相同操作
# 以添加索引为例：
# 在从库 S1 上执行（不影响主库服务）
mysql -u root -p << 'EOF'
-- 停止从库复制
STOP SLAVE;
-- 执行 ALTER TABLE（在从库上可能需要数分钟到数小时）
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);
-- 等待 ALTER 完成，验证索引效果
EXPLAIN SELECT * FROM orders WHERE user_id=100 AND status='paid' ORDER BY created_at DESC;
-- 启动从库复制
START SLAVE;
-- 验证从库恢复同步
SHOW SLAVE STATUS\G;
EOF
# 等待从库 Seconds_Behind_Master = 0，确保主从完全同步
# 执行主从切换（应用写入切到 S1）
# Keepalived 自动切换 VIP，或应用手动切换连接字符串
# 在新从库（原主库）上执行相同操作
# 完成后，系统恢复全量服务
回滚条件与命令：
# 回滚触发条件：
# 1. 索引添加后，新索引导致查询变慢（可能优化器选错索引）
# 2. 执行 ALTER TABLE 中断，主从不同步
# 3. 索引占用过多磁盘空间（>表大小的 50%）
# 回滚步骤：
mysql -u root -p << 'EOF'
-- 删除不适合的索引
ALTER TABLE orders DROP INDEX idx_user_status_date;
-- 验证查询是否回到正常
EXPLAIN SELECT * FROM orders WHERE user_id=100;
EOF
-- 如果涉及主从切换，需要手动切回：
-- 应用连接字符串改回原主库
-- 在原从库（现主库）执行 CHANGE MASTER TO，指向新主库
最佳实践（30 条 DBA 压箱底技巧）
第一部分：索引设计 (10 条)
- 遵循索引顺序三原则
-- 1. 等值条件 → 2. 范围条件 → 3. 排序字段
ALTER TABLE orders ADD INDEX idx_opt (user_id, status, created_at);
-- user_id = ? AND status IN (...) ORDER BY created_at
-
使用覆盖索引，避免回表
-- 不走索引示例（SELECT 包含非索引字段）
SELECT user_id, name, amount FROM orders WHERE user_id = 100;
-- 改为覆盖索引
ALTER TABLE orders ADD INDEX idx_cover (user_id, amount, name);
- 前缀索引节省空间
-- 字符串字段超过 20 字符，使用前缀索引
ALTER TABLE users ADD INDEX idx_email (email(10)); -- 仅索引前 10 个字符
-
避免在 WHERE 条件中使用函数
-- 错误：函数导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 正确：直接范围比较
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
- 定期清理冗余索引
# 每月检查一次重复索引
yum install -y percona-toolkit
pt-duplicate-key-checker -h localhost -u root -p'password'
-
单列索引 vs 联合索引
-- 避免创建过多单列索引，优先使用联合索引
-- 错误：3 个单列索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_amount ON orders(amount);
-- 正确：1 个联合索引（3 个字段都能用上）
CREATE INDEX idx_combo ON orders(user_id, status, amount);
- 避免索引列为 NULL
-- NOT NULL 默认，避免 NULL 值导致索引失效
ALTER TABLE users MODIFY COLUMN status VARCHAR(50) NOT NULL DEFAULT 'active';
- 使用 EXPLAIN 验证每个关键查询
# 上线前必须检查 type、rows、filtered
mysql -u root -p << 'EOF'
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100;
EOF
-
避免在索引列进行类型转换
-- 错误：user_id 是 BIGINT，但传入字符串
SELECT * FROM users WHERE user_id = '100'; -- MySQL 自动转换，索引失效
-- 正确：类型匹配
SELECT * FROM users WHERE user_id = 100;
- 定期重建索引，整理碎片
# 对于频繁更新的大表，每季度重建一次
mysql -u root -p << 'EOF'
-- MySQL 5.7+：原地重建，不阻塞查询
ALTER TABLE orders ENGINE=InnoDB;
EOF
第二部分：查询优化 (10 条)
- *避免 SELECT ，仅查询必需字段**
-- 减少网络传输，降低缓冲池压力
SELECT user_id, name, email FROM users;
-
用 JOIN 代替子查询
-- 错误：相关子查询（N+1 问题）
SELECT * FROM users WHERE user_id IN (SELECT DISTINCT user_id FROM orders);
-- 正确：JOIN
SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON u.user_id = o.user_id;
-
使用分页“上次 ID”法，避免深分页
-- 错误：大偏移量扫描所有行
SELECT * FROM users LIMIT 100000, 20;
-- 正确：记录上次 ID，快速定位
SELECT * FROM users WHERE user_id > :last_id ORDER BY user_id LIMIT 20;
-
批量操作改为分批处理
# 错误：一次插入 100w 条，造成内存溢出、主从延迟
INSERT INTO users (...) VALUES (...), (...), ...; # 100w 条
# 正确：分批 1000 条一次
for batch in batches:
INSERT INTO users (...) VALUES (...), ...; # 1000 条
-
避免在 WHERE 中使用 OR
-- 错误：多个 OR 导致全表扫描
SELECT * FROM users WHERE id = 1 OR id = 2 OR id = 3;
-- 正确：用 IN
SELECT * FROM users WHERE id IN (1, 2, 3);
-
使用 LIMIT 限制返回行数
-- 错误：返回 100w 行，网络传输 100MB
SELECT * FROM users;
-- 正确：前端分页，一次仅返回 20 行
SELECT * FROM users LIMIT 0, 20;
-
避免在索引列使用 LIKE ‘%prefix’
-- 错误：前缀通配符，无法使用索引
SELECT * FROM users WHERE name LIKE '%zhang%';
-- 正确：使用后缀通配符，或用全文搜索
SELECT * FROM users WHERE name LIKE 'zhang%';
-- 或：SELECT * FROM users WHERE MATCH(name) AGAINST('zhang');
-
避免 UNION，改用 UNION ALL
-- 错误：UNION 去重需要排序，性能差
SELECT user_id FROM users UNION SELECT user_id FROM orders;
-- 正确：确认无重复数据，用 UNION ALL
SELECT user_id FROM users UNION ALL SELECT user_id FROM orders;
- 定期分析表统计信息，帮助优化器
# MySQL 8.0+ 自动更新，5.7 需要手动
mysql -u root -p << 'EOF'
ANALYZE TABLE users;
ANALYZE TABLE orders;
EOF
-
避免在 GROUP BY 后使用 HAVING 过滤
-- 低效：GROUP BY 全表 → HAVING 再过滤
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 100;
-- 优化：先用 WHERE 过滤，再 GROUP BY
SELECT user_id, COUNT(*) FROM orders
WHERE created_at > '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) > 100;
第三部分：架构设计 (10 条)
- 实施主从复制，分离读写
# 主库：处理写（INSERT/UPDATE/DELETE）
# 从库：处理读（SELECT），可有多个
# 读写比例通常 1:9（1 主 9 从）
-
部署 Redis 缓存热数据，减少数据库访问
# Cache-Aside 模式
def get_user(user_id):
# 先查缓存
user = redis.get(f"user:{user_id}")
if user:
return user
# 缓存未命中，查数据库
user = mysql.query(f"SELECT * FROM users WHERE user_id={user_id}")
# 写入缓存，过期时间 1 小时
redis.setex(f"user:{user_id}", 3600, user)
return user
- 业务高峰期使用查询结果缓存
-- MySQL 5.7 支持查询缓存（8.0 已删除）
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 1GB;
- 分库分表支持无限扩展
单库容量上限：100GB（B+ 树高度限制）
分库分表：4 库 × 4 表 = 16 个分片，容量 = 100GB × 16 = 1.6TB
水平扩展：增加分片数（如改为 8 库 × 8 表 = 64 分片），容量再提升
- 监控关键指标，提前预警
# 每日定时检查
- InnoDB 缓冲池使用率 > 95%
- 慢查询 QPS > 10 条/分钟
- 主从复制延迟 > 1 秒
- 磁盘使用率 > 85%
-
定期备份，制定恢复计划
# 每日全量备份，每小时增量备份
mysqldump -u root -p --single-transaction --master-data=2 \
--all-databases > backup_$(date +%Y%m%d_%H%M%S).sql
# 模拟恢复（每周一次），确保备份可用
mysql -u root -p < backup_20240115_120000.sql
- 使用 utf8mb4 避免字符集问题
-- 默认 utf8（MySQL 中仅支持 3 字节），改用 utf8mb4（4 字节）
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
name VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
避免大事务，拆分为小事务
-- 错误：一个事务更新 1w 条记录，主从延迟严重
BEGIN;
UPDATE users SET status='active' WHERE created_at < '2024-01-01'; -- 100w 行
COMMIT;
-- 正确：分批更新
FOR i IN 0..99:
UPDATE users SET status='active'
WHERE created_at < '2024-01-01'
LIMIT i*10000, 10000;
-
开启 slow_query_log，定期分析
# 设置 1 秒阈值，每周分析一次
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
# 每周五定时任务
0 2 * * 5 pt-query-digest /var/log/mysql/slow.log | mail admin@example.com
- 建立变更审批流程，避免线上故障
DBA 变更检查清单：
☐ EXPLAIN 确认查询是否走索引
☐ 在从库灰度验证（1 小时）
☐ 在预发布环境全量验证（30 分钟）
☐ 在主库执行前备份（mysqldump）
☐ 准备回滚脚本
☐ 设置时间窗口（业务低谷）
☐ 监控变更后的性能指标（30 分钟）
FAQ（常见问题）
Q1: 添加索引会让 INSERT/UPDATE 变慢吗？
A: 是的，但可控。每增加一个索引，INSERT/UPDATE 会多一次磁盘 I/O（维护索引 B+ 树）。性能损耗通常 10-20%，但查询性能提升 10-100 倍，ROI 很高。建议：
- 写频繁的表（每秒 > 10k INSERT）：索引数 ≤ 5 个
- 读频繁的表：索引数可适当增加（≤ 10 个）
- 定期删除未使用的索引
Q2: 为什么执行 ALTER TABLE 会阻塞写入？
A: MySQL 5.6 之前的默认行为。5.7+ 支持 Online DDL，大部分 ALTER 操作不阻塞，但仍有例外（如改字段类型）。推荐方案：
- 在从库执行 ALTER（从库可短暂下线）
- 完成后主从切换
- 原主库变成新从库，继续 ALTER
Q3: 分库分表后，如何处理跨库 JOIN？
A: 分库分表的最大痛点。解决方案：
- 应用层 JOIN：将两个库的数据都查出来，在应用层合并（内存占用高）
- 冗余字段：在订单表冗余用户名，避免 JOIN（数据一致性维护复杂）
- 搜索引擎：关联数据写入 Elasticsearch，在 ES 中 JOIN（额外维护成本）
Q4: MySQL 性能优化的“天花板”是多少？
A: 取决于硬件与架构：
- 单机单库：约 10w QPS（写 1k + 读 99k）
- 主从复制（1 主 10 从）：约 100w QPS
- 分库分表（16 分片）：约 1600w QPS
- 加缓存层（Redis）：约 1000w+ QPS（缓存命中率 > 95%）
Q5: 如何判断是否需要分库分表？
A: 关键指标：
| 指标 |
阈值 |
行动 |
| 单表行数 |
> 1000w |
需要分表 |
| 单表大小 |
> 50GB |
需要分表 |
| 写 QPS |
> 5k |
需要分库 |
| 读 QPS |
> 10k |
部署从库 |
| 磁盘 I/O |
> 80% 利用率 |
优化查询或扩容 |
Q6: InnoDB 缓冲池应该设置多大？
A: 简单规则：物理内存的 70-80%
示例：32GB 服务器
- 系统 OS：2GB
- MySQL 其他开销（连接、排序、临时表）：2GB
- InnoDB 缓冲池：28GB
Q7: 如何确保主从复制的数据一致性？
A: 关键配置：
-- 主库
sync_binlog = 1 # 每次提交都刷盘
innodb_flush_log_at_trx_commit = 1 # 事务立即刷盘
-- 从库
slave-parallel-workers = 4 # 并行复制，加速应用 binlog
Q8: 查询缓存（Query Cache）还值得用吗？
A: 不值得，原因：
- 只要表有任何更新，该表的所有缓存立即失效（命中率通常 < 30%）
- MySQL 8.0 已删除查询缓存功能
- 推荐用 Redis 代替（自己控制过期策略，命中率 > 95%）
Q9: 什么时候用 MEMORY 存储引擎？
A: 尽量避免，原因：
- 数据存在内存，服务器重启数据丢失
- 不支持 binlog，无法主从复制
- 只适合临时结果表（如临时统计结果）
Q10: 慢查询日志对生产环保影响大吗？
A: 影响很小（< 3%）。启用慢查询日志的性能成本很低，收益很高（快速定位性能问题）。建议生产环境始终启用。
附录：关键脚本
脚本 1：一键诊断脚本（MySQL 性能快速检查）
#!/bin/bash
# 文件名：mysql_health_check.sh
# 用途：一次性检查 MySQL 性能、配置、主从复制等关键指标
# 执行：bash mysql_health_check.sh
set -e
# 配置变量（需要修改）
MYSQL_HOST="192.168.1.10"
MYSQL_USER="root"
MYSQL_PASSWORD="password"
MYSQL_PORT="3306"
# 颜色输出
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color
echo -e "${YELLOW}=== MySQL 性能诊断报告 ===${NC}"
echo "执行时间：$(date '+%Y-%m-%d %H:%M:%S')"
echo ""
# 1. 检查 MySQL 是否运行
echo -e "${GREEN}[1/10] 检查 MySQL 进程状态${NC}"
if mysqladmin -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD ping 2>/dev/null | grep -q "mysqld is alive"; then
echo -e "${GREEN}✓ MySQL 服务运行正常${NC}"
else
echo -e "${RED}✗ MySQL 服务未运行${NC}"
exit 1
fi
# 2. 获取版本信息
echo -e "\n${GREEN}[2/10] MySQL 版本${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT VERSION() as 'MySQL Version';"
# 3. 检查缓冲池使用率
echo -e "\n${GREEN}[3/10] InnoDB 缓冲池使用率${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
ROUND((pages_data * 100) / (pages_data + pages_free), 2) as 'Buffer Pool Usage %'
FROM (
SELECT
(VARIABLE_VALUE * 1024 * 1024 / 16384) as pages_total,
VARIABLE_VALUE as size_mb
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME = 'innodb_buffer_pool_size'
) AS pool_size,
(
SELECT
VARIABLE_VALUE as pages_data
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'
) AS data_pages,
(
SELECT
VARIABLE_VALUE as pages_free
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_free'
) AS free_pages;
"
# 4. 检查慢查询设置
echo -e "\n${GREEN}[4/10] 慢查询设置${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
@@slow_query_log as 'Slow Query Log',
@@long_query_time as 'Query Time Threshold (s)';"
# 5. 检查 QPS
echo -e "\n${GREEN}[5/10] 当前 QPS（近似值）${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SHOW STATUS WHERE variable_name IN ('Questions', 'Innodb_rows_read', 'Innodb_rows_inserted');"
# 6. 检查连接数
echo -e "\n${GREEN}[6/10] 连接数状态${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
@@max_connections as 'Max Connections',
(SELECT COUNT(*) FROM information_schema.PROCESSLIST) as 'Current Connections',
ROUND(((SELECT COUNT(*) FROM information_schema.PROCESSLIST) * 100) / @@max_connections, 2) as 'Usage %';"
# 7. 检查主从复制
echo -e "\n${GREEN}[7/10] 主从复制状态${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G" 2>/dev/null || echo "此实例为独立库（无从库配置）"
# 8. 检查表碎片
echo -e "\n${GREEN}[8/10] 数据库表统计（Top 5 大表）${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
TABLE_SCHEMA,
TABLE_NAME,
ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024), 2) as 'Size(GB)',
TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC
LIMIT 5;"
# 9. 检查索引统计
echo -e "\n${GREEN}[9/10] 索引统计${NC}"
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
SELECT
TABLE_SCHEMA,
TABLE_NAME,
COUNT(*) as 'Index Count'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
GROUP BY TABLE_SCHEMA, TABLE_NAME
ORDER BY 'Index Count' DESC
LIMIT 10;"
# 10. 安全建议
echo -e "\n${GREEN}[10/10] 安全建议${NC}"
echo "1. ✓ 定期备份数据库（建议每日全量 + 每小时增量）"
echo "2. ✓ 启用 binlog，配置主从复制（高可用）"
echo "3. ✓ 定期分析慢查询日志，优化关键 SQL"
echo "4. ✓ 监控关键指标（缓冲池、连接数、磁盘）"
echo "5. ✓ 定期清理冗余索引，减少写入成本"
echo -e "\n${YELLOW}=== 诊断完成 ===${NC}"
脚本 2：自动建立主从复制脚本
#!/bin/bash
# 文件名：setup_replication.sh
# 用途：自动配置 MySQL 主从复制（仅适用于全新实例）
# 执行：bash setup_replication.sh master|slave
set -e
MASTER_IP="192.168.1.10"
SLAVE_IP="192.168.1.11"
REPL_USER="repl"
REPL_PASSWORD="repl_password"
MASTER_PORT="3306"
SLAVE_PORT="3306"
if [ "$1" = "master" ]; then
echo "配置主库..."
# 修改 /etc/my.cnf
sed -i '/\[mysqld\]/a\
server-id = 10\
log_bin = mysql-bin\
binlog_format = ROW\
sync_binlog = 1' /etc/my.cnf
# 重启 MySQL
systemctl restart mysqld
# 创建复制账户
mysql -u root -p$MYSQL_PASSWORD << EOF
CREATE USER '$REPL_USER'@'$SLAVE_IP' IDENTIFIED BY '$REPL_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO '$REPL_USER'@'$SLAVE_IP';
FLUSH PRIVILEGES;
EOF
echo "✓ 主库配置完成"
elif [ "$1" = "slave" ]; then
echo "配置从库..."
# 修改 /etc/my.cnf
sed -i '/\[mysqld\]/a\
server-id = 11\
relay-log = mysql-relay-bin' /etc/my.cnf
# 重启 MySQL
systemctl restart mysqld
# 获取主库 binlog 位置
MASTER_STATUS=$(mysql -h $MASTER_IP -u root -p$MYSQL_PASSWORD -e "SHOW MASTER STATUS\G")
LOG_FILE=$(echo "$MASTER_STATUS" | grep "File:" | awk '{print $NF}')
LOG_POS=$(echo "$MASTER_STATUS" | grep "Position:" | awk '{print $NF}')
# 配置从库复制
mysql -u root -p$MYSQL_PASSWORD << EOF
CHANGE MASTER TO
MASTER_HOST='$MASTER_IP',
MASTER_USER='$REPL_USER',
MASTER_PASSWORD='$REPL_PASSWORD',
MASTER_LOG_FILE='$LOG_FILE',
MASTER_LOG_POS=$LOG_POS;
START SLAVE;
SHOW SLAVE STATUS\G
EOF
echo "✓ 从库配置完成"
else
echo "用法：bash setup_replication.sh [master|slave]"
exit 1
fi
脚本 3：慢查询分析与优化建议脚本
#!/bin/bash
# 文件名：analyze_slow_queries.sh
# 用途：分析慢查询日志，给出优化建议
SLOW_LOG="/var/log/mysql/slow.log"
if [ ! -f "$SLOW_LOG" ]; then
echo "慢查询日志不存在：$SLOW_LOG"
exit 1
fi
echo "=== MySQL 慢查询分析报告 ==="
echo "日志路径：$SLOW_LOG"
echo "分析时间：$(date '+%Y-%m-%d %H:%M:%S')"
echo ""
# 使用 pt-query-digest 分析
pt-query-digest "$SLOW_LOG" --limit=10 | head -100
echo ""
echo "=== 优化建议 ==="
echo "1. 检查 Top 10 慢查询的执行计划（EXPLAIN）"
echo "2. 为缺失索引的查询添加索引"
echo "3. 考虑使用 JOIN 代替子查询"
echo "4. 避免 SELECT *，仅查询必需字段"
echo "5. 检查是否有全表扫描（type=ALL）"
扩展阅读
官方文档：
- MySQL 官方文档
- MySQL 性能调优指南
- InnoDB 存储引擎官方手册
第三方工具：
- Percona Toolkit - DBA 必备工具集
- Percona XtraBackup - 物理备份工具
- sysbench - 性能基准测试工具
深入学习：
- 《高性能 MySQL》（第 3 版）- DBA 必读经典
- Let’s Encrypt MySQL - MySQL 源码学习
- Percona 博客 - 运维技术干货
希望通过这篇系统性的指南，能帮助你更深入地理解和实践 MySQL 性能优化。从诊断、索引、SQL、配置到架构，每个环节都环环相扣。真正的优化是一场持续的战斗，需要结合监控数据不断迭代。如果你想与更多同行交流这些实战技巧，欢迎来 云栈社区 的数据库板块一起探讨。
发表于 4 天前
|
查看: 32|
回复: 0
