数据库设计

良好的表结构设计是高性能的基石应该根据系统将要执行的业务查询来设计这往往需要权衡各种因素。糟糕的表结构设计会浪费大量的开发时间严重延误项目开发周期让人痛苦万分而且直接影响到数据库的性能并需要花费大量不必要的优化时间效果往往还不怎么样
在数据库表设计上有个很重要的设计准则称为范式设计

范式设计

定义

范式来自英文 Normal Form简称 NF。要想设计—个好的关系必须使关系满足一定的约束条件此约束已经形成了规范分成几个等级一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的同时不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟不仅给数据库的编程人员制造麻烦而且面目可憎可能存储了大量不需要的冗余信息
目前关系数据库有六种范式第一范式1NF、第二范式2NF、第三范式3NF、巴斯-科德范式BCNF、第四范式(4NF和第五范式5NF又称完美范式
满足最低要求的范式是第一范式1NF。在第一范式的基础上进一步满足更多规范要求的称为第二范式2NF其余范式以次类推。一般来说数据库只需满足第三范式(3NF就行了
下面提到的范式定义并非官方定义由于官方定义晦涩难懂因此下面的说法是比较通俗易懂的

第一范式

定义 属于第一范式关系的所有属性都不可再分即数据项不可分
理解 第一范式强调数据表的原子性是其他范式的基础

第二范式

第二范式2NF是在第一范式1NF的基础上建立起来的即满足第二范式2NF必须先满足第一范式1NF
第二范式2NF要求数据库表中的每个实例或行必须可以被惟一地区分
通常在实现来说需要为表加上一个列以存储各个实例的惟一标识。例如员工信息表中加上了员工编号emp_id列因为每个员工的员工编号是惟一的因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码

第三范式

指每一个非主属性既不部分依赖于也不传递依赖于业务主键也就是在第二范式的基础上消除了非主键对主键的传递依赖
例如存在一个部门信息表其中每个部门有部门编号dept_id、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表则根据第三范式3NF也应该构建它否则就会有大量的数据冗余

反范式设计

定义

在实际的业务查询中会大量存在着表的关联查询而大量的表关联很多的时候非常影响查询的性能
所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反。允许存在少量得冗余换句话来说反范式化本质思想就是使用空间来换取时间

优点

• 反范式设计可以减少表的关联
• 可以更好的进行索引优化
  

缺点

• 存在数据冗余及数据维护异常
• 对数据的修改需要更多的成本
  

字段数据类型优化

MySQL 支持的数据类型非常多选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据下面几个简单的原则都有助于做出更好的选择

基本原则

更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快因为它们占用更少的磁盘、内存和 CPU 缓存并且处理时需要的 CPU 周期也更少
但是要确保没有低估需要存储的值的范围因为在的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的就选择你认为不会超过范围的最小类型

简单就好

简单数据类型的操作通常需要更少的 CPU 周期。例如整型比字符操作代价更低因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。比如应该使用 MySQL 内建的类型而不是字符串来存储日期和时间

尽量避免NULL

很多表都包含可为 NULL(空值)的列即使应用程序并不需要保存 NULL 也是如此这是因为可为 NULL 是列的默认属性。通常情况下最好指定列为 NOT NULL除非真的需要存储 NULL 值
如果查询中包含可为 NULL 的列对 MySQL 来说更难优化因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时每个索引记录需要一个额外的字节
通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小所以调优时没有必要首先在现有 schema 中查找并修改掉这种情况除非确定这会导致问题。但是如果计划在列上建索引就应该尽量避免设计成可为 NULL 的列

整数类型

存储整数可以使用这几种整数类型:TINYINTSMALLINTMEDIUMINTINTBIGINT。分别使用 816243264 位存储空间也就是 1、2、3、4、8 个字节
同时整数类型有可选的 UNSIGNED 属性表示不允许负值这大致可以使正数的上限提高一倍。例如 TINYINT UNSIGNED 可以存储的范围是 0~255而 TINYINT 的存储范围是-128~127
有符号和无符号类型使用相同的存储空间并具有相同的性能因此可以根据实际情况选择合适的类型
MySQL 可以为整数类型指定宽度例如 INT(11)对大多数应用这是没有意义的它不会限制值的合法范围,只是规定了 MySQL 的一些交互工具例如 MySQL 命令行客户端)用来显示字符的个数。对于存储和计算来说INT(1)和 INT(20)是相同的
在选择上遵循着更小的通常更好的原则在业务许可的情况下尽量选择位数小的

实数类型

实数是带有小数部分的数字。MySQL 既支持精确类型的存储 DECIMAL 类型也支持不精确类型存储 FLOAT 和 DOUBLE 类型
DECIMAL 类型用于存储精确的小数本质上 MySQL 是以字符串形式存放的。所以 CPU 不支持对 DECIMAL 的直接计算所以在 MySQL 中自身实现了 DECIMAL 的高精度计算。相对而言CPU 直接支持原生浮点计算所以浮点运算明显更快
浮点和 DECIMAL 类型都可以指定精度。对于 DECIMAL 列可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL 5.0 和更高版本将数字打包保存到一个二进制字符串中每 4 个字节存 9 个数字)。例如DECIMAL(18,9) 小数点两边将各存储 9 个数字一共使用 9 个字节:小数点前的数字用 4 个字节小数点后的数字用 4 个字节小数点本身占 1 个字节
MySQL 5.0 和更高版本中的 DECIMAL 类型允许最多 65 个数字
浮点类型在存储同样范围的值时通常比 DECIMAL 使用更少的空间。FLOAT 使用 4 个字节存储DOUBLE 占用 8 个字节所以 DOUBLE 比 FLOAT 有更高的精度和更大的范围
因为需要额外的空间和计算开销所以应该尽量只在对小数进行精确计算时才使用 DECIMAL例如存储财务或金融数据在精度不敏感和需要快速运算的时候选择 FLOAT 和 DOUBLE
但在数据量比较大的而且要求精度时可以考虑使用 BIGINT 代替 DECIMAL将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分则可以把所有金额乘以一百万,然后将结果存储在 BIGINT 里这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题

