InnoDB DDL表设计

薄荷街的香草先生
78 阅读12分钟

作者：捡田螺的小男孩
链接：juejin.cn/post/714713…
来源：稀土掘金

表命名规范

tb_业务前缀_表作用 [tb_account_number]
  • 表名、字段名必须使用小写字母或者数字，禁止使用数字开头，禁止使用拼音，并且一般不使用英文缩写。
  • 主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。
  • 表名不使用复数名词

字段

避免使用MySQL保留字

如果库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号来引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

因此，我们一般避免使用MySQL保留字，如select、interval、desc等等

必备字段与命名

表必备一般来说，或具备这几个字段：

  • id： 主键，一个表必须得有主键，必须
  • create_time： 创建时间，必须
  • modifed_time: 修改时间，必须，更新记录时，需要更新它
  • version : 数据记录的版本号，用于乐观锁，非必须
  • remark ：数据记录备注，非必须
  • modified_by :修改人，非必须
  • creator ：创建人，非必须
status: 标识状态  
delete_status: 软删除标志位  
关联字段：关联表名_字段名称 [base_user_id]  
必备三字段：id，create_time，update_time

字段类型

设计表时，我们需要选择合适的字段类型，比如：

  • 尽可能选择存储空间小的字段类型，就好像数字类型的，从tinyint、smallint、int、bigint从左往右开始选择
  • 小数类型如金额，则选择 decimal，禁止使用 float 和 double。
  • 如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
  • varchar是可变长字符串，不预先分配存储空间，长度不要超过5000。
  • 如果存储的值太大，建议字段类型修改为text，同时抽出单独一张表，用主键与之对应。
  • 同一表中，所有varchar字段的长度加起来，不能大于65535. 如果有这样的需求，请使用TEXT/LONGTEXT 类型。
id: bigint unsigned  
时间: datetime  
是否：tinyint unsigned [1-是，0-否]  
枚举：tinyint  
金额：decimal(m,n) [n是指小数的长度，m是指整数加小数的总长度]  
人年龄：tinyint unsigned

字段长度

先问大家一个问题，大家知道数据库字段长度表示字符长度还是字节长度嘛？

其实在mysql中，varchar和char类型表示字符长度，而其他类型表示的长度都表示字节长度。比如char(10)表示字符长度是10，而bigint（4）表示显示长度是4个字节，但是因为bigint实际长度是8个字节，所以bigint（4）的实际长度就是8个字节。

我们在设计表的时候，需要充分考虑一个字段的长度，比如一个用户名字段（它的长度5~20个字符），你觉得应该设置多长呢？可以考虑设置为 username varchar（32）。字段长度一般设置为2的幂哈（也就是2的n次方）。

时间的类型

我们设计表的时候，一般都需要加通用时间的字段，如create_time、modified_time等等。那对于时间的类型，我们该如何选择呢？

