广州中医药大学医学信息工程学院

广 州 中 医 药 大 学 医 学 信 息 工 程 学 院 实 验 报 告 课 程 名 称 : 网 络 数 据 库 编 程 专 业 班 级 : 计 算 机 科 学 与 技 术 ( 2012 ) 级 学 生 姓 名 : 张 鹏 燕 2012081076 薛 丽 梅 2012081080 杨 晓 珠 2012081018 翁 浩 彬 2012081007 实 验 名 称 : 数 据 库 性 能 优 化 实 验 成 绩 : 课 程 类 别 : 限 选 公 选 其 它

数 据 库 系 统 性 能 优 化 ( 基 于 MySQL 数 据 库, 采 用 一 定 的 查 询 优 化 方 案, 用 MySQL 的 内 部 数 据 说 明 优 化 前 与 优 化 后 CPU 的 情 况 ) 一 实 验 背 景 数 据 库 系 统 是 管 理 信 息 系 统 的 核 心, 基 于 数 据 库 的 联 机 事 务 处 理 (OLTP) 以 及 联 机 分 析 处 理 (OLAP) 是 银 行 企 业 政 府 等 部 门 最 为 重 要 的 计 算 机 应 用 之 一 从 大 多 数 系 统 的 应 用 实 例 来 看, 查 询 操 作 在 各 种 数 据 库 操 作 中 所 占 据 的 比 重 最 大, 而 查 询 操 作 所 基 于 的 SELECT 语 句 在 SQL 语 句 中 又 是 代 价 最 大 的 语 句 举 例 来 说, 如 果 数 据 的 量 积 累 到 一 定 的 程 度, 比 如 一 个 银 行 的 账 户 数 据 库 表 信 息 积 累 到 上 百 万 甚 至 上 千 万 条 记 录, 全 表 扫 描 一 次 往 往 需 要 数 十 分 钟, 甚 至 数 小 时 如 果 采 用 比 全 表 扫 描 更 好 的 查 询 策 略, 往 往 可 以 使 查 询 时 间 降 为 几 分 钟, 由 此 可 见 查 询 优 化 技 术 的 重 要 性 小 组 通 过 不 少 的 科 研 文 档 中 发 现, 许 多 程 序 员 在 利 用 一 些 前 端 数 据 库 开 发 工 具 ( 如 PowerBuilder Delphi 等 ) 开 发 数 据 库 应 用 程 序 时, 只 注 重 用 户 界 面 的 华 丽, 并 不 重 视 查 询 语 句 的 效 率 问 题, 导 致 所 开 发 出 来 的 应 用 系 统 效 率 低 下, 资 源 浪 费 严 重 因 此, 如 何 设 计 高 效 合 理 的 查 询 语 句 就 显 得 非 常 重 要 通 过 调 查 得 出 许 多 程 序 员 认 为 查 询 优 化 是 DBMS( 数 据 库 管 理 系 统 ) 的 任 务, 与 程 序 员 所 编 写 的 SQL 语 句 关 系 不 大, 这 是 错 误 的 一 个 好 的 查 询 计 划 往 往 可 以 使 程 序 性 能 提 高 数 十 倍 查 询 计 划 是 用 户 所 提 交 的 SQL 语 句 的 集 合, 查 询 规 划 是 经 过 优 化 处 理 之 后 所 产 生 的 语 句 集 合 本 实 验 以 应 用 实 例 为 基 础, 结 合 数 据 库 理 论, 介 绍 查 询 优 化 技 术 在 现 实 系 统 中 的 运 用 二 实 验 优 化 方 案 DBMS 处 理 查 询 计 划 的 过 程 是 这 样 的 : 在 做 完 查 询 语 句 的 词 法 语 法 检 查 之 后, 将 语 句 提 交 给 DBMS 的 查 询 优 化 器, 优 化 器 做 完 代 数 优 化 和 存 取 路 径 的 优 化 之 后, 由 预 编 译 模 块 对 语 句 进 行 处 理 并 生 成 查 询 规 划, 然 后 在 合 适 的 时 间 提 交 给 系 统 处 理 执 行, 最 后 将 执 行 结 果 返 回 给 用 户 在 实 际 的 数 据 库 产 品 ( 如 Oracle

Sybase 等 ) 的 高 版 本 中 都 是 采 用 基 于 代 价 的 优 化 方 法, 这 种 优 化 能 根 据 从 系 统 字 典 表 所 得 到 的 信 息 来 估 计 不 同 的 查 询 规 划 的 代 价, 然 后 选 择 一 个 较 优 的 规 划 虽 然 现 在 的 数 据 库 产 品 在 查 询 优 化 方 面 已 经 做 得 越 来 越 好, 但 由 用 户 提 交 的 SQL 语 句 是 系 统 优 化 的 基 础, 很 难 设 想 一 个 原 本 糟 糕 的 查 询 计 划 经 过 系 统 的 优 化 之 后 会 变 得 高 效, 因 此 用 户 所 写 语 句 的 优 劣 至 关 重 要 本 实 验 中, 系 统 所 做 查 询 优 化 我 们 暂 不 讨 论, 下 面 的 实 验 过 程 我 们 小 组 将 重 点 说 明 改 善 用 户 查 询 计 划 的 解 决 方 案 归 纳 总 结,MySQL 数 据 库 查 询 优 化 的 方 法 主 要 分 为 以 下 五 类 : 1) 使 用 索 引, CREATE INDEX MySQL 允 许 对 数 据 库 进 行 索 引, 以 此 能 迅 速 查 找 记 录, 从 而 无 需 一 开 始 就 扫 描 整 个 表, 由 此 显 著 的 加 快 查 询 速 度 每 个 表 最 多 可 以 做 到 16 个 索 引, 此 外 MySQL 还 支 持 多 列 索 引 及 全 文 检 索 2) 使 用 LIMIT 1 取 得 唯 一 行 此 方 法 可 以 用 于 查 询 数 据 量 较 少 的 数 据 表, 在 已 知 所 查 询 的 结 果 仅 有 一 条 记 录 时, 在 SELEST 语 句 条 件 下 加 上 LIMIT 1 限 制 条 件 可 以 直 接 加 快 查 询 速 度 3) 尽 量 少 使 用 SELECT * 语 句 中 的 通 配 符 *, 明 确 写 出 查 询 内 容 4) 调 整 内 部 变 量 MySQL 的 性 能 开 放, 因 而 用 户 可 以 轻 松 地 进 一 步 调 整 其 缺 省 设 置 以 获 得 更 优 的 性 能 及 稳 定 性 可 变 缺 省 设 置 值 : <1> 改 变 缓 冲 区 长 度 (key_buffer) <2> 改 变 表 长 (read_buffer_size) <3> 设 定 打 开 表 的 数 目 的 最 大 值 (table_cache) <4> 对 缓 长 查 询 设 定 一 个 时 间 限 制 (long_query_time) 5) 用 连 接 查 询 替 代 子 查 询 通 过 以 上 五 种 方 法 可 以 对 MySQL 数 据 库 的 查 询 操 作 作 出 优 化, 以 提 高 用 户 的 体 验 三 实 验 过 程 实 验 准 备 : 建 立 两 个 测 试 数 据 表 :city country( 如 下 ) 建 立 city 表 :

