MySQL学习（二）---->设计规范&字符集

范式与反范式

　　优秀的库表设计是高性能数据库的基础。如何才能设计出高性能的库表结构呢？这里必须要提到数据库范式。范式是基础规范，反范式是针对性设计。

范式

　　范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式，才能设计出高效率、优雅的数据库，否则可能会设计出低效的库表结构。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，还又称完美范式）。

满足最低要求的叫第一范式，简称 1NF。在第一范式基础上进一步满足一些要求的为第二范式，简称 2NF。其余依此类推。各种范式呈递次规范，越高的范式数据库冗余越小。通常所用到的只是前三个范式，即：第一范式（1NF），第二范式（2NF），第三范式（3NF）。

第一范式

　　第一范式无重复的列，表中的每一列都是拆分的基本数据项，即列不能够再拆分成其他几列，强调的是列的原子性.。

如果在实际场景中，一个联系人有家庭电话和公司电话，那么以“姓名、性别、电话”为表头的表结构就没有达到 1NF。要符合 1NF 我们只需把电话列拆分，让表头变为姓名、性别、家庭电话、公司电话即可。

第二范式

　　第二范式属性完全依赖于主键，首先要满足它符合 1NF，另外还需要包含两部分内容：

• 表必须有一个主键；
• 没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。即要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式

　　第三范式属性不传递依赖于其他非主属性，首先需要满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

第二范式和第三范式的区别

• 第二范式：非主键列是否依赖主键（包括一列通过某一列间接依赖主键），要是有依赖关系就是第二范式；
• 第三范式：非主键列是否直接依赖主键，不能是那种通过传递关系的依赖。要是符合这种依赖关系就是第三范式。

通过对前三个范式的了解，我们知道 3NF 是 2NF 的子集，2NF 是 1NF 的子集。

设计符合 2NF 的表

接下来以订单信息表为例，讲述如何设计一个符合 2NF 的表，首先，我们看原始的订单信息表，如下图所示。

图中，以订单编号和商品编号作为联合主键，商品名称、单位、价格等信息不与主键相关，只与编号相关，违反了第二范式。 应该对订单信息表进行拆分，商品信息单独一张表，订单项目一张表，如下所示，拆分分成 3 张表。

• 包含客户信息的订单信息表；
• 包含商品详情的商品信息表；
• 包含订单详情的订单详情表。

范式优缺点

经过前面的讲解和案例分析可知范式具备以下优点：

1. 避免数据冗余，减少维护数据完整性的麻烦；
2. 减少数据库的空间；
3. 数据变更速度快。

同时，也有如下缺点：

1. 按照范式的规范设计的表，等级越高的范式设计出来的表数量越多。
2. 获取数据时，表关联过多，性能较差。
3. 表的数量越多，查询所需要的时间越多。也就是说所用的范式越高，对数据操作的性能越低。  

反范式

　　范式是普适的规则，满足大多数的业务场景的需求。对于一些特殊的业务场景，范式设计的表，无法满足性能的需求。此时，就需要根据业务场景，在范式的基础之上进行灵活设计，也就是反范式设计。

反范式设计主要从三方面考虑：

• 业务场景；

• 相应时间；

• 字段冗余。

反范式设计就是用空间来换取时间，提高业务场景的响应时间，减少多表关联。主要的优点如下。

1. 允许适当的数据冗余，业务场景中需要的数据几乎都可以在一张表上显示，避免关联；
2. 可以设计有效的索引。

范式与反范式异同

范式化模型：

• 数据没有冗余，更新容易；
• 当表的数量比较多，查询数据需要多表关联时，会导致查询性能低下。

反范式化模型：

• 冗余将带来很好的读取性能，因为不需要 join 很多表；
• 虽然需要维护冗余数据，但是对磁盘空间的消耗是可以接受的。

MySQL 使用原则和设计规范

讲完范式，接下来我们看看 MySQL 使用中的一些使用原则和设计规范。

MySQL 虽然具有很多特性并提供了很多功能，但是有些特性会严重影响它的性能，比如，在数据库里进行计算，写大事务、大 SQL、存储大字段等。

想要发挥 MySQL 的最佳性能，需要遵循 3 个基本使用原则。

1. 首先是需要让 MySQL 回归存储的基本职能：MySQL 数据库只用于数据的存储，不进行数据的复杂计算，不承载业务逻辑，确保存储和计算分离；

2. 其次是查询数据时，尽量单表查询，减少跨库查询和多表关联；

3. 还有就是要杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手。

• 大事务，运行步骤较多，涉及的表和字段较多，容易造成资源的争抢，甚至形成死锁。一旦事务回滚，会导致资源占用时间过长。

• 大 SQL，复杂的 SQL 意味着过多的表的关联，MySQL 数据库处理关联超过 3 张表以上的 SQL 时，占用资源多，性能低下。

