关系型数据库的设计

范式

关系

注意:根据阿里开发规范,不再设置数据库的外键,在应用层保证外键逻辑即可

数据库设计

1:1

1:n

设想学生-班级案例,若在班级中保存所有学生的主键,则表长不好预测,表的数据亢余。

所以是在多的一方保存一次1的一方的主键

m:n

在中间多添加一个“关系”实体。

在关系中存储双方的主键,并将组合的主键作为自己的主键(这样两条记录中保存的双方id不会同时相同)

转化成了两个一对多关系,其中关系是多个一方,保存双方的主键

orm

一对多

在1的一方定义一个列表,保存多个’n’的对象

在n的一方保存一个1的对象

“要能找到信息啊,要不然还得再去数据库查,麻烦”

查询的时候通常是需要所有信息的,单单在n的一方知道1的主键没啥用,,另外,即使用了复杂查询,也要有个能在内存中表示出1:n关系的对象来保存。分别取出1和n的n+1个对象,再在内存中读取键值判断关系不方便。

<mapper namespace="studentNamespace"><resultMap type="zhongfucheng2.Student" id="studentMap"><id property="id" column="sid"/><result property="name" column="sname"/></resultMap><!--查询选修的java学科有多少位学⽣--><!--由于我们只要查询学⽣的名字,⽽我们的实体studentMap可以封装学⽣的名字,那么我们返回
studentMap即可,并不需要再关联到学科表--><select id="findByGrade" parameterType="string" resultMap="studentMap">select s.sname,s.sid from zhongfucheng.students s,zhongfucheng.grades g
WHERE s.sgid=g.gid and g.gname=#{name};</select>
</mapper>

就是说光select学生表,把记录包装给grade的list里就行了。这个例子没有要查的学科表的信息

<select id="selectUsers" resultType="map">

上述语句只是简单地将所有的列映射到 HashMap 的键上,这由 resultType 属性指定。虽然在大部分情况下都够用,但是 HashMap 并不是一个很好的领域模型。你的程序更可能会使用 JavaBean 或 POJO(Plain Old Java Objects,普通老式 Java 对象)作为领域模型。MyBatis 对两者都提供了支持。看看下面这个 JavaBean:

高级结果映射

MyBatis 创建时的一个思想是:数据库不可能永远是你所想或所需的那个样子。 我们希望每个数据库都具备良好的第三范式或 BCNF 范式==?==,可惜它们并不都是那样。 如果能有一种数据库映射模式,完美适配所有的应用程序,那就太好了,但可惜也没有。 而 ResultMap 就是 MyBatis 对这个问题的答案。

result标签的extends:继承

举个例子,比如一对多,要左连接,因为分类一定要找到,无论有没有属性对应

为什么category7的也来了?难道是左连接,左边的数据查出来了,右边的没查出来也给个7?

但是不左连接,不带属性的直接就没有了,,

多对多前后端设计

多对多被拆成了两个一对多,到底是写一对多查询还是多对多查询呢?

如果关联表(如评论)需要单独页面列出来,就查关联表LIST,因为保存有另外两表的对象,所以另外俩也查出来了

ER图

关系:菱形

实体:矩形

属性:椭圆

  • why自动生成的不一样?

对于主键等有不同的叫法(主码等),想知道了临时再查

ER图优化笔记

er图

  • 多对多关联表不需要在ER图写明双方主键。一对多也不用写另一方的主键。等到设计数据库时自己实现
  • 关系类型(m,n)和属性不说明也可以不注明
  • 用户和分享项之间的关系太多了,可以考虑把m:n关系拆成一个实体,变成两个m:1。毕竟实体可以是抽象的

主键设计

在微服务、数据库迁移等情景下,自增id可能会出现重复、参照完整性缺失等问题。

如果只是为了唯一id,可以使用雪花算法、uuid。

  • uuid随机分布不好建立索引?

uuid缺点:

  • 太长了,占空间,索引麻烦、没有可读性

  • 为什么不重复?因为和硬件地址有关么?

mysql若设置为自增主键,主键值会不断增加,如果插入了比当前自增值大的主键,则会在最大值的基础上自增;

不论删除了主键值最大的记录,下一个自增的主键值还是在之前的最大值上自增;