字符串类型

MysQL 支持多种字符串类型包括 VARCHAR 和 CHAR 类型、BLOB 和 TEXT 类型、ENUM枚举和 SET 类型

VARCHAR 和 CHAR 类型

VARCHAR 和 CHAR 是两种最主要的字符串类型

VARCHAR

VARCHAR 类型用于存储可变长字符串是最常见的字符串数据类型。它比定长类型更节省空间因为它仅使用必要的空间例如越短的字符串使用越少的空间)
在内部实现上既然是变长VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度如果列的最大长度小于或等于 255 字节,则只使用 1 个字节表示,否则使用 2 个字节
VARCHAR 节省了存储空间所以对性能也有帮助。但是由于行是变长的在 UPDATE 时新值比旧值长时使行变得比原来更长这就肯能导致需要做额外的工作。如果一个行占用的空间增长并且在页内没有更多的空间可以存储在这种情况下MyISAM 会将行拆成不同的片段存储InnoDB 则需要分裂页来使行可以放进页内
使用 VARCHAR(5)和 VARCHAR(200)存储’hello’在磁盘空间上开销是一 样的但是更长的列会消耗更多的内存因为 MySQL 通常会 分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时 会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略是只分配真正需要的空间

CHAR

CHAR 类型是定长的MySQL 总是根据定义的字符串长度分配足够的空间。当存储 CHAR 值时MySQL 会删除所有的末尾空格CHAR 值会根据需要采用空格进行填充以方便比较

对比

在 CHAR 和 VARCHAR 的选择上遵循一下对比即可

• 字符串列的最大长度比平均长度大很多﹔列的更新很少使用了像 UTF-8 这样复杂的字符集每个字符都使用不同的字节数进行存储
• CHAR 适合存储很短的字符串或者所有值定长或都接近同一个长度。例如 CHAR 非常适合存储密码的 MD5 值因为这是一个定长的值。对于经常变更的数据CHAR 也比 VARCHAR 更好因为定长的 CHAR 类型不容易产生碎片
• 对于非常短的列CHAR 比 VARCHAR 在存储空间上也更有效率。例如用 CHAR( 1)来存储只有 Y 和 N 的值如果采用单字节字符集只需要一个字节但是 VARCHAR(1)却需要两个字节因为还有一个记录长度的额外字节
  

BLOB 和 TEXT 类型

BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型分别采用二进制和字符方式存储
与其他类型不同MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当 BLOB 和 TEXT 值太大时InnoDB 会使用专门的“外部”存储区域来进行存储此时每个值在行内需要 1~4 个字节存储一个指针然后在外部存储区域存储实际的值
BLOB 和 TEXT 家族之间仅有的不同是 BLOB 类型存储的是二进制数据没有排序规则或字符集而 TEXT 类型有字符集和排序规则
使用 BLOB 和 TEXT 需要注意

