本文摘录总结自《高性能 MySQL》(第三版), 将以每章一篇文章的方式带大家读这本数据库经典之作. 总结精华, 帮大家快速抓住重点信息, 节省宝贵时间.
这章概念性东西比较多, 可能有点枯燥. 但讲了很多底层原理, 坚持读下来还是会有一些收获的.
100 多位经验丰富的开发者参与, 在 GitHub 上获得了近 1000 个 star 的全栈全平台开源项目想了解下吗?
项目地址: https://github.com/cachecats/coderiver
一, 选择优化的数据类型
MySQL 支持的数据类型非常多, 选择正确的数据类型至关重要. 下面的几个简单原则有助于做出更好的选择.
更小的通常更好
一般情况下, 应该尽量使用可以正确存储数据的最小数据类型. 更小的数据类型通常更快, 因为它们占用更少的磁盘, 内存和 CPU 缓存, 并且处理时需要的 CPU 周期也更少.
但是要确保没有低估需要存储的值的范围, 因为在 schema 中的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作. 如果无法确定哪个数据类型是最好的, 就选择你认为不会超过范围的最小类型.
简单就好
简单数据类型的操作通常需要更少的 CPU 周期. 例如, 整型比字符操作代价更低, 因为字符集和校对规则 (排序规则) 使字符比较比整型比较更复杂. 这里有两个例子: 一个是应该使用 MySQL 内建的类型 (2) 而不是字符串来存储日期和时间, 另外一个是应该用整型存储 IP 地址. 稍后我们将专门讨论这个话题.
尽量避免 NULL
通常情况下最好指定列为 NOT NULL, 除非真的需要存储 NULL 值.
如果查询中包含可为 NULL 的列, 对 MySQL 来说更难优化, 因为可为 NULL 的列使得索引, 索引统计和值比较都更复杂. 可为 NULL 的列会使用更多的存储空间, 在 MySQL 里也需要特殊处理. 如果计划在列上建索引, 就应该尽量避免设计成可为 NULL 的列.
1.1 整数类型
有两种类型的数字: 整数 (whole number) 和实数(real number). 如果存储整数, 可以使用这几种整数类型: TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT. 分别使用 8,16,24,32,64 位存储空间.
整数类型有可选的 UNSIGNED 属性, 表示不允许负值, 这大致可以使正数的上限提高一倍. 例如 TINYINT UNSIGNED 可以存储的范围是 0~255, 而 TINYINT 的存储范围是−128~127.
有符号和无符号类型使用相同的存储空间, 并具有相同的性能, 因此可以根据实际情况选择合适的类型.
MySQL 可以为整数类型指定宽度, 例如 INT(11), 对大多数应用这是没有意义的: 它不会限制值的合法范围, 只是规定了 MySQL 的一些交互工具 (例如 MySQL 命令行客户端) 用来显示字符的个数. 对于存储和计算来说, INT(1) 和 INT(20)是相同的.
1.2 实数类型
实数是带有小数部分的数字. 然而, 它们不只是为了存储小数部分; 也可以使用 DECIMAL 存储比 BIGINT 还大的整数. MySQL 既支持精确类型, 也支持不精确类型.
FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算. DECIMAL 类型用于存储精确的小数.
CPU 不支持对 DECIMAL 的直接计算, 5.0 以及更高版本中, MySQL 服务器自身实现了 DECIMAL 的高精度计算. 相对而言, CPU 直接支持原生浮点计算, 所以浮点运算明显更快.
定义列的时候建议只指定数据类型, 不指定精度.
因为需要额外的空间和计算开销, 所以应该尽量只在对小数进行精确计算时才使用 DECIMAL-- 例如存储财务数据. 但在数据量比较大的时候, 可以考虑使用 BIGINT 代替 DECIMAL, 将需要存储的货币单位根据小数的位数乘以相应的倍数即可.
1.3 字符串类型
VARCHAR
VARCHAR 类型用于存储可变长字符串, 是最常见的字符串数据类型. 它比定长类型更节省空间, 因为它仅使用必要的空间.
VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度: 如果列的最大长度小于或等于 255 字节, 则只使用 1 个字节表示, 否则使用 2 个字节.
VARCHAR 节省了存储空间, 所以对性能也有帮助. 但是, 由于行是变长的, 在 UPDATE 时可能使行变得比原来更长, 这就导致需要做额外的工作.
下面这些情况下使用 VARCHAR 是合适的:
字符串列的最大长度比平均长度大很多;
列的更新很少, 所以碎片不是问题;
使用了像 UTF-8 这样复杂的字符集, 每个字符都使用不同的字节数进行存储.
最好的策略是只分配真正需要的空间, 不要太慷慨, 因为更长的列会消耗更多的内存.
CHAR
CHAR 类型是定长的: MySQL 总是根据定义的字符串长度分配足够的空间. 当存储 CHAR 值时, MySQL 会删除所有的末尾空格.
CHAR 适合存储很短的字符串, 或者所有值都接近同一个长度. 例如, CHAR 非常适合存储密码的 MD5 值, 因为这是一个定长的值.
对于经常变更的数据, CHAR 也比 VARCHAR 更好, 因为定长的 CHAR 类型不容易产生碎片. 对于非常短的列, CHAR 比 VARCHAR 在存储空间上更有效率, 因为 VARCHAR 还需要一个记录长度的额外字节.
BLOB 和 TEXT 类型
BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型, 分别采用二进制和字符方式存储.
实际上, 它们分别属于两组不同的数据类型家族: 字符类型是 TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT; 对应的二进制类型是 TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB . BLOB 是 SMALLBLOB 的同义词, TEXT 是 SMALLTEXT 的同义词.
MySQL 对 BLOB 和 TEXT 列进行排序与其他类型是不同的: 它只对每个列的最前 max_sort_length 字节而不是整个字符串做排序. 如果只需要排序前面一小部分字符, 则可以减小 max_sort_length 的配置, 或者使用 ORDER BY SUSTRING(column,length ) .
使用枚举 (ENUM) 代替字符串类型
有时候可以使用枚举列代替常用的字符串类型. 枚举列可以把一些不重复的字符串存储成一个预定义的集合. MySQL 在存储枚举时非常紧凑, 会根据列表值的数量压缩到一个或者两个字节中. MySQL 在内部会将每个值在列表中的位置保存为整数, 并且在表的. frm 文件中保存 "数字 - 字符串" 映射关系的 "查找表".
枚举最不好的地方是, 字符串列表是固定的, 添加或删除字符串必须使用 ALTER TABLE. 除非能接受只在列表末尾添加元素, 否则使用枚举不是个好主意.
1.4 日期和时间类型
MySQL 可以使用许多类型来保存日期和时间值, 例如 YEAR 和 DATE.MySQL 能存储的最小时间粒度为秒(MariaDB 支持微秒级别的时间类型). 但是 MySQL 也可以使用微秒级的粒度进行临时运算, 我们会展示怎么绕开这种存储限制.
MySQL 提供两种相似的日期类型, DATETIME 和 TIMESTAMP. 对于很多应用程序, 它们都能工作, 但是在某些场景, 一个比另一个工作得好.
DATETIME
这个类型能保存大范围的值, 从 1001 年到 9999 年, 精度为秒. 它把日期和时间封装到格式为 YYYYMMDDHHMMSS 的整数中, 与时区无关. 使用 8 个字节的存储空间. 默认情况下, MySQL 以一种可排序的, 无歧义的格式显示 DATETIME 值, 例如 "2008-01-16 22:37:08". 这是 ANSI 标准定义的日期和时间表示方法.
TIMESTAMP
就像它的名字一样, TIMETAMP 类型保存了从 1970 年 1 月 1 日午夜 (格林尼治标准时间) 以来的秒数, 它和 UNIX 时间戳相同. TIMESTAMP 只使用 4 个字节的存储空间, 因此它的范围比 DATETIME 小得多: 只能表示从 1970 年到 2038 年.
TIMESTAMP 显示的值也依赖于时区. MySQL 服务器, 操作系统, 以及客户端连接都有时区设置.
有必要强调一下这个区别: 如果在多个时区存储或访问数据, TIMESTAMP 和 DATETIME 的行为将很不一样. 前者提供的值与时区有关系, 后者则保留文本表示的日期和时间.
TIMESTAMP 的特殊属性:
在插入数据时如果没有指定值, 会自动填充为当前时间.
TIMESTAMP 默认为 NOT NULL.
通常应该尽量使用 TIMESTAMP, 因为它比 DATETIME 空间效率更高.
如果需要存储比秒更小粒度的日期和时间值, 可以使用 BIGINT 类型存储微秒级别的时间截, 或者使用 DOUBLE 存储秒之后的小数部分. 这两种方式都可以, 或者也可以使用 MariaDB 替代 MySQL.
1.5 选择标识符(identifier)
为标识列 (identifier column) 选择合适的数据类型非常重要.
标识列也可能在另外的表中作为外键使用, 所以为标识列选择数据类型时, 应该选择跟关联表中的对应列一样的类型. 混用不同类型可能导致性能问题, 即使没有性能影响, 在比较操作时隐式的类型转换也可能导致很难发现的错误.
在可以满足值的范围的需求, 并且预留未来增长空间的前提下, 应该选择最小的数据类型. 下面是一些小技巧:
整数类型
整数通常是标识列最好的选择, 因为它们很快并且可以使用 AUTO_INCREMENT .
ENUM 和 SET 类型
对于标识列来说 ENUM 和 SET 类型通常是比较糟糕的选择, 应尽量避免用这种类型.
字符串类型
字符串类型很消耗空间, 且通常比数字类型慢, 所以也应避免使用字符串作为标识列.
对于完全 "随机" 的字符串也需要多加注意, 例如 MD5(),SHA1()或者 UUID()产生的字符串. 这些函数生成的新值会任意分布在很大的空间内, 这会导致 INSERT 以及一些 SELECT 语句变得很慢.
如果存储 UUID 值, 则应该移除 "-" 符号; 或者更好的做法是, 用 UNHEX()函数转换 UUID 值为 16 字节的数字, 并且存储在一个 BINARY(16)列中. 检索时可以通过 HEX()函数来格式化为十六进制格式.
1.6 特殊数据类型
某些类型的数据并不直接与内置类型一致. 这里有两个例子:
低于秒级精度的时间戳
前面也介绍了, 建议使用 BIGINT 类型存储时间戳.
IPv4 地址
人们经常使用 VARCHAR(15)列来存储 IP 地址. 然而, 它们实际上是 32 位无符号整数, 不是字符串. 用小数点将地址分成四段的表示方法只是为了让人们阅读容易. 所以应该用无符号整数存储 IP 地址. MySQL 提供 INET_ATON() 和 INET_NTOA()函数在这两种表示方法之间转换.
二, MySQL schema 设计中的陷阱
虽然有一些普遍的好或坏的设计原则, 但也有一些问题是由 MySQL 的实现机制导致的, 这意味着有可能犯一些只在 MySQL 下发生的特定错误. 本节我们讨论设计 MySQL 的 schema 的问题. 这也许会帮助你避免这些错误, 并且选择在 MySQL 特定实现下工作得更好的替代方案.
太多的列
MySQL 的存储引擎 API 工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据, 然后在服务器层将缓冲内容解码成各个列. 从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的. MyISAM 的定长行结构实际上与服务器层的行结构正好匹配, 所以不需要转换. 然而, MyISAM 的变长行结构和 InnoDB 的行结构则总是需要转换. 转换的代价依赖于列的数量. 当我们研究一个 CPU 占用非常高的案例时, 发现客户使用了非常宽的表(数千个字段), 然而只有一小部分列会实际用到, 这时转换的代价就非常高. 如果计划使用数千个字段, 必须意识到服务器的性能运行特征会有一些不同.
太多的关联
所谓的 "实体 - 属性 - 值"(EAV)设计模式是一个常见的糟糕设计模式, 尤其是在 MySQL 下不能靠谱地工作. MySQL 限制了每个关联操作最多只能有 61 张表, 但是 EAV 数据库需要许多自关联. 我们见过不少 EAV 数据库最后超过了这个限制. 事实上在许多关联少于 61 张表的情况下, 解析和优化查询的代价也会成为 MySQL 的问题. 一个粗略的经验法则, 如果希望查询执行得快速且并发性好, 单个查询最好在 12 个表以内做关联.
全能的枚举
注意防止过度使用枚举(ENUM). 下面是我们见过的一个例子:
- CREATE TABLE ... (
- country enum('','0','1','2',...,'31')
这种模式的 schema 设计非常凌乱. 这么使用枚举值类型也许在任何支持枚举类型的数据库都是一个有问题的设计方案, 这里应该用整数作为外键关联到字典表或者查找表来查找具体值. 但是在 MySQL 中, 当需要在枚举列表中增加一个新的国家时就要做一次 ALTER TABLE 操作. 在 MySQL 5.0 以及更早的版本中 ALTER TABLE 是一种阻塞操作; 即使在 5.1 和更新版本中, 如果不是在列表的末尾增加值也会一样需要 ALTER TABLE.
变相的枚举
枚举 (ENUM) 列允许在列中存储一组定义值中的单个值, 集合 (SET) 列则允许在列中存储一组定义值中的一个或多个值. 有时候这可能比较容易导致混乱. 这是一个例子:
- CREATE TABLE ... (
- is_default set ('Y','N') NOT NULL default 'N'
如果这里真和假两种情况不会同时出现, 那么毫无疑问应该使用枚举列代替集合列.
非此发明 (Not Invent Here) 的 NULL
我们之前写了避免使用 NULL 的好处, 并且建议尽可能地考虑替代方案. 即使需要存储一个事实上的 "空值" 到表中时, 也不一定非得使用 NULL. 也许可以使用 0, 某个特殊值, 或者空字符串作为代替.
但是遵循这个原则也不要走极端. 当确实需要表示未知值时也不要害怕使用 NULL. 在一些场景中, 使用 NULL 可能会比某个神奇常数更好. 从特定类型的值域中选择一个不可能的值, 例如用−1 代表一个未知的整数, 可能导致代码复杂很多, 并容易引入 bug, 还可能会让事情变得一团糟. 处理 NULL 确实不容易, 但有时候会比它的替代方案更好.
三, 范式和反范式
对于任何给定的数据通常都有很多种表示方法, 从完全的范式化到完全的反范式化, 以及两者的折中. 在范式化的数据库中, 每个事实数据会出现并且只出现一次. 相反, 在反范式化的数据库中, 信息是冗余的, 可能会存储在多个地方.
3.1 范式的优点和缺点
当为性能问题而寻求帮助时, 经常会被建议对 schema 进行范式化设计, 尤其是写密集的场景. 这通常是个好建议. 因为下面这些原因, 范式化通常能够带来好处:
范式化的更新操作通常比反范式化要快.
当数据较好地范式化时, 就只有很少或者没有重复数据, 所以只需要修改更少的数据.
范式化的表通常更小, 可以更好地放在内存里, 所以执行操作会更快.
很少有多余的数据意味着检索列表数据时更少需要 DISTINCT 或者 GROUP BY 语句.
范式化设计的 schema 的缺点是通常需要关联. 稍微复杂一些的查询语句在符合范式的 schema 上都可能需要至少一次关联, 也许更多. 这不但代价昂贵, 也可能使一些索引策略无效. 例如, 范式化可能将列存放在不同的表中, 而这些列如果在一个表中本可以属于同一个索引.
3.2 反范式的优点和缺点
反范式化的 schema 因为所有数据都在一张表中, 可以很好地避免关联. 如果不需要关联表, 则对大部分查询最差的情况 -- 即使表没有使用索引 -- 是全表扫描. 当数据比内存大时这可能比关联要快得多, 因为这样避免了随机 I/O .
单独的表也能使用更有效的索引策略.
3.3 混用范式化和反范式化
范式化和反范式化的 schema 各有优劣, 怎么选择最佳的设计?
事实是, 完全的范式化和完全的反范式化 schema 都是实验室里才有的东西: 在真实世界中很少会这么极端地使用. 在实际应用中经常需要混用, 可能使用部分范式化的 schema, 缓存表, 以及其他技巧.
最常见的反范式化数据的方法是复制或者缓存, 在不同的表中存储相同的特定列. 在 MySQL 5.0 和更新版本中, 可以使用触发器更新缓存值, 这使得实现这样的方案变得更简单.
好啦, 本章的内容就到这里啦, 我们下期见~
全栈全平台开源项目 CodeRiver
CodeRiver 是一个免费的项目协作平台, 愿景是打通 IT 产业上下游, 无论你是产品经理, 设计师, 程序员或是测试, 还是其他行业人员, 只要有好的创意, 想法, 都可以来 CodeRiver 免费发布项目, 召集志同道合的队友一起将梦想变为现实!
CodeRiver 本身还是一个大型开源项目, 致力于打造全栈全平台企业级精品开源项目. 涵盖了 React,vue,Angular, 小程序, ReactNative,Android,Flutter,Java,Node 等几乎所有主流技术栈, 主打代码质量.
目前已经有近 100 名优秀开发者参与, GitHub 上的 star 数量将近 1000 个. 每个技术栈都有多位经验丰富的大佬坐镇, 更有两位架构师指导项目架构. 无论你想学什么语言处于什么技术水平, 相信都能在这里学有所获.
通过 高质量源码 + 博客 + 视频, 帮助每一位开发者快速成长.
项目地址: https://github.com/cachecats/coderiver
您的鼓励是我们前行最大的动力, 欢迎点赞, 欢迎送小星星 ~
来源: https://juejin.im/post/5c488039f265da61553b23c5