关于java12:聊聊数据库建表的15个小技巧

39次阅读

共计 7292 个字符,预计需要花费 19 分钟才能阅读完成。

前言

对于后端开发同学来说,拜访数据库,是代码中必不可少的一个环节。

零碎中收集到用户的外围数据,为了安全性,咱们个别会存储到数据库,比方:mysql,oracle 等。

后端开发的日常工作,须要一直的建库和建表,来满足业务需要。

通常状况下,建库的频率比建表要低很多,所以,咱们这篇文章次要探讨建表相干的内容。

如果咱们在建表的时候不留神细节,等前面零碎上线之后,表的保护老本变得十分高,而且很容易踩坑。

明天就跟大家一起聊聊,数据库建表的 15 个小技巧,心愿对你会有所帮忙

1. 名字

建表的时候,给 字段 索引 起个好名字,真的太重要了。

1.1 见名知意

名字就像 字段 索引 的一张脸,能够给人留下第一印象。

好的名字,长篇累牍,见名知意,让人情绪愉悦,可能进步沟通和保护老本。

坏的名字,模仿两可,不知所云。而且显得横七竖八,看得让人抓狂。

反例:

用户名称字段定义成:yong_hu_ming、用户_name、name、user_name_123456789

你看了可能会一脸懵逼,这是什么骚操作?

正例:

用户名称字段定义成:user_name

舒适揭示一下,名字也不宜过长,尽量管制在 30 个字符以内。

1.2 大小写

名字尽量都用 小写字母,因为从视觉上,小写字母更容易让人读懂。

反例:

字段名:PRODUCT_NAME、PRODUCT_name

全副大写,看起来有点不太直观。而一部分大写,一部分小写,让人看着更不爽。

正例:

字段名:product_name

名字还是应用全小写字母,看着更难受。

1.3 分隔符

很多时候,名字为了让人好了解,有可能会蕴含多个单词。

那么,多个单词间的 分隔符 该用什么呢?

反例:

字段名:productname、productName、product name、product@name

单词间没有分隔,或者单词间用驼峰标识,或者单词间用空格分隔,或者单词间用 @分隔,这几种形式都不太倡议。

正例:

字段名:product_name

强烈建议大家在单词间用 _ 分隔。

1.4 表名

对于表名,在长篇累牍,见名知意的根底之上,倡议带上 业务前缀

如果是订单相干的业务表,能够在表名后面加个前缀:order_

例如:order_pay、order_pay_detail 等。

如果是商品相干的业务表,能够在表名后面加个前缀:product_

例如:product_spu,product_sku 等。

这样做的益处是为了不便归类,把雷同业务的表,能够十分疾速的汇集到一起。

另外,还有有个益处是,如果哪天有非订单的业务,比方:金融业务,也须要建一个名字叫做 pay 的表,能够取名:finance_pay,就能十分轻松的辨别。

这样就不会呈现 同名表 的状况。

1.5 字段名称

字段名称 是开发人员施展空间最大,但也最容易产生凌乱的中央。

比方有些表,应用 flag 示意状态,另外的表用 status 示意状态。

能够对立一下,应用 status 示意状态。

如果一个表应用了另一个表的主键,能够在另一张表的名前面,加 _id_sys_no,例如:

在 product_sku 表中有个字段,是 product_spu 表的主键,这时候能够取名:product_spu_id 或 product_spu_sys_no。

还有创立工夫,能够对立成:create_time,批改工夫对立成:update_time。

删除状态固定为:delete_status。

其实还有很多公共字段,在不同的表之间,能够应用全局对立的命名规定,定义成雷同的名称,以便于大家好了解。

1.6 索引名

在数据库中,索引有很多种,包含:主键、一般索引、惟一索引、联结索引等。

每张表的主键只有一个,个别应用:id或者 sys_no 命名。

一般索引和联结索引,其实是一类。在建设该类索引时,能够加 ix_ 前缀,比方:ix_product_status。

惟一索引,能够加 ux_ 前缀,比方:ux_product_code。

2. 字段类型

在设计表时,咱们在抉择 字段类型 时,可施展空间很大。

工夫格局的数据有:date、datetime 和 timestamp 等等能够抉择。

字符类型的数据有:varchar、char、text 等能够抉择。

