数据库系列(一)基本知识

1 数据库设计PowerDesigner&三大范式
2 命名风格、主键选择、外键、存储过程
3 数据库事务和数据库锁
4 触发器、游标、视图、自定义函数、
5 字段类型、字段可空、统计字段、逻辑删除

一、数据库设计&三大范式

数据库设计步骤

  1、需求分析:大概明白需要存储什么

  2、概要设计阶段:E-R图,表-属性-关系表,解决多方的沟通问题

  3、详细设计,细致到表甚至字段

关系型数据库:面向对象的封装+关系

面向对象的思想来建表:

  一对一:人----身份证;垂直分表的时候;相同主键/外键

  一对多:订单----商品表;省-----市;公司-----员工;主外键表示

  多对多:用户----菜单;学生----课程;女神----备胎;关系表/中间/映射表

三大范式

三大范式只是建议,其实都可能被违背
为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库,必须满足一定的范式。
在实际开发中最为常见的设计范式有三个:

1.1.第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性,本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分,那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储,这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式,如下表所示。

 

1.2.第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。一般不要违背。

1.3.第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

用户---公司ID----公司名称(非直接相关)===》违背了第三范式,因为用户表中包含了公司名称。如果需要显示公司名称,用第三范式,查询会很慢,还要关联公司表,但是更麻烦,更新了公司表,还要用户更新表中的公司名称,这个可以用一个定时作业去解决。如果更新很少但是查询特别多,这个时候就可以去适当违背第三范式。

三大范式只是建议,就好像设计模式一样,其实都可能被违背,正确的设计会减少很多工作量。

二、命名风格、主键选择、外键、存储过程

2.1 命名风格:

大写字母的模块缩写,不要下划线(避免映射),英文单词,首字母大写拼音/汉字完全反对

2.2 主键:

主键只是唯一标识,主键一般对用户没有意义;主键最好单例,提升查询效率;
复合索引(mapping/几个外键组合),永不更新,数据不要变化;最好由计算机生成!
主键生成方案选择:
自增ID : 数据库自动增加,int/bigint,sqlserver默认聚集索引,空间小,可以有业务意义(比如大于1000w都是新用户)多库环境、不同环境的数据库id冲突; 订单连续,容易让同行推算出你的销量;
GUID: GUID程序生成的,全球唯一,插入数据库,不会错误的join,无限的,方便导入;空间大,没有自身含义,没有聚集索引(创建时间来聚集)

2.3 外键

2.4 存储过程

  存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户用过指定存储过程的名称并给出参数来执行。

  坏处:

    存储过程不太好维护;逻辑分散了;不好管理;全部数据库操作

    【少用】,大量复杂的逻辑、计算、数据传输,可以用,否则还是程序来完成

  好处:

    数据库编程(下订单---减少一个库存---写日志),迁移方便;预编译;减少数据的传输

三、数据库事务和数据库锁

3.1 数据库事务

  多条SQL作为一个整体提交给数据库系统,要么全部执行,要么全部取消,是一个不可分割的逻辑单元;两个SQL语句,一个下单成功,另外一个减少库存,事务保证一起成功或者一起失败。

  最简单的事务,就是 update A set name="123",tag=123 where id=123

  begin tran--rollback/commit;默认开启事务,就是跨域省略begin tran;显示开启事务,还有个开启全部事务。

ACID

  原子性:要么都成功,要么都失败

  一致性:事务执行完,数据都是正确的

  隔离性:两个事务同时操作一张表,B事务要么在A事务前完成,要么在A事务完成执行(锁表)

  持久性:数据提交后,就固化下来

3.2  数据库锁

  多用户同时访问一个数据资源:

    1、修改数据===》同时修改,一个改成张三,一个改成李四,总归有一个被覆盖

    2、不可重复读

    3、脏读/幻读===》要么读到的是更新前的,要么读到的是更新后的。正在更新的的数据,突然来查询了。。。。

  锁就是,保证访问同一资源时,有个先后顺序管理,处理并发问题。

  乐观锁:

    认为没有并发,读取数据---更新---保存,乐观锁就是没有锁。

    更新的时候做一个判断:(乐观锁的方式)

      1、时间戳:数据库增加一列TimeSpan,long,每次查询拿出来,更新时+1,保存时,判断是否大于数据库的值,要保证任何操作都按照这个规范,所以也有个漏洞,比如其他渠道更新。

      2、Version,版本号

      3、检查更新字段,一定是原始的数据上更新的

      4、检查所有的字段

    乐观锁的性能高!!!!!!!

  悲观锁:

    认为任何时候都可能在多线程并发,读数据的时候,别人恰好在修改;基于数据库的锁的机制来完成的

    共享锁,也叫S锁,读的锁:允许别的事务来读,但是不允许修改;读完就释放,锁定数据页;除非HoldLock一直锁定

    排它锁,也叫X锁,写的锁:准备写数据,不允许读也不允许写,比如select * from a update b,b表就不允许读。

    更新所,也叫U锁:先查询再更新

    行锁:where id = 2

    表锁:where 1 = 1

  怎么避免死锁呢?高并发的情况下,死锁是不可能避免的,只能尽量减少。

    1、不用锁,就不会死锁,乐观锁也可以,因为乐观锁没有锁的概念

    2、统一操作顺序,先A后B再C

    3、最小单元锁,锁里面的操作尽量减少

    4、避免事务中等待用户输入

    5、减少数据库并发

    6、分库/分表/分区

    7、降低事务级别----谨慎使用

    8、设置死锁时间----谨慎使用

  高并发的系统,死锁其实是不可避免的,只能尽量减少

    1、按照固有的顺序操作数据

    2、事务尽量的简短;更不要再事务期间搞什么等待;降低并发;事务里面耗时短;锁的隔离级别

四、 触发器、游标、视图、自定义函数

触发器

  触发器是一种特殊类型的存储过程,触发器主要是通过事件进行触发被自动调用执行的;尽量不要使用;事发前/事发后执行动作;数据库的自发动作,更新Company的name,会自动更新别的表名字,这个是完整动作;触发器泛滥,嵌套式。有很多个触发器的时候,排查问题都不好排查

游标

  游标实际上是一种能从多条数据记录的结果集中每次提取一条记录的机制;复杂的结果集,复杂的操作就可以用。但是我倒是从来没用到过游标,一般存储过程中会用到游标

视图

  视图就是一个虚拟的数据表,该数据表中的数据记录是有一条查询语句的查询结果得到的

函数

  不太推荐使用自定义函数,因为计算交给数据库,没法使用索引

五、字段类型、字段可空、统计字段、逻辑删除

字段类型

  这里有一个博客,介绍的还是蛮详细的:https://www.cnblogs.com/andy_tigger/archive/2011/08/21/2147745.html

字段可空

  数值类型的,不要可空,in查询  not in 查询,会没有结果,一般给个默认值;尽量非空,看情况(时间不适合默认值)

统计字段

  创建时间、创建人、最后更新时间、最后更新人、state、IsEnable

软删除

  假删除、逻辑删除,把状态改成删除,而不是物理删除。会造成一点空间浪费,但是可以保存数据备份。


原文链接:https://blog.csdn.net/weixin_43864900/article/details/104405988  https://www.cnblogs.com/taotaozhuanyong/archive/2019/09/21/11563714.html

 

posted @   明志德道  阅读(150)  评论(0编辑  收藏  举报
编辑推荐:
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
阅读排行:
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
点击右上角即可分享
微信分享提示