MySQL性能优化圣经：索引、慢查询、分库分表 - 详解

1️⃣ 适用场景 & 前置条件

项目	要求
适用场景	日均 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 基础语法、索引原理、事务隔离级别

2️⃣ 反模式警告

⚠️ 以下场景不推荐使用本方案：

1. 低并发个人博客：日均 PV < 1000，引入优化反而增加维护成本，直接用默认配置足够
2. 内存表 MEMORY 存储引擎：数据丢失风险高，性能优化效果反而不明显
3. Windows 环境生产：MySQL 在 Windows 下性能、可靠性不如 Linux，不推荐生产使用
4. 已采用 NoSQL 方案：MongoDB/Redis 主存储的架构，不适合传统 SQL 优化策略
5. 缺乏监控告警基础：未部署 Prometheus/Zabbix 的环境，性能瓶颈定位困难

替代方案对比：

场景	推荐方案	理由
超大表（>1TB）	Elasticsearch + MySQL	ES 提供更好的分析查询能力
实时大数据分析	ClickHouse/Druid	列式存储更适合 OLAP
流式日志存储	ELK Stack（Elasticsearch）	专为时序数据优化

3️⃣ 环境与版本矩阵

组件	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+ 树高度

4️⃣ 阅读导航

建议阅读路径：

快速上手（20分钟）：→ 章节 6（快速清单）→ 章节 7（实施步骤 Step 1-6） → 章节 13（附录：关键脚本）

深入理解（60分钟）：→ 章节 8（最小必要原理）→ 章节 7（实施步骤完整版）→ 章节 11（最佳实践 30 条）→ 章节 12（FAQ）

故障排查（应急）：→ 章节 9（常见故障与排错）→ 章节 8（调试思路）

5️⃣ 快速清单

• [ ] 诊断阶段
- • [ ] 识别慢查询：启用慢查询日志，查询 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 按业务维度水平分片
- • [ ] 数据归档：历史数据分区+冷存储，释放热数据空间

6️⃣ 实施步骤（核心内容）

系统架构与数据流说明

应用层 → 主 MySQL（可写）
         ↓
         binlog ────→ 从 MySQL 1（只读）
         │            ↓
         │        从 MySQL 2（只读）
         │
         └────→ 读写分离中间件（Mycat/Sharding-JDBC）
                 ↓
                 分片库 1（用户 ID 0-50w）
                 分片库 2（用户 ID 50w-100w）
                 分片库 3（用户 ID 100w-150w）

关键组件与数据流向：

1. 应用层：发送 SQL 到主库（写操作）或从库（读操作）
2. 主库（Master）：处理所有写入（INSERT/UPDATE/DELETE），实时生成 binlog
3. binlog 传输：异步/半同步复制到从库，保证最终一致性
4. 从库集群：多个从库共享负载，处理 SELECT 查询，实现读扩展
5. 分片路由：根据分片键（如用户 ID）计算目标库，支持水平扩展
6. 缓存层（可选）：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

关键参数解释：

1. long_query_time=1：超过 1 秒的查询记录为慢查询（生产环境建议 0.5-1s）
2. log_queries_not_using_indexes：记录全表扫描，便于识别坏查询
3. 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

关键参数解释：

1. 索引顺序：WHERE 等值条件 → WHERE 范围条件 → ORDER BY → 覆盖字段
2. 覆盖索引：将 SELECT 需要的字段也放入索引，避免回表查询（性能提升 10-50 倍）
3. 前缀索引：字符串字段超过 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 ;
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 (
SELECTDISTINCT user_id FROM orders WHERE created_at > DATE_SUB(NOW(), INTERVAL7DAY)
);

✅ 优化写法（用 JOIN 代替子查询）：

SELECTDISTINCT u.*FROM users u
INNERJOIN orders o ON u.user_id = o.user_id
WHERE o.created_at > DATE_SUB(NOW(), INTERVAL7DAY);

执行计划对比：

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 >500ORDERBY 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
# 其他步骤相同

关键参数解释：

1. innodb_buffer_pool_size：至少 80% 物理内存（热数据全量驻留）
2. innodb_buffer_pool_instances：CPU 核心数 / 4，减少全局锁竞争
3. 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
defget_shard_db(user_id, num_shards=4):
    shard_idx = user_id % num_shards
