数据库表名、字段名、索引名等都要命名规范,可读性高(一般要求用英文),让别人一看命名,就知道这个字段表示什么意思
user_name, user_age
表名,字段名必须使用小写字母或者数字,禁止使用数字开头,主键索引名为pk_字段名,唯一索引名为uk_ 字段名,普通索引名则为idx_字段名
设计表时,我们需要选择合适的字段类型,比如:
主键设计的话,最好不要与业务逻辑有所关联,有些业务上的字段,比如身份证,虽然是唯一的,一些开发者喜欢用它来做主键,但是不是很建议。主键最好是毫无意义的一串独立不重复的数字,比如uuid,又比如auto_increment自增的主键,或者是雪花算法生成的主键等等
数据库字段长度表示字符长度还是字节长度?
其实在mysql中,varchar和char类型的表示字符长度,而其他类型的表示的长度是都表示字节长度,比如char(10)表示的字符长度是10,而bigint(4)表示显示长度是4个字节,但是因为bigint实际长度是8个字节,所以bigint(4)的实际长度就是8个字节
字段高度一般为2的幂哈 username varchar(32)
什么是物理删除?什么是逻辑删除?
物理删除:把数据从硬盘中删除,可释放存储空间
逻辑删除:给数据添加一个字段,比如is_deleted,以标记该数据已经逻辑删除
物理删除就是执行delete语句
逻辑删除就是这样:
update t_user set is_deleted = 1 where user_id = '666'
如主键,create_time,modifed_time等
表必备一般来说,或具备这几个字段
id:主键,一个表必须得有主键,必须
create_time:创建时间,必须
modifed_time:修改时间,必须,更新记录时,需要更新他
version:数据记录的版本号,用于乐观锁,非必须
remark:数据记录备注:非必须
modified_by:修改人:非必须
creator:创建人:非必须
我们建表的时候,要牢记,一张表的字段不宜过多哈,一般尽量不要超过20个字段,笔者记得上个公司,有伙伴设计开户表,加了五十多个字段
如果一个表的字段过多,表中保存的数据可能就会很大,查询效率就会很低,因此。一张表不要涉及太多字段,如果业务需求,实在需要很多字段,可以把一张大的表,拆分成多张小的表,他们的主键相同即可
当表的字段数非常多时,可以将表分成两张表,一张作为条件查询表,一张作为详细内容表
如果没有特殊的理由,一般都建议将字段定义为not null
为什么呢?
首先,评估你的表数据量,如果你的表数据量只有一百几十行,就没必要加索引,否则设计表的时候,如果有查询条件的字段,一般就需要建立索引,但是索引也不能滥用
假设你新建一张用户表,如下
create Table t_user(
'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,
'modified_time' datetime not null,
primary key('id')
)engine = InnoDB DEFAULT CHARSET = utf-8
对于这张表,很可能会有根据user_id或者name查询用户信息,并且,user_id是唯一的。因此,你是可以给user_id加上唯一索引,name加上普通索引
create Table t_user(
'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,
'modified_time' datetime not null,
primary key('id'),
key 'idx_name' ('name') using btree,
unique key un_user_id (user_id)
)engine = InnoDB DEFAULT CHARSET = utf-8
什么数据库三范式(3NF),大家是否还有印象?
第一范式:对属性的原子性,要求属性具有原子性,不可再分解
第二范式:对记录的唯一性,要求记录有唯一标识,及实体的唯一性,即不存在部分依赖
第三范式:对字段的冗余性,要求任何字段不能由其他字段派生出来,他要求字段没有冗余,即不存在传递依赖
我们设计表及其字段之间的关系,应尽量满足第三范式,但是有时候,可以适当冗余,来提高效率,比如如下张表
商品名称 | 商品型号 | 单价 | 数量 | 总金额 |
---|---|---|---|---|
手机 | 华为 | 8000 | 5 | 40000 |
以上这张表存放商品信息的基本表,总金额这个字段的存在,表明该表的设计不满足第三范式,因为总金额可以由单价*数量得到,说明总金额是冗余字段,但是,增加总金额这个冗余字段,可以提高查询统计速度,这就是以空间换时间的方法。
当然这只是一个小例子,大家开发设计的时候,要结合具体业务!
如果库名,表名,字段名等属性含有保留字时,sql语句必须用反引号来引用属性名称,这将使得SQL语句书写,shell脚本中变量的转义等变的非常复杂
因此,我们一般避免使用mysql保留字,如select,interval,desc等
什么是外键呢?
外键,也叫foreign key,他是用于将两个表连接在一起的键。foreign key是一个表中的字段(或字段集合),他引用另一个表中的primary key,他是用来表征数据的一致性和完整性的
阿里的java规范也有这么一条
不得使用外,一切外键概念必须在应用层解决
我们为什么不推荐使用外键呢
建表是需要选择存储引擎的,我们一般都选择INNODB存储引擎,除非读写比率小于1%,才考虑使用MyISAM
有些小伙伴可能会有疑惑,不是还有MEMORY等其他存储引擎吗?什么时候使用它呢?其实其他存储引擎一般除了都建议在DBA的指导下使用。
我们来复习一下这MySQL这三种存储引擎的对比区别吧:
特性 | INNODB | MyISAM | MEMORY |
---|---|---|---|
事务安全 | 支持 | 无 | 无 |
存储限制 | 64TB | 有 | 有 |
空间使用 | 高 | 低 | 低 |
内存使用 | 高 | 低 | 高 |
插入数据速度 | 低 | 高 | 高 |
是否支持外键 | 支持 | 无 | 无 |
数据库库、表、开发程序等都需要统一字符集,通常中英文环境用utf8。
MySQL支持的字符集有utf8、utf8mb4、GBK、latin1等。
如果你设计的数据库字段是枚举类型的话,就需要在comment后面注释清楚每个枚举的意思,以便于维护
正例如下:
`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授权态 00:在线-授权态有效 01:下线-授权态失效 02:下线-主动退出 03:下线-在别处被登录'
反例:
`session_status` varchar(2) COLLATE utf8_bin NOT NULL COMMENT 'session授权态'
我们设计表的时候,一般都需要加通用时间的字段,如create_time、modified_time等等。那对于时间的类型,我们该如何选择呢?
对于MySQL来说,主要有date、datetime、time、timestamp 和 year。
推荐优先使用datetime类型来保存日期和时间,因为存储范围更大,且跟时区无关。
什么是存储过程
已预编译为一个可执行过程的一个或多个SQL语句。
什么是触发器
触发器,指一段代码,当触发某个事件时,自动执行这些代码。使用场景:
对于MYSQL来说,存储过程、触发器等还不是很成熟, 并没有完善的出错记录处理,不建议使用。
日常开发中,1对多的关系应该是非常常见的。比如一个班级有多个学生,一个部门有多个员工等等。这种的建表原则就是:在从表(N的这一方)创建一个字段,以字段作为外键指向主表(1的这一方)的主键。示意图如下:
学生表是多(N)的一方,会有个字段class_id保存班级表的主键。当然,一班不加外键约束哈,只是单纯保存这个关系而已。
有时候两张表存在N:N关系时,我们应该消除这种关系。通过增加第三张表,把N:N修改为两个 1:N。比如图书和读者,是一个典型的多对多的关系。一本书可以被多个读者借,一个读者又可以借多本书。我们就可以设计一个借书表,包含图书表的主键,以及读者的主键,以及借还标记等字段。
设计表的时候,我们尤其需要关注一些大字段,即占用较多存储空间的字段。比如用来记录用户评论的字段,又或者记录博客内容的字段,又或者保存合同数据的字段。如果直接把表字段设计成text类型的话,就会浪费存储空间,查询效率也不好。
在MySQl中,这种方式保存的设计方案,其实是不太合理的。这种非常大的数据,可以保存到mongodb中,然后,在业务表保存对应mongodb的id即可。
这种设计思想类似于,我们表字段保存图片时,为什么不是保存图片内容,而是直接保存图片url即可。
什么是分库分表呢?
分库:就是一个数据库分成多个数据库,部署到不同机器。
分表:就是一个数据库表分成多个表。
为什么需要分库分表: 数据量太大的话,SQL的查询就会变慢。如果一个查询SQL没命中索引,千百万数据量级别的表可能会拖垮整个数据库。即使SQL命中了索引,如果表的数据量超过一千万的话,查询也是会明显变慢的。这是因为索引一般是B+树结构,数据千万级别的话,B+树的高度会增高,查询就变慢啦。
分库分表主要有水平拆分、垂直拆分的说法,拆分策略有range范围、hash取模。而分库分表主要有这些问题:
最后的话,跟大家聊来一些写SQL的经验吧: