第一篇 开发篇

Transcription

1 第 一 篇 开 发 篇...8 第 1 章 帮 助 的 使 用 按 照 层 次 看 帮 助 快 速 查 阅 帮 助...10 第 2 章 表 类 型 ( 存 储 引 擎 ) 的 选 择 Mysql 存 储 引 擎 概 述 各 种 存 储 引 擎 的 特 性 如 何 选 择 合 适 的 存 储 引 擎...12 第 3 章 选 择 合 适 的 数 据 类 型 选 择 数 据 类 型 的 基 本 原 则 固 定 长 度 数 据 列 与 可 变 长 度 的 数 据 列 char 与 varchar text 和 blob 浮 点 数 与 定 点 数...15 第 4 章 字 符 集 字 符 集 概 述 Mysql 支 持 的 字 符 集 简 介 Unicode 简 述 怎 样 选 择 合 适 的 字 符 集 Mysql 字 符 集 的 设 置 第 5 章 索 引 的 设 计 和 使 用 Mysql 索 引 概 述 设 计 索 引 的 原 则 btree 索 引 与 hash 索 引 Mysql 如 何 使 用 索 引 第 6 章 锁 机 制 和 事 务 控 制 如 何 加 锁 死 锁 事 务 控 制...22 第 7 章 SQL 中 的 安 全 问 题...29 网 易 技 术 部 1

2 7.1 SQL 注 入 简 述 开 发 中 可 以 采 取 的 措 施 preparestatement + Bind-variable 使 用 应 用 程 序 提 供 的 转 换 函 数 : 自 己 定 义 函 数 进 行 校 验 第 8 章 SQL Mode 及 相 关 问 题 Mysql SQL Mode 简 介 SQL Mode 与 可 移 植 性 SQL Mode 与 数 据 效 验...34 第 9 章 常 用 SQL 技 巧 检 索 包 含 最 大 / 最 小 值 的 行 巧 用 rand()/rand(n) 提 取 随 机 行 利 用 group by 的 with rollup 子 句 做 统 计 用 bit group functions 做 统 计 第 10 章 其 他 需 注 意 的 问 题 数 据 库 名 表 名 大 小 写 问 题 使 用 外 键 需 注 意 的 地 方...41 第 二 篇 优 化 篇...45 第 11 章 SQL 优 化 优 化 SQL 的 一 般 步 骤 通 过 show status 和 应 用 特 点 了 解 各 种 SQL 的 执 行 频 率 定 位 执 行 效 率 较 低 的 SQL 语 句 : 通 过 EXPLAIN 分 析 低 效 SQL 的 执 行 计 划 : 确 定 问 题, 并 采 取 相 应 的 优 化 措 施 : 索 引 问 题 索 引 的 存 储 分 类 MySQL 如 何 使 用 索 引 查 看 索 引 使 用 情 况 两 个 简 单 实 用 的 优 化 方 法 定 期 分 析 表 :...50 网 易 技 术 部 2

3 使 用 optimize table : 常 用 SQL 的 优 化 大 批 量 插 入 数 据 : 优 化 insert 语 句 : 优 化 group by 语 句 : 优 化 order by 语 句 : 优 化 join 语 句 : mysql 如 何 优 化 or 条 件 : 查 询 优 先 还 是 更 新 (insert update delete) 优 先 : 使 用 SQL 提 示 : 其 他 优 化 措 施...56 第 12 章 优 化 数 据 库 对 象 优 化 表 的 数 据 类 型 通 过 拆 分, 提 高 表 的 访 问 效 率 逆 规 范 化 使 用 冗 余 统 计 表 选 择 更 合 适 的 表 类 型...58 第 13 章 锁 问 题 获 取 锁 等 待 情 况 什 么 情 况 下 使 用 表 锁 什 么 情 况 下 使 用 行 锁 insert select 带 来 的 问 题 next-key 锁 对 并 发 插 入 的 影 响 隔 离 级 别 对 并 发 插 入 的 影 响 如 何 减 少 锁 冲 突...62 第 14 章 优 化 Mysql Server 查 看 Mysql server 当 前 参 数 影 响 Mysql 性 能 的 重 要 参 数 key_buffer_size 的 设 置 table_cache 的 设 置...65 网 易 技 术 部 3

4 innodb_buffer_pool_size 的 设 置 : innodb_flush_log_at_trx_commit 的 设 置 : innodb_additional_mem_pool_size : innodb_table_locks: innodb_lock_wait_timeout: innodb_support_xa: innodb_doublewrite: innodb_log_buffer_size: innodb_log_file_size :...67 第 15 章 I/O 问 题 使 用 磁 盘 阵 列 或 虚 拟 文 件 卷 分 布 I/O 使 用 Symbolic Links 分 布 I/O...68 第 16 章 应 用 优 化 使 用 连 接 池 减 少 对 Mysql 的 访 问 避 免 对 同 一 数 据 做 重 复 检 索 : 使 用 mysql query cache: 加 cache 层 : 负 载 均 衡 利 用 mysql 复 制 分 流 查 询 操 作 : 采 用 分 布 式 数 据 库 架 构 : 第 三 篇 管 理 维 护 篇...73 第 17 章 mysql 安 装 升 级 安 装 安 装 方 法 比 较 rpm 安 装 步 骤 二 进 制 安 装 步 骤 源 码 安 装 步 骤 源 码 安 装 的 性 能 考 虑 : 去 掉 不 需 要 的 模 块 :...75 网 易 技 术 部 4

5 只 选 择 要 使 用 的 字 符 集 : 使 用 pgcc 编 译 : 使 用 静 态 编 译 以 提 高 性 能 : mysql 升 级 mysql 降 级...78 第 18 章 Mysql 日 志 管 理 错 误 日 志 : BINLOG: 查 询 日 志 慢 查 询 日 志 :...80 第 19 章 数 据 备 份 与 恢 复 : 备 份 / 恢 复 策 略 : 冷 备 份 : 逻 辑 备 份 : 单 个 表 的 备 份 : 使 用 备 份 工 具 ibbackup: 时 间 点 恢 复 : 位 置 恢 复 : MyISAM 表 修 复 :...84 第 20 章 Mysql 安 全 : 正 确 设 置 目 录 权 限 : 尽 量 避 免 以 root 权 限 运 行 mysql: 删 除 匿 名 帐 号 : 给 mysql root 帐 号 设 置 口 令 : 设 置 安 全 密 码 并 定 期 修 改 : 只 授 予 帐 号 必 须 的 权 限 : 除 root 外, 任 何 用 户 不 应 有 mysql 库 user 表 的 存 取 权 限 : 不 要 把 FILE PROCESS 或 SUPER 权 限 授 予 管 理 员 以 外 的 帐 号 : load data local 带 来 的 安 全 问 题 : 尽 量 避 免 通 过 symlinks 访 问 表 :...88 网 易 技 术 部 5

6 20.11 使 用 merge 存 储 引 擎 潜 藏 的 安 全 漏 洞 : 防 止 DNS 欺 骗 : drop table 命 令 并 不 收 回 以 前 的 相 关 访 问 授 权 : 使 用 SSL: 如 果 可 能, 给 所 有 用 户 加 上 访 问 IP 限 制 : 严 格 控 制 操 作 系 统 帐 号 和 权 限 : 增 加 防 火 墙 : 其 他 安 全 设 置 选 项 : allow-suspicious-udfs: old-passwords: safe-user-create: secure-auth: skip-grant-tables: skip-networking: skip-show-database: 第 21 章 Mysql 复 制 : Mysql 复 制 概 述 : 安 装 配 置 : 日 常 管 理 维 护 : 经 常 查 看 slave 状 态 怎 样 强 制 主 服 务 器 阻 塞 更 新 直 到 从 服 务 器 同 步? master 执 行 的 语 句 在 slave 上 执 行 失 败 怎 么 办? Slave 上 出 现 log event entry exceeded max_allowed_packet 错 误 怎 么 办? 多 主 复 制 时, 自 动 增 长 变 量 的 冲 突 问 题 怎 么 样 知 道 slave 上 现 在 复 制 到 什 么 地 方 了 需 要 注 意 的 问 题 :...95 第 22 章 Mysql Cluster: Mysql Cluster 概 述 : Mysql Cluster 架 构 :...96 网 易 技 术 部 6

7 22.3 安 装 配 置 : 管 理 节 点 配 置 步 骤 : sql 节 点 和 数 据 节 点 的 配 置 : 管 理 维 护 : Cluster 的 启 动 Cluster 的 关 闭 数 据 备 份 和 恢 复 :...99 第 23 章 Oracle 向 Mysql 数 据 迁 移 : 数 据 类 型 的 差 异 : 利 用 导 出 文 本 迁 移 : 导 出 为 insert sql 文 本 导 出 为 固 定 格 式 文 本 利 用 工 具 软 件 迁 移 : 使 用 DBA 组 开 发 的 迁 移 工 具 : 数 据 迁 移 常 见 问 题 : 字 符 集 问 题 : 特 殊 字 符 处 理 : 日 期 字 段 的 处 理 : 如 何 使 迁 移 过 程 不 被 SQL 错 误 中 断 : 如 何 查 找 产 生 warnings 的 原 因 : 第 24 章 应 急 处 理 : 一 般 处 理 流 程 : 忘 记 root 密 码 : 表 损 坏 如 何 处 理 : MyISAM 表 超 过 4G 无 法 访 问 : 数 据 目 录 磁 盘 空 间 不 足 怎 么 办? 如 何 禁 止 DNS 反 向 解 析? 第 25 章 Mysql 管 理 中 一 些 常 用 的 命 令 和 技 巧 : 参 数 设 置 方 法 : mysql.sock 丢 失 后 怎 么 连 接 数 据 库? 网 易 技 术 部 7

8 25.3 同 一 台 机 器 运 行 多 个 mysql: 查 看 用 户 权 限 : 修 改 用 户 密 码 : 怎 样 灵 活 的 指 定 连 接 的 主 机 : 到 底 匹 配 哪 个 符 合 条 件 的 用 户 : 不 进 入 mysql, 怎 样 运 行 sql 语 句? 客 户 端 怎 么 访 问 内 网 数 据 库? 第 一 篇 开 发 篇 第 1 章 帮 助 的 使 用 在 不 同 的 mysql 版 本 中, 很 多 特 性 和 语 法 有 可 能 是 不 一 样 的, 我 们 怎 么 样 才 能 知 道 当 前 版 本 的 语 法 是 什 么 样 呢? 最 好 的 办 法 是 学 会 使 用 mysql 的 帮 助 方 法 很 简 单 : 1.1 按 照 层 次 看 帮 助 如 果 不 知 道 帮 助 能 够 提 供 些 什 么, 可 以 一 层 一 层 往 下 看 : 命 令 如 下 : mysql>? contents You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> is one of the following categories: Account Management Administration Data Definition Data Manipulation Data Types Functions 网 易 技 术 部 8

