> ## Documentation Index
> Fetch the complete documentation index at: https://docs.akria.net/llms.txt
> Use this file to discover all available pages before exploring further.

# Day 05 | 运维脚本与优化

> Shell 生产级备份脚本与 SQL 慢查询深度优化实战

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

## <span style={{ display: 'inline-flex', alignItems: 'center', gap: '8px' }}><Icon icon="diagram-project" size={26} color="#059669" /> 知识图谱 | Knowledge Map</span>

<CardGroup cols={2}>
  <Card title="Shell 运维自动化" icon="server" color="#ea5a0c">
    * **系统监控**：磁盘/内存水位监测
    * **逻辑封装**：增强健壮性的 Error Handling
    * **远程执行**：SSH 批量下发指令
    * **备份自动化**：定时备份脚本
  </Card>

  <Card title="SQL 性能调优" icon="database" color="#0285c7">
    * **EXPLAIN 进阶**：读懂 type/rows/key\_len
    * **优化口诀**：小表驱动大表、索引失效规避
    * **实战案例**：千万级大表的分页优化
    * **慢查询分析**：定位性能瓶颈
  </Card>
</CardGroup>

***

## <span style={{ display: 'inline-flex', alignItems: 'center', gap: '8px' }}><Icon icon="code" size={26} color="#059669" /> 代码任务 (90 mins)</span>

<Steps>
  <Step title="Shell 生产级脚本实战">
    我们将编写一个 **自动备份与磁盘监控脚本**。拒绝硬编码，采用函数式编程。

    **任务分解**：

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

    <CodeGroup>
      ```bash backup_script.sh theme={null}
      #!/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
      ```
    </CodeGroup>

    **常见错误**：

    <Warning>
      * ❌ `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` - 正确
    </Warning>

    **验证步骤**：

    1. 修改配置（根据实际情况）：
       ```bash theme={null}
       # 修改脚本中的配置变量
       BACKUP_SRC="./test_data"  # 测试用目录
       BACKUP_DEST="./backups"   # 测试用备份目录
       ```

    2. 创建测试目录：
       ```bash theme={null}
       mkdir -p test_data
       echo "test file" > test_data/test.txt
       ```

    3. 运行脚本：
       ```bash theme={null}
       chmod +x backup_script.sh
       ./backup_script.sh
       ```

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

    5. 验证备份文件：
       ```bash theme={null}
       ls -lh backups/
       tar -tzf backups/backup_*.tar.gz  # 查看备份内容
       ```
  </Step>

  <Step title="SQL 慢查询优化">
    我们将学习如何分析和优化慢查询，这是数据库性能优化的核心技能。

    **任务分解**：

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

    <CodeGroup>
      ```sql 04_slow_query_opt.sql theme={null}
      -- ============================================
      -- 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));
      ```
    </CodeGroup>

    **字段类型选择详解**：

    <AccordionGroup>
      <Accordion title="慢查询优化原则">
        **1. 避免全表扫描**：

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

        **2. 利用索引**：

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

        **3. 优化 JOIN**：

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

        **4. 优化排序**：

        * 为 ORDER BY 字段添加索引
        * 避免 `ORDER BY` 和 `WHERE` 使用不同索引
      </Accordion>

      <Accordion title="深分页优化详解">
        **问题**：
        `LIMIT 1000000, 10` 需要扫描并丢弃前 100 万行，非常慢。

        **优化方案**：

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

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

        **或者使用 JOIN**：

        ```sql theme={null}
        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 行，耗时毫秒级
      </Accordion>

      <Accordion title="小表驱动大表">
        **原则**：小表作为驱动表，大表作为被驱动表。

        **原因**：

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

        **示例**：

        ```sql theme={null}
        -- 假设 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;
        ```
      </Accordion>
    </AccordionGroup>

    **验证步骤**：

    1. 连接 MySQL：
       ```bash theme={null}
       docker exec -it mysql-learn mysql -uroot -proot
       ```

    2. 执行 SQL 脚本：
       ```sql theme={null}
       USE infra_db;
       source /path/to/04_slow_query_opt.sql;
       ```

    3. 分析查询：
       ```sql theme={null}
       -- 对比优化前后的 EXPLAIN 结果
       EXPLAIN SELECT * FROM users WHERE status != 1;
       EXPLAIN SELECT * FROM users WHERE status IN (0, 2);
       -- 对比 type 和 rows 的差异
       ```

    4. 测试深分页优化：
       ```sql theme={null}
       -- 如果有足够的数据，测试深分页
       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;
       ```

    **常见错误**：

    <Warning>
      * ❌ `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'` - 可以使用索引
      * ✅ 使用子查询优化深分页 - 正确
    </Warning>
  </Step>
</Steps>

***

## <span style={{ display: 'inline-flex', alignItems: 'center', gap: '8px' }}><Icon icon="trophy" size={26} color="#059669" /> 每日挑战 (Challenge)</span>

<CardGroup cols={2}>
  <Card title="Shell 挑战" icon="terminal">
    **任务**：给备份脚本加上 `find $BACKUP_DEST -mtime +7 -delete` 命令，实现自动删除 7 天前旧备份的功能。

    **提示**：

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

  <Card title="SQL 挑战" icon="database">
    **任务**：使用 `SHOW PROFILES` 查看你刚才执行的 SQL 到底耗时多少秒。

    **提示**：

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

***

## <span style={{ display: 'inline-flex', alignItems: 'center', gap: '8px' }}><Icon icon="file" size={26} color="#059669" /> 今日产出物</span>

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

***

## <span style={{ display: 'inline-flex', alignItems: 'center', gap: '8px' }}><Icon icon="github" size={26} color="#059669" /> 参考代码</span>

<CardGroup cols={2}>
  <Card title="查看参考代码" icon="code" href="https://github.com/akriamail/insightful-ops/tree/main/docs/mintlify/scripts/training/30daystudy/week-1/day-05">
    在 GitHub 查看完整的示例代码

    <br />

    <small>包含 Shell 脚本和 SQL 文件</small>
  </Card>

  <Card title="在线运行" icon="play" href="https://replit.com">
    使用在线编辑器测试代码

    <br />

    <small>无需本地环境配置</small>
  </Card>
</CardGroup>

***

## 实际应用场景

<CardGroup cols={2}>
  <Card title="Shell 生产级脚本在运维中的应用" icon="server">
    * **自动化备份**：定时执行备份脚本，确保数据安全
    * **系统监控**：监控磁盘、内存、CPU 使用率，及时告警
    * **日志管理**：自动清理旧日志，防止磁盘满
    * **部署自动化**：编写健壮的部署脚本，减少人工错误
    * **故障恢复**：编写自动恢复脚本，快速恢复服务
  </Card>

  <Card title="SQL 慢查询优化在生产环境的重要性" icon="database">
    * **性能监控**：定期分析慢查询日志，定位性能瓶颈
    * **索引优化**：根据查询模式设计合适的索引
    * **查询优化**：优化慢查询语句，提高响应速度
    * **容量规划**：通过性能分析预测数据库容量需求
    * **用户体验**：优化查询性能，提升用户体验
  </Card>
</CardGroup>

<Tip>
  **与 Day 04 和 Day 06 的关联**：

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

***

<CardGroup cols={2}>
  <Card title="上一天: 三剑客优化" href="/training/30daystudy/day-04">
    Day 04 | Shell 三剑客与 SQL 优化
  </Card>

  <Card title="下一天: 整合实战" href="/training/30daystudy/day-06">
    Day 06 | 第一阶段综合实战
  </Card>
</CardGroup>