Day 05 | 运维脚本与优化

今日里程碑：你将不再写那种”一次性脚本”，而是通过函数封装、错误处理编写可用于生产环境的 自动化运维工具；同时，你将掌握数据库性能优化的”黄金法则”。今天不只是写代码，而是要彻底理解 “如何编写健壮的运维脚本”、“如何分析慢查询” 以及 “如何优化数据库性能”。

知识图谱 | Knowledge Map

Shell 运维自动化

系统监控：磁盘/内存水位监测
逻辑封装：增强健壮性的 Error Handling
远程执行：SSH 批量下发指令
备份自动化：定时备份脚本

SQL 性能调优

EXPLAIN 进阶：读懂 type/rows/key_len
优化口诀：小表驱动大表、索引失效规避
实战案例：千万级大表的分页优化
慢查询分析：定位性能瓶颈

代码任务 (90 mins)

Shell 生产级脚本实战

我们将编写一个 自动备份与磁盘监控脚本。拒绝硬编码，采用函数式编程。任务分解：

定义配置变量
编写颜色输出函数
编写磁盘检查函数
编写备份函数
编写清理旧备份函数
添加错误处理

#!/bin/bash
# backup_script.sh - 生产级备份脚本

# ============================================
# 配置区（集中管理配置，便于修改）
# ============================================

# 备份源目录
BACKUP_SRC="/var/www/html"

# 备份目标目录
BACKUP_DEST="/backup"

# 备份文件前缀
BACKUP_PREFIX="backup"

# 保留天数（超过此天数的备份将被删除）
RETENTION_DAYS=7

# 磁盘使用率告警阈值（百分比）
DISK_WARNING_THRESHOLD=80

# 生成时间戳（格式：YYYYMMDD_HHMMSS）
DATE=$(date +%Y%m%d_%H%M%S)

# ============================================
# 颜色输出函数（提高可读性）
# ============================================

# 红色输出（用于错误和告警）
function echo_red() {
    # \033[31m：红色
    # $1：第一个参数（要输出的文本）
    # \033[0m：重置颜色
    # -e：启用转义字符解释
    echo -e "\033[31m$1\033[0m"
}

# 绿色输出（用于成功信息）
function echo_green() {
    # \033[32m：绿色
    echo -e "\033[32m$1\033[0m"
}

# 黄色输出（用于警告信息）
function echo_yellow() {
    # \033[33m：黄色
    echo -e "\033[33m$1\033[0m"
}

# ============================================
# 磁盘检查函数
# ============================================

function check_disk() {
    # 获取根目录（/）的磁盘使用率
    # df /：显示根目录的磁盘使用情况
    # grep /：过滤包含 "/" 的行
    # awk '{print $5}'：提取第 5 列（使用率，格式：45%）
    # sed 's/%//g'：删除百分号，只保留数字
    usage=$(df / | grep / | awk '{print $5}' | sed 's/%//g')
    
    # 判断磁盘使用率是否超过阈值
    if [ "$usage" -gt "$DISK_WARNING_THRESHOLD" ]; then
        # 超过阈值，输出红色告警
        echo_red "[ALARM] Disk usage is high: ${usage}%"
        echo_yellow "Warning: Backup may fail if disk is full!"
        return 1  # 返回非零表示警告
    else
        # 正常，输出绿色信息
        echo_green "[OK] Disk usage: ${usage}%"
        return 0  # 返回零表示正常
    fi
}

# ============================================
# 执行备份函数
# ============================================

function perform_backup() {
    # 创建备份目录（如果不存在）
    # mkdir -p：递归创建目录，如果目录已存在不会报错
    mkdir -p "$BACKUP_DEST"
    
    # 检查源目录是否存在
    if [ ! -d "$BACKUP_SRC" ]; then
        echo_red "[ERROR] Source directory does not exist: $BACKUP_SRC"
        return 1
    fi
    
    # 构建备份文件名
    backup_file="$BACKUP_DEST/${BACKUP_PREFIX}_${DATE}.tar.gz"
    
    # 执行备份
    # tar -zcf：创建 gzip 压缩的 tar 文件
    # -z：使用 gzip 压缩
    # -c：创建归档
    # -f：指定文件名
    # 2>/dev/null：将错误输出重定向到 /dev/null（忽略错误）
    tar -zcf "$backup_file" "$BACKUP_SRC" 2>/dev/null
    
    # 检查备份是否成功
    # $?：上一个命令的退出码
    # 0 表示成功，非零表示失败
    if [ $? -eq 0 ]; then
        # 获取备份文件大小
        backup_size=$(du -h "$backup_file" | awk '{print $1}')
        echo_green "[SUCCESS] Backup created: $backup_file (Size: $backup_size)"
        return 0
    else
        echo_red "[ERROR] Backup failed!"
        return 1
    fi
}

