mysql 各 个 存 储 引 擎 概 述 : innodb 存 储 引 擎 : 面 向 oltp(online transaction processing) 行 锁 支 持 外 键 非 锁 定 读 默 认 采 用 repeaable 级 别 ( 可 重 复 读 ) 通 过 next-keyloc

Transcription

1 写 在 前 面 : 本 人 在 学 习 研 究 实 践 MySQL 数 据 库 管 理 技 术 的 过 程 中, 发 现 了 很 多 写 得 非 常 好 的 博 文, 并 且 发 现, 对 于 某 一 个 特 定 的 单 个 的 知 识 点 ( 而 不 是 全 套 MySQL 知 识 ), 有 时 候 看 博 文 会 比 看 书 好 得 多 理 解 得 透 彻 写 书 是 一 种 很 枯 燥 的 过 程, 作 者 在 连 续 写 作 之 中, 很 有 可 能 对 某 个 ( 或 某 些 ) 技 术 点 考 虑 得 不 是 很 周 全, 而 写 博 文 不 同, 写 博 文 只 是 针 对 某 一 个 技 术 点, 精 力 会 比 较 集 中 本 人 将 这 些 博 文 逐 渐 汇 集 起 来, 争 取 形 成 体 系, 分 享 给 大 家 本 文 档 ( 无 论 是 word 格 式 还 是 PDF 格 式 ) 以 后 会 不 断 更 新 温 馨 提 示 : 阅 读 时 建 议 点 开 PDF 或 者 word 的 导 航 栏, 便 于 翻 阅 MySQL 体 系 结 构 基 于 mysql 体 系 结 构 的 深 入 解 析 由 : 连 接 池 组 件 管 理 服 务 和 工 具 组 件 sql 接 口 组 件 查 询 分 析 器 组 件 优 化 器 组 件 缓 冲 组 件 插 件 式 存 储 引 擎 物 理 文 件 组 成 mysql 是 独 有 的 插 件 式 体 系 结 构, 各 个 存 储 引 擎 有 自 己 的 特 点

2 mysql 各 个 存 储 引 擎 概 述 : innodb 存 储 引 擎 : 面 向 oltp(online transaction processing) 行 锁 支 持 外 键 非 锁 定 读 默 认 采 用 repeaable 级 别 ( 可 重 复 读 ) 通 过 next-keylocking 策 略 避 免 幻 读 插 入 缓 冲 二 次 写 自 适 应 哈 希 索 引 预 读 myisam 存 储 引 擎 : 不 支 持 事 务 表 锁 全 文 索 引 适 合 olap( 在 线 分 析 处 理 ), 其 中 myd: 放 数 据 文 件,myi: 放 索 引 文 件 ndb 存 储 引 擎 : 集 群 存 储 引 擎,share nothing, 可 提 高 可 用 性 memory 存 储 引 擎 : 数 据 存 放 在 内 存 中, 表 锁, 并 发 性 能 差, 默 认 使 用 哈 希 索 引 archive 存 储 引 擎 : 只 支 持 insert 和 select zlib 算 法 压 缩 1:10, 适 合 存 储 归 档 数 据 如 日 志 等 行 锁 maria 存 储 引 擎 : 目 的 取 代 myisam 缓 存 数 据 和 索 引 行 锁 mvcc innodb 特 性 : 主 体 系 结 构 : 默 认 7 个 后 台 线 程,4 个 io thread(insert buffer log read write),1 个 master thread( 优 先 级 最 高 ),1 个 锁 (lock) 监 控 线 程,1 个 错 误 监 控 线 程 可 以 通 过 show engine innodb status 来 查 看 新 版 本 已 对 默 认 的 read thread 和 write thread 分 别 增 大 到 4 个, 可

3 通 过 show variables like 'innodb_io_thread%' 查 看 存 储 引 擎 组 成 : 缓 冲 池 (buffer pool) 重 做 日 志 缓 冲 池 (redo log buffer) 以 及 额 外 的 内 存 池 (additional memory pool). 具 体 配 置 可 由 show variables like 'innodb_buffer_pool_size' show variables like 'innodb_log_buffer_size' show variables like 'innodb_additional_mem_pool_size' 来 查 看 缓 冲 池 : 占 最 大 块 内 存, 用 来 存 放 各 种 数 据 的 缓 存 包 括 有 索 引 页 数 据 页 undo 页 插 入 缓 冲 自 适 应 哈 希 索 引 innodb 存 储 的 锁 信 息 数 据 字 典 信 息 等 工 作 方 式 总 是 将 数 据 库 文 件 按 页 ( 每 页 16k) 读 取 到 缓 冲 池, 然 后 按 最 近 最 少 使 用 (lru) 的 算 法 来 保 留 在 缓 冲 池 中 的 缓 存 数 据 如 果 数 据 库 文 件 需 要 修 改, 总 是 首 先 修 改 在 缓 存 池 中 的 页 ( 发 生 修 改 后 即 为 脏 页 ), 然 后 再 按 照 一 定 的 频 率 将 缓 冲 池 的 脏 页 刷 新 到 文 件 通 过 命 令 show engine innodb status; 来 查 看 日 志 缓 冲 : 将 重 做 日 志 信 息 先 放 入 这 个 缓 冲 区, 然 后 按 一 定 频 率 将 其 刷 新 到 重 做 日 志 文 件 master thread: loop 主 循 环 每 秒 一 次 的 操 作 : 日 志 缓 冲 刷 新 到 磁 盘, 即 使 这 个 事 务 还 没 有 提 交 ( 总 是 执 行, 所 以 再 大 的 事 务 commit 的 时 间 也 是 很 快 的 ) 合 并 插 入 缓 冲 (innodb 当 前 一 秒 发 生 的 io 次 数 小 于 5 次 则 执 行 ) 至 多 刷 新 100 个 innodb 的 缓 冲 池 中 的 脏 页 到 磁 盘 ( 超 过 配 置 的 脏 页 所 占 缓 冲 池 比 例 则 执 行, 在 配 置 文 件 中 由 innodb_max_dirty_pages_pac 决 定, 默 认 是 90, 新 版 本 是 75, google 建 议 是 80) 如 果 当 前 没 用 用 户 活 动, 切 换 到 backgroud loop loop 主 循 环 每 10 秒 一 次 的 操 作 : 刷 新 100 个 脏 页 到 磁 盘 ( 过 去 10 秒 IO 操 作 小 于 200 次 则 执 行 ) 合 并 至 多 5 个 插 入 缓 冲 ( 总 是 )

4 将 日 志 缓 冲 到 磁 盘 ( 总 是 ) 删 除 无 用 的 Undo 页 ( 总 是 ) 刷 新 100 个 或 者 10 个 脏 页 到 磁 盘 ( 有 超 过 70% 的 脏 页, 刷 新 100 个 脏 页 ; 否 则 刷 新 10 个 脏 页 ) 产 生 一 个 检 查 点 backgroud loop, 若 当 前 没 有 用 户 活 动 ( 数 据 库 空 闲 时 ) 或 者 数 据 库 关 闭 时, 就 会 切 换 到 这 个 循 环 : 删 除 无 用 的 Undo 页 ( 总 是 ) 合 并 20 个 插 入 缓 冲 ( 总 是 ) 跳 回 到 主 循 环 ( 总 是 ) 不 断 刷 新 100 个 页, 直 到 符 合 条 件 ( 可 能 在 flush loop 中 完 成 ) 如 果 flush loop 中 也 没 有 什 么 事 情 可 以 做 了,InnoDB 存 储 引 擎 会 切 换 到 suspend_loop, 将 master thread 挂 起, 等 待 事 件 的 发 生 若 启 用 了 InnoDB 存 储 引 擎, 却 没 有 使 用 任 何 InnoDB 存 储 引 擎 的 表, 那 么 master thread 总 是 处 于 挂 起 状 态 插 入 缓 冲 : 不 是 缓 冲 池 的 一 部 分,Insert Buffer 是 物 理 页 的 一 个 组 成 部 分, 它 带 来 InnoDB 性 能 的 提 高 根 据 B+ 算 法 ( 下 文 会 提 到 ) 的 特 点, 插 入 数 据 的 时 候 会 主 键 索 引 是 顺 序 的, 不 会 造 成 数 据 库 的 随 机 读 取, 而 对 于 非 聚 集 索 引 ( 即 辅 助 索 引 ), 叶 子 节 点 的 插 入 不 再 是 顺 序 的 了, 这 时 需 要 离 散 地 访 问 非 聚 集 索 引, 插 入 性 能 在 这 里 变 低 了 InnoDB 引 入 插 入 缓 冲, 判 断 非 聚 集 索 引 页 是 否 在 缓 冲 池 中, 如 果 在 则 直 接 插 入 ; 不 在, 则 先 放 在 插 入 缓 冲 区 中 然 后 根 据 上 述 master thread 中 介 绍 的, 会 有 一 定 的 频 率 将 插 入 缓 冲 合 并 此 外, 辅 助 索 引 不 能 是 唯 一 的, 因 为 插 入 到 插 入 缓 冲 时, 并 不 去 查 找 索 引 页 的 情 况, 否 则 仍 然 会 造 成 随 机 读, 失 去 插 入 缓 冲 的 意 义 了 插 入 缓 冲 可 能 会 占 缓 冲 池 中 内 存, 默 认 也 能 会 占 到 1/2, 所 以 可 以 将 这 个 值 调 小 点, 到 1/3 通 过 IBUF_POOL_SIZE_PER_MAX_SIZE 来 设 置,2 表 示 1/2,3 表 示 1/3 两 次 写 : 它 带 来 InnoDB 数 据 的 可 靠 性 如 果 写 失 效, 可 以 通 过 重 做 日 志 进 行 恢 复, 但 是 重 做 日 志 中 记 录 的 是 对 页 的 物 理 操 作, 如 果 页 本 身 损 坏, 再 对 其 进 行 重 做 是 没 有 意 义 的 所 以, 在 应 用 重 做 日 志 前, 需 要 一 个 页 的 副 本, 当 写 入 失 效 发 生 时, 先 通 过 页 的 副 本 来 还 原 该 页, 再 进 行 重 做, 这 就 是 doublewire 恢 复 数 据 = 页 副 本 + 重 做 日 志

5 自 适 应 哈 希 索 引 :InnoDB 存 储 引 擎 提 出 一 种 自 适 应 哈 希 索 引, 存 储 引 擎 会 监 控 对 表 上 索 引 的 查 找, 如 果 观 察 到 建 立 建 立 哈 希 索 引 会 带 来 速 度 的 提 升, 则 建 立 哈 希 索 引, 所 以 称 之 为 自 适 应 的 自 适 应 哈 希 索 引 只 能 用 来 搜 索 等 值 的 查 询, 如 select * from table where index_col='***', 此 外 自 适 应 哈 希 是 由 InnoDB 存 储 引 擎 控 制 的, 我 们 只 能 通 过 innodb_adaptive_hash_index 来 禁 用 或 启 用, 默 认 开 启 mysql 文 件 参 数 文 件 : 告 诉 Mysql 实 例 启 动 时 在 哪 里 可 以 找 到 数 据 库 文 件, 并 且 指 定 某 些 初 始 化 参 数, 这 些 参 数 定 义 了 某 种 内 存 结 构 的 大 小 等 设 置 用 文 件 存 储, 可 编 辑, 若 启 动 时 加 载 不 到 则 不 能 成 功 启 动 ( 与 其 他 数 据 库 不 同 ) 参 数 有 动 态 和 静 态 之 分, 静 态 相 当 于 只 读, 动 态 是 可 以 set 的 如 我 们 通 过 show variable like '***' 查 出 来 的 key value 值, 是 可 以 通 过 set key=value 直 接 修 改 的 同 是, 修 改 时 还 有 作 用 域 之 分, 即 这 个 seesion 个 有 效 和 全 局 有 效, 在 对 应 的 key 前 加 上 session 或 global 即 可, 如 日 志 文 件 : 用 来 记 录 Mysql 实 例 对 某 种 条 件 做 出 响 应 时 写 入 的 文 件 如 错 误 日 志 文 件 二 进 制 日 志 文 件 慢 查 询 日 志 文 件 查 询 日 志 文 件 等 错 误 日 志 : 通 过 show variables like 'log_error' 来 查 看 错 误 日 志 存 放 地 址 慢 查 询 日 志 : 通 过 show variables like '%long%' 查 看 慢 查 询 日 志 记 录 的 阈 值, 新 版 本 设 成 了 0.05; 通 过 show variables like 'log_slow_queries' 查 看 是 否 开 启 了, 默 认 为 关 闭 的 ; 通 过

