18-02 数据库设计核心要点

于 2023-05-22 15:16:03 发布
阅读量765 收藏 1
点赞数
分类专栏： 架构设计 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_56709616/article/details/130768153
版权

概念结构设计

  • 通过对用户需求进行综合、归纳和抽象，形成独立于具体数据库管理系统的概念模型
  • 把需求分析阶段得到的应用需求，抽象成概念模型
  • 连接现实世界和信息世界的桥梁
  • 好的概念结构设计
    • 能真实、充分的反映现实世界
    • 要易于理解
    • 要易于修改

基本概念

  • 实体（Entity）
    客观存在并可相互区别的事物称为实体，可以是具体的人、事、物或抽象的概念
  • 属性（Attribute）
    实体所具有的某一特性称为属性，一个实体可以由若干属性来刻画
  • 关键字（key）
    唯一标识实体的属性集称为关键字（码），具有唯一性和最小性的特性
  • 域（Domain）
    属性的取值范围称为该属性的域
  • 实体类型（Entity Type）
    相同类型和性质的实体组成了实体类型，实体是实体类型的实例

E-R模型

  • 比较流行的概念模型，用E-R图来描述现实世界
  • 先分析实体之间的联系，再用E-R图表示出来

两个实体之间的联系

  • 一对一：对于实体集A中的每个实体来说，实体集B中至多有一个实体与之联系，反之亦然，则实体A和B有一对一联系
  • 一对多
  • 多对多

逻辑结构设计

  • 把E-R图转换为数据库系统支持的逻辑数据模型

常见的逻辑数据模型

  • 层次模型
  • 网状模型
  • 关系模型
  • 面向对象数据模型
关系模型
  • 术语
    • 关系（relation）：一个关系对应一张表
    • 元组（tuple）：表中的行
    • 属性（attribute）：表中的列
    • 属性名：属性的名称
    • 关键字（key）：用来唯一确认某个元组的属性或属性组，也叫码或键
    • 域（domain）：一组具有相同数据类型的值的集合，属性的取值范围来自某个域
    • 分量：元组中的某一个属性值
    • 关系模式：是对关系的描述，表示为：关系名（属性1，属性2，属性3）
    • 规范化：关系模型要求关系是规范化的。规范化的最基本要求是，关系中的每个分量必须是不可分的数据项。不符合这个要求的关系，就是非规范关系。

物理结构设计

确定数据的存储结构

  • 选择用什么数据库
  • 确定每个字段的数据类型
  • 如何记录日志
    eg：binlog、relaylog、undolog、slowlog…
  • 如何设计备份
  • 如何确定系统配置

确定数据的存取方法

  • 创建什么索引、怎样创建索引
    • B+Tree索引、Hash索引、R-Tree索引
    • 联合索引
  • 存取时间
  • 维护代价
  • 空间利用率

数据库加密设计

  • 敏感信息如何加密？
    • 在数据库层面处理
    • 在应用层面处理
    • 在传输层处理

在数据库层面处理

  • ENCODE()/DECODE()/DES_ENCRYPT/DES_DECRYPT…
    以上函数MySQL5.7废弃，MySQL8.0删除
  • AES_ENCRYPT(),AES_DECRYPT()
    AES_ENCRYPT(明文，密钥)，AES_DECRYPT(密文，秘钥)
创建表格
CREATE TABLE test_aes (
	id INT PRIMARY KEY auto_increment,
	column_varbinary VARBINARY(16),
	column_binary BINARY(16),
	column_blob BLOB(16),
	column_varchar_utf8 VARCHAR(64),
	column_varchar_latin VARCHAR(64) CHAR SET latin1
)
插入数据
INSERT INTO test_aes ( column_varbinary, 
					   column_binary, 
					   column_blob, 
					   column_varchar_utf8, 
					   column_varchar_latin )
