不是,你还在随便设计数据库字段类型和长度?
共 9280字,需浏览 19分钟
·
2024-05-24 00:00
阅读本文大概需要 10 分钟。
来自:juejin.cn/post/7350936959060541480
前言
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(1)
,而不是varchar(1)
?
char(1)
就真的是只开辟了1个字符大小,而varchar(1)
不是只开辟一个字符大小,varchar需要单独记录字符串长度的大小需要额外空间
3.主键设计要合理
Auto_increment
自增的主键,或者是雪花算法生成的主键等等
4.选择合适的字段长度
username varchar(32)
。字段长度一般设置为2的幂哈(也就是2的n次方)。
varchar(20)
和varchar(255)
占用的空间都是一样的,但是使用索引长度有所不同。
-
当长度设置为1的时候,表示当该字段值被查询并显示时,MySQL会为这个整数值分配至少1个字符的宽度。如果实际值的位数小于1,MySQL会在前面补足空格以达到指定的显示宽度;如果实际值的位数大于1,则按照实际位数显示。例如,一个值为123的INT字段,若设置长度为1,实际显示时仍会完整显示为“123”,不会截断。
-
当长度设置为11的时候,同理,表示当该字段值被查询并显示时,MySQL会为其分配至少11个字符的宽度。同样,如果实际值的位数小于11,前面补足空格;若大于11,则按实际位数显示。
5.优先考虑逻辑删除,而不是物理删除
-
物理删除:把数据从硬盘中删除,可释放存储空间 -
逻辑删除:给数据添加一个字段,比如is_deleted,以标记该数据已经逻辑删除。
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 可以防止出现空指针的问题
-
其次,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,通过业务字段冗余来减少表关联
-
第一范式: 对属性的原子性,要求属性具有原子性,不可再分解;
-
第二范式: 对记录的唯一性,要求记录有唯一标识,即实体的唯一性,即不存在部分依赖;
-
第三方式: 对字段的冗余性,要求任何字段不能由其他字段派生出来,它要求字段没有冗余,即不存在传递依赖;
单价*数量
得到,说明总金额是冗余字段。
10.不使用外键,都在代码层维护
11.没有特殊场景一般都选择INNODB存储引擎
12.时间类型的选择
create_time
、update_time
等等,那对于时间的类型,我们应该如何选择呢?
-
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字节
13.不建议在数据表中使用Text数据类型,而要单独开一张表放Text类型的数据呢?
14.考虑是否需要分库分表
-
分库:就是一个数据库分成多个数据库,部署到不同机器。
-
分表:就是一个数据库表分成多个表。
为什么需要分库分表?
15.sql编写的一些优化经验
-
查询SQL不要使用
select *
,而是select(具体字段) -
避免在where子句中使用or来连接条件(可能会导致索引失效,因为or要两个条件都有索引)、
-
避免在索引列上使用mysql的内置函数
-
避免在where子句中对字段进行表达式操作,还有隐式转换
-
避免在where子句中使用
!=
或<>
操作符 -
使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则。
-
对查询进行优化,应考虑在where及order by涉及的列上建立索引
-
如果插入数据过多,考虑批量插入
-
在适当的时候,使用覆盖索引
-
使用explain 分析你SQL的计划
16.总结
推荐阅读:
官方推出了 Spring AI 框架,Java集成 AI 不再是难事!
Spring Boot 如何防护 XSS + SQL 注入攻击 ?终于懂了!
《云服务器限时免费领取》 轻量级服务器-2核4G5M 1个月 云服务器-2核4G3M 1个月 ⬇戳阅读原文领取 朕已阅