最佳实践产品文档

云 数 据 库 TDSQL 最 佳 实 践 产 品 文 档

版 权 声 明 2015-2016 腾 讯 云 版 权 所 有 本 文 档 著 作 权 归 腾 讯 云 单 独 所 有, 未 经 腾 讯 云 事 先 书 面 许 可, 任 何 主 体 不 得 以 任 何 形 式 复 制 修 改 抄 袭 传 播 全 部 或 部 分 本 文 档 内 容 商 标 声 明 及 其 它 腾 讯 云 服 务 相 关 的 商 标 均 为 腾 讯 云 计 算 ( 北 京 ) 有 限 责 任 公 司 及 其 关 联 公 司 所 有 本 文 档 涉 及 的 第 三 方 主 体 的 商 标, 依 法 由 权 利 人 所 有 服 务 声 明 本 文 档 意 在 向 客 户 介 绍 腾 讯 云 全 部 或 部 分 产 品 服 务 的 当 时 的 整 体 概 况, 部 分 产 品 服 务 的 内 容 可 能 有 所 调 整 您 所 购 买 的 腾 讯 云 产 品 服 务 的 种 类 服 务 标 准 等 应 由 您 与 腾 讯 云 之 间 的 商 业 合 同 约 定, 除 非 双 方 另 有 约 定, 否 则, 腾 讯 云 对 本 文 档 内 容 不 做 任 何 明 示 或 模 式 的 承 诺 或 保 证 第 2 页 共 27 页

文 档 目 录 文 档 声 明... 2 编 程 与 使 用 规 范... 4 分 布 式 版 本 编 程 与 使 用 规 范... 19 第 3 页 共 27 页

编 程 与 使 用 规 范 如 对 本 规 范 有 任 何 意 见 或 建 议, 欢 迎 联 系 腾 讯 云 工 作 人 员 或 直 接 发 送 邮 件 到 benhu@tencent.com 1. 引 言 1.1. 文 档 目 的 本 文 档 旨 在 明 确 TDSQL 数 据 库 技 术 与 应 用 的 规 划 与 设 计 安 装 与 部 署 运 行 管 理 与 维 护 等 全 生 命 周 期 各 阶 段 的 主 要 技 术 标 准 和 指 导 原 则, 便 于 TDSQL 数 据 库 应 用 项 目 的 统 一 建 设 和 管 理, 增 加 TDSQL 数 据 库 技 术 应 用 的 规 范 性 性 能 保 障 和 可 维 护 性 1.2. 预 期 读 者 使 用 (D)CDB for TDSQL 的 项 目 相 关 数 据 库 设 计 开 发 管 理 和 运 行 维 护 人 员 2. 设 计 规 范 2.1. 数 据 库 设 计 原 则 TDSQL 面 向 的 是 OLTP 的 应 用 场 景, 并 不 适 用 于 大 多 数 复 杂 的 OLAP 反 范 式 设 计 : 不 必 强 制 满 足 第 三 范 式, 尽 量 少 使 用 外 键 外 键 用 来 保 护 参 照 完 整 性, 可 在 业 务 端 实 现 适 度 的 冗 余 设 计, 减 少 多 表 join 查 询, 更 适 应 MPP 架 构 的 横 向 扩 展 能 力 直 接 基 于 I/O 和 查 询 进 行 优 化 充 分 考 虑 业 务 逻 辑 和 数 据 分 离, 数 据 库 只 作 为 一 个 保 证 ACID 特 性 的 关 系 数 据 的 持 久 化 存 储 系 统, 尽 量 避 免 使 用 自 定 义 函 数 存 储 过 程 触 发 器 和 视 图 充 分 考 虑 数 据 库 整 体 安 全 设 计, 数 据 库 管 理 和 使 用 人 员 权 限 分 离 第 4 页 共 27 页

充 分 考 虑 具 体 数 据 对 象 的 访 问 频 度 及 性 能 需 求, 结 合 主 机 存 储 等 需 求, 做 好 数 据 库 性 能 设 计 充 分 考 虑 数 据 增 长 模 型, 决 策 是 否 采 用 groupshard 模 式 充 分 考 虑 业 务 数 据 安 全 等 级, 设 计 合 适 的 备 份 和 恢 复 策 略 2.2. 数 据 库 模 型 设 计 规 范 2.2.1. 基 础 规 范 只 使 用 InnoDB 存 储 引 擎, 避 免 使 用 MyISAM 引 擎, 二 者 对 比,InnoDB 具 有 如 下 特 性 完 整 的 ACID 支 持 崩 溃 自 检 恢 复 行 级 锁, 高 并 发 的 保 证 更 能 发 挥 多 核 CPU 的 性 能 自 带 缓 存 池, 更 好 的 利 用 内 存 所 有 表 使 用 统 一 的 字 符 集, 强 烈 建 议 使 用 UTF8 或 UTF8MB4 字 符 集 不 在 数 据 库 中 存 储 图 片 二 进 制 文 件 等 大 数 据 提 前 规 划 好 单 表 规 模, 行 数 和 大 小 控 制 单 行 字 段 总 长 度, 合 理 设 置 innodb_page_size, 尽 量 采 用 COMPACT 行 格 式, 避 免 行 溢 出 2.2.2. 数 据 库 对 象 命 名 规 范 数 据 库 对 象 命 名 规 规 范 的 适 用 范 围 为 使 用 TDSQL 的 数 据 库 设 计 管 理 开 发 人 员, 对 于 TDSQL 产 品 自 带 的 系 统 库 表 等 对 象 不 在 本 规 范 约 束 范 围 内 数 据 库 对 象 命 名 时 整 体 遵 循 如 下 规 范 : 对 象 命 名 要 使 用 富 有 意 义 英 文 词 汇, 除 约 定 俗 成 外, 避 免 使 用 缩 写 在 索 引 约 束 等 对 象 命 名 时 会 同 时 包 含 表 名 字 段 名 等 多 个 名 字, 此 时 可 以 使 用 缩 写, 缩 写 规 则 和 字 符 数 要 统 一 只 使 用 小 写 字 母 数 字 和 下 划 线 的 组 合 名 字 长 度 不 要 超 过 32 个 字 符 不 要 使 用 SQL 关 键 字 对 象 名 字 至 少 包 括 : 对 象 类 型 父 对 象 名 对 象 名 第 5 页 共 27 页

