《数据库索引设计优化》读书笔记（五）

第8章 为表连接设计索引

练习

8.1 评估图8.25 中所示连接的响应时间，过滤因子使用给定的值。

分析：

        A 为父表，B 为子表，两个表做主外键关联查询，只有主键和外键上有索引，并且 A 表的主键索引和 B 表的外键索引为聚簇索引。

        以 A 作为外层表做嵌套循环连接计算响应时间：

1. 通过聚簇索引 AK 访问 A 表

   索引 AK TR = 10000000 * 1% = 100000 TS = 10000000
   LTR 100000 * 10ms + 10000000 * 0.01ms = 1000 + 100 = 1100s

2. 通过聚簇索引 AK 访问 B 表

   # 每次循环：索引高度=3 → 3 I/O ≈ 0.3ms，再读取数据（5行 × 0.01ms）
   LTR 100000 * (0.3ms + 5 * 0.01ms) = 100000 × 0.35ms = 35s

3. 提取数据

   # 实际匹配行数
   100000 (A) * 5 (B匹配) % 0.001% (b1过滤) = 5行
   # 提取时间（可忽略）
   5 * 0.1ms = 0.5ms

• TR：Tuple Read（元组读取），表示需要读取的实际数据行数。
• TS：Tuple Scan（元组扫描），表示需要扫描的索引条目数。
• LTR：Logical Read Time（逻辑读取时间），估算的读取时间。

        所以以 A 作为外层表做嵌套循环连接响应时间约为 1135（1100 + 35）秒

        以 B 作为外层表做嵌套循环连接计算响应时间：

1. 通过聚簇索引 AK 访问 B 表

   索引 AK TR = 50000000 * 0.001% = 500 TS = 50000000
   LTR 500 * 10ms + 50000000 * 0.01ms = 505s

2. 通过聚簇索引 AK 访问 A 表

   # A.AK 是主键，每次查找仅需三次 I/O（索引高度）→ 3 * 0.1ms/次。
   # 总时间
   500 × 0.3ms = 150ms = 0.15s

3. 提取数据

   # 实际匹配行数
   100000 (A) * 5 (B匹配) % 0.001% (b1过滤) = 5行
   # 提取时间（可忽略）
   5 * 0.1ms = 0.5ms

        所以以 B 作为外层表做嵌套循环连接响应时间约为 505 秒

8.2 在不添加冗余字段的前提下，为该连接设计最佳索引并评估响应时间。

分析：

        因为 B1 > :B1 的 FF 很小，仅为 0.001%，所以可以建立以 B1 为前缀的宽索引（B1,A2,B2）。以 B 作为外层表做嵌套循环连接计算响应时间：

1. 通过索引 B1 访问 B 表，因为 B1 是宽索引，所以无需回表访问

   索引范围扫描 b1 > :b1：
   扫描行数 = 50000000 * 0.001% = 500行。
   时间：500 × 0.01ms（索引扫描） = 5ms（无需回表！）。

2. 通过聚簇索引 AK 访问 A 表

   每次查找：A.AK 是主键，B.AK 是外键，因此每个 B.AK 在 A 表中有且仅有 1 行匹配。
   检查该行是否满足 a1=:a1（FF=1%），即：有1%概率返回该行（满足条件）；有99%概率不返回（不满足条件）。
   实际返回行数计算
   总匹配行数 = 500 (B) × 1% (A) = 5行
   
   单次查找成本：
   主键查找：3 I/O ≈ 0.3ms（无论是否满足 a1=:a1 都需执行）
   数据检查：a1=:a1 条件判断 ≈ 0.01ms（可忽略）。
   总单次时间：0.3ms。
   
   500 次查找：500 × 0.3ms = 150ms = 0.15s。

3. 提取数据

   # 实际匹配行数
   100000 (A) * 5 (B匹配) % 0.001% (b1过滤) = 5行
   # 提取时间（可忽略）
   5 * 0.1ms = 0.5ms

        所以使用 B1 上的宽索引（B1,A2,B2），以 B 作为外层表做嵌套循环连接响应时间约为 155.5 ms。

        关键优化点：

• 覆盖索引（Covering Index）：索引包含所有查询字段（B1, A2, B2），避免回表操作，将 B 表扫描时间从秒级降至毫秒级。
• 索引列顺序：B1 作为第一列，高效支持范围查询 B1 > :B1。
• 连接优化：仍依赖 A 表主键索引，但循环次数少（500次），耗时可控。

        进一步优化：

1. 为 A 表的 A1 创建非聚簇索引
2. 使用 Hash Join

   SELECT /*+ HASH_JOIN(a b) */ a2, b1, b2 
   FROM a, b 
   WHERE a1=:a1 AND b1>:b1 AND a.ak=b.ak;

        经过进一步优化的响应时间计算：

1. B 表扫描（覆盖索引）

   通过 (b1, a2, b2) 索引直接获取 b1 > :b1 的 500 行，无需回表（所有字段已在索引中）。
   时间：500 × 0.01ms = 5ms。

2. A 表访问（利用 A1 的索引）

   （1）通过 A1 索引快速定位
   直接定位 A1 = :A1 的 100000 行（FF=1%）
   索引高度=3 → 3 I/O ≈ 0.3ms
   时间：0.3ms（无需扫描全表）
   
   （2）与 B 表记录的 AK 匹配
   将 A 表的 100000个 AK 加载到内存哈希表（耗时约 10ms）
   对 B 表的 500 行 B.AK 进行哈希探测（500 × 0.001ms ≈ 0.5ms）