# ============================================
# 清理旧备份函数
# ============================================

function cleanup_old_backups() {
    echo "Cleaning up backups older than $RETENTION_DAYS days..."
    
    # 查找并删除超过保留天数的备份文件
    # find $BACKUP_DEST：在备份目录中查找
    # -name "${BACKUP_PREFIX}_*.tar.gz"：匹配备份文件模式
    # -mtime +$RETENTION_DAYS：修改时间超过 RETENTION_DAYS 天
    # -delete：删除找到的文件
    deleted_count=$(find "$BACKUP_DEST" -name "${BACKUP_PREFIX}_*.tar.gz" -mtime +$RETENTION_DAYS -delete -print | wc -l)
    
    if [ "$deleted_count" -gt 0 ]; then
        echo_green "[SUCCESS] Deleted $deleted_count old backup(s)"
    else
        echo "No old backups to delete"
    fi
}

# ============================================
# 主流程
# ============================================

echo "=========================================="
echo "Backup Script Started"
echo "=========================================="
echo "Source: $BACKUP_SRC"
echo "Destination: $BACKUP_DEST"
echo "Date: $(date '+%Y-%m-%d %H:%M:%S')"
echo "=========================================="
echo ""

# 1. 检查磁盘空间
check_disk
disk_check_result=$?

# 2. 执行备份
if perform_backup; then
    # 3. 清理旧备份
    cleanup_old_backups
    
    echo ""
    echo_green "=========================================="
    echo_green "Backup completed successfully!"
    echo_green "=========================================="
    exit 0
else
    echo ""
    echo_red "=========================================="
    echo_red "Backup failed!"
    echo_red "=========================================="
    exit 1
fi

常见错误：

❌ usage=$(df / | awk '{print $5}') - 没有过滤，可能获取错误行
❌ if [ $usage -gt 80 ] - 变量未加引号，空值会报错
❌ tar -zcf backup.tar.gz /var/www/html - 没有错误处理
✅ usage=$(df / | grep / | awk '{print $5}' | sed 's/%//g') - 正确
✅ if [ "$usage" -gt "$DISK_WARNING_THRESHOLD" ] - 正确
✅ if [ $? -eq 0 ]; then ... fi - 正确

验证步骤：

修改配置（根据实际情况）：

# 修改脚本中的配置变量
BACKUP_SRC="./test_data"  # 测试用目录
BACKUP_DEST="./backups"   # 测试用备份目录

创建测试目录：

mkdir -p test_data
echo "test file" > test_data/test.txt

运行脚本：

chmod +x backup_script.sh
./backup_script.sh

检查输出：
- 应该看到磁盘使用率检查结果
- 应该看到备份成功信息
- 应该看到备份文件大小
- 应该看到旧备份清理信息

验证备份文件：

ls -lh backups/
tar -tzf backups/backup_*.tar.gz  # 查看备份内容

SQL 慢查询优化

我们将学习如何分析和优化慢查询，这是数据库性能优化的核心技能。任务分解：

启用慢查询日志
分析慢查询场景
优化查询语句
对比优化前后的性能

-- ============================================
-- Day 05: SQL 慢查询优化实战
-- ============================================

USE infra_db;

-- ============================================
-- 1. 启用慢查询日志（MySQL 配置）
-- ============================================

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 启用慢查询日志（需要 MySQL 权限）
-- SET GLOBAL slow_query_log = 'ON';
-- SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询记录为慢查询

-- ============================================
-- 2. 场景一：全表扫描优化
-- ============================================

-- 问题查询：使用 != 导致全表扫描
-- 优化前：type = ALL（全表扫描）
EXPLAIN SELECT * FROM users WHERE status != 1;
-- 预期：type = ALL, rows = 全表行数

-- 优化方案 1：使用 IN 代替 !=
-- 优化后：type = range（范围扫描）
EXPLAIN SELECT * FROM users WHERE status IN (0, 2);
-- 预期：type = range（如果 status 有索引）

-- 优化方案 2：使用 UNION（如果 IN 列表很长）
EXPLAIN 
SELECT * FROM users WHERE status = 0
UNION
SELECT * FROM users WHERE status = 2;
-- 预期：每个子查询可以使用索引

-- ============================================
-- 3. 场景二：模糊匹配优化
-- ============================================

-- 问题查询：左模糊匹配导致索引失效
-- 优化前：type = ALL（全表扫描）
EXPLAIN SELECT * FROM users WHERE username LIKE '%admin';
-- 预期：type = ALL, key = NULL