returnf"db{shard_idx}"
definsert_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

7️⃣ 最小必要原理

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，扩展无上限

8️⃣ 可观测性（监控 + 告警 + 性能）

监控指标

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

9️⃣ 常见故障与排错

症状	诊断命令	可能根因	快速修复	永久修复
查询突然变慢（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，指向新主库

1️⃣1️⃣ 最佳实践（30 条 DBA 压箱底技巧）

第一部分：索引设计 (10 条)

1. 遵循索引顺序三原则

-- 1. 等值条件 → 2. 范围条件 → 3. 排序字段
ALTER TABLE orders ADD INDEX idx_opt (user_id, status, created_at);
-- user_id = ? AND status IN (...) ORDER BY created_at

2. 使用覆盖索引，避免回表

-- 不走索引示例（SELECT 包含非索引字段）
SELECT user_id, name, amount FROM orders WHERE user_id =100;
-- 改为覆盖索引
ALTER TABLE orders ADD INDEX idx_cover (user_id, amount, name);

3. 前缀索引节省空间

-- 字符串字段超过 20 字符，使用前缀索引
ALTER TABLE users ADD INDEX idx_email (email(10));  -- 仅索引前 10 个字符

4. 避免在 WHERE 条件中使用函数

-- 错误：函数导致索引失效
SELECT*FROM users WHEREYEAR(created_at) =2024;
-- 正确：直接范围比较
SELECT*FROM users WHERE created_at >='2024-01-01'AND created_at <'2025-01-01';

5. 定期清理冗余索引

# 每月检查一次重复索引
yum install -y percona-toolkit
pt-duplicate-key-checker -h localhost -u root -p'password'

6. 单列索引 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);

7. 避免索引列为 NULL

-- NOT NULL 默认，避免 NULL 值导致索引失效
ALTER TABLE users MODIFY COLUMN status VARCHAR(50) NOT NULLDEFAULT'active';

8. 使用 EXPLAIN 验证每个关键查询

# 上线前必须检查 type、rows、filtered
mysql -u root -p << 'EOF'
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 100;
EOF

9. 避免在索引列进行类型转换

-- 错误：user_id 是 BIGINT，但传入字符串
SELECT*FROM users WHERE user_id ='100';  -- MySQL 自动转换，索引失效
-- 正确：类型匹配
SELECT*FROM users WHERE user_id =100;

10. 定期重建索引，整理碎片

# 对于频繁更新的大表，每季度重建一次
mysql -u root -p << 'EOF'
-- MySQL 5.7+：原地重建，不阻塞查询
ALTER TABLE orders ENGINE=InnoDB;
EOF

第二部分：查询优化 (10 条)

11. *避免 SELECT ，仅查询必需字段

-- 减少网络传输，降低缓冲池压力
SELECT user_id, name, email FROM users;

12. 用 JOIN 代替子查询

-- 错误：相关子查询（N+1 问题）
SELECT*FROM users WHERE user_id IN (SELECTDISTINCT user_id FROM orders);
-- 正确：JOIN
SELECTDISTINCT u.*FROM users u INNERJOIN orders o ON u.user_id = o.user_id;

13. 使用分页"上次 ID"法，避免深分页

-- 错误：大偏移量扫描所有行
SELECT*FROM users LIMIT 100000, 20;
-- 正确：记录上次 ID，快速定位
SELECT*FROM users WHERE user_id > :last_id ORDERBY user_id LIMIT 20;

14. 批量操作改为分批处理

# 错误：一次插入 100w 条，造成内存溢出、主从延迟
INSERT INTO users (...) VALUES (...), (...), ...;  -- 100w 条
# 正确：分批 1000 条一次
for batch in batches:
    INSERT INTO users (...) VALUES (...), ...;  -- 1000 条

15. 避免在 WHERE 中使用 OR

-- 错误：多个 OR 导致全表扫描
SELECT*FROM users WHERE id =1OR id =2OR id =3;
-- 正确：用 IN
SELECT*FROM users WHERE id IN (1, 2, 3);

16. 使用 LIMIT 限制返回行数

-- 错误：返回 100w 行，网络传输 100MB
SELECT*FROM users;
-- 正确：前端分页，一次仅返回 20 行
SELECT*FROM users LIMIT 0, 20;

