共计 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
类型,最终数据会保留失败。
所以抉择一个适合的字段类型,还是十分重要的一件事件。
以下准则能够参考一下:
- 尽可能抉择占用存储空间小的字段类型,在满足失常业务需要的状况下,从小到大,往上选。
- 如果字符串长度固定,或者差异不大,能够抉择 char 类型。如果字符串长度差异较大,能够抉择 varchar 类型。
- 是否字段,能够抉择 bit 类型。
- 枚举字段,能够抉择 tinyint 类型。
- 主键字段,能够抉择 bigint 类型。
- 金额字段,能够抉择 decimal 类型。
- 工夫字段,能够抉择 timestamp 或 datetime 类型。
3. 字段长度
后面咱们曾经定义好了 字段名称
,抉择了适合的 字段类型
,接下来,须要重点关注的是 字段长度
了。
比方:varchar(20),biginit(20)等。
那么问题来了,varchar
代表的是 字节
长度,还是 字符
长度呢?
答:在 mysql 中除了 varchar
和char
是代表 字符
长度之外,其余的类型都是代表 字节
长度。
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 存储引擎没啥好说的。
次要有以下起因:
- 在 innodb 中,须要额定的空间存储 null 值,须要占用更多的空间。
- null 值可能会导致索引生效。
- 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。
而 datetime
和timestamp
类型更适宜咱们保留 日期和工夫
。
但它们有稍微区别。
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 等。
而 float
和double
可能会失落精度,因而举荐大家应用 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-8
和utf8mb4
。
其中 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_ci
、utf8mb4_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。