-- 优化方案 1：使用右模糊匹配（可以使用索引）
EXPLAIN SELECT * FROM users WHERE username LIKE 'admin%';
-- 预期：type = range, key = idx_username

-- 优化方案 2：如果必须使用左模糊，考虑全文索引或搜索引擎
-- 这里只是演示，实际生产环境可能需要 Elasticsearch 等工具

-- ============================================
-- 4. 场景三：深分页优化（重要！）
-- ============================================

-- 问题查询：LIMIT 1000000, 10 会导致极慢
-- 原因：MySQL 需要扫描并丢弃前 100 万行
-- 优化前：type = ALL, rows = 1000010（扫描 100 万+ 行）
EXPLAIN SELECT * FROM users ORDER BY id LIMIT 1000000, 10;
-- 预期：非常慢，即使有索引

-- 优化方案：利用覆盖索引先查 ID，再回表查询
-- 步骤 1：先查询 ID（使用覆盖索引，很快）
EXPLAIN SELECT id FROM users ORDER BY id LIMIT 1000000, 10;
-- 预期：type = index（全索引扫描，但比全表扫描快）

-- 步骤 2：使用子查询或 JOIN 回表查询
EXPLAIN 
SELECT u.* 
FROM users u
INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 10
) AS tmp ON u.id = tmp.id;
-- 预期：先执行子查询（快），再 JOIN（只 JOIN 10 行，也快）

-- ============================================
-- 5. 场景四：函数操作优化
-- ============================================

-- 问题查询：对字段使用函数导致索引失效
-- 优化前：type = ALL（全表扫描）
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 预期：type = ALL, key = NULL

-- 优化方案：将函数操作移到常量侧
-- 优化后：type = range（范围扫描）
EXPLAIN SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';
-- 预期：type = range（如果 created_at 有索引）

-- ============================================
-- 6. 场景五：OR 条件优化
-- ============================================

-- 问题查询：OR 条件可能导致索引失效
-- 优化前：可能 type = ALL
EXPLAIN SELECT * FROM users 
WHERE username = 'Alice' OR email = 'alice@example.com';
-- 预期：可能无法使用索引

-- 优化方案：使用 UNION 代替 OR
-- 优化后：每个子查询可以使用索引
EXPLAIN 
SELECT * FROM users WHERE username = 'Alice'
UNION
SELECT * FROM users WHERE email = 'alice@example.com';
-- 预期：每个子查询 type = ref（如果字段有索引）

-- ============================================
-- 7. 性能对比测试
-- ============================================

-- 测试查询性能（需要实际数据）
-- 注意：实际测试时需要大量数据才能看到明显差异

-- 慢查询（全表扫描）
-- SELECT * FROM users WHERE status != 1;

-- 优化后（使用索引）
-- SELECT * FROM users WHERE status IN (0, 2);

-- 使用时间函数测量（MySQL 8.0+）
-- SELECT BENCHMARK(1000000, (SELECT COUNT(*) FROM users WHERE status != 1));

字段类型选择详解：

慢查询优化原则

1. 避免全表扫描：

为 WHERE 条件中的字段添加索引
避免使用 !=、NOT IN、IS NULL（某些情况）
避免对字段使用函数

2. 利用索引：

使用 =、IN、BETWEEN、LIKE 'prefix%'
遵循最左前缀原则
使用覆盖索引减少回表

3. 优化 JOIN：

小表驱动大表
为 JOIN 条件添加索引
避免子查询，使用 JOIN

4. 优化排序：

为 ORDER BY 字段添加索引
避免 ORDER BY 和 WHERE 使用不同索引

深分页优化详解

问题： LIMIT 1000000, 10 需要扫描并丢弃前 100 万行，非常慢。优化方案：

-- 步骤 1：先查 ID（使用覆盖索引，很快）
SELECT id FROM users ORDER BY id LIMIT 1000000, 10;

-- 步骤 2：回表查询（只查询 10 行，很快）
SELECT * FROM users WHERE id IN (1000001, 1000002, ..., 1000010);

或者使用 JOIN：

SELECT u.* 
FROM users u
INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 10
) AS tmp ON u.id = tmp.id;

性能提升：

优化前：扫描 100 万+ 行，耗时数秒
优化后：扫描索引 + 查询 10 行，耗时毫秒级

小表驱动大表

原则：小表作为驱动表，大表作为被驱动表。原因：

驱动表会被全表扫描
被驱动表通过索引查找
小表扫描快，大表通过索引查找也快

示例：

-- 假设 users 表小，orders 表大
-- ✅ 正确：users 作为驱动表
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- ❌ 错误：orders 作为驱动表（如果 MySQL 选择错误）
-- 可以通过 STRAIGHT_JOIN 强制顺序
SELECT * FROM orders o
STRAIGHT_JOIN users u ON o.user_id = u.id;