6 show variabes like 'log_queries_not_using_indexes' 查 看 是 将 没 有 使 用 索 引 的 查 询 记 录 到 慢 日 志 中 mysql 中 可 以 直 接 通 过 mysqldumpslow 命 令 来 查 看 慢 日 志 二 进 制 文 件 : 不 记 录 查 询, 只 记 录 对 数 据 库 所 有 的 修 改 操 作 目 的 是 为 了 恢 复 (point-in-time 修 复 ) 和 复 制 通 过 show variables like 'datadir' 查 看 存 放 路 径 二 进 制 日 志 支 持 STATEMENT ROW MIX 三 种 格 式, 通 过 binlog_format 参 数 设 定, 通 常 设 置 为 ROW, 可 以 为 数 据 库 的 恢 复 和 复 制 带 来 更 好 的 可 靠 性, 但 会 带 来 二 进 制 文 件 大 小 的 增 加, 复 制 时 会 增 加 网 络 开 销 mysql 中 通 过 mysqlbinlog 查 看 二 进 制 日 志 文 件 内 容 socket 文 件 : 当 用 Unix 域 套 接 字 方 式 进 行 连 接 时 需 要 的 文 件 pid 文 件 :Mysql 实 例 的 进 程 ID 文 件 Mysql 表 结 构 文 件 : 用 来 存 放 Mysql 表 结 构 定 义 文 件 因 为 Mysql 插 件 式 存 储 引 擎 的 体 系 结 构, 每 个 表 都 有 一 个 对 应 的 文 件, 以 frm 后 缀 结 尾 存 储 引 擎 文 件 : 存 储 自 己 的 文 件 来 保 存 各 种 数 据, 真 正 存 储 了 数 据 和 索 引 等 数 据 下 面 主 要 介 绍 InnoDB 的 存 储 引 擎 下 的 表 空 间 文 件 和 重 做 日 志 文 件 表 空 间 文 件 :InnoDB 默 认 的 表 空 间 文 件 为 ibdata1, 可 通 过 show variables like 'innodb_file_per_table' 查 看 每 个 表 是 否 产 生 单 独 的.idb 表 空 间 文 件 但 是, 单 独 的 表 空 间 文 件 仅 存 储 该 表 的 数 据 索 引 和 插 入 缓 冲 等 信 息, 其 余 信 息 还 是 存 放 在 默 认 的 表 空 间 中 重 做 日 志 文 件 : 实 例 和 介 质 失 败, 重 做 日 志 文 件 就 能 派 上 用 场, 如 数 据 库 掉 电,InnoDB 存 储 引 擎 会 使 用 重 做 日 志 恢 复 到 掉 电 前 的 时 刻, 以 此 来 保 证 数 据 的 完 整 性 参 数 innodb_log_file_size 指 定 了 重 做 日 志 文 件 的 大 小 ;innodb_log_file_in_group 指 定 了 日 志 文 件 组 中 重 做 日 志 文 件 的 数 量, 默 认 为 2,innodb_mirrored_log_groups 指 定 了 日 志 镜 像 文 件 组 的 数 量, 默 认 为 1, 代 表 只 有 一 个 日 志 文 件 组, 没 有 镜 像 ;innodb_log_group_home_dir

7 指 定 了 日 志 文 件 组 所 在 路 径, 默 认 在 数 据 库 路 径 下 二 进 制 日 志 和 重 做 日 志 的 区 别 : 首 先, 二 进 制 日 志 会 记 录 所 有 与 Mysql 有 关 的 日 志 记 录, 包 括 InnoDB MyISAM Heap 等 其 他 存 储 引 擎 的 日 志 而 InnoDB 存 储 引 擎 重 做 日 志 只 存 储 有 关 其 本 身 的 事 务 日 志 ; 其 次 内 容 不 同, 不 管 将 二 进 制 日 志 文 件 记 录 的 格 式 设 为 STATEMENT 还 是 ROW, 又 或 者 是 MIXED, 其 记 录 的 都 是 关 于 一 个 事 务 的 具 体 操 作 内 容 而 InnoDB 存 储 引 擎 的 重 做 日 志 文 件 记 录 的 关 于 每 个 页 的 更 改 的 物 理 情 况 此 外, 写 入 时 间 不 同, 二 进 制 日 志 文 件 是 在 事 务 提 交 前 进 行 记 录 的, 而 在 事 务 进 行 的 过 程 中, 不 断 有 重 做 日 志 条 目 被 写 入 重 做 日 志 文 件 中 mysql innodb 表 表 空 间 : 表 空 间 可 看 做 是 InnoDB 存 储 引 擎 逻 辑 结 构 的 最 高 层 段 : 表 空 间 由 各 个 段 组 成, 常 见 的 段 有 数 据 段 索 引 段 回 滚 段 等 区 : 由 64 个 连 续 的 页 组 成, 每 个 页 大 小 为 16kb, 即 每 个 区 大 小 为 1MB 页 : 每 页 16kb, 且 不 能 更 改 常 见 的 页 类 型 有 : 数 据 页 Undo 页 系 统 页 事 务 数 据 页 插 入 缓 冲 位 图 页 插 入 缓 冲 空 闲 列 表 页 未 压 缩 的 二 进 制 大 对 象 页 压 缩 的 二 进 制 大 对 象 页 行 :InnoDB 存 储 引 擎 是 面 向 行 的 (row-oriented), 每 页 最 多 允 许 存 放 7992 行 数 据 行 记 录 格 式 : 常 见 两 种 行 记 录 格 式 Compact 和 Redundant,mysql5.1 版 本 后, 主 要 是 Compact 行 记 录 格 式 对 于 Compact, 不 管 是 char 型 还 是 varchar 型,null 型 都 是 不 占 用 存 储 空 间 的 ; 对 于 Redudant,varchar 的 null 不 占 用 空 间,char 的 null 型 是 占 用 存 储 空 间 的 varchar 类 型 的 长 度 限 制 是 65535, 其 实 达 不 到, 会 有 别 的 开 销, 一 般 是 左 右, 这 还 跟 选 取 的 字 符 集 有 关 此 外 这 个 长 度 限 制 是 一 整 行 的, 例 如 :create table test(a varchar(22000), b varchar(22000), cvarchar(22000)) charset=latin1 engine=innodb 也 会 报 错 对 于 blob 类 型 的 数 据, 在 数 据 页 面 中 只 保 存 了 varchar(65535) 的 前 768 个 字 节 前 缀 数 据, 之 后 跟 的 是 偏 移 量, 指 向 行 溢 出 页, 也 就 是 Uncompressed BLOB Page 新 的 InnoDB Plugin 引 入 了 新 的 文 件 格 式 称 为 Barracuda, 其 有 两 种 新 的 行 记 录 格 式 Compressed 和 Dynamic, 两 者 对 于 存 入 Blog 字 段 采 用 了 完 全 溢 出 的 方 式, 在 数 据 库 页 中 存 放 20 个 字 节 的 指 针, 实 际 的 数 据 都 存 入 在 BLOB Page 中

8 数 据 页 结 构 : 数 据 页 结 构 由 以 下 7 个 部 分 组 成 : File Header( 文 件 头 ): 记 录 页 的 一 些 头 信 息, 如 页 偏 移 量 上 一 页 下 一 页 页 类 型 等, 固 定 长 度 为 38 个 字 节 Page Header( 页 头 ): 记 录 页 的 状 态 信 息, 堆 中 记 录 数 指 向 空 闲 列 表 的 指 针 已 删 除 记 录 的 字 节 数 最 后 插 入 的 位 置 等, 固 定 长 度 共 56 个 字 节 Infimun+Supremum Records: 在 InnoDB 存 储 引 擎 中, 每 个 数 据 页 中 有 两 个 虚 拟 的 行 记 录, 用 来 限 定 记 录 的 边 界 Infimun 记 录 是 比 该 页 中 任 何 主 键 都 要 小 的 值,Supermum 指 比 任 何 可 能 大 的 值 还 要 大 的 值 这 两 个 值 在 页 创 建 时 被 建 立, 并 且 在 任 何 情 况 下 不 会 被 删 除 在 Compact 行 格 式 和 Redundant 行 格 式 下, 两 者 占 用 的 字 节 数 各 不 相 同

9 User Records( 用 户 记 录, 即 行 记 录 ): 实 现 记 录 的 内 容 再 次 强 调,InnoDB 存 储 引 擎 表 总 是 B+ 村 索 引 组 织 的 Free Space( 空 闲 空 间 ): 指 空 闲 空 间, 同 样 也 是 个 链 表 数 据 结 构 当 一 条 记 录 被 删 除 后, 该 空 间 会 被 加 入 空 闲 链 表 中 Page Directory( 页 目 录 ): 页 目 录 存 放 了 记 录 的 相 对 位 置, 并 不 是 偏 移 量, 有 些 时 候 这 些 记 录 称 为 Slots( 槽 ),InnoDB 并 不 是 每 个 记 录 一 个 槽, 槽 是 一 个 稀 疏 目 录, 即 一 个 槽 中 可 能 属 于 多 个 记 录, 最 少 属 于 4 条 记 录, 最 多 属 于 8 条 记 录 需 要 牢 记 的 是,B+ 树 索 引 本 身 并 不 能 找 到 具 体 的 一 条 记 录,B+ 树 索 引 能 找 到 只 是 该 记 录 所 在 的 页 数 据 库 把 页 载 入 内 存, 然 后 通 过 Page Directory 再 进 行 二 叉 查 找 只 不 过 二 叉 查 找 的 时 间 复 杂 度 低, 同 时 内 存 中 的 查 找 很 快, 因 此 通 过 忽 略 了 这 部 分 查 找 所 用 的 时 间 File Trailer( 文 件 结 尾 信 息 ): 为 了 保 证 页 完 整 地 写 入 磁 盘 ( 如 写 过 程 的 磁 盘 损 坏 机 器 宕 机 等 ), 固 定 长 8 个 字 节 视 图 :Mysql 中 的 视 图 总 是 虚 拟 的 表, 本 身 不 支 持 物 化 视 图 但 是 通 过 一 些 其 他 技 巧 ( 如 触 发 器 ), 同 样 也 可 以 实 现 一 些 简 单 的 物 化 视 图 的 功 能 分 区 :Mysql 数 据 库 支 持 RANGE LIST HASH KEY COLUMNS 分 区, 并 且 可 以 使 用 HASH 或 KEY 来 进 行 子 分 区 mysql innodb 常 见 索 引 与 算 法 : B+ 树 索 引 :B+ 树 的 数 据 结 构 相 对 较 复 杂,B 代 表 的 是 balance 最 早 是 从 平 衡 二 叉 树 演 化 而 来, 但 B+ 树 并 不 是 一 个 二 叉 树, 对 其 较 详 细 的 介 绍 可 以 参 见 这 篇 文 章 : 由 于 B+ 树 索 引 的 高 扇 出 性, 因 此 在 数 据 库 中,B+ 树 的 高 度 一 般 都 在 2~3 层, 也 就 对 于 查 找 某 一 键 值 的 行 记 录, 最 多 只 要 2 到 3 次 IO, 现 在 一 般 的 磁 盘 每 秒 至 少 可 以 做 100 次 IO,2~3 次 的 IO 意 味 着 查 询 时 间 只 需 0.02~0.03 秒 数 据 库 中 的 B+ 索 引 可 以 分 为 聚 集 索 引 (clustered index) 和 辅 助 聚 集 索 引 (secondary index), 但 其 内 部 都 是 B+ 树 的, 即 高 度 平 衡 的, 叶 子 节 点 存 放 数 据 聚 集 索 引 : 由 于 聚 集 索 引 是 按 照 主 键 组 织 的, 所 以 每 一 张 表 只 能 有 一 个 聚 集 索 引, 每 个 数 据 页 都 通 过 双 向 链 表 进 行 连 接, 叶 子 节 点 存 放 一 整 行 的 信 息, 所 以 查 询 优 化 器 更 倾 向 走 聚 集 索 引 此 外, 对 于 聚 集 索 引 的 存 储 是 逻 辑 上 连 续 的 所 以, 聚 集 索 引 对 于 主 键 的 排 序 查 找 和 范 围 查 找 速 度 非 常 快 辅 助 索 引 : 也 叫 非 聚 集 索 引, 叶 子 节 点 不 存 全 部 数 据, 主 要 存 键 值 及 一 个 boomark( 其 实 就 是 聚 集 索 引 的 键 ) 告 诉 InnoDB 哪 里 可 以 找 到 与 索 引 相 对 应 的 行 数 据, 如 一 个 高 度 为 3 的 辅 助 索 引 和 一 个 高 度 为 3 的 聚 集 索 引, 若 根 据 辅 助 索 引 来 查 询 行 记 录, 一 共 需 要 6 次 IO 另 外 辅 助 索 引 可 以 有 多 个