9 Functions and Modifiers for Use with GROUP BY Geographic Features Language Structure Plugins Storage Engines Stored Routines Table Maintenance Transactions Triggers 对 于 列 出 的 分 类, 可 以 进 行 看 自 己 感 兴 趣 的 部 分, 例 如 : mysql>? data types You asked for help about help category: "Data Types" For more information, type 'help <item>', where <item> is one of the following topics: AUTO_INCREMENT BIGINT BINARY BIT BLOB BLOB DATA TYPE BOOL EAN 对 于 列 出 的 具 体 数 据 类 型, 可 以 进 一 步 看 详 细 情 况 : mysql>? int Name: 'INT' Description: INT[(M)] [UNSIGNED] [ZEROFILL] 网 易 技 术 部 9

10 A normal-size integer. The signed range is to The unsigned range is 0 to 快 速 查 阅 帮 助 实 际 当 中, 如 果 我 们 需 要 快 速 查 阅 某 项 语 法 时, 可 以 使 用 关 键 字 进 行 快 速 查 询 例 如, 我 想 知 道 show 命 令 都 能 看 些 什 么 东 西, 可 以 用 如 下 命 令 : mysql>? show Name: 'SHOW' Description: SHOW has many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following: SHOW AUTHORS SHOW CHARACTER SET [LIKE 'pattern'] SHOW COLLATION [LIKE 'pattern'] SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern'] SHOW CONTRIBUTORS SHOW CREATE DATABASE db_name SHOW CREATE EVENT event_name SHOW CREATE FUNCTION funcname 我 想 知 道 create table 的 语 法, 可 以 命 令 如 下 : mysql>? create table Name: 'CREATE TABLE' Description: Syntax: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) 网 易 技 术 部 10

11 [table_option...] [partition_options] Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_option...] [partition_options] select_statement 第 2 章 表 类 型 ( 存 储 引 擎 ) 的 选 择 2.1 Mysql 存 储 引 擎 概 述 mysql 支 持 多 种 存 储 引 擎, 在 处 理 不 同 类 型 的 应 用 时, 可 以 通 过 选 择 使 用 不 同 的 存 储 引 擎 提 高 应 用 的 效 率, 或 者 提 供 灵 活 的 存 储 mysql 的 存 储 引 擎 包 括 :MyISAM InnoDB BDB MEMORY MERGE EXAMPLE NDB Cluster ARCHIVE CSV BLACKHOLE FEDERATED 等, 其 中 InnoDB 和 BDB 提 供 事 务 安 全 表, 其 他 存 储 引 擎 都 是 非 事 务 安 全 表 2.2 各 种 存 储 引 擎 的 特 性 下 面 我 们 重 点 介 绍 几 种 常 用 的 存 储 引 擎 并 对 比 各 个 存 储 引 擎 之 间 的 区 别 和 推 荐 使 用 方 式 特 点 Myisam BDB Memory InnoDB Archive 存 储 限 制 没 有 没 有 有 64TB 没 有 事 务 安 全 支 持 支 持 锁 机 制 表 锁 页 锁 表 锁 行 锁 行 锁 网 易 技 术 部 11

12 B 树 索 引 支 持 支 持 支 持 支 持 哈 希 索 引 支 持 支 持 全 文 索 引 支 持 集 群 索 引 支 持 数 据 缓 存 支 持 支 持 索 引 缓 存 支 持 支 持 支 持 数 据 可 压 缩 支 持 支 持 空 间 使 用 低 低 N/A 高 非 常 低 内 存 使 用 低 低 中 等 高 低 批 量 插 入 的 速 度 高 高 高 低 非 常 高 支 持 外 键 支 持 最 常 使 用 的 2 种 存 储 引 擎 : 1. Myisam 是 Mysql 的 默 认 存 储 引 擎, 当 create 创 建 新 表 时, 未 指 定 新 表 的 存 储 引 擎 时, 默 认 使 用 Myisam 每 个 MyISAM 在 磁 盘 上 存 储 成 三 个 文 件 文 件 名 都 和 表 名 相 同, 扩 展 名 分 别 是.frm ( 存 储 表 定 义 ).MYD (MYData, 存 储 数 据 ).MYI (MYIndex, 存 储 索 引 ) 数 据 文 件 和 索 引 文 件 可 以 放 置 在 不 同 的 目 录, 平 均 分 布 io, 获 得 更 快 的 速 度 2. InnoDB 存 储 引 擎 提 供 了 具 有 提 交 回 滚 和 崩 溃 恢 复 能 力 的 事 务 安 全 但 是 对 比 Myisam 的 存 储 引 擎,InnoDB 写 的 处 理 效 率 差 一 些 并 且 会 占 用 更 多 的 磁 盘 空 间 以 保 留 数 据 和 索 引 2.3 如 何 选 择 合 适 的 存 储 引 擎 选 择 标 准 : 根 据 应 用 特 点 选 择 合 适 的 存 储 引 擎, 对 于 复 杂 的 应 用 系 统 可 以 根 据 实 际 情 况 选 择 多 种 存 储 引 擎 进 行 组 合. 下 面 是 常 用 存 储 引 擎 的 适 用 环 境 : 1. MyISAM: 默 认 的 MySQL 插 件 式 存 储 引 擎, 它 是 在 Web 数 据 仓 储 和 其 他 应 用 环 境 下 最 常 使 用 的 存 储 引 擎 之 一 2. InnoDB: 用 于 事 务 处 理 应 用 程 序, 具 有 众 多 特 性, 包 括 ACID 事 务 支 持 网 易 技 术 部 12

13 3. Memory: 将 所 有 数 据 保 存 在 RAM 中, 在 需 要 快 速 查 找 引 用 和 其 他 类 似 数 据 的 环 境 下, 可 提 供 极 快 的 访 问 4. Merge: 允 许 MySQL DBA 或 开 发 人 员 将 一 系 列 等 同 的 MyISAM 表 以 逻 辑 方 式 组 合 在 一 起, 并 作 为 1 个 对 象 引 用 它 们 对 于 诸 如 数 据 仓 储 等 VLDB 环 境 十 分 适 合 第 3 章 选 择 合 适 的 数 据 类 型 3.1 选 择 数 据 类 型 的 基 本 原 则 前 提 : 使 用 适 合 存 储 引 擎 选 择 原 则 : 根 据 选 定 的 存 储 引 擎, 确 定 如 何 选 择 合 适 的 数 据 类 型 下 面 的 选 择 方 法 按 存 储 引 擎 分 类 : 1. MyISAM 数 据 存 储 引 擎 和 数 据 列 MyISAM 数 据 表, 最 好 使 用 固 定 长 度 的 数 据 列 代 替 可 变 长 度 的 数 据 列 2. MEMORY 存 储 引 擎 和 数 据 列 MEMORY 数 据 表 目 前 都 使 用 固 定 长 度 的 数 据 行 存 储, 因 此 无 论 使 用 CHAR 或 VARCHAR 列 都 没 有 关 系 两 者 都 是 作 为 CHAR 类 型 处 理 的 3. InnoDB 存 储 引 擎 和 数 据 列 建 议 使 用 VARCHAR 类 型 对 于 InnoDB 数 据 表, 内 部 的 行 存 储 格 式 没 有 区 分 固 定 长 度 和 可 变 长 度 列 ( 所 有 数 据 行 都 使 用 指 向 数 据 列 值 的 头 指 针 ), 因 此 在 本 质 上, 使 用 固 定 长 度 的 CHAR 列 不 一 定 比 使 用 可 变 长 度 VARCHAR 列 简 单 因 而, 主 要 的 性 能 因 素 是 数 据 行 使 用 的 存 储 总 量 由 于 CHAR 平 均 占 用 的 空 间 多 于 VARCHAR, 因 此 使 用 VARCHAR 来 最 小 化 需 要 处 理 的 数 据 行 的 存 储 总 量 和 磁 盘 I/O 是 比 较 好 的 3.2 固 定 长 度 数 据 列 与 可 变 长 度 的 数 据 列 char 与 varchar CHAR 和 VARCHAR 类 型 类 似, 但 它 们 保 存 和 检 索 的 方 式 不 同 它 们 的 最 大 长 度 和 是 否 尾 部 空 格 被 保 留 等 方 面 也 不 同 在 存 储 或 检 索 过 程 中 不 进 行 大 小 写 转 换 网 易 技 术 部 13

14 下 面 的 表 显 示 了 将 各 种 字 符 串 值 保 存 到 CHAR(4) 和 VARCHAR(4) 列 后 的 结 果, 说 明 了 CHAR 和 VARCHAR 之 间 的 差 别 : 值 CHAR(4) 存 储 需 求 VARCHAR(4) 存 储 需 求 '' ' ' 4 个 字 节 '' 1 个 字 节 'ab' 'ab ' 4 个 字 节 'ab ' 3 个 字 节 'abcd' 'abcd' 4 个 字 节 'abcd' 5 个 字 节 'abcdefgh' 'abcd' 4 个 字 节 'abcd' 5 个 字 节 请 注 意 上 表 中 最 后 一 行 的 值 只 适 用 不 使 用 严 格 模 式 时 ; 如 果 MySQL 运 行 在 严 格 模 式, 超 过 列 长 度 不 的 值 不 保 存, 并 且 会 出 现 错 误 从 CHAR(4) 和 VARCHAR(4) 列 检 索 的 值 并 不 总 是 相 同, 因 为 检 索 时 从 CHAR 列 删 除 了 尾 部 的 空 格 通 过 下 面 的 例 子 说 明 该 差 别 : mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO vc VALUES ('ab ', 'ab '); Query OK, 1 row affected (0.00 sec) mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc; CONCAT(v, '+') CONCAT(c, '+') ab + ab row in set (0.00 sec) 网 易 技 术 部 14