如果改变表结构,先取消主键自增再设置主键自增,主键会从当前最大值开始自增;

和innonDB有关

连接

一次查询多表时需要连接。连接是讲多个表按条件生成一个连接表。然后在连接表上按查询条件查询。

⼀)内连接(等值连接):查询客户姓名,订单编号,订单价格

 select c.name,o.isbn,o.pricefrom customers c inner join orders owhere c.id = o.customers_id;

内连接(等值连接)只能查询出多张表中,连接字段相同的记录

⼆)外连接 outer join :按客户分组,查询每个客户的姓名和订单数

分为左外连接和右外连接

 ---------------------------------------------------左外连接:select c.name,count(o.isbn)from customers c left outer join orders oon c.id = o.customers_idgroup by c.name;---------------------------------------------------右外连接:select c.name,count(o.isbn)from orders o right outer join customers c on c.id = o.customers_idgroup by c.name;---------------------------------------------------注意:外连接既能查询出多张表中,连接字段相同的记录;⼜能根据⼀⽅,将另⼀⽅不符合相同记录
强⾏查询出来

连接方式的选择

在一次mybatis报错中学到的,之前只会无脑左连接。

左连接时,生成的连接表会有全部的左表的数据;

内连接只有符合连接条件的记录(行);

如果需要左表的全部数据,比如首页列出所有数据,就需要左连接;

如果只是查询符合连接条件的某一个或几个,用内连接;

在Myabtis中,如果只返回一个数据,确查询到了多个,就会报错。

如果只查一个数据就要用内连接。如果返回左表所有内容,会导致报错。

话说左右换个位置不久没区别了。

多表查询时同名字段/别名作用

多表查询时多个表中相同名字的字段可能会覆盖。尽量在设计时不要有同名表存在同名字段。

如果真的这么设计了,可以在查询时用as给字段起别名,并且修改orm框架的关联映射,将列名改为别名

给要返回给应用层的取别名,Sql里可以用表名.列名区分。应用层因为关联映射写的时候没别名,不会区分。

脏数据

范式

sql语句笔记

查询最后一条记录

可以通过order by、desc来对查询到的数据排序,用limit选取其中的部分

阿里开发规范要求数据库设计记录的创建时间和修改时间。如果有创建时间就很好办

last_insert_id()函数:

仅适用于设置自增主键时。获取最后一次插入的主键值。

如果查询时需要筛选条件,那么最后一次插入的记录可能不是筛选条件内的最后一条记录

top

SELECT TOP 1 * FROM table_name
SELECT TOP 1 * FROM user order by id desc; # 降序排列

limit当id超过1000就不适用了吧 ??

mysql默认排序

MyISAM 表

MySQL Select 默认排序是按照物理存储顺序显示的(不进行额外排序)。也就是说SELECT * FROM tbl – 会产生“表扫描”。如果表没有删除、替换、更新操作,记录会显示为插入的顺序。

•InnoDB 表

同样的情况,会按主键的顺序排列。

似乎是按索引排序,如果select的字段中有索引列(比如主键),就会自动按主键升序排序

所以说,不要依赖mysql默认的排序

多表查询、左连接

一对多,双表查询要连接一次

多对多,三表查询要连接两次,各自与中间关联表相连

多表查询时,记得给重名字段起别名。

中间关系表的东西不用select东西了

    <select id="selectForShareItemById" parameterType="String" resultMap="ForShareItemResult">select s.id, s.title, s.descripe, s.time, s.url, s.create_time, s.update_time, s.status, s.user_id,t.id as tid, t.tag_namefrom for_share_item sinner join for_item_tag it on s.id = it.item_idinner join for_tag t on it.tag_id = t.idwhere s.id = #{id}</select>

分库分表

原理

即使SQL命中了索引,如果表的数据量超过一千万的话,查询也是会明显变慢的。这是因为索引一般是B+树结构,数据千万级别的话,B+树的高度会增高,每高一层就要多去硬盘查一此索引。

实践

Mybatis-plus分库分表

Sharding-JDBC

逻辑删除

逻辑删除与唯一索引问题:由于数据并未物理删除而知识改变了逻辑删除位,可能造成无法插入同样信息问题

此时要想同时解决防重(幂等性)问题,可以加入分布式锁(高并发下会影响性能)

