不是，你还在随便设计数据库字段类型和长度？-技术圈

程序员的成长之路

互联网/程序员/技术/资料共享

阅读本文大概需要 10 分钟。

来自：juejin.cn/post/7350936959060541480

前言

作为一名后端开发，我们经常需要设计数据库表，以下是我整理的一些MySQL表的经验，希望可以给大家一点参考和帮助

1.命名规范

数据库表名、字段名、索引名等都需要命名规范

表名、字段名必须使用小写字母或数字(不推荐) 禁止使用数字开头，禁止使用拼音，并且一般不使用英文缩写
主键索引名为pk_字段名; 唯一索引名为uk_字段名; 普通索引名为 idx_字段名

看个反例

acc_no,1_acc_no,zhanghao

看个正例

account_no,account_number

2.选择合适的字段类型

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

尽可能选择存储空间小的字段类型，就比如数字类型，从 tinyint (1byte)、smallint (2byte)、int (4byte)、bigint (8byte)
小数类型如金额，必须选择 decimal ，禁止使用 float 和 double
如果存储的字符串长度几乎相等，使用 char 定长字符串类型
varchar 是可变长字符串，不预先分配存储空间，长度不要超过5000
如果存储的字符串过长，建议字段类型修改为 text ，同时抽出单独的一张表，用主键与之对应

思考与疑惑

为什么长度几乎相等的字符串要选char而不是varchar？

比如性别(gender)这个字段，只有两个值(男、女)，就一个字符，为什么用char(1),而不是varchar(1)?

因为char(1)就真的是只开辟了1个字符大小，而varchar(1)不是只开辟一个字符大小，varchar需要单独记录字符串长度的大小需要额外空间

为什么在字段设计的时候有的人设置gender为tinyint,有点设置gender为char(1)?

在设计表的时候可以通过0和1来标识男还是女，也可以通过 '男' 和 '女' 来标识，可是有什么区别呢?

这里就涉及到一个概念了，数据库字段长度的表示到底是字符长度还是字节长度？

其实在MySQL中，varchar和char类型表示字符长度，而其他类型表示的长度都是字节长度

举个例子，'男' 如果用字符长度来表示就是1，但是用字节长度来表示就是3个字节，在UTF-8下中文基本都是三字节

而用0和1来标识男女就只需要一个字节

3.主键设计要合理

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

4.选择合适的字段长度

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

为什么字段长度一般要设置为2的幂呢？

这是因为数据库在存储数据时，会使用位运算来处理字段的长度，而位运算对于2的幂次方比较方便。如果字段长度不是2的幂哈，则需要使用算术运算来处理，这样会降低数据库的性能。

此外，设置字段长度为2的幂哈也有助于减少数据库的内存使用。数据库会根据字段长度来分配内存空间，如果字段长度不是2的幂哈，则需要使用更大的内存块来存储，这样会造成内存的浪费。

因此，为了提高数据库的性能和优化内存使用，一般建议将字段长度设置为2的幂哈。如果确实需要更长的字段长度，可以使用变长字段类型（如VARCHAR）来适应不同的长度需求。

varchar(20)和varchar(255)有什么区别？

通常情况下使用varchar(20)和varchar(255)占用的空间都是一样的,但是使用索引长度有所不同。

int(1)和nt(11)有什么区别？

在MySQL中，对于int类型的字段，设置其长度并不是指存储的数字位数或最大值的大小，而是指显示宽度。

也就是说，长度设置对实际存储的数据范围和占用空间并无影响，它主要影响的是数据的显示格式。

举个例子：

当长度设置为1的时候，表示当该字段值被查询并显示时，MySQL会为这个整数值分配至少1个字符的宽度。如果实际值的位数小于1，MySQL会在前面补足空格以达到指定的显示宽度；如果实际值的位数大于1，则按照实际位数显示。例如，一个值为123的INT字段，若设置长度为1，实际显示时仍会完整显示为“123”，不会截断。
当长度设置为11的时候，同理，表示当该字段值被查询并显示时，MySQL会为其分配至少11个字符的宽度。同样，如果实际值的位数小于11，前面补足空格；若大于11，则按实际位数显示。

那为什么会看到有人喜欢设置int(11)呢？

最大可能位数：INT类型的最大可能位数是10位（不含正负号），即从-2,147,483,648到2,147,483,647。