10 索 引 的 使 用 原 则 : 高 选 择 取 出 表 中 的 少 部 分 数 据 ( 也 称 为 唯 一 索 引 ) 一 般 取 出 的 数 据 量 超 过 表 中 数 据 的 20%, 优 化 器 不 会 使 用 索 引, 而 进 行 全 表 扫 描 如 对 于 性 别 等 字 段 是 没 有 意 义 的 联 合 索 引 : 也 称 复 合 索 引, 是 在 多 列 (>=2) 上 建 立 的 索 引 Innodb 中 的 复 合 索 引 也 是 b+ tree 结 构 索 引 的 数 据 包 含 多 列 (col1, col2, col3 ), 在 索 引 中 依 次 按 照 col1, col2, col3 排 序 如 (1, 2), (1, 3),(2,0) 使 用 复 合 索 引 要 充 分 利 用 最 左 前 缀 原 则, 顾 名 思 义, 就 是 最 左 优 先 如 创 建 索 引 ind_col1_col2(col1, col2), 那 么 在 查 询 where col1 = xxx and col2 = xx 或 者 where col1 = xxx 都 可 以 走 ind_col1_col2 索 引, 但 where col2=**** 是 走 不 到 索 引 的 在 创 建 多 列 索 引 时, 要 根 据 业 务 需 求,where 子 句 中 使 用 最 频 繁 且 过 滤 效 果 好 的 的 一 列 放 在 最 左 边 哈 希 索 引 : 哈 希 算 法 也 是 比 较 常 见 的 算 法,mysql innodb 中 使 用 了 比 较 常 见 的 链 地 址 法 进 行 去 重 此 外 上 面 已 经 提 及,innoDB 中 的 hash 是 自 适 应 的, 什 么 时 候 使 用 hash 是 系 统 决 定 的, 无 法 进 行 人 工 设 置 二 分 查 找 法 : 这 个 算 法 比 较 常 见, 这 里 就 不 多 提 及 了 在 InnoDB 中, 每 页 Page Directory 中 的 槽 是 按 照 主 键 的 顺 序 存 放 的, 对 于 某 一 条 具 体 记 录 的 查 询 是 通 过 对 Page Directory 进 行 二 分 查 找 得 到 的 mysql innodb 中 的 锁 InnoDB 存 储 引 擎 锁 的 实 现 和 Oracle 非 常 类 似, 提 供 一 致 性 的 非 锁 定 读 行 级 锁 支 持 行 级 锁 没 有 相 关 的 开 销, 可 以 同 时 得 到 并 发 性 和 一 致 性 InnoDB 存 储 引 擎 实 现 了 如 下 两 种 标 准 的 行 级 锁 : 共 享 锁 (S Lock): 允 许 事 务 读 一 行 数 据 ; 排 他 锁 (X Lock): 允 许 事 务 删 除 或 者 更 新 一 行 数 据 当 一 个 事 务 已 经 获 得 了 行 r 的 共 享 锁, 那 么 另 外 的 事 务 可 以 立 即 获 得 行 r 的 共 享 锁, 因 为 读 取 没 有 改 变 行 r 的 数 据, 我 们 称 这 种 情 况 为 锁 兼 容 但 如 果 有 事 务 想 获 得 行 r 的 排 他 锁, 则 它 必 须 等 待 事 务 释 放 行 r 上 的 共 享 锁 这 种 情 况 称 为 锁 不 兼 容 在 InnoDB Plugin 之 前, 只 能 通 过 SHOW FULL PROCESSLIST,SHOW ENGINE INOODB STATUS 等 命 令 来 查 看 当 前 的 数 据 库 请 求, 然 后 再 判 断 当 前 事 务 中 的 锁 的 情 况 新 版 本 的 InnoDB Plugin 中, 在 INFORMATION_SCHEMA 架 构 下 添 加 了 INNODB_TRX INNODB_LOCKS InnoDB_LOCK_WAITS 通 过 这 三 张 表, 可 以 更 简 单 地 监 控 当 前 的 事 务 并 分 析 可 能 存 在 的 锁 的 问 题 INNODB_TRX 由 8 个 字 段 组 成 :

11 trx_id:innodb 存 储 引 擎 内 部 唯 一 的 事 务 ID trx_state: 当 前 事 务 的 状 态 trx_started: 事 务 的 开 始 时 间 trx_requested_lock_id: 等 待 事 务 的 锁 ID 如 trx_state 的 状 态 为 LOCK WAIT, 那 么 该 值 代 表 当 前 的 等 待 之 前 事 务 占 用 锁 资 源 的 ID. 若 trx_state 不 是 LOCK WAIT, 则 该 值 为 NULL trx_wait_started: 事 务 等 待 开 始 的 时 间 trx_weight: 事 务 的 权 重, 反 映 了 一 个 事 务 修 改 和 锁 住 的 行 数 在 InnoDB 存 储 引 擎 中, 当 发 生 死 锁 需 要 回 滚 时,InnoDB 存 储 会 选 择 该 值 最 小 的 进 行 回 滚 trx_mysql_thread_id:mysql 中 的 线 程 ID,SHOW PROCESSLIST 显 示 的 结 果 trx_query: 事 务 运 行 的 sql 语 句 通 过 select * from infomation_schema.innodb_trx; 可 查 看 INNODB_LOCKS 表, 该 表 由 如 下 字 段 组 成 : lock_id: 锁 的 ID lock_trx_id: 事 务 ID lock_mode: 锁 的 模 式 lock_type: 锁 的 类 型, 表 锁 还 是 行 锁 lock_table: 要 加 锁 的 表 lock_index: 锁 的 索 引 lock_space:innodb 存 储 引 擎 表 空 间 的 ID 号 lock_page: 被 锁 住 的 页 的 数 量 若 是 表 锁, 则 该 值 为 NULL lock_rec: 被 锁 住 的 行 的 数 量 若 是 表 锁, 则 该 值 为 NULL lock_data: 被 锁 住 的 行 的 主 键 值 当 是 表 锁 时, 该 值 为 NULL 通 过 select * from information_schema.innodb_lock; 可 查 看 INNODB_LOCK_WAIT 由 4 个 字 段 组 成 : requesting_trx_id: 申 请 锁 资 源 的 事 务 ID requesting_lock_id: 申 请 的 锁 的 ID blocking_trx_id: 阻 塞 的 锁 的 ID 通 过 select * from information_schema.innodb_lock_waits; 可 查 看 一 致 性 的 非 锁 定 读 :InnoDB 存 储 引 擎 通 过 行 多 版 本 控 制 的 方 式 来 读 取 当 前 执 行 时 间 数 据 库 中 行 的 数 据 如 果 读 取 的 行 正 在 执 行 Delete update 操 作, 这 时 读 取 操 作 不 会 因 此 而 会 等 待 行 上 锁 的 释 放, 相 反,InnoDB 存 储 引 擎 会 去 读 取 行 的 一 个 快 照 数 据 快 照 数 据 是 指 该 行 之 前 版 本 的 数 据, 该 实 现 是 通 过 Undo 段 来 实 现 而 Undo 用 来 事 务 中 回 滚 数 据, 因 此 快 照 本 身 是 没 有 额 外 开 销 的 此 外, 快 照 数 据 是 不 需 要 上 锁 的, 因 为 没 有 必 要 对 历 史 的 数 据 进 行 修 改 一 个 行 可 能 有 不 止 一 个 快 照 数 据, 所 以 称 这 种 技 术 为 行 多 版 本 技 术 由 此 带 来 并 发 控 制, 称 之 为 多 版 本 并 发 控 制 (Multi VersionConcurrency Control, MVCC) 事 务 的 隔 离 级 别 :Read uncommitted Read committed Repeatable read serializable 在 Read Committed 和 Repeatable Read 下,InnoDB 存 储 引 擎 使 用 非 锁 定 一 致 性 读 然 而, 对 于 快 照 的 定 义 却 不 同 在 Read Committed 事 务 隔 离 级 别 下, 对 于 快 照 数 据, 非 一 致 性 读

12 总 是 读 取 被 锁 定 行 的 最 新 一 份 快 照 数 据 在 Repeatable 事 务 隔 离 级 别 下, 对 于 快 照 数 据, 非 一 致 性 读 总 是 读 取 事 务 开 始 时 的 行 数 据 版 本 锁 的 算 法 : Record Lock: 单 行 记 录 上 的 锁 Gap Lock: 间 隙 锁, 锁 定 一 个 范 围, 但 不 包 含 记 录 本 身 Next-Key Lock:Gap Lock + Record Lock, 锁 定 一 个 范 围, 并 且 锁 定 记 录 本 身 更 加 详 细 的 介 绍 可 以 参 见 这 篇 blog, 锁 的 问 题 : 丢 失 更 新 : 经 典 的 数 据 库 问 题, 当 两 个 或 多 个 事 务 选 择 同 一 行, 然 后 基 于 最 初 选 定 的 值 更 新 该 行 时, 会 发 生 丢 失 更 新 问 题 每 个 事 务 都 不 知 道 其 它 事 务 的 存 在 最 后 的 更 新 将 重 写 由 其 它 事 务 所 做 的 更 新, 这 将 导 致 数 据 丢 失 例 : 事 务 A 和 事 务 B 同 时 修 改 某 行 的 值, 1. 事 务 A 将 数 值 改 为 1 并 提 交 2. 事 务 B 将 数 值 改 为 2 并 提 交 这 时 数 据 的 值 为 2, 事 务 A 所 做 的 更 新 将 会 丢 失 解 决 办 法 : 事 务 并 行 变 串 行 操 作, 对 更 新 操 作 加 排 他 锁

13 脏 读 : 一 个 事 务 读 到 另 一 个 事 务 未 提 交 的 更 新 数 据, 即 读 到 脏 数 据 例 : 1.Mary 的 原 工 资 为 1000, 财 务 人 员 将 Mary 的 工 资 改 为 了 8000( 但 未 提 交 事 务 ) 2.Mary 读 取 自 己 的 工 资, 发 现 自 己 的 工 资 变 为 了 8000, 欢 天 喜 地! 3. 而 财 务 发 现 操 作 有 误, 回 滚 了 事 务,Mary 的 工 资 又 变 为 了 1000, 像 这 样,Mary 记 取 的 工 资 数 8000 是 一 个 脏 数 据 解 决 办 法 : 脏 读 只 有 在 事 务 隔 离 级 别 是 Read Uncommitted 的 情 况 下 才 会 出 现, innodb 默 认 隔 离 级 别 是 Repeatable Read, 所 以 生 产 环 境 下 不 会 出 现 脏 读 不 可 重 复 读 : 在 同 一 个 事 务 中, 多 次 读 取 同 一 数 据, 返 回 的 结 果 有 所 不 同 换 句 话 说 就 是, 后 续 读 取 可 以 读 到 另 一 个 事 务 已 提 交 的 更 新 数 据 相 反 " 可 重 复 读 " 在 同 一 事 务 多 次 读 取 数 据 时, 能 够 保 证 所 读 数 据 一 样, 也 就 是 后 续 读 取 不 能 读 到 另 一 事 务 已 提 交 的 更 新 数 据 脏 读 和 不 可 重 复 读 的 主 要 区 别 在 于, 脏 读 是 读 到 未 提 交 的 数 据, 不 可 重 复 读 是 读 到 已 提 交 的 数 据 例 : 1. 在 事 务 1 中,Mary 读 取 了 自 己 的 工 资 为 1000, 操 作 并 没 有 完 成 2. 在 事 务 2 中, 这 时 财 务 人 员 修 改 了 Mary 的 工 资 为 2000, 并 提 交 了 事 务. 3. 在 事 务 1 中,Mary 再 次 读 取 自 己 的 工 资 时, 工 资 变 为 了 2000 解 决 办 法 : 读 到 已 提 交 的 数 据, 一 般 数 据 库 是 可 接 受 的, 因 此 事 务 隔 离 级 别 一 般 设 为 Read Committed Mysql InnoDB 通 过 Next-Key Lock 算 法 避 免 不 可 重 复 读, 默 认 隔 离 级 别 为 Repeatable Read mysql innodb 中 的 事 务 事 务 的 四 个 特 性 : 原 子 性 一 致 性 隔 离 性 持 久 性 隔 离 性 通 过 锁 实 现, 原 子 性 一 致 性 持 久 性 通 过 数 据 库 的 redo 和 undo 来 完 成 重 做 日 志 记 录 了 事 务 的 行 为, 通 过 redo 实 现, 保 证 了 事 务 的 完 整 性, 但 事 务 有 时 还 需 要 撤 销, 这 时 就 需 要 产 生 undo undo 和 redo 正 好 相 反, 对 于 数 据 库 进 行 修 改 时, 数 据 库 不 但 会 产 生 redo, 而 且 还 会 产 生 一 定 的 undo, 即 使 执 行 的 事 务 或 语 句 由 于 某 种 原 因 失 败 了, 或 者 如 果 用 一 条 rollback 语 句 请 求 回 滚, 就 可 以 用 这 些 undo 信 息 将 数 据 回 滚 到 修 改 之 前 的 样 子 与 redo 不 同 的 是,redo 存 放 在 重 做 日 志 文 件 中,undo 存 放 在 数 据 库 内 部 的 一 个 特 殊 段 (segment) 中, 这 称 为 undo 段 (undo segment),undo 段 位 于 共 享 表 空 间 内 还 有 一 点 重 要 的 是,undo 记 录 的 是 与 事 务 操 作 相 反 的 逻 辑 操 作, 如 insert undo 记 录 一 个 delete, 所 以 undo 只 是 逻 辑 地 将 数 据 库 恢 复 成 事 务 开 始 前 的 样 子 如 :insert 10 万 行 的 数 据, 可 能 导 致 表 空 间 增 大, 回 滚 后, 表 空 间 不 会 减 小 回 去

