MySQL 8.0 如何创建一张规范的表

2022-05-06 00:00:00 索引 字段 记录 类型 主键

这一节内容，基于 MySQL8.0 版本，聊一下如何创建一张规范的表。

首先贴出一张相对规范的表结构：

CREATE TABLE student_info (`id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',`stu_name` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '姓名',`stu_class` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '班级',`stu_num` INT NOT NULL DEFAULT '0' COMMENT '学号',`stu_score` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '总分',`tuition` DECIMAL(5, 2) NOT NULL DEFAULT '0' COMMENT '学费',`phone_number` VARCHAR(20) NOT NULL DEFAULT '0' COMMENT '电话号码',`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',`status` TINYINT NOT NULL DEFAULT '1' COMMENT '1代表记录有效，0代表记录',PRIMARY KEY (`id`),UNIQUE KEY uniq_stu_num (`stu_num`),KEY idx_stu_score (`stu_score`),KEY idx_update_time_tuition (`update_time`, `tuition`)) ENGINE = INNODB charset = utf8mb4 COMMENT '学生信息表';


这里对上面设置的原因进行解释：

1 表、字段全采用小写。

防止因为大小写问题找不到表或者弄错表。


2 int 类型不再加上大显示宽度，也就是不适用类似int(11) 的形式。

具体原因可复习：MySQL 5.7 和 8.0 几处细节上的差异。


3 每张表必须显式定义主键，可用自增 int 类型或者有序 UUID。

如果 InnoDB 表没有显式定义主键，则可能会选择索引做为主键，但是索引很可能不是递增的，写入数据时，很可能会导致数据页频繁分裂，从而导致写入效率低和页空间浪费。这也是选择自增 int 类型或者有序 UUID 做为主键的原因。


4 增加 comment 来描述字段和表的含义。比如：status TINYINT NOT NULL DEFAULT '1' COMMENT '1代表记录有效，0代表记录'。

方便其他人知道字段的含义，并且不加 comment，可能过一段时间自己都忘记字段作用是什么了。跟写代码加注释一个意思。


5 通常建议包含 create_time 和 update_time 字段，即表必须包含记录创建时间和修改时间的字段。

方便知道记录什么时候创建，什么时候更新的，分析问题的时候很方便。如果有数据归档，也可以根据这两个字段进行归档处理。


6 核心业务表增加记录标记字段。

如上表的 status 字段，写入记录时，默认记录为 1，表示记录有效，如果记录用不到，则把 status 更新成 0，避免物理删除，增加数据安全性。


7 用尽量少的存储空间来存储一个字段的数据：

  • 能用 int 的就不用 char 或者 varchar；

  • 能用 tinyint 的就不用 int；

  • 使用 UNSIGNED 存储非负数值；

  • 只存储年使用 YEAR 类型；

  • 只存储日期使用 DATE 类型。


8 存储浮点数必须使用 DECIMAL 替代 FLOAT 和 DOUBLE。

在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得不到正确的结果。


9 尽可能不使用 TEXT、BLOB 类型。

会浪费更多的磁盘和内存空间，非必要的大量大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。如果实在有某个字段过长需要使用 TEXT、BLOB 类型，则建议独立出来一张表，用主键来对应，避免影响原表的查询效率。


10 经常做为条件、排序、关联的字段增加索引。

加快查询速度，降低锁等待时间。


11 具有性的字段，添加成索引，比如上面的 stu_num 字段。

万一业务没完全解决性，那数据库还有一层性保证。


12 几个字段同时作为条件的概率很高时，或者方便查询能走覆盖索引，可以考虑创建联合索引。

走覆盖索引，避免回表，提高查询速度。


13 字符集使用 utf8mb4，无乱码风险；与 utf8 编码相比，utf8mb4 能支持 Emoji 表情。

utf8 的升级版，建议 8.0 都采用这个字符集。


14 存储引擎使用 InnoDB。

99% 的情况使用 InnoDB 就对了。


15 单表字段数目建议小于 30；

字段数太多影响性能，并且不好维护。

原文链接：https://mp.weixin.qq.com/s/HU2zf_f41260HYEPcO62ug


相关文章