15 3.2.2 text 和 blob 在 使 用 text 和 blob 字 段 类 型 时 要 注 意 以 下 几 点, 以 便 更 好 的 发 挥 数 据 库 的 性 能. 1. BLOB 和 TEXT 值 也 会 引 起 自 己 的 一 些 问 题, 特 别 是 执 行 了 大 量 的 删 除 或 更 新 操 作 的 时 候 删 除 这 种 值 会 在 数 据 表 中 留 下 很 大 的 " 空 洞 ", 以 后 填 入 这 些 " 空 洞 " 的 记 录 可 能 长 度 不 同, 为 了 提 高 性 能, 建 议 定 期 使 用 OPTIMIZE TABLE 功 能 对 这 类 表 进 行 碎 片 整 理. 2. 使 用 合 成 的 (synthetic) 索 引 合 成 的 索 引 列 在 某 些 时 候 是 有 用 的 一 种 办 法 是 根 据 其 它 的 列 的 内 容 建 立 一 个 散 列 值, 并 把 这 个 值 存 储 在 单 独 的 数 据 列 中 接 下 来 你 就 可 以 通 过 检 索 散 列 值 找 到 数 据 行 了 但 是, 我 们 要 注 意 这 种 技 术 只 能 用 于 精 确 匹 配 的 查 询 ( 散 列 值 对 于 类 似 < 或 >= 等 范 围 搜 索 操 作 符 是 没 有 用 处 的 ) 我 们 可 以 使 用 MD5() 函 数 生 成 散 列 值, 也 可 以 使 用 SHA1() 或 CRC32(), 或 者 使 用 自 己 的 应 用 程 序 逻 辑 来 计 算 散 列 值 请 记 住 数 值 型 散 列 值 可 以 很 高 效 率 地 存 储 同 样, 如 果 散 列 算 法 生 成 的 字 符 串 带 有 尾 部 空 格, 就 不 要 把 它 们 存 储 在 CHAR 或 VARCHAR 列 中, 它 们 会 受 到 尾 部 空 格 去 除 的 影 响 合 成 的 散 列 索 引 对 于 那 些 BLOB 或 TEXT 数 据 列 特 别 有 用 用 散 列 标 识 符 值 查 找 的 速 度 比 搜 索 BLOB 列 本 身 的 速 度 快 很 多 3. 在 不 必 要 的 时 候 避 免 检 索 大 型 的 BLOB 或 TEXT 值 例 如,SELECT * 查 询 就 不 是 很 好 的 想 法, 除 非 你 能 够 确 定 作 为 约 束 条 件 的 WHERE 子 句 只 会 找 到 所 需 要 的 数 据 行 否 则, 你 可 能 毫 无 目 的 地 在 网 络 上 传 输 大 量 的 值 这 也 是 BLOB 或 TEXT 标 识 符 信 息 存 储 在 合 成 的 索 引 列 中 对 我 们 有 所 帮 助 的 例 子 你 可 以 搜 索 索 引 列, 决 定 那 些 需 要 的 数 据 行, 然 后 从 合 格 的 数 据 行 中 检 索 BLOB 或 TEXT 值 4. 把 BLOB 或 TEXT 列 分 离 到 单 独 的 表 中 在 某 些 环 境 中, 如 果 把 这 些 数 据 列 移 动 到 第 二 张 数 据 表 中, 可 以 让 你 把 原 数 据 表 中 的 数 据 列 转 换 为 固 定 长 度 的 数 据 行 格 式, 那 么 它 就 是 有 意 义 的 这 会 减 少 主 表 中 的 碎 片, 使 你 得 到 固 定 长 度 数 据 行 的 性 能 优 势 它 还 使 你 在 主 数 据 表 上 运 行 SELECT * 查 询 的 时 候 不 会 通 过 网 络 传 输 大 量 的 BLOB 或 TEXT 值 3.3 浮 点 数 与 定 点 数 为 了 能 够 引 起 大 家 的 重 视, 在 介 绍 浮 点 数 与 定 点 数 以 前 先 让 大 家 看 一 个 例 子 : mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2)); Query OK, 0 rows affected (0.29 sec) 网 易 技 术 部 15

16 mysql> insert into test values( , ); Query OK, 1 row affected (0.07 sec) mysql> select * from test; c1 c row in set (0.00 sec) 从 上 面 的 例 子 中 我 们 看 到 c1 列 的 值 由 变 成 了 , 这 就 是 浮 点 数 的 不 精 确 性 造 成 的 在 mysql 中 float double( 或 real) 是 浮 点 数,decimal( 或 numberic) 是 定 点 数 浮 点 数 相 对 于 定 点 数 的 优 点 是 在 长 度 一 定 的 情 况 下, 浮 点 数 能 够 表 示 更 大 的 数 据 范 围 ; 它 的 缺 点 是 会 引 起 精 度 问 题 在 今 后 关 于 浮 点 数 和 定 点 数 的 应 用 中, 大 家 要 记 住 以 下 几 点 : 1 浮 点 数 存 在 误 差 问 题 ; 2 对 货 币 等 对 精 度 敏 感 的 数 据, 应 该 用 定 点 数 表 示 或 存 储 ; 3 编 程 中, 如 果 用 到 浮 点 数, 要 特 别 注 意 误 差 问 题, 并 尽 量 避 免 做 浮 点 数 比 较 ; 4 要 注 意 浮 点 数 中 一 些 特 殊 值 的 处 理 第 4 章 字 符 集 4.1 字 符 集 概 述 字 符 集 是 一 套 符 号 和 编 码 的 规 则, 不 论 是 在 oracle 数 据 库 还 是 在 mysql 数 据 库, 都 存 在 字 符 集 的 选 择 问 题, 而 且 如 果 在 数 据 库 创 建 阶 段 没 有 正 确 选 择 字 符 集, 那 么 可 能 在 后 期 需 要 更 换 字 符 集, 而 字 符 集 的 更 换 是 代 价 比 较 高 的 操 作, 也 存 在 一 定 的 风 险, 所 以, 我 们 推 荐 在 应 用 开 始 阶 段, 就 按 照 需 求 正 确 的 选 择 合 适 的 字 符 集, 避 免 后 期 不 必 要 的 调 整 网 易 技 术 部 16

17 4.2 Mysql 支 持 的 字 符 集 简 介 mysql 服 务 器 可 以 支 持 多 种 字 符 集 ( 可 以 用 show character set 命 令 查 看 所 有 mysql 支 持 的 字 符 集 ), 在 同 一 台 服 务 器 同 一 个 数 据 库 甚 至 同 一 个 表 的 不 同 字 段 都 可 以 指 定 使 用 不 同 的 字 符 集, 相 比 oracle 等 其 他 数 据 库 管 理 系 统, 在 同 一 个 数 据 库 只 能 使 用 相 同 的 字 符 集, mysql 明 显 存 在 更 大 的 灵 活 性 mysql 的 字 符 集 包 括 字 符 集 (CHARACTER) 和 校 对 规 则 (COLLATION) 两 个 概 念 字 符 集 是 用 来 定 义 mysql 存 储 字 符 串 的 方 式, 校 对 规 则 则 是 定 义 了 比 较 字 符 串 的 方 式 字 符 集 和 校 对 规 则 是 一 对 多 的 关 系, MySQL 支 持 30 多 种 字 符 集 的 70 多 种 校 对 规 则 每 个 字 符 集 至 少 对 应 一 个 校 对 规 则 可 以 用 SHOW COLLATION LIKE 'utf8%'; 命 令 查 看 相 关 字 符 集 的 校 对 规 则 4.3 Unicode 简 述 Unicode 是 一 种 编 码 规 范 我 们 在 这 里 简 述 一 下 Unicode 编 码 产 生 的 历 史 先 从 ASCII 码 说 起,ASCII 码 也 是 一 种 编 码 规 范, 只 不 过 ASCII 码 只 能 最 多 表 示 256 个 字 符, 是 针 对 英 文 产 生 的, 而 面 对 中 文 阿 拉 伯 文 之 类 的 复 杂 文 字,256 个 字 符 显 然 是 不 够 用 的 于 是 各 个 国 家 或 组 织 都 相 继 制 定 了 符 合 自 己 语 言 文 字 的 标 准, 比 如 gb2312 big5 等 等 但 是 这 种 各 自 制 定 自 己 的 标 准 的 做 法 显 然 是 有 很 多 弊 端 的, 于 是 Unicode 编 码 规 范 应 运 而 生 Unicode 也 是 一 种 字 符 编 码 方 法, 不 过 它 是 由 国 际 组 织 设 计, 可 以 容 纳 全 世 界 所 有 语 言 文 字 的 编 码 方 案 Unicode 的 学 名 是 "Universal Multiple-Octet Coded Character Set", 简 称 为 UCS UCS 可 以 看 作 是 "Unicode Character Set" 的 缩 写 Unicode 有 两 套 标 准 UCS-2 和 UCS-4, 前 者 用 2 个 字 节 表 示 一 个 字 符, 后 者 用 4 个 字 节 表 示 一 个 字 符 以 目 前 常 用 的 UCS-2 为 例, 它 可 以 表 示 的 字 符 数 为 2^16=65535, 基 本 上 可 以 容 纳 所 有 的 欧 美 字 符 和 绝 大 多 数 亚 洲 字 符 4.4 怎 样 选 择 合 适 的 字 符 集 我 们 建 议 在 能 够 完 全 满 足 应 用 的 前 提 下, 尽 量 使 用 小 的 字 符 集 因 为 更 小 的 字 符 集 意 网 易 技 术 部 17

18 味 着 能 够 节 省 空 间 减 少 网 络 传 输 字 节 数, 同 时 由 于 存 储 空 间 的 较 小 间 接 的 提 高 了 系 统 的 性 能 有 很 多 字 符 集 可 以 保 存 汉 字, 比 如 utf8 gb2312 gbk latin1 等 等, 但 是 常 用 的 是 gb2312 和 gbk 因 为 gb2312 字 库 比 gbk 字 库 小, 有 些 偏 僻 字 ( 例 如 : 洺 ) 不 能 保 存, 因 此 在 选 择 字 符 集 的 时 候 一 定 要 权 衡 这 些 偏 僻 字 在 应 用 出 现 的 几 率 以 及 造 成 的 影 响, 不 能 做 出 肯 定 答 复 的 话 最 好 选 用 gbk 4.5 Mysql 字 符 集 的 设 置 mysql 的 字 符 集 和 校 对 规 则 有 4 个 级 别 的 默 认 设 置 : 服 务 器 级 数 据 库 级 表 级 和 字 段 级 分 别 在 不 同 的 地 方 设 置, 作 用 也 不 相 同 服 务 器 字 符 集 和 校 对, 在 mysql 服 务 启 动 的 时 候 确 定 可 以 在 my.cnf 中 设 置 : [mysqld] default-character-set=utf8 或 者 在 启 动 选 项 中 指 定 : mysqld --default-character-set=utf8 或 者 在 编 译 的 时 候 指 定 :./configure --with-charset=utf8 如 果 没 有 特 别 的 指 定 服 务 器 字 符 集, 默 认 使 用 latin1 作 为 服 务 器 字 符 集 上 面 三 种 设 置 的 方 式 都 只 指 定 了 字 符 集, 没 有 指 定 校 对 规 则, 这 样 是 使 用 该 字 符 集 默 认 的 校 对 规 则, 如 果 要 使 用 该 字 符 集 的 非 默 认 校 对 规 则, 则 需 要 在 指 定 字 符 集 的 同 时 指 定 校 对 规 则 可 以 用 show variables like 'character_set_server'; 命 令 查 询 当 前 服 务 器 的 字 符 集 和 校 对 规 则 第 5 章 索 引 的 设 计 和 使 用 5.1 Mysql 索 引 概 述 所 有 MySQL 列 类 型 可 以 被 索 引 对 相 关 列 使 用 索 引 是 提 高 SELECT 操 作 性 能 的 最 佳 途 径 根 据 存 储 引 擎 定 义 每 个 表 的 最 大 索 引 数 和 最 大 索 引 长 度 所 有 存 储 引 擎 支 持 每 个 表 至 少 网 易 技 术 部 18