14 MySQL 数 据 类 型 MySQL 存 储 引 擎 MySQL Innodb 日 志 机 制 深 入 分 析 1.1. Log & Checkpoint Innodb 的 事 务 日 志 是 指 Redo log, 简 称 Log, 保 存 在 日 志 文 件 ib_logfile* 里 面 Innodb 还 有 另 外 一 个 日 志 Undo log, 但 Undo log 是 存 放 在 共 享 表 空 间 里 面 的 (ibdata* 文 件 ) 由 于 Log 和 Checkpoint 紧 密 相 关, 因 此 将 这 两 部 分 合 在 一 起 分 析 名 词 解 释 :LSN, 日 志 序 列 号,Innodb 的 日 志 序 列 号 是 一 个 64 位 的 整 型 写 入 机 制 Log 写 入 LSN 实 际 上 对 应 日 志 文 件 的 偏 移 量, 新 的 LSN= 旧 的 LSN + 写 入 的 日 志 大 小 举 例 如 下 : LSN=1G, 日 志 文 件 大 小 总 共 为 600M, 本 次 写 入 512 字 节, 则 实 际 写 入 操 作 为 : l 求 出 偏 移 量 : 由 于 LSN 数 值 远 大 于 日 志 文 件 大 小, 因 此 通 过 取 余 方 式, 得 到 偏 移 量 为 400M; l 写 入 日 志 : 找 到 偏 移 400M 的 位 置, 写 入 512 字 节 日 志 内 容, 下 一 个 事 务 的 LSN 就 是 ; Checkpoint 写 入 Innodb 实 现 了 Fuzzy Checkpoint 的 机 制, 每 次 取 到 最 老 的 脏 页, 然 后 确 保 此 脏 页 对 应 的 LSN 之 前 的 LSN 都 已 经 写 入 日 志 文 件, 再 将 此 脏 页 的 LSN 作 为 Checkpoint 点 记 录 到 日 志 文 件, 意 思 就 是 此 LSN 之 前 的 LSN 对 应 的 日 志 和 数 据 都 已 经 写 入 磁 盘 文 件 恢 复 数 据 文 件 的 时 候, Innodb 扫 描 日 志 文 件, 当 发 现 LSN 小 于 Checkpoint 对 应 的 LSN, 就 认 为 恢 复 已 经 完 成 Checkpoint 写 入 的 位 置 在 日 志 文 件 开 头 固 定 的 偏 移 量 处, 即 每 次 写 Checkpoint 都 覆 盖 之 前 的 Checkpoint 信 息 管 理 机 制 由 于 Checkpoint 和 日 志 紧 密 相 关, 将 日 志 和 Checkpoint 一 起 说 明, 详 细 的 实 现 机 制 如 下 :

15 如 上 图 所 示,Innodb 的 一 条 事 务 日 志 共 经 历 4 个 阶 段 : 1) 创 建 阶 段 : 事 务 创 建 一 条 日 志 ; 2) 日 志 刷 盘 : 日 志 写 入 到 磁 盘 上 的 日 志 文 件 ; 3) 数 据 刷 盘 : 日 志 对 应 的 脏 页 数 据 写 入 到 磁 盘 上 的 数 据 文 件 ; 4) 写 CKP: 日 志 被 当 作 Checkpoint 写 入 日 志 文 件 ; 对 应 这 4 个 阶 段, 系 统 记 录 了 4 个 日 志 相 关 的 信 息, 用 于 其 它 各 种 处 理 使 用 : Log sequence number(lsn1): 当 前 系 统 LSN 最 大 值, 新 的 事 务 日 志 LSN 将 在 此 基 础 上 生 成 (LSN1+ 新 日 志 的 大 小 ); Log flushed up to(lsn2): 当 前 已 经 写 入 日 志 文 件 的 LSN; Oldest modified data log(lsn3): 当 前 最 旧 的 脏 页 数 据 对 应 的 LSN, 写 Checkpoint 的 时 候 直 接 将 此 LSN 写 入 到 日 志 文 件 ; Last checkpoint at(lsn4): 当 前 已 经 写 入 Checkpoint 的 LSN;

16 对 于 系 统 来 说, 以 上 4 个 LSN 是 递 减 的, 即 : LSN1>=LSN2>=LSN3>=LSN4. 具 体 的 样 例 如 下 ( 使 用 show innodb status \G 命 令 查 看,Oldest modified data log 没 有 显 示 ): 保 护 机 制 Innodb 的 数 据 并 不 是 实 时 写 盘 的, 为 了 避 免 宕 机 时 数 据 丢 失, 保 证 数 据 的 ACID 属 性,Innodb 至 少 要 保 证 数 据 对 应 的 日 志 不 能 丢 失 对 于 不 同 的 情 况,Innodb 采 取 不 同 的 对 策 : 1) 宕 机 导 致 日 志 丢 失 Innodb 有 日 志 刷 盘 机 制, 可 以 通 过 innodb_flush_log_at_trx_commit 参 数 进 行 控 制 ; 2) 日 志 覆 盖 导 致 日 志 丢 失 Innodb 日 志 文 件 大 小 是 固 定 的, 写 入 的 时 候 通 过 取 余 来 计 算 偏 移 量, 这 样 存 在 两 个 LSN 写 入 到 同 一 位 置 的 可 能, 后 面 写 的 把 前 面 写 得 就 覆 盖 了, 以 写 入 机 制 章 节 的 样 例 为 例,LSN= 和 LSN= 两 个 日 志 的 偏 移 量 是 相 同 的 了 这 种 情 况 下, 为 了 保 证 数 据 一 致 性, 必 须 要 求 LSN= 对 应 的 脏 页 数 据 都 已 经 刷 到 磁 盘 中, 也 就 是 要 求 Last checkpoint 对 应 的 LSN 一 定 要 大 于 , 否 则 覆 盖 后 日 志 也 没 有 了, 数 据 也 没 有 刷 盘, 一 旦 宕 机, 数 据 就 丢 失 了 为 了 解 决 第 二 种 情 况 导 致 数 据 丢 失 的 问 题,Innodb 实 现 了 一 套 日 志 保 护 机 制, 详 细 实 现 如 下 : 上 图 中, 直 线 代 表 日 志 空 间 (Log cap, 约 等 于 日 志 文 件 总 大 小 *0.8,0.8 是 一 个 安 全 系 数 ),Ckp age 和 Buf age 是 两 个 浮 动 的 点,Buf async Buf sync Ckp async Ckp sync 是 几 个 固 定 的 点 各 个 概 念 的 含 义 如 下 :

17 概 念 计 算 含 义 Ckp age LSN1- LSN4 还 没 有 做 Checkpoint 的 日 志 范 围, 若 Ckp age 超 过 日 志 空 间, 说 明 被 覆 盖 的 日 志 (LSN1-LSN4-Log cap) 对 应 日 志 和 数 据 可 能 还 没 有 刷 到 磁 盘 上 Buf age LSN1- LSN3 还 没 有 将 脏 页 刷 盘 的 日 志 的 范 围, 若 Buf age 超 过 日 志 空 间, 说 明 被 覆 盖 的 日 志 (LSN1-LSN3-Log cap) 对 应 数 据 肯 定 还 没 有 刷 到 磁 盘 上 Buf async 日 志 空 间 大 小 * 7/8 强 制 将 Buf age-buf async 的 脏 页 刷 盘, 此 时 事 务 还 可 以 继 续 执 行, 所 以 为 async, 对 事 务 的 执 行 速 度 没 有 直 接 影 响 ( 有 间 接 影 响, 例 如 CPU 和 磁 盘 更 忙 了, 事 务 的 执 行 速 度 可 能 受 到 影 响 ) Buf sync 日 志 空 间 大 小 * 15/16 强 制 将 2*(Buf age-buf async) 的 脏 页 刷 盘, 此 时 事 务 停 止 执 行, 所 以 为 sync, 由 于 有 大 量 的 脏 页 刷 盘, 因 此 阻 塞 的 时 间 比 Ckp sync 要 长 Ckp async 日 志 空 间 大 小 * 31/32 强 制 写 Checkpoint, 此 时 事 务 还 可 以 继 续 执 行, 所 以 为 async, 对 事 务 的 执 行 速 度 没 有 影 响 ( 间 接 影 响 也 不 大, 因 为 写 Checkpoint 的 操 作 比 较 简 单 ) Ckp sync 日 志 空 间 大 小 * 64/64 强 制 写 Checkpoint, 此 时 事 务 停 止 执 行, 所 以 为 sync, 但 由 于 写 Checkpoint 的 操 作 比 较 简 单, 即 使 阻 塞, 时 间 也 很 短 当 事 务 执 行 速 度 大 于 脏 页 刷 盘 速 度 时,Ckp age 和 Buf age 会 逐 步 增 长, 当 达 到 async 点 的 时 候, 强 制 进 行 脏 页 刷 盘 或 者 写 Checkpoint, 如 果 这 样 做 还 是 赶 不 上 事 务 执 行 的 速 度, 则 为 了 避 免 数 据 丢 失, 到 达 sync 点 的 时 候, 会 阻 塞 其 它 所 有 的 事 务, 专 门 进 行 脏 页 刷 盘 或 者 写 Checkpoint 因 此 从 理 论 上 来 说, 只 要 事 务 执 行 速 度 大 于 脏 页 刷 盘 速 度, 最 终 都 会 触 发 日 志 保 护 机 制, 进 而 将 事 务 阻 塞, 导 致 MySQL 操 作 挂 起 由 于 写 Checkpoint 本 身 的 操 作 相 比 写 脏 页 要 简 单, 耗 费 时 间 也 要 少 得 多, 且 Ckp sync 点 在 Buf sync 点 之 后, 因 此 绝 大 部 分 的 阻 塞 都 是 阻 塞 在 了 Buf sync 点, 这 也 是 当 事 务 阻 塞 的 时 候, IO 很 高 的 原 因, 因 为 这 个 时 候 在 不 断 的 刷 脏 页 数 据 到 磁 盘 例 如 如 下 截 图 的 日 志 显 示 了 很 多 事 务 阻 塞 在 了 Buf sync 点 :

18 附 注 :Innodb 的 日 志 保 护 机 制 实 现 可 以 参 考 log0log.c 文 件 的 void log_check_margins(void) 函 数 MySQL 日 志 (Oracle) 关 于 redo log 和 undo log 这 两 个 概 念 redo log 重 做 日 志 undo log 撤 消 日 志 重 做 日 志 : 每 当 有 操 作 执 行 前, 将 数 据 真 正 更 改 时, 先 前 相 关 操 作 写 入 重 做 日 志 这 样 当 断 电, 或 者 一 些 意 外, 导 致 后 续 任 务 无 法 完 成 时, 系 统 恢 复 后, 可 以 继 续 完 成 这 些 更 改 撤 消 日 志 : 当 一 些 更 改 在 执 行 一 半 时, 发 生 意 外, 而 无 法 完 成, 则 可 以 根 据 撤 消 日 志 恢 复 到 更 改 之 前 的 壮 态 网 上 找 到 一 些 解 说 : 以 便 以 后 自 己 参 考 有 两 个 概 念 : 前 滚 与 回 退 比 如 某 一 时 刻 数 据 库 DOWN 机 了, 有 两 个 事 务, 一 个 事 务 已 经 提 交, 另 一 个 事 务 正 在 处 理 数 据 库 重 启 的 时 候 就 要 根 据 日 志 进 行 前 滚 及 回 退, 把 已 提 交 事 务 的 更 改 写 到 数 据 文 件, 未 提 交 事 务 的 更 改 恢 复 到 事 务 开 始 前 的 状 态 redo- undo-datafile insert 一 条 记 录 时, 表 跟 undo 的 信 息 都 会 放 进 redo 中, 在 commit 或 之 前, redo 的 信 息 会 放 进 硬 盘 上. 故 障 时, redo 便 可 恢 复 那 些 已 经 commit 了 的 数 据. redo 每 次 操 作 都 先 记 录 到 redo 日 志 中, 当 出 现 实 例 故 障 ( 像 断 电 ), 导 致 数 据 未 能 更 新 到 数 据 文 件, 则 数 据 库 重 启 时 须 redo, 重 新 把 数 据 更 新 到 数 据 文 件 undo 记 录 更 改 前 的 一 份 copy, 但 你 系 统 rollback 时, 把 这 份 copy 重 新 覆 盖 到 原 来 的 数 据