17. 避免在索引列使用 LIKE ‘%prefix’

-- 错误：前缀通配符，无法使用索引
SELECT*FROM users WHERE name LIKE'%zhang%';
-- 正确：使用后缀通配符，或用全文搜索
SELECT*FROM users WHERE name LIKE'zhang%';
-- 或：SELECT * FROM users WHERE MATCH(name) AGAINST('zhang');

18. 避免 UNION，改用 UNION ALL

-- 错误：UNION 去重需要排序，性能差
SELECT user_id FROM users UNIONSELECT user_id FROM orders;
-- 正确：确认无重复数据，用 UNION ALL
SELECT user_id FROM users UNIONALLSELECT user_id FROM orders;

19. 定期分析表统计信息，帮助优化器

# MySQL 8.0+ 自动更新，5.7 需要手动
mysql -u root -p << 'EOF'
ANALYZE TABLE users;
ANALYZE TABLE orders;
EOF

20. 避免在 GROUP BY 后使用 HAVING 过滤

-- 低效：GROUP BY 全表 → HAVING 再过滤
SELECT user_id, COUNT(*) FROM orders GROUPBY user_id HAVINGCOUNT(*) >100;
-- 优化：先用 WHERE 过滤，再 GROUP BY
SELECT user_id, COUNT(*) FROM orders
WHERE created_at >'2024-01-01'
GROUPBY user_id
HAVINGCOUNT(*) >100;

第三部分：架构设计 (10 条)

21. 实施主从复制，分离读写

# 主库：处理写（INSERT/UPDATE/DELETE）
# 从库：处理读（SELECT），可有多个
# 读写比例通常 1:9（1 主 9 从）

22. 部署 Redis 缓存热数据，减少数据库访问

# Cache-Aside 模式
defget_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

23. 业务高峰期使用查询结果缓存

-- MySQL 5.7 支持查询缓存（8.0 已删除）
SETGLOBAL query_cache_type =1;
SETGLOBAL query_cache_size =1GB;

24. 分库分表支持无限扩展

单库容量上限：100GB（B+ 树高度限制）
分库分表：4 库 × 4 表 = 16 个分片，容量 = 100GB × 16 = 1.6TB
水平扩展：增加分片数（如改为 8 库 × 8 表 = 64 分片），容量再提升

25. 监控关键指标，提前预警

# 每日定时检查
- InnoDB 缓冲池使用率 > 95%
- 慢查询 QPS > 10 条/分钟
- 主从复制延迟 > 1 秒
- 磁盘使用率 > 85%

26. 定期备份，制定恢复计划

# 每日全量备份，每小时增量备份
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

27. 使用 utf8mb4 避免字符集问题

-- 默认 utf8（MySQL 中仅支持 3 字节），改用 utf8mb4（4 字节）
CREATE TABLE users (
    user_id BIGINTPRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

28. 避免大事务，拆分为小事务

-- 错误：一个事务更新 1w 条记录，主从延迟严重
BEGIN;
UPDATE users SET status='active'WHERE created_at <'2024-01-01';  -- 100w 行
COMMIT;
-- 正确：分批更新
FOR i IN0..99:
UPDATE users SET status='active'
WHERE created_at <'2024-01-01'
    LIMIT i*10000, 10000;

29. 开启 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

30. 建立变更审批流程，避免线上故障

DBA 变更检查清单：
☐ EXPLAIN 确认查询是否走索引
☐ 在从库灰度验证（1 小时）
☐ 在预发布环境全量验证（30 分钟）
☐ 在主库执行前备份（mysqldump）
☐ 准备回滚脚本
☐ 设置时间窗口（业务低谷）
☐ 监控变更后的性能指标（30 分钟）

1️⃣2️⃣ 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: 分库分表的最大痛点。解决方案：

1. 应用层 JOIN：将两个库的数据都查出来，在应用层合并（内存占用高）
2. 冗余字段：在订单表冗余用户名，避免 JOIN（数据一致性维护复杂）
3. 搜索引擎：关联数据写入 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️⃣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）"

posted @ 2025-12-21 15:46 clnchanpin 阅读(96) 评论(0) 收藏举报

刷新页面返回顶部