设置长度为11可以确保任何在这个范围内的整数值在显示时都不需要额外的填充空格。即使数值较小，如只有1位或2位，也能保证有足够的宽度来容纳可能出现的最大位数，使得数据显示整齐且易于阅读。

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

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

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

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

delete from account_info_tab whereaccount_no =‘666’;

逻辑删除呢，就是这样：

update account_info_tab set is_deleted = 1 where account_no =‘666’;

那么为什么推荐逻辑删除而非物理删除？

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

6.每个表都需要添加几个通用字段如主键、create_time、update_time

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

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

7.尽可能的使用not null定义字段

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

为什么要这样做呢？

首先，NOT NULL 可以防止出现空指针的问题
其次，NULL 值的存储也需要额外的空间，它也会导致比较运算更为复杂，使优化器难以优化SQL
NULL 值有可能会导致索引失效

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

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

索引也不要建得太多，一般单表索引个数不要超过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;

9.不需要严格遵守3NF，通过业务字段冗余来减少表关联

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

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

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

以上这张存放商品信息的基本表。总金额这个字段的存在，表明该表的设计不满足第三范式，因为总金额可以由单价*数量得到，说明总金额是冗余字段。

但是，增加总金额这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

10.不使用外键，都在代码层维护

阿里的Java开发规范也明确规定了

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

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

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

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

11.没有特殊场景一般都选择INNODB存储引擎

建表是需要选择存储引擎的，我们一般都选择 INNODB 存储引擎，除非读写比率小于1%，才会考虑使用MyISAM(也就是基本上都是读的场景)

12.时间类型的选择

我们设计表的时候，一般都需要加通用时间的字段，如 create_time、update_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类型来保存日期和时间，因为存储范围更大，且跟时区无关

13.不建议在数据表中使用Text数据类型，而要单独开一张表放Text类型的数据呢？

从BufferPool的角度考虑一下，大家认为BufferPool有什么关系呢？

一开始我是这样考虑的，Text字段一般来说会很大，如果要加载到BufferPool里面，会把内存撑爆？

我一开始是从这个角度去想的，但是后来想想，无论要不要将Text类型的字段的数据单独放到一张表，都不影响加载到BufferPool，所以撑爆内存的这个角度想不太对

换个角度，换到索引的角度去想一下

在MySQL的InnoDB引擎下，我们是通过B+树去存储索引结构的，在B+树中真正存储数据的都是叶子节点

而且我们一页只能存放16KB大小的数据，如果说你把Text数据和其他数据同时放在一张表，那么一条记录会比没放Text的时候要大很多，导致一个索引页存放的数据条数大大的减少

14.考虑是否需要分库分表

什么是分库分表呢？

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

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

我们在设计表的时候，其实可以提前估算一下，是否需要做分库分表。比如一些用户信息，未来可能数据量到达百万设置千万的话，就可以提前考虑分库分表。

为什么需要分库分表？

1.数据量太大的话，SQL的查询就会变慢。

2.如果一个查询SQL没命中索引，千百万数据量级别的表可能会拖垮整个数据库。

3.即使SQL命中了索引，如果表的数据量超过一千万的话，查询也是会明显变慢的。这是因为索引一般是B+树结构，数据千万级别的话，B+树的高度会增高，查询就变慢了，因为磁盘IO次数变多了

15.sql编写的一些优化经验

查询SQL不要使用select * ,而是select(具体字段)
避免在where子句中使用or来连接条件（可能会导致索引失效，因为or要两个条件都有索引）、
避免在索引列上使用mysql的内置函数
避免在where子句中对字段进行表达式操作，还有隐式转换
避免在where子句中使用 != 或 <> 操作符
使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。
对查询进行优化，应考虑在where及order by涉及的列上建立索引
如果插入数据过多，考虑批量插入
在适当的时候，使用覆盖索引
使用explain 分析你SQL的计划

16.总结

以上就是我对MySQL表设计的一些经验，在优化上面可能还有很多优化的经验，希望大家可以在评论区多交流下sql优化的经验。

<END>

推荐阅读：

官方推出了 Spring AI 框架，Java集成 AI 不再是难事！

Spring Boot 如何防护 XSS + SQL 注入攻击？终于懂了！

    
     《云服务器限时免费领取》
    
    
     轻量级服务器-2核4G5M 1个月
    
    
     云服务器-2核4G3M 1个月
    
⬇戳阅读原文领取                                 朕已阅