19 redo 记 录 所 有 操 作, 用 于 恢 复 (redo records all the database transaction used for recovery) undo 记 录 所 有 的 前 印 象, 用 于 回 滚 (undo is used to store uncommited data infor used for rollback) redo 已 递 交 的 事 务, 实 例 恢 复 时 要 写 到 数 据 文 件 去 的 undo 未 递 交 的 事 务. redo 的 原 因 是 : 每 次 commit 时, 将 数 据 的 修 改 立 即 写 到 online redo 中, 但 是 并 不 一 定 同 时 将 该 数 据 的 修 改 写 到 数 据 文 件 中 因 为 该 数 据 已 经 提 交, 但 是 只 存 在 联 机 日 志 文 件 中, 所 以 在 恢 复 时 需 要 将 数 据 从 联 机 日 志 文 件 中 找 出 来, 重 新 应 用 一 下, 使 已 经 更 改 数 据 在 数 据 文 件 中 也 改 过 来! undo 的 原 因 是 : 在 oracle 正 常 运 行 时, 为 了 提 高 效 率, 加 入 用 户 还 没 有 commit, 但 是 空 闲 内 存 不 多 时, 会 由 DBWR 进 程 将 脏 块 写 入 到 数 据 文 件 中, 以 便 腾 出 宝 贵 的 内 存 供 其 它 进 程 使 用 这 就 是 需 要 UNDO 的 原 因 因 为 还 没 有 发 出 commit 语 句, 但 是 oracle 的 dbwr 进 程 已 经 将 没 有 提 交 的 数 据 写 到 数 据 文 件 中 去 了 undo 也 是 也 是 datafile, 可 能 dirty buffer 没 有 写 回 到 磁 盘 里 面 去 只 有 先 redo apply 成 功 了, 才 能 保 证 undo datafile 里 面 的 东 西 都 是 正 确 的, 然 后 才 能 rollback 做 undo 的 目 的 是 使 系 统 恢 复 到 系 统 崩 溃 前 ( 关 机 前 ) 的 状 态, 再 进 行 redo 是 保 证 系 统 的 一 致 性. 不 做 undo, 系 统 就 不 会 知 道 之 前 的 状 态,redo 就 无 从 谈 起 所 以 instance crash recovery 的 时 候 总 是 先 rollforward, 再 rollback undo 回 退 段 中 的 数 据 是 以 回 退 条 目 方 式 存 储 回 退 条 目 = 块 信 息 ( 在 事 务 中 发 生 改 动 的 块 的 编 号 )+ 在 事 务 提 交 前 存 储 在 块 中 的 数 据 在 每 一 个 回 退 段 中 oracle 都 为 其 维 护 一 张 事 务 表 在 事 务 表 中 记 录 着 与 该 回 退 段 中 所 有 回 退 条 目 相 关 的 事 务 编 号 ( 事 务 SCN& 回 退 条 目 ) redo 重 做 记 录 由 一 组 变 更 向 量 组 成 每 个 变 更 变 量 中 记 录 了 事 务 对 数 据 库 中 某 个 块 所 做 的 修 改 当 用 户 提 交 一 条 commit 语 句 时,LGWR 进 程 会 立 刻 将 一 条 提 交 记 录 写 入 到 重 做 日 志 文 件 中, 然 后 再 开 始 写 入 与 该 事 务 相 关 的 重 做 信 息 # 事 务 提 交 成 功 后,Oracle 将 为 该 事 备 生 成 一 个 系 统 变 更 码 (SCN) 事 务 的 SCN 将 同 时 记 录 在 它 的 提 交 记 录 和 重 做 记 录 中 commit 提 交 事 务 前 完 成 的 工 作 :

20 在 SGA 区 的 回 退 缓 存 中 生 成 该 事 务 的 回 退 条 目 在 回 退 条 目 中 保 存 有 该 事 务 所 修 改 的 数 据 的 原 始 版 本 在 SGA 区 的 重 做 日 志 缓 存 中 生 成 该 事 务 的 重 做 记 录 重 做 记 录 中 记 载 了 该 事 务 对 数 据 块 所 进 行 的 修 改, 并 且 还 记 载 了 对 回 退 段 中 的 数 据 块 所 进 行 的 修 改 缓 存 中 的 重 做 记 录 有 可 能 在 事 务 提 交 之 前 就 写 入 硬 盘 中 在 SGA 区 的 数 据 库 缓 丰 中 记 录 了 事 务 对 数 据 库 所 进 行 的 修 改 这 些 修 改 也 有 可 能 在 事 务 提 交 之 前 就 写 入 硬 盘 中 提 交 事 务 时 完 成 的 工 作 : 在 为 该 事 务 指 定 的 回 退 段 中 的 内 部 事 务 表 内 记 录 下 这 个 事 务 已 经 被 提 交, 并 且 生 成 一 个 惟 一 的 SCN 记 录 在 内 部 事 务 表 中, 用 于 惟 一 标 识 这 个 事 务 LGWR 后 进 进 程 将 SGA 区 重 做 日 志 缓 存 中 的 重 做 记 录 写 入 联 机 重 做 日 志 文 件 在 写 入 重 做 日 志 的 同 时 还 将 写 入 该 事 务 的 SCN Oracle 服 务 进 程 释 放 事 务 所 使 用 的 所 有 记 录 锁 与 表 锁 Oracle 通 知 用 户 事 务 提 交 完 成 Oracle 将 该 事 务 标 记 为 已 完 成 rollback 回 退 事 务 完 成 的 工 作 : Oracle 通 过 使 用 回 退 段 中 的 回 退 条 目, 撤 销 事 务 中 所 有 SQL 语 句 对 数 据 库 所 做 的 修 改 Oracle 服 务 进 程 释 放 事 务 所 使 用 的 所 有 锁 Oracle 通 知 事 务 回 退 成 功 Oracle 将 该 事 务 标 记 为 已 完 成 举 个 例 子 : insert into a(id) values(1);(redo) 这 条 记 录 是 需 要 回 滚 的 回 滚 的 语 句 是 delete from a where id = 1;(undo) 试 想 想 看 如 果 没 有 做 insert into a(id) values(1);(redo) 那 么 delete from a where id = 1;(undo) 这 句 话 就 没 有 意 义 了 现 在 看 下 正 确 的 恢 复 : 先 insert into a(id) values(1);(redo) 然 后 delete from a where id = 1;(undo) 系 统 就 回 到 了 原 先 的 状 态, 没 有 这 条 记 录 了 MySQL 主 从 复 制 MySQL 丢 数 据 及 主 从 数 据 不 一 致 的 场 景 随 着 对 MySQL 的 学 习, 发 现 了 MySQL 的 很 多 问 题, 最 重 要 的 就 是 丢 数 据 的 问 题 对 于 丢 数 据 问 题, 我 们 应 该 了 解 丢 数 据 的 场 景, 这 样 在 以 后 的 学 习 中 多 考 虑 如 何 去 避 免 及 解 决 这 些 问 题

21 1.MySQL 数 据 库 层 丢 数 据 场 景 本 节 我 们 主 要 介 绍 一 下 在 存 储 引 擎 层 上 是 如 何 会 丢 数 据 的 1.1.InnoDB 丢 数 据 InnoDB 支 持 事 务, 同 Oracle 类 似, 事 务 提 交 需 要 写 redo undo 采 用 日 志 先 行 的 策 略, 将 数 据 的 变 更 在 内 存 中 完 成, 并 且 将 事 务 记 录 成 redo, 顺 序 的 写 入 redo 日 志 中, 即 表 示 该 事 务 已 经 完 成, 就 可 以 返 回 给 客 户 已 提 交 的 信 息 但 是 实 际 上 被 更 改 的 数 据 还 在 内 存 中, 并 没 有 刷 新 到 磁 盘, 即 还 没 有 落 地, 当 达 到 一 定 的 条 件, 会 触 发 checkpoint, 将 内 存 中 的 数 据 (page) 合 并 写 入 到 磁 盘, 这 样 就 减 少 了 离 散 写 IOPS, 提 高 性 能 在 这 个 过 程 中, 如 果 服 务 器 宕 机 了, 内 存 中 的 数 据 丢 失, 当 重 启 后, 会 通 过 redo 日 志 进 行 recovery 重 做 确 保 不 会 丢 失 数 据 因 此 只 要 redo 能 够 实 时 的 写 入 到 磁 盘,InnoDB 就 不 会 丢 数 据 先 来 看 一 下 innodb_flush_log_at_trx_commit 这 个 参 数 : = 0 : 每 秒 write cache & flush disk = 1 : 每 次 commit 都 write cache & flush disk = 2 : 每 次 commit 都 write cache, 然 后 根 据 innodb_flush_log_at_timeout( 默 认 为 1s) 时 间 flush disk 从 这 三 个 配 置 来 看, 显 然 innodb_flush_log_at_trx_commit=1 最 为 安 全, 因 为 每 次 commit 都 保 证 redo 写 入 了 disk 但 是 这 种 方 式 性 能 对 DML 性 能 来 说 比 较 低, 在 我 们 的 测 试 中 发 现, 如 果 设 置 为 2,DML 性 能 要 比 设 置 为 1 高 10 倍 左 右 为 什 么 oracle 的 实 时 写 要 比 innodb 的 实 时 写 性 能 更 好? 线 程 与 进 程? 后 面 还 需 要 研 究 大 家 可 以 考 虑 一 下 0 与 2 的 区 别? 在 某 些 DML 操 作 频 繁 的 场 景 下, 库 的 innodb_flush_log_at_trx_commit 需 要 设 置 为 2, 这 样 就 存 在 丢 数 据 的 风 险 : 当 服 务 器 出 现 宕 机, 重 启 后 进 行 crash recovery 则 会 丢 失 innodb_flush_log_at_timeout 秒 内 的 数 据 PS: 当 开 启 了 内 部 XA 事 务 ( 默 认 开 启 ), 且 开 启 binlog, 情 况 稍 有 不 一 样, 后 面 会 进 行 介 绍 1.2.MyISAM 丢 数 据 MyISAM 存 储 引 擎 在 我 们 的 生 产 中 用 的 并 不 多, 但 是 系 统 的 数 据 字 典 表 元 数 据 等 都 是 存 储 在 MyISAM 引 擎 下 MyISAM 不 支 持 事 务, 且 没 有 data cache, 所 有 DML 操 作 只 写 到 OS cache 中,flush disk 操 作 均 由 OS 来

22 完 成, 因 此 如 果 服 务 器 宕 机, 则 这 部 分 数 据 肯 定 会 丢 失 2. 主 从 复 制 不 一 致 主 从 复 制 原 理 :MySQL 主 库 在 事 务 提 交 时 写 binlog, 并 通 过 sync_binlog 参 数 来 控 制 binlog 刷 新 到 磁 盘 落 地, 而 备 库 通 过 IO 线 程 从 主 库 读 取 binlog, 并 记 录 到 本 地 的 relay log 中, 由 本 地 的 SQL 线 程 再 将 relay log 的 数 据 应 用 到 本 地 数 据 库, 如 下 图 所 示 : 从 上 图 我 们 可 以 看 到, 在 主 从 环 境 中, 增 加 了 binlog, 这 就 增 加 了 环 境 的 复 杂 性, 因 此 也 增 加 了 丢 数 据 以 及 数 据 不 一 致 可 能 在 分 析 这 些 丢 数 据 的 可 能 性 之 前, 我 们 先 了 解 一 下 binlog 的 刷 新 机 制 以 及 MySQL 的 内 部 XA 事 务 是 如 何 保 证 binlog 与 redo 的 一 致 性 的 2.1.binlog 刷 新 机 制 master 写 binlog 与 innodb 引 擎 写 redo 类 似, 也 有 参 数 控 制 :sync_binlog = 0 : 表 示 MySQL 不 控 制 binlog 的 刷 新, 由 文 件 系 统 自 己 控 制 它 的 缓 存 的 刷 新 > 0 : 表 示 每 sync_binlog 次 事 务 提 交,MySQL 调 用 文 件 系 统 的 刷 新 操 作 将 缓 存 刷 下 去 其 中 最 安 全 的 就 是 =1, 表 示 每 次 事 务 提 交,MySQL 都 会 把 binlog 缓 存 刷 下 去, 这 样 在 掉 电 等 情 况 下, 系 统 才 有 可 能 丢 失 1 个 事 务 的 数 据 当 sync_binlog 设 置 为 1, 对 系 统 的 IO 消 耗 也 是 非 常 大 的