• 大批量，意味着多条 SQL 一次性执行完成，必须确保进行充分的测试，并且在业务低峰时段或者非业务时段执行。

• 大字段，blob、text 等大字段，尽量少用。必须要用时，尽量与主业务表分离，减少对这类字段的检索和更新。

下面具体讲解数据库的基本设置规则：

• 必须指定默认存储引擎为 InnoDB，并且禁用 MyISAM 存储引擎，随着 MySQL 8.0 版本的发布，所有的数据字典表都已经转换成了 InnoDB，MyISAM 存储引擎已成为了历史。

• 默认字符集 UTF8mb4，以前版本的 UTF8 是 UTF8mb3，未包含个别特殊字符，新版本的 UTF8mb4 包含所有字符，官方强烈建议使用此字符集。

• 关闭区分大小写功能。设置 lower_case_tables_name=1，即可关闭区分大小写功能，即大写字母 T 和小写字母 t 一样。

MySQL 数据库提供的功能很全面，但并不是所有的功能性能都高效。

1. 存储过程、触发器、视图、event。为了存储计算分离，这类功能尽量在程序中实现。这些功能非常不完整，调试、排错、监控都非常困难，相关数据字典也不完善，存在潜在的风险。一般在生产数据库中，禁止使用。

2. blob、text、enum、set。这些字段类型，在 MySQL 数据库的检索性能不高，很难使用索引进行优化。如果必须使用这些功能，一般采取特殊的结构设计，或者与程序结合使用其他的字段类型替代。比如：set 可以使用整型（0，1，2，3）、注释功能和程序的检查功能集合替代。

以上是基础规范的内容，但并不是全部，只是以点带面，进行粗略的介绍。下面我们开始讲解命名规范，统一的规范命名，可以增加可读性，减少隐式转换。

规范命名

1. 命名规范如下，命名时的字符取值范围为：a~z，0~9 和 _（下画线）。

2. 所有表名小写，不允许驼峰式命名；

3. 允许使用 -（横线）和 （空格）；如下图所示，当使用 -（横线），后台默认会转化成 @002d；

4. 不允许使用其他特殊字符作为名称，减少潜在风险。

数据库库名的命名规则必须遵循“见名知意”的原则，即库名规则为“数据库类型代码 + 项目简称 + 识别代码 + 序号”。

这样包含了更多的业务信息，比如：

• 广告系统业务生产库：AD、AD1、AD2；

• 广告系统业务开发库：ADDEV、ADDEV1、ADDEV2；

• 广告系统业务测试库：ADTEST、ADTEST1、ADTEST2；

• 只有一个数据库，则不加序号，否则末尾增加序号；

• 生产库不加识别代码，否则需要增加识别代码 DEV 或 TEST；

• 如果只作历史库，则只需要项目简称 +H+ 序号；

表名的命名规则分为：

• 单表仅使用 a~z、_；

• 分表名称为“表名_编号”；

• 业务表名代表用途、内容：子系统简称_业务含义_后缀。

常见业务表类型有：

• 临时表，tmp；

• 备份表，bak；

• 字典表，dic；

• 日志表，log。

字段名精确，遵循“见名知意”的原则，格式：名称_后缀。

• 避免普遍简单、有歧义的名称。

• 用户表中，用户名的字段为 user_name比 name更好。

• 布尔型的字段，以助动词（has/is）开头。

• 用户是否有留言 has_message，用户是否通过检查 is_checked 等。

常见后缀如下：

• 流水号/无意义主键，后缀为 id，比如 task_id；

• 时间，后缀为 time，insert_time。

程序账号与数据库名称保持一致。如果所有的程序账号都是 root@‘%’，密码也一样，很容易错连到其他的数据库，造成误操作。

索引命名格式，主要为了区分哪些对象是索引：

• 前缀_表名（或缩写）_字段名（或缩写）；

• 主键必须使用前缀“pk_”；

• UNIQUE 约束必须使用前缀“uk_”；

• 普通索引必须使用前缀“idx_”。

数据库规范库表字段的命名，能够提高数据库的易读性，为数据库表设计打下基础。下面我们具体看看表设计的一些规则。

• 显式指定需要的属性；

创建表时显示指定字符集、存储引擎、注释信息等。        

• 不同系统之间，统一规范；

• 不同表之间的相同字段或者关联字段，字段类型/命名要保持一致；库表字符集和前端程序、中间件必须保持一致的 UTF8mb4。

InnoDB 表的注意事项

• 主键列，UNSIGNED 整数，使用 auto_increment；禁止手动更新 auto_increment，可以删除。

• 必须添加 comment 注释。

• 必须显示指定的 engine。

• 表必备三字段：id、 xxx_create、 xxx_modified。

• id 为主键，类型为 unsigned bigint 等数字类型；

• xxx_create、xxx_modified 的类型均为 datetime 类型，分别记录该条数据的创建时间、修改时间。

备份表/临时表等常见表的设计规范