下 表 列 出 了 不 同 数 据 库 对 象 的 命 名 规 则 : 数 据 库 对 象 格 式 样 例 说 明 数 据 库 (SCHEMA) db_< 数 据 库 名 > db_user 表 示 这 个 数 据 库 下 都 是 用 户 相 关 的 数 据 表 tbl_< 表 名 > tbl_employees 表 示 存 储 雇 员 信 息 的 表 主 键 pk_< 表 名 >_< 字 段 名 >[_ 字 pk_emlo_id_name 段 名 ] 表 示 tbl_employees 表 的 id 和 name 字 段 共 同 组 成 主 键 但 是 在 TDSQL, 这 里 的 主 键 名 其 实 没 有 意 义, 系 统 会 忽 略 这 个 名 字, 然 后 统 一 命 名 为 :PRIMARY 唯 一 索 引 uidx_< 表 名 >_< 字 段 名 >[_ uidx_empl_name_age_1 表 示 tbl_employees 表 的 na 字 段 名 ]_< 编 号 > me 和 age 字 段 创 建 的 第 一 个 唯 一 索 引 普 通 索 引 idx_< 表 名 缩 写 >_< 列 名 缩 idx_empl_name_age_1 表 示 tbl_employees 表 的 na 写 >[_ 列 名 缩 写 ]_< 编 号 > me 和 age 字 段 创 建 的 第 一 个 普 通 索 引 外 键 fk_< 表 名 >_< 关 联 表 名 >_< fk_empl_user_uid_id 字 段 名 >[_ 字 段 名 ] _< 关 联 字 段 名 >[_ 关 联 字 段 名 ] 表 示 tbl_employees 表 的 ui d 字 段 外 键 约 束 到 tbl_user 的 id 字 段 视 图 v_< 视 图 名 > v_female - 存 储 过 程 sp_< 存 储 过 程 名 > sp_add_empl - 自 定 义 函 数 f_< 函 数 名 > f_count_empl - 触 发 器 trg_< 触 发 器 名 > trg_update_empl - 临 时 表 tmp_< 表 名 > tmp_latecomer - 表 格 1 2.2.3. 实 例 配 置 TDSQL 实 例 在 申 请 后 需 要 初 始 化, 初 始 化 过 程 中 有 2 个 重 要 指 标 需 要 指 定 : 字 符 集 : 指 定 库 表 默 认 的 字 符 集 第 6 页 共 27 页

-innodb_page_size:innodb 表 文 件 存 储 中 页 的 大 小 页 是 InnoDB 引 擎 在 文 件 中 存 储 的 最 小 单 位, 原 生 Mysql 的 默 认 值 为 16K, 该 值 的 配 置 从 以 下 几 点 考 虑 实 例 中 主 要 表 的 单 行 数 据 大 小 InnoDB 表 的 ROW_FORMAT 配 置 是 否 使 用 SSD 盘 在 初 始 化 后, 还 有 些 参 数 可 以 设 置 : 根 据 业 务 需 要, 可 通 过 SQL MODE 设 置 STRICT_ALL_TABLES 或 STRICT_TRANS_TABLES 标 志 位 开 启 ST RICT 模 式, 严 格 拒 绝 不 符 合 字 段 类 型 定 义 的 数 据 写 入, 两 个 标 志 位 的 区 别 : STRICT_ALL_TABLES: 对 于 不 支 持 事 务 的 存 储 引 擎, 如 果 同 时 更 新 多 行 数 据, 如 果 不 符 合 定 义 的 数 据 不 是 第 一 行, 则 会 导 致 该 行 前 面 的 行 数 据 更 新, 该 行 及 后 续 行 数 据 不 更 新 的 情 况 STRICT_TRANS_TABLES: 对 于 不 支 持 事 务 的 存 储 引 擎, 如 果 不 符 合 定 义 的 数 据 是 第 一 行, 则 拒 绝 请 求, 否 则 按 照 普 通 模 式 运 行 TDSQL 默 认 会 启 动 事 务 自 动 提 交 (AUTOCOMMIT), 也 建 议 保 持 开 启 TDSQL 默 认 的 事 务 隔 离 级 别 是 REPEATABLE-READ, 请 根 据 需 要 决 定 是 否 调 整 为 READ- COMMITED(ORACLE 的 默 认 级 别 ) 2.2.4. 数 据 库 TDSQL 中 数 据 库 的 概 念 和 ORACLE 不 同, 可 以 认 为 是 一 个 SCHEMA, 类 似 于 一 个 表 的 文 件 夹, 方 便 DBA 的 分 类 管 理 创 建 数 据 库 时, 除 了 指 定 数 据 库 名, 强 烈 建 议 明 确 指 定 数 据 库 默 认 的 字 符 集 参 数, 例 如 : CREATE DATABASE `db_user` CHARSET ='utf8' COLLATE='utf8_bin' 2.2.5. 表 2.2.5.1. 表 参 数 设 置 InnoDB 表 的 默 认 ROW_FORMAT 为 COMPACT, 请 根 据 情 况 指 定 合 适 的 值 : COMPACT: 在 不 产 生 行 溢 出 的 情 况 下, 一 行 数 据 存 储 在 一 个 数 据 页 中, 数 据 读 取 效 率 最 好 DYNAMIC: 至 少 存 储 在 2 个 页 中, 数 据 页 只 存 储 溢 出 页 的 指 针, 数 据 存 在 溢 出 页 中, 这 种 结 构 的 索 引 存 储 最 集 中, 索 引 访 问 效 率 较 高 REDUNDANT: 老 的 格 式, 不 要 使 用 第 7 页 共 27 页