验证步骤：

连接 MySQL：

docker exec -it mysql-learn mysql -uroot -proot

执行 SQL 脚本：

USE infra_db;
source /path/to/04_slow_query_opt.sql;

分析查询：

-- 对比优化前后的 EXPLAIN 结果
EXPLAIN SELECT * FROM users WHERE status != 1;
EXPLAIN SELECT * FROM users WHERE status IN (0, 2);
-- 对比 type 和 rows 的差异

测试深分页优化：

-- 如果有足够的数据，测试深分页
EXPLAIN SELECT * FROM users ORDER BY id LIMIT 1000, 10;
EXPLAIN 
SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 1000, 10) AS tmp 
ON u.id = tmp.id;

常见错误：

❌ WHERE status != 1 - 导致全表扫描
❌ WHERE username LIKE '%keyword' - 索引失效
❌ WHERE YEAR(created_at) = 2024 - 索引失效
❌ LIMIT 1000000, 10 - 深分页，非常慢
✅ WHERE status IN (0, 2) - 可以使用索引
✅ WHERE username LIKE 'keyword%' - 可以使用索引
✅ WHERE created_at >= '2024-01-01' - 可以使用索引
✅ 使用子查询优化深分页 - 正确

每日挑战 (Challenge)

Shell 挑战

任务：给备份脚本加上 find $BACKUP_DEST -mtime +7 -delete 命令，实现自动删除 7 天前旧备份的功能。提示：

使用 find 命令查找旧文件
-mtime +7：修改时间超过 7 天
-delete：删除找到的文件
思考：如何添加日志记录删除的文件？

SQL 挑战

任务：使用 SHOW PROFILES 查看你刚才执行的 SQL 到底耗时多少秒。提示：

启用 profiling：SET profiling = 1;
执行查询后：SHOW PROFILES;
查看详细分析：SHOW PROFILE FOR QUERY 1;
思考：如何对比优化前后的性能差异？

今日产出物

day05/backup_script.sh - Shell 生产级备份脚本
day05/04_slow_query_opt.sql - SQL 慢查询优化脚本

参考代码

查看参考代码

在 GitHub 查看完整的示例代码

在线运行

使用在线编辑器测试代码

实际应用场景

Shell 生产级脚本在运维中的应用

自动化备份：定时执行备份脚本，确保数据安全
系统监控：监控磁盘、内存、CPU 使用率，及时告警
日志管理：自动清理旧日志，防止磁盘满
部署自动化：编写健壮的部署脚本，减少人工错误
故障恢复：编写自动恢复脚本，快速恢复服务

SQL 慢查询优化在生产环境的重要性

性能监控：定期分析慢查询日志，定位性能瓶颈
索引优化：根据查询模式设计合适的索引
查询优化：优化慢查询语句，提高响应速度
容量规划：通过性能分析预测数据库容量需求
用户体验：优化查询性能，提升用户体验

与 Day 04 和 Day 06 的关联：

Day 04 学习了索引的基础知识，今天学习了如何优化慢查询
Day 06 将进行综合实战，今天学习的脚本编写和查询优化是基础
掌握今天的生产级脚本和慢查询优化，明天的综合实战会更轻松

上一天: 三剑客优化

Day 04 | Shell 三剑客与 SQL 优化

下一天: 整合实战

Day 06 | 第一阶段综合实战

网站概览 | Overview

项目愿景 | Project Vision

内部培训 | Internal Training

技术规范 | Protocols

基础设施 | Infrastructure

工作安排 | Work Plan

团队协作 | Team Collab

Day 05 | 运维脚本与优化

知识图谱 | Knowledge Map

Shell 运维自动化

SQL 性能调优

代码任务 (90 mins)

每日挑战 (Challenge)

Shell 挑战

SQL 挑战

今日产出物

参考代码

查看参考代码

在线运行

实际应用场景

Shell 生产级脚本在运维中的应用

SQL 慢查询优化在生产环境的重要性

上一天: 三剑客优化

下一天: 整合实战

​ 知识图谱 | Knowledge Map

Shell 运维自动化

SQL 性能调优

​ 代码任务 (90 mins)

​ 每日挑战 (Challenge)

Shell 挑战

SQL 挑战

​ 今日产出物

​ 参考代码

查看参考代码

在线运行

​实际应用场景

Shell 生产级脚本在运维中的应用

SQL 慢查询优化在生产环境的重要性

上一天: 三剑客优化

下一天: 整合实战

知识图谱 | Knowledge Map

代码任务 (90 mins)

每日挑战 (Challenge)

今日产出物

参考代码

实际应用场景