分 享 技 术 分 享 快 乐 葛 云 杰 (Fiyo) 电 话 :18660746900 邮 箱 :wfgyj@126.com
关 于 我 姓 名 : 葛 云 杰 网 名 :Dylan Oracle OCM Oracle 用 户 组 年 轻 专 家 中 国 OCM 联 盟 成 员 山 东 Oracle 用 户 组 联 合 创 始 人 齐 鲁 IT 联 盟 联 合 创 始 人 软 件 研 发 工 程 师 具 有 十 年 以 上 软 件 研 发 / 设 计 经 验 参 与 了 众 多 软 件 研 发 系 统 架 构 设 计 数 据 库 运 维 项 目, 积 累 了 丰 富 的 经 验
开 发 人 员 : 如 果 你 是 做 数 据 库 开 发, 那 本 文 的 内 容 非 常 适 合, 因 为 本 文 是 从 程 序 员 的 角 度 来 谈 数 据 库 性 能 优 化 架 构 师 : 如 果 你 已 经 是 数 据 库 应 用 的 架 构 师, 那 本 文 的 知 识 你 应 该 清 楚 90% DBA( 数 据 库 管 理 员 ): 大 型 数 据 库 优 化 的 知 识 非 常 复 杂, 本 文 只 是 从 程 序 员 的 角 度 来 谈 性 能 优 化,DBA 除 了 需 要 了 解 这 些 知 识 外, 还 需 要 深 入 数 据 库 的 内 部 体 系 架 构 来 解 决 问 题
要 正 确 的 优 化 SQL, 我 们 需 要 快 速 定 位 能 性 的 瓶 颈 点, 也 就 是 说 快 速 找 到 我 们 SQL 主 要 的 开 销 在 哪 里? 而 大 多 数 情 况 性 能 最 慢 的 设 备 会 是 瓶 颈 点, 如 下 载 时 网 络 速 度 可 能 会 是 瓶 颈 点, 本 地 复 制 文 件 时 硬 盘 可 能 会 是 瓶 颈 点, 为 什 么 这 些 一 般 的 工 作 我 们 能 快 速 确 认 瓶 颈 点 呢, 因 为 我 们 对 这 些 慢 速 设 备 的 性 能 数 据 有 一 些 基 本 的 认 识, 如 网 络 带 宽 是 2Mbps, 硬 盘 是 每 分 钟 7200 转 等 等
这 个 优 化 法 则 归 纳 为 5 个 层 次 : 1 减 少 数 据 访 问 ( 减 少 磁 盘 访 问 ) 2 返 回 更 少 数 据 ( 减 少 网 络 传 输 或 磁 盘 访 问 ) 3 减 少 交 互 次 数 ( 减 少 网 络 传 输 ) 4 减 少 服 务 器 CPU 开 销 ( 减 少 CPU 及 内 存 开 销 ) 5 利 用 更 多 资 源 ( 增 加 资 源 )
数 据 库 访 问 优 化 详 解 减 少 数 据 访 问 返 回 更 少 的 数 据 减 少 交 互 次 数 减 少 数 据 库 服 务 器 CPU 运 算 利 用 更 多 的 资 源
数 据 库 访 问 优 化 详 解 减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 只 通 过 索 引 访 问 数 据 优 化 SQL 执 行 计 划
减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 索 引 会 大 大 增 加 表 记 录 的 DML 操 作 (INSERT,UPDATE,DELETE) 开 销, 正 确 的 索 引 可 以 让 性 能 提 升 100,1000 倍 以 上, 不 合 理 的 索 引 也 可 能 会 让 性 能 下 降 100 倍
减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 索 引 有 哪 些 种 类? 常 见 的 索 引 有 B-TREE 索 引 位 图 索 引 全 文 索 引, 位 图 索 引 一 般 用 于 数 据 仓 库 应 用, 全 文 索 引 由 于 使 用 较 少, 这 里 不 深 入 介 绍 B-TREE 索 引 包 括 很 多 扩 展 类 型, 如 组 合 索 引 反 向 索 引 函 数 索 引 等 等 B-TREE 索 引 也 称 为 平 衡 树 索 引 (Balance Tree), 它 是 一 种 按 字 段 排 好 序 的 树 形 目 录 结 构, 主 要 用 于 提 升 查 询 性 能 和 唯 一 约 束 支 持 叶 子 节 点 内 容 : 索 引 字 段 内 容 + 表 记 录 ROWID 根 节 点, 分 支 节 点 内 容 : 当 一 个 数 据 块 中 不 能 放 下 所 有 索 引 字 段 数 据 时, 就 会 形 成 树 形 的 根 节 点 或 分 支 节 点
减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 SQL 什 么 条 件 会 使 用 索 引? 当 字 段 上 建 有 索 引 时, 通 常 以 下 情 况 会 使 用 索 引 : INDEX_COLUMN =? INDEX_COLUMN >? INDEX_COLUMN >=? INDEX_COLUMN <? INDEX_COLUMN <=? INDEX_COLUMN between? and? INDEX_COLUMN in (?,?,...,?) INDEX_COLUMN like? '%'( 后 导 模 糊 查 询 ) T1. INDEX_COLUMN=T2. COLUMN1( 两 个 表 通 过 索 引 字 段 关 联 )
减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 SQL 什 么 条 件 不 会 使 用 索 引? 查 询 条 件 INDEX_COLUMN <>? INDEX_COLUMN not in (?,?,...,?) function(index_column) =? INDEX_COLUMN + 1 =? INDEX_COLUMN 'a' =? INDEX_COLUMN like '%'? INDEX_COLUMN like '%'? '%' INDEX_COLUMN is null NUMBER_INDEX_COLUMN='12345' CHAR_INDEX_COLUMN=12345 a.index_column=a.column_1 不 能 使 用 索 引 原 因 不 等 于 操 作 不 能 使 用 索 引 经 过 普 通 运 算 或 函 数 运 算 后 的 索 引 字 段 不 能 使 用 索 引 含 前 导 模 糊 查 询 的 Like 语 法 不 能 使 用 索 引 B-TREE 索 引 里 不 保 存 字 段 为 NULL 值 记 录, 因 此 IS NULL 不 能 使 用 索 引 Oracle 在 做 数 值 比 较 时 需 要 将 两 边 的 数 据 转 换 成 同 一 种 数 据 类 型, 如 果 两 边 数 据 类 型 不 同 时 会 对 字 段 值 隐 式 转 换, 相 当 于 加 了 一 层 函 数 处 理, 所 以 不 能 使 用 索 引 给 索 引 查 询 的 值 应 是 已 知 数 据, 不 能 是 未 知 字 段 值
减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 一 般 在 什 么 字 段 上 建 索 引? 主 键 及 外 键 通 常 都 要 有 索 引, 其 它 需 要 建 索 引 的 字 段 应 满 足 以 下 条 件 : 1 字 段 出 现 在 查 询 条 件 中, 并 且 查 询 条 件 可 以 使 用 索 引 ; 2 语 句 执 行 频 率 高, 一 天 会 有 几 千 次 以 上 ; 3 通 过 字 段 条 件 可 筛 选 的 记 录 集 很 小, 那 数 据 筛 选 比 例 是 多 少 才 适 合? 这 个 没 有 固 定 值, 需 要 根 据 表 数 据 量 来 评 估, 以 下 是 经 验 公 式, 可 用 于 快 速 评 估 : 小 表 ( 记 录 数 小 于 10000 行 的 表 ): 筛 选 比 例 <10%; 大 表 :( 筛 选 返 回 记 录 数 )<( 表 总 记 录 数 * 单 条 记 录 长 度 )/10000/16 单 条 记 录 长 度 字 段 平 均 内 容 长 度 之 和 + 字 段 数 *2
减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 如 何 知 道 SQL 是 否 使 用 了 正 确 的 索 引? 简 单 SQL 可 以 根 据 索 引 使 用 语 法 规 则 判 断, 复 杂 的 SQL 不 好 办, 判 断 SQL 的 响 应 时 间 是 一 种 策 略, 但 是 这 会 受 到 数 据 量 主 机 负 载 及 缓 存 等 因 素 的 影 响, 有 时 数 据 全 在 缓 存 里, 可 能 全 表 访 问 的 时 间 比 索 引 访 问 时 间 还 少 要 准 确 知 道 索 引 是 否 正 确 使 用, 需 要 到 数 据 库 中 查 看 SQL 真 实 的 执 行 计 划
减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 索 引 对 DML(INSERT,UPDATE,DELETE) 附 加 的 开 销 有 多 少? 这 个 没 有 固 定 的 比 例, 与 每 个 表 记 录 的 大 小 及 索 引 字 段 大 小 密 切 相 关, 以 下 是 一 个 普 通 表 测 试 数 据, 仅 供 参 考 : 索 引 对 于 Insert 性 能 降 低 56% 索 引 对 于 Update 性 能 降 低 47% 索 引 对 于 Delete 性 能 降 低 29% 因 此 对 于 写 IO 压 力 比 较 大 的 系 统, 表 的 索 引 需 要 仔 细 评 估 必 要 性, 另 外 索 引 也 会 占 用 一 定 的 存 储 空 间
数 据 库 访 问 优 化 详 解 减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 只 通 过 索 引 访 问 数 据 优 化 SQL 执 行 计 划
减 少 数 据 访 问 只 通 过 索 引 访 问 数 据 有 些 时 候, 我 们 只 是 访 问 表 中 的 几 个 字 段, 并 且 字 段 内 容 较 少, 我 们 可 以 为 这 几 个 字 段 单 独 建 立 一 个 组 合 索 引, 这 样 就 可 以 直 接 只 通 过 访 问 索 引 就 能 得 到 数 据, 一 般 索 引 占 用 的 磁 盘 空 间 比 表 小 很 多, 所 以 这 种 方 式 可 以 大 大 减 少 磁 盘 IO 开 销 如 :select id,name from company where type='2'; 引 如 果 这 个 SQL 经 常 使 用, 我 们 可 以 在 type,id,name 上 创 建 组 合 索 create index my_comb_index on company(type,id,name); 有 了 这 个 组 合 索 引 后,SQL 就 可 以 直 接 通 过 my_comb_index 索 引 返 回 数 据, 不 需 要 访 问 company 表
数 据 库 访 问 优 化 详 解 减 少 数 据 访 问 创 建 并 使 用 正 确 的 索 引 只 通 过 索 引 访 问 数 据 优 化 SQL 执 行 计 划
减 少 数 据 访 问 优 化 SQL 执 行 计 划 SQL 执 行 计 划 是 关 系 型 数 据 库 最 核 心 的 技 术 之 一, 它 表 示 SQL 执 行 时 的 数 据 访 问 算 法 由 于 业 务 需 求 越 来 越 复 杂, 表 数 据 量 也 越 来 越 大, 程 序 员 越 来 越 懒 惰,SQL 也 需 要 支 持 非 常 复 杂 的 业 务 逻 辑, 但 SQL 的 性 能 还 需 要 提 高, 因 此, 优 秀 的 关 系 型 数 据 库 除 了 需 要 支 持 复 杂 的 SQL 语 法 及 更 多 函 数 外, 还 需 要 有 一 套 优 秀 的 算 法 库 来 提 高 SQL 性 能
数 据 库 访 问 优 化 详 解 减 少 数 据 访 问 返 回 更 少 的 数 据 减 少 交 互 次 数 减 少 数 据 库 服 务 器 CPU 运 算 利 用 更 多 的 资 源
数 据 库 访 问 优 化 详 解 返 回 更 少 的 数 据 数 据 分 页 处 理 只 返 回 需 要 的 字 段
返 回 更 少 的 数 据 数 据 分 页 处 理 客 户 端 ( 应 用 程 序 或 浏 览 器 ) 分 页 将 数 据 从 应 用 服 务 器 全 部 下 载 到 本 地 应 用 程 序 或 浏 览 器, 在 应 用 程 序 或 浏 览 器 内 部 通 过 本 地 代 码 进 行 分 页 处 理 优 点 : 编 码 简 单, 减 少 客 户 端 与 应 用 服 务 器 网 络 交 互 次 数 缺 点 : 首 次 交 互 时 间 长, 占 用 客 户 端 内 存 适 应 场 景 : 客 户 端 与 应 用 服 务 器 网 络 延 时 较 大, 但 要 求 后 续 操 作 流 畅, 如 手 机 GPRS, 超 远 程 访 问 ( 跨 国 ) 等 等
返 回 更 少 的 数 据 数 据 分 页 处 理 应 用 服 务 器 分 页 将 数 据 从 数 据 库 服 务 器 全 部 下 载 到 应 用 服 务 器, 在 应 用 服 务 器 内 部 再 进 行 数 据 筛 选 以 下 是 一 个 应 用 服 务 器 端 Java 程 序 分 页 的 示 例 : List list=executequery( select * from employee order by id ); Int count= list.size(); List sublist= list.sublist(10, 20); 优 点 : 编 码 简 单, 只 需 要 一 次 SQL 交 互, 总 数 据 与 分 页 数 据 差 不 多 时 性 能 较 好 缺 点 : 总 数 据 量 较 多 时 性 能 较 差 适 应 场 景 : 数 据 库 系 统 不 支 持 分 页 处 理, 数 据 量 较 小 并 且 可 控
返 回 更 少 的 数 据 数 据 分 页 处 理 数 据 库 SQL 分 页 采 用 数 据 库 SQL 分 页 需 要 两 次 SQL 完 成 一 个 SQL 计 算 总 数 量 一 个 SQL 返 回 分 页 后 的 数 据 优 点 : 性 能 好 缺 点 : 编 码 复 杂, 各 种 数 据 库 语 法 不 同, 需 要 两 次 SQL 交 互 Oracle 数 据 库 一 般 采 用 rownum 来 进 行 分 页, 常 用 分 页 语 法 有 如 下 两 种 : 直 接 通 过 rownum 分 页 : select * from (select a.*,rownum from (select * from product a where company_id=? order by status) a where rownum<=20) where rownum>10; 数 据 访 问 开 销 = 索 引 IO+ 索 引 全 部 记 录 结 果 对 应 的 表 数 据 IO 采 用 rowid 分 页 语 法 : 优 化 原 理 是 通 过 纯 索 引 找 出 分 页 记 录 的 ROWID, 再 通 过 ROWID 回 表 返 回 数 据, 要 求 内 层 查 询 和 排 序 字 段 全 在 索 引 里 create index myindex on product(company_id,status); select b.* from (select * from (select a.*,rownum rn from (select rowid rid,status from product a where company_id=? order by status) a where rownum<=20) where rn>10) a, product b where a.rid=b.rowid; 数 据 访 问 开 销 = 索 引 IO+ 索 引 分 页 结 果 对 应 的 表 数 据 IO
数 据 库 访 问 优 化 详 解 返 回 更 少 的 数 据 数 据 分 页 处 理 只 返 回 需 要 的 字 段
返 回 更 少 的 数 据 只 返 回 需 要 的 字 段 通 过 去 除 不 必 要 的 返 回 字 段 可 以 提 高 性 能, 例 : 调 整 前 :select * from product where company_id=?; 调 整 后 :select id,name from product where company_id=?; 优 点 : 1 减 少 数 据 在 网 络 上 传 输 开 销 2 减 少 服 务 器 数 据 处 理 开 销 3 减 少 客 户 端 内 存 占 用 4 字 段 变 更 时 提 前 发 现 问 题, 减 少 程 序 BUG 5 如 果 访 问 的 所 有 字 段 刚 好 在 一 个 索 引 里 面, 则 可 以 使 用 纯 索 引 访 问 提 高 性 能 缺 点 : 增 加 编 码 工 作 量
数 据 库 访 问 优 化 详 解 减 少 数 据 访 问 返 回 更 少 的 数 据 减 少 交 互 次 数 减 少 数 据 库 服 务 器 CPU 运 算 利 用 更 多 的 资 源
数 据 库 访 问 优 化 详 解 减 少 交 互 次 数 batch DML In List 设 置 Fetch Size 使 用 存 储 过 程 优 化 业 务 逻 辑 使 用 ResultSet 游 标 处 理 记 录
减 少 交 互 次 数 batch DML 数 据 库 访 问 框 架 一 般 都 提 供 了 批 量 提 交 的 接 口,jdbc 支 持 batch 的 提 交 处 理 方 法 当 你 一 次 性 要 往 一 个 表 中 插 入 1000 万 条 数 据 时, 如 果 采 用 普 通 的 executeupdate 处 理, 那 么 和 服 务 器 交 互 次 数 为 1000 万 次, 按 每 秒 钟 可 以 向 数 据 库 服 务 器 提 交 10000 次 估 算, 要 完 成 所 有 工 作 需 要 1000 秒 如 果 采 用 批 量 提 交 模 式,1000 条 提 交 一 次, 那 么 和 服 务 器 交 互 次 数 为 1 万 次, 交 互 次 数 大 大 减 少 采 用 batch 操 作 一 般 不 会 减 少 很 多 数 据 库 服 务 器 的 物 理 IO, 但 是 会 大 大 减 少 客 户 端 与 服 务 端 的 交 互 次 数, 从 而 减 少 了 多 次 发 起 的 网 络 延 时 开 销, 同 时 也 会 降 低 数 据 库 的 CPU 开 销
数 据 库 访 问 优 化 详 解 减 少 交 互 次 数 batch DML In List 设 置 Fetch Size 使 用 存 储 过 程 优 化 业 务 逻 辑 使 用 ResultSet 游 标 处 理 记 录
数 据 库 访 问 优 化 详 解 减 少 交 互 次 数 batch DML In List 设 置 Fetch Size 使 用 存 储 过 程 优 化 业 务 逻 辑 使 用 ResultSet 游 标 处 理 记 录
减 少 交 互 次 数 In List 很 多 时 候 我 们 需 要 按 一 些 ID 查 询 数 据 库 记 录, 我 们 可 以 采 用 一 个 ID 一 个 请 求 发 给 数 据 库, 如 下 所 示 : for :var in ids[] do begin select * from mytable where id=:var; end; 我 们 也 可 以 做 一 个 小 的 优 化, 如 下 所 示, 用 ID INLIST 的 这 种 方 式 写 SQL: select * from mytable where id in(:id1,id2,...,idn); 通 过 这 样 处 理 可 以 大 大 减 少 SQL 请 求 的 数 量, 从 而 提 高 性 能 那 如 果 有 10000 个 ID, 那 是 不 是 全 部 放 在 一 条 SQL 里 处 理 呢? 答 案 肯 定 是 否 定 的 首 先 大 部 份 数 据 库 都 会 有 SQL 长 度 和 IN 里 个 数 的 限 制, 如 ORACLE 的 IN 里 就 不 允 许 超 过 1000 个 值
数 据 库 访 问 优 化 详 解 减 少 交 互 次 数 batch DML In List 设 置 Fetch Size 使 用 存 储 过 程 优 化 业 务 逻 辑 使 用 ResultSet 游 标 处 理 记 录
减 少 交 互 次 数 设 置 Fetch Size 当 我 们 采 用 select 从 数 据 库 查 询 数 据 时, 数 据 默 认 并 不 是 一 条 一 条 返 回 给 客 户 端 的, 也 不 是 一 次 全 部 返 回 客 户 端 的, 而 是 根 据 客 户 端 fetch_size 参 数 处 理, 每 次 只 返 回 fetch_size 条 记 录, 当 客 户 端 游 标 遍 历 到 尾 部 时 再 从 服 务 端 取 数 据, 直 到 最 后 全 部 传 送 完 成 所 以 如 果 我 们 要 从 服 务 端 一 次 取 大 量 数 据 时, 可 以 加 大 fetch_size, 这 样 可 以 减 少 结 果 数 据 传 输 的 交 互 次 数 及 服 务 器 数 据 准 备 时 间, 提 高 性 能 String vsql ="select * from t_employee"; PreparedStatement pstmt = conn.preparestatement(vsql,resultset.type_forward_only,resultset.concur_ READ_ONLY); pstmt.setfetchsize(1000); ResultSet rs = pstmt.executequery(vsql); int cnt = rs.getmetadata().getcolumncount(); Object o; while (rs.next()) { } for (int i = 1; i <= cnt; i++) { } o = rs.getobject(i);
数 据 库 访 问 优 化 详 解 减 少 交 互 次 数 batch DML In List 设 置 Fetch Size 使 用 存 储 过 程 优 化 业 务 逻 辑 使 用 ResultSet 游 标 处 理 记 录
减 少 交 互 次 数 使 用 存 储 过 程 如 果 采 用 存 储 过 程 你 可 以 将 整 个 业 务 逻 辑 封 装 在 存 储 过 程 里, 然 后 在 客 户 端 直 接 调 用 存 储 过 程 处 理, 这 样 可 以 减 少 网 络 交 互 的 成 本 当 然, 存 储 过 程 也 并 不 是 十 全 十 美, 存 储 过 程 有 以 下 缺 点 : a 不 可 移 植 性 b 学 习 成 本 高 c 增 加 一 些 系 统 维 护 和 调 试 成 本 d 存 储 过 程 和 常 用 应 用 程 序 语 言 不 一 样, 它 支 持 的 函 数 及 语 法 有 可 能 不 能 满 足 需 求, 有 些 逻 辑 就 只 能 通 过 应 用 程 序 处 理 e 会 增 加 一 些 数 据 库 服 务 端 的 处 理 成 本, 对 于 集 中 式 数 据 库 可 能 会 导 致 系 统 可 扩 展 性 问 题 f 存 储 过 程 需 要 重 新 编 译 才 能 生 效, 在 24*7 高 并 发 应 用 场 景
数 据 库 访 问 优 化 详 解 减 少 交 互 次 数 batch DML In List 设 置 Fetch Size 使 用 存 储 过 程 优 化 业 务 逻 辑 使 用 ResultSet 游 标 处 理 记 录
减 少 交 互 次 数 优 化 业 务 逻 辑 某 移 动 公 司 推 出 优 惠 套 参, 活 动 对 像 为 VIP 会 员 并 且 2010 年 1,2,3 月 平 均 话 费 20 元 以 上 的 客 户 那 我 们 的 检 测 逻 辑 为 : select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003'; select vip_flag from member where phone_no='13988888888'; if avg_money>20 and vip_flag=true then begin 执 行 套 参 (); end; 如 果 我 们 修 改 业 务 逻 辑 为 : select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003'; if avg_money>20 then begin select vip_flag from member where phone_no='13988888888'; if vip_flag=true then begin 执 行 套 参 (); end; end; 通 过 这 样 可 以 减 少 一 些 判 断 vip_flag 的 开 销, 平 均 话 费 20 元 以 下 的 用 户 就 不 需 要 再 检 测 是 否 VIP 了
数 据 库 访 问 优 化 详 解 减 少 交 互 次 数 batch DML In List 设 置 Fetch Size 使 用 存 储 过 程 优 化 业 务 逻 辑 使 用 ResultSet 游 标 处 理 记 录
减 少 交 互 次 数 使 用 ResultSet 游 标 处 理 记 录 现 在 大 部 分 Java 框 架 都 是 通 过 jdbc 从 数 据 库 取 出 数 据, 然 后 装 载 到 一 个 list 里 再 处 理,list 里 可 能 是 业 务 Object, 也 可 能 是 hashmap 由 于 JVM 内 存 一 般 都 小 于 4G, 所 以 不 可 能 一 次 通 过 sql 把 大 量 数 据 装 载 到 list 里 为 了 完 成 功 能, 很 多 程 序 员 喜 欢 采 用 分 页 的 方 法 处 理, 如 一 次 从 数 据 库 取 1000 条 记 录, 通 过 多 次 循 环 搞 定, 保 证 不 会 引 起 JVM Out of memory 问 题 代 码 示 例 : String vsql ="select * from t_employee"; PreparedStatement pstmt = conn.preparestatement(vsql,resultset.type_forward_only,resultset.concur_read_only); pstmt.setfetchsize(100); ResultSet rs = pstmt.executequery(vsql); int col_cnt = rs.getmetadata().getcolumncount(); Object o; while (rs.next()) { for (int j = 1; j <= col_cnt; j++) { o = rs.getobject(j); } }
数 据 库 访 问 优 化 详 解 减 少 数 据 库 服 务 器 CPU 运 算 使 用 绑 定 变 量 合 理 使 用 排 序 减 少 比 较 操 作 大 量 复 杂 运 算 在 客 户 端 处 理
减 少 数 据 库 服 务 器 CPU 运 算 使 用 绑 定 变 量 绑 定 变 量 有 以 下 优 点 : 1 防 止 SQL 注 入 2 提 高 SQL 可 读 性 3 提 高 SQL 解 析 性 能, 不 使 用 绑 定 变 更 我 们 一 般 称 为 硬 解 析, 使 用 绑 定 变 量 我 们 称 为 软 解 析 如 果 我 们 不 采 用 绑 定 变 量, 采 用 字 符 串 拼 接 的 模 式 生 成 SQL, 那 么 每 条 SQL 都 会 产 生 执 行 计 划, 这 样 会 导 致 共 享 池 耗 尽, 缓 存 命 中 率 也 很 低 一 些 不 使 用 绑 定 变 量 的 场 景 : a 数 据 仓 库 应 用, 这 种 应 用 一 般 并 发 不 高, 但 是 每 个 SQL 执 行 时 间 很 长,SQL 解 析 的 时 间 相 比 SQL 执 行 时 间 比 较 小, 绑 定 变 量 对 性 能 提 高 不 明 显 数 据 仓 库 一 般 都 是 内 部 分 析 应 用, 所 以 也 不 太 会 发 生 SQL 注 入 的 安 全 问 题 b 数 据 分 布 不 均 匀 的 特 殊 逻 辑, 如 产 品 表, 记 录 有 1 亿, 有 一 产 品 状 态 字 段, 上 面 建 有 索 引, 有 审 核 中, 审 核 通 过, 审 核 未 通 过 3 种 状 态, 其 中 审 核 通 过 9500 万, 审 核 中 1 万, 审 核 不 通 过 499 万
数 据 库 访 问 优 化 详 解 减 少 数 据 库 服 务 器 CPU 运 算 使 用 绑 定 变 量 合 理 使 用 排 序 减 少 比 较 操 作 大 量 复 杂 运 算 在 客 户 端 处 理
减 少 数 据 库 服 务 器 CPU 运 算 合 理 使 用 排 序 普 通 OLTP 系 统 排 序 操 作 一 般 都 是 在 内 存 里 进 行 的, 对 于 数 据 库 来 说 是 一 种 CPU 的 消 耗, 曾 在 PC 机 做 过 测 试, 单 核 普 通 CPU 在 1 秒 钟 可 以 完 成 100 万 条 记 录 的 全 内 存 排 序 操 作, 所 以 说 由 于 现 在 CPU 的 性 能 增 强, 对 于 普 通 的 几 十 条 或 上 百 条 记 录 排 序 对 系 统 的 影 响 也 不 会 很 大 但 是 当 你 的 记 录 集 增 加 到 上 万 条 以 上 时, 你 需 要 注 意 是 否 一 定 要 这 么 做 了, 大 记 录 集 排 序 不 仅 增 加 了 CPU 开 销, 而 且 可 能 会 由 于 内 存 不 足 发 生 硬 盘 排 序 的 现 象, 当 发 生 硬 盘 排 序 时 性 能 会 急 剧 下 降
减 少 数 据 库 服 务 器 CPU 运 算 合 理 使 用 排 序 可 能 会 发 生 排 序 操 作 的 SQL 语 法 : Order by Group by Distinct Exists 子 查 询 Not Exists 子 查 询 In 子 查 询 Not In 子 查 询 Union( 并 集 ),Union All 也 是 一 种 并 集 操 作, 但 是 不 会 发 生 排 序, 如 果 你 确 认 两 个 数 据 集 不 需 要 执 行 去 除 重 复 数 据 操 作, 那 请 使 用 Union All 代 替 Union Minus( 差 集 ) Intersect( 交 集 ) Create Index Merge Join, 这 是 一 种 两 个 表 连 接 的 内 部 算 法, 执 行 时 会 把 两 个 表 先 排 序 好 再 连 接, 应 用 于 两 个 大 表 连 接 的 操 作 如 果 你 的 两 个 表 连 接 的 条 件 都 是 等 值 运 算, 那 可 以 采 用 Hash Join 来 提 高 性 能, 因 为 Hash Join 使 用 Hash 运 算 来 代 替 排 序 的 操 作
数 据 库 访 问 优 化 详 解 减 少 数 据 库 服 务 器 CPU 运 算 使 用 绑 定 变 量 合 理 使 用 排 序 减 少 比 较 操 作 大 量 复 杂 运 算 在 客 户 端 处 理
减 少 数 据 库 服 务 器 CPU 运 算 合 理 使 用 排 序 我 们 SQL 的 业 务 逻 辑 经 常 会 包 含 一 些 比 较 操 作, 如 a=b,a<b 之 类 的 操 作, 对 于 这 些 比 较 操 作 数 据 库 都 体 现 得 很 好, 但 是 如 果 有 以 下 操 作, 我 们 需 要 保 持 警 惕 : Like 模 糊 查 询, 如 下 所 示 : a like %abc% Like 模 糊 查 询 对 于 数 据 库 来 说 不 是 很 擅 长, 特 别 是 你 需 要 模 糊 检 查 的 记 录 有 上 万 条 以 上 时, 性 能 比 较 糟 糕
数 据 库 访 问 优 化 详 解 减 少 数 据 库 服 务 器 CPU 运 算 使 用 绑 定 变 量 合 理 使 用 排 序 减 少 比 较 操 作 大 量 复 杂 运 算 在 客 户 端 处 理
减 少 数 据 库 服 务 器 CPU 运 算 大 量 复 杂 运 算 在 客 户 端 处 理 如 果 有 大 量 这 类 函 数 运 算, 尽 量 放 在 客 户 端 处 理, 一 般 CPU 每 秒 中 也 只 能 处 理 1 万 -10 万 次 这 样 的 函 数 运 算, 放 在 数 据 库 内 不 利 于 高 并 发 处 理
数 据 库 访 问 优 化 详 解 利 用 更 多 的 资 源 客 户 端 多 进 程 并 行 访 问 数 据 库 并 行 处 理 并 不 是 所 有 的 SQL 都 可 以 使 用 并 行 处 理, 一 般 只 有 对 表 或 索 引 进 行 全 部 访 问 时 才 可 以 使 用 并 行 数 据 库 表 默 认 是 不 打 开 并 行 访 问, 所 以 需 要 指 定 SQL 并 行 的 提 示, 如 下 所 示 : select /*+parallel(a,4)*/* from employee; 并 行 的 优 点 : 使 用 多 进 程 处 理, 充 分 利 用 数 据 库 主 机 资 源 (CPU,IO), 提 高 性 能 并 行 的 缺 点 : 1 单 个 会 话 占 用 大 量 资 源, 影 响 其 它 会 话, 所 以 只 适 合 在 主 机 负 载 低 时 期 使 用 ; 2 只 能 采 用 直 接 IO 访 问, 不 能 利 用 缓 存 数 据, 所 以 执 行 前 会 触 发 将 脏 缓 存 数 据 写 入 磁 盘 操 作 注 : 1 并 行 处 理 在 OLTP 类 系 统 中 慎 用, 使 用 不 当 会 导 致 一 个 会 话 把 主 机 资 源 全 部 占 用, 而 正 常 事 务 得 不 到 及 时 响 应, 所 以 一 般 只 是 用 于 数 据 仓 库 平 台 2 一 般 对 于 百 万 级 记 录 以 下 的 小 表 采 用 并 行 访 问 性 能 并 不 能 提 高, 反 而 可 能 会 让 性 能 更 差
Thank You