COMPRESSED: 会 对 数 据 进 行 压 缩 后 存 储, 最 高 的 空 间 利 用 率, 不 过 会 增 加 CPU 和 内 存 消 耗, 增 加 系 统 响 应 时 间, 降 低 吞 吐 量, 谨 慎 使 用 强 烈 建 议 明 确 指 定 表 默 认 的 字 符 集 参 数 文 件 存 放 路 径 等 物 理 相 关 的 参 数 是 不 允 许 指 定 的 2.2.5.2. 主 外 键 约 束 设 计 InnoDB 表 必 须 指 定 主 键 建 议 不 使 用 具 有 实 际 意 义 的 字 段 做 主 键, 如 果 一 定 要 使 用, 确 保 该 字 段 具 有 现 实 世 界 的 唯 一 且 不 变 性 ( 例 如 : 银 行 交 易 流 水 号 ), 以 尽 量 避 免 主 键 修 改 减 少 外 键 的 使 用, 特 别 是 在 groupshard 模 式 下, 考 虑 到 数 据 库 的 自 动 分 库 分 表, 建 议 不 使 用 外 键 组 成 主 键 的 字 段 总 长 度 越 短, 效 率 越 高 整 数 类 型 的 字 段 做 主 键 最 合 适 如 果 存 在 多 个 唯 一 键, 考 虑 最 常 用 的 唯 一 键 作 为 主 键 建 议 使 用 自 增 字 段 作 为 主 键 字 段 属 性 尽 量 加 上 NOT NULL 约 束 以 及 默 认 值, 使 用 NULL 值 会 导 致 如 下 副 作 用 : 含 义 不 明, 对 很 多 运 算 符 不 管 用, 增 加 复 杂 度 例 如 a!=5 是 无 法 匹 配 到 a 为 NULL 的 行 很 难 进 行 查 询 优 化 含 NULL 的 复 合 索 引 失 效 不 要 使 用 CHECK 约 束,TDSQL(Mysql) 虽 然 不 报 错, 但 会 忽 略, 可 以 使 用 ENUM 类 型 代 替 2.2.5.3. 字 段 类 型 设 计 整 数 类 型 请 根 据 实 际 存 储 值 的 范 围 选 择 合 适 的 类 型 无 符 号 整 数 类 型 请 添 加 UNSIGNED 关 键 字 字 符 串 类 型 请 根 据 实 际 存 储 值 的 情 况 确 定 选 择 定 长 类 型 还 是 变 长 类 型 根 据 实 际 存 储 值 的 情 况, 尽 量 减 少 字 符 串 类 型 的 长 度 尽 量 避 免 使 用 TEXT/BLOB 类 型, 不 要 在 数 据 库 中 存 储 图 片 二 进 制 文 件 等 数 据 非 整 数 尽 量 使 用 精 确 的 DECIMAL, 避 免 使 用 浮 点 数 下 表 是 推 荐 使 用 的 主 要 数 据 类 型 取 值 范 围 和 存 储 需 求 : 类 型 值 范 围 存 储 需 求 TINYINT[(M)] -128 到 127 或 0 到 255 1 个 字 节 SMALLINT[(M)] -32768 到 32767 或 0 到 65535 2 个 字 节 INT[(M)] -2147483648 到 214748364 或 4 个 字 节 第 8 页 共 27 页

类 型 值 范 围 存 储 需 求 0 到 4294967295 BIGINT[(M)] -9223372036854775808 到 9223378 个 字 节 2036854775807 或 0 到 18446744073709551615 DECIMAL[(M[,D])] 整 数 最 大 位 数 (M) 为 65, 小 数 位 数 变 长 最 大 (D) 为 30 DATE '1000-01-01' 到 '9999-12-31' 3 个 字 节 TIME[(<microsecond precision>)] '-838:59:59.999999' 到 '838:59:59.9 5 个 字 节 99999' DATETIME[(microsecond precision)] TIMESTAMP[(<microsecond precision)] '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999' '1970-01-01 00:00:01' (UTC) 到 '2038-01-19 05:14:07' (UTC) 8 个 字 节 4 个 字 节 CHAR[(M)] 0<M<=255 M 的 整 数 倍, 和 字 符 集 设 置 有 关 VARCHAR[(M)] 0<M<65532/N N 的 取 值 和 实 际 字 符 集 设 置 有 关 TEXT[(M)] 0<M<65535/N N 的 取 值 和 实 际 字 符 集 设 置 有 关 表 格 2 2.2.6. 索 引 低 选 择 性 的 列 不 加 索 引, 如 : 性 别 在 组 合 索 引 中, 常 用 的 字 段 放 在 前 面, 选 择 性 高 的 字 段 放 在 前 面 需 要 经 常 排 序 的 字 段 可 加 到 索 引 中, 字 段 顺 序 和 最 常 用 的 排 序 一 致 对 于 较 长 的 字 符 串 类 型 字 段, 建 议 使 用 前 缀 索 引 合 理 合 并 索 引, 避 免 冗 余, 例 如 (a,b) 和 (a), 应 该 去 掉 (a) 单 张 表 的 索 引 控 制 在 5 个 以 内 单 个 索 引 的 字 段 不 要 超 过 5 个 TDSQL 的 InnoDB 引 擎 支 持 全 文 索 引, 但 目 前 限 于 英 文 第 9 页 共 27 页

2.2.7. 分 页 设 计 分 页 是 应 用 中 最 常 见 的 访 问 模 型, 我 们 用 下 面 几 种 分 页 方 式 的 实 际 测 试 情 况 来 看 看 如 何 设 计 合 理 的 分 页 模 型 : /* * id 是 表 post 的 主 键 */ MySQL> SELECT sql_no_cache * FROM post LIMIT 20000,10; 10 row in set (0.13 sec) MySQL> SELECT sql_no_cache * FROM post LIMIT 80000,10; 10 rows in set (0.58 sec) MySQL> SELECT sql_no_cache id FROM post LIMIT 80000,10; 10 rows in set (0.02 sec) MySQL> SELECT sql_no_cache * FROM post WHERE id>=323423 LIMIT 10; 10 rows in set (0.01 sec) MySQL> SELECT * FROM post WHERE id >= ( SELECT sql_no_cache id FROM post LIMIT 8 0000,1 ) LIMIT 10 ; 10 rows in set (0.02 sec) 上 面 的 结 果 很 明 显, 要 尽 量 避 免 直 接 使 用 LIMIT m,n 这 种 分 页 方 式 2.3. 数 据 库 安 全 设 计 数 据 库 用 户 安 全 设 计 原 则 数 据 库 用 户 权 限 授 权 按 照 最 小 分 配 原 则 数 据 库 用 户 要 分 为 管 理 应 用 维 护 备 份 四 类 用 户 数 据 库 用 户 权 限 设 计 规 范 除 TDSQL 和 核 心 维 护 人 员 外, 其 他 用 户 不 能 拥 有 SUPER 权 限 账 号 避 免 使 用 简 单 密 码 第 10 页 共 27 页