建 立 country 表 : 注 :show profile 是 由 Jeremy Cole 捐 献 给 MySQL 社 区 版 本 的 默 认 的 是 关 闭 的, 但 是 会 话 级 别 可 以 开 启 这 个 功 能 开 启 它 可 以 让 MySQL 收 集 在 执 行 语 句 的 时 候 所 使 用 的 资 源 为 了 统 计 报 表, 把 profiling 设 为 1 说 明 : 具 体 为 大 家 解 释 以 下 三 种 方 法, 说 明 MySQL 查 询 优 化 的 结 果 1. 建 立 索 引 MySQL 允 许 对 数 据 库 进 行 索 引, 以 此 能 迅 速 查 找 记 录, 从 而 无 需 一 开 始 就 扫 描 整 个 表, 由 此 显 著 的 加 快 查 询 速 度 每 个 表 最 多 可 以 做 到 16 个 索 引, 此 外 MySQL 还 支 持 多 列 索 引 及 全 文 检 索

例 : 查 询 city 表 中 的 Ottawa 的 信 息 www.51testing.com 优 化 前 : 语 句 :SELECT * FROM sakila.city where city = Ottawa ; 结 果 截 屏 : CPU 使 用 情 况 : Io 使 用 情 况 :

优 化 后 : 语 句 : alter table sakila.city add index(city); SELECT * FROM sakila.city where city = Ottawa ; 结 果 截 屏 : CPU 使 用 情 况 :

