云平台开发技术人员MySQL数据库知识参考手册.doc

此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 云平台开发人员云平台开发人员 MySQL 数据库知识数据库知识 参考手册参考手册 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 内容简介内容简介 本文档组织结构 本文档从实践角度出发 结合部分实践示例 从基础的帮助信息查看 到存储引擎的选型及的 优化 系统的从开发人员角度阐述了 MySQL 数据库的各个层面知识 本文档分由 11 个章节 组成 包含两个附录 主要分为三篇 上篇 第 1 2 章 基础篇 基础篇的目的是帮助读者快速的了解 MySQL 使用及相关的命令 以及 MySQL 数据库中 的基础数据类型 为后续 schema 设计 性能调优奠定基础 中篇 第 3 9 章 进阶篇 进阶篇中从开发人员实践角度上介绍了 MySQL 字符集 存储引擎 schema 设计 SQL 优化 应用优化 安全防范等知识 其中第九章从部分源码层面介绍了 MySQL 实例的启动 配置加载 连接处理及 SQL 语 句的执行 起到一个抛砖引玉的作用 希望读者能够更好地从源码的层面上去了解 MySQL 的 工作原理 下篇 附录一 附录二 手册篇 手册篇中主要列举了 MySQL 的内置函数 错误码说明 供开发人员在实际应用中进行翻 阅查询 本文档面向的读者 本文档不但适合 MySQL 数据库相关开发人员阅读 同样也适合数据库管理员 DBA 进行参 考学习 不管是数据库新手还是数据库专家 相信都能在本文档中有所收获 1 应用软件 MySQL 数据库开发人员 2 MySQL 数据库管理员 MySQL DBA 3 软件架构师 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 编写 修订 审核记录编写 修订 审核记录 编号编号文档版本文档版本修订章节修订章节修订原因修订原因修订日期修订日期修订人修订人 1 11 0 全文初稿 2016 01 22 罗雷 2 22 0 全文文字修订及案 例实践 2016 02 26 陈强 3 33 0 全文文字修订 增 加部分章节 2016 03 03 罗雷 4 44 0 内容简介增加内容简介 2016 03 15 罗雷 5 5 6 6 7 7 8 8 9 9 1010 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 目录目录 第一章第一章 学会使用帮助学会使用帮助 5 第一节 HELP命令 5 第二节 服务端帮助命令 6 第三节 快速查阅帮助 8 第二章第二章 数据基本类型数据基本类型 11 第一节数值类型 11 1 BIT M 11 2 TINYINT M UNSIGNED ZEROFILL 11 3 BOOL BOOLEAN 11 4 SMALLINT M UNSIGNED ZEROFILL 11 5 MEDIUMINT M UNSIGNED ZEROFILL 11 6 INT M UNSIGNED ZEROFILL 11 7 INTEGER M UNSIGNED ZEROFILL 11 8 BIGINT M UNSIGNED ZEROFILL 11 9 FLOAT M D UNSIGNED ZEROFILL 12 10 DOUBLE M D UNSIGNED ZEROFILL 12 11 DOUBLE PRECISION M D UNSIGNED ZEROFILL REAL M D UNSIGNED ZEROFILL 13 第二节日期和时间类型 14 1 DATE 14 2 DATETIME 14 3 TIMESTAMP M 14 4 YEAR 2 4 14 第三节字符串类型 15 1 BINARY M 15 2 CHAR M 15 3 VARBINARY M 15 4 VARCHAR M 15 5 TINYBLOB 15 6 TINYTEXT 15 7 BLOB M 15 8 TEXT M 16 9 MEDIUMBLOB 16 10 MEDIUMTEXT 16 11 LONGBLOB 16 12 LONGTEXT 16 13 ENUM value1 value2 16 14 SET value1 value2 16 第四节 注释 17 第三章第三章 字符集字符集 18 第一节字符集概述 18 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第二节MYSQL 支持的字符集 18 第三节UNICODE 简述 18 第四节怎样选择合适的字符集 19 第五节MYSQL 字符集的设置 19 第六节关于中文乱码 19 第四章第四章 存储引擎存储引擎 21 第一节MYSQL 存储引擎概述 21 第二节各种存储引擎的特性 21 第三节如何选择合适的存储引擎 22 第四节一般性建议 22 第五章第五章 SCHEMA 设计设计 23 第一节 高效的模型设计 23 第二节 适度冗余 23 第三节 大字段垂直分拆 25 第四节 大表水平分拆 27 第五节 分区表 27 第六节 统计表 28 第七节 合适的数据类型 28 第八节 创建合适的索引 32 第九节 规范的对象命名 39 第六章第六章 SQL 优化优化 41 第一节 MYSQL 的 QUERY OPTIMIZER 41 第二节 优化基本思路和原则 42 第三节 JOIN的实现原理及优化思路 46 第四节 ORDER BY GROUP BY 和 DISTINCT 优化 47 第五节 利用 EXPLAIN和 PROFILING 50 第五节慢日志分析 53 第七章第七章 应用优化应用优化 56 第一节使用连接池 56 第二节 减少对 MYSQL 的访问 56 第三节 加CACHE层 56 第四节 使用负载均衡 56 第八章第八章 SQL 中的安全问题中的安全问题 58 第一节 SQL 注入简述 58 第二节 开发中可以采取的措施 58 第三节 自己定义函数进行校验 59 第九章第九章 MYSQL 源码分析源码分析 60 第一节MYSQL启动之配置文件加载 60 第二节MYSQL 启动之权限加载 61 第三节MYSQL 启动之连接创建 63 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第四节一条 SELECT 语句的执行 66 第五节引擎数据查询接口 72 附录一 函数列表附录一 函数列表 75 第一节字符串函数 75 第二节数学函数 80 第三节日期和时间函数 82 第四节全文搜索功能函数 87 第五节加密函数 87 第六节信息函数 88 第七节其他函数 90 第八节聚合函数 91 附录二 错误码列表附录二 错误码列表 94 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第一章第一章 学会使用帮助学会使用帮助 第一节第一节 help 命令命令 mysql help For information about MySQL products and services visit For developer information including the MySQL Reference Manual visit To buy MySQL Enterprise support training or other products visit List of all MySQL commands Note that all text commands must be first on line and end with Synonym for help clear c Clear the current input statement connect r Reconnect to the server Optional arguments are db and host delimiter d Set statement delimiter edit e Edit command with EDITOR ego G Send command to mysql server display result vertically exit q Exit mysql Same as quit go g Send command to mysql server help h Display this help nopager n Disable pager print to stdout notee t Don t write into outfile pager P Set PAGER to pager Print the query results via PAGER print p Print current command prompt R Change your mysql prompt quit q Quit mysql rehash Rebuild completion hash source Execute an SQL script file Takes a file name as an argument status s Get status information from the server system Execute a system shell command tee T Set outfile to outfile Append everything into given outfile use u Use another database Takes database name as argument charset C Switch to another charset Might be needed for processing binlog with multi byte charsets warnings W Show warnings after every statement nowarning w Don t show warnings after every statement For server side help type help contents 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 示例 Connect 命令 mysql r Connection id 5830 Current database test Status 命令 mysql status mysql Ver 14 14 Distrib 5 5 25 for Linux x86 64 using readline 5 1 Connection id 5830 Current database test Current user root localhost SSL Not in use Current pager stdout Using outfile Using delimiter Server version 5 5 25 log MySQL Community Server GPL Protocol version 10 Connection Localhost via UNIX socket Server characterset utf8 Db characterset utf8 Client characterset utf8 Conn characterset utf8 UNIX socket var lib mysql mysql sock Uptime 1 day 21 hours 13 min 38 sec Threads 48 Questions 4927665 Slow queries 1298 Opens 352 Flush tables 1 Open tables 337 Queries per second avg 30 264 第二节第二节 服务端帮助命令服务端帮助命令 mysql contents You asked for help about help category Contents For more information type help where is one of the following categories Account Management Administration Compound Statements Data Definition 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Help Metadata Language Structure Plugins Table Maintenance Transactions User Defined Functions Utility 对于列出的分类 可以进行看自己感兴趣的部分 例如 mysql Data Manipulation You asked for help about help category Data Manipulation For more information type help where is one of the following topics CALL DELETE DO DUAL HANDLER INSERT INSERT DELAYED INSERT SELECT JOIN LOAD DATA LOAD XML REPLACE SELECT UNION UPDATE 对于列出的内容 可以进一步看详细情况 mysql insert Name INSERT Description Syntax INSERT LOW PRIORITY DELAYED HIGH PRIORITY IGNORE INTO tbl name col name VALUES VALUE expr DEFAULT ON DUPLICATE KEY UPDATE col name expr 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 col name expr Or INSERT LOW PRIORITY DELAYED HIGH PRIORITY IGNORE INTO tbl name SET col name expr DEFAULT ON DUPLICATE KEY UPDATE col name expr col name expr Or INSERT LOW PRIORITY HIGH PRIORITY IGNORE INTO tbl name col name SELECT ON DUPLICATE KEY UPDATE col name expr col name expr INSERT inserts new rows into an existing table The INSERT VALUES and INSERT SET forms of the statement insert rows based on explicitly specified values The INSERT SELECT form inserts rows selected from another table or tables INSERT SELECT is discussed further in HELP INSERT SELECT URL 第三节第三节 快速查阅帮助快速查阅帮助 实际工作中 如果我们需要快速查阅某项语法时 可以使用关键字直接进行快速查询 例如 我想知道 create 到底有哪些可用语法时 可使用如下命令 mysql create Many help items for your request exist To make a more specific request please type help where is one of the following topics CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE FUNCTION UDF CREATE INDEX 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 CREATE PROCEDURE CREATE SERVER CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW SHOW SHOW CREATE DATABASE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW CREATE TABLE SPATIAL 想详细了解 create table 语法时 可使用 mysql create table Name CREATE TABLE Description Syntax CREATE TEMPORARY TABLE IF NOT EXISTS tbl name create definition table options partition options Or CREATE TEMPORARY TABLE IF NOT EXISTS tbl name create definition table options partition options select statement Or CREATE TEMPORARY TABLE IF NOT EXISTS tbl name LIKE old tbl name LIKE old tbl name create definition col name column definition CONSTRAINT symbol PRIMARY KEY index type index col name index option INDEX KEY index name index type index col name index option 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 CONSTRAINT symbol UNIQUE INDEX KEY index name index type index col name index option FULLTEXT SPATIAL INDEX KEY index name index col name index option CONSTRAINT symbol FOREIGN KEY index name index col name reference definition CHECK expr 想查询 timestamp 取值范围时 可使用 mysql timestamp Name TIMESTAMP Description TIMESTAMP A timestamp The range is 1970 01 01 00 00 01 UTC to 2038 01 19 03 14 07 UTC TIMESTAMP values are stored as the number of seconds since the epoch 1970 01 01 00 00 00 UTC A TIMESTAMP cannot represent the value 1970 01 01 00 00 00 because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing 0000 00 00 00 00 00 the zero TIMESTAMP value Unless specified otherwise the first TIMESTAMP column in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value This makes TIMESTAMP useful for recording the timestamp of an INSERT or UPDATE operation You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value unless it has been defined with the NULL attribute to permit NULL values The automatic initialization and updating to the current date and time can be specified using DEFAULT and ON UPDATE clauses are described in Note The TIMESTAMP format that was used prior to MySQL 4 1 is not supported in MySQL 5 5 see MySQL 3 23 4 0 4 1 Reference Manual for information regarding the old format URL overview html 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第二章第二章 数据基本数据基本类型类型 第一节第一节 数值类型数值类型 1 BIT M 位字段类型 M 表示每个值的位数 范围为从 1 到 64 如果 M 被省略 默认为 1 2 TINYINT M UNSIGNED ZEROFILL 很小的整数 带符号的范围是 128 到 127 无符号的范围是 0 到 255 3 BOOL BOOLEAN 是 TINYINT 1 的同义词 zero 值被视为假 非 zero 值视为真 4 SMALLINT M UNSIGNED ZEROFILL 小的整数 带符号的范围是 32768 到 32767 无符号的范围是 0 到 65535 5 MEDIUMINT M UNSIGNED ZEROFILL 中等大小的整数 带符号的范围是 8388608 到 8388607 无符号的范围是 0 到 16777215 6 INT M UNSIGNED ZEROFILL 普通大小的整数 带符号的范围是 2147483648 到 2147483647 无符号的范围是 0 到 4294967295 7 INTEGER M UNSIGNED ZEROFILL 这是 INT 的同义词 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 8 BIGINT M UNSIGNED ZEROFILL 大整数 带符号的范围是 9223372036854775808 到 9223372036854775807 无符号 的范围是 0 到 18446744073709551615 使用带符号的 BIGINT 或 DOUBLE 值进行所有算法 因此除了位函数 不应使用大于 9223372036854775807 63 位 的无符号的大整数 如果这样做 结果中的最后几位可能出 错 这是由于将 BIGINT 值转换为 DOUBLE 进行四舍五入时造成的错误 MySQL 可以在以下情况下处理 BIGINT a 当使用整数在一个 BIGINT 列保存大的无符号的值时 b 在 MIN col name 或 MAX col name 中 其中 col name 指 BIGINT 列 c 使用操作符 等等 并且两个操作数均为整数时 d 总是可以使用一个字符串在 BIGINT 列中保存严格整数值 在这种情况下 MySQL 执行 字符串 数字转换 其间不存在双精度表示 e 当两个操作数均为整数值时 和 操作符使用 BIGINT 算法 这说明如果乘两个大整 数 或来自返回整数的函数 当结果大于 9223372036854775807 时 会得到意想不到 的结果 9 FLOAT M D UNSIGNED ZEROFILL 小 单精度 浮点数 允许的值是 3 402823466E 38 到 1 175494351E 38 0 和 1 175494351E 38 到 3 402823466E 38 这些是理论限制 基于 IEEE 标准 实际的范 围根据硬件或操作系统的不同可能稍微小些 M 是小数纵位数 D 是小数点后面的位数 如果 M 和 D 被省略 根据硬件允许的限制来保存 值 单精度浮点数精确到大约 7 位小数位 如果指定 UNSIGNED 不允许负值 使用浮点数可能会遇到意想不到的问题 因为在 MySQL 中的所有计算用双精度完成 10 DOUBLE M D UNSIGNED ZEROFILL 普通大小 双精度 浮点数 允许的值是 1 7976931348623157E 308 到 2 2250738585072014E 308 0 和 2 2250738585072014E 308 到 1 7976931348623157E 308 这些是理论限制 基于 IEEE 标准 实际的范围根据硬件或 操作系统的不同可能稍微小些 M 是小数总位数 D 是小数点后面的位数 如果 M 和 D 被省略 根据硬件允许的限制来保存 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 值 双精度浮点数精确到大约 15 位小数位 如果指定 UNSIGNED 不允许负值 11 DOUBLE PRECISION M D UNSIGNED ZEROFILL REAL M D UNSIGNED ZEROFILL 为 DOUBLE 的同义词 除了 如果 SQL 服务器模式包括 REAL AS FLOAT 选项 REAL 是 FLOAT 的同义词而不是 DOUBLE 的同义词 12 FLOAT p UNSIGNED ZEROFILL 浮点数 p 表示精度 以位数表示 但 MySQL 只使用该值来确定是否结果列的数据类型为 FLOAT 或 DOUBLE 如果 p 为从 0 到 24 数据类型变为没有 M 或 D 值的 FLOAT 如果 p 为从 25 到 53 数据类型变为没有 M 或 D 值的 DOUBLE 结果列范围与本节前面描述的单精 度 FLOAT 或双精度 DOUBLE 数据类型相同 13 DECIMAL M D UNSIGNED ZEROFILL 压缩的 严格 定点数 M 是小数位数 精度 的总数 D 是小数点 标度 后面的位数 小数点和 负数 的 符号不包括在 M 中 如果 D 是 0 则值没有小数点或分数部分 DECIMAL 整数最 大位数 M 为 65 支持的十进制数的最大位数 D 是 30 如果 D 被省略 默认是 0 如果 M 被省略 默认是 10 如果指定 UNSIGNED 不允许负值 所有 DECIMAL 列的基本计算 用 65 位精度完成 类型类型字节字节最小值最小值最大值最大值 带符号的 无符号的 带符号的 无符号的 TINYINT1 128127 0255 SMALLINT2 3276832767 065535 MEDIUMINT3 83886088388607 016777215 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 INT4 21474836482147483647 04294967295 BIGINT8 9 22337E 189 22337E 18 01 84467E 19 第二节第二节 日期和时间类型日期和时间类型 1 DATE 日期 支持的范围为 1000 01 01 到 9999 12 31 MySQL 以 YYYY MM DD 格式显示 DATE 值 但允许使用字符串或数字为 DATE 列分配值 2 DATETIME 日期和时间的组合 支持的范围是 1000 01 01 00 00 00 到 9999 12 31 23 59 59 MySQL 以 YYYY MM DD HH MM SS 格式显示 DATETIME 值 但允许使用字 符串或数字为 DATETIME 列分配值 3 TIMESTAMP M 时间戳 范围是 1970 01 01 00 00 00 到 2037 年 TIMESTAMP 列用于 INSERT 或 UPDATE 操作时记录日期和时间 如果你不分配一个值 表 中的第一个 TIMESTAMP 列自动设置为最近操作的日期和时间 也可以通过分配一个 NULL 值 将 TIMESTAMP 列设置为当前的日期和时间 TIMESTAMP 值返回后显示为 YYYY MM DD HH MM SS 格式的字符串 显示宽度固定为 19 个字符 如果想要获得数字值 应在 TIMESTAMP 列添加 0 4 YEAR 2 4 两位或四位格式的年 默认是四位格式 在四位格式中 允许的值是 1901 到 2155 和 0000 在两位格式中 允许的值是 70 到 69 表示从 1970 年到 2069 年 MySQL 以 YYYY 格式显示 YEAR 值 但允许使用字符串或数字为 YEAR 列分配值 列类型列类型 零零 值值 DATETIME 0000 00 00 00 00 00 DATE 0000 00 00 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 TIMESTAMP0 TIME 00 00 00 YEAR0 第三节第三节 字符串类型字符串类型 1 BINARY M BINARY 类型类似于 CHAR 类型 但保存二进制字节字符串而不是非二进制字符串 2 CHAR M 定长字符串 M 表示最大列长度 M 的范围是 0 到 255 3 VARBINARY M VARBINARY 类型类似于 VARCHAR 类型 但保存二进制字节字符串而不是非二进制字符串 4 VARCHAR M 长字符串 M 表示最大列长度 M 的范围是 0 到 65 535 5 TINYBLOB 最大长度为 255 28 1 字节的 BLOB 列 6 TINYTEXT 最大长度为 255 28 1 字符的 TEXT 列 7 BLOB M 最大长度为 65 535 216 1 字节的 BLOB 列 可以给出该类型的可选长度 M 如果给出 则 MySQL 将列创建为最小的但足以容纳 M 字节长 的值的 BLOB 类型 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 8 TEXT M 最大长度为 65 535 216 1 字符的 TEXT 列 可以给出可选长度 M 则 MySQL 将列创建为最小的但足以容纳 M 字符长的值的 TEXT 类型 9 MEDIUMBLOB 最大长度为 16 777 215 224 1 字节的 BLOB 列 10 MEDIUMTEXT 最大长度为 16 777 215 224 1 字符的 TEXT 列 11 LONGBLOB 最大长度为 4 294 967 295 或 4GB 232 1 字节的 BLOB 列 LONGBLOB 列的最大有效 允许的 长度取决于客户端 服务器协议中配置最大包大小和可用的内存 12 LONGTEXT 最大长度为 4 294 967 295 或 4GB 232 1 字符的 TEXT 列 LONGTEXT 列的最大有效 允许的 长度取决于客户端 服务器协议中配置最大包大小和可用的内存 13 ENUM value1 value2 枚举类型 只能有一个值的字符串 从值列 value1 value2 NULL 中或特殊 错误 值中选出 ENUM 列最多可以有 65 535 个截然不同的值 ENUM 值在内部用整数表示 14 SET value1 value2 一个设置 字符串对象可以有零个或多个值 每个值必须来自列值 value1 value2 SET 列最多可以有 64 个成员 SET 值在内部用整数表示 列类型列类型存储需求存储需求 CHAR M M 个字节 0 M 255 VARCHAR M L 1 个字节 其中 L M 且 0 M 65535 参见下面的 注释 BINARY M M 个字节 0 M 255 VARBINARY M L 1 个字节 其中 L M 且 0 M 255 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 TINYBLOB TINYTEXTL 1 个字节 其中 L 28 BLOB TEXTL 2 个字节 其中 L 216 MEDIUMBLOB MEDIUMTEXTL 3 个字节 其中 L 224 LONGBLOB LONGTEXTL 4 个字节 其中 L Client 连接 DB 表 四者一致 从上图可知 只需保证原始串 返回结果 Client 连接三者编码格式一致即可 MySQL 支持多字符集编码 Server 内部会进行编码转换 需要注意字符集范围问题 设置方式 在连接上执行 set names 命令即可 如 Set names utf8 效果等同于执行下面三个语句 Set character set client utf8 Set character set connection utf8 Set character set results utf8 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第四章第四章 存储引擎存储引擎 第一节第一节 MySQL 存储引擎概述存储引擎概述 mysql 支持多种存储引擎 在处理不同类型的应用时 可以通过选择使用不同的存储引 擎提高应用的效率 或者提供灵活的存储 mysql 的存储引擎包括 MyISAM InnoDB BDB MEMORY MERGE EXAMPLE NDB Cluster ARCHIVE CSV BLACKHOLE FEDERATED 等 其中 InnoDB 和 BDB 提供事 务安全表 其他存储引擎都是非事务安全表 第二节第二节 各种存储引擎的特性各种存储引擎的特性 特点特点MyisamBDBMemoryInnoDBArchive 存储限制没有没有有64TB没有 事务安全支持支持 锁机制表锁页锁表锁行锁行锁 B 树索引支持支持支持支持 哈希索引支持支持 全文索引支持 集群索引支持 数据缓存支持支持 索引缓存支持支持支持 数据可压 缩 支持支持 空间使用低低N A高非常低 内存使用低低中等高低 批量插入 的速度 高高高低非常高 支持外键支持 最常使用的 2 种存储引擎 Myisam 是 Mysql5 5 版本之前的默认存储引擎 当 create 创建新表时 未指定新表的存 储引擎时 默认使用 Myisam 每个 MyISAM 在磁盘上存储成三个文件 文件名都和表名 相同 扩展名分别是 frm 存储表定义 MYD MYData 存储数据 MYI MYIndex 存储索引 数据文件和索引文件可以放置在不同的目录 平均分布 io 获得 更快的速度 InnoDB 存储引擎提供了具有提交 回滚和崩溃恢复能力的事务安全 但是对比 Myisam 的存储引擎 InnoDB 写的处理效率差一些 聚簇结构非 MyISAM 堆结构 并且会占用更 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 多的磁盘空间以保留数据和索引 但 Innodb 的数据页可缓存在 Innodb buffer pool 中 内存足够时 可极大的降低了磁盘 IO 压力 提供了较好的读性能 第三节第三节 如何选择合适的存储引擎如何选择合适的存储引擎 选择标准 根据应用特点选择合适的存储引擎 对于复杂的应用系统可以根据实际情况选择 多种存储引擎进行组合 下面是常用存储引擎的适用环境 a MyISAM 5 5 版本前默认存储引擎 它是在 Web 数据仓储和其他应用环境下最常使 用的存储引擎之一 b InnoDB 5 5 及 5 5 后版本默认存储引擎 用于事务处理应用程序 具有众多特性 包括 ACID 事务支持 c Memory 将所有数据保存在 RAM 中 在需要快速查找引用和其他类似数据的环境下 可提供极快的访问 但服务重启数据丢失 d Merge 允许 MySQL DBA 或开发人员将一系列等同的 MyISAM 表以逻辑方式组合在 一起 并作为 1 个对象引用它们 对于诸如数据仓储等 VLDB 环境十分适合 第四节第四节 一般性建议一般性建议 可通过 show engines 查看 mysql 支持的存储引擎 如下所示 目前来说 InnoDB 是为 MySQL 处理高并发 大数据量时的最大性能而设计的 它不仅可 以缓存索引 也可以缓存数据页 使得查询性能得到明显的提升 同时它的 CPU 效率也是 任何其它基于磁盘的关系数据库引擎所不能匹敌的 此外对事物及外键的支持 行级锁等特 性 在数据量大的网站或是应用中 Innodb 是倍受青睐的 另一方面 在数据库的复制操作中 Innodb 也是能保证 master 和 slave 数据一致有一定 的作用 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第五章第五章 Schema 设计设计 很多人都认为性能是在通过编写代码 程序代码或者是数据库代码 的过程中优化出来的 其实这是一个非常大的误区 真正影响性能最大的部分是在设计中就已经产生了的 后期的 优化很多时候所能够带来的改善都只是在解决前期设计所遗留下来的一些问题而已 而且能 够解决的问题通常也比较有限 本章将就如何在 MySQL 数据库 Schema 设计的时候保证 尽可能的高效 尽可能减少后期的烦恼 第一节第一节 高效的模型设计高效的模型设计 最规范的就一定是最合理的吗 在数据库 Schema 设计理论方面 一直有一个被大家奉为 葵花宝典 的规范化范式理论 通过范式理论所设计的数据库 Schema 逻辑清晰 关系明确 扩展方便 就连存储的数据量 也做到了尽可能的少 尤其是当范式级别较高的时候 几乎找不到任何的冗余数据 在很多人 眼里 数据库 Schema 满足的范式级别越高则该 Schema 设计的越优秀 但是 很多人忽略了一点 那就是产生该理论的时期和出发点 关系性数据库的规范化范式理 论诞生于上世纪七十年代初 最根本的目的是让数据库中尽量的去除数据的冗余 保持数据的 一致 使数据的修改简单 实际上 尽量去除数据的冗余不仅仅是为了让我们查询相同的数据量的时候能够多返回几条记 录 还有一个很重要的原因就是在当时的那个年代 数据的存储空间是及其昂贵的 而且存储 设备的容量也都非常的小 这一点在硬件存储设备发展如此迅速的如今 空间大小已经不再是 太大的问题了 而范式理论中的数据一致性和使数据修改简单保证主要是依靠添在数据库中添加各种约束来保 证 而各种约束对于数据库来说本身其实就是一个非常消耗资源的事情 所以 对于基于性能的数据库 Schema 设计 我们并不能完全以规范化范式理论来作为唯一 的指导 在设计过程中 应该从实际需求出发 以性能提升为根本目标来展开设计工作 很多 时候为了尽可能提高性能 我们必须做反范式设计 第二节第二节 适度冗余适度冗余 熟悉 MySQL 的优化器的读者可能清楚 MySQL 的优化器虽然号称使用了新一代的优化器技 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 术实现的非常优秀 但是由于目前 MySQL 所收集的数据统计信息还不是特别的多 所以起表 现并不是特别的让人满意 也并非如 MySQL 官方所宣传的那样智能 虽然处理普通 Join 的 时候一般都能比较智能的得到比较高效的执行计划 但是当遇到一些自查询或者较为复杂的 Join 的时候 很容易出现不太合理的执行计划 不少时候对各表的访问顺序选择的并不合适 造成复杂 Query 的整体执行效率低下 所以 为了让我们的 Query 执行计划尽可能的最优化 最直接有效的方式就是尽量减少 Join 而要减少 Join 我们就不可避免的需要通过表字段的冗余来实现 假设我们有如下业务 学生表 班级表 学生及班级关系表 分数表 我想查询二年级一班学生的总得分或平均分 需要至少 score list user class relation class 三张表的关联 join 才可得出结果 我们将 class id 冗余在分数表中 会是什么结果呢 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 这时 只需要 score list class 两张表进行关联即可 从数据库范式理论来看 这样的设计是不合理的 因为可能造成冗余的 class id 数据不一致 每次更新的时候 都需要更新两个或多张表的数据 为了尽可能让数据保证一致 应用程序中 需要处理更多的逻辑 但是 从性能角度来看的话 这种冗余是非常有价值的 虽然我们的数 据更新逻辑复杂了 但是我们在考虑更新带来的附加成本的时候 还应该考虑我们到底会有多 少更新发生在冗余数据上面呢 我们需要考虑的是一个系统的整体性能 而不是系统中单个行 为的性能 就像示例中的班级 id 虽然更新的成本增加了 但是查询的效率提高了 而且发生 示例中查询的频率要远大于更新的频率 通过少部分操作的成本投入换取更大的性能收获 实 际上是我们系统性能优化中经常使用的策略 在大部分应用系统中 类似于上面示例中的这种查询频繁但是更新较少的数据非常非常多 很 多时候如果我们一味的追求范式化理论的 Schema 设计在高性能要求的系统中是非常不合适 的 我个人认为 数据库的规范化理论其实质是在概念上的单一化 虽然规范后的数据库中的 表一般都较小 使表中相关列最少 这虽然可能在某些情况下增强了数据库的可维护性 但在 系统要完成一些数据的查询检索时 可能要用复杂的 Join 才能实现 这势必会造成查询检索 的性能低下 如果我们通过拆分 Join 通过多次简单的查询来在应用中实现 Join 逻辑 那所 带来的网络开销将会是非常巨大的 第三节第三节 大字段垂直分拆大字段垂直分拆 大字段垂直拆分策略相对于前面介绍的适度冗余策略在做法上可以说差不多是完全相反的做法 适度冗余策略是将别的表中的字段拿过来在自己身上也存一份数据 而大字段垂直拆分简单来 说就是将自己身上的字段拆分出去放在另外 单独 的表里面 可能很多读者朋友都会有疑惑了 我们刚刚才分析出了将别的字段拿过来放自己表里面为什么 现在又要将自己的字段分出去呢 这样不是有些自相矛盾了吗 其实并没有任何矛盾 前面我们将别人的字段那过来 是因为我们很多时候的查询需要使用该 字段 为了减少 Join 带来的性能消耗才拿过来的 而我们将大字段拿出去 也是将一些我们 在大部分查询中并不需要使用该字段的时候才会拿出去 而且 在我