数字类型的数据有:int、bigint、smallint、tinyint 等能够抉择。

说实话,抉择很多,有时候是一件坏事,也可能是一件好事。

如何抉择一个 适合 的字段类型,变成了咱们不得不面对的问题。

如果字段类型选大了,比方:本来只有 1 -10 之间的 10 个数字,后果选了 bigint,它占8 个字节。

其实,1-10 之间的 10 个数字,每个数字 1 个字节就能保留,抉择 tinyint 更为适合。

这样会白白浪费 7 个字节的空间。

如果字段类型择小了,比方:一个 18 位的 id 字段,抉择了 int 类型,最终数据会保留失败。

所以抉择一个适合的字段类型,还是十分重要的一件事件。

以下准则能够参考一下:

  1. 尽可能抉择占用存储空间小的字段类型,在满足失常业务需要的状况下,从小到大,往上选。
  2. 如果字符串长度固定,或者差异不大,能够抉择 char 类型。如果字符串长度差异较大,能够抉择 varchar 类型。
  3. 是否字段,能够抉择 bit 类型。
  4. 枚举字段,能够抉择 tinyint 类型。
  5. 主键字段,能够抉择 bigint 类型。
  6. 金额字段,能够抉择 decimal 类型。
  7. 工夫字段,能够抉择 timestamp 或 datetime 类型。

3. 字段长度

后面咱们曾经定义好了 字段名称 ,抉择了适合的 字段类型 ,接下来,须要重点关注的是 字段长度 了。

比方:varchar(20),biginit(20)等。

那么问题来了,varchar代表的是 字节 长度,还是 字符 长度呢?

答:在 mysql 中除了 varcharchar是代表 字符 长度之外,其余的类型都是代表 字节 长度。

biginit(n) 这个 n 示意什么意思呢?

如果咱们定义的字段类型和长度是:bigint(4),bigint 理论长度是 8 个字节。

当初有个数据 a =1,a 显示 4 个字节,所以在不满 4 个字节时后面填充 0(前提是该字段设置了 zerofill 属性),比方:0001。

当满了 4 个字节时,比方当初数据是 a =123456,它会依照理论的长度显示,比方:123456。

但须要留神的是,有些 mysql 客户端即便满了 4 个字节,也可能只显示 4 个字节的内容,比方会显示成:1234。

所以 bigint(4),这里的 4 示意显示的长度为 4 个字节,理论长度还是占 8 个字节。

4. 字段个数

咱们在建表的时候,肯定要对 字段个数 做一些限度。

我之前见过有人创立的表,有几十个,甚至上百个字段,表中保留的数据十分大,查问效率很低。

如果真有这种状况,能够将一张 大表 拆成多张 小表,这几张表的主键雷同。

倡议每表的字段个数,不要超过 20 个。

5. 主键

在创立表时,肯定要创立 主键

因为主键自带了主键索引,相比于其余索引,主键索引的查问效率最高,因为它不须要回表。

此外,主键还是人造的 惟一索引,能够依据它来判重。

单个 数据库中,主键能够通过 AUTO_INCREMENT,设置成 主动增长 的。

但在 分布式 数据库中,特地是做了分库分表的业务库中,主键最好由内部算法(比方:雪花算法)生成,它可能保障生成的 id 是全局惟一的。

除此之外,主键倡议保留跟业务无关的值,缩小业务耦合性,不便今后的扩大。

不过我也见过,有些一对一的表关系,比方:用户表和用户扩大表,在保留数据时是一对一的关系。

这样,用户扩大表的主键,能够间接保留用户表的主键。

6. 存储引擎

mysql5.1 以前的版本,默认的存储引擎是 myslam,而mysql5.1 当前的版本,默认的存储引擎变成了innodb

之前咱们还在创立表时,还始终纠结要选哪种存储引擎?

myslam的索引和数据离开存储,而有利于查问,但它不反对事务和外键等性能。

innodb 虽说查问性能,稍强劲一点,但它反对事务和外键等,性能更弱小一些。

以前的倡议是:读多写少的表,用 myslam 存储引擎。而写多读多的表,用 innodb。

但虽说 mysql 对 innodb 存储引擎性能的一直优化,当初 myslam 和 innodb 查问性能相差曾经越来越小。

所以,倡议咱们在应用 mysql8 当前的版本时,间接应用默认的 innodb 存储引擎即可,无需额定批改存储引擎。