• 备份表，表名必须添加 bak 和日期，主要用于系统版本上线时，存储原始数据，上线完成后，必须及时删除。

• 临时表，用于存储中间业务数据，定期优化，及时降低表碎片。

• 日志类表，首先考虑不入库，保存成文件，其次如果入库，明确其生命周期，保留业务需求的数据，定期清理。

• 大字段表，把主键字段和大字段，单独拆分成表，并且保持与主表主键同步，尽量减少大字段的检索和更新。

大表，根据业务需求，从垂直和水平两个维度进行拆分。

垂直拆分：按列关联度。

水平拆分：

• 按照时间、地域、范围等；

• 冷热数据（历史数据归档）。

字段设计要求

1. 根据业务场景需求，选择合适的类型，最短的长度；确保字段的宽度足够用，但也不要过宽。所有字段必须为 NOT NULL，空值则指定 default 值，空值难以优化，查询效率低。比如：人的年龄用 unsigned tinyint（范围 0~255，人的寿命不会超过 255 岁）；海龟就必须是 smallint，但如果是太阳的年龄，就必须是 int；如果是所有恒星的年龄都加起来，那么就必须使用 bigint。

2. 表字段数少而精，尽量不加冗余列。

3. 单实例表个数必须控制在 2000 个以内。

4. 单表分表个数必须控制在 1024 个以内。

5. 单表字段数上限控制在 20~50 个。

禁用 ENUM、SET 类型。

• 兼容性不好，性能差。

解决方案：使用 TINYINT，在 COMMENT 信息中标明被枚举的含义。`is_disable` TINYINT UNSIGNED DEFAULT '0' COMMENT '0:启用 1:禁用 2:异常’。

禁用列为 NULL。

• MySQL 难以优化 NULL 列；
• NULL 列加索引，需要额外空间；
• 含 NULL 复合索引无效。

解决方案：在列上添加 NOT NULL/DEFAULT 缺省值。

禁止 VARBINARY、BLOB 存储图片、文件等。

• 禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统中，数据库中存储路径。

不建议使用 TEXT/BLOB：

• 处理性能差；

• 行长度变长；

• 全表扫描代价大。

解决方案：拆分成单独的表。

存储字节越小，占用空间越小。尽量选择合适的整型，如下图所示。

主键列，无负数，建议使用 INT UNSIGNED 或者 BIGINT UNSIGNED；预估字段数字取值会超过 42 亿，使用 BIGINT 类型。

短数据使用 TINYINT 或 SMALLINT，比如：人类年龄，城市代码。

使用 UNSIGNED 存储非负数值，扩大正数的范围。

MySQL字符集

MySQL中的utf8和utf8mb4

我们上边说utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而在MySQL中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以设计MySQL的大叔偷偷的定义了两个概念：

• utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。
• utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

有一点需要大家十分的注意，在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用utf8mb4。

字符集的查看

MySQL支持好多好多种字符集，查看当前MySQL中支持的字符集可以用下边这个语句：

SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];

其中CHARACTER SET和CHARSET是同义词，用任意一个都可以。我们查询一下（支持的字符集太多了，我们省略了一些）：

mysql> SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
...
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
...
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
...
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
...
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
...
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
...
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
...
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
...
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)

可以看到，我使用的这个MySQL版本一共支持41种字符集，其中的Default collation列表示这种字符集中一种默认的比较规则。大家注意返回结果中的最后一列Maxlen，它代表该种字符集表示一个字符最多需要几个字节。为了让大家的印象更深刻，我把几个常用到的字符集的Maxlen列摘抄下来，大家务必记住：

字符集名称	Maxlen
ascii	1
latin1	1
gb2312	2
gbk	2
utf8	3
utf8mb4	4

字符集名称	Maxlen
ascii	1
latin1	1
gb2312	2
gbk	2
utf8	3
utf8mb4	4

查看MySQL中支持的比较规则的命令如下

SHOW COLLATION [LIKE 匹配的模式];

我们前边说过一种字符集可能对应着若干种比较规则，MySQL支持的字符集就已经非常多了，所以支持的比较规则更多，我们先只查看一下utf8字符集下的比较规则：

mysql> SHOW COLLATION LIKE 'utf8\_%';

• 后边紧跟着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。
• 名称后缀意味着该比较规则是否区分语言中的重音、大小写啥的，具体可以用的值如下：

后缀	英文释义	描述
_ai	accent insensitive	不区分重音
_as	accent sensitive	区分重音
_ci	case insensitive	不区分大小写
_cs	case sensitive	区分大小写
_bin	binary	以二进制方式比较

• 比如utf8_general_ci这个比较规则是以ci结尾的，说明不区分大小写。

每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则，SHOW COLLATION的返回结果中的Default列的值为YES的就是该字符集的默认比较规则，比方说utf8字符集默认的比较规则就是utf8_general_ci。