关于mysql:21个MySQL表设计的经验准则

2次阅读

共计 6332 个字符，预计需要花费 16 分钟才能阅读完成。

数据库表名、字段名、索引名等都须要命名标准，可读性高(个别要求用英文)，让他人一看命名，就晓得这个字段示意什么意思。

比方一个表的账号字段，反例如下：

acc_no,1_acc_no,zhanghao

正例：

account_no,account_number

表名、字段名必须应用小写字母或者数字，禁止应用数字结尾，禁止应用拼音，并且个别不应用英文缩写。
主键索引名为pk_字段名；惟一索引名为uk_字段名；一般索引名则为idx_字段名。

设计表时，咱们须要抉择适合的字段类型，比方：

尽可能抉择存储空间小的字段类型，就如同数字类型的，从 tinyint、smallint、int、bigint 从左往右开始抉择
小数类型如金额，则抉择 decimal，禁止应用 float 和 double。
如果存储的字符串长度简直相等，应用 char 定长字符串类型。
varchar是可变长字符串，不事后调配存储空间，长度不要超过5000。
如果存储的值太大，倡议字段类型批改为text，同时抽出独自一张表，用主键与之对应。
同一表中，所有 varchar 字段的长度加起来，不能大于65535. 如果有这样的需要，请应用TEXT/LONGTEXT 类型。

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

先问大家一个问题，大家晓得数据库字段长度示意 字符长度 还是 字节长度 嘛？

其实在 mysql 中，varchar和 char 类型示意字符长度，而其余类型示意的长度都示意字节长度。比方 char(10) 示意字符长度是 10，而 bigint（4） 示意显示长度是 4 个字节，然而因为 bigint 理论长度是 8 个字节，所以 bigint（4）的理论长度就是 8 个字节。

咱们在设计表的时候，须要充分考虑一个字段的长度，比方一个用户名字段（它的长度 5~20 个字符），你感觉应该设置多长呢？能够思考设置为 username varchar（32）。字段长度个别设置为 2 的幂哈（也就是 2 的 n 次方）。’;

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

物理删除：把数据从硬盘中删除，可开释存储空间
逻辑删除：给数据增加一个字段，比方is_deleted，以标记该数据曾经逻辑删除。

物理删除就是执行 delete 语句，如删除 account_no =‘666’ 的账户信息 SQL 如下：

delete from account_info_tab whereaccount_no ='666';

逻辑删除呢，就是这样：

update account_info_tab set is_deleted = 1 where account_no ='666';

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

为什么不举荐应用物理删除，因为复原数据很艰难

物理删除会使自增主键不再间断

外围业务表的数据不倡议做物理删除，只适宜做状态变更。

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

id：主键，一个表必须得有主键，必须
create_time：创立工夫，必须
modifed_time/update_time: 批改工夫，必须，更新记录时，须要更新它
version : 数据记录的版本号，用于乐观锁，非必须
remark：数据记录备注，非必须
modified_by : 批改人，非必须
creator：创建人，非必须

咱们建表的时候，要牢记，一张表的字段不宜过多哈，个别尽量不要超过 20 个字段哈。笔者记得上个公司，有搭档设计开户表，加了五十多个字段。。。

如果一张表的字段过多，表中保留的数据可能就会很大，查问效率就会很低。因而，一张表不要设计太多字段哈，如果业务需要，切实须要很多字段，能够把一张大的表，拆成多张小的表，它们的主键雷同即可。

当表的字段数十分多时，能够将表分成两张表，一张作为条件查问表，一张作为具体内容表 (次要是为了性能思考)。

如果没有非凡的理由，个别都倡议将字段定义为 NOT NULL。

为什么呢？

首先，NOT NULL 能够防止出现空指针问题。
其次，NULL值存储也须要额定的空间的，它也会导致比拟运算更为简单，使优化器难以优化 SQL。
NULL值有可能会导致索引生效
如果将字段默认设置成一个空字符串或常量值并没有什么不同，且都不会影响到应用逻辑，那就能够将这个字段设置为NOT NULL。