7. NOT NULL

在创立字段时,须要抉择该字段是否容许为NULL

咱们在定义字段时,应该尽可能明确该字段NOT NULL

为什么呢?

咱们次要以 innodb 存储引擎为例,myslam 存储引擎没啥好说的。

次要有以下起因:

  1. 在 innodb 中,须要额定的空间存储 null 值,须要占用更多的空间。
  2. null 值可能会导致索引生效。
  3. null 值只能用 is null 或者 is not null 判断,用 = 号 判断永远返回 false。

因而,倡议咱们在定义字段时,能定义成 NOT NULL,就定义成 NOT NULL。

但如果某个字段间接定义成 NOT NULL,万一有些中央忘了给该字段写值,就会 insert 不了数据。

这也算正当的状况。

但有一种状况是,零碎有新性能上线,新增了字段。上线时个别会先执行 sql 脚本,再部署代码。

因为老代码中,不会给新字段赋值,则 insert 数据时,也会报错。

由此,十分有必要给 NOT NULL 的字段设置默认值,特地是前面新增的字段。

例如:

alter table product_sku add column  brand_id int(10) not null default 0;

8. 外键

在 mysql 中,是存在 外键 的。

外键存在的次要作用是:保证数据的 一致性 完整性

例如:

create table class (id int(10) primary key auto_increment,
  cname varchar(15)
);

有个班级表 class。

而后有个 student 表:

create table student(id int(10) primary key auto_increment,
  name varchar(15) not null,
  gender varchar(10) not null,
  cid int,
  foreign key(cid) references class(id)
);

其中 student 表中的 cid 字段,保留的 class 表的 id,这时通过 foreign key 减少了一个外键。

这时,如果你间接通过 student 表的 id 删除数据,会报异样:

a foreign key constraint fails

必须要先删除 class 表对于的 cid 那条数据,再删除 student 表的数据才行,这样可能保证数据的一致性和完整性。

顺便说一句:只有存储引擎是 innodb 时,能力应用外键。

如果只有两张表的关联还好,但如果有十几张表都建了外键关联,每删除一次主表,都须要同步删除十几张子表,很显然性能会十分差。

因而,互联网零碎中,个别倡议不应用外键。因为这类零碎更多的是为了性能思考,宁肯就义一点数据一致性和完整性。

除了 外键 之外,存储过程 触发器 也不太倡议应用,他们都会影响性能。

9. 索引

在建表时,除了指定 主键索引 之外,还须要创立一些 一般索引

例如:

create table product_sku(id int(10) primary key auto_increment,
  spu_id int(10) not null,
  brand_id int(10) not null,
  name varchar(15) not null
);

在创立商品表时,应用 spu_id(商品组表)和 brand_id(品牌表)的 id。

像这类保留其余表 id 的状况,能够减少一般索引:

create table product_sku (id int(10) primary key auto_increment,
  spu_id int(10) not null,
  brand_id int(10) not null,
  name varchar(15) not null,
  KEY `ix_spu_id` (`spu_id`) USING BTREE,
  KEY `ix_brand_id` (`brand_id`) USING BTREE
);

前面查表的时候,效率更高。

但索引字段也不能建的太多,可能会影响保留数据的效率,因为索引须要额定的存储空间。

倡议单表的索引个数不要超过:5个。

如果在建表时,发现索引个数超过 5 个了,能够删除局部 一般索引 ,改成 联结索引

顺便说一句:在创立联结索引的时候,须要应用留神 最左匹配准则,不然,建的联结索引效率可能不高。

对于数据反复率十分高的字段,比方:状态,不倡议独自创立一般索引。因为即便加了索引,如果 mysql 发现 全表扫描 效率更高,可能会导致索引生效。

10. 工夫字段

工夫字段 的类型,咱们能够抉择的范畴还是比拟多的,目前 mysql 反对:date、datetime、timestamp、varchar 等。

varchar类型可能是为了跟接口保持一致,接口中的工夫类型是 String。

但如果哪天咱们要通过工夫范畴查问数据,效率会非常低,因为这种状况没法走索引。

date类型次要是为了保留 日期 ,比方:2020-08-20,不适宜保留 日期和工夫,比方:2020-08-20 12:12:20。