23 2.2. 内 部 XA 事 务 原 理 MySQL 的 存 储 引 擎 与 MySQL 服 务 层 之 间, 或 者 存 储 引 擎 与 存 储 引 擎 之 间 的 分 布 式 事 务, 称 之 为 内 部 XA 事 务 最 为 常 见 的 内 部 XA 事 务 存 在 与 binlog 与 InnoDB 存 储 引 擎 之 间 在 事 务 提 交 时, 先 写 二 进 制 日 志, 再 写 InnoDB 存 储 引 起 的 redo 日 志 对 于 这 个 操 作 要 求 必 须 是 原 子 的, 即 需 要 保 证 两 者 同 时 写 入, 内 部 XA 事 务 机 制 就 是 保 证 两 者 的 同 时 写 入 XA 事 务 的 大 致 流 程 : 1. 事 务 提 交 后,InnoDB 存 储 引 擎 会 先 做 一 个 PREPARE 操 作, 将 事 务 的 XID 写 入 到 redo 日 志 中 2. 写 binlog 日 志 3. 再 将 该 事 务 的 commit 信 息 写 到 redo log 中 如 果 在 步 骤 1 和 步 骤 2 失 败 的 情 况 下, 整 个 事 务 会 回 滚, 如 果 在 步 骤 3 失 败 的 情 况 下,MySQL 数 据 库 在 重 启 后 会 先 检 查 准 备 的 UXID 事 务 是 否 已 经 提 交, 若 没 有, 则 在 存 储 引 擎 层 再 进 行 一 次 提 交 操 作 这 样 就 保 证 了 redo 与 binlog 的 一 致 性, 防 止 丢 数 据 2.3.master 库 写 redo binlog 不 实 时 丢 数 据 的 场 景 上 面 我 们 介 绍 了 MySQL 的 内 部 XA 事 务 流 程, 但 是 这 个 流 程 并 不 是 天 衣 无 缝 的,redo 的 ib_logfile 与 binlog 日 志 如 果 被 设 置 非 实 时 flush, 就 有 可 能 存 在 丢 数 据 的 情 况 1.redo 的 trx_prepare 未 写 入, 但 binlog 写 入, 造 成 从 库 数 据 量 比 主 库 多 2.redo 的 trx_prepare 与 commit 都 写 入 了, 但 是 binlog 未 写 入, 造 成 从 库 数 据 量 比 主 库 少 从 目 前 来 看, 只 能 牺 牲 性 能 去 换 取 数 据 的 安 全 性, 必 须 要 设 置 redo 和 binlog 为 实 时 刷 盘, 如 果 对 性 能 要 求 很 高, 则 考 虑 使 用 SSD

24 2.4.slave 库 写 redo binlog 不 实 时 丢 数 据 的 场 景 master 正 常, 但 是 slave 出 现 异 常 的 情 况 下 宕 机, 这 个 时 候 会 出 现 什 么 样 的 情 况 呢? 如 果 数 据 丢 失,slave 的 SQL 线 程 还 会 重 新 应 用 吗? 这 个 我 们 需 要 先 了 解 SQL 线 程 的 机 制 slave 读 取 master 的 binlog 日 志 后, 需 要 落 地 3 个 文 件 :relay log relay log info master info: relay log: 即 读 取 过 来 的 master 的 binlog, 内 容 与 格 式 与 master 的 binlog 一 致 relay log info: 记 录 SQL Thread 应 用 的 relay log 的 位 置 文 件 号 等 信 息 master info: 记 录 IO Thread 读 取 master 的 binlog 的 位 置 文 件 号 延 迟 等 信 息 因 此 如 果 当 这 3 个 文 件 如 果 不 及 时 落 地, 则 主 机 crash 后 会 导 致 数 据 的 不 一 致 在 MySQL 之 前,slave 记 录 的 master 信 息 以 及 slave 应 用 binlog 的 信 息 存 放 在 文 件 中, 即 master.info 与 relay-log.info 在 版 本 之 后, 允 许 记 录 到 table 中, 参 数 设 置 如 下 : master-info-repository = TABLE relay-log-info-repository = TABLE 6. 对 应 的 表 分 别 为 mysql.slave_master_info 与 mysql.slave_relay_log_info, 且 这 两 个 表 均 为 innodb 引 擎 表 master info 与 relay info 还 有 3 个 参 数 控 制 刷 新 : sync_relay_log: 默 认 为 10000, 即 每 次 sync_relay_log 事 件 会 刷 新 到 磁 盘 为 0 则 表 示 不 刷 新, 交 由 OS 的 cache 控 制

25 sync_master_info: 若 master-info-repository 为 FILE, 当 设 置 为 0, 则 每 次 sync_master_info 事 件 都 会 刷 新 到 磁 盘, 默 认 为 次 刷 新 到 磁 盘 ; 若 master-info-repository 为 TABLE, 当 设 置 为 0, 则 表 不 做 任 何 更 新, 设 置 为 1, 则 每 次 事 件 会 更 新 表 # 默 认 为 sync_relay_log_info: 若 relay_log_info_repository 为 FILE, 当 设 置 为 0, 交 由 OS 刷 新 磁 盘, 默 认 为 次 刷 新 到 磁 盘 ; 若 relay_log_info_repository 为 TABLE, 且 为 INNODB 存 储, 则 无 论 为 任 何 值, 则 都 每 次 evnet 都 会 更 新 表 建 议 参 数 设 置 如 下 : sync_relay_log = sync_master_info = sync_relay_log_info = master-info-repository = TABLE 12.

26 relay-log-info-repository = TABLE 15. 当 这 样 设 置, 导 致 调 用 fsync()/fdatasync() 随 着 master 的 事 务 的 增 加 而 增 加, 且 若 slave 的 binlog 和 redo 也 实 时 刷 新 的 话, 会 带 来 很 严 重 的 IO 性 能 瓶 颈 2.5.master 宕 机 后 无 法 及 时 恢 复 造 成 的 数 据 丢 失 当 master 出 现 故 障 后,binlog 未 及 时 传 到 slave, 或 者 各 个 slave 收 到 的 binlog 不 一 致 且 master 无 法 在 第 一 时 间 恢 复, 这 个 时 候 怎 么 办? 如 果 master 不 切 换, 则 整 个 数 据 库 只 能 只 读, 影 响 应 用 的 运 行 如 果 将 别 的 slave 提 升 为 新 的 master, 那 么 原 master 未 来 得 及 传 到 slave 的 binlog 的 数 据 则 会 丢 失, 并 且 还 涉 及 到 下 面 2 个 问 题 1. 各 个 slave 之 间 接 收 到 的 binlog 不 一 致, 如 果 强 制 拉 起 一 个 slave, 则 slave 之 间 数 据 会 不 一 致 2. 原 master 恢 复 正 常 后, 由 于 新 的 master 日 志 丢 弃 了 部 分 原 master 的 binlog 日 志, 这 些 多 出 来 的 binlog 日 志 怎 么 处 理, 重 新 搭 建 环 境? 对 于 上 面 出 现 的 问 题, 一 种 方 法 是 确 保 binlog 传 到 从 库, 或 者 说 保 证 主 库 的 binlog 有 多 个 拷 贝 第 二 种 方 法 就 是 允 许 数 据 丢 失, 制 定 一 定 的 策 略, 保 证 最 小 化 丢 失 数 据 1. 确 保 binlog 全 部 传 到 从 库 方 案 一 : 使 用 semi sync( 半 同 步 ) 方 式, 事 务 提 交 后, 必 须 要 传 到 slave, 事 务 才 能 算 结 束 对 性 能 影 响 很 大, 依 赖 网 络 适 合 小 tps 系 统 方 案 二 : 双 写 binlog, 通 过 DBDR OS 层 的 文 件 系 统 复 制 到 备 机, 或 者 使 用 共 享 盘 保 存 binlog 日 志 方 案 三 : 在 数 据 层 做 文 章, 比 如 保 证 数 据 库 写 成 功 后, 再 异 步 队 列 的 方 式 写 一 份, 部 分 业 务 可 以 借 助 设 计 和 数 据 流 解 决 2. 保 证 数 据 最 小 化 丢 失 上 面 的 方 案 设 计 及 架 构 比 较 复 杂, 如 果 能 容 忍 数 据 的 丢 失, 可 以 考 虑 使 用 淘 宝 的 TMHA 复 制 管 理 工 具 当 master 宕 机 后,TMHA 会 选 择 一 个 binlog 接 收 最 大 的 slave 作 为 master 当 原 master 宕 机 恢 复 后,

27 通 过 binlog 的 逆 向 应 用, 把 原 master 上 多 执 行 的 事 务 回 退 掉 3. 总 结 通 过 上 面 的 总 结 分 析,MySQL 丢 数 据 的 场 景 是 五 花 八 门, 涉 及 到 单 库 的 丢 数 据 场 景 主 从 的 丢 数 据 场 景 以 及 MySQL 内 部 XA 事 务 原 理 等, 相 对 还 比 较 复 杂, 有 点 难 以 理 解 只 有 当 我 们 了 解 了 这 些 丢 数 据 的 场 景, 才 能 更 好 的 去 学 习, 并 解 决 这 些 问 题 根 据 分 布 式 领 域 的 CAP 理 论 (Consistency Availability Partition tolerance), 任 何 的 分 布 式 系 统 只 能 同 时 满 足 2 点, 没 办 法 三 者 兼 顾 MySQL 的 主 从 环 境 满 足 Availability, 且 主 从 互 不 干 扰, 因 此 满 足 Partition tolerance, 但 是 不 满 足 Consistency, 如 果 需 要 满 足 Consistency, 则 肯 定 会 失 去 Partition tolerance, 因 此 实 现 100% 高 可 用 性 的 MySQL 主 从 架 构 还 是 非 常 困 难 的 只 能 通 过 一 些 设 计 去 牺 牲 部 分 特 性 去 满 足 另 外 的 特 性 后 续 会 重 点 对 MySQL 高 可 用 性 的 架 构 方 案 进 行 进 一 步 测 试 和 研 究 PS: 关 于 Partition tolerance 解 释 : Partition tolerance is sacrificed if certain requests are forced to wait for unbounded time because of a failure. This is most often the case when a node holding a lock cannot be reached, and quorum loss is not used to break the lock. 参 考 文 档 : 1.Innodb 的 log 写 入 策 略 及 主 从 同 步 : 2.MySQL 数 据 丢 失 讨 论