19 16 个 索 引, 总 索 引 长 度 至 少 为 256 字 节 大 多 数 存 储 引 擎 有 更 高 的 限 制 在 MySQL 5.1 中, 对 于 MyISAM 和 InnoDB 表, 前 缀 可 以 达 到 1000 字 节 长 请 注 意 前 缀 的 限 制 应 以 字 节 为 单 位 进 行 测 量, 而 CREATE TABLE 语 句 中 的 前 缀 长 度 解 释 为 字 符 数 当 为 使 用 多 字 节 字 符 集 的 列 指 定 前 缀 长 度 时 一 定 要 加 以 考 虑 还 可 以 创 建 FULLTEXT 索 引 该 索 引 可 以 用 于 全 文 搜 索 只 有 MyISAM 存 储 引 擎 支 持 FULLTEXT 索 引, 并 且 只 为 CHAR VARCHAR 和 TEXT 列 索 引 总 是 对 整 个 列 进 行, 不 支 持 局 部 ( 前 缀 ) 索 引 也 可 以 为 空 间 列 类 型 创 建 索 引 只 有 MyISAM 存 储 引 擎 支 持 空 间 类 型 空 间 索 引 使 用 R- 树 默 认 情 况 MEMORY(HEAP) 存 储 引 擎 使 用 hash 索 引, 但 也 支 持 B- 树 索 引 5.2 设 计 索 引 的 原 则 1. 搜 索 的 索 引 列, 不 一 定 是 所 要 选 择 的 列 换 句 话 说, 最 适 合 索 引 的 列 是 出 现 在 WHERE 子 句 中 的 列, 或 连 接 子 句 中 指 定 的 列, 而 不 是 出 现 在 SELECT 关 键 字 后 的 选 择 列 表 中 的 列 2. 使 用 惟 一 索 引 考 虑 某 列 中 值 的 分 布 对 于 惟 一 值 的 列, 索 引 的 效 果 最 好, 而 具 有 多 个 重 复 值 的 列, 其 索 引 效 果 最 差 例 如, 存 放 年 龄 的 列 具 有 不 同 值, 很 容 易 区 分 各 行 而 用 来 记 录 性 别 的 列, 只 含 有 M 和 F, 则 对 此 列 进 行 索 引 没 有 多 大 用 处 ( 不 管 搜 索 哪 个 值, 都 会 得 出 大 约 一 半 的 行 ) 3. 使 用 短 索 引 如 果 对 串 列 进 行 索 引, 应 该 指 定 一 个 前 缀 长 度, 只 要 有 可 能 就 应 该 这 样 做 例 如, 如 果 有 一 个 CHAR(200) 列, 如 果 在 前 10 个 或 20 个 字 符 内, 多 数 值 是 惟 一 的, 那 么 就 不 要 对 整 个 列 进 行 索 引 对 前 10 个 或 20 个 字 符 进 行 索 引 能 够 节 省 大 量 索 引 空 间, 也 可 能 会 使 查 询 更 快 较 小 的 索 引 涉 及 的 磁 盘 I/O 较 少, 较 短 的 值 比 较 起 来 更 快 更 为 重 要 的 是, 对 于 较 短 的 键 值, 索 引 高 速 缓 存 中 的 块 能 容 纳 更 多 的 键 值, 因 此,MySQL 也 可 以 在 内 存 中 容 纳 更 多 的 值 这 增 加 了 找 到 行 而 不 用 读 取 索 引 中 较 多 块 的 可 能 性 ( 当 然, 应 该 利 用 一 些 常 识 如 仅 用 列 值 的 第 一 个 字 符 进 行 索 引 是 不 可 能 有 多 大 好 处 的, 因 为 这 个 索 引 中 不 会 有 许 多 不 同 的 值 ) 4. 利 用 最 左 前 缀 在 创 建 一 个 n 列 的 索 引 时, 实 际 是 创 建 了 MySQL 可 利 用 的 n 个 索 引 多 列 索 引 可 起 几 个 索 引 的 作 用, 因 为 可 利 用 索 引 中 最 左 边 的 列 集 来 匹 配 行 这 样 的 列 集 称 为 最 左 前 缀 ( 这 与 索 引 一 个 列 的 前 缀 不 同, 索 引 一 个 列 的 前 缀 是 利 用 该 的 前 n 个 字 符 作 为 索 引 值 ) 5. 不 要 过 度 索 引 不 要 以 为 索 引 越 多 越 好, 什 么 东 西 都 用 索 引 是 错 的 每 个 额 外 的 索 引 都 要 占 用 额 外 的 磁 盘 空 间, 并 降 低 写 操 作 的 性 能, 这 一 点 我 们 前 面 已 经 介 绍 过 在 网 易 技 术 部 19

20 修 改 表 的 内 容 时, 索 引 必 须 进 行 更 新, 有 时 可 能 需 要 重 构, 因 此, 索 引 越 多, 所 花 的 时 间 越 长 如 果 有 一 个 索 引 很 少 利 用 或 从 不 使 用, 那 么 会 不 必 要 地 减 缓 表 的 修 改 速 度 此 外,MySQL 在 生 成 一 个 执 行 计 划 时, 要 考 虑 各 个 索 引, 这 也 要 费 时 间 创 建 多 余 的 索 引 给 查 询 优 化 带 来 了 更 多 的 工 作 索 引 太 多, 也 可 能 会 使 MySQL 选 择 不 到 所 要 使 用 的 最 好 索 引 只 保 持 所 需 的 索 引 有 利 于 查 询 优 化 如 果 想 给 已 索 引 的 表 增 加 索 引, 应 该 考 虑 所 要 增 加 的 索 引 是 否 是 现 有 多 列 索 引 的 最 左 索 引 如 果 是, 则 就 不 要 费 力 去 增 加 这 个 索 引 了, 因 为 已 经 有 了 6. 考 虑 在 列 上 进 行 的 比 较 类 型 索 引 可 用 于 < < = = > = > 和 BETWEEN 运 算 在 模 式 具 有 一 个 直 接 量 前 缀 时, 索 引 也 用 于 LIKE 运 算 如 果 只 将 某 个 列 用 于 其 他 类 型 的 运 算 时 ( 如 STRCMP( )), 对 其 进 行 索 引 没 有 价 值 5.3 btree 索 引 与 hash 索 引 对 于 BTREE 和 HASH 索 引, 当 使 用 = <=> IN IS NULL 或 者 IS NOT NULL 操 作 符 时, 关 键 元 素 与 常 量 值 的 比 较 关 系 对 应 一 个 范 围 条 件 Hash 索 引 还 有 一 些 其 它 特 征 : 它 们 只 用 于 使 用 = 或 <=> 操 作 符 的 等 式 比 较 ( 但 很 快 ) 优 化 器 不 能 使 用 hash 索 引 来 加 速 ORDER BY 操 作 ( 该 类 索 引 不 能 用 来 按 顺 序 搜 索 下 一 个 条 目 ) MySQL 不 能 确 定 在 两 个 值 之 间 大 约 有 多 少 行 ( 这 被 范 围 优 化 器 用 来 确 定 使 用 哪 个 索 引 ) 如 果 你 将 一 个 MyISAM 表 改 为 hash- 索 引 的 MEMORY 表, 会 影 响 一 些 查 询 只 能 使 用 整 个 关 键 字 来 搜 索 一 行 ( 用 B- 树 索 引, 任 何 关 键 字 的 最 左 面 的 前 缀 可 用 来 找 到 行 ) 对 于 BTREE 索 引, 当 使 用 > < >= <= BETWEEN!= 或 者 <>, 或 者 LIKE 'pattern'( 其 中 'pattern' 不 以 通 配 符 开 始 ) 操 作 符 时, 关 键 元 素 与 常 量 值 的 比 较 关 系 对 应 一 个 范 围 条 件 常 量 值 系 指 : 查 询 字 符 串 中 的 常 量 同 一 联 接 中 的 const 或 system 表 中 的 列 无 关 联 子 查 询 的 结 果 完 全 从 前 面 类 型 的 子 表 达 式 组 成 的 表 达 式 下 面 是 一 些 WHERE 子 句 中 有 范 围 条 件 的 查 询 的 例 子 : 下 列 范 围 查 询 适 用 于 btree 索 引 和 hash 索 引 SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20); 下 列 范 围 查 询 适 用 于 btree 索 引 网 易 技 术 部 20

21 SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10; SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo'; 5.4 Mysql 如 何 使 用 索 引 索 引 用 于 快 速 找 出 在 某 个 列 中 有 一 特 定 值 的 行 不 使 用 索 引,MySQL 必 须 从 第 1 条 记 录 开 始 然 后 读 完 整 个 表 直 到 找 出 相 关 的 行 表 越 大, 花 费 的 时 间 越 多 如 果 表 中 查 询 的 列 有 一 个 索 引,MySQL 能 快 速 到 达 一 个 位 置 去 搜 寻 到 数 据 文 件 的 中 间, 没 有 必 要 看 所 有 数 据 如 果 一 个 表 有 1000 行, 这 比 顺 序 读 取 至 少 快 100 倍 注 意 如 果 你 需 要 访 问 大 部 分 行, 顺 序 读 取 要 快 得 多, 因 为 此 时 我 们 避 免 磁 盘 搜 索 大 多 数 MySQL 索 引 (PRIMARY KEY UNIQUE INDEX 和 FULLTEXT) 在 B 树 中 存 储 只 是 空 间 列 类 型 的 索 引 使 用 R- 树, 并 且 MEMORY 表 还 支 持 hash 索 引 关 于 什 么 情 况 下 数 据 库 会 使 用 索 引 以 及 什 么 情 况 下 数 据 库 不 会 使 用 索 引 的 详 细 解 释 请 看 优 化 篇 的 相 关 章 节, 这 里 就 不 再 累 述 第 6 章 锁 机 制 和 事 务 控 制 6.1 如 何 加 锁 锁 定 表 的 语 法 : LOCK TABLES tbl_name [AS alias] {READ [LOCAL] [LOW_PRIORITY] WRITE} [, tbl_name [AS alias] {READ [LOCAL] [LOW_PRIORITY] WRITE}]... 解 锁 语 法 : UNLOCK TABLES innodb 的 存 储 引 擎 提 供 行 级 锁, 支 持 共 享 锁 和 排 他 锁 两 种 锁 定 模 式, 以 及 四 种 不 同 的 隔 离 级 别 网 易 技 术 部 21