VALUES
	(
		AES_ENCRYPT( 'varbinary', 'key_pass' ),
		AES_ENCRYPT( 'binary', 'key_pass' ),
		AES_ENCRYPT( 'blob', 'key_pass' ),
		AES_ENCRYPT( 'varchar_utf8', 'key_pass' ),
		AES_ENCRYPT( 'varchar_latin', 'key_pass' ) 
	)

在这里插入图片描述
结论1：添加数据操作执行失败，因为utf-8字符集的varchar字段无法存储加密后的数据

INSERT INTO test_aes ( column_varbinary, 
					   column_binary, 
					   column_blob, 
					   column_varchar_latin )
VALUES
	(
		AES_ENCRYPT( 'varbinary', 'key_pass' ),
		AES_ENCRYPT( 'binary', 'key_pass' ),
		AES_ENCRYPT( 'blob', 'key_pass' ),
		AES_ENCRYPT( 'varchar_latin', 'key_pass' ) 
	)

结论2：执行成功，latin1字符集的varchar字段可以存储加密后的数据，varbinary、binary、blob可以存储加密后的数据（基于二进制存储）

utf-8的varchar怎么存储加密数据？

  • 方案1：用hex()转16进制存储

    INSERT INTO test_aes (column_varchar_utf8)
VALUES (HEX(AES_ENCRYPT('varchar_hex','test_key')))

    SELECT AES_DECRYPT(UNHEX(column_varchar_utf8),'test_key') from test_aes

  • 方案2：转base64存储

    INSERT INTO test_aes (column_varchar_utf8) VALUES
(to_base64(AES_ENCRYPT('base64','key')))

    SELECT AES_DECRYPT(from_base64(column_varchar_utf8),'key') from test_aes

对于无法解密的数据，aes_decrypt会直接返回null

在传输层处理

  • SSL
    • 数据加密
    • 身份验证
    • 消息完整性验证
  • SSL连接时参数控制
    在这里插入图片描述
    在这里插入图片描述

事物隔离级别

ACID

  • 原子性（Atomicity）：事物是一个不可再分割的单位，其中的操作要么都发生，要么都不发生
  • 一致性（Consistency）：事物在开始和结束的时候，应该满足一致性约束
  • 隔离性（Isolation）：事物的执行不会被其他的事务干扰
  • 持久性（Durability）：事务一旦提交，对数据库的修改就会持久的保存在数据库中，不会被回滚

隔离级别

在这里插入图片描述

Read Uncommitted（读未提交）
  • 事务能够看到其他未提交事务的执行结果
  • 实际项目中很少使用，因为隔离性较差，而且性能和其他级别相比，提升不大
Read Committed（读取提交内容）
  • 一个事务只能看见其他事务已提交后的结果
  • 多数数据库系统的默认隔离级别（MySQL不是）
Repeatable Read（可重读）
  • 确保在同一事务中多次读取同一数据时，结果相同
  • MySQL默认的隔离级别
Serializable（串行化）
  • 通过强制事务排序，使之不可能发生冲突
  • 隔离级别最高，性能最差，用得很少

设置隔离级别

在这里插入图片描述

查看隔离级别

在这里插入图片描述

隔离级别可能造成的问题

  • 脏读：读到了其他事务尚未提交的数据
    未提交的数据意味着这条数据可能会回滚，也就是说，这条数据最终并不会存到数据库中（读到不一定会存在的数据）
  • 不可重复读：在一个事务中对同一条数据的两次查询结果不同
  • 幻读
    • 当读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再去操作该范围的数据行时，发现有新的“幻影”行
    • 类似于不可重复读，都是在一个事务周期内读的数据不一致，区别在于：幻读侧重于插入操作带来的影响，而不可重复读侧重于修改或删除带来的影响。
      在这里插入图片描述

数据库锁详解

InnoDB中的锁