28 MySQL 分 区 分 表 Mysql 分 区 mysql 分 区 功 能 详 细 介 绍, 以 及 实 例 原 文 地 址 : 一, 什 么 是 数 据 库 分 区 前 段 时 间 写 过 一 篇 关 于 mysql 分 表 的 的 文 章, 下 面 来 说 一 下 什 么 是 数 据 库 分 区, 以 mysql 为 例 mysql 数 据 库 中 的 数 据 是 以 文 件 的 形 势 存 在 磁 盘 上 的, 默 认 放 在 /mysql/data 下 面 ( 可 以 通 过 my.cnf 中 的 datadir 来 查 看 ), 一 张 表 主 要 对 应 着 三 个 文 件, 一 个 是 frm 存 放 表 结 构 的, 一 个 是 myd 存 放 表 数 据 的, 一 个 是 myi 存 表 索 引 的 如 果 一 张 表 的 数 据 量 太 大 的 话, 那 么 myd,myi 就 会 变 的 很 大, 查 找 数 据 就 会 变 的 很 慢, 这 个 时 候 我 们 可 以 利 用 mysql 的 分 区 功 能, 在 物 理 上 将 这 一 张 表 对 应 的 三 个 文 件, 分 割 成 许 多 个 小 块, 这 样 呢, 我 们 查 找 一 条 数 据 时, 就 不 用 全 部 查 找 了, 只 要 知 道 这 条 数 据 在 哪 一 块, 然 后 在 那 一 块 找 就 行 了 如 果 表 的 数 据 太 大, 可 能 一 个 磁 盘 放 不 下, 这 个 时 候, 我 们 可 以 把 数 据 分 配 到 不 同 的 磁 盘 里 面 去 分 区 的 二 种 方 式 1, 横 向 分 区 什 么 是 横 向 分 区 呢? 就 是 横 着 来 分 区 了, 举 例 来 说 明 一 下, 假 如 有 100W 条 数 据, 分 成 十 份, 前 10W 条 数 据 放 到 第 一 个 分 区, 第 二 个 10W 条 数 据 放 到 第 二 个 分 区, 依 此 类 推 也 就 是 把 表 分 成 了 十 分, 根 用 merge 来 分 表, 有 点 像 哦 取 出 一 条 数 据 的 时 候, 这 条 数 据 包 含 了 表 结 构 中 的 所 有 字 段, 也 就 是 说 横 向 分 区, 并 没 有 改 变 表 的 结 构 2, 纵 向 分 区 什 么 是 纵 向 分 区 呢? 就 是 竖 来 分 区 了, 举 例 来 说 明, 在 设 计 用 户 表 的 时 候, 开 始 的 时 候 没 有 考 虑 好, 而 把 个 人 的 所 有 信 息 都 放 到 了 一 张 表 里 面 去, 这 样 这 个 表 里 面 就 会 有 比 较 大 的 字 段, 如 个 人 简 介, 而 这 些 简 介 呢, 也 许 不 会 有 好 多 人 去 看, 所 以 等 到 有 人 要 看 的 时 候, 在 去 查 找, 分 表 的 时 候, 可 以 把 这 样 的 大 字 段, 分 开 来 感 觉 数 据 库 的 分 区 好 像 是 切 苹 果, 到 底 是 横 着 切 呢, 还 是 竖 着 切, 根 据 个 人 喜 好 了,mysql 提 供 的 分 区 属 于 第 一 种, 横 向 分 区, 并 且 细 分 成 很 多 种 方 式 下 面 将 举 例 说 明 一 下 二,mysql 的 分 区 我 觉 着 吧,mysql 的 分 区 只 有 一 种 方 式, 只 不 过 运 用 不 同 的 算 法, 規 则 将 数 据 分 配 到 不 同 的 区 块 中 而 已 1,mysql5.1 及 以 上 支 持 分 区 功 能 安 装 安 装 的 时 候, 我 们 就 可 以 查 看 一 下 查 看 复 制 打 印? [root@blackghost mysql ]#./configure --help grep -A 3 Partition === Partition Support === Plugin Name: partition Description: MySQL Partitioning Support Supports build: static Configurations: max, max-no-ndb 查 看 一 下, 如 果 发 现 有 上 面 这 个 东 西, 说 明 他 是 支 持 分 区 的, 默 认 是 打 开 的 如 果 你 已 经 安

29 装 过 了 mysql 的 话 查 看 复 制 打 印? mysql> show variables like "%part%"; Variable_name Value have_partitioning YES row in set (0.00 sec) 查 看 一 下 变 量, 如 果 支 持 的 话, 会 有 上 面 的 提 示 的 2,range 分 区 按 照 RANGE 分 区 的 表 是 通 过 如 下 一 种 方 式 进 行 分 区 的, 每 个 分 区 包 含 那 些 分 区 表 达 式 的 值 位 于 一 个 给 定 的 连 续 区 间 内 的 行 查 看 复 制 打 印? // 创 建 range 分 区 表 mysql> CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' 用 户 ID', `name` varchar(50) NOT NULL DEFAULT '' COMMENT ' 名 称 ', `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 为 男,1 为 女 ', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (3), PARTITION p1 VALUES LESS THAN (6), PARTITION p2 VALUES LESS THAN (9), PARTITION p3 VALUES LESS THAN (12), PARTITION p4 VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.13 sec) // 插 入 一 些 数 据 mysql> INSERT INTO `test`.`user` (`name`,`sex`)values ('tank', '0'),('zhang',1),('ying',1),(' 张 ',1),(' 映 ',0),('test1',1),('tank2',1),('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1),('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1),('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1); Query OK, 25 rows affected (0.05 sec) Records: 25 Duplicates: 0 Warnings: 0 // 到 存 放 数 据 库 表 文 件 的 地 方 看 一 下,my.cnf 里 面 有 配 置,datadir 后 面 就 是 [root@blackghost test]# ls grep user xargs du -sh 4.0K user#p#p0.myd 4.0K user#p#p0.myi 4.0K user#p#p1.myd

30 4.0K user#p#p1.myi 4.0K user#p#p2.myd 4.0K user#p#p2.myi 4.0K user#p#p3.myd 4.0K user#p#p3.myi 4.0K user#p#p4.myd 4.0K user#p#p4.myi 12K user.frm 4.0K user.par // 取 出 数 据 mysql> select count(id) as count from user; count row in set (0.00 sec) // 删 除 第 四 个 分 区 mysql> alter table user drop partition p4; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 /** 存 放 在 分 区 里 面 的 数 据 丢 失 了, 第 四 个 分 区 里 面 有 14 条 数 据, 剩 下 的 3 个 分 区 只 有 11 条 数 据, 但 是 统 计 出 来 的 文 件 大 小 都 是 4.0K, 从 这 儿 我 们 可 以 看 出 分 区 的 最 小 区 块 是 4K */ mysql> select count(id) as count from user; count row in set (0.00 sec) // 第 四 个 区 块 已 删 除 [root@blackghost test]# ls grep user xargs du -sh 4.0K user#p#p0.myd 4.0K user#p#p0.myi 4.0K user#p#p1.myd 4.0K user#p#p1.myi 4.0K user#p#p2.myd 4.0K user#p#p2.myi

31 4.0K user#p#p3.myd 4.0K user#p#p3.myi 12K user.frm 4.0K user.par /* 可 以 对 现 有 表 进 行 分 区, 并 且 会 按 規 则 自 动 的 将 表 中 的 数 据 分 配 相 应 的 分 区 中, 这 样 就 比 较 好 了, 可 以 省 去 很 多 事 情, 看 下 面 的 操 作 */ mysql> alter table aa partition by RANGE(id) (PARTITION p1 VALUES less than (1), PARTITION p2 VALUES less than (5), PARTITION p3 VALUES less than MAXVALUE); Query OK, 15 rows affected (0.21 sec) // 对 15 数 据 进 行 分 区 Records: 15 Duplicates: 0 Warnings: 0 // 总 共 有 15 条 mysql> select count(*) from aa; count(*) row in set (0.00 sec) // 删 除 一 个 分 区 mysql> alter table aa drop partition p2; Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: 0 // 只 有 11 条 了, 说 明 对 现 有 的 表 分 区 成 功 了 mysql> select count(*) from aa; count(*) row in set (0.00 sec) 3,list 分 区 LIST 分 区 中 每 个 分 区 的 定 义 和 选 择 是 基 于 某 列 的 值 从 属 于 一 个 值 列 表 集 中 的 一 个 值, 而 RANGE 分 区 是 从 属 于 一 个 连 续 区 间 值 的 集 合 查 看 复 制 打 印? // 这 种 方 式 失 败 mysql> CREATE TABLE IF NOT EXISTS `list_part` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' 用 户 ID', `province_id` int(2) NOT NULL DEFAULT 0 COMMENT ' 省 ',

32 `name` varchar(50) NOT NULL DEFAULT '' COMMENT ' 名 称 ', `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 为 男,1 为 女 ', PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 PARTITION BY LIST (province_id) ( PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), PARTITION p1 VALUES IN (9,10,11,12,16,21), PARTITION p2 VALUES IN (13,14,15,19), PARTITION p3 VALUES IN (17,18,20,22,23,24) ); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function // 这 种 方 式 成 功 mysql> CREATE TABLE IF NOT EXISTS `list_part` ( `id` int(11) NOT NULL COMMENT ' 用 户 ID', `province_id` int(2) NOT NULL DEFAULT 0 COMMENT ' 省 ', `name` varchar(50) NOT NULL DEFAULT '' COMMENT ' 名 称 ', `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 为 男,1 为 女 ' ) ENGINE=INNODB DEFAULT CHARSET=utf8 PARTITION BY LIST (province_id) ( PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), PARTITION p1 VALUES IN (9,10,11,12,16,21), PARTITION p2 VALUES IN (13,14,15,19), PARTITION p3 VALUES IN (17,18,20,22,23,24) ); Query OK, 0 rows affected (0.33 sec) 上 面 的 这 个 创 建 list 分 区 时, 如 果 有 主 銉 的 话, 分 区 时 主 键 必 须 在 其 中, 不 然 就 会 报 错 如 果 我 不 用 主 键, 分 区 就 创 建 成 功 了, 一 般 情 况 下, 一 个 张 表 肯 定 会 有 一 个 主 键, 这 算 是 一 个 分 区 的 局 限 性 吧 如 果 对 数 据 进 行 测 试, 请 参 考 range 分 区 的 测 试 来 操 作 4,hash 分 区 HASH 分 区 主 要 用 来 确 保 数 据 在 预 先 确 定 数 目 的 分 区 中 平 均 分 布, 你 所 要 做 的 只 是 基 于 将 要 被 哈 希 的 列 值 指 定 一 个 列 值 或 表 达 式, 以 及 指 定 被 分 区 的 表 将 要 被 分 割 成 的 分 区 数 量 查 看 复 制 打 印? mysql> CREATE TABLE IF NOT EXISTS `hash_part` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' 评 论 ID', `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT ' 评 论 ', `ip` varchar(25) NOT NULL DEFAULT '' COMMENT ' 来 源 IP', PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 PARTITION BY HASH(id) PARTITIONS 3; Query OK, 0 rows affected (0.06 sec)

33 测 试 请 参 考 range 分 区 的 操 作 5,key 分 区 按 照 KEY 进 行 分 区 类 似 于 按 照 HASH 分 区, 除 了 HASH 分 区 使 用 的 用 户 定 义 的 表 达 式, 而 KEY 分 区 的 哈 希 函 数 是 由 MySQL 服 务 器 提 供 查 看 复 制 打 印? mysql> CREATE TABLE IF NOT EXISTS `key_part` ( `news_id` int(11) NOT NULL COMMENT ' 新 闻 ID', `content` varchar(1000) NOT NULL DEFAULT '' COMMENT ' 新 闻 内 容 ', `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT ' 来 源 IP', `create_time` DATE NOT NULL DEFAULT ' :00:00' COMMENT ' 时 间 ' ) ENGINE=INNODB DEFAULT CHARSET=utf8 PARTITION BY LINEAR HASH(YEAR(create_time)) PARTITIONS 3; Query OK, 0 rows affected (0.07 sec) 测 试 请 参 考 range 分 区 的 操 作 6, 子 分 区 子 分 区 是 分 区 表 中 每 个 分 区 的 再 次 分 割, 子 分 区 既 可 以 使 用 HASH 希 分 区, 也 可 以 使 用 KEY 分 区 这 也 被 称 为 复 合 分 区 (composite partitioning) 1, 如 果 一 个 分 区 中 创 建 了 子 分 区, 其 他 分 区 也 要 有 子 分 区 2, 如 果 创 建 了 了 分 区, 每 个 分 区 中 的 子 分 区 数 必 有 相 同 3, 同 一 分 区 内 的 子 分 区, 名 字 不 相 同, 不 同 分 区 内 的 子 分 区 名 子 可 以 相 同 ( 不 适 用 ) 查 看 复 制 打 印? mysql> CREATE TABLE IF NOT EXISTS `sub_part` ( `news_id` int(11) NOT NULL COMMENT ' 新 闻 ID', `content` varchar(1000) NOT NULL DEFAULT '' COMMENT ' 新 闻 内 容 ', `u_id` int(11) NOT NULL DEFAULT 0s COMMENT ' 来 源 IP', `create_time` DATE NOT NULL DEFAULT ' :00:00' COMMENT ' 时 间 ' ) ENGINE=INNODB DEFAULT CHARSET=utf8 PARTITION BY RANGE(YEAR(create_time)) SUBPARTITION BY HASH(TO_DAYS(create_time))( PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,subpartition s1,subpartition s2), PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,subpartition s4,subpartition good), PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,subpartition tank1,subpartition tank3) ); Query OK, 0 rows affected (0.07 sec) 官 方 网 站 说 不 同 分 区 内 的 子 分 区 可 以 有 相 同 的 名 字, 但 是 mysql 却 不 行 会 提 示 以 下 错 误 ERROR 1517 (HY000): Duplicate partition name s1 三, 分 区 管 理 1, 删 除 分 区 mysql> alter table user drop partition p4;

34 2, 新 增 分 区 查 看 复 制 打 印? //range 添 加 新 分 区 mysql> alter table user add partition(partition p4 values less than MAXVALUE); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 //list 添 加 新 分 区 mysql> alter table list_part add partition(partition p4 values in (25,26,28)); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 //hash 重 新 分 区 mysql> alter table hash_part add partition partitions 4; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 //key 重 新 分 区 mysql> alter table key_part add partition partitions 4; Query OK, 1 row affected (0.06 sec) // 有 数 据 也 会 被 重 新 分 配 Records: 1 Duplicates: 0 Warnings: 0 // 子 分 区 添 加 新 分 区, 虽 然 我 没 有 指 定 子 分 区, 但 是 系 统 会 给 子 分 区 命 名 的 mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table sub1_part\g; *************************** 1. row *************************** Table: sub1_part Create Table: CREATE TABLE `sub1_part` ( `news_id` int(11) NOT NULL COMMENT ' 新 闻 ID', `content` varchar(1000) NOT NULL DEFAULT '' COMMENT ' 新 闻 内 容 ', `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT ' 来 源 IP', `create_time` date NOT NULL DEFAULT ' ' COMMENT ' 时 间 ' ) ENGINE=InnoDB DEFAULT CHARSET=utf8!50100 PARTITION BY RANGE (YEAR(create_time)) SUBPARTITION BY HASH (TO_DAYS(create_time)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0 ENGINE = InnoDB, SUBPARTITION s1 ENGINE = InnoDB, SUBPARTITION s2 ENGINE = InnoDB), PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s3 ENGINE = InnoDB,