datetimetimestamp类型更适宜咱们保留 日期和工夫

但它们有稍微区别。

  • timestamp:用 4 个字节来保留数据,它的取值范畴为1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07。此外,它还跟时区无关。
  • datetime:用 8 个字节来保留数据,它的取值范畴为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。它跟时区无关。

优先举荐应用 datetime 类型保留日期和工夫,能够保留的工夫范畴更大一些。

舒适揭示一下,在给工夫字段设置默认值是,倡议不要设置成:0000-00-00 00:00:00,不然查问表时可能会因为转换不了,而间接报错。

11. 金额字段

mysql 中有多个字段能够示意浮点数:float、double、decimal 等。

floatdouble可能会失落精度,因而举荐大家应用 decimal 类型保留金额。

个别咱们是这样定义浮点数的:decimal(m,n)。

其中 n 是指 小数 的长度,而 m 是指 整数加小数 的总长度。

如果咱们定义的金额类型是这样的:decimal(10,2),则示意整数长度是 8 位,并且保留 2 位小数。

12. 惟一索引

惟一索引 在咱们理论工作中,应用频率相当高。

你能够给单个字段,加惟一索引,比方:组织机构 code。

也能够给多个字段,加一个联结的惟一索引,比方:分类编号、单位、规格等。

单个的惟一索引还好,但如果是联结的惟一索引,字段值呈现 null 时,则唯一性束缚可能会生效。

创立惟一索引时,相干字段肯定不能蕴含 null 值,否则唯一性会生效。

13. 字符集

mysql 中反对的 字符集 有很多,罕用的有:latin1、utf-8、utf8mb4、GBK 等。

这 4 种字符集状况如下:

latin1容易呈现乱码问题,在理论我的项目中应用比拟少。

GBK 反对中文,但不反对国内通用字符,在理论我的项目中应用也不多。

从目前来看,mysql 的字符集应用最多的还是:utf-8utf8mb4

其中 utf-8 占用 3 个字节,比 utf8mb4 的 4 个字节,占用更小的存储空间。

但 utf- 8 有个问题:即无奈存储 emoji 表情,因为 emoji 表情个别须要 4 个字节。

由此,应用 utf- 8 字符集,保留 emoji 表情时,数据库会间接报错。

所以,倡议在建表时字符集设置成:utf8mb4,会省去很多不必要的麻烦。

14. 排序规定

不晓得,你关注过没,在 mysql 中创立表时,有个 COLLATE 参数能够设置。

例如:

CREATE TABLE `order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(30) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_code` (`code`),
  KEY `un_code_name` (`code`,`name`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

它是用来设置 排序规定 的。

字符排序规定跟字符集无关,比方:字符集如果是 utf8mb4,则字符排序规定也是以:utf8mb4_ 结尾的,罕用的有:utf8mb4_general_ciutf8mb4_bin等。

其中 utf8mb4_general_ci 排序规定,对字母的大小写不敏感。说得更直白一点,就是不辨别大小写。

而 utf8mb4_bin 排序规定,对字符大小写敏感,也就是辨别大小写。

说实话,这一点还是十分重要的。

如果 order 表中当初有一条记录,name 的值是大写的 YOYO,但咱们用小写的 yoyo 去查,例如:

select * from order where name='yoyo';

如果字符排序规定是 utf8mb4_general_ci,则能够查出大写的 YOYO 的那条数据。

如果字符排序规定是 utf8mb4_bin,则查不进去。

由此,字符排序规定肯定要依据理论的业务场景抉择,否则容易呈现问题。

15. 大字段

咱们在创立表时,对一些非凡字段,要额定关注,比方:大字段,即占用较多存储空间的字段。

比方:用户的评论,这就属于一个大字段,但这个字段可长可短。

但个别会对评论的总长度做限度,比方:最多容许输出 500 个字符。

如果间接定义成 text 类型,可能会节约存储空间,所以倡议将这类字段定义成 varchar 类型的存储效率更高。

当然,我还见过更大的字段,即该字段间接保留合同数据。

一个合同可能会占 几 Mb

在 mysql 中保留这种数据,从零碎设计的角度来说,自身就不太正当。

像合同这种十分大的数据,能够保留到 mongodb 中,而后在 mysql 的业务表中,保留 mongodb 表的 id。

正文完
 0