MySQL 规范和优化建议

本文所写内容在于限制一些数据库规范及优化建议，可以留言提出建议。

【1】范式

范式作为本文的第一小节，旨于在设计数据表时候尽可能符合三大范式。

【1-1】第一范式（1NF）

每一列属性都是不可再分的属性值，确保每一列的原子性。
两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

【1-2】第二范式（2NF）

第二范式就是在第一范式的基础上属性完全依赖于主键。

实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

例如在设计实体表时候有一个实体ID，此ID可以区分每一条数据。通常这个唯一属性列被称为主键。

【1-3】 第三范式（3NF）

第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。
例如若在实体表 a 中每行数据都包含实体 b 中 ID、名称等有大量的数据冗余。则需要把实体 b 单独设计成一个新的模型。

【2】字符集、排序

【2-1】字符集选择

请使用 utf8 或 utf8mb4。
utf8 是 utf8mb3 的一个别名，可以支持1-3字节表示的unicode字符。

utf8mb4 的 mb4 是most bytes 4的意思，用来兼容四字节的unicode。utf8mb4 是 utf8 的超集，理论上原来使用 utf8，然后将字符集修改为 utf8mb4，也并不会对已有的utf8编码读取产生任何问题。

当然，为了节省空间，一般情况下使用 utf8 也就够了。除非你的模型中的列是有如 Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和一些不常用的汉字，以及任何新增的 Unicode 字符等等。

为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8，尽管对于 CHAR 类型数据，使用utf8mb4 存储会多消耗一些空间。

【2-2】排序规则

常见排序规则utf8[mb4]_genera_ci、utf8[mb4]_bin。

1. utf8[mb4]_genera_ci 不区分大小写。
2. utf8[mb4]_bin 区分大小写且使用二进制编码。

不需要特定于语言的排序规则功能时，请使用二进制排序规则顺序进行快速比较和排序操作。

相比较下， utf8[mb4]_bin 在比较和排序操作方面相对会快些。按存储数据特点选择就可了。

【3】表

MySQL对数据库的数量没有限制。但操作系统可能对目录数量有所限制。InnoDB最多允许40亿。

表名称需清晰表达业务含义。

同一业务的表，尽可能加上统一的业务标志前缀。

【4】列

MySQL 对每个表有4096列的硬限制，不同的引擎有自身的定义（InnoDB每个表的限制为1017列）。 我们不建议一个表有过多的列，若不可避免可选择垂直、水平切表。

【4-1】列名

MySQL 对列长度有 64 位限制。我们在建模型时候请遵循以下规则。

列名富有代表性且明显含义。 例如广告位模型中广告位名称，我们应规定为 name 而不是 a 、b 等。

列名应该简洁。例如实体模型 a 中实体名称，我们应规定为 name 而不是 实体_name。本身广告位模型已经含有实体 a 含义，故不需要在加实体前缀。

保持列名保持简单。尽量短于18个字符。

【4-2】类型

MySQL 表最大支持 65,535 字节的行限制，意思是你所有字段类型加起来只能使用 65,535 字段（你也可以使用 TEXT、BLOB 类型来超过这种限制）。

当然还是没有去掉额外的存储所需，如 VARCHAR 需要两个字节来存储值的长度。

1. 字符串类型 对于小于8KB的列值，请使用二进制 VARCHAR而不是 BLOB。若你在进行 ROUP BY 和ORDER BY 时候，MySQL 产生的临时表可以使用 MEMORY存储引擎 。
2. 对于可以表示为字符串或数字的唯一ID或其他值，请首选数字列。由于大数值可以比相应字符串存储在更少的字节中，因此它传输速度更快，并且占用更少的内存来进行比较。
3. 尽可能使用最有效（最小）的数据类型。例如 MEDIUMINT 通常比 INT 列占用的空间少25％。
4. 主键索引 尽可能选择小的。因为所有二级索引都会在叶子结点保存一份主键值。
5. 字符串类型 使用 varchar 类型，存储一样数据下 char 会花费较多空间。
6. 数字类型 若不需要使用负数, 应该加上 UNSIGNED 。
7. 时间类型 请使用 timestamp。默认值可选 CURRENT_TIMESTAMP。

【4-3】列长度

对于字符串类型请合理设置其长度，MySQL 对行大小有硬性限制。如以下设置是不能成功创建模型：

CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
f VARCHAR(10000)) ENGINE=InnoDB CHARACTER SET latin1;

当然你若想避开限制可使用 TEXT ，如增加列 g TEXT(6000)。

【4-4】NOT NULL

请严格遵循所有列不能为NULL。

1. 需花费一个字节记录值是否为空。请见 行模式。
2. 可能会使索引失效。
3. 某些函数下失效。如 count(null) 为 0。

【4-5】其他

若将随机生成的值用作InnoDB表中的主键时，请尽可能在其前面加上一个升序值，例如当前日期和时间。当连续的主值在物理上彼此靠近存储时，InnoDB可以更快地插入和检索它们。

比较来自不同列的值时，请尽可能使用相同的字符集和排序规则声明这些列，以避免在运行查询时进行字符串转换。

为所有表设置created_at、updated_at字段。

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created_at',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated_at',

【5】索引

【5-1】主键索引

1. 主键索引 尽可能选择小的。因为所有二级索引都会在叶子结点保存一份主键值。
2. 当使用随机生成的值用作InnoDB表中的主键时，请尽可能在其前面加上一个升序值，例如当前日期和时间。当连续的主值在物理上彼此靠近存储时，InnoDB可以更快地插入和检索它们。

【5-2】二级索引

1. 请控制索引数量，特别当主键类型比较大情况下。
2. 请控制单个索引包含的列数量和顺序。一个索引最多可以包含16列。
3. 联合索引下请根据查询特点，设置最优的排列顺序。尽可能满足最左匹配原则。
4. 若查询列存在于唯一索引，请指定查询列。

【5-3】行模式

1. 对于较大的表，或者包含大量重复的文本或数字数据的表，可以考虑使用 COMPRESSED 行格式。将数据放入缓冲池或执行全表扫描所需的磁盘I/O更少。
2. 当使用COMPACT row格式(默认的InnoDB格式)和变长字符集(如utf8或sjis)时，CHAR(N)列占用的空间是可变的，但至少要占用N个字节。所以尽可能使用VARCHAR。

【6】参数调优

1） 若数据库是比较繁忙的，可以适当增加innodb的并发数。如 set innodb_thread_concurrency = 20;