PostgreSQL VS MySQL&oracle 唐 成 2011.10.24
自 我 介 绍 唐 成 : 现 供 职 阿 里 巴 巴 ( 中 国 ) 网 络 技 术 有 限 公 司 主 要 负 责 公 司 greenplum 和 postgresql 数 据 库 的 工 作 个 人 热 爱 技 术, 精 通 oracle 数 据 库 和 C/C++ 语 言 网 名 :osdba
功 能 特 性 的 对 比 MySQL 最 重 要 的 引 擎 InnoDB 很 早 就 由 Oracle 公 司 控 制 目 前 整 个 MySQL 数 据 库 都 由 Oracle 控 制 对 复 杂 查 询 的 处 理 较 弱, 查 询 优 化 器 不 够 成 熟 只 有 一 种 表 连 接 类 型 : 嵌 套 循 环 连 接 (nested-loop), 不 支 持 排 序 - 合 并 连 接 (sort-merge join) 与 散 列 连 接 (hash join) 性 能 优 化 工 具 与 度 量 信 息 不 足 PostgreSQL BSD 协 议, 没 有 被 大 公 司 垄 断 很 强 大 的 查 询 优 化 器, 支 持 很 复 杂 的 查 询 处 理 都 支 持 提 供 了 很 多 性 能 视 图, 可 以 方 便 的 看 到 发 生 在 一 个 表 和 索 引 上 的 select delete update insert 统 计 信 息, 也 可 以 看 到 cache 命 中 率 网 上 有 一 个 开 源 的 pgstatspack 工 具
功 能 特 性 的 对 比 MySQL InnoDB 的 表 和 索 引 都 是 按 相 同 的 方 式 存 储 也 就 是 说 表 都 是 索 引 组 织 表 这 一 般 要 求 主 键 不 能 太 长 而 且 插 入 时 的 主 键 最 好 是 按 顺 序 递 增, 否 则 对 性 能 有 很 大 影 响 大 部 分 查 询 只 能 使 用 表 上 的 单 一 索 引 ; 在 某 些 情 况 下, 会 存 在 使 用 多 个 索 引 的 查 询, 但 是 查 询 优 化 器 通 常 会 低 估 其 成 本, 它 们 常 常 比 表 扫 描 还 要 慢 表 增 加 列, 基 本 上 是 重 建 表 和 索 引, 会 花 很 长 时 间 存 储 过 程 与 触 发 器 的 功 能 有 限 可 用 来 编 写 存 储 过 程 触 发 器 计 划 事 件 以 及 存 储 函 数 的 语 言 功 能 较 弱 PostgreSQL 不 存 在 这 个 问 题 不 存 在 这 个 问 题 表 增 加 列, 只 是 在 数 据 字 典 中 增 加 表 定 义, 不 会 重 建 表 除 支 持 pl/pgsql 写 存 储 过 程, 还 支 持 perl python Tcl 类 型 的 存 储 过 程 :pl/perl,pl/python, pl/tcl 也 支 持 用 C 语 言 写 存 储 过 程
功 能 特 性 的 对 比 MySQL 不 支 持 Sequence 不 支 持 函 数 索 引, 只 能 在 创 建 基 于 具 体 列 的 索 引 不 支 持 物 化 视 图 每 次 SQL 都 硬 解 析 MySQL 支 持 的 SQL 语 法 (ANSI SQL 标 准 ) 的 很 小 一 部 分 不 支 持 递 归 查 询 通 用 表 表 达 式 (Oracle 的 with 语 句 ) 或 者 窗 口 函 数 ( 分 析 函 数 ) 支 持 PostgreSQL 支 持 函 数 索 引, 同 时 还 支 持 部 分 数 据 索 引, 通 过 规 则 系 统 可 以 实 现 物 化 视 图 的 功 能 执 行 计 划 共 享 都 支 持
功 能 特 性 的 对 比 MySQL 不 支 持 用 户 自 定 义 类 型 或 域 (domain) 对 于 时 间 日 期 间 隔 等 时 间 类 型 没 有 秒 以 下 级 别 的 存 储 类 型 身 份 验 证 功 能 是 完 全 内 置 的, 不 支 持 操 作 系 统 认 证 PAM 认 证, 不 支 持 LDAP 以 及 其 它 类 似 的 外 部 身 份 验 证 功 能 不 支 持 database link 有 一 种 叫 做 Federated 的 存 储 引 擎 可 以 作 为 一 个 中 转 将 查 询 语 句 传 递 到 远 程 服 务 器 的 一 个 表 上, 不 过, 它 功 能 很 粗 糙 并 且 漏 洞 很 多 支 持 PostgreSQL 可 以 精 确 到 秒 以 下 支 持 OS 认 证 Kerberos 认 证 Ident 的 认 证 LDAP 认 证 PAM 认 证 有 dblink, 同 时 还 有 一 个 dbi-link 的 东 西, 可 以 连 接 到 oracle 和 mysql 上
MySQL Mysql Cluster 可 能 与 你 的 想 象 有 较 大 差 异 开 源 的 cluster 软 件 较 少 复 制 (Replication) 功 能 是 异 步 的, 并 且 有 很 大 的 局 限 性. 例 如, 它 是 单 线 程 的 (single-threaded), 因 此 一 个 处 理 能 力 更 强 的 Slave 的 恢 复 速 度 也 很 难 跟 上 处 理 能 力 相 对 较 慢 的 Master. 功 能 特 性 的 对 比 PostgreSQL 有 丰 富 的 开 源 cluster 软 件 支 持 explain 看 执 行 计 划 的 结 果 简 单 explain 返 回 丰 富 的 信 息 类 似 于 ALTER TABLE 或 CREATE TABLE 一 类 的 操 作 都 是 非 事 务 性 的. 它 们 会 提 交 未 提 交 的 事 务, 并 且 不 能 回 滚 也 不 能 做 灾 难 恢 复 DDL 也 是 有 事 务 的
PostgreSQL 多 版 本 实 现 PostgreSQL 的 多 版 本 实 现 与 其 它 数 据 库 的 最 大 差 别 是 没 有 回 滚 段 差 别 : 最 新 版 本 和 历 史 版 本 是 否 分 离 存 储 : PostgreSQL 不 分, 而 oracle 和 InnoDB 分, 而 innodb 也 只 是 分 离 了 数 据, 索 引 本 身 没 有 分 开 PostgreSQL 每 个 表 的 内 部 都 有 一 个 transaction id(xid,4 个 字 节 ) 字 段 每 次 更 新 数 据 行 时, 并 不 会 删 除 旧 的 数 据 行, 而 是 生 成 一 个 新 的 数 据 行, 新 的 数 据 行 的 xid 字 段 填 写 当 前 的 transaction id, 而 每 发 生 一 次 事 务 transcaction id 都 会 加 1, 这 类 似 Oracle 中 的 SCN 号 查 询 时, 如 果 发 现 一 行 的 xid 比 当 前 的 xid 新, 则 表 明 这 个 行 的 数 据 是 新 事 务 的, 则 跳 过, 这 样 只 返 回 小 于 等 于 当 前 xid 的 数 据 行, 这 样 就 实 现 了 数 据 的 一 致 性 当 然 这 些 删 除 掉 的 行, 仍 然 占 用 磁 盘 空 间, 这 时 PostgreSQL 提 供 了 vacuum 命 令 手 动 或 自 动 去 清 除 这 些 过 期 数 据
PostgreSQL 多 版 本 实 现 PostgreSQL 的 主 要 优 势 在 于 : 没 有 回 滚 段, 就 没 有 MySQL 和 oracle 回 滚 段 所 带 来 的 问 题 1. 回 滚 段 如 何 损 坏, 则 数 据 库 无 法 启 动 PostgreSQL 数 据 库 在 出 现 异 常 crash 后, 数 据 库 起 不 来 的 几 率 要 比 oracle 和 mysql 小 一 些 同 时 实 例 恢 复 的 速 度 也 比 oracle 和 Innodb 快 很 多 2. 无 oracle 和 MySQL 回 滚 段 满 的 问 题, 也 没 有 oracle 的 ora-01555 的 问 题 3. PostgreSQL 回 滚 可 以 很 快 完 成, 而 对 于 Innodb 和 oracle 回 滚 一 个 大 事 务, 会 带 来 很 多 严 重 的 问 题 同 时 回 滚 的 过 程 也 会 再 次 产 生 大 量 的 redo 日 志 4. WAL 日 志 要 比 oracle 和 Innodb 简 单, 对 于 oracle 不 仅 需 要 记 录 数 据 文 件 的 变 化, 还 要 记 录 回 滚 段 的 变 化
PostgreSQL 多 版 本 实 现 PostgreSQL 的 主 要 劣 势 在 于 : 最 新 版 本 和 历 史 版 本 不 分 离 存 储, 导 致 清 理 老 旧 版 本 需 要 作 更 多 的 扫 描, 代 价 比 较 大, 但 这 个 问 题 一 般 并 不 是 突 出, 因 为 VACUUM 中 也 有 很 多 的 优 化 如 PostgreSQL 8.3 中 加 入 了 HOT 技 术 使 用 HOT 后, 若 所 有 索 引 属 性 都 没 被 修 改 ( 索 引 键 是 否 修 改 是 在 执 行 时 逐 行 判 断 的, 因 此 若 一 条 UPDATE 语 句 修 改 了 某 属 性, 但 前 后 值 相 同 则 认 为 没 有 修 改 ), 且 新 版 本 与 原 版 本 存 储 在 一 个 页 面 上 则 不 会 产 生 新 的 索 引 记 录 由 于 索 引 中 完 全 没 有 版 本 信 息, 不 能 实 现 Coverage index scan, 即 查 询 只 扫 描 索 引, 直 接 从 索 引 中 返 回 所 需 的 属 性, 还 需 要 访 问 表 而 oracle 是 完 全 实 现 了 Covera index scan,innodb 是 部 分 实 现 了 ( 在 某 种 情 况 下 也 是 可 以 的, 但 不 是 所 有 情 况 下 都 可 以 了 )
PostgreSQL 多 版 本 实 现 在 业 务 上 产 生 的 差 异 : 对 于 insert 操 作,oracle 和 innodb, 需 要 在 数 据 文 件 插 入 数 据, 在 回 滚 段 中 记 录 少 量 回 滚 信 息,redo 日 志 就 记 录 操 作 信 息 ; 对 于 PostgreSQL 只 需 要 在 数 据 文 件 和 WAL 日 志 中 记 录 操 作 对 于 delete 操 作,oracle 和 innodb, 需 要 在 数 据 文 件 删 除 数 据, 在 回 滚 段 中 记 录 旧 数 据,redo 日 志 就 记 录 操 作 信 息 ; 对 于 PostgreSQL 只 需 要 更 新 数 据 文 件 和 在 WAL 日 志 中 记 录 操 作 对 于 update 操 作,PostgreSQL 需 要 新 生 成 一 行, 会 导 致 原 表 膨 胀, 而 对 于 oracle 和 innodb, 是 把 旧 行 中 变 化 前 的 字 段 值 记 录 在 回 滚 段 中, 所 以 原 表 的 大 小 不 会 发 生 明 显 的 变 化 另 也 有 可 能 会 导 致 索 引 的 变 化 对 于 PostgreSQL 数 据 库 来 说, 需 要 对 表 做 vacuum 以 清 理 表 中 的 垃 圾 数 据, 而 对 于 oracle 和 innodb 中 的 回 滚 段 中 的 数 据 需 要 经 常 做 收 缩
PostgreSQL 进 程 模 式 VS MySQL 线 程 模 式 PostgreSQL 和 oracle 是 进 程 模 式,MySQL 是 线 程 模 式 进 程 模 式 对 多 CPU 利 用 率 比 较 高 进 程 模 式 共 享 数 据 需 要 用 到 共 享 内 存, 而 线 程 模 式 数 据 本 身 就 是 在 进 程 空 间 内 都 是 共 享 的, 不 同 线 程 访 问 只 需 要 控 制 好 线 程 之 间 的 同 步 因 为 多 个 线 程 都 共 享 同 一 块 内 存, 所 以 程 序 一 旦 有 bug, 就 可 能 破 坏 整 个 系 统 线 程 模 式 对 资 源 消 耗 比 较 少 所 以 MySQL 能 支 持 远 比 oracle 多 的 更 多 的 连 接 对 于 PostgreSQL 的 来 说, 如 果 不 使 用 连 接 池 软 件, 也 存 在 这 个 问 题, 但 PostgreSQL 中 有 优 秀 的 连 接 池 软 件 软 件, 如 pgbouncer 和 pgpool, 所 以 通 过 连 接 池 也 可 以 支 持 很 多 的 连 接
堆 表 与 索 引 组 织 表 的 的 对 比 Oracle 支 持 堆 表, 也 支 持 索 引 组 织 表 PostgreSQL 只 支 持 堆 表, 不 支 持 索 引 组 织 表, Innodb 只 支 持 索 引 组 织 表 索 引 组 织 表 的 优 势 : 表 内 的 数 据 就 是 按 索 引 的 方 式 组 织, 数 据 是 有 序 的, 如 果 数 据 都 是 按 主 键 来 访 问, 那 么 访 问 数 据 比 较 快 而 堆 表, 按 主 键 访 问 数 据 时, 是 需 要 先 按 主 键 索 引 找 到 数 据 的 物 理 位 置
堆 表 与 索 引 组 织 表 的 的 对 比 索 引 组 织 表 的 劣 势 : 索 引 组 织 表 中 上 再 加 其 它 的 索 引 时, 其 它 的 索 引 记 录 的 数 据 位 置 不 再 是 物 理 位 置, 而 是 主 键 值, 所 以 对 于 索 引 组 织 表 来 说, 主 键 的 值 不 能 太 大, 否 则 占 用 的 空 间 比 较 大 对 于 索 引 组 织 表 来 说, 如 果 每 次 在 中 间 插 入 数 据, 可 能 会 导 致 索 引 分 裂, 索 引 分 裂 会 大 大 降 低 插 入 的 性 能 所 以 对 于 使 用 innodb 来 说, 我 们 一 般 最 好 让 主 键 是 一 个 无 意 义 的 序 列, 这 样 插 入 每 次 都 发 生 在 最 后, 以 避 免 这 个 问 题 索 引 组 织 表 的 全 表 扫 描 要 比 堆 表 慢
分 区 表 的 对 比 PostgreSQL 是 通 过 继 承 来 实 现 仿 真 分 区 表 好 处 : 比 oracle 和 mysql 的 分 区 表 更 灵 活 父 子 表 可 以 有 不 同 的 结 构, 也 可 以 有 不 同 的 索 引 缺 点 : 对 于 绑 定 变 量 的 SQL, 不 能 进 行 分 区 表 裁 剪
PostgreSQL 不 支 持 裸 设 备 PostgreSQL 数 据 库 需 要 一 个 很 好 的 文 件 系 统, 这 一 般 不 存 在 问 题,Linux 下 我 们 一 般 可 以 使 用 xfs Oracle 可 以 使 用 裸 设 备 MySQL 的 innodb 也 可 以 使 用 祼 设 备, 但 共 享 表 空 间 只 能 使 用 一 个 祼 设 备 而 独 享 表 空 间, 单 个 表 必 须 使 用 一 个 单 独 文 件, 也 就 是 如 果 使 用 裸 设 备, 每 个 表 必 须 一 个 裸 设 备, 这 基 本 上 不 可 行, 所 以 MySQL 中 使 用 裸 设 备 的 也 很 少 存 储 层 的 差 别
SQL 解 析 的 不 同 MySQL 对 所 有 的 SQL 都 是 硬 解 析, 执 行 计 划 不 能 被 复 用 PostgreSQL 可 以 对 SQL 硬 解 析 一 次, 后 面 再 执 行 时 复 用 这 个 执 行 计 划, 但 执 行 计 划 不 能 在 session 之 间 共 享 Oracle 有 共 享 池, 所 有 session 的 SQL 执 行 计 划 都 可 以 共 享 但 共 享 池 的 设 计 过 于 复 杂, 很 容 易 导 致 很 多 问 题
DDL 的 不 同 MySQL 和 oracle 的 DDL 都 是 自 动 提 交 PostgreSQL 中 的 DDL 与 DML 没 有 太 大 的 差 别,DDL 也 可 以 回 滚
开 源 的 差 别 MySQL 不 是 一 个 纯 粹 的 开 源 数 据 库 PostgreSQL 使 用 BSD 协 议,MySQL 使 用 GPL 和 商 业 双 重 协 议 MySQL 数 据 库 是 一 个 公 司 的 一 产 品, 是 一 个 公 司 做 了 绝 大 部 分 开 发 的 工 作, 所 以 MySQL 不 是 完 全 意 义 上 的 开 源 数 据 库, 这 是 个 劣 势, 不 能 得 到 绝 大 多 数 人 的 支 持, 或 让 社 区 更 多 人 参 与 进 来 PostgreSQL 与 MySQL 不 同,PostgreSQL 一 开 始 就 是 定 位 在 ORACLE 这 些 大 数 据 库 能 做 的 事 情 所 以 从 发 展 来 看,PostgreSQL 的 前 途 会 更 好 MySQL 目 前 是 由 oracle 公 司 控 制, 而 oracle 公 司 有 本 身 的 商 业 数 据 库, 在 oracle 公 司 内 部,MySQL 的 发 展 必 然 会 受 到 oracle 数 据 库 的 压 制
PostgreSQL 缺 点 与 oracle 和 MySQL 相 比 最 大 的 缺 点 是 人 气 不 旺 PostgreSQL 中 的 中 文 资 料 远 远 少 于 oracle 和 MySQL PostgreSQL 在 国 外 使 用 还 是 比 较 广 泛 的, 特 别 在 日 本 但 在 中 国 人 气 相 对 oracle 和 mysql 还 是 有 比 较 大 的 差 距, 只 是 近 几 年 来, 在 国 内 使 用 PostgreSQL 的 公 司 也 越 来 越 多 了 需 要 对 vacuum 做 仔 细 的 安 排, 特 别 对 于 更 新 频 繁 的 数 据 库 vacuum 是 由 PostgreSQL 多 版 本 设 计 决 定 的 vacuum 不 能 回 收 表 已 经 占 用 的 空 间 不 支 持 裸 设 备 要 求 OS 下 有 一 个 健 壮 的 文 件 系 统 在 Linux 下 我 们 一 般 选 XFS, 对 于 solaris 下 选 ZFS oracle 对 裸 设 备 有 很 好 的 支 持, 而 MySQL 的 innodb 引 擎 也 可 以 放 在 裸 设 备 下, 但 由 于 独 享 表 空 间 必 需 是 一 个 文 件 一 张 表, 所 以 使 用 裸 设 备 的 管 理 成 本 也 比 较 高 不 支 持 converage index scan 由 于 索 引 中 完 全 没 有 版 本 信 息, 不 能 实 现 Coverage index scan, 即 查 询 只 扫 描 索 引, 直 接 从 索 引 中 返 回 所 需 的 属 性, 还 需 要 访 问 表 对 于 类 似 select count(*) from table 的 语 句,PostgreSQL 要 比 oracle 慢, 另 MySQL 的 select count(*) 也 不 如 oracle
Q&A
谢 谢 大 家!