对于MySQL来说，主要有date、datetime、time、timestamp 和 year。

  • date ：表示的日期值, 格式yyyy-mm-dd,范围1000-01-01 到 9999-12-31，3字节
  • time ：表示的时间值，格式 hh:mm:ss，范围-838:59:59 到 838:59:59，3字节
  • datetime：表示的日期时间值，格式yyyy-mm-dd hh:mm:ss，范围1000-01-01 00:00:00到9999-12-31 23:59:59```,8字节，跟时区无关
  • timestamp：表示的时间戳值，格式为yyyymmddhhmmss，范围1970-01-01 00:00:01到2038-01-19 03:14:07，4字节，跟时区有关
  • year：年份值，格式为yyyy。范围1901到2155，1字节

推荐优先使用datetime类型来保存日期和时间，因为存储范围更大，且跟时区无关。

大字段

设计表的时候，我们尤其需要关注一些大字段，即占用较多存储空间的字段。比如用来记录用户评论的字段，又或者记录博客内容的字段，又或者保存合同数据的字段。如果直接把表字段设计成text类型的话，就会浪费存储空间，查询效率也不好。

在MySQl中，这种方式保存的设计方案，其实是不太合理的。这种非常大的数据，可以保存到mongodb中，然后，在业务表保存对应mongodb的id即可。

这种设计思想类似于，我们表字段保存图片时，为什么不是保存图片内容，而是直接保存图片url即可

not null

如果没有特殊的理由， 一般都建议将字段定义为 NOT NULL 。

为什么呢？

  • 首先， NOT NULL 可以防止出现空指针问题。
  • 其次，NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL。
  • NULL值有可能会导致索引失效
  • 如果将字段默认设置成一个空字符串或常量值并没有什么不同，且都不会影响到应用逻辑， 那就可以将这个字段设置为NOT NULL
  • NULL只能通过is null is not null判断，用=号永远返回false

字段数量

我们建表的时候，要牢记，一张表的字段不宜过多哈，一般尽量不要超过20个字段哈。

如果一张表的字段过多，表中保存的数据可能就会很大，查询效率就会很低。因此，一张表不要设计太多字段哈，如果业务需求，实在需要很多字段，可以把一张大的表，拆成多张小的表，它们的主键相同即可。

当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表 (主要是为了性能考虑)。

索引

主键设计要合理

主键设计的话，最好不要与业务逻辑有所关联。有些业务上的字段，比如身份证，虽然是唯一的，一些开发者喜欢用它来做主键，但是不是很建意哈。主键最好是毫无意义的一串独立不重复的数字，比如UUID，又或者Auto_increment自增的主键，或者是雪花算法生成的主键等等;

唯一索引

  • 可以给单个字段加，也可以创建联合的唯一索引  
  • 唯一索引失效: 如果是联合的唯一索引，字段值出现null时，则唯一性约束可能会失效  
  • 创建唯一索引时，相关字段一定不能包含null值，否则唯一性会失效

评估加索引的字段

评估你的表数据量。如果你的表数据量只有一百几十行，就没有必要加索引。否则设计表的时候，如果有查询条件的字段，一般就需要建立索引

不使用外键关联

什么是外键呢？

外键，也叫FOREIGN KEY，它是用于将两个表连接在一起的键。FOREIGN KEY是一个表中的一个字段（或字段集合），它引用另一个表中的PRIMARY KEY。它是用来保证数据的一致性和完整性的。

阿里的Java规范也有这么一条：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

我们为什么不推荐使用外键呢？

  • 使用外键存在性能问题、并发死锁问题、使用起来不方便等等。每次做DELETE或者UPDATE都必须考虑外键约束，会导致开发的时候很难受,测试数据造数据也不方便。
  • 还有一个场景不能使用外键，就是分库分表。

存储引擎、字符集、排序规则

存储引擎

建表是需要选择存储引擎的，我们一般都选择INNODB存储引擎，除非读写比率小于1%, 才考虑使用MyISAM 。

有些小伙伴可能会有疑惑，不是还有MEMORY等其他存储引擎吗？什么时候使用它呢？其实其他存储引擎一般除了都建议在DBA的指导下使用。

我们来复习一下这MySQL这三种存储引擎的对比区别吧：

特性INNODBMyISAMMEMORY
事务安全支持无无
存储限制64TB有有
空间使用高低低
内存使用高低高
插入数据速度低高高
是否支持外键支持无无

字符集

数据库库、表、开发程序等都需要统一字符集，建议设置utf8mb4。

MySQL支持的字符集有utf8、utf8mb4、GBK、latin1等。

  • utf8：支持中英文混合场景，国际通过，3个字节长度，但无法存储4个字节的emoji表情
  • utf8mb4: 完全兼容utf8，4个字节长度，一般存储emoji表情需要用到它。
  • GBK ：支持中文，但是不支持国际通用字符集，2个字节长度
  • latin1：MySQL默认字符集，1个字节长度，容易出现乱码问题，在实际项目中使用比较少

排序规则

  • 排序规则和字符集有关  
  • 字符集utf8mb4对应的排序规则:utf8mb4_general_ci-大小写不敏感；utf8mb4_bin-大小写敏感

设计规范

不需要严格遵守 3NF，冗余字段减少表关联

什么是数据库三范式（3NF），大家是否还有印象吗？

  • 第一范式：对属性的原子性，要求属性具有原子性，不可再分解；
  • 第二范式：对记录的唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖；
  • 第三方式：对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；

我们设计表及其字段之间的关系, 应尽量满足第三范式。但是有时候，可以适当冗余，来提高效率。比如以下这张表

商品名称商品型号单价数量总金额
手机华为8000540000

以上这张存放商品信息的基本表。总金额这个字段的存在，表明该表的设计不满足第三范式，因为总金额可以由单价*数量得到，说明总金额是冗余字段。但是，增加总金额这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

优先考虑逻辑删除，而不是物理删除

什么是物理删除？什么是逻辑删除？

  • 物理删除：把数据从硬盘中删除，可释放存储空间
  • 逻辑删除：给数据添加一个字段，比如is_deleted，以标记该数据已经逻辑删除。

为什么推荐用逻辑删除，不推荐物理删除呢？

  • 为什么不推荐使用物理删除，因为恢复数据很困难
  • 物理删除会使自增主键不再连续
  • 核心业务表 的数据不建议做物理删除，只适合做状态变更。

1:N 关系的设计

日常开发中，1对多的关系应该是非常常见的。比如一个班级有多个学生，一个部门有多个员工等等。这种的建表原则就是： 在从表（N的这一方）创建一个字段，以字段作为外键指向主表（1的这一方）的主键。示意图如下:

一对多关系的设计.awebp

学生表是多（N）的一方，会有个字段class_id保存班级表的主键。当然，一班不加外键约束哈，只是单纯保存这个关系而已。

有时候两张表存在N:N关系时，我们应该消除这种关系。通过增加第三张表，把N:N修改为两个 1:N。比如图书和读者，是一个典型的多对多的关系。一本书可以被多个读者借，一个读者又可以借多本书。我们就可以设计一个借书表，包含图书表的主键，以及读者的主键，以及借还标记等字段。

考虑是否需要分库分表

  • 单表行数超过2000万行或者单表容量超过2GB，才推荐进行分库分表  
  • 如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表

不建议使用存储过程，触发器

什么是存储过程

已预编译为一个可执行过程的一个或多个SQL语句。

什么是触发器

触发器，指一段代码，当触发某个事件时，自动执行这些代码。使用场景：

  • 可以通过数据库中的相关表实现级联更改。
  • 实时监控某张表中的某个字段的更改而需要做出相应的处理。
  • 例如可以生成某些业务的编号。
  • 注意不要滥用，否则会造成数据库及应用程序的维护困难。

对于MYSQL来说，存储过程、触发器等还不是很成熟， 并没有完善的出错记录处理，不建议使用。

表备份

-- 1.只复制表结构  
create table tableName like someTable;  
​  
-- 2.只复制表数据  
create table tableName select * from someTable;  
​  
-- 3.复制表结构+数据  
create table tableName like someTable;  
insert into tableName select * from someTable;

表属性查询

-- 查看库中所有的表  
show tables;  
​  
-- 查存储引擎  
show engines;  
​  
-- 查数据存放位置  
show variables like '%datadir%';  
​  
-- 查看表详细信息状态  
show table status like 'tb_relation_student';
avatar
文章
阅读
粉丝