TDSQL 的 权 限 支 持 到 字 段 级 别, 权 限 的 主 体 对 象 要 按 最 小 原 则 控 制 开 发 测 试 和 生 产 环 境 中 用 户 权 限 设 置 要 保 持 一 致 严 格 禁 止 在 数 据 库 中 存 储 任 何 形 式 的 密 码 明 文 1. 3. 开 发 规 范 1. 3.1.SQL 编 码 规 范 每 行 不 要 超 过 80 个 字 符, 超 过 就 换 行 缩 进 使 用 两 个 空 格 来 缩 进 代 码 关 键 词 要 大 写, 比 如 :SELECT 常 数 符 号 要 大 写, 比 如 :NULL 合 理 使 用 注 释 建 表 语 句 前 要 对 表 的 用 途 进 行 详 细 注 释 每 个 字 段 后 使 用 COMMNET 子 句 添 加 字 段 的 注 释 建 表 语 句 最 后 面 使 用 COMMENT 子 句 添 加 对 表 的 一 句 话 注 释 去 掉 多 余 的 扩 容, 例 如 : ((a AND b) AND c OR (((a AND b) AND (c AND d)))) 应 该 优 化 为 : (a AND b AND c) OR (a AND b AND c AND d) 去 掉 重 叠 的 条 件, 例 如 : (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) 第 11 页 共 27 页

应 该 优 化 为 : B=5 OR B=6 3.2.SQL 语 句 规 范 3.2.1. 索 引 和 分 区 合 理 使 用 USE INDEX 和 IGNORE INDEX 进 行 索 引 的 选 择 查 询 条 件 尽 量 使 用 索 引 注 意 字 段 类 型, 避 免 类 型 转 换 类 型 转 换 除 了 会 增 加 CPU 消 耗, 如 果 转 换 失 败, 还 会 导 致 索 引 失 效 对 于 复 合 索 引, 查 询 条 件 必 须 包 含 所 有 前 缀 字 段 才 管 用, 例 如 索 引 (a,b,c), 查 询 条 件 必 须 是 a 或 者 a b 或 者 a b c 才 能 使 用 到 索 引 3.2.2.SELECT 列 和 WHERE 条 件 尽 量 不 要 让 数 据 库 做 算 术 运 算, 交 给 应 用 层 来 做, 例 如 : SELECT a FROM tbl WHERE id*10=100; 尽 量 不 要 直 接 SELECT *, 直 接 列 出 需 要 查 询 的 字 段 尽 量 使 用 UNION ALL, 而 不 是 UNION UNION 会 做 去 重 和 排 序 WHERE 子 句 使 用 的 原 则 : 尽 量 使 用 索 引, 尽 量 简 单, 尽 量 匹 配 更 少 的 行 WHERE 子 句 中 多 使 用 等 值 操 作 符, 少 使 用 非 等 值 操 作 符, 等 等 值 操 作 符 通 常 会 导 致 索 引 失 效 (Mysql 不 支 持 范 围 索 引 ) 就 算 有 索 引,WHERE 子 句 匹 配 的 行 数 不 要 超 过 表 的 30%, 否 则 效 率 还 是 很 低,InnoDB 引 擎 还 很 有 可 能 直 接 放 弃 使 用 索 引, 采 用 全 表 扫 描 LIKE 子 句 的 条 件 中,% 不 要 是 第 一 个 字 符, 尽 量 靠 后 更 复 杂 的 需 求 考 虑 使 用 全 文 索 引 OR 条 件 大 于 3 个 : 不 同 字 段 的, 使 用 UNION ALL 代 替 相 同 字 段 的, 用 IN 代 替 第 12 页 共 27 页

尽 量 使 用 WHERE 子 句 代 替 HAVING 子 句, 例 如 : SELECT id,count(*) FROM tbl GROUP BY id HAVING age>=30; 应 该 替 换 为 : SELECT id,count(*) FROM tbl WHERE age>30 GROUP BY id; 一 个 表 的 ORDER BY 和 GROUP BY 的 组 合 都 不 应 该 超 过 3 种, 否 则 从 业 务 逻 辑 考 虑 进 行 优 化 或 者 分 成 多 张 表 如 果 不 需 要 排 序,GROUP BY 子 句 写 成 :GROUP BY NULL WHERE 子 句 尽 量 使 用 主 键 InnoDB 表 尽 量 避 免 使 用 类 似 COUNT(*) 的 全 表 扫 描 查 询, 从 设 计 上 考 虑 另 外 用 一 张 表 存 这 个 计 数 值 尽 量 避 免 使 用 子 查 询 3.2.3.DML 语 句 UPDATE DELETE 等 语 句 尽 量 带 上 WHERE 子 句, 且 使 用 索 引 字 段, 最 好 使 用 主 键 使 用 INSERT... ON DUPLICATE KEY update (INSERT IGNORE) 来 避 免 不 必 要 的 查 询 INSERT UPDATE DELETE 语 句 中 不 要 使 用 不 确 定 值 的 函 数, 例 如 :RAND() 和 NOW() 多 条 INSERT 语 句 要 合 并 成 一 条 批 量 提 交, 一 次 不 要 超 过 500 行 数 据 禁 止 在 UPDATE 语 句 中, 讲 "," 写 成 AND, 非 常 危 险, 例 如 : UPDATE tbl SET fid=fid+1000, gid=gid+1000 WHERE id > 2; 如 果 写 成 UPDATE tbl SET fid=fid+1000 AND gid=gid+1000 WHERE id > 2; 第 13 页 共 27 页