方案:加一张防重表,在防重表中增加商品表的name和model字段作为唯一索引。

例如:

CREATE TABLE `product_unique` (`id` bigint(20) NOT NULL COMMENT 'id',`name` varchar(130) DEFAULT NULL COMMENT '名称',`model` varchar(255)  NOT NULL COMMENT '规格',`user_id` bigint(20) unsigned NOT NULL COMMENT '创建用户id',`user_name` varchar(30)  NOT NULL COMMENT '创建用户名称',`create_date` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',PRIMARY KEY (`id`),UNIQUE KEY `ux_name_model` (`name`,`model`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品防重表';

其中表中的id可以用商品表的id,表中的name和model就是商品表的name和model,不过在这张防重表中增加了这两个字段的唯一索引。

在添加商品数据之前,先添加防重表。如果添加成功,则说明可以正常添加商品,如果添加失败,则说明有重复数据。

防重表添加失败,后续的业务处理,要根据实际业务需求而定。

如果业务上允许添加一批商品时,发现有重复的,直接抛异常,则可以提示用户:系统检测到重复的商品,请刷新页面重试。

例如:

try {transactionTemplate.execute((status) -> {productUniqueMapper.batchInsert(productUniqueList);productMapper.batchInsert(productList);return Boolean.TRUE;});
} catch(DuplicateKeyException e) {throw new BusinessException("系统检测到重复的商品,请刷新页面重试");
}

在批量插入数据时,如果出现了重复数据,捕获DuplicateKeyException异常,转换成BusinessException这样运行时的业务异常。

  • 好一手异常转换

还有一种业务场景,要求即使出现了重复的商品,也不抛异常,让业务流程也能够正常走下去。

例如:

try {transactionTemplate.execute((status) -> {productUniqueMapper.insert(productUnique);productMapper.insert(product);return Boolean.TRUE;});
} catch(DuplicateKeyException e) {product = productMapper.query(product);
}

在插入数据时,如果出现了重复数据,则捕获DuplicateKeyException,在catch代码块中再查询一次商品数据,将数据库已有的商品直接返回。

如果调用了同步添加商品的接口,这里非常关键的一点,是要返回已有数据的id,业务系统做后续操作,要拿这个id操作。

当然在执行execute之前,还是需要先查一下商品数据是否存在,如果已经存在,则直接返回已有数据,如果不存在,才执行execute方法。这一步千万不能少。

例如:

Product oldProduct = productMapper.query(product);
if(Objects.nonNull(oldProduct)) {return oldProduct;
}try {transactionTemplate.execute((status) -> {productUniqueMapper.insert(productUnique);productMapper.insert(product);return Boolean.TRUE;});
} catch(DuplicateKeyException e) {product = productMapper.query(product);
}
return product;

千万注意:防重表和添加商品的操作必须要在同一个事务中,否则会出问题。

顺便说一下,还需要对商品的删除功能做特殊处理一下,在逻辑删除商品表的同时,要物理删除防重表。用商品表id作为查询条件即可。

补充

(1)流水型数据

流水型数据是无状态的,多笔业务之间没有关联,每次业务过来的时候都会产生新的单据,比如交易流水、支付流水,只要能插入新单据就能完成业务,特点是后面的数据不依赖前面的数据,所有的数据按时间流水进入数据库。

(2)状态型数据

状态型数据是有状态的,多笔业务之间依赖于有状态的数据,而且要保证该数据的准确性,比如充值时必须要拿到原来的余额,才能支付成功。

(3)配置型数据

此类型数据数据量较小,而且结构简单,一般为静态数据,变化频率很低。

状态表

OLTP业务方向

能不拆就不拆读需求水平扩展


数据量为千万级,可能达到亿级或者更高

流水表

OLTP业务的历史记录

业务拆分,面向分布式存储设计


OLAP业务统计数据源

设计数据统计需求存储的分布式扩展


规范

记得看阿里巴巴

统一字符集,似乎字符集不同也可能导致多表查询时索引失效

MySQL数据库的事务隔离级别默认为RR(Repeatable-Read),建议初始化时统一设置为RC(Read-Committed),对于OLTP业务更适合。

(4)数据库中的表要合理规划,控制单表数据量,对于MySQL数据库来说,建议单表记录数控制在2000W以内。

(5)MySQL实例下,数据库、表数量尽可能少;数据库一般不超过50个,每个数据库下,数据表数量一般不超过500个(包括分区表)。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/78216.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

【SCSS】网格布局中的动画

效果 index.html <!DOCTYPE html> <html><head><title> Document </title><link type"text/css" rel"styleSheet" href"index.css" /></head><body><div class"container">&l…

BigDecimal

文章目录 BigDecimal 的用处BigDecimal 的大小比较BigDecimal 保留几位小数BigDecimal 的使用注意事项总结 BigDecimal 的用处 《阿里巴巴Java开发手册》中提到&#xff1a;浮点数之间的等值判断&#xff0c;基本数据类型不能用来比较&#xff0c;包装数据类型不能用 equals 来…

机器学习04-数据理解之数据可视化-(基于Pima数据集)

什么是数据可视化? 数据可视化是指通过图表、图形、地图等视觉元素将数据呈现出来的过程。它是将抽象的、复杂的数据转化为直观、易于理解的视觉表达的一种方法。数据可视化的目的是帮助人们更好地理解数据&#xff0c;从中发现模式、趋势、关联和异常&#xff0c;从而作出更明…

Jmeter-获取接口响应头(Response headers)信息进行关联

文章目录 Jmeter-获取接口响应头&#xff08;Response headers&#xff09;信息进行关联使用正则表达式提取器将Set-Cookie的值提取出来在其余接口中关联该提取信息运行查看关联是否成功 Jmeter-获取接口响应头&#xff08;Response headers&#xff09;信息进行关联 获取某一…

MATLAB | 如何绘制这样的描边散点图?

part.-1 前前言 最近略忙可能更新的内容会比较简单&#xff0c;见谅哇&#xff0c;今日更新内容&#xff1a; part.0 前言 看到gzhBYtools科研笔记(推荐大家可以去瞅瞅&#xff0c;有很多有意思的图形的R语言复现&#xff01;&#xff01;)做了这样一张图&#xff1a; 感觉很…

【网络基础实战之路】设计网络划分的实战详解

系列文章传送门&#xff1a; 【网络基础实战之路】设计网络划分的实战详解 【网络基础实战之路】一文弄懂TCP的三次握手与四次断开 【网络基础实战之路】基于MGRE多点协议的实战详解 【网络基础实战之路】基于OSPF协议建立两个MGRE网络的实验详解 PS&#xff1a;本要求基于…

Spring Cloud +UniApp 智慧工地云平台源码,智能监控和AI分析系统,危大工程管理、视频监控管理、项目人员管理、绿色施工管理

一套智慧工地云平台源码&#xff0c;PC管理端APP端平板端可视化数据大屏端源码 智慧工地可视化系统利用物联网、人工智能、云计算、大数据、移动互联网等新一代信息技术&#xff0c;通过工地中台、三维建模服务、视频AI分析服务等技术支撑&#xff0c;实现智慧工地高精度动态仿…

字节编码学习

字节编码学习 文章目录 字节编码学习01_字节与ASCII码表02_每个国家都有独特的码表03_国际化UTF-804_编码本和解码本不一致&#xff0c;乱码 01_字节与ASCII码表 public class Demo01 {public static void main(String[] args) {// 计算机的底层全部都是字节 ---- ----// 一个…

Python自动化实战之使用Pytest进行API测试详解

概要 每次手动测试API都需要重复输入相同的数据&#xff0c;而且还需要跑多个测试用例&#xff0c;十分繁琐和无聊。那么&#xff0c;有没有一种方法可以让你更高效地测试API呢&#xff1f;Pytest自动化测试&#xff01;今天&#xff0c;小编将向你介绍如何使用Pytest进行API自…

SpringMVC请求和响应

目录 1、简介 2、数据响应方式 2.1、页面跳转 2.1.1、直接返回字符串 2.1.2、ModelAndView 2.1.3、request域 2.2、回写数据 2.2.1、直接返回字符串 2.2.2、返回对象或集合 3、获得请求数据 3.1、基本类型参数 3.2、获得POJO类型参数 3.3、获得数组类型参数 3.4、…

墨觉Run Plus耳机真的很好嘛?南卡、韶音、墨觉哪个好

随着近年来蓝牙耳机市场的快速变化&#xff0c;耳机的样式也在不断演变&#xff0c;从入耳式发展到无需入耳的骨传导耳机。骨传导耳机因其可以通过骨传导技术进行声音传输&#xff0c;无需贴合耳道即可实现耳机功能&#xff0c;因此备受市场欢迎。尤其是近两年&#xff0c;骨传…

linuxARM裸机学习笔记(6)----UART串口通信和串口格式化函数移植实验

UART串口通信 协议介绍&#xff1a;串口通信协议_ft232和ch340是串口的哪种协议_夜路难行々的博客-CSDN博客 I.MX6U UART UART时钟源选择的是pll3_80m&#xff0c;然后在(bit5:0)设置分频值&#xff0c;设置为1分频 ADBR(bit14) &#xff1a;自动波特率检测使能位&#xff0c…

【C++】类和对象(上)

目录 1. 面向过程和面向对象初步认识 2. 类的引入 3. 类的定义 4. 类的访问限定符及封装 4.1 访问限定符 4.2 封装 5. 类的作用域 6. 类的实例化 7. 类对象模型 8. this指针 8.1 this指针的引出 8.2 this指针的特性 没有坚持的努力&#xff0c;本质上并没有多大意…

Git rebase和merge区别详解

文章目录 变基的基础用法变基过程中的冲突解决冲突后无法push问题更新变基后的代码更有趣的变基用法变基的风险用变基解决变基变基 vs 合并 此文在阅读前需要有一定的git命令基础&#xff0c;若基础尚未掌握&#xff0c;建议先阅读这篇文章Git命令播报详版 在 Git 中整合来自不…

封装动态SQL的插件

最近根据公司的业务需要封装了一个简单的动态SQL的插件&#xff0c;要求是允许用户在页面添加SQL的where条件&#xff0c;然后开发者只需要给某个接口写查询对应的表&#xff0c;参数全部由插件进行拼接完成。下面是最终实现&#xff1a; 开发人员只需要在接口写上下面的查询SQ…

JavaWeb项目工程结构介绍

介绍idea创建Web项目工程下的项目结构&#xff08;新建的web工程&#xff09; 了解目录大致作用 一级目录 :.idea、out、src、web、工程名.iml .idea目录&#xff1a;记录了IntelliJ IDEA 的配置目录&#xff0c;包含项目的配置信息、工程设置、构建配置等。它是用来存储项目…

C++ 派生类成员的标识与访问——作用域分辨符

在派生类中&#xff0c;成员可以按访问属性分为以下四种&#xff1a; &#xff08;1&#xff09;不可访问成员。这是从基类私有成员继承下来的&#xff0c;派生类或是建立派生类对象的模块都无法访问到它们&#xff0c;如果从派生类继续派生新类&#xff0c;也是无法访问的。 &…

Vue系列第七篇:Element UI之el-main,el-table,el-dialog,el-pagination,el-breadcrumb等控件使用

本篇实现主页面功能&#xff0c;包括主页面排版布局&#xff0c;学生管理模块实现&#xff0c;后台接口实现等功能。 目录 1.运行效果 1.1登录页面 1.2主页面 1.3学生管理 - 信息列表 1.4学生管理 - 信息管理 1.5学生管理 - 作业列表 1.6学生管理 - 作业管理 2.前端代码…

HDFS介绍

目录 ​编辑 一、HDFS基础 1.1 概述 1.2 HDFS的设计目标 1.2.1 硬件故障 1.2.2 流式数据访问 1.2.3 超大数据集 1.2.4 简单的一致性模型 1.2.5 移动计算而不是移动数据 1.2.6 跨异构硬件和软件平台的可移植性 1.3 基础概念 1.3.1 块&#xff08;Block&#xff09; 1.3.2 复制…

Spring集成Junit

目录 1、简介 2、Junit存在的问题 3、回顾Junit注解 4、集成步骤 4.1、导入坐标 4.2、Runwith 4.3、ContextConfiguration 4.4、Autowired 4.5、Test 4.6、代码 5、补充说明 5.1、Runwith 5.2、BlockJUnit4ClassRunner 5.3、没有配置Runwith ⭐作者介绍&#xff1…