3. 数据组合与返回

   匹配行数 = 500 (B) * (100000/10000000) = 5行。
   提取时间：5 * 0.1ms = 0.5ms。

        总响应时间为：5 + 0.3 + 10 + 0.5 + 0.5 = 16.3ms，又快了一个数量级。

8.3 CUST 表中有三个指向代码表的外键。评估在嵌套循环和最佳表访问顺序下，下述这四表连接的本地响应时间（参见图8.26）

分析：

        根据四个表的记录数，最佳表访问顺序为 C1->CUST->C3->C2（先过滤小表，立即减少大表的数据量，再逐步过滤）。

1. 1. 访问 C1 表（驱动表）

   聚簇索引全扫描：
   扫描：1000行 × 0.01ms = 10ms
   读取：1000行 × 10ms = 10000ms(10s)
   时间：10.01s

2. 连接 CUST 表

   使用 C1PK 外键索引：
   1000 次索引查找 * 0.3ms = 300ms
   读取匹配行：(1000 * 1000) * 0.01ms=10s
   时间：10.3s

3. 连接 C3 表

   使用 C3PK 外键索引：
   3000 次索引查找 * 0.3ms = 300ms
   时间：3000 * 0.3ms = 900ms (0.9s)

4. 连接 C2 表

   使用 C2PK 索引：
   10000次查找 * 0.3ms = 3000ms
   时间：3s

5. 提取数据

   结果行数 = CUST行数 / (C1行数 * C3行数 * C2行数) * CUST行数
            = 1000000 / (1000 * 3000 * 10000) * 1000000
   		 = 33
   时间：33 行 * 0.1ms = 3.3ms

        本地响应时间约为 10.010 + 10.300 + 0.9 + 3  = 24.21s

8.4 假设SQL 8.19 在一个批任务中会被执行一百万次。需要改进索引么？调优的空间有多大？有其它方法可以用来提升该 SELECT 语句的性能吗？

-- SQL 8.19
SELECT CNAME, C1TEXT, C2TEXT, C3TEXT
  FROM CUST, C1, C2, C3
 WHERE CUST.CNO = :CNO
   AND CUST.C1PK = C1.C1PK
   AND CUST.C2PK = C2.C2PK
   AND CUST.C3PK = C3.C3PK

分析：

1. 创建覆盖索引（核心优化）

   ALTER TABLE CUST ADD INDEX idx_cust_cover (CNO, C1PK, C2PK, C3PK, CNAME);

   覆盖索引优化（CUST表）
   节省时间 = 原始 CUST 表访问时间 * 回表消除效率
                  = (总时间 * CUST 表时间占比) * 70%
                  = 24.21s * 40% * 70% 
                  = 6.78s

   说明：
   . 假设 CUST 表访问占 40%（基于典型执行计划分析）
   . 覆盖索引消除 70% 回表 I/O（实测值范围通常为 60-80%）

2. 维度表索引优化

   -- 对文本字段创建覆盖索引
   ALTER TABLE C1 ADD INDEX idx_c1_cover (C1PK, C1TEXT);
   ALTER TABLE C2 ADD INDEX idx_c2_cover (C2PK, C2TEXT);
   ALTER TABLE C3 ADD INDEX idx_c3_cover (C3PK, C3TEXT);

   维度表覆盖索引优化（C1/C2/C3）
   总节省 = Σ(维度表行数 * 单行回表时间 * 查询次数比例)

   C1表节省 = 1000行 × 0.5ms × 100% = 0.5s
   C2表节省 = 10000行 × 0.2ms × 100% = 2.0s  
   C3表节省 = 3000行 × 0.3ms × 100% = 0.9s
   --------------------------
   总计节省 = 0.5 + 2.0 + 0.9 = 3.4s

   说明：
   . 单行回表时间：根据行大小估算
   . 查询次数比例：100% 表示每次查询都访问这些表

3. 使用强制索引

   SELECT C.CNAME, C1.C1TEXT, C2.C2TEXT, C3.C3TEXT
   FROM CUST FORCE INDEX (idx_cust_cover)
   JOIN C1 FORCE INDEX (idx_c1_cover) ON C.C1PK = C1.C1PK
   JOIN C2 FORCE INDEX (idx_c2_cover) ON C.C2PK = C2.C2PK
   JOIN C3 FORCE INDEX (idx_c3_cover) ON C.C3PK = C3.C3PK
   WHERE C.CNO = ?;

   强制索引优化
   节省时间 = 总时间 × 中间结果集减少比例
                  = 24.21s × 15%
                  = 3.63s

   说明：
   优化后中间结果集减少 15%（经验值）
   通过强制 CUST→C1→C2→C3 顺序避免笛卡尔积膨胀

         优化后的一次执行时间约为：24.21 - 6.78 - 3.4 - 3.63 = 10.4s

        其它优化措施（MySQL 特有）：

• 使用预处理语句

  PREPARE stmt FROM '
      SELECT C.CNAME, C1.C1TEXT, C2.C2TEXT, C3.C3TEXT
      FROM CUST FORCE INDEX (idx_cust_cover)
      JOIN C1 FORCE INDEX (idx_c1_cover) ON C.C1PK = C1.C1PK
      JOIN C2 FORCE INDEX (idx_c2_cover) ON C.C2PK = C2.C2PK
      JOIN C3 FORCE INDEX (idx_c3_cover) ON C.C3PK = C3.C3PK
      WHERE C.CNO = ?;
  ';
  SET @cno = 1001;
  EXECUTE stmt USING @cno;
  -- 重复使用...
  DEALLOCATE PREPARE stmt;

• 调整会话参数

  -- 增大排序缓冲区
  SET SESSION sort_buffer_size = 4M;
  
  -- 启用查询缓存（MySQL 8.0前）
  SET SESSION query_cache_type = 1;