数据库原理DatabasePrincipl

An Introduction to Database System 数据库原理Database Principle仲恺农业技术学院计算机科学与工程学院 An Introduction to Database System 第 九 章 关 系 查 询 处 理 和 查 询 优 化9.1 概 述9.2 代 数 优 化9.3 物 理 优 化9.4 MSSQL优 化 之 执 行 计 划 9.5 小 结 An Introduction to Database System 9.1 概 述n 本 章 目 的 ： n RDBMS的 查 询 处 理 步 骤 n 查 询 优 化 的 概 念 n 基 本 方 法 和 技 术 n 查 询 优 化 分 类 ： n 代 数 优 化n 物 理 优 化 An Introduction to Database System 9.1 概 述 （ 续 ）n 9.1.1 查 询 处 理 步 骤n 9.1.2 实 现 查 询 操 作 的 算 法 示 例 An Introduction to Database System 9.1.1 查 询 处 理 步 骤n RDBMS查 询 处 理 阶 段 ： 1. 查 询 分 析2. 查 询 检 查3. 查 询 优 化 4. 查 询 执 行 An Introduction to Database System 查 询 处 理 步 骤 （ 续 ） 查 询 处 理 步 骤 An Introduction to Database System 1. 查 询 分 析n 对 查 询 语 句 进 行 扫 描 、 词 法 分 析 和 语 法分 析 n 从 查 询 语 句 中 识 别 出 语 言 符 号 n 进 行 语 法 检 查 和 语 法 分 析 An Introduction to Database System 2. 查 询 检 查 n 根 据 数 据 字 典 对 合 法 的 查 询 语 句 进 行 语 义 检 查 n 根 据 数 据 字 典 中 的 用 户 权 限 和 完 整 性 约 束 定 义 对 用 户 的 存取 权 限 进 行 检 查 n 检 查 通 过 后 把 SQL查 询 语 句 转 换 成 等 价 的 关 系 代 数 表 达 式 n RDBMS一 般 都 用 查 询 树 (语 法 分 析 树 )来 表 示 扩 展 的 关 系 代数 表 达 式 n 把 数 据 库 对 象 的 外 部 名 称 转 换 为 内 部 表 示 An Introduction to Database System 3. 查 询 优 化n 查 询 优 化 ： 选 择 一 个 高 效 执 行 的 查 询 处 理 策 略 n 查 询 优 化 分 类 ：n 代 数 优 化 ： 指 关 系 代 数 表 达 式 的 优 化 ， 改 变 操 作 的 次 序 和 组 合n 物 理 优 化 ： 指 存 取 路 径 和 底 层 操 作 算 法 的 选 择n 查 询 优 化 方 法 选 择 的 依 据 ： n 基 于 规 则 (rule based)n 基 于 代 价 (cost based)n 基 于 语 义 (semantic based) An Introduction to Database System 4. 查 询 执 行 n 依 据 优 化 器 得 到 的 执 行 策 略 生 成 查 询 计划n 代 码 生 成 器 (code generator)生 成 执 行 查询 计 划 的 代 码 An Introduction to Database System 9.1 关 系 数 据 库 系 统 的 查 询 处 理n 9.1.1 查 询 处 理 步 骤n 9.1.2 实 现 查 询 操 作 的 算 法 示 例 An Introduction to Database System 9.1.2 实 现 查 询 操 作 的 算 法 示 例 n 一 、 选 择 操 作 的 实 现 n 二 、 连 接 操 作 的 实 现 An Introduction to Database System 一 、 选 择 操 作 的 实 现 n 例 1 Select * from student where ；考 虑 的 几 种 情 况 ： C1： 无 条 件 ； C2： Sno 200215121； C3： Sage20； C4： Sdept CS AND Sage20； An Introduction to Database System 选 择 操 作 的 实 现 （ 续 ）n 选 择 操 作 典 型 实 现 方 法 ：n 1. 简 单 的 全 表 扫 描 方 法 对 查 询 的 基 本 表 顺 序 扫 描 ， 逐 一 检 查 每 个 元 组 是 否 满 足 选择 条 件 ， 把 满 足 条 件 的 元 组 作 为 结 果 输 出 适 合 小 表 ， 不 适 合 大 表 n 2. 索 引 (或 散 列 )扫 描 方 法 适 合 选 择 条 件 中 的 属 性 上 有 索 引 (例 如 B+树 索 引 或 Hash索引 ) 通 过 索 引 先 找 到 满 足 条 件 的 元 组 主 码 或 元 组 指 针 ， 再 通 过元 组 指 针 直 接 在 查 询 的 基 本 表 中 找 到 元 组 An Introduction to Database System 选 择 操 作 的 实 现 （ 续 ）n 例 1-C2 以 C2为 例 ， Sno 200215121， 并 且 Sno上 有 索 引(或 Sno是 散 列 码 )n 使 用 索 引 (或 散 列 )得 到 Sno为 200215121 元 组 的 指 针n 通 过 元 组 指 针 在 student表 中 检 索 到 该 学 生n 例 1-C3 以 C3为 例 ， Sage20， 并 且 Sage 上 有 B+树 索 引 n 使 用 B+树 索 引 找 到 Sage 20的 索 引 项 ， 以 此 为 入 口 点 在 B+树的 顺 序 集 上 得 到 Sage20的 所 有 元 组 指 针n 通 过 这 些 元 组 指 针 到 student表 中 检 索 到 所 有 年 龄 大 于 20的 学生 。 An Introduction to Database System 选 择 操 作 的 实 现 （ 续 ）n 例 1-C4 以 C4为 例 ， Sdept CS AND Sage20， 如 果Sdept和 Sage上 都 有 索 引 ：n 算 法 一 ： 分 别 用 上 面 两 种 方 法 分 别 找 到 Sdept CS的 一 组 元组 指 针 和 Sage20的 另 一 组 元 组 指 针 求 这 2组 指 针 的 交 集 到 student表 中 检 索 得 到 计 算 机 系 年 龄 大 于 20的 学 生n 算 法 二 ： 找 到 Sdept CS的 一 组 元 组 指 针 ， 通 过 这 些 元 组 指 针 到 student表 中 检 索 对 得 到 的 元 组 检 查 另 一 些 选 择 条 件 (如 Sage20)是 否 满 足 把 满 足 条 件 的 元 组 作 为 结 果 输 出 。 An Introduction to Database System 二 、 连 接 操 作 的 实 现 n 连 接 操 作 是 查 询 处 理 中 最 耗 时 的 操 作 之 一 n 本 节 只 讨 论 等 值 连 接 (或 自 然 连 接 )最 常 用 的 实 现 算 法 n 例 2 SELECT * FROM Student， SC WHERE Student.Sno=SC.Sno； An Introduction to Database System 连 接 操 作 的 实 现 （ 续 ）n 1. 嵌 套 循 环 方 法 (nested loop) n 2. 排 序 -合 并 方 法 (sort-merge join 或 merge join)n 3. 索 引 连 接 (index join)方 法 n 4. Hash Join方 法 An Introduction to Database System 连 接 操 作 的 实 现 （ 续 ）1. 嵌 套 循 环 方 法 (nested loop)n 对 外 层 循 环 (Student)的 每 一 个 元 组 (s)， 检索 内 层 循 环 (SC)中 的 每 一 个 元 组 (sc)n 检 查 这 两 个 元 组 在 连 接 属 性 (sno)上 是 否 相等 n 如 果 满 足 连 接 条 件 ， 则 串 接 后 作 为 结 果 输出 ， 直 到 外 层 循 环 表 中 的 元 组 处 理 完 为 止 An Introduction to Database System 连 接 操 作 的 实 现 （ 续 ）2. 排 序 -合 并 方 法 (sort-merge join 或 merge join) n 适 合 连 接 的 诸 表 已 经 排 好 序 的 情 况 n 排 序 合 并 连 接 方 法 的 步 骤 ： 如 果 连 接 的 表 没 有 排 好 序 ， 先 对 Student表 和 SC表 按连 接 属 性 Sno排 序 取 Student表 中 第 一 个 Sno， 依 次 扫 描 SC表 中 具 有 相同 Sno的 元 组 An Introduction to Database System 连 接 操 作 的 实 现 （ 续 ）200215121200215122200215123200215124. 200215121 1 92200215121 2 85200215121 3 88200215122 2 90200215122 3 80. 排 序 -合 并 连 接 方 法 示 意 图 An Introduction to Database System 连 接 操 作 的 实 现 （ 续 ）n 排 序 合 并 连 接 方 法 的 步 骤 （ 续 ） ： 当 扫 描 到 Sno不 相 同 的 第 一 个 SC元 组 时 ， 返 回Student表 扫 描 它 的 下 一 个 元 组 ， 再 扫 描 SC表 中具 有 相 同 Sno的 元 组 ， 把 它 们 连 接 起 来 重 复 上 述 步 骤 直 到 Student 表 扫 描 完 An Introduction to Database System 连 接 操 作 的 实 现 （ 续 ）n Student表 和 SC表 都 只 要 扫 描 一 遍n 如 果 2个 表 原 来 无 序 ， 执 行 时 间 要 加 上 对 两 个表 的 排 序 时 间n 对 于 2个 大 表 ， 先 排 序 后 使 用 sort-merge join方法 执 行 连 接 ， 总 的 时 间 一 般 仍 会 大 大 减 少 An Introduction to Database System 连 接 操 作 的 实 现 （ 续 ）3. 索 引 连 接 (index join)方 法n 步 骤 ： 在 SC表 上 建 立 属 性 Sno的 索 引 ， 如 果 原 来 没 有 该 索引 对 Student中 每 一 个 元 组 ， 由 Sno值 通 过 SC的 索 引查 找 相 应 的 SC元 组 把 这 些 SC元 组 和 Student元 组 连 接 起 来 循 环 执 行 ， 直 到 Student表 中 的 元 组 处 理 完 为 止 An Introduction to Database System 连 接 操 作 的 实 现 （ 续 ）4. Hash Join方 法 n 把 连 接 属 性 作 为 hash码 ， 用 同 一 个 hash函 数 把 R和 S中 的 元 组 散 列 到 同 一 个hash文 件 中n 步 骤 ：n 划 分 阶 段 (partitioning phase)：n 对 包 含 较 少 元 组 的 表 (比 如 R)进 行 一 遍 处 理 n 把 它 的 元 组 按 hash函 数 分 散 到 hash表 的 桶 中n 试 探 阶 段 (probing phase)： 也 称 为 连 接 阶 段 (join phase) n 对 另 一 个 表 (S)进 行 一 遍 处 理n 把 S的 元 组 散 列 到 适 当 的 hash桶 中n 把 元 组 与 桶 中 所 有 来 自 R并 与 之 相 匹 配 的 元 组 连 接 起 来 An Introduction to Database System 连 接 操 作 的 实 现 （ 续 ）n 上 面 hash join算 法 前 提 ： 假 设 两 个 表 中较 小 的 表 在 第 一 阶 段 后 可 以 完 全 放 入 内存 的 hash桶 中 n 以 上 的 算 法 思 想 可 以 推 广 到 更 加 一 般 的多 个 表 的 连 接 算 法 上 An Introduction to Database System 优化的一般步骤 1 把 查 询 转 换 成 某 种 内 部 表 示2 代 数 优 化 ： 把 语 法 树 转 换 成 标 准 （ 优 化 ）形 式3 物 理 优 化 ： 选 择 低 层 的 存 取 路 径4 生 成 执 行 计 划 ， 选 择 代 价 最 小 的 An Introduction to Database System 第 九 章 关 系 查 询 处 理 和 查 询 优 化9.1 概 述9.2 代 数 优 化9.3 物 理 优 化9.4 MSSQL优 化 之 执 行 计 划 9.5 小 结 An Introduction to Database System 9.2 代 数 优 化n 9.2.1 关 系 代 数 表 达 式 等 价 变 换 规 则 n 9.2.2 查 询 树 的 启 发 式 优 化 An Introduction to Database System 9.2.1 关 系 代 数 表 达 式 等 价 变 换 规 则 n 代 数 优 化 策 略 ： 通 过 对 关 系 代 数 表 达 式的 等 价 变 换 来 提 高 查 询 效 率 n 关 系 代 数 表 达 式 的 等 价 ： 指 用 相 同 的 关系 代 替 两 个 表 达 式 中 相 应 的 关 系 所 得 到的 结 果 是 相 同 的n 两 个 关 系 表 达 式 E1和 E2是 等 价 的 ， 可 记 为E1E2 An Introduction to Database System 9.2.1 关 系 代 数 表 达 式 等 价 变 换 规 则 （ 续 ）1)2)3)4)5) An Introduction to Database System 常用的等价变换规则：1. 连 接 、 笛 卡 尔 积 交 换 律 设 E1和 E2是 关 系 代 数 表 达 式 ， F是 连 接 运 算 的 条 件 ， 则 有 E1 E2E2 E1 E1 E2E2 E1 E1 E2E2 E12. 连 接 、 笛 卡 尔 积 的 结 合 律 设 E 1， E2， E3是 关 系 代 数 表 达 式 ， F1和 F2是 连 接 运 算 的 条 件 ， 则 有 (E1 E2) E3E1 (E2 E3) (E1 E2) E3E1 (E2 E3) (E1 E2) E3E1 (E2 E3) An Introduction to Database System 关 系 代 数 表 达 式 等 价 变 换 规 则 （ 续 ）3. 投 影 的 串 接 定 律 ( (E) (E)这 里 ， E是 关 系 代 数 表 达 式 ， Ai(i=1， 2， ， n)，Bj(j=1， 2， ， m)是 属 性 名 且 A1， A2， ， An构 成B1， B2， ， Bm的 子 集 。4. 选 择 的 串 接 定 律 ( (E) (E)这 里 ， E是 关 系 代 数 表 达 式 ， F 1、 F2是 选 择 条 件 。 选 择 的 串 接 律 说 明 选 择 条 件 可 以 合 并 。 这 样 一 次 就 可检 查 全 部 条 件 。nAAA , 21 mBBB , 21 nAAA , 211F 2F 21 FF An Introduction to Database System 关 系 代 数 表 达 式 等 价 变 换 规 则 （ 续 ）5. 选 择 与 投 影 操 作 的 交 换 律 F( (E) (F(E)选 择 条 件 F只 涉 及 属 性 A1， ， An。若 F中 有 不 属 于 A1， ， An的 属 性 B1， ，Bm则 有 更 一 般 的 规 则 ： ( F(E) (F( (E)nAAA , 21 nAAA , 21nAAA , 21 nAAA , 21mn BBBAAA , 2121 An Introduction to Database System 关 系 代 数 表 达 式 等 价 变 换 规 则 （ 续 ）6. 选 择 与 笛 卡 尔 积 的 交 换 律如 果 F中 涉 及 的 属 性 都 是 E1中 的 属 性 ， 则 (E1 E2) (E1) E2如 果 F=F1 F2， 并 且 F1只 涉 及 E1中 的 属 性 ， F2只 涉 及 E2中 的 属 性 ， 则 由 上 面 的 等 价 变 换 规 则 1， 4， 6可 推 出 ： (E1 E2) (E1) (E2)若 F1只 涉 及 E1中 的 属 性 ， F2涉 及 E1和 E2两 者 的 属 性 ， 则仍 有 (E 1 E2) ( (E1) E2)它 使 部 分 选 择 在 笛 卡 尔 积 前 先 做 。 1F 2F2F 1FF FFF An Introduction to Database System 关 系 代 数 表 达 式 等 价 变 换 规 则 （ 续 ）7. 选 择 与 并 的 分 配 律设 E=E1 E2， E1， E2有 相 同 的 属 性 名 ， 则 F(E1 E2)F(E1) F(E2)8. 选 择 与 差 运 算 的 分 配 律若 E1与 E2有 相 同 的 属 性 名 ， 则 F(E1-E2)F(E1)-F(E2)9. 选 择 对 自 然 连 接 的 分 配 律 F(E1 E2)F(E1) F(E2) F只 涉 及 E1与 E2的 公 共 属 性 An Introduction to Database System 关 系 代 数 表 达 式 等 价 变 换 规 则 （ 续 ）10. 投 影 与 笛 卡 尔 积 的 分 配 律设 E1和 E2是 两 个 关 系 表 达 式 ， A1， ， An是 E1的 属 性 ，B1， ， Bm是 E2的 属 性 ， 则 (E1 E2) (E1) (E2)11. 投 影 与 并 的 分 配 律设 E 1和 E2有 相 同 的 属 性 名 ， 则 (E1 E2) (E1) (E2)mn BBBAAA , 2121 nAAA , 21 mBBB , 21 nAAA , 21 nAAA , 21 nAAA , 21 An Introduction to Database System 9.2 代 数 优 化n 9.2.1 关 系 代 数 表 达 式 等 价 变 换 规 则 n 9.2.2 查 询 树 的 启 发 式 优 化 An Introduction to Database System 典型的启发式规则：n 选 择 运 算 应 尽 可 能 先 做 n 目 的 ： 减 小 中 间 关 系n 在 执 行 连 接 操 作 前 对 关 系 适 当 进 行 预 处 理n 按 连 接 属 性 排 序n 在 连 接 属 性 上 建 立 索 引 n 投 影 运 算 和 选 择 运 算 同 时 做n 目 的 ： 避 免 重 复 扫 描 关 系 n 将 投 影 运 算 与 其 前 面 或 后 面 的 双 目 运 算 结 合n 目 的 ： 减 少 扫 描 关 系 的 遍 数 An Introduction to Database System 查询优化的一般准则 （续）n 某 些 选 择 运 算 在 其 前 面 执 行 的 笛 卡 尔 积 = 连 接 运 算 例 ： Student.Sno=SC.Sno (Student SC) Student SCn 提 取 公 共 子 表 达 式 当 查 询 的 是 视 图 时 ， 定 义 视 图 的 表 达 式 就 是 公 共 子 表 达 式 的 情 况 如 果 这 种 重 复 出 现 的 子 表 达 式 的 结 果 不 是 很 大 的 关 系 并 且 从 外 存 中读 入 这 个 关 系 比 计 算 该 子 表 达 式 的 时 间 少 得 多 ， 则 先 计 算 一 次 公 共子 表 达 式 并 把 结 果 写 入 中 间 文 件 是 合 算 的 An Introduction to Database System 查 询 树 的 启 发 式 优 化 （ 续 ）n 遵 循 这 些 启 发 式 规 则 ， 应 用 9.2.1的 等 价 变 换 公 式 来 优化 关 系 表 达 式 的 算 法 。算 法 ： 关 系 表 达 式 的 优 化输 入 ： 一 个 关 系 表 达 式 的 查 询 树输 出 ： 优 化 的 查 询 树方 法 ：(1) 利 用 等 价 变 换 规 则 4把 形 如 F1 F2 Fn(E)变 换 为 F1(F2(Fn(E)。(2) 对 每 一 个 选 择 ， 利 用 等 价 变 换 规 则 4 9尽 可 能 把 它移 到 树 的 叶 端 。 An Introduction to Database System 查 询 树 的 启 发 式 优 化 （ 续 ）(3) 对 每 一 个 投 影 利 用 等 价 变 换 规 则 3， 5， 10， 11中的 一 般 形 式 尽 可 能 把 它 移 向 树 的 叶 端 。n 注 意 ： 等 价 变 换 规 则 3使 一 些 投 影 消 失 规 则 5把 一 个 投 影 分 裂 为 两 个 ， 其 中 一 个 有 可 能被 移 向 树 的 叶 端 (4) 利 用 等 价 变 换 规 则 3 5把 选 择 和 投 影 的 串 接 合 并 成单 个 选 择 、 单 个 投 影 或 一 个 选 择 后 跟 一 个 投 影 。 使 多个 选 择 或 投 影 能 同 时 执 行 ， 或 在 一 次 扫 描 中 全 部 完 成 An Introduction to Database System 查 询 树 的 启 发 式 优 化 （ 续 ） (5) 把 上 述 得 到 的 语 法 树 的 内 节 点 分 组 。 每 一 双 目 运算 ( ， ， ， -)和 它 所 有 的 直 接 祖 先 为 一 组 (这 些 直接 祖 先 是 (， 运 算 )。n 如 果 其 后 代 直 到 叶 子 全 是 单 目 运 算 ， 则 也 将 它 们 并入 该 组 n 但 当 双 目 运 算 是 笛 卡 尔 积 ( )， 而 且 后 面 不 是 与 它组 成 等 值 连 接 的 选 择 时 ， 则 不 能 把 选 择 与 这 个 双 目运 算 组 成 同 一 组 ， 把 这 些 单 目 运 算 单 独 分 为 一 组 An Introduction to Database System 优化的一般步骤 (续)例 2： 求 选 修 了 课 程 2的 学 生 姓 名SELECT Student.SnameFROM Student, SCWHERE Student.Sno=SC.SnoAND SC.Cno=2; An Introduction to Database System (1) 把SQL语句转换成查询树结 果project(Sname) select(SC.Cno=2) join(Student.Sno=SC.Sno) Student SC查 询 树 An Introduction to Database System 为了使用关系代数表达式的优化法，假设内部表示是关系代数语法树，则上面的查询树如下图所示。Sname SC.Cno=2 Student.Sno=SC.S Student SC关 系 代 数 语 法 树 An Introduction to Database System (2) 对查询树进行优化利 用 优 化 算 法 把 语 法 树 转 换 成 标 准 （ 优 化 ） 形 式 Sname Student.Sno=SC.Sno SC.Cno=2 Student SC优 化 后 的 查 询 树 An Introduction to Database System n 例 3： An Introduction to Database System n 优 化 后 的 查 询 树 An Introduction to Database System An Introduction to Database System 优化 An Introduction to Database System 第 九 章 关 系 查 询 处 理 和 查 询 优 化9.1 概 述9.2 代 数 优 化9.3 物 理 优 化9.4 MSSQL优 化 之 执 行 计 划 9.5 小 结 An Introduction to Database System 9.3 物 理 优 化n 代 数 优 化 改 变 查 询 语 句 中 操 作 的 次 序 和 组 合 ，不 涉 及 底 层 的 存 取 路 径n 对 于 一 个 查 询 语 句 有 许 多 存 取 方 案 ， 它 们 的 执行 效 率 不 同 ， 仅 仅 进 行 代 数 优 化 是 不 够 的 n 代 数 优 化 比 较 粗 糙 ,而 存 储 路 径 优 化 效 果 明 显n 物 理 优 化 就 是 要 选 择 高 效 合 理 的 操 作 算 法 或 存取 路 径 ， 求 得 优 化 的 查 询 计 划 An Introduction to Database System 存 取 路 径 选 择 优 化n 一 、 选 择 操 作n 二 、 连 接 操 作 An Introduction to Database System 存 取 路 径 选 择 优 化n 一 、 选 择 操 作 An Introduction to Database System 存 取 路 径 选 择 优 化 (续 ) An Introduction to Database System 存 取 路 径 选 择 优 化 (续 )n 二 、 连 接 操 作n 1. 如 果 2个 表 都 已 经 按 照 连 接 属 性 排 序n 选 用 排 序 -合 并 方 法2. 如 果 一 个 表 在 连 接 属 性 上 有 索 引n 选 用 索 引 连 接 方 法3. 如 果 上 面 2个 规 则 都 不 适 用 ， 其 中 一 个 表 较 小n 选 用 Hash join方 法 An Introduction to Database System 第 九 章 关 系 查 询 处 理 和 查 询 优 化9.1 概 述9.2 代 数 优 化9.3 物 理 优 化9.4 MSSQL优 化 之 执 行 计 划 9.5 小 结 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划n 谈 到 优 化 就 必 然 要 涉 及 索 引 ， 就 像 要 讲锁 必 然 要 说 事 务 一 样n 探 索 MSSQL的 执 行 计 划 ， 来 让 大 家 知 道如 何 查 看 MSSQL的 优 化 机 制 ， 以 此 来 优化 SQL查 询 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）例 4 MSSQL优 化1. 新 建 测 试 表 T_UserInfo 2. 插 入 100条 数 据 演 示 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）3. 查 询 SELECT * FROM T_UserInfo WHERE USERID=ABCDE6EF 4. Ctrl+L查 看 执 行 计 划 这 就 是 MSSQL的 执 行计 划 ：表 扫 描 ： 扫 描 表 中 的 行 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）5. 查 看 该 语 句 对 IO的 读 写 执 行 :SET STATISTICS IO ON 此 时 再 执 行 该 SQL:SELECT * FROM T_UserInfo WHERE USERID=ABCDE6EF 四 个 值 分 别 为 ： 执 行 的 扫 描 次 数 ; 从 数 据 缓 存 读 取 的 页 数 ; 从 磁 盘 读 取 的 页 数 ; 为 进 行 查 询 而 放 入 缓 存 的 页 数 ; An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）注 意 ： 如 果 对 于 一 个 SQL查 询 有 多 种 写 法 ，那 么 这 四 个 值 中 的 逻 辑 读 (logical reads)决 定 了 哪 个 是 最 优 化 的 。 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）6. 接 下 来 我 们 为 其 建 一 个 聚 集 索 引 CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)7. 查 询 SELECT * FROM T_UserInfo WHERE USERID=ABCDE6EF8. Ctrl+L查 看 执 行 计 划 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）9. 注 意 : 此 时 逻 辑 读 由 原 来 的 1变 成 2， 说 明 我 们 又 加了 一 个 索 引 页 ， 现 在 我 们 查 询 时 ， 逻 辑 读 就 是 要 读两 页 (1索 引 页 +1数 据 页 )， 此 时 的 效 率 还 不 如 不 建索 引 。 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）10. 测 试 数 据 改 变 成 1000条 11. 再 执 行 SET STATISTICS IO ON,再 执 行 SELECT * FROM T_UserInfo WHERE USERID=ABCDE6EF在 不 加 聚 集 索 引 的 情 况 下 ：表 T_UserInfo。 扫 描 计 数 1， 逻 辑 读 10 次 ， 物 理 读 0 次 ， 预 读 0 次 。在 加 聚 集 索 引 的 情 况 下 ： CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)表 T_UserInfo。 扫 描 计 数 1， 逻 辑 读 2 次 ， 物 理 读 0 次 ， 预 读 0 次 。(其 实 也 就 是 说 此 时 是 读 了 一 个 索 引 页 ， 一 个 数 据 页 )在 加 索 引 的 情 况 下 ： CREATE INDEX INDEX_Userid ON T_UserInfo (Userid)表 T_UserInfo。 扫 描 计 数 1， 逻 辑 读 3 次 ， 物 理 读 0 次 ， 预 读 0 次据 此 ， 在 数 据 量 稍 大 时 ， 索 引 的 查 询 优 势 就 显 示 出 来 了 。 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）先 小 结 下 ： 当 你 构 建 SQL语 句 时 ， 按 Ctrl+L就 可 以 看 到 语句 是 如 何 执 行 ， 是 用 索 引 扫 描 还 是 表 扫 描 ？ 通 过 SET STATISTICS IO ON 来 查 看 逻 辑 读 ，完 成 同 一 功 能 的 不 同 SQL语 句 ， 逻 辑 读 越 小 查 询 速 度 越 快 (当 然 不 要 找 那 个 只 有 几 百条 记 录 的 例 子 )。 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）n -相 关 SQL语 句 解 释n -n -建 聚 集 索 引n CREATE CLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid)n -建 非 聚 集 索 引n CREATE NONCLUSTERED INDEX INDEX_Userid ON T_UserInfo (Userid) n -删 除 索 引n DROP INDEX T_UserInfo.INDEX_Userid An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ）n -显 示 有 关 由 Transact-SQL 语 句 生 成 的 磁 盘 活 动 量 的 信 息n SET STATISTICS IO ONn -关 闭 有 关 由 Transact-SQL 语 句 生 成 的 磁 盘 活 动 量 的 信 息n SET STATISTICS IO OFFn -显 示 返 回 有 关 语 句 执 行 情 况 的 详 细 信 息 ， 并 估 计 语 句 对 资 源 的需 求 n SET SHOWPLAN_ALL ON n -关 闭 返 回 有 关 语 句 执 行 情 况 的 详 细 信 息 ， 并 估 计 语 句 对 资 源 的需 求 n SET SHOWPLAN_ALL OFFn -n 请 记 住 ： SET STATISTICS IO 和 SET SHOWPLAN_ALL 是 互斥 的 。 An Introduction to Database System 9.4 MSSQL优 化 之 执 行 计 划 （ 续 ） ? SELECT * FROM T_UserInfo WHERE USERID LIKE ABCDE8% An Introduction to Database System 第 九 章 关 系 查 询 处 理 和 查 询 优 化9.1 概 述9.2 代 数 优 化9.3 物 理 优 化9.4 MSSQL优 化 之 执 行 计 划 9.5 小 结 An Introduction to Database System 9.5 小 结n 查 询 处 理 是 RDBMS的 核 心 ， 查 询 优 化 技术 是 查 询 处 理 的 关 键 技 术 n 本 章 讲 解 的 优 化 方 法 n 启 发 式 代 数 优 化n 基 于 规 则 的 存 取 路 径 优 化n 基 于 代 价 的 优 化n 本 章 的 目 的 ： 希 望 读 者 掌 握 查 询 优 化 方法 的 概 念 和 技 术 An Introduction to Database System 小 结 （ 续 ）n 比 较 复 杂 的 查 询 ， 尤 其 是 涉 及 连 接 和 嵌 套 的 查 询n 不 要 把 优 化 的 任 务 全 部 放 在 RDBMS上n 应 该 找 出 RDBMS的 优 化 规 律 ， 以 写 出 适 合 RDBMS自动 优 化 的 SQL语 句 n 对 于 RDBMS不 能 优 化 的 查 询 需 要 重 写 查 询 语 句 ， 进 行手 工 调 整 以 优 化 性 能 An Introduction to Database System n 作 业 ： 275 2