22 6.2 死 锁 InnoDB 自 动 检 测 事 务 的 死 锁, 并 回 滚 一 个 或 几 个 事 务 来 防 止 死 锁 InnoDB 不 能 在 MySQL LOCK TABLES 设 定 表 锁 定 的 地 方 或 者 涉 及 InnoDB 之 外 的 存 储 引 擎 设 置 锁 定 的 地 方 检 测 死 锁 你 必 须 通 过 设 定 innodb_lock_wait_timeout 系 统 变 量 的 值 来 解 决 这 些 情 况 如 果 要 依 靠 锁 等 待 超 时 来 解 决 死 锁 问 题, 对 于 更 新 事 务 密 集 的 应 用, 将 有 可 能 导 致 大 量 事 务 的 锁 等 待, 导 致 系 统 异 常, 所 以 不 推 荐 在 一 个 事 务 中 混 合 更 新 不 同 存 储 类 型 的 表, 也 不 推 荐 相 同 类 型 的 表 采 用 不 同 的 锁 定 方 式 加 锁 6.3 事 务 控 制 事 务 MySQL 通 过 SET AUTOCOMMIT, START TRANSACTION, COMMIT 和 ROLLBACK 等 语 句 支 持 本 地 语 法 : START TRANSACTION BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET AUTOCOMMIT = {0 1} 默 认 情 况 下,mysql 是 autocommit 的, 如 果 需 要 通 过 明 确 的 commit 和 rollback 来 提 交 和 回 滚 事 务, 那 么 需 要 通 过 明 确 的 事 务 控 制 命 令 来 开 始 事 务, 这 是 和 oracle 的 事 务 管 理 明 显 不 同 的 地 方, 如 果 应 用 是 从 oracle 数 据 库 迁 移 到 mysql 数 据 库, 则 需 要 确 保 应 用 中 是 否 对 事 务 进 行 了 明 确 的 管 理 START TRANSACTION 或 BEGIN 语 句 可 以 开 始 一 项 新 的 事 务 COMMIT 和 ROLLBACK 用 来 提 交 或 者 回 滚 事 务 CHAIN 和 RELEASE 子 句 分 别 用 来 定 义 在 事 务 提 交 或 者 回 滚 之 后 的 操 作,chain 会 立 即 启 动 一 个 新 事 物, 并 且 和 刚 才 的 事 务 具 有 相 同 的 隔 离 级 别,release 则 会 断 开 和 客 户 端 的 连 接 SET AUTOCOMMIT 可 以 修 改 当 前 连 接 的 提 交 方 式, 如 果 设 置 了 SET AUTOCOMMIT=0, 则 设 置 之 后 的 所 有 事 务 都 需 要 通 过 明 确 的 命 令 进 行 提 交 或 者 回 滚 如 果 我 们 只 是 对 某 些 语 句 需 要 进 行 事 务 控 制, 则 使 用 START TRANSACTION 开 始 一 个 事 务 网 易 技 术 部 22

23 比 较 方 便, 这 样 事 务 结 束 之 后 可 以 自 动 回 到 自 动 提 交 的 方 式, 如 果 我 们 希 望 我 们 所 有 的 事 务 都 不 是 自 动 提 交 的, 那 么 通 过 修 改 AUTOCOMMIT 来 控 制 事 务 比 较 方 便, 这 样 不 用 在 每 个 事 务 开 始 的 时 候 再 执 行 START TRANSACTION time session_1 session_ > mysql> select * from tt3; Empty set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt3 values('1',1); Query OK, 1 row affected (0.03 sec) mysql> commit; Query OK, 0 rows affected (0.05 sec) mysql> select * from tt3; Empty set (0.00 sec) mysql> select * from tt3; Empty set (0.00 sec) mysql> select * from tt3; id name row in set (0.00 sec) 网 易 技 术 部 23

24 mysql> insert into tt3 values('2',2); Query OK, 1 row affected (0.04 sec) 这 个 事 务 是 按 照 自 动 提 交 执 行 的 mysql> select * from tt3; id name rows in set (0.00 sec) 网 易 技 术 部 24

25 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt3 values('3',3); Query OK, 1 row affected (0.00 sec) mysql> commit and chain; Query OK, 0 rows affected (0.05 sec) 自 动 开 始 一 个 新 的 事 务 mysql> insert into tt3 values('4',4); Query OK, 1 row affected (0.00 sec) mysql> select * from tt3; id name rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.06 sec) 网 易 技 术 部 25