此 时 fid+1000 AND gid=gid+1000 将 作 为 值 赋 给 fid, 且 没 有 Warning 删 除 一 张 表 用 TRUNCATE, 而 不 是 DELETE 3.2.4. 多 表 JOIN 多 表 join 时, 各 表 的 顺 序 按 照 各 表 在 WHERE 子 句 条 件 下 返 回 的 行 数 从 小 到 大 排 列, 行 数 最 小 的 在 最 左 边 避 免 大 表 间 的 JOIN, 一 般 表 的 记 录 数 不 超 过 10W 条 的 情 况 下, 才 建 议 使 用 JOIN 3.2.5. 其 它 合 理 使 用 PREPARE Statement, 提 供 性 能 还 能 防 SQL 注 入 拆 分 负 载 SQL 为 多 条 SQL, 避 免 大 事 务 如 果 要 对 表 进 行 DDL 操 作, 尽 量 将 对 一 张 表 的 DDL 在 一 条 SQL 语 句 完 成, 例 如 : 要 给 表 t 增 加 一 个 字 段 b, 然 后 给 已 有 字 段 a 建 立 索 引, 可 以 用 下 面 一 条 语 句 完 成 : ALTER TABLE t ADD COLUMN b VARCHAR(10), ADD INDEX idx_a(a); 4. 数 据 库 管 理 规 范 4.1. 基 本 要 求 TDSQL 自 带 的 安 装 程 序 会 在 安 装 前 进 行 系 统 环 境 检 查, 包 括 以 下 项 目 : 检 查 项 操 作 系 统 版 本 期 望 结 果 Linux 系 列 文 件 句 柄 大 于 100000 时 间 同 步 NTP 配 置 正 确 第 14 页 共 27 页

检 查 项 用 户 安 装 目 录 数 据 目 录 期 望 结 果 检 查 安 装 指 定 的 用 户 是 否 存 在 检 查 安 装 目 录 是 否 存 在 且 有 写 权 限 检 查 指 定 的 数 据 目 录 是 否 存 在 且 有 写 权 限 表 格 3 4.2.NOSHARD 与 GROUP SHARD 的 选 择 当 满 足 以 下 条 件 时, 可 以 考 虑 使 用 GROUP SHARD: 表 中 数 据 会 持 续 增 长, 在 可 预 见 的 时 间 内, 超 过 单 机 最 大 存 储 量 表 的 读 写 量 很 大, 超 过 了 单 机 最 大 吞 吐 量 充 分 考 虑 了 分 库 分 表 会 导 致 的 在 表 设 计 和 使 用 上 的 各 种 限 制 关 于 分 布 式 版 本 (GROUP SHARD 版 本 ) 在 使 用 上 的 限 制, 请 参 考 : 分 布 式 版 本 开 发 指 南 4.3. 主 从 配 置 与 容 灾 目 前 云 数 据 库 已 支 持 下 列 能 力 TDSQL 支 持 一 主 多 从 配 置, 且 能 在 主 机 宕 机 时 自 动 选 举 一 台 从 机 为 主 机 从 机 个 数 可 以 自 定 义, 且 多 个 从 机 提 供 负 载 均 衡 的 只 读 能 力, 所 以 增 加 从 机 可 以 扩 容 读 能 力 ( 从 机 的 数 据 有 延 迟 ) TDSQL 支 持 强 同 步 和 异 步 两 种 主 从 同 步 方 式 : 强 同 步 能 保 证 主 机 宕 机 不 丢 数 据, 因 此 建 议 至 少 配 置 2 个 从 机 因 为 如 果 只 有 1 个 从 机, 当 主 机 宕 机, 为 保 证 数 据 不 丢 失, 剩 下 1 个 从 机 只 能 提 供 只 读 服 务 强 同 步 性 能 会 低 于 异 步, 损 失 情 况 视 主 从 机 之 间 的 网 络 质 量 而 定 一 般 建 议 只 在 同 机 房, 或 者 同 城 多 个 机 房 之 间 使 用 强 同 步 TDSQL 还 支 持 将 2 个 逻 辑 实 例 设 置 为 主 从, 一 般 用 于 异 地 数 据 容 灾 4.4. 备 份 与 恢 复 第 15 页 共 27 页

目 前 云 数 据 库 已 支 持 下 列 能 力 为 TDSQL 集 群 配 置 一 个 HDFS 集 群, 用 来 存 储 冷 备 和 BINLOG 备 份 TDSQL 支 持 配 置 定 期 冷 备 到 HDFS BINLOG 会 实 时 备 份 到 HDFS 利 用 冷 备 和 BINLOG 备 份,TDSQL 支 持 指 定 时 间 点 的 回 档 4.5. 用 户 和 权 限 TDSQL 的 使 用 者 应 该 分 为 集 群 管 理 者 数 据 库 实 例 管 理 者 和 数 据 库 使 用 者 : 集 群 管 理 者 主 要 通 过 运 维 管 理 平 台 完 成 以 下 工 作 : 管 理 整 个 集 群 的 资 源 池, 负 责 资 源 的 上 下 架 和 合 理 的 分 配 为 实 例 管 理 者 分 配 数 据 库 实 例 协 助 实 例 管 理 者 进 行 实 例 管 理 负 责 管 理 所 有 实 例 的 冷 备 和 BINLOG 备 份 数 据 关 注 集 群 和 实 例 的 运 行 指 标, 保 证 集 群 和 各 实 例 的 正 常 运 行 数 据 库 实 例 管 理 者 主 要 通 过 数 据 库 管 理 平 台 完 成 以 下 工 作 : 提 交 新 建 扩 容 删 除 实 例 的 申 请 负 责 维 护 实 例 的 个 性 化 参 数 负 责 管 理 数 据 库 实 例 的 账 号 和 密 码 关 注 实 例 的 运 行 指 标, 进 行 数 据 库 性 能 管 理 通 过 查 看 慢 查 询 分 析 错 误 日 志 慢 查 询 日 志 等 信 息, 进 行 数 据 库 的 性 能 优 化 分 析 数 据 库 使 用 者 通 过 SQL 客 户 端 连 接 数 据 库, 提 交 SQL 语 句, 存 取 数 据 4.6. 日 志 管 理 TDSQL 集 群 的 日 志 主 要 包 括 : 集 群 中 各 个 组 件 的 运 行 日 志 实 例 的 网 关 日 志 第 16 页 共 27 页