35 SUBPARTITION s4 ENGINE = InnoDB, SUBPARTITION good ENGINE = InnoDB), PARTITION p2 VALUES LESS THAN (3000) (SUBPARTITION tank0 ENGINE = InnoDB, SUBPARTITION tank1 ENGINE = InnoDB, SUBPARTITION tank3 ENGINE = InnoDB), PARTITION p3 VALUES LESS THAN MAXVALUE (SUBPARTITION p3sp0 ENGINE = InnoDB, // 子 分 区 的 名 子 是 自 动 生 成 的 SUBPARTITION p3sp1 ENGINE = InnoDB, SUBPARTITION p3sp2 ENGINE = InnoDB)) 1 row in set (0.00 sec) 3, 重 新 分 区 查 看 复 制 打 印? //range 重 新 分 区 mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE); Query OK, 11 rows affected (0.08 sec) Records: 11 Duplicates: 0 Warnings: 0 //list 重 新 分 区 mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5)); Query OK, 0 rows affected (0.28 sec) Records: 0 Duplicates: 0 Warnings: 0 //hash 和 key 分 区 不 能 用 REORGANIZE, 官 方 网 站 说 的 很 清 楚 mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION 9' at line 1 四, 分 区 优 点 1, 分 区 可 以 分 在 多 个 磁 盘, 存 储 更 大 一 点 2, 根 据 查 找 条 件, 也 就 是 where 后 面 的 条 件, 查 找 只 查 找 相 应 的 分 区 不 用 全 部 查 找 了 3, 进 行 大 数 据 搜 索 时 可 以 进 行 并 行 处 理 4, 跨 多 个 磁 盘 来 分 散 数 据 查 询, 来 获 得 更 大 的 查 询 吞 吐 量 MySQL 分 表 mysql 分 表 的 3 种 方 法 原 文 出 处 : 一, 先 说 一 下 为 什 么 要 分 表 当 一 张 的 数 据 达 到 几 百 万 时, 你 查 询 一 次 所 花 的 时 间 会 变 多, 如 果 有 联 合 查 询 的 话, 我 想 有 可 能 会 死 在 那 儿 了 分 表 的 目 的 就 在 于 此, 减 小 数 据 库 的 负 担, 缩 短 查 询 时 间

36 根 据 个 人 经 验,mysql 执 行 一 个 sql 的 过 程 如 下 : 1, 接 收 到 sql;2, 把 sql 放 到 排 队 队 列 中 ;3, 执 行 sql;4, 返 回 执 行 结 果 在 这 个 执 行 过 程 中 最 花 时 间 在 什 么 地 方 呢? 第 一, 是 排 队 等 待 的 时 间, 第 二,sql 的 执 行 时 间 其 实 这 二 个 是 一 回 事, 等 待 的 同 时, 肯 定 有 sql 在 执 行 所 以 我 们 要 缩 短 sql 的 执 行 时 间 mysql 中 有 一 种 机 制 是 表 锁 定 和 行 锁 定, 为 什 么 要 出 现 这 种 机 制, 是 为 了 保 证 数 据 的 完 整 性, 我 举 个 例 子 来 说 吧, 如 果 有 二 个 sql 都 要 修 改 同 一 张 表 的 同 一 条 数 据, 这 个 时 候 怎 么 办 呢, 是 不 是 二 个 sql 都 可 以 同 时 修 改 这 条 数 据 呢? 很 显 然 mysql 对 这 种 情 况 的 处 理 是, 一 种 是 表 锁 定 (myisam 存 储 引 擎 ), 一 个 是 行 锁 定 (innodb 存 储 引 擎 ) 表 锁 定 表 示 你 们 都 不 能 对 这 张 表 进 行 操 作, 必 须 等 我 对 表 操 作 完 才 行 行 锁 定 也 一 样, 别 的 sql 必 须 等 我 对 这 条 数 据 操 作 完 了, 才 能 对 这 条 数 据 进 行 操 作 如 果 数 据 太 多, 一 次 执 行 的 时 间 太 长, 等 待 的 时 间 就 越 长, 这 也 是 我 们 为 什 么 要 分 表 的 原 因 二, 分 表 1, 做 mysql 集 群, 例 如 : 利 用 mysql cluster,mysql proxy,mysql replication,drdb 等 等 有 人 会 问 mysql 集 群, 根 分 表 有 什 么 关 系 吗? 虽 然 它 不 是 实 际 意 义 上 的 分 表, 但 是 它 启 到 了 分 表 的 作 用, 做 集 群 的 意 义 是 什 么 呢? 为 一 个 数 据 库 减 轻 负 担, 说 白 了 就 是 减 少 sql 排 队 队 列 中 的 sql 的 数 量, 举 个 例 子 : 有 10 个 sql 请 求, 如 果 放 在 一 个 数 据 库 服 务 器 的 排 队 队 列 中, 他 要 等 很 长 时 间, 如 果 把 这 10 个 sql 请 求, 分 配 到 5 个 数 据 库 服 务 器 的 排 队 队 列 中, 一 个 数 据 库 服 务 器 的 队 列 中 只 有 2 个, 这 样 等 待 时 间 是 不 是 大 大 的 缩 短 了 呢? 这 已 经 很 明 显 了 所 以 我 把 它 列 到 了 分 表 的 范 围 以 内, 我 做 过 一 些 mysql 的 集 群 : linux mysql proxy 的 安 装, 配 置, 以 及 读 写 分 离 mysql replication 互 为 主 从 的 安 装 及 配 置, 以 及 数 据 同 步 优 点 : 扩 展 性 好, 没 有 多 个 分 表 后 的 复 杂 操 作 (php 代 码 ) 缺 点 : 单 个 表 的 数 据 量 还 是 没 有 变, 一 次 操 作 所 花 的 时 间 还 是 那 么 多, 硬 件 开 销 大 2, 预 先 估 计 会 出 现 大 数 据 量 并 且 访 问 频 繁 的 表, 将 其 分 为 若 干 个 表 这 种 预 估 大 差 不 差 的, 论 坛 里 面 发 表 帖 子 的 表, 时 间 长 了 这 张 表 肯 定 很 大, 几 十 万, 几 百 万 都 有 可 能 聊 天 室 里 面 信 息 表, 几 十 个 人 在 一 起 一 聊 一 个 晚 上, 时 间 长 了, 这 张 表 的 数 据 肯 定 很 大 像 这 样 的 情 况 很 多 所 以 这 种 能 预 估 出 来 的 大 数 据 量 表, 我 们 就 事 先 分 出 个 N 个 表, 这 个 N 是 多 少, 根 据 实 际 情 况 而 定 以 聊 天 信 息 表 为 例 : 我 事 先 建 100 个 这 样 的 表, message_00,message_01,message_02...message_98,message_99. 然 后 根 据 用 户 的 ID 来 判 断 这 个 用 户 的 聊 天 信 息 放 到 哪 张 表 里 面, 你 可 以 用 hash 的 方 式 来 获 得, 可 以 用 求 余 的 方 式 来 获 得, 方 法 很 多, 各 人 想 各 人 的 吧 下 面 用 hash 的 方 法 来 获 得 表 名 : 查 看 复 制 打 印? <?php function get_hash_table($table,$userid) { $str = crc32($userid); if($str<0){ $hash = "0".substr(abs($str), 0, 1); }else{ $hash = substr($str, 0, 2); } } return $table."_".$hash;

37 echo get_hash_table('message','user18991'); // 结 果 为 message_10 echo get_hash_table('message','user34523'); // 结 果 为 message_13?> 说 明 一 下, 上 面 的 这 个 方 法, 告 诉 我 们 user18991 这 个 用 户 的 消 息 都 记 录 在 message_10 这 张 表 里,user34523 这 个 用 户 的 消 息 都 记 录 在 message_13 这 张 表 里, 读 取 的 时 候, 只 要 从 各 自 的 表 中 读 取 就 行 了 优 点 : 避 免 一 张 表 出 现 几 百 万 条 数 据, 缩 短 了 一 条 sql 的 执 行 时 间 缺 点 : 当 一 种 规 则 确 定 时, 打 破 这 条 规 则 会 很 麻 烦, 上 面 的 例 子 中 我 用 的 hash 算 法 是 crc32, 如 果 我 现 在 不 想 用 这 个 算 法 了, 改 用 md5 后, 会 使 同 一 个 用 户 的 消 息 被 存 储 到 不 同 的 表 中, 这 样 数 据 乱 套 了 扩 展 性 很 差 3, 利 用 merge 存 储 引 擎 来 实 现 分 表 我 觉 得 这 种 方 法 比 较 适 合, 那 些 没 有 事 先 考 虑, 而 已 经 出 现 了 得, 数 据 查 询 慢 的 情 况 这 个 时 候 如 果 要 把 已 有 的 大 数 据 量 表 分 开 比 较 痛 苦, 最 痛 苦 的 事 就 是 改 代 码, 因 为 程 序 里 面 的 sql 语 句 已 经 写 好 了, 现 在 一 张 表 要 分 成 几 十 张 表, 甚 至 上 百 张 表, 这 样 sql 语 句 是 不 是 要 重 写 呢? 举 个 例 子, 我 很 喜 欢 举 子 mysql>show engines; 的 时 候 你 会 发 现 mrg_myisam 其 实 就 是 merge 查 看 复 制 打 印? mysql> CREATE TABLE IF NOT EXISTS `user1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `sex` int(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE IF NOT EXISTS `user2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `sex` int(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `user1` (`name`, `sex`) VALUES(' 张 映 ', 0); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1); Query OK, 1 row affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `alluser` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `sex` int(1) NOT NULL DEFAULT '0',

38 INDEX(id) ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select id,name,sex from alluser; id name sex 张 映 0 1 tank rows in set (0.00 sec) mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0); Query OK, 1 row affected (0.00 sec) mysql> select id,name,sex from user2 ; id name sex tank 1 2 tank rows in set (0.00 sec) 从 上 面 的 操 作 中, 我 不 知 道 你 有 没 有 发 现 点 什 么? 假 如 我 有 一 张 用 户 表 user, 有 50W 条 数 据, 现 在 要 拆 成 二 张 表 user1 和 user2, 每 张 表 25W 条 数 据, INSERT INTO user1(user1.id,user1.name,user1.sex)select (user.id,user.name,user.sex)from user where user.id <= INSERT INTO user2(user2.id,user2.name,user2.sex)select (user.id,user.name,user.sex)from user where user.id > 这 样 我 就 成 功 的 将 一 张 user 表, 分 成 了 二 个 表, 这 个 时 候 有 一 个 问 题, 代 码 中 的 sql 语 句 怎 么 办, 以 前 是 一 张 表, 现 在 变 成 二 张 表 了, 代 码 改 动 很 大, 这 样 给 程 序 员 带 来 了 很 大 的 工 作 量, 有 没 有 好 的 办 法 解 决 这 一 点 呢? 办 法 是 把 以 前 的 user 表 备 份 一 下, 然 后 删 除 掉, 上 面 的 操 作 中 我 建 立 了 一 个 alluser 表, 只 把 这 个 alluser 表 的 表 名 改 成 user 就 行 了 但 是, 不 是 所 有 的 mysql 操 作 都 能 用 的 a, 如 果 你 使 用 alter table 来 把 merge 表 变 为 其 它 表 类 型, 到 底 层 表 的 映 射 就 被 丢 失 了 取 而 代 之 的, 来 自 底 层 myisam 表 的 行 被 复 制 到 已 更 换 的 表 中, 该 表 随 后 被 指 定 新 类 型 b, 网 上 看 到 一 些 说 replace 不 起 作 用, 我 试 了 一 下 可 以 起 作 用 的 晕 一 个 先 mysql> UPDATE alluser SET sex=replace(sex, 0, 1) where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from alluser;