共享排它锁
  • 共享锁（SLock）：允许持有该锁的事务读取一行数据
  • 排他锁（XLock）：允许持有该锁的事务删除或更新一行数据
  • 特性：
    • 行级锁
    • 如果一个事务持有行X的共享锁，那么其他事务也可以获得该行的共享锁
    • 如果一个事务持有行X的排它锁，那么其他事务必须等待这个事务释放后才能持有
    • 在这里插入图片描述
  • UPDATE、DELETE语句如何加锁？
    InnoDB会自动给涉及的数据集加排它锁
  • SELECT语句如何加锁？
    • 普通的SELECT语句，不会加锁
      sql select * from table where ...
    • 在这里插入图片描述
    • 在这里插入图片描述
意向锁（Intention Locks）
  • 未来某个时刻，事务要加共享锁/排它锁了，所以先提前声明意向
    • 意向共享锁（intention shared lock，简称IS）：表示事务有意向对表中的某些行加共享锁
    • 意向排它锁（intention exclusive lock，简称IX）：表示事务有意向对表中的某些行加排它锁
  • 特性
    • 表级锁
    • 事务在请求S锁和X锁前，需要先获得对应的IS、IX锁
    • 意向锁并不会阻塞全表扫描以外的任何请求
  • 为什么要引入意向锁？
    • 提高事务的锁检测性能
    • 共享排它锁，意向锁的兼容性
    • 在这里插入图片描述
记录锁（Record Locks）
  • 用来封锁索引记录，从而防止其他事务针对同一记录执行增删改操作
间隙锁（Gap Locks）
  • 在REPEATABLE-READ（可重复读）隔离级别下的锁机制，用来封锁索引里面的间隙，解决幻读问题。
  • 间隙：键值在条件范围内但不存在记录
    包括：索引记录之间的间隙，第一条索引记录之前的范围，最后一条索引记录之后的范围
    在这里插入图片描述
  • 间隙锁为什么能够解决幻读问题？
    • 在这里插入图片描述
    • 在这里插入图片描述
  • 间隙锁特性
    • 要有索引
    • 封锁的是索引里面的间隙，防止其他事务在间隙中插入数据
    • 解决幻读问题
临键锁（Next-Key Locks）
  • 记录锁和间隙锁的组合，封锁索引记录和索引中的间隙。会封锁记录本身以及索引记录之前的区间，即范围（上一条记录，记录本身]
  • 如果一个事务占用了索引记录R的共享排它锁，则其他事务不能再记录R之前的区间插入新的索引记录
  • InnoDB行锁默认用的就是临键锁
插入意向锁（Insert Intention Locks）
  • 由insert操作再插入行之前设置的一种间隙锁
  • 多个事务，在同一个索引、同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此
自增锁（AUTO-INC Locks）
  • 特殊的表级锁
  • 专门针对插入AUTO_INCREMENT类型的列的事务
    • 最简单的情况，如果一个事务正在往表中插入记录，其他事务的插入必须等待，以便第一个事务插入的行时连续的值
    • innodb_autoinc_lock_mode可用来调整该锁的算法

锁优化的几个建议

  • 尽量使用较低的隔离级别（实际项目中很少调整）
  • 尽量使用索引访问数据，使加锁更精确，减少锁冲突的机会
  • 选择合理的事务大小，小事务发生锁冲突的几率也更少
  • 除非必须，查询时不要显示加锁

阻塞与死锁

阻塞

  • 一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，就形成了阻塞
  • innodb_lock_wait_timeout控制等待时间，单位秒，默认50
  • 在这里插入图片描述

死锁

  • 是指多个进程/线程在运行过程中因争夺资源而造成的一种僵局，当进程/线程处于这种僵持状态时，若无外力作用，他们都将无法再向前推进
  • 在这里插入图片描述