实 例 的 数 据 库 运 行 日 志 慢 查 询 日 志 错 误 日 志 实 例 的 数 据 库 BINLOG 文 件 HDFS 中 存 储 的 各 种 日 志 备 份 所 有 日 志 都 根 据 需 要 配 置 合 理 的 清 理 策 略 5. 性 能 优 化 建 议 5.1. 性 能 优 化 原 则 数 据 库 性 能 优 化 通 常 涵 盖 方 法 论 体 系 特 性 及 工 具 2 个 方 面 方 法 论 体 系 包 含 3 部 分 内 容 : 性 能 规 划 : 深 入 了 解 应 用 与 数 据 库 的 交 互 特 征, 确 立 良 好 的 设 计 开 发 测 试 迭 代 过 程, 上 线 前 消 除 模 型 上 的 性 能 瓶 颈 实 例 调 优 : 建 立 性 能 基 准, 对 比 调 节 数 据 库 操 作 系 统 存 储 网 络 等 的 配 置, 主 动 监 控 消 除 瓶 颈 SQL 调 优 : 书 写 高 效 SQL, 优 化 相 关 数 据 库 对 象, 充 分 借 助 优 化 器, 确 定 最 佳 执 行 计 划 5.2. 性 能 优 化 步 骤 首 先 执 行 下 面 的 初 始 检 查 : 获 取 直 接 用 户 的 使 用 反 馈, 确 定 性 能 目 标 和 范 围 获 取 性 能 表 现 好 与 坏 时 的 操 作 系 统 数 据 库 应 用 统 计 信 息 对 数 据 库 做 一 次 全 面 健 康 检 查 定 期 收 集 数 据 库 统 计 信 息 根 据 收 集 的 信 息, 以 及 对 应 用 特 性 的 了 解, 构 建 性 能 概 念 模 型, 明 确 性 能 瓶 颈 所 在, 以 及 导 致 性 能 的 根 本 原 因 提 出 一 系 列 针 对 的 优 化 措 施, 并 根 据 它 们 对 性 能 改 善 的 重 要 程 度 排 序, 然 后 逐 一 加 以 实 施 不 要 一 次 执 行 所 有 的 优 化 措 施, 必 须 逐 条 尝 试, 逐 步 对 比 通 过 获 取 直 接 用 户 的 反 馈 验 证 调 节 是 否 已 经 产 生 预 期 的 效 果, 否 则, 需 要 重 新 提 炼 性 能 概 念 模 型, 直 到 对 应 用 特 性 了 解 进 一 步 准 确 重 复 上 述, 直 到 性 能 达 到 目 标 或 由 于 客 观 约 束 无 法 进 一 步 优 化 5.3. 常 用 优 化 技 巧 第 17 页 共 27 页

查 看 实 例 long_query 和 long_query_rate 指 标, 分 析 慢 查 询 出 现 频 率 及 规 律 使 用 慢 查 询 分 析 工 具, 从 出 现 频 率 最 高 耗 时 最 长 的 SQL 语 句 开 始 分 析, 通 过 优 化 SQL 语 句, 添 加 索 引 等 方 式 解 决 查 看 实 例 mem_hit_rate 和 mem_available 指 标, 分 析 innodb 的 缓 存 池 是 否 足 够, 内 存 是 否 是 瓶 颈 查 看 cpu_usage_rate, 结 合 慢 查 询 分 析 CPU 消 耗 是 否 合 理,CPU 是 否 是 瓶 颈 调 整 innodb_page_size 参 数, 对 比 性 能 测 试, 找 到 最 合 适 的 配 置 对 常 用 语 句 使 用 EXPLAIN 进 行 查 询 分 析, 找 出 潜 在 的 设 计 问 题 根 据 业 务 场 景 设 计 合 适 的 用 例 对 不 同 规 格 实 例 进 行 性 能 测 试 第 18 页 共 27 页

分 布 式 版 本 编 程 与 使 用 规 范 分 布 式 版 本 编 程 与 使 用 规 范 如 对 本 规 范 有 任 何 意 见 或 建 议, 欢 迎 联 系 腾 讯 云 工 作 人 员 或 直 接 发 送 邮 件 到 benhu@tencent.com 本 文 未 特 定 知 名 的 情 况 下,TDSQL 均 指 代 分 布 式 云 数 据 库 DCDB for TDSQL 1. 概 述 : 1.1 分 布 式 简 介 DCDB for TDSQL 是 部 署 在 腾 讯 云 公 有 云 上 的 一 种 兼 容 MySQL 协 议 和 语 法, 支 持 自 动 水 平 拆 分 的 分 布 式 数 据 库 分 布 式 数 据 库 即 业 务 获 取 是 完 整 的 逻 辑 库 表, 后 端 却 将 库 表 均 匀 的 拆 分 到 多 个 物 理 分 片 节 点 目 前,DCDB for TDSQL 默 认 部 署 主 备 架 构 且 提 供 了 容 灾 备 份 恢 复 监 控 迁 移 等 方 面 的 全 套 解 决 方 案, 适 用 于 TB 或 PB 级 的 海 量 数 据 库 场 景 分 布 式 云 数 据 库 DCDB for TDSQL 采 用 的 水 平 切 分 的 分 布 式 方 案, 即 根 据 表 中 的 数 据 的 逻 辑 关 系, 将 同 一 个 表 中 的 数 据 按 照 某 种 条 件 拆 分 到 多 台 数 据 库 ( 主 机 ) 上 面, 返 种 切 分 称 之 为 数 据 的 水 平 ( 横 向 ) 切 分 1.2 名 词 解 释 DCDB for TDSQL: 是 TDSQL 的 分 布 式 版 本 ( 又 称 为 groupsharding), 本 文 因 不 涉 及 普 通 版 本, 因 此 后 续 简 称 TDSQL 垂 直 切 分 : 只 按 照 业 务 逻 辑 将 表 分 类, 分 布 到 不 同 的 数 据 库 上 面, 这 就 将 一 个 单 一 数 据 库 的 压 力 分 担 到 不 同 的 数 据 库 上 面 水 平 切 分 : 不 是 将 表 在 做 分 类, 而 是 按 照 某 个 字 段 的 某 种 规 则 来 分 散 到 多 个 库 中, 每 个 库 叫 做 一 个 分 片, 一 个 分 片 仅 包 括 一 部 分 数 据 第 19 页 共 27 页

