数据库设计(MySQL)避坑指南曾经吃过的亏

2024年 3月 13日 84.0k 0

1、主键设计的坑

a、MySQL是索引组织表,所以一定要显示指定主键

没有显示指定主键,MySQL部份功能会受限,比如组复制
没有显示主键,当表要做关联查询时会比较难受
没有显示主键,如果也没有指定唯一键,那么可能会造成删除异常,修改异常

b、不要使用业务字段当主键

如果使用业务字段当主键,当业务发现变化了,原有的主键字段不符合了,需要增加或减少字段对于已上线的系统来说都是毁灭性的,特别是当时间长这个表变成大表后,后面要变更主键可能必须停业务才能操作

c、不要使用组合主键

不建议使用组合主键,每个主键都是二级索引的叶子,如果使用组合主键,极大增加了存储需求

d、不用UUID

理由和C一样

f、自增ID

当有主从表(父子表)时,从表关联主表依赖主表自增产生的ID,那就必须在主表插入后还要查询一次。使业务开发更复杂

建议

使用业务生成雪花ID做为主键,或数据库单独拿一个表来生成自增ID

2、函数设计的坑

开发函数时记得带上DETERMINISTIC 关键字 声明为确定性函数
当没有带 DETERMINISTIC 的坑 参见我的文档 确定性函数

3、字段设计的坑

a、时间类型声明为datetime 而非TIMESTAMP 类型

2038不远了,不要再埋坑,而且从5.6版本后MySQL优化了datetime占用的存储空间
详见我的文档 MySQL数据类型存储空间占用–日期型

b、varchar类型声明 注意长度

当修改字符宽度有两个情况不能online ddl
1、减少宽度
2、跨越255这个坎
看下面列子

drop table stt;
create table stt
( id int not null,
str1 char(10) not null,
str2 varchar(60) not null,
str3 varchar(70) not null,
primary key(id)
) engine = InnoDB default charset = utf8mb4 ROW_FORMAT=COMPACT ;


将str2列由60减少到50 是不能online ddl的 减少宽度不能INPLACE


将 str2列由60增加到64也是不能online ddl的 跨越255这个坎不能INPLACE


而将 str3列由80增加到84是能online ddl的

所以我们在声明varchar的时候 如果发现靠近255的时候 不妨加大一点点,为了以后在线变更能更快速。
怎么算空间占用,详细见我的文档 MySQL数据类型存储空间占用–字符串

4、字符集的声明

创建表的时候,尽量指定字符集并指定排序规则,列上也同理
这里看一个官方例子

CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

show create table t1;


可以看到c1列的排序规则是latin1_swedish_ci 而不是创建表指定的 latin1_danish_ci
官方原文档在此

5、关联表、主从表(父子表)

这种关系紧密的表的设计在常用查询字段一定要加上冗余,以减少关联

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论