如何避免死锁
  • 以固定的顺序访问表和行
    两个更新数据的事务，事务A更新数据的顺序为1，2；事务B更新数据的顺序为2，1，这样更容易造成死锁
  • 大事务拆小，大事务更倾向于死锁，无果业务允许，将大事务拆小
  • 在同一个事务中，尽可能一次锁定需要的所有资源，可减少死锁概率
  • 降低隔离级别（很少使用）
  • 用表级锁，表级锁不存在死锁问题（很少故意这么干）
    • 如果SQL没有使用索引，会自动用表级锁
    • 可以用如下语法强制加表级锁
    • 在这里插入图片描述

MVCC（Multi-Version Concurrency Control） 多版本并发控制

  • MVCC使得支持行锁的数据库引擎，不再单纯的使用行锁来进行数据库的并发控制，而是把数据库的行锁与行的多个版本结合起来，这样只需要很小的开销，即可实现非锁定读，大大提高数据库系统的并发性能。

隐藏字段

  • DB_TRX_ID（6字节）：用来记录最近一次对本记录插入或修改的事务ID
    删除操作也会视为一个update操作，只是会额外修改删除标记（DELETE BIT）
  • DB_ROLL_PTR（7字节）：指向当前行的undo log信息，也叫回滚指针
  • DB_ROW_ID（6字节）：随新行插入而单调递增的行ID
    如果当前表没有聚簇索引，InnoDB会用该字段作为聚簇索引；如果已存在聚簇索引，该字段则不作为聚簇索引

Undo log

  • 存储老版本数据，当一个事务需要读取行记录时，如果当前行记录不可见，则顺着undo log链找到满足其可见性条件的行记录版本
  • insert undo log：事务insert新纪录时产生的undo log
    只在事务回滚时需要，并且在事务提交后就可以立即丢弃
  • update undo log：事务进行delete和update操作时产生的undo log
    • 不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录时，对应的undo log才会被pruge线程删除
    • pruge线程：专门用来处理DELETE BIG是true的记录的线程

MVCC工作机制

  • 在这里插入图片描述
  • 在这里插入图片描述

快照读

  • 普通SELECT语句会使用快照读
    • REPEATABLE-READ级别：事务begin后，第一条SELECT语句会创建一个快照，读取满足可见性的数据，之后该事务会一直用这个快照，直到事务结束
    • READ-COMMITTED级别：事务begin后，每条SELECT语句都会重置快照（重新创建快照）读取当前满足可见性的数据

当前度

在这里插入图片描述