• BLOB 和 TEXT 值会引起一些性能问题所以尽量避免使用 BLOB 和 TEXT 类型
• 一定要用建议把 BLOB 或 TEXT 列分离到单独的表中
• 在不必要的时候避免检索大型的 BLOB 或 TEXT 值。例如SELECT *查询就不是很好的想法除非能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则很可能毫无目的地在网络上传输大量的值。建议可以搜索索引列决定需要的哪些数据行然后从符合条件的数据行中检索 BLOB 或 TEXT 值
• 还可以使用合成的(Synthetic)索引来提高大文本字段(BLOB 或 TEXT)的查询性能。简单来说合成索引就是根据大文本字段的内容建立一个散列值并把这个值存储在单独的数据列中接下来就可以通过检索散列值找到数据行了。但是要注意这种技术只能用于精确匹配的查询散列值对于类似“<”或“>=”等范围搜索操作符是没有用处的)。可以使用 MD5 函数生成散列值也可以使用 SHA1(或 CRC32)或者使用自己的应用程序逻辑来计算散列值
  

使用枚举代替字符串

如果表中的字段的取值是固定几个字符串可以使用枚举列代替常用的字符串类型
枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举时非常紧凑会根据列表值的数量压缩到一个或者两个字节中MySQL 在内部会将每个值在列表中的位置保存为整数这样的话可以让表的大小大为缩小
例如

CREATE TABLE enum_test(
  e ENUM(' fish', 'apple', 'dog') NOT NULL
);

INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

注意

• 因为枚举列实际存储为整数而不是字符串所以不要使用数字作为 ENUM 枚举常量这种双重性很容易导致混乱例如 ENUN( ’ 1’,‘2’,‘3’)
• 枚举字段是按照内部存储的整数而不是定义的字符串进行排序的所以尽量按照需要的顺序来定义枚举列
  

日期和时间类型

如果需要存储比秒更小粒度的日期和时间值MySQL 目前没有提供合适的数据类型但是可以使用自己的存储格式

• 可以使用 BIGINT 类型存储微秒级别的时间截
• 使用 DOUBLE 存储秒之后的小数部分
  | 日期类型 | 占用空间 | 表示范围 |
  | — | — | — |
  | DATETIME | 8 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
  | DATE | 3 | 1000-01-01 ~ 9999-12-31 |
  | TIME | 3 | YEAR(2):1970-2070, YEAR(4):1901-2155 |
  | YEAR | 1 | -838:59:59 ~ 838:59:59 |
  | TIMESTAMP | 4 | 1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC |

MySQL 可以使用许多类型来保存日期和时间值例如 YEAR 和 DATE。MySQL 能存储的最小时间粒度为秒
大部分时间类型都没有替代品因此没有什么是最佳选择的问题。唯一的问题 MySQL 提供两种相似的日期类型:DATETIME 和 TIMESTAMP。对于很多应用程序它们都能工作但是在某些场景需要做些适当选择

DATETIME

这个类型能保存大范围的值从 1001 年到 9999 年精度为秒。它把日期和时间封装到格式为 YyYYMMDDHHMMSS 的整数中与时区无关。使用 8 个字节的存储空间

TIMESTAMP

TIMETAMP 类型保存了从 1970 年 1 月 1 日午夜格林尼治标准时间)以来的秒数它和 UNIX 时间戳相同。TIMESTAMP 只使用 4 个字节的存储空间因此它的范围比 DATETIME 小得多﹔只能表示从 1970 年到 2038 年。TIMESTAMP 显示的值也依赖于时区。从空间效率来说当然 TIMETAMP 比 DATETIME 更高

命名规范

• 数据库、表、字段的命名要遵守可读性原则尽可能少使用或者不使用缩写
• 表名、字段名必须使用小写字母或数字禁止出现数字开头禁止两个下划线中间只出现数字。数据库字段名的修改代价很大因为无法进行预发布所以字段名称需要慎重考虑
  • 说明MySQL 在 Windows 下不区分大小写但在 Linux 下默认是区分大小写。因此数据库名、表名、字段名都不允许出现任何大写字母避免节外生枝
• 表名不使用复数名词
• 数据库、表、字段的命名禁用保留字如 desc、range、match 之类
• 对象的名字应该能够描述它所表示的对象
  • 表的名称应该能够体现表中存储的数据内容最好是遵循“业务名称_表的作用”对于存储过程存储过程应该能够体现存储过程的功能。库名与应用名称尽量一致
• 主键索引名为 pk_字段名唯一索引名为 uk_字段名普通索引名则为 idx_字段名
• 表达是与否概念的字段不应该使用 is_xxx 的方式命名而是使用某种状态比如是否删除使用delete_flag而不是is_del数据类型是 unsigned tinyint1 表示是0 表示否