mysql 建表最佳实践

目录

一 自增主键 id

二 创建时间、更新时间

三 字段添加索引

四 数据逻辑删除

五 灵活运用数据库编码

六 添加 version 字段

七 COLLATE 区分字符大小写

---

本文主要总结工作这些年来，一些建表的较好的实践经验，希望能给你一点点启发或者帮助。

一 自增主键 id

为什么需要自增主键id？

从性能、存储空间两个角度考虑：

性能：自增主键在新增记录的时候属于顺序写，磁盘的数据页利用率高，不会触发数据页分裂；与业务相关的字段做主键，容易触发随机写，为了维护索引的有序性，需要移动索引树页子节点，容易引发数据页分裂。

存储空间：自增主键的长度一般要比业务主键要小，而非主键索引的叶子节点上存储的都是主键的值，显然，主键长度越小，非主键索引的叶子节点就越小，非主键索引占用的空间也就越小。

因此，从性能和存储空间两个角度来考虑，自增主键是合理的。

事无绝对，是否可以在建表的时候不创建id？

你还真别说，我还真就遇到过，我们当初这块有一个学生关注老师表，该表就没有主键id，学生id和老师id作为联合主键id，说实话给学生关注老师表添加自增主键id，该字段也没啥业务含义。

如果说创建自增主键id没有业务含义，且能接受文件写入一定的性能影响，我觉得不加自增id也是可以的。

二 创建时间、更新时间

为什么需要添加创建时间、更新时间呢？

有了创建时间和更新时间，对于数据统计、数据追踪有很大的意义；其中更新时间天然就是一个版本的概念，便于乐观锁的实现。

创建时间、更新时间取哪里的时间呢？WEB 服务器时间？DB 服务器时间？

建议取 WEB 服务器时间（Tomcat，Jboss，Apache），不建议取 DB 服务器时间。还真别说，我就遇到过 DB 服务器时间错乱，导致线上数据错误的问题。都是血淋淋的教训。

三 字段添加索引

为什么需要索引？

索引的目的是为了减少查询次数，提高查询效率。可以充分的运用覆盖索引、最左前缀原则、唯一索引等对查询进行优化。

四 数据逻辑删除

什么是数据逻辑删除？

所谓的逻辑删除，就是给数据打上删除标签。

为什么不建议进行物理删除？

因为一旦进行物理删除之后，数据就没了，将来查问题、对数据进行追溯都不方便。再说了，我们是有原则的人，从删库到跑路的事情，我们坚决不干。

五 灵活运用数据库编码

如果说表需要存储 emoji 等特殊字符，可以使用 utf8mb4 编码，不需要建议使用 utf8 编码。

六 添加 version 字段

version 字段是给表添加的版本字段，每次对表进行更新，version 字段也进行更新。version 字段是用来实现乐观锁的。

七 COLLATE 区分字符大小写

查看 COLLATE 支持那些类型？

mysql 中执行 show collation 命令。

COLLATE 有哪些常用类型？

utf8mb4_general_ci（默认）、utf8mb4_unicode_ci、utf8mb4_bin这三个。

• utf8mb4_bin 就是直接将所有字符看作二进制串，然后从最高位往最低位比对。是区分大小写的。
• utf8mb4_general_ci 和 utf8mb4_unicode_ci 是以 ci（Case Insensitive）结尾，不区分大小写

注意 ！！！

如果业务开发中字母需要区分大小写 COLLATE 类型用 utf8mb4_bin，如果不需要 COLLATE 使用 utf8mb4_general_ci 和 utf8mb4_unicode_ci 都行。