首先，评估你的表数据量。如果你的表数据量只有一百几十行，就没有必要加索引。否则设计表的时候，如果有查问条件的字段，个别就须要建设索引。然而索引也不能滥用：

索引也不要建得太多，个别单表索引个数不要超过 5 个。因为创立过多的索引，会升高写得速度。
区分度不高的字段，不能加索引，如性别等
索引创立完后，还是要留神防止索引生效的状况，如应用 mysql 的内置函数，会导致索引生效的
索引过多的话，能够通过联结索引的话形式来优化。而后的话，索引还有一些规定，如笼罩索引，最左匹配准则等等。。

假如你新建一张用户表，如下：

CREATE TABLE user_info_tab (`id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对于这张表，很可能会有依据 user_id 或者 name 查问用户信息，并且，user_id是惟一的。因而，你是能够给 user_id 加上惟一索引，name加上一般索引。

CREATE TABLE user_info_tab (`id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  UNIQUE KEY un_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

第一范式：对属性的原子性，要求属性具备原子性，不可再合成；
第二范式：对记录的唯一性，要求记录有惟一标识，即实体的唯一性，即不存在局部依赖；
第三形式：对字段的冗余性，要求任何字段不能由其余字段派生进去，它要求字段没有冗余，即不存在传递依赖；

咱们设计表及其字段之间的关系, 应尽量满足第三范式。然而有时候，能够适当冗余，来提高效率。比方以下这张表

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

以上这张寄存商品信息的根本表。总金额 这个字段的存在，表明该表的设计不满足第三范式，因为 总金额 能够由 单价 * 数量 失去，阐明 总金额 是冗余字段。然而，减少 总金额 这个冗余字段，能够进步查问统计的速度，这就是以空间换工夫的作法。

当然，这只是个小例子哈，大家开发设计的时候，要联合具体业务剖析哈。

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

因而，咱们个别防止应用 MySQL 保留字，如 select、interval、desc 等等

什么是外键呢？

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

阿里的 Java 标准也有这么一条：

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

咱们为什么不举荐应用外键呢？

应用外键存在性能问题、并发死锁问题、应用起来不不便等等。每次做 DELETE 或者 UPDATE 都必须思考外键束缚，会导致开发的时候很好受, 测试数据造数据也不不便。

还有一个场景不能应用外键，就是分库分表。

建表是须要抉择 存储引擎 的，咱们个别都抉择 INNODB 存储引擎，除非读写比率小于1%, 才思考应用MyISAM。

有些小伙伴可能会有纳闷，不是还有 MEMORY 等其余存储引擎吗？什么时候应用它呢？其实其余存储引擎个别除了都倡议在 DBA 的领导下应用。

咱们来温习一下这 MySQL 这三种存储引擎的比照区别吧：

个性	INNODB	MyISAM	MEMORY
事务平安	反对	无	无
存储限度	64TB	有	有
空间应用	高	低	低
内存应用	高	低	高
插入数据速度	低	高	高
是否反对外键	反对	无	无

数据库库、表、开发程序等都须要对立字符集，通常中英文环境用utf8。

MySQL 反对的字符集有 utf8、utf8mb4、GBK、latin1 等。

utf8：反对中英文混合场景，国内通过，3 个字节长度
utf8mb4: 齐全兼容 utf8，4 个字节长度，个别存储 emoji 表情 须要用到它。
GBK：反对中文，然而不反对国内通用字符集，2 个字节长度
latin1：MySQL 默认字符集，1 个字节长度

如果你设计的数据库字段是枚举类型的话，就须要在 comment 前面正文分明每个枚举的意思，以便于保护

正例如下：

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session 受权态 00：在线 - 受权态无效 01：下线 - 受权态生效 02：下线 - 被动退出 03：下线 - 在别处被登录'

反例：

`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session 受权态'

并且，如果你的枚举类型在将来的版本有减少批改的话，也须要同时保护到 comment 前面。

咱们设计表的时候，个别都须要加通用工夫的字段，如 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 类型来保留日期和工夫，因为存储范畴更大，且跟时区无关。

什么是存储过程

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

什么是触发器

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

能够通过数据库中的相干表实现级联更改。
实时监控某张表中的某个字段的更改而须要做出相应的解决。
例如能够生成某些业务的编号。
留神不要滥用，否则会造成数据库及应用程序的保护艰难。

对于 MYSQL 来说，存储过程、触发器等还不是很成熟，并没有欠缺的出错记录解决，不倡议应用。

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

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

有时候两张表存在 N:N 关系时，咱们应该打消这种关系。通过减少第三张表，把 N:N 批改为两个 1:N。比方图书和读者，是一个典型的多对多的关系。一本书能够被多个读者借，一个读者又能够借多本书。咱们就能够设计一个借书表，蕴含图书表的主键，以及读者的主键，以及借还标记等字段。

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

在 MySQl 中，这种形式保留的设计方案，其实是不太正当的。这种十分大的数据，能够保留到 mongodb 中，而后，在业务表保留对应 mongodb 的id即可。

这种设计思维相似于，咱们表字段保留图片时，为什么不是保留图片内容，而是间接保留图片 url 即可。

什么是分库分表呢？

分库：就是一个数据库分成多个数据库，部署到不同机器。

分表：就是一个数据库表分成多个表。

咱们在设计表的时候，其实能够提前估算一下，是否须要做 分库分表。比方一些用户信息，将来可能数据量达到百万设置千万的话，就能够提前思考分库分表。

为什么须要分库分表: 数据量太大的话，SQL 的查问就会变慢。如果一个查问 SQL 没命中索引，千百万数据量级别的表可能会拖垮整个数据库。即便 SQL 命中了索引，如果表的数据量超过一千万的话，查问也是会显著变慢的。这是因为索引个别是 B + 树结构，数据千万级别的话，B+ 树的高度会增高，查问就变慢啦。

分库分表次要有程度拆分、垂直拆分的说法，拆分策略有range 范畴、hash 取模。而分库分表次要有这些问题：

事务问题
跨库关联
排序问题
分页问题
分布式 ID

最初的话，跟大家聊来一些写 SQL 的教训吧：

查问 SQL 尽量不要应用 select *，而是select 具体字段
如果晓得查问后果只有一条或者只有最大 / 最小一条记录，倡议用limit 1
应尽量避免在 where 子句中应用 or 来连贯条件
留神优化 limit 深分页问题
应用 where 条件限定要查问的数据，防止返回多余的行
尽量避免在索引列上应用 mysql 的内置函数
应尽量避免在 where子句中对字段进行表达式操作
应尽量避免在 where 子句中应用!= 或<>操作符
应用联结索引时，留神索引列的程序，个别遵循最左匹配准则。
对查问进行优化，应思考在 where 及 order by 波及的列上建设索引
如果插入数据过多，思考批量插入
在适当的时候，应用笼罩索引
应用 explain 剖析你 SQL 的打算

正文完

mysql

发表至： mysql

2022-09-25

0

关于mysql:mysql数据库-sqlmodeonlyfullgroupby-模式引发的惨案

javarmiserverExportException-Port-already-in-use-1099

关于mysql:一文弄懂-OceanBase-用户管理和访问控制

MySQL逻辑架构-SQL语句的执行都经历了哪些步骤

关于前端:面试官这些js手写题你会吗

关于mysql:21个MySQL表设计的经验准则

1. 命名标准

2. 抉择适合的字段类型

3. 主键设计要正当

4. 抉择适合的字段长度

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

6. 每个表都须要增加这几个通用字段如主键、create_time、modifed_time 等

7. 一张表的字段不宜过多

8. 尽可能应用 not null 定义字段

9. 设计表时，评估哪些字段须要加索引

10. 不须要严格遵守 3NF，通过业务字段冗余来缩小表关联

11. 防止应用 MySQL 保留字

12. 不搞外键关联，个别都在代码保护

13. 个别都抉择 INNODB 存储引擎

14. 抉择适合对立的字符集。

15. 如果你的数据库字段是枚举类型的，须要在 comment 正文分明

16. 工夫的类型抉择

17. 不倡议应用 Stored procedure (包含存储过程，触发器)。

18. 1:N 关系的设计

19. 大字段

20. 思考是否须要分库分表

21. sqL 编写的一些优化教训

站内搜索