> ## 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 04 | 三剑客与 SQL 优化

> Shell 文本处理三剑客与 SQL 索引优化核心

<Info>
  **今日目标**：掌握 Linux 文本处理最强工具（grep/awk/sed），并学会使用 EXPLAIN 分析 SQL 性能瓶颈。今天不只是写代码，而是要彻底理解 **"如何高效地处理文本数据"**、**"索引为什么能提高查询速度"** 以及 **"如何通过 EXPLAIN 优化 SQL 性能"**。
</Info>

## <span style={{ display: 'inline-flex', alignItems: 'center', gap: '8px' }}><Icon icon="book" size={26} color="#059669" /> 学习内容 (30 mins)</span>

在开始写代码前，先搞懂这些核心概念，否则后面的代码你会看得云里雾里。

<AccordionGroup>
  <Accordion title="Shell 三剑客 (15 mins)" icon="terminal">
    **什么是三剑客？**

    grep、awk、sed 是 Linux 文本处理的三大神器，被称为"三剑客"。它们各有所长，组合使用可以处理各种文本处理任务。

    **为什么需要三剑客？**

    * **日志分析**：从大量日志中提取关键信息
    * **数据提取**：从结构化文本中提取特定字段
    * **文本替换**：批量修改配置文件
    * **数据转换**：将数据从一种格式转换为另一种格式

    **grep 详解**：

    * **作用**：在文件中搜索匹配的行
    * **基本语法**：`grep "pattern" file`
    * **常用选项**：
      * `-i`：忽略大小写
      * `-v`：反向匹配（显示不匹配的行）
      * `-n`：显示行号
      * `-c`：统计匹配的行数
      * `-r`：递归搜索目录
      * `-E`：使用扩展正则表达式
    * **实际应用**：
      ```bash theme={null}
      grep "ERROR" log.txt              # 查找包含 ERROR 的行
      grep -i "error" log.txt           # 忽略大小写查找
      grep -v "INFO" log.txt            # 查找不包含 INFO 的行
      grep -n "ERROR" log.txt           # 显示行号
      grep -c "ERROR" log.txt           # 统计 ERROR 出现的次数
      ```

    **awk 详解**：

    * **作用**：处理文本文件，按列处理数据
    * **基本语法**：`awk 'pattern {action}' file`
    * **工作原理**：
      * 按行读取文件
      * 每行按空格或制表符分割成字段
      * `$1`、`$2`、`$3` 分别表示第 1、2、3 列
      * `$0` 表示整行
      * `NF` 表示字段数
      * `NR` 表示当前行号
    * **实际应用**：
      ```bash theme={null}
      awk '{print $1}' file.txt        # 打印第一列
      awk '{print $NF}' file.txt       # 打印最后一列
      awk 'NR==1' file.txt             # 打印第一行
      awk '$1 > 100 {print $0}' file.txt  # 第一列大于 100 的行
      awk '{sum+=$1} END {print sum}' file.txt  # 计算第一列的总和
      ```

    **sed 详解**：

    * **作用**：流编辑器，用于文本替换、删除、插入
    * **基本语法**：`sed 's/old/new/g' file`
    * **常用命令**：
      * `s`：替换（substitute）
      * `d`：删除（delete）
      * `p`：打印（print）
      * `g`：全局替换（global）
    * **实际应用**：
      ```bash theme={null}
      sed 's/old/new/g' file.txt       # 替换所有 old 为 new
      sed 's/old/new/' file.txt        # 只替换每行第一个 old
      sed '/pattern/d' file.txt        # 删除包含 pattern 的行
      sed -n '1,5p' file.txt           # 打印第 1 到 5 行
      sed -i 's/old/new/g' file.txt   # 直接修改文件（-i 选项）
      ```

    **三剑客组合使用**：

    * **管道组合**：将三个工具通过管道连接
      ```bash theme={null}
      grep "ERROR" log.txt | awk '{print $2}' | sed 's/://g'
      ```
      * 先用 grep 过滤包含 ERROR 的行
      * 再用 awk 提取第二列
      * 最后用 sed 删除冒号
  </Accordion>

  <Accordion title="SQL 性能优化 (15 mins)" icon="database">
    **什么是索引？**

    索引是数据库的"目录"，类似于书籍的目录。它可以帮助数据库快速定位数据，而不需要扫描整个表。

    **为什么需要索引？**

    * **提高查询速度**：从全表扫描变为索引查找
    * **加速排序**：ORDER BY 操作更快
    * **加速连接**：JOIN 操作更快
    * **唯一性约束**：唯一索引保证数据唯一性

    **索引的工作原理**：

    * **B+ 树结构**：索引通常使用 B+ 树存储
    * **快速定位**：通过索引快速找到数据位置
    * **避免全表扫描**：不需要扫描所有数据

    **索引类型**：

    * **普通索引**：最基本的索引，没有任何限制
    * **唯一索引**：索引列的值必须唯一
    * **主键索引**：特殊的唯一索引，不能为 NULL
    * **复合索引**：多个字段组成的索引

    **EXPLAIN 详解**：

    * **作用**：分析 SQL 语句的执行计划
    * **使用方法**：`EXPLAIN SELECT ...`
    * **关键字段**：
      * **type**：连接类型（性能从好到坏）
        * `system`：系统表，只有一行
        * `const`：主键或唯一索引查询，只有一行
        * `eq_ref`：唯一索引扫描
        * `ref`：非唯一索引扫描
        * `range`：范围扫描
        * `index`：全索引扫描
        * `ALL`：全表扫描（最慢，需要优化）
      * **key**：使用的索引
      * **rows**：扫描的行数（越少越好）
      * **Extra**：额外信息

    **索引失效的情况**：

    * **LIKE 左模糊**：`LIKE '%keyword'` 会导致索引失效
    * **函数操作**：`WHERE YEAR(created_at) = 2024` 会导致索引失效
    * **类型转换**：`WHERE id = '123'`（id 是 INT）会导致类型转换
    * **OR 条件**：某些情况下 OR 会导致索引失效
    * **NOT 条件**：`WHERE status != 1` 可能导致索引失效
  </Accordion>