Io 使 用 情 况 : 优 化 前 后 对 比 :

2. 尽 量 不 使 用 通 配 符 * 尽 量 不 用 通 配 符 * 来 进 行 查 询 由 于 使 用 通 配 符 * 会 取 出 所 有 的 列, 加 大 了 数 据 库 的 工 作 量 所 以 优 化 的 原 则 为 : 需 要 哪 列 就 取 哪 列, 不 要 为 了 方 便 而 加 大 数 据 库 的 压 力 例 : 查 询 表 country 有 哪 些 国 家 优 化 前 : 语 句 :SELECT * FROM sakila.country; 结 果 截 屏 : CPU 使 用 情 况 :

Io 使 用 情 况 : 优 化 后 : 语 句 :SELECT country FROM sakila.country; 结 果 截 屏 :

CPU 使 用 情 况 : Io 使 用 情 况 :

优 化 前 后 执 行 时 间 对 比, 截 图 如 下 : 3. 用 连 接 查 询 替 代 子 查 询 因 为 子 查 询 会 多 次 遍 历 表 中 所 有 的 数 据 ( 视 你 的 子 查 询 的 层 次 而 定 ), 而 连 接 查 询 只 会 遍 历 一 次 例 : 查 找 中 国 的 所 有 城 市 的 信 息 优 化 前 : 语 句 :Select * from sakila.city where country_id in( Select country_id from sakila.country where country = China ); 结 果 截 屏 :

CPU 使 用 情 况 : Io 使 用 情 况 :

优 化 后 : 语 句 :Select * from sakila.city inner join( Select country_id from sakila.country where country ='China' )as country on city.country_id = country.country_id; 结 果 截 屏 : CPU 使 用 情 况 :

Io 使 用 情 况 : www.51testing.com

优 化 前 后 对 比 : 四 实 验 总 结 实 现 查 询 优 化 是 取 得 良 好 执 行 性 能 并 简 化 管 理 的 关 键 因 素 MySQL 查 询 不 是 一 个 无 序 的 查 询, 不 同 语 句 的 使 用 和 使 用 顺 序 将 直 接 影 响 其

查 询 速 度 凭 着 对 具 体 数 据 库 特 征 的 了 解, 对 MySQL 语 句 进 行 语 法 的 重 新 构 造, 开 发 人 员 能 够 帮 助 查 询 优 化 器 获 得 更 好 的 执 行 计 划, 而 这 些 执 行 计 划 是 查 询 优 化 器 无 法 靠 自 身 独 立 工 作 产 生 的 无 论 如 何 在 对 于 MySQL 数 据 库 查 询 优 化 仍 旧 是 一 个 可 探 究 的 课 题 市 面 上 不 同 的 公 司 采 取 共 享 资 源 的 方 法 提 高 程 序 员 对 数 据 库 本 身 开 发 水 平 的 提 高 但 是, 从 目 前 的 应 用 和 开 发 现 状 来 说, 如 何 像 软 件 辅 助 工 具 CASE 一 样 更 加 方 便 地 提 升 自 动 化 程 度, 简 化 MySQL 数 据 库 查 询 优 化 的 过 程 仍 然 是 一 个 要 点 问 题 在 另 一 方 面, 实 验 结 果 虽 未 明 显 显 示 实 验 过 程 中 为 系 统 增 加 的 负 荷, 但 我 们 作 为 开 发 人 员 需 明 确, 在 进 行 数 据 库 查 询 优 化 过 程 中, 应 当 衡 量 该 优 化 方 案 的 优 缺 点, 作 出 正 确 的 选 择 五 心 得 体 会 通 过 本 次 实 验, 我 们 感 受 到 CPU 运 行 时 间 的 微 妙 变 化, 认 识 到 时 间 的 微 妙 差 距, 从 而 意 识 到 数 据 库 作 出 微 小 的 调 整, 便 可 以 加 快 CPU 的 运 行 速 度, 从 更 深 的 层 面 认 识 开 发 语 句 的 魅 力 基 于 MySQL 数 据 库 的 查 询, 采 用 索 引 和 通 配 符 可 以 节 约 CPU 的 运 行 时 间 同 时, 小 组 间 的 合 作 与 交 流 成 为 实 验 高 效 完 成 的 关 键, 这 也 是 我 们 在 本 次 实 验 中 能 够 总 结 的 经 验 与 收 获