21 个 MySQL 表设计的经验准则

作为后端开发，我们经常需要设计数据库表。整理了21个设计MySQL表的经验准则，分享给大家，大家看完一定会有帮助的。

数据库表名、字段名、索引名等都需要命名规范，可读性高(一般要求用英文)，让别人一看命名，就知道这个字段表示什么意思。

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

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 类型。

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

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: 修改时间，必须，更新记录时，需要更新它

version : 数据记录的版本号，用于乐观锁，非必须

remark ：数据记录备注，非必须

modified_by :修改人，非必须

首先， NOT NULL 可以防止出现空指针问题。

其次，NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL。

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;

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;

第一范式：对属性的原子性，要求属性具有原子性，不可再分解；

第二范式：对记录的唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖；

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

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

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

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

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

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

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

utf8：支持中英文混合场景，国际通过，3个字节长度

utf8mb4:   完全兼容utf8，4个字节长度，一般存储emoji表情需要用到它。

GBK ：支持中文，但是不支持国际通用字符集，2个字节长度

`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授权态'

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字节，跟时区有关

可以通过数据库中的相关表实现级联更改。

实时监控某张表中的某个字段的更改而需要做出相应的处理。

例如可以生成某些业务的编号。

为什么需要分库分表

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

事务问题

跨库关联

排序问题

分页问题

查询SQL尽量不要使用select *，而是select具体字段

如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1

应尽量避免在where子句中使用or来连接条件

如果插入数据过多，考虑批量插入

- EOF -

关注「程序员的那些事」加星标，不错过圈内事

点赞和在看就是最大的支持❤️