分 片 规 则 : 即 拆 分 数 据 的 规 则, 关 系 型 数 据 库 是 一 个 二 维 的 模 型, 所 以 一 般 有 两 种 典 型 的 方 案 进 行 拆 分 : 采 用 某 个 有 规 律 的 字 段 进 行 人 工 划 分 : 如 日 期 ( 如 2015 年 一 个 分 片,2016 年 一 个 分 片 ) 用 户 身 份 证 号 码 对 特 定 字 段 进 行 hash: 将 根 据 特 定 范 围 字 段 分 布 到 不 同 库 中 ; 这 个 特 定 字 段 就 叫 做 ShardKey 实 例 : 多 个 逻 辑 上 统 一 的 分 片 组 成 一 个 逻 辑 实 例 分 片 : 由 数 据 库 引 擎 组 成 的 物 理 逻 辑 上 的 实 例, 由 一 个 主 节 点 (Master) 若 干 备 节 点 (Slave_n) 若 干 异 地 备 份 节 点 (Watcher_m) 节 点 : 承 载 分 片 的 物 理 设 备 当 然, 节 点 可 以 死 物 理 机, 也 可 以 是 虚 拟 机, 也 可 能 是 一 个 小 集 群 逻 辑 表 : 水 平 切 分 通 常 只 是 物 理 上 的 切 分, 在 逻 辑 层 面, 切 分 后 的 数 据 库 或 数 据 库 表 仍 然 是 一 个 完 整 的 数 据 库 表 proxy :TDSQL 通 过 Tproxy 实 现 自 动 分 库 分 表 逻 辑, 管 理 底 层 的 多 个 物 理 数 据 库 实 例, 对 客 户 端 提 供 唯 一 的 兼 容 mys ql 数 据 库 服 务 端 口 shard: 一 个 物 理 的 数 据 库 实 例, 用 户 看 到 的 一 个 逻 辑 实 例 由 多 个 物 理 实 例 构 成 2. 使 用 规 范 2.1 数 据 库 连 接 分 布 式 云 数 据 库 TDSQL 仍 然 提 供 了 唯 一 的 IP, 端 口 供 用 户 访 问 和 使 用, 例 如 : mysql -h10.231.136.34 -P3306 -utest12 -ptestpassword 2.2 不 兼 容 概 述 第 20 页 共 27 页

分 布 式 云 数 据 库 TDSQL 与 MySQL ( 金 融 级 ) 云 数 据 库 CDB for TDSQL 存 在 下 列 兼 容 点 建 表 时, 需 指 明 shardkey( 即 分 区 字 段 ), 代 码 示 例 如 下 : create table test(a int, b int, ) shardkey=b 跨 节 点 的 join transaction 批 量 插 入 等 如 果 拆 分 到 两 个 不 同 的 分 片, 将 不 支 持 ; 这 时 候 仅 支 持 shardkey 取 值 在 一 样 的 情 况 下 的 批 量 操 作 视 图 存 储 过 程 触 发 器 自 建 分 区 不 支 持 MySQL 3.4.0 以 下 的 版 本 SSL 压 缩 协 议 兼 容 性 : 兼 容 性 说 明 2.2.1 建 表 建 表 的 时 候 必 须 在 最 后 面 指 定 shardkey 的 值, 该 值 为 表 中 的 一 个 字 段 名 字, 会 用 于 SQL 的 路 由 选 择 : mysql> create table test1 ( a int, b int, c char(20) ); ERROR 1005 (07000): Proxy Warning - sql is not legal,tokenizer_gram went wrong mysql> create table test1 ( a int, b int, c char(20) ) shardkey=a; Query OK, 0 rows affected (1.56 sec) shardkey 字 段 有 如 下 限 定 要 求 : 1. 如 存 在 主 键 或 者 唯 一 索 引, 则 shardkey 字 段 必 须 是 主 键 或 者 唯 一 索 引 的 一 部 分 2. 如 包 含 多 个 唯 一 索 引 ( 包 括 主 键 ), 则 这 些 索 引 必 须 有 交 集 字 段, 且 此 字 段 必 须 为 shardkey 3. shardkey 字 段 的 类 型 必 须 是 int,bigint,smallint/char/varchar 4. shardkey 字 段 的 值 不 应 该 有 中 文, 网 关 不 会 转 换 字 符 集, 所 以 不 同 字 符 集 可 能 会 路 由 到 不 同 的 分 区 5. 不 要 update shardkey 字 段 的 值 6. shardkey=a 放 在 sql 的 最 后 面 第 21 页 共 27 页

2.2.2 普 通 sql select: 建 议 您 带 上 shardkey 字 段, 若 不 带 上 也 可 以 使 用, 但 此 时 书 记 先 需 要 全 表 扫 描, 然 后 网 关 进 行 结 果 集 聚 合, 对 性 能 影 响 很 大 : mysql> select * from test1 where a=2; +------+------+---------+ a b c +------+------+---------+ 2 3 record2 2 4 record3 +------+------+---------+ 2 rows in set (0.00 sec) insert/replace: 字 段 必 须 包 含 shardkey, 否 则 会 拒 绝 执 行 该 sql, 因 为 proxy 无 法 确 认 应 该 插 入 到 哪 一 个 分 片 : mysql> insert into test1 (b,c) values(4,"record3"); ERROR 1105 (07000): Proxy Warning - sql have no shardkey mysql> insert into test1 (a,c) values(4,"record3"); Query OK, 1 row affected (0.01 sec) delete/update: 为 了 安 全 考 虑, 我 们 要 求 执 行 该 类 sql 的 时 候 必 须 带 有 where 条 件, 否 则 拒 绝 执 行 该 sql 命 令 : mysql> delete from test1; ERROR 1005 (07000): Proxy Warning - sql is not legal,tokenizer_gram went wrong mysql> delete from test1 where a=1; Query OK, 1 row affected (0.01 sec) 第 22 页 共 27 页