阳宝宝的向日葵
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
健身房信息管理系统数据库设计样本.doc
11-19
概念结构设计是数据库设计的核心部分。本系统的概念结构设计包括了局部 E-R 图和全局 E-R 图两个部分。局部 E-R 图展示了系统中的每个实体之间的关系，包括了健身馆、健身房、健身项目、教练、会员、会员卡、管理员...
数据库的设计关键点总结
天元白手的博客
03-28 2033
一、设计数据库的步骤： 1、需求分析阶段：分析客户的业务和数据处理需求。 2、概要设计阶段：绘制数据库的E-R图，用于在团队内部设计、设计人员和客户之间进行沟通，确认需求信息的正确性和完整性。 3、详细设计阶段：将E-R图转换为多张表，进行逻辑分析，确认各表的主外键，并应用数据库设计的三大范式（下面会讲解）进行审核，经过项目组开会讨论确定后，还需要根据项目的技术实现，团队开发能力以及项目的成本进行...
数据库设计的关键点
yx1166的博客
01-21 1647
总结一下数据库设计的关键点 E-R图。如果图没画好，可能会造成缺表、多表，表之间的联系紊乱之类的问题。所以E-R图是数据库设计的重中之重。 设计好每个表的关键字段。数据表是记录实体的信息，字段很多，但是影响力较大的字段就那么几个。比如，状态。其他的字段，影响比较小。 字段的数据类型。数据类型会影响到数据库查询的性能。 文件存储要放在文件系统（或专用文件数据库）。业务数据库只保留文件链接（文件提取码）即可。如果把文件也存入业务数据库，会影响业务数据库的性能，出现业务系统界面显示慢的问题。 ...
数据库设计重点总结
妖精小狗的博客
09-26 3016
为什么需要设计数据库： 糟糕的数据库设计： 数据冗余、存储空间浪费 数据更新和插入的异常 程序性能差   良好的数据库设计： 节省数据的存储空间 能够保证数据的完整性 方便进行数据库应用系统的开发   软件项目开发周期中数据库设计： 需求分析阶段： 分析客户的业务和数据处理需求 ①设计数据库的步骤 a. 收集信息 与该系统有关人员进行交流、座谈，充分了解用户需求，理解数...
数据库设计的基本原则和主要步骤
weixin_42560233的博客
11-20 3万+
数据库设计的基本原则： （1）把具有同一个主题的数据存储在一个数据表中，“一表一用”。 （2）尽量消除冗余，提高访问数据库的速度。 （3）一般要求数据库设计达到第三范式，多对多，最大限度消除了数据冗余、修改异常、插入异常、删除异常，基本满足关系规范化的要求。 （4）关系数据库中，各个数据表之间关系只能为一对一和一对多的关系。对于多对多的关系必须转换为一对多的关系来处理。 （5）设计数据表结构时，...
11 个重要的数据库设计规则
岁月静好
06-30 3112
简介 　　在您开始阅读这篇文章之前，我得明确地告诉您，我并不是一个数据库设计领域的大师。以下列出的 11 点是我对自己在平时项目实践和阅读中学习到的经验总结出来的个人见解。我个人认为它们对我的数据库设计提供了很大的帮助。实属一家之言，欢迎拍砖 : ) 　　我之所以写下这篇这么完整的文章是因为，很多开发者一参与到数据库设计，就会很自然地把 “三范式” 当作银弹一样来使用。他们往往认为遵循这个规范
商业银行信贷管理系统的数据库设计要点(1).doc
02-27
商业银行信贷管理系统的数据库设计要点 [摘 要] 信贷管理系统的数据库设计是信贷管理系统建设的重点之一,直接关系到应用系统的架构 、性能、安全等。本文将从系统的业务功能、性能需求方面结合3年来信贷管理系统实施...
《数据库系统原理》设计报告 基于MYSQL图书管理系统数据库设计
06-29
"数据库系统原理设计报告基于MYSQL图书管理系统数据库设计" 本报告主要介绍了数据库系统原理设计报告，基于MYSQL图书管理系统数据库设计。报告中涵盖了数据库系统原理的基本概念、数据库设计方法与步骤、E-R模型...
2023年软件设计师考试备考要点分析.doc
10-26
2. 数据结构：数据结构是软件设计师考试的核心内容之一，包括线性表、数组、广义表、树、图、查找算法和排序等。这部分的学习要吃透理论、适当加大习题量、最佳能结合上机，深化理解。 3. 操作系统知识：操作系统...
His查询系统数据库 系统要点.pdf
10-18
数据库设计要点： 1. 数据库表设计：His查询系统数据库系统要点中包含多个表，包括P_OFFICE、P_HOSPITAL、P_PUBLIC_LEECHDOM、P_LEECHDOM_INFORMATION、P_LEECHDOM_LIST等。每个表都有其特定的字段和数据类型，用于...
数据库设计的重要性和设计原则
热门推荐
鹏霄万里展雄飞
12-29 3万+
说起数据库设计，相信大家都明白怎么回事，但说起数据库设计的重要性，我想大家也只是停留在概念上而已，到底如何重要？怎么重要呢？今天就将我至今为止的理解向大家阐述下。 一个不良的数据库设计，必然会造成很多问题，轻则增减字段，重则系统无法运行。我先来说说数据库设计不合理的表现吧： 1. 与需求不符 因为这个原因造成的改动量往往是最大。如果进入编码阶段的话，很可能会直接让你崩溃掉。 2. 性能低下
数据库设计总结
zouhuixing的博客
04-08 2万+
对于一个系统，数据库的设计是非常重要的，数据库设计决定了以后数据好不好维护。后期需求好不好展。同时也决定了系统的性能。一个坏的数据库设计一个功能点的改动可能会设计多张表的改动。一不小心可能就会引起数据的不一致。为了解决这些问题。在数据库设计之初就要考虑这些问题。减少后期系统维护量。说了这么多数据库设计的重要性那么数据库设计应该考虑哪些问题呢？ 一、范式与反范式的设计 范式设计的目的是为了减少数...
数据库架构设计要点
简单生活
10-10 367
数据库架构设计要点 如何解决数据量大的问题 主要的解决思路是垂直拆分和水平拆分，如何进行拆分也需要和业务紧密绑定在一起，至于细节后面再写文章单独讨论。 水平拆分后，势必引入新的问题如何对数据进行路由，大体有2种思路 算：一致性hash、取余 查：新增路由服务，每次访问数据库之前多一次查询操作 高可用 常用的高可用方案就是冗余，数据库也不例外 读高可用 优点：读写分离，提升性能 缺点：数据一致性的问题 读写高可用方案一 优点：读写分离，提升性能 缺点： 写库也采用了冗余，但.
数据库设计建模（重点）
qq30211478的博客
04-20 1068
一、确定业务中的实体、属性和方法 1、通过了解熟悉业务流程，将业务流程中的事务抽象成类、属性和方法，通过编程逻辑把问题解决。 确定业务中的类：收集业务资料，罗列业务中的所有名词，通过“找名词”的方法，确定业务中的类，注意区分开类和属性 2、区分类和属性（重点） 区分类和属性的技巧：确定该名词是否有方法（即是否拥有动作）。 一般描述为xx的xx是属性。 对于类和方法的封装，要根据业务逻辑...
数据库设计的一些要点
weixin_47119687的博客
01-09 573
E-R图（Entity-Relationship） 实体关系图 数据库建模 三线图进行数据表设计 PowerDesigner PDMan 概念数据模型（Conceptual Data Model） 给客户看 逻辑数据模型（Logical Data Model） 给程序员看：体现了主外键关系 物理数据模型（Physical Data Model） 生成SQL语言 面向对象模型（Object-Oriented Model） 生成代码 多对多关联：需要一个表来维护，关系表分别创建外键与两张表关联 一对多，多对一
mysql数据库设计重点知识
weixin_42975115的博客
09-24 156
union :联合查询 作用：查询多表字段的相同字段 SELECT country, name FROM Websites WHERE country='CN' UNION ALL SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country; ## 排序 降序：SELECT * FROM user ORDER ...
数据库设计原则（金融行业）
eagle89的专栏
07-19 4965
一个好的数据结构设计或者说数据库设计，不仅能应对复杂的业务变化，更可以应对未来海量的数据扩容，同时数据结构逻辑清晰更方便业务人员去使用数据。     适用场景：互联网高并发、高吞吐、海量数据、强一致性要求高、mysql数据库、互联网金融业务。      一、数据库设计框架思维     1. 数据库设计第一原则，领域设计 + 恒等式设计 + 数据流转设计     注：这三个思想后面会单独写...
数据库表设计笔记(1)--电子商城核心表
为梦想而奋斗，无悔_一叶扁舟的点滴成长！
02-28 737
CREATE TABLE `tb_content` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `category_id` bigint(20) NOT NULL COMMENT '内容类目ID', `title` varchar(200) DEFAULT NULL COMMENT '内容标题', `sub_title` varchar(1
mysql的核心技术要点
最新发布
11-30
1. 数据库设计：MySQL支持多种数据类型，包括整型、浮点型、日期时间型、字符串型等，合理选择数据类型可以提高数据库的性能和效率。 2. SQL语言：MySQL使用SQL语言进行数据库操作，包括数据的增删改查、事务处理、...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值