https://blog.csdn.net/qq_36386908/article/details/104458385
https://www.cnblogs.com/huanjueshacha/p/5666405.html
作为一个开发人员,数据库表设计是理所当然的任务,那么,我们需要使用专业性的工具和专业性的知识来完成这项任务,把它做好,做精。
软件开发的步骤
大致分为:
- 需求分析
- 系统设计
- 编码实现
- 系统测试
- 运行维护
开始之前先了解一个概念:ER图
实体-关系图(Entity Relationship Diagram),也称为E-R图,提供了表示实体、属性和关系的方法,用来描述现实世界的概念模型。
构成E-R图的基本要素是实体、属性和关系
实体(Entity):实体用来表示具有相同特征和性质的事物(类似于java的类),实体由实体名和实体属性来表示。
属性(Attribute):实体所具有的某一特性,一个实体可以有若干个属性
关系(Relationship):实体彼此之间相互连接的方式称为关系。一般可分为以下 3 种类型:
一对一关系 (1 ∶ 1)
一对多关系 (1 ∶ N)
多对多关系 (M ∶ N)
may-be 和 must-be
在实体与实体之间的关系中,都会存在着may-be和must-be这俩种情况,例如:
系统中有顾客和订单俩个实体(1:N关系),一个顾客对应多个订单,一个订单对应一个顾客,而且一个顾客可以(may be)没有订单和他对应,一个订单一定(must be)会有顾客和它对应.
编程是我们将自己说的语言转换成计算机语言,而建模就是将实体的关系使用数据库实体(表)来展示出来,我们的业务依赖于这些实体正常运行,个人认为,充分理解实体的属性和对应关系是关键。这个实体是做什么的,它具备什么样的属性,有哪些其它的实体与它是有关联的,它们有着怎样的对应关系呢?
ER图中符号的表示
1)# : 唯一, 以后可能表示为主键
2)* : 非空
3)o : 可有可无
4)虚线: may be 顾客这边虚线,顾客可能没有订单
5)实线: must be 订单这边实线,订单一定是属于某个客户。
6)竖杠(|): 代表要强制在(|)一方建立一个联合主键,将对方ID拿过来做联合主键
7)伞状图标代表多的一方,不是伞状图标则代表一的一方
数据库设计
数据建模完成之后,可以把ER图转换成数据中的表
实体的名字转换为表的名字
实体的属性转换为表中的列
具有唯一特点的属性设置为表中的主键
根据实体之间的关系设置为表中某列为外键列(主外键关联)
注意:第四步主要是:实体关系—>表关系
设计关系数据库时,遵从不同的规范要求,才能设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:
第一范式(1NF)
第二范式(2NF)
第三范式(3NF)
巴斯-科德范式(BCNF)
第四范式(4NF)
第五范式(5NF,又称完美范式)
注:满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了
第一范式1NF:字段原子性
第一范式简单的说就是表中的字段是最小不可再分的,我们下边举个例子,我们看到以下一张用户表。里边的字段是不可再分的,这样就符合第一范式的原子性,可能有些朋友会疑问,这个地址还可以分成省份、城市、区/县三个字段,是的!如果是一个电商项目它需要再分,那么就不符合第一范式,所以具体还是看项目的需求,没有固定标准,在项目需求中它的设计已不可再分那么就符合第一范式!总结成一句话就是-将实体的属性分解到最小的粒度
第二范式2NF: 消除对主键的部分依赖
2NF的使用是需要满足1NF为前提,在表中添加一个业务字段,而主键不用来做业务处理,比如我们的商品表有商品id,商品id为商品的主键,但是需要创建一个商品编号列来专门处理业务,因为id太敏感,我们处理业务都是用商品编号来处理,比如展示商品时展示编号等等!
第三范式3NF:在2NF的基础上添加外键
3NF的使用必须满足2NF,要求表中的每一列只与主键直接相关而不是间接相关,(表中的每一列只能依赖于主键),比如下面的例子,订单表中有客户相关信息,在分离出客户表之后,订单表中只需要有一个用户id即可(外键),而不能有其他的客户信息。因为其他的客户信息直接关联于用户id,而不是直接与订单id直接相关。如下图所示:
分离之后:
数据类型
尽量使用可以正确存储数据的最小数据类型
更小的数据类型意味着更快,占用更少的磁盘,内存、缓存和处理时间
尽量使用整型表示字符串
因为字符集和校对规则,使处理字符比整型更复杂,比如:我们使用数据库内置的datetime类型存储时间而不是字符类型,我们使用整型存储ip而不是直接将ip字符串存到数据库中
尽可能使用not null
这个值是很烦人的,建字段时请尽量指定是否非空,NULL使得索引,统计,比较都变得更复杂,而且索引尽量不要创建到可以为null的字段上
字符串类型
VARCHAR是可变长字符串
比定长字符串(CHAR)更节省空间,仅使用必要的空间另外VARCHAR需要额外字节记录字符串长度(不同情况需要字节数不同)
CHAR类型是定长字符串
开发中基本很少用(一些公司甚至基本上不考虑这种类型了),注意:字符串长度定义不是字节数,是字符数
日期和时间类型
datetime
使用8字节存储空间,保存从1001年到9999年的秒数。与时区无关,默认情况下,Mysql以一种可排序的格式显示它的值,例如:"2018-10-14 22:30:08"
timestamp
只使用4字节存储,保存1970年1月1日午夜以来的秒数,依赖于系统时区,和UNIX时间戳相同,转换函数分别为FROM_UNIXTIME()和UNIX_TIMESTAMP(),可以设置根据当前时间戳更新,比如我们熟悉的update_time字段
整数类型
UNSIGNED
属性表示不允许负值,可以使得正数的上限提高一倍,比如tinyint+unsigned可以使原本的-128~127的范围变为0~255
tinyint
我们一般用它存储状态值而不要用int,如果是Boolean类型,那么tinyint(1)当值为1和0时,查询结果自动转为true和false,条件参数相应的也可以直接传入true和false即可
INT(11)
不会限制值的范围,只是规定了一些客户端工具用来显示的字符的个数,所以对于存储和计算来说INT(11)和INT(1)相同
IP地址
实际上是32位无符号整数,用INT存储,Mysql提供转换函数为INET_ATON()和INET_NTOA()
小数
decimal不会损失精度,存储空间会随数据的增大而增大。double占用固定空间,较大数的存储会损失精度,通常存金额用decimal(11,2),这表示整数部分和小数部分分别为9位和2位注意!,当然可以根据具体的金额大小选择长度,注意这时候对应的java中用BigDecimal类来处理运算时要仔细,因为加减法和比较跟平常不一样
引用的博客中还介绍了数据库引擎,这里不深入介绍了,关于使用整形存储IP地址的方法,参考这里
https://www.cnblogs.com/huanjueshacha/p/5666405.html
移位运算我也不会,明天学习一下规则,线程池只是种也要使用。
建表语句的语法网上一堆这里也不细说了
索引
已为客官备好,轻点哦https://blog.csdn.net/qq_36386908/article/details/104571358
索引数据结构在这在这https://blog.csdn.net/qq_36386908/article/details/104730786
MySQL读写分离
点一下就会https://blog.csdn.net/qq_36386908/article/details/105279214
MySQL分表分库
一样点一下就成https://blog.csdn.net/qq_36386908/article/details/105372751
SQL优化
这里列举出来一些用过的,看到的欢迎大家评论区补充讨论
1、查询尽量避免全表扫描,首先考虑在where、order by字段上添加索引
2、避免在where字段上使用NULL值,所以在设计表时尽量使用NOT NULL约束,有些数据会默认为NULL,可以设置默认值为0或者-1
3、避免在where子句中使用!=或<>操作符,Mysql只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE使用索引
4、避免在where中使用OR来连接条件,否则可能导致引擎放弃索引来执行全表扫描,可以使用UNION进行合并查询
select id from t where num = 30 union select id from t where num = 40;
5、尽量避免在where子句中进行函数或者表达式操作
6、最好不要使用select * from t,用具体的字段列表代替"*",不要返回用不到的任何字段
7、in 和 not in 也要慎用,否则会导致全表扫描,如
select id from t where num IN(1,2,3)如果是连续的值建议使用between and,select id from t where between 1 and 3;
8、select id from t where col like %a%;模糊查询左侧有%会导致全表检索,如果需要全文检索可以使用全文搜索引擎比如es,slor
9、limit offset rows关于分页查询,尽量保证不要出现大的offset,比如limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行,完全可以加一些条件过滤一下(完成筛选),而不应该使用limit跳过已查询到的数据。这是一个==offset做无用功==的问题。对应实际工程中,要避免出现大页码的情况,尽量引导用户做条件过滤