2.2.3 join join: 当 前 TDSQL 只 支 持 单 个 shard 内 的 join 操 作, 单 个 shard 意 味 着 在 一 个 事 务 内 的 所 有 sql 必 须 操 作 同 一 个 s hard, 因 此 必 须 指 定 shardkey 字 段 mysql> create table test1 ( a int, b int, c char(20) ) shardkey=a; Query OK, 0 rows affected (1.56 sec) mysql> create table test2 ( a int, d int, e char(20) ) shardkey=a; Query OK, 0 rows affected (1.46 sec) mysql> insert into test1 (a,b,c) values(1,2,"record1"),(2,3,"record2"); Query OK, 2 rows affected (0.02 sec) mysql> insert into test2 (a,d,e) values(1,3,"test2_record1"),(2,3, "test2_record2"); Query OK, 2 rows affected (0.02 sec) mysql> select * from test1 join test2 on test1.a=test2.a; ERROR 1105 (07000): Proxy Warning - join shardkey error mysql> select * from test1 join test2 on test1.a=test2.a where test1.a=1; +------+------+---------+------+------+---------------+ a b c a d e +------+------+---------+------+------+---------------+ 1 2 record1 1 3 test2_record1 +------+------+---------+------+------+---------------+ 1 row in set (0.00 sec) 注 意 :mysql> select * from test1 join test2 on test1.a=test2.a; 语 句 在 后 续 版 本 中 会 支 持, 但 有 如 下 前 置 条 件 : 1. 必 须 是 inner join 2. inner join 的 where 条 件 必 须 是 2 张 表 的 shardkey 字 段 相 等 第 23 页 共 27 页

2.2.4 事 务 TDSQL 支 持 单 个 shard 内 的 事 务, 单 个 shard 意 味 着 在 一 个 事 务 内 的 所 有 sql 必 须 操 作 同 一 个 shard 执 行 mysql> select * from test1; +------+------+---------+ a b c +------+------+---------+ 2 3 record2 1 2 record1 +------+------+---------+ 2 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test1 (a,b,c) values(2,4,"record3"); Query OK, 1 row affected (0.00 sec) mysql> insert into test1 (a,b,c) values(1,4,"record3"); ERROR 1105 (07000): Proxy ERROR - In transaction,this sql use a diffent backend mysql> select * from test1; ERROR 1105 (07000 ): Proxy ERROR - In transaction,this sql use more than one backend mysql> rollback; Query OK, 0 rows affected (0.01 sec) 注 意 : 后 续 版 本 将 支 持 部 分 简 单 DML 语 句 第 24 页 共 27 页

2.2.5 自 增 字 段 TDSQL 支 持 一 定 意 义 上 的 自 增 字 段, 保 证 某 个 字 段 全 局 唯 一, 但 是 不 保 证 单 调 递 增, 具 体 使 用 方 法 如 下 : 创 建 : create table auto_inc ( a int,b int,c int auto_increment,d int) shardkey=d; 插 入 : mysql> insert into shard.auto_inc ( a,b,d,c) values(1,2,3,0),(1,2,3,0); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from shard.auto_inc; + + + + + a b c d + + + + + 1 2 1 3 1 2 2 3 + + + + + 2 rows in set (0.01 sec) 值 得 说 明 的 是, 如 果 在 proxy 调 度 切 换 重 启 等 过 程 中, 自 增 长 字 段 中 间 会 有 空 洞, 例 如 : mysql> insert into shard.auto_inc ( a,b,d,c) values(11,12,13,0),(21,22,23,0); Query OK, 2 rows affected (0.03 sec) mysql> select * from shard.auto_inc; + + + + + a b c d + + + + + 21 22 2002 23 1 2 1 3 第 25 页 共 27 页

1 2 2 3 11 12 2001 13 + + + + + 4 rows in set (0.01 sec) 更 改 当 前 值 alter table auto auto_increment=100 当 前 版 本 不 支 持 自 增 字 段 作 为 shardkey, 后 续 版 本 计 划 支 持 2.2.6 SQL 命 令 限 制 TDSQL 目 前 支 持 的 sql 命 令 如 下 : 1. delele,update,insert,replace,select 2. alter,create,drop,truncate 3. show,describe(desc,explain),help 4. start,begin,commit,rollback,savepoint 5. set 注 意 : 对 于 一 般 的 查 询 数 据 库 状 态 信 息 的 sql,proxy 会 发 往 默 认 shard, 这 样 查 询 统 计 信 息 的 话, 看 到 的 结 果 是 单 个 shard 的 信 息 2.2.7 用 户 权 限 相 关 限 制 暂 不 支 持 使 用 sql 命 令 通 过 proxy 进 行 用 户 权 限 相 关 的 设 置, 请 至 " 腾 讯 云 管 理 中 心 > 云 数 据 库 >TDSQL> 管 理 第 26 页 共 27 页

Powered by TCPDF (www.tcpdf.org) 最 佳 实 践 产 品 文 档 进 行 操 作 2.2.8 其 它 不 支 持 的 MySQL 特 性 1. 跨 节 点 的 join transaction( 注 : 不 跨 节 点, 即 同 一 个 shardkey 下 的 数 据 操 作,join 和 transaction 是 可 以 支 持 的 ) 2. 视 图 存 储 过 程 触 发 器 3. 自 建 分 区 4. 批 量 数 据 导 入 : 不 支 持 load data local infile 之 类 的 sql, 需 要 转 成 insert 语 句, 类 似 insert into values(xxx,xxx),(xxx,xxx); 5. 子 查 询,having 子 句 2.2.9 聚 合 函 数 限 制 1. 如 果 需 要 distinct 后 再 做 聚 合, 那 么 where 条 件 必 须 带 shardkey: 如 :select count(distinct a),sum(distinct a),avg(distinct a) from table where sk=\*\* 2. 对 于 distinct order by group by 如 果 后 面 跟 函 数 的 话, 该 函 数 必 须 出 现 在 select 的 字 段 中, 而 且 必 须 定 义 别 名, 对 应 distinct order by group by 后 使 用 别 名 : 如 :select concat(...) as substr from table where... order by substr 3. group by 的 字 段 必 须 在 select 的 字 段 中 : 如 :select count(a),b from test group by b, 其 中 select 中 必 须 包 含 字 段 b 第 27 页 共 27 页