26 mysql> select * from tt3; id name rows in set (0.00 sec) 开 始 一 个 事 务, 会 造 成 一 个 隐 含 的 unlock tables 被 执 行 : time session_1 session_ > mysql> select * from tt3; Empty set (16.65 sec) mysql> lock table tt3 write; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt3 values('1',1); Query OK, 1 row affected (0.07 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt3; Empty set (16.65 sec) mysql> select * from tt3; 等 待 等 待 等 待 网 易 技 术 部 26

27 mysql> start transaction; 等 待 Query OK, 0 rows affected (0.00 sec) mysql> select * from tt3; id name row in set (37.71 sec) 开 始 一 个 事 务 时, 表 锁 被 释 放 对 lock 方 式 加 的 表 锁, 不 能 通 过 rollback 进 行 回 滚 因 此, 在 同 一 个 事 务 中, 最 好 不 使 用 不 同 存 储 引 擎 的 表, 否 则 rollback 时 需 要 对 非 事 务 类 型 的 表 进 行 特 别 的 处 理, 因 为 commit rollback 只 能 对 事 务 类 型 的 表 进 行 提 交 和 回 滚 通 常 情 况 下, 只 对 提 交 的 事 务 纪 录 到 二 进 制 的 日 志 中, 但 是 如 果 一 个 事 务 中 包 含 非 事 务 类 型 的 表, 那 么 回 滚 操 作 也 会 被 记 录 到 二 进 制 日 志 中, 以 确 保 非 事 务 类 型 表 的 更 新 可 以 被 复 制 到 从 的 数 据 库 中 和 oracle 的 事 务 管 理 相 同, 所 有 的 DDL 语 句 是 不 能 回 滚 的, 并 且 部 分 的 DDL 语 句 会 造 成 隐 式 的 提 交 在 事 务 中 可 以 通 过 定 义 savepoint, 指 定 回 滚 事 务 的 一 个 部 分, 但 是 不 能 指 定 提 交 事 务 的 一 个 部 分 对 于 复 杂 的 应 用, 可 以 定 义 多 个 不 同 的 savepoint, 满 足 不 同 的 条 件 时, 回 滚 不 同 的 savepoint 需 要 注 意 的 是, 如 果 定 义 了 相 同 名 字 的 savepoint, 则 后 面 定 义 的 savepoint 会 覆 盖 之 前 的 定 义 对 于 不 再 需 要 使 用 的 savepoint, 可 以 通 过 release savepoint 命 令 删 除 savepoint, 删 除 后 的 savepoint, 不 能 再 执 行 rollback to savepoint 命 令 下 面 我 们 例 子 就 是 模 拟 回 滚 事 务 的 一 个 部 分, 通 过 定 义 savepoint 来 指 定 需 要 回 滚 的 事 务 的 位 置 网 易 技 术 部 27

28 time session_1 session_ > mysql> select * from tt3; id name rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> delete from tt3 where id = '2'; Query OK, 1 row affected (0.00 sec) mysql> select * from tt3; id name rows in set (0.00 sec) mysql> select * from tt3; id name rows in set (0.00 sec) mysql> select * from tt3; id name rows in set (0.00 sec) 网 易 技 术 部 28

29 mysql> savepoint test; Query OK, 0 rows affected (0.00 sec) mysql> delete from tt3 where id = '3'; Query OK, 1 row affected (0.00 sec) mysql> select * from tt3; id name rows in set (0.00 sec) mysql> select * from tt3; id name rows in set (0.00 sec) mysql> rollback to savepoint test; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt3; id name rows in set (0.00 sec) mysql> select * from tt3; id name rows in set (0.00 sec) 网 易 技 术 部 29

30 mysql> commit; Query OK, 0 rows affected (0.05 sec) mysql> select * from tt3; id name rows in set (0.00 sec) mysql> select * from tt3; id name rows in set (0.00 sec) 第 7 章 SQL 中 的 安 全 问 题 7.1 SQL 注 入 简 述 SQL Injection 攻 击 具 有 很 大 的 危 害, 攻 击 者 可 以 利 用 它 读 取 修 改 或 者 删 除 数 据 库 内 的 数 据, 获 取 数 据 库 中 的 用 户 名 和 密 码 等 敏 感 信 息, 甚 至 可 以 获 得 数 据 库 管 理 员 的 权 限 如 果 能 够 再 利 用 SQLServer 扩 展 存 储 过 程 和 自 定 义 扩 展 存 储 过 程 来 执 行 一 些 系 统 命 令, 攻 击 者 还 可 以 获 得 该 系 统 的 控 制 权 而 且,SQL Injection 也 很 难 防 范 网 站 管 理 员 无 法 通 过 安 装 系 统 补 丁 或 者 进 行 简 单 的 安 全 配 置 进 行 自 我 保 护, 一 般 的 防 火 墙 也 无 法 拦 截 SQL Injection 攻 击 SQL Injection 原 理 : 结 构 化 查 询 语 言 (SQL) 是 一 种 用 来 和 数 据 库 交 互 的 文 本 语 言 SQL Injection 就 是 利 用 某 些 数 据 库 的 外 部 接 口 把 用 户 数 据 插 入 到 实 际 的 数 据 库 操 作 语 言 (SQL) 当 中, 从 而 达 到 入 侵 数 据 库 乃 至 操 作 系 统 的 目 的 它 的 产 生 主 要 是 由 于 程 序 对 用 户 输 入 的 数 据 没 有 进 行 严 格 的 过 滤, 导 致 非 法 数 据 库 查 询 语 句 的 执 行 如 下 面 的 用 户 登 陆 验 证 程 序 : $sql = "SELECT * FROM user WHERE username='$username' AND password='$password'"; 网 易 技 术 部 30

31 $result = mysql_db_query($dbname, $sql); 如 果 我 们 提 交 如 下 url: or '1=1 那 么 就 可 以 成 功 登 陆 系 统, 但 是 很 显 然 这 并 不 是 我 们 预 期 的, 同 样 我 们 也 可 以 利 用 sql 的 注 释 语 句 实 现 sql 注 入, 如 下 面 的 例 子 : 这 样 就 把 后 面 的 语 句 给 注 释 掉 了! 说 说 这 两 种 提 交 的 不 同 之 处, 我 们 提 交 的 第 一 句 是 利 用 逻 辑 运 算, 第 二 三 句 是 根 据 mysql 的 特 性,mysql 支 持 /* 和 # 两 种 注 释 格 式, 所 以 我 们 提 交 的 时 候 是 把 后 面 的 代 码 注 释 掉, 值 得 注 意 的 是 由 于 编 码 问 题, 在 IE 地 址 栏 里 提 交 # 会 变 成 空 的, 所 以 我 们 在 地 址 栏 提 交 的 时 候, 应 该 提 交 %23, 才 会 变 成 #, 就 成 功 注 释 了, 这 个 比 逻 辑 运 算 简 单 得 多 了 7.2 开 发 中 可 以 采 取 的 措 施 preparestatement + Bind-variable 对 Java,Jsp 开 发 的 应 用, 可 以 使 用 preparestatement + Bind-variable 来 防 止 sql 注 入, 另 外 从 PHP 5 开 始, 也 在 扩 展 的 mysqli 中 支 持 prepared statements, 所 以 在 使 用 这 类 语 言 作 数 据 库 开 发 时, 强 烈 建 议 使 用 preparestatement + Bind-variable 来 实 现, 而 尽 量 不 要 使 用 拼 接 的 sql 使 用 应 用 程 序 提 供 的 转 换 函 数 : 很 多 应 用 程 序 接 口 都 提 供 了 对 特 殊 的 字 符 进 行 转 换 的 函 数, 恰 当 的 使 用 这 些 函 数, 可 以 防 止 应 用 程 序 用 户 输 入 使 应 用 程 序 生 成 不 期 望 的 效 果 的 语 句 的 数 值 MySQL C API: 使 用 mysql_real_escape_string() API 调 用 MySQL++ : 使 用 escape 和 quote 修 饰 符 PHP : 使 用 mysql_real_escape_string() 函 数 ( 适 用 于 PHP 4.3.0, 之 前 的 版 本 请 使 用 mysql_escape_string(), PHP 之 前 的 版 本 请 使 用 addslashes()) 从 PHP 5 开 始, 可 以 使 用 扩 展 的 mysqli, 这 是 对 MYSQL 新 特 性 的 一 个 扩 展 支 持, 其 中 的 一 个 优 点 就 是 支 持 prepared statements 网 易 技 术 部 31

32 Perl DBI : 使 用 placeholders 或 者 quote() 方 法 Ruby DBI : 使 用 placeholders 或 者 quote() 方 法 Java JDBC : 使 用 PreparedStatement 和 placeholders 自 己 定 义 函 数 进 行 校 验 如 果 现 有 的 转 换 函 数 仍 然 不 能 满 足 要 求, 则 需 要 自 己 编 写 函 数 进 行 输 入 校 验 输 入 验 证 是 一 个 很 复 杂 的 问 题 输 入 验 证 的 途 径 可 以 分 为 以 下 几 种 : 整 理 数 据 使 之 变 得 有 效 ; 拒 绝 已 知 的 非 法 输 入 ; 只 接 受 已 知 的 合 法 的 输 入 所 以 如 果 想 要 获 得 最 好 的 安 全 状 态, 目 前 最 好 的 解 决 办 法 就 是 对 用 户 提 交 或 者 可 能 改 变 的 数 据 进 行 简 单 分 类, 分 别 应 用 正 则 表 达 式 来 对 用 户 提 供 的 输 入 数 据 进 行 严 格 的 检 测 和 验 证 第 8 章 SQL Mode 及 相 关 问 题 8.1 Mysql SQL Mode 简 介 MySQL 服 务 器 能 够 工 作 在 不 同 的 SQL 模 式 下, 并 能 针 对 不 同 的 客 户 端 以 不 同 的 方 式 应 用 这 些 模 式 这 样, 应 用 程 序 就 能 对 服 务 器 操 作 进 行 量 身 定 制 以 满 足 自 己 的 需 求 这 类 模 式 定 义 了 MySQL 应 支 持 的 SQL 语 法, 以 及 应 该 在 数 据 上 执 行 何 种 确 认 检 查 这 样, 就 能 在 众 多 不 同 的 环 境 下 与 其 他 数 据 库 服 务 器 一 起 更 容 易 地 使 用 MySQL 可 以 使 用 --sqlmode="modes" 选 项, 通 过 启 动 mysqld 来 设 置 默 认 的 SQL 模 式 从 MySQL 4.1 开 始, 也 能 在 启 动 之 后, 使 用 SET [SESSION GLOBAL] sql_mode='modes' 语 句, 通 过 设 置 sql_mode 变 量 更 改 模 式 通 常 在 linux 下 安 装 完 mysql 后, 默 认 的 sql-mode 值 是 空, 在 这 种 情 形 下 mysql 执 行 的 是 一 种 不 严 格 的 检 查, 例 如 日 期 字 段 可 以 插 入 :00:00 这 样 的 值, 还 有 如 果 要 插 入 的 字 段 长 度 超 过 列 定 义 的 长 度, 那 么 mysql 不 会 终 止 操 作, 而 是 会 自 动 截 断 后 面 的 字 符 继 续 插 入 操 作, 如 下 例 : 网 易 技 术 部 32

33 mysql> create table t5 (c1 char(3)); mysql> insert into t5 values('abcd'); mysql> select * from t5; c abc row in set (0.00 sec) 我 们 发 现 插 入 的 字 符 被 自 动 截 断 了, 但 是 如 果 我 们 本 意 希 望 如 果 长 度 超 过 限 制 就 报 错, 那 么 我 们 可 以 设 置 sql_mode 为 STRICT_TRANS_TABLES, 如 下 : mysql> set session sql_mode='strict_trans_tables' 这 样 我 们 再 执 行 同 样 的 操 作,mysql 就 会 告 诉 我 们 插 入 的 值 太 长, 操 作 被 终 止, 如 下 : mysql> insert into t5 values('abcd'); ERROR 1406 (22001): Data too long for column 'c1' at row 1 经 常 使 用 的 sql_mode 值 如 下 表 : Sql_mode 值 ANSI STRICT_TRANS_T ABLES 描 述 更 改 语 法 和 行 为, 使 其 更 符 合 标 准 SQL 如 果 不 能 将 给 定 的 值 插 入 到 事 务 表 中, 则 放 弃 该 语 句 对 于 非 事 务 表, 如 果 值 出 现 在 单 行 语 句 或 多 行 语 句 的 第 1 行, 则 放 弃 该 语 句 本 节 后 面 给 出 了 更 详 细 的 描 述 TRADITIONAL Make MySQL 的 行 为 象 传 统 SQL 数 据 库 系 统 该 模 式 的 简 单 描 述 是 当 在 列 中 插 入 不 正 确 的 值 时 给 出 错 误 而 不 是 警 告 注 释 : 一 旦 发 现 错 误 立 即 放 弃 INSERT/UPDATE 如 果 你 使 用 非 事 务 存 储 引 擎, 这 种 方 式 不 是 你 想 要 的, 因 为 出 现 错 误 前 进 行 的 数 据 更 改 不 会 滚 动, 结 果 是 更 新 只 进 行 了 一 部 分 说 明 : 如 果 把 sql_mode 的 值 设 置 成 后 面 的 两 个 值 ( 也 就 是 我 们 说 的 严 格 模 式 ), 那 么 当 在 列 中 插 入 或 更 新 不 正 确 的 值 时,mysql 将 会 给 出 错 误, 并 且 放 弃 insert/update 操 作 在 我 们 的 一 般 应 用 中 建 议 使 用 这 两 种 模 式, 而 不 是 使 用 默 认 的 空 或 ANSI 模 式 但 是 需 要 注 意 的 问 题 是, 如 果 数 据 库 运 行 在 严 格 模 式 下, 并 且 你 的 存 储 引 擎 不 支 持 事 务, 那 么 有 数 据 不 一 致 的 风 网 易 技 术 部 33

34 险 存 在, 比 如 一 组 sql 中 有 两 个 dml 语 句, 如 果 后 面 的 一 个 出 现 了 问 题, 但 是 前 面 的 已 经 操 作 成 功, 那 么 mysql 并 不 能 回 滚 前 面 的 操 作 因 此 说 设 置 sql_mode 需 要 应 用 人 员 权 衡 各 种 得 失, 从 而 得 到 一 个 合 适 的 选 择 Sql_mode 的 值 还 有 很 多, 这 里 不 再 累 述, 可 以 参 考 相 关 的 手 册 8.2 SQL Mode 与 可 移 植 性 如 果 mysql 与 其 它 异 构 数 据 库 之 间 有 数 据 移 植 的 需 求 的 话, 那 么 下 面 的 sql_mode 的 组 合 设 置 可 以 达 到 相 应 的 效 果 : 数 据 库 Sql_mode 值 DB2 PIPES_AS_CONCAT ANSI_QUOTES IGNORE_SPACE NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_FIELD_OPTIONS MAXDB PIPES_AS_CONCAT ANSI_QUOTES IGNORE_SPACE NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_FIELD_OPTIONS NO_AUTO_CREATE_USER MSSQL PIPES_AS_CONCAT ANSI_QUOTES IGNORE_SPACE NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_FIELD_OPTIONS ORACLE PIPES_AS_CONCAT ANSI_QUOTES IGNORE_SPACE NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_FIELD_OPTIONS NO_AUTO_CREATE_USER POSTGRESQL PIPES_AS_CONCAT ANSI_QUOTES IGNORE_SPACE NO_KEY_OPTIONS NO_TABLE_OPTIONS NO_FIELD_OPTIONS 8.3 SQL Mode 与 数 据 效 验 SQL Mode 还 可 以 实 现 对 数 据 效 验 和 转 移 等 功 能 如 : 效 验 日 期 数 据 合 法 性. 在 INSERT 或 UPDATE 过 程 中, 如 果 被 零 除 ( 或 MOD(X,0)), 则 产 生 错 误 将 " 视 为 识 别 符 引 号 ( ` 引 号 字 符 ) 禁 用 反 斜 线 字 符 ( \ ) 做 为 字 符 串 内 的 退 出 字 符 启 用 NO_BACKSLASH_ESCAPES 模 式, 反 斜 线 则 成 为 普 通 字 符 将 视 为 字 符 串 连 接 操 作 符 (+)( 同 CONCAT()), 而 不 视 为 OR 网 易 技 术 部 34

35 第 9 章 常 用 SQL 技 巧 9.1 检 索 包 含 最 大 / 最 小 值 的 行 MIN([DISTINCT] expr), MAX([DISTINCT] expr) 返 回 expr 的 最 小 值 和 最 大 值 MIN() 和 MAX() 的 取 值 可 以 是 一 个 字 符 串 参 数 ; 在 这 些 情 况 下, 它 们 返 回 最 小 或 最 大 字 符 串 值 DISTINCT 关 键 词 可 以 被 用 来 查 找 expr 的 不 同 值 的 最 小 或 最 大 值, 然 而, 这 产 生 的 结 果 与 省 略 DISTINCT 的 结 果 相 同 若 找 不 到 匹 配 的 行,MIN() 和 MAX() 返 回 NULL mysql> SELECT student_name, MIN(test_score), MAX(test_score) -> FROM student -> GROUP BY student_name; 9.2 巧 用 rand()/rand(n) 提 取 随 机 行 可 按 照 如 下 的 随 机 顺 序 检 索 数 据 行, 如 下 : mysql> SELECT * FROM tbl_name ORDER BY RAND(); ORDER BY RAND() 同 LIMIT 的 结 合 可 以 从 一 组 列 中 选 择 随 机 样 本, 如 下 : mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000; 9.3 利 用 group by 的 with rollup 子 句 做 统 计 create table sales ( year int not null, country varchar(20) not null, product varchar(32) not null, profit int 网 易 技 术 部 35

36 ); select year, sum(profit) from sales group by year; select year, sum(profit) from sales group by year with rollup; select year, country, product, sum(profit) from sales group by year, country, product; select year, country, product, sum(profit) from sales group by year, country, product with rollup; insert into sales values(2004,'china','tnt2004',2001); insert into sales values(2004,'china','tnt2004',2002); insert into sales values(2004,'china','tnt2004',2003); insert into sales values(2005,'china','tnt2005',2004); insert into sales values(2005,'china','tnt2005',2005); insert into sales values(2005,'china','tnt2005',2006); insert into sales values(2005,'china','tnt2005',2007); insert into sales values(2005,'china','tnt2005',2008); insert into sales values(2005,'china','tnt2005',2009); insert into sales values(2006,'china','tnt2006',2010); insert into sales values(2006,'china','tnt2006',2011); insert into sales values(2006,'china','tnt2006',2012); select year, country, product, sum(profit) from sales group by year, country, product; select year, country, product, sum(profit) from sales group by year, country, product with rollup; 网 易 技 术 部 36

37 SELECT year, country, product, SUM(profit) FROM sales GROUP BY year, country, product WITH ROLLUP LIMIT 5; 当 你 使 用 ROLLUP 时, 你 不 能 同 时 使 用 ORDER BY 子 句 进 行 结 果 排 序 换 言 之, ROLLUP 和 ORDER BY 是 互 相 排 斥 的 LIMIT 用 在 ROLLUP 后 面 9.4 用 bit group functions 做 统 计 Group by 统 计 语 句 的 一 般 用 法 : SELECT student_name, AVG([DISTINCT] expr) -> FROM student -> GROUP BY student_name; 在 本 小 节, 主 要 介 绍 group by 语 句 和 bit_and,bit_or 函 数 共 同 使 用 完 成 统 计 工 作, 先 来 了 解 一 下 bit_and 和 bit_or 函 数, 举 例 如 下 : mysql> CREATE TABLE `ta` ( -> `id` smallint(5) unsigned NOT NULL default '0', -> KEY `id` (`id`) -> ) TYPE=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `ta` VALUES("1"),("2"),("3"),("4"); Query OK, 8 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT BIT_OR(id) from ta; BIT_OR(id) 网 易 技 术 部 37

38 row in set (0.00 sec) # # # # # OR # # mysql> SELECT BIT_AND(id) from ta; BIT_AND(id) row in set (0.00 sec) # # # # # AND # # 熟 悉 了 bit_and 和 bit_or 后 我 们 一 起 来 学 习 一 下 bit_and,bit_or 和 group by 函 数 共 同 使 用 进 行 统 计 工 作. 针 对 上 面 的 表 ta 我 们 增 加 字 段 cust_type 并 按 这 个 字 段 的 值 进 行 分 类 统 计 alter table ta add column cust_type varchar(100); update ta set cust_type ='2' update ta set cust_type ='1' where id>3; where cust_type is null; mysql> SELECT cust_type,bit_or(id) from ta group by cust_type; 网 易 技 术 部 38

39 cust_type BIT_OR(id) rows in set (0.00 sec) mysql> SELECT cust_type,bit_and(id) from ta group by cust_type; cust_type BIT_and(id) rows in set (0.00 sec) BIT_AND(expr) 返 回 expr 中 所 有 比 特 的 bitwise AND 计 算 执 行 的 精 确 度 为 64 比 特 (BIGINT) 若 找 不 到 匹 配 的 行, 则 这 个 函 数 返 回 ( 这 是 无 符 号 BIGINT 值, 所 有 比 特 被 设 置 为 1) BIT_OR(expr) 返 回 expr 中 所 有 比 特 的 bitwise OR 计 算 执 行 的 精 确 度 为 64 比 特 (BIGINT) 若 找 不 到 匹 配 的 行, 则 函 数 返 回 0 BIT_XOR(expr) 网 易 技 术 部 39

40 返 回 expr 中 所 有 比 特 的 bitwise XOR 计 算 执 行 的 精 确 度 为 64 比 特 (BIGINT) 若 找 不 到 匹 配 的 行, 则 函 数 返 回 0 第 10 章 其 他 需 注 意 的 问 题 10.1 数 据 库 名 表 名 大 小 写 问 题 在 MySQL 中, 数 据 库 对 应 数 据 目 录 中 的 目 录 数 据 库 中 的 每 个 表 至 少 对 应 数 据 库 目 录 中 的 一 个 文 件 ( 也 可 能 是 多 个, 取 决 于 存 储 引 擎 ) 因 此, 所 使 用 操 作 系 统 的 大 小 写 敏 感 性 决 定 了 数 据 库 名 和 表 名 的 大 小 写 敏 感 性 这 说 明 在 大 多 数 Unix 中 数 据 库 名 和 表 名 对 大 小 写 敏 感, 而 在 Windows 中 对 大 小 写 不 敏 感 一 个 显 著 的 例 外 情 况 是 Mac OS X, 它 基 于 Unix 但 使 用 默 认 文 件 系 统 类 型 (HFS+), 对 大 小 写 不 敏 感 然 而,Mac OS X 也 支 持 UFS 卷, 该 卷 对 大 小 写 敏 感, 就 像 Unix 一 样 注 释 : 尽 管 在 某 些 平 台 中 数 据 库 名 和 表 名 对 大 小 写 不 敏 感, 不 应 在 同 一 查 询 中 使 用 不 同 的 大 小 写 来 引 用 给 定 的 数 据 库 或 表 下 面 的 查 询 不 会 工 作, 因 为 它 同 时 引 用 了 表 my_tables 和 as MY_tables: mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1; 列 索 引 存 储 子 程 序 和 触 发 器 名 在 任 何 平 台 上 对 大 小 写 不 敏 感, 列 的 别 名 也 不 敏 感 默 认 情 况, 表 别 名 在 Unix 中 对 大 小 写 敏 感, 但 在 Windows 或 Mac OS X 中 对 大 小 写 不 敏 感 下 面 的 查 询 在 Unix 中 不 会 工 作, 因 为 它 同 时 引 用 了 别 名 a 和 A: mysql> SELECT col_name FROM tbl_name AS a -> WHERE a.col_name = 1 OR A.col_name = 2; 然 而, 该 查 询 在 Windows 中 是 可 以 的 要 想 避 免 出 现 差 别, 最 好 采 用 一 致 的 转 换, 例 如 总 是 用 小 写 创 建 并 引 用 数 据 库 名 和 表 名 在 大 多 数 移 植 和 使 用 中 建 议 使 用 该 转 换 网 易 技 术 部 40

41 在 MySQL 中 如 何 在 硬 盘 上 保 存 和 使 用 表 名 和 数 据 库 名 由 lower_case_tables_name 系 统 变 量 确 定, 可 以 在 启 动 mysqld 时 设 置 lower_case_tables_name 可 以 采 用 下 面 的 任 一 值 : 值 含 义 使 用 CREATE TABLE 或 CREATE DATABASE 语 句 指 定 的 大 写 和 小 写 在 硬 盘 上 保 存 表 名 和 数 据 0 库 名 名 称 比 较 对 大 小 写 敏 感 在 Unix 系 统 中 的 默 认 设 置 即 如 此 请 注 意 如 果 在 大 小 写 不 敏 感 的 文 件 系 统 上 用 --lower-case-table-names=0 强 制 设 为 0, 并 且 使 用 不 同 的 大 小 写 访 问 MyISAM 表 名, 会 导 致 索 引 破 坏 表 名 在 硬 盘 上 以 小 写 保 存, 名 称 比 较 对 大 小 写 敏 感 MySQL 将 所 有 表 名 转 换 为 小 写 以 便 存 1 储 和 查 找 该 行 为 也 适 合 数 据 库 名 和 表 的 别 名 该 值 为 Windows 和 Mac OS X 系 统 中 的 默 认 值 表 名 和 数 据 库 名 在 硬 盘 上 使 用 CREATE TABLE 或 CREATE DATABASE 语 句 指 定 的 大 小 写 进 行 2 保 存, 但 MySQL 将 它 们 转 换 为 小 写 以 便 查 找 名 称 比 较 对 大 小 写 敏 感 注 释 : 只 在 对 大 小 写 不 敏 感 的 文 件 系 统 上 适 用! InnoDB 表 名 以 小 写 保 存, 例 如 lower_case_tables_name=1 在 Windows 和 Mac OS X 中,lower_case_tables_name 的 默 认 值 是 1 如 果 只 在 一 个 平 台 上 使 用 MySQL, 通 常 不 需 要 更 改 lower_case_tables_name 变 量 然 而, 如 果 你 想 要 在 对 大 小 写 敏 感 不 同 的 文 件 系 统 的 平 台 之 间 转 移 表, 会 遇 到 困 难 例 如, 在 Unix 中,my_tables 和 MY_tables 是 两 个 不 同 的 表, 但 在 Windows 中, 这 两 个 表 名 相 同 要 想 避 免 由 于 数 据 库 或 表 名 的 大 小 写 造 成 的 数 据 转 移 问 题, 可 使 用 两 个 选 项 : 在 任 何 系 统 中 可 以 使 用 lower_case_tables_name=1 使 用 该 选 项 的 不 利 之 处 是 当 使 用 SHOW TABLES 或 SHOW DATABASES 时, 看 不 出 名 字 原 来 是 用 大 写 还 是 小 写 在 Unix 中 使 用 lower_case_tables_name=0, 在 Windows 中 使 用 lower_case_tables_name=2 这 样 了 可 以 保 留 数 据 库 名 和 表 名 的 大 小 写 不 利 之 处 是 必 须 确 保 在 Windows 中 查 询 总 是 用 正 确 大 小 写 引 用 数 据 库 名 和 表 名 如 果 将 查 询 转 移 到 Unix 中, 由 于 在 Unix 中 大 小 写 很 重 要, 如 果 大 小 写 不 正 确, 它 们 不 工 作 网 易 技 术 部 41

42 例 外 : 如 果 你 正 使 用 InnoDB 表, 在 任 何 平 台 上 均 应 将 lower_case_tables_name 设 置 为 1, 以 强 制 将 名 转 换 为 小 写 请 注 意 在 Unix 中 将 lower_case_tables_name 设 置 为 1 之 前, 重 启 mysqld 之 前, 必 须 先 将 旧 的 数 据 库 名 和 表 名 转 换 为 小 写 10.2 使 用 外 键 需 注 意 的 地 方 在 MySQL 中,InnoDB 表 支 持 对 外 部 关 键 字 约 束 条 件 的 检 查 对 于 除 InnoDB 类 型 的 表, 当 使 用 REFERENCES tbl_name(col_name) 子 句 定 义 列 时 可 以 使 用 外 部 关 键 字, 该 子 句 没 有 实 际 的 效 果, 只 作 为 备 忘 录 或 注 释 来 提 醒, 你 目 前 正 定 义 的 列 指 向 另 一 个 表 中 的 一 个 列 执 行 该 语 句 时, 实 现 下 面 很 重 要 : MySQL 不 执 行 表 tbl_name 中 的 动 作, 例 如 作 为 你 正 定 义 的 表 中 的 行 的 动 作 的 响 应 而 删 除 行 ; 换 句 话 说, 该 句 法 不 会 致 使 ON DELETE 或 ON UPDATE 行 为 ( 如 果 你 在 REFERENCES 子 句 中 写 入 ON DELETE 或 ON UPDATE 子 句, 将 被 忽 略 ) 该 句 法 可 以 创 建 一 个 column; 但 不 创 建 任 何 索 引 或 关 键 字 如 果 用 该 句 法 定 义 InnoDB 表, 将 会 导 致 错 误 你 可 以 使 用 作 为 联 接 列 创 建 的 列, 如 下 所 示 : CREATE TABLE person ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(60) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE shirt ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, style ENUM('t-shirt', 'polo', 'dress') NOT NULL, 网 易 技 术 部 42

43 color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL, owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id), PRIMARY KEY (id) ); INSERT INTO person VALUES (NULL, 'Antonio Paz'); := LAST_INSERT_ID(); INSERT INTO shirt VALUES (NULL, 'polo', (NULL, 'dress', (NULL, 't-shirt', INSERT INTO person VALUES (NULL, 'Lilliana Angelovska'); := LAST_INSERT_ID(); INSERT INTO shirt VALUES (NULL, 'dress', (NULL, 'polo', (NULL, 'dress', (NULL, 't-shirt', SELECT * FROM person; id name Antonio Paz 2 Lilliana Angelovska 网 易 技 术 部 43

44 SELECT * FROM shirt; id style color owner polo blue 1 2 dress white 1 3 t-shirt blue 1 4 dress orange 2 5 polo red 2 6 dress blue 2 7 t-shirt white SELECT s.* FROM person p, shirt s WHERE p.name LIKE 'Lilliana%' AND s.owner = p.id AND s.color <> 'white'; id style color owner dress orange 2 5 polo red 2 6 dress blue 输 出 中 : 按 照 这 种 方 式 使 用,REFERENCES 子 句 不 会 显 示 在 SHOW CREATE TABLE 或 DESCRIBE 的 网 易 技 术 部 44

45 SHOW CREATE TABLE shirt\g *************************** 1. row *************************** Table: shirt Create Table: CREATE TABLE `shirt` ( `id` smallint(5) unsigned NOT NULL auto_increment, `style` enum('t-shirt','polo','dress') NOT NULL, `color` enum('red','blue','orange','white','black') NOT NULL, `owner` smallint(5) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 在 列 定 义 中, 按 这 种 方 式 使 用 REFERENCES 作 为 注 释 或 提 示 适 用 于 表 MyISAM 和 BerkeleyDB 网 易 技 术 部 45

46 第 二 篇 优 化 篇 第 11 章 SQL 优 化 11.1 优 化 SQL 的 一 般 步 骤 通 过 show status 和 应 用 特 点 了 解 各 种 SQL 的 执 行 频 率 通 过 SHOW STATUS 可 以 提 供 服 务 器 状 态 信 息, 也 可 以 使 用 mysqladmin extendedstatus 命 令 获 得 SHOW STATUS 可 以 根 据 需 要 显 示 session 级 别 的 统 计 结 果 和 global 级 别 的 统 计 结 果 以 下 几 个 参 数 对 Myisam 和 Innodb 存 储 引 擎 都 计 数 : 1. Com_select 执 行 select 操 作 的 次 数, 一 次 查 询 只 累 加 1; 2. Com_insert 执 行 insert 操 作 的 次 数, 对 于 批 量 插 入 的 insert 操 作, 只 累 加 一 次 ; 3. Com_update 执 行 update 操 作 的 次 数 ; 4. Com_delete 执 行 delete 操 作 的 次 数 ; 以 下 几 个 参 数 是 针 对 Innodb 存 储 引 擎 计 数 的, 累 加 的 算 法 也 略 有 不 同 : 1. Innodb_rows_read select 查 询 返 回 的 行 数 ; 2. Innodb_rows_inserted 执 行 Insert 操 作 插 入 的 行 数 ; 3. Innodb_rows_updated 执 行 update 操 作 更 新 的 行 数 ; 4. Innodb_rows_deleted 执 行 delete 操 作 删 除 的 行 数 ; 通 过 以 上 几 个 参 数, 可 以 很 容 易 的 了 解 当 前 数 据 库 的 应 用 是 以 插 入 更 新 为 主 还 是 以 查 询 操 作 为 主, 以 及 各 种 类 型 的 SQL 大 致 的 执 行 比 例 是 多 少 对 于 更 新 操 作 的 计 数, 是 对 执 行 次 数 的 计 数, 不 论 提 交 还 是 回 滚 都 会 累 加 对 于 事 务 型 的 应 用, 通 过 Com_commit 和 Com_rollback 可 以 了 解 事 务 提 交 和 回 滚 的 情 况, 对 于 回 滚 操 作 非 常 频 繁 的 数 据 库, 可 能 意 味 着 应 用 编 写 存 在 问 题 此 外, 以 下 几 个 参 数 便 于 我 们 了 解 数 据 库 的 基 本 情 况 : 网 易 技 术 部 46

47 1. Connections 试 图 连 接 Mysql 服 务 器 的 次 数 2. Uptime 服 务 器 工 作 时 间 3. Slow_queries 慢 查 询 的 次 数 定 位 执 行 效 率 较 低 的 SQL 语 句 : 可 以 通 过 以 下 两 种 方 式 定 位 执 行 效 率 较 低 的 SQL 语 句 : 1. 可 以 通 过 慢 查 询 日 志 定 位 那 些 执 行 效 率 较 低 的 sql 语 句, 用 --log-slowqueries[=file_name] 选 项 启 动 时,mysqld 写 一 个 包 含 所 有 执 行 时 间 超 过 long_query_time 秒 的 SQL 语 句 的 日 志 文 件 可 以 链 接 到 管 理 维 护 中 的 相 关 章 节 2. 慢 查 询 日 志 在 查 询 结 束 以 后 才 纪 录, 所 以 在 应 用 反 映 执 行 效 率 出 现 问 题 的 时 候 查 询 慢 查 询 日 志 并 不 能 定 位 问 题, 可 以 使 用 show processlist 命 令 查 看 当 前 MySQL 在 进 行 的 线 程, 包 括 线 程 的 状 态, 是 否 锁 表 等 等, 可 以 实 时 的 查 看 SQL 执 行 情 况, 同 时 对 一 些 锁 表 操 作 进 行 优 化 通 过 EXPLAIN 分 析 低 效 SQL 的 执 行 计 划 : 通 过 以 上 步 骤 查 询 到 效 率 低 的 SQL 后, 我 们 可 以 通 过 explain 或 者 desc 获 取 MySQL 如 何 执 行 SELECT 语 句 的 信 息, 包 括 select 语 句 执 行 过 程 表 如 何 连 接 和 连 接 的 次 序 explain 可 以 知 道 什 么 时 候 必 须 为 表 加 入 索 引 以 得 到 一 个 使 用 索 引 来 寻 找 记 录 的 更 快 的 SELECT mysql> explain select sum(moneys) from sales a,companys b where a.company_id = b.id and a.year = 2006; select_type table type possible_keys key key_len rows Extra 网 易 技 术 部 47

48 SIMPLE b index PRIMARY PRIMARY 4 1 Using index SIMPLE a ALL NULL NULL NULL 12 Using where rows in set (0.02 sec) select_type: table: type: select 类 型 输 出 结 果 集 的 表 表 示 表 的 连 接 类 型 当 表 中 仅 有 一 行 是 type 的 值 为 system 是 最 佳 的 连 接 类 型 ; 当 select 操 作 中 使 用 索 引 进 行 表 连 接 时 type 的 值 为 ref; 当 select 的 表 连 接 没 有 使 用 索 引 时, 经 常 会 看 到 type 的 值 为 ALL, 表 示 对 该 表 进 行 了 全 表 扫 描, 这 时 需 要 考 虑 通 过 创 建 索 引 来 提 高 表 连 接 的 效 率 possible_keys: 表 示 查 询 时, 可 以 使 用 的 索 引 列. key: key_len: rows: Extra: 表 示 使 用 的 索 引 索 引 长 度 扫 描 范 围 执 行 情 况 的 说 明 和 描 述 确 定 问 题, 并 采 取 相 应 的 优 化 措 施 : 经 过 以 上 步 骤, 基 本 可 以 确 认 问 题 出 现 的 原 因, 可 以 根 据 情 况 采 取 相 应 的 措 施, 进 行 优 化 提 高 执 行 的 效 率 例 如 上 面 的 例 子, 我 们 确 认 是 对 a 表 的 全 表 扫 描 导 致 效 率 的 不 理 想, 我 们 对 a 表 的 year 字 段 创 建 了 索 引, 查 询 需 要 扫 描 的 行 数 明 显 较 少 mysql> explain select sum(moneys) from sales a,companys b where a.company_id = b.id and a.year = 2006; 网 易 技 术 部 48

49 select_type table type possible_keys key key_len rows Extra SIMPLE b index PRIMARY PRIMARY 4 1 Using index SIMPLE a ref year year 4 3 Using where rows in set (0.02 sec) 11.2 索 引 问 题 索 引 的 存 储 分 类 myisam 表 的 数 据 文 件 和 索 引 文 件 是 自 动 分 开 的 ;innodb 的 数 据 和 索 引 是 存 储 在 同 一 个 表 空 间 里 面, 但 可 以 有 多 个 文 件 组 成 创 建 索 引 语 法 如 下 : CREATE [UNIQUE FULLTEXT SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) index_col_name: col_name [(length)] [ASC DESC] 索 引 的 存 储 类 型 目 前 只 有 两 种 (btree 和 hash), 具 体 和 表 的 模 式 相 关 : myisam innodb btree btree memory/heap hash,btree 网 易 技 术 部 49

50 mysql 目 前 不 支 持 函 数 索 引, 只 能 对 列 的 前 一 部 分 (length) 进 行 索 引, 例 : create index ind_test on table1(name(5)), 对 于 char 和 varchar 列, 使 用 前 缀 索 引 将 大 大 节 省 空 间 MySQL 如 何 使 用 索 引 索 引 用 于 快 速 找 出 在 某 个 列 中 有 一 特 定 值 的 行 对 相 关 列 使 用 索 引 是 提 高 SELECT 操 作 性 能 的 最 佳 途 径 查 询 要 使 用 索 引 最 主 要 的 条 件 是 查 询 条 件 中 需 要 使 用 索 引 关 键 字, 如 果 是 多 列 索 引, 那 么 只 有 查 询 条 件 使 用 了 多 列 关 键 字 最 左 边 的 前 缀 时, 才 可 以 使 用 索 引, 否 则 将 不 能 使 用 索 引 下 列 情 况 下,Mysql 不 会 使 用 已 有 的 索 引 : 1. 如 果 mysql 估 计 使 用 索 引 比 全 表 扫 描 更 慢, 则 不 使 用 索 引 例 如 : 如 果 key_part1 均 匀 分 布 在 1 和 100 之 间, 下 列 查 询 中 使 用 索 引 就 不 是 很 好 : SELECT * FROM table_name where key_part1 > 1 and key_part1 < 如 果 使 用 heap 表 并 且 where 条 件 中 不 用 = 索 引 列, 其 他 > < >= <= 均 不 使 用 索 引 ; 3. 如 果 不 是 索 引 列 的 第 一 部 分 ; 4. 如 果 like 是 以 % 开 始 ; 5. 对 where 后 边 条 件 为 字 符 串 的 一 定 要 加 引 号, 字 符 串 如 果 为 数 字 mysql 会 自 动 转 为 字 符 串, 但 是 不 使 用 索 引 查 看 索 引 使 用 情 况 如 果 索 引 正 在 工 作,Handler_read_key 的 值 将 很 高, 这 个 值 代 表 了 一 个 行 被 索 引 值 读 的 次 数, 很 低 的 值 表 明 增 加 索 引 得 到 的 性 能 改 善 不 高, 因 为 索 引 并 不 经 常 使 用 Handler_read_rnd_next 的 值 高 则 意 味 着 查 询 运 行 低 效, 并 且 应 该 建 立 索 引 补 救 这 个 值 的 含 义 是 在 数 据 文 件 中 读 下 一 行 的 请 求 数 如 果 你 正 进 行 大 量 的 表 扫 描, 该 值 较 高 通 常 说 明 表 索 引 不 正 确 或 写 入 的 查 询 没 有 利 用 索 引 网 易 技 术 部 50