</AccordionGroup>

***

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

<Steps>
  <Step title="日志分析脚本">
    编写 `04_log_analysis.sh`，分析 Nginx/System 日志：

    **任务分解**：

    1. 模拟生成包含 ERROR 和 INFO 的日志文件
    2. 使用 grep 提取所有 ERROR 行
    3. 使用 awk 统计 ERROR 出现的次数
    4. 使用 sed 格式化输出

    <CodeGroup>
      ```bash 04_log_analysis.sh theme={null}
      #!/bin/bash
      # 04_log_analysis.sh - 日志分析脚本

      LOG_FILE="app_trace.log"

      # ============================================
      # 1. 模拟生成日志文件
      # ============================================

      # 创建日志文件并写入模拟数据
      # >：覆盖写入（如果文件已存在，会清空后写入）
      echo "INFO: Service started" > $LOG_FILE
      echo "ERROR: Connection timeout" >> $LOG_FILE
      echo "INFO: Health check pass" >> $LOG_FILE
      echo "ERROR: Database unreachable" >> $LOG_FILE
      echo "INFO: User login successful" >> $LOG_FILE
      echo "ERROR: Permission denied" >> $LOG_FILE

      echo "Log file created: $LOG_FILE"
      echo "----------------------------------------"

      # ============================================
      # 2. 使用 grep 提取 ERROR 行
      # ============================================

      echo "Analyzing errors..."
      echo "----------------------------------------"

      # grep "ERROR"：查找包含 ERROR 的行
      # -n：显示行号
      # -i：忽略大小写（虽然这里不需要，但演示用法）
      echo "All ERROR lines (with line numbers):"
      grep -n "ERROR" $LOG_FILE
      echo ""

      # ============================================
      # 3. 使用 awk 提取错误信息
      # ============================================

      # awk -F': '：指定分隔符为 ": "（冒号+空格）
      # {print $2}：打印第二列（错误信息部分）
      echo "Error messages only:"
      grep "ERROR" $LOG_FILE | awk -F': ' '{print $2}'
      echo ""

      # ============================================
      # 4. 使用 grep 统计错误数量
      # ============================================

      # grep -c：统计匹配的行数
      error_count=$(grep -c "ERROR" $LOG_FILE)
      echo "Total Errors: $error_count"
      echo ""

      # ============================================
      # 5. 使用 awk 统计 INFO 和 ERROR 的数量
      # ============================================

      echo "Log statistics:"
      # awk 统计：对每行进行匹配，累加计数
      # /ERROR/：匹配包含 ERROR 的行
      # /INFO/：匹配包含 INFO 的行
      # END：处理完所有行后执行
      awk '
      /ERROR/ { error++ }
      /INFO/ { info++ }
      END {
          print "  ERROR: " error
          print "  INFO:  " info
          print "  Total: " (error + info)
      }
      ' $LOG_FILE
      echo ""

      # ============================================
      # 6. 使用 sed 格式化输出
      # ============================================

      echo "Formatted error messages:"
      # sed 's/ERROR: //'：删除 "ERROR: " 前缀
      # sed 's/$/ [CRITICAL]/'：在每行末尾添加 " [CRITICAL]"
      grep "ERROR" $LOG_FILE | sed 's/ERROR: //' | sed 's/$/ [CRITICAL]/'
      echo ""

      # ============================================
      # 7. 三剑客组合使用
      # ============================================

      echo "Combined usage (grep | awk | sed):"
      # 管道组合：grep 过滤 -> awk 提取 -> sed 格式化
      grep "ERROR" $LOG_FILE | \
          awk -F': ' '{print $2}' | \
          sed 's/^/  - /' | \
          sed 's/$/ (needs attention)/'
      ```
    </CodeGroup>

    **常见错误**：

    <Warning>
      * ❌ `grep ERROR $LOG_FILE` - 如果 LOG\_FILE 为空，grep 会报错
      * ❌ `awk '{print $1}'` - 如果文件为空，awk 不会输出任何内容
      * ❌ `sed 's/old/new' file.txt` - 没有 g 标志，只替换每行第一个匹配
      * ✅ `grep "ERROR" "$LOG_FILE"` - 变量加引号，防止空格
      * ✅ `awk '{print $1}' file.txt` - 正确语法
      * ✅ `sed 's/old/new/g' file.txt` - 加 g 标志，全局替换
    </Warning>

    **验证步骤**：

    1. 运行脚本：
       ```bash theme={null}
       chmod +x 04_log_analysis.sh
       ./04_log_analysis.sh
       ```

    2. 检查输出：
       * 应该看到日志文件创建信息
       * 应该看到所有 ERROR 行（带行号）
       * 应该看到错误信息统计
       * 应该看到格式化后的错误消息

    3. 检查生成的日志文件：
       ```bash theme={null}
       cat app_trace.log
       ```

    4. 测试不同的 grep 选项：
       ```bash theme={null}
       grep -i "error" app_trace.log    # 忽略大小写
       grep -v "INFO" app_trace.log     # 反向匹配
       grep -c "ERROR" app_trace.log    # 统计数量
       ```
  </Step>

  <Step title="SQL 索引实战">
    编写 `04_index_opt.sql`：

    **任务分解**：

    1. 为用户名添加索引
    2. 使用 EXPLAIN 分析查询
    3. 对比有索引和无索引的查询性能
    4. 演示索引失效的情况

    <CodeGroup>
      ```sql 04_index_opt.sql theme={null}
      -- ============================================
      -- Day 04: SQL 索引优化实战
      -- ============================================

      USE infra_db;

      -- ============================================
      -- 1. 查看当前表结构
      -- ============================================

      -- 查看 users 表的索引情况
      SHOW INDEX FROM users;

      -- ============================================
      -- 2. 创建索引
      -- ============================================

      -- 为 username 字段创建普通索引
      -- 索引名称：idx_username（命名规范：idx_字段名）
      -- 如果索引已存在，会报错，可以使用 IF NOT EXISTS（MySQL 8.0+）
      ALTER TABLE users ADD INDEX idx_username (username);

      -- 验证索引创建
      SHOW INDEX FROM users;
      -- 应该看到 idx_username 索引

      -- ============================================
      -- 3. 使用 EXPLAIN 分析查询
      -- ============================================

      -- 分析使用索引的查询
      -- 这个查询应该使用 idx_username 索引
      EXPLAIN SELECT * FROM users WHERE username = 'Alice';

      -- 查看 EXPLAIN 结果的关键字段：
      -- type: ref（使用非唯一索引）
      -- key: idx_username（使用的索引）
      -- rows: 1（扫描的行数，应该很少）

      -- ============================================
      -- 4. 对比有索引和无索引的查询
      -- ============================================

      -- 查询 1：使用索引（精确匹配）
      EXPLAIN SELECT * FROM users WHERE username = 'Alice';
      -- 预期：type = ref, key = idx_username, rows = 1

      -- 查询 2：不使用索引（全表扫描）
      -- 注意：如果表很小，MySQL 可能选择全表扫描而不是索引
      EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
      -- 预期：type = ALL（全表扫描），因为没有 email 索引

      -- ============================================
      -- 5. 索引失效的情况
      -- ============================================

      -- 情况 1：LIKE 左模糊匹配（索引失效）
      EXPLAIN SELECT * FROM users WHERE username LIKE '%lic';
      -- 预期：type = ALL（全表扫描），索引失效
      -- 原因：左模糊匹配无法利用索引

      -- 情况 2：LIKE 右模糊匹配（可以使用索引）
      EXPLAIN SELECT * FROM users WHERE username LIKE 'Alic%';
      -- 预期：type = range, key = idx_username
      -- 原因：右模糊匹配可以利用索引

      -- 情况 3：函数操作（索引失效）
      -- 注意：如果 created_at 有索引
      EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
      -- 预期：type = ALL（全表扫描），索引失效
      -- 原因：对字段使用函数会导致索引失效

      -- 情况 4：类型转换（可能导致索引失效）
      -- 如果 id 是 INT 类型
      EXPLAIN SELECT * FROM users WHERE id = '1';
      -- MySQL 可能会进行类型转换，影响性能

      -- ============================================
      -- 6. 创建复合索引
      -- ============================================

      -- 为 status 和 created_at 创建复合索引
      -- 复合索引：多个字段组成的索引
      ALTER TABLE users ADD INDEX idx_status_created (status, created_at);

      -- 验证复合索引
      SHOW INDEX FROM users;

      -- 使用复合索引的查询
      EXPLAIN SELECT * FROM users WHERE status = 1 ORDER BY created_at DESC;
      -- 预期：type = ref, key = idx_status_created
      -- 注意：复合索引遵循"最左前缀原则"

      -- ============================================
      -- 7. 删除索引（如果需要）
      -- ============================================

      -- 删除索引的语法（演示用，不要执行）
      -- ALTER TABLE users DROP INDEX idx_username;
      ```
    </CodeGroup>

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

    <AccordionGroup>
      <Accordion title="EXPLAIN 结果解读">
        **type 字段（连接类型）**：

        * `system`：系统表，只有一行（最快）
        * `const`：主键或唯一索引查询，只有一行
        * `eq_ref`：唯一索引扫描
        * `ref`：非唯一索引扫描（常用，性能好）
        * `range`：范围扫描（如 BETWEEN、IN、LIKE 'prefix%'）
        * `index`：全索引扫描（比全表扫描好，但仍需优化）
        * `ALL`：全表扫描（最慢，需要优化）

        **key 字段**：

        * 显示使用的索引名称
        * `NULL` 表示没有使用索引

        **rows 字段**：

        * 扫描的行数
        * 越少越好

        **Extra 字段**：

        * `Using index`：使用覆盖索引（很好）
        * `Using where`：使用 WHERE 过滤
        * `Using filesort`：需要额外排序（可能需要优化）
      </Accordion>

      <Accordion title="索引失效的原因">
        **1. LIKE 左模糊匹配**：

        ```sql theme={null}
        -- ❌ 索引失效
        WHERE username LIKE '%keyword'

        -- ✅ 可以使用索引
        WHERE username LIKE 'keyword%'
        ```

        **2. 函数操作**：

        ```sql theme={null}
        -- ❌ 索引失效
        WHERE YEAR(created_at) = 2024

        -- ✅ 可以使用索引
        WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
        ```

        **3. 类型转换**：

        ```sql theme={null}
        -- ❌ 可能导致索引失效
        WHERE id = '123'  -- id 是 INT

        -- ✅ 正确
        WHERE id = 123
        ```

        **4. OR 条件**：

        ```sql theme={null}
        -- ❌ 可能导致索引失效
        WHERE username = 'Alice' OR email = 'alice@example.com'

        -- ✅ 可以使用索引（使用 UNION）
        SELECT * FROM users WHERE username = 'Alice'
        UNION
        SELECT * FROM users WHERE email = 'alice@example.com'
        ```
      </Accordion>

      <Accordion title="最左前缀原则">
        **什么是最左前缀原则？**

        复合索引按照字段顺序从左到右匹配。只有使用索引最左边的字段，索引才会生效。

        **示例**：

        ```sql theme={null}
        -- 创建复合索引
        CREATE INDEX idx_status_created ON users(status, created_at);

        -- ✅ 可以使用索引（使用最左边的字段）
        WHERE status = 1

        -- ✅ 可以使用索引（使用所有字段）
        WHERE status = 1 AND created_at > '2024-01-01'

        -- ❌ 索引失效（没有使用最左边的字段）
        WHERE created_at > '2024-01-01'
        ```

        **实际应用**：

        * 创建复合索引时，将最常用的字段放在最左边
        * 查询时尽量使用索引最左边的字段
      </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_index_opt.sql;
       ```
       或者直接复制粘贴 SQL 语句执行

    3. 查看索引：
       ```sql theme={null}
       SHOW INDEX FROM users;
       -- 应该看到 idx_username 和 idx_status_created 索引
       ```

    4. 分析查询：
       ```sql theme={null}
       EXPLAIN SELECT * FROM users WHERE username = 'Alice';
       -- 查看 type、key、rows 字段
       ```

    5. 对比性能：
       ```sql theme={null}
       -- 使用索引的查询
       EXPLAIN SELECT * FROM users WHERE username = 'Alice';

       -- 不使用索引的查询
       EXPLAIN SELECT * FROM users WHERE username LIKE '%lic';
       -- 对比 type 和 rows 的差异
       ```

    **常见错误**：

    <Warning>
      * ❌ `CREATE INDEX idx_username ON users(username)` - 语法错误，应该用 ALTER TABLE
      * ❌ `WHERE username LIKE '%keyword'` - 左模糊匹配，索引失效
      * ❌ `WHERE YEAR(created_at) = 2024` - 函数操作，索引失效
      * ✅ `ALTER TABLE users ADD INDEX idx_username (username)` - 正确
      * ✅ `WHERE username LIKE 'keyword%'` - 右模糊匹配，可以使用索引
      * ✅ `WHERE created_at >= '2024-01-01'` - 范围查询，可以使用索引
    </Warning>
  </Step>
</Steps>

***

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

<CardGroup cols={2}>
  <Card title="Shell 挑战" icon="terminal">
    **任务**：尝试用管道串联三剑客，编写一个脚本从日志文件中提取 IP 地址并统计访问次数。

    **提示**：

    * 使用 `grep` 过滤包含 IP 的行
    * 使用 `awk` 提取 IP 地址（通常是第一列）
    * 使用 `sort | uniq -c` 统计每个 IP 的访问次数
    * 思考：如何用 sed 格式化输出？
  </Card>

  <Card title="SQL 挑战" icon="database">
    **任务**：了解"最左前缀原则"，创建复合索引并测试不同的查询场景。

    **提示**：

    * 创建 `(status, created_at)` 复合索引
    * 测试 `WHERE status = 1`（应该使用索引）
    * 测试 `WHERE created_at > '2024-01-01'`（索引失效）
    * 思考：如何设计复合索引的顺序？
  </Card>
</CardGroup>

***

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

* `day04/04_log_analysis.sh` - Shell 日志分析脚本
* `day04/04_index_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-04">
    在 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">
    * **日志分析**：使用 grep 过滤错误日志，awk 提取关键信息
    * **配置管理**：使用 sed 批量修改配置文件
    * **数据提取**：从命令输出中提取特定字段
    * **文本处理**：格式化输出，生成报表
    * **故障排查**：快速定位问题，分析日志
  </Card>

  <Card title="SQL 索引在生产环境的优化" icon="database">
    * **查询优化**：为常用查询字段添加索引
    * **性能监控**：使用 EXPLAIN 分析慢查询
    * **索引设计**：根据查询模式设计合适的索引
    * **避免失效**：注意索引失效的情况，优化查询语句
    * **复合索引**：合理使用复合索引提高查询效率
  </Card>
</CardGroup>

<Tip>
  **与 Day 03 和 Day 05 的关联**：

  * Day 03 学习了管道和函数，今天学习了如何使用三剑客处理文本数据
  * Day 05 将学习更高级的运维脚本和 SQL 慢查询优化，今天的索引知识是基础
  * 掌握今天的三剑客和索引优化，明天的学习会更轻松
</Tip>

***

<CardGroup cols={2}>
  <Card title="上一天: 管道/函数" href="/training/30daystudy/day-03">
    Day 03 | Shell 管道与 SQL 联表查询
  </Card>

  <Card title="下一天: 运维实战" href="/training/30daystudy/day-05">
    Day 05 | Shell 脚本实战与 SQL 优化
  </Card>
</CardGroup>