当前位置:首页 > 数据库 > mysql的分库分表和表关系建立

mysql的分库分表和表关系建立

2022年11月09日 10:02:03数据库8

一、mysql分库分表

不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是,可用数据库连接少甚至无连接可用。当使用MySQL数据库的时候,单表超出了2000万数据量就会出现性能上的分水岭。并且物理服务器的CPU、内存、存储、连接数等资源有限,某个时段大量连接同时执行操作,会导致数据库在处理上遇到性能瓶颈。为了解决这个问题,行业先驱门充分发扬了分而治之的思想,对大表进行分割,然后实施更好的控制和管理,同时使用多台机器的CPU、内存、存储,提供更好的性能。而分而治之则有两种方式:垂直拆分水平拆分

  • IO瓶颈
    ​ 1:磁盘读IO瓶颈,热点数据太多,数据库缓存放不下,每次查询时会产生大量的IO,降低查询速度 -> 分库和垂直分表。
    ​ 2:网络IO瓶颈,请求的数据太多,网络带宽不够 -> 分库。

  • CPU瓶颈
    1:SQL问题,如SQL中包含join,group by,order by,非索引字段条件查询等,增加CPU运算的操作 -> SQL优化,建立合适的索引,在业务Service层进行业务计算。
    2:单表数据量太大,查询时扫描的行太多,SQL效率低,CPU率先出现瓶颈 -> 水平分表。

1、垂直切分(纵向切分):

垂直切分是对不同的表(或者Schema)进行切分,存储到不同的数据库(主机)之上。垂直切分,强调的是业务的拆分。一个数据库由多个表构成,每个表对应不同的业务,那么我们可以指按照业务的不同将表进行分类,并将其分布到不同的数据库上,这样就将数据分摊到了不同的库上面,做到专库专用。

1.1 垂直拆分的优点:

  • 解决业务系统层面的耦合,业务清晰
  • 与微服务的治理类似,也能对不同业务的数据进行分级管理、维护、监控、扩展等
  • 高并发场景下,垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

1.2 垂直拆分的缺点:

  • 部分表无法join,只能通过接口聚合方式解决,提升了开发的复杂度
  • 分布式事务处理复杂
  • 依然存在单表数据量过大的问题(需要水平切分)

1.3 垂直分库: 垂直分库就是根据业务耦合性,将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似,按业务分类进行独立划分。与"微服务治理"的做法相似,每个微服务使用单独的一个数据库。业务划分专库专用。

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

1.4 垂直分表: 把一个表的多个字段分别拆成多个表,一般按字段的冷热拆分,热字段一个表,冷字段一个表。从而提升了数据库性能。在字段很多的情况下(例如一个大表有100多个字段),通过"大表拆小表",更便于开发与维护,也能避免跨页问题。数据库是以行为单位将数据加载到内存中,这样拆分以后核心表大多是访问频率较高的字段,而且字段长度也都较短,因而可以加载更多数据到内存中,来增加查询的命中率,减少磁盘IO,以此来提升数据库性能。

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

2、水平切分(横向切分)解决单库单表数据量大的问题

水平切分是对同一个表中的数据进行切分,存储到不同的数据库(主机)之上。规则是根据表中数据的逻辑关系,按照某种条件拆分。水平切分,强调的是技术层面的拆分。是将其按照一定的逻辑规则将一个表中的数据分散到多个库中,在每个表中包含一部分数据,所有表加起来就是全量的数据。简单来说,我们可以将对数据的水平切分理解为按照数据行进行切分,就是将表中的某些行切分到一个数据库表中,而将其他行切分到其他数据库表中。当某张表数据量达到一定的程度的时候,MySQL单表出现2000万以上数据就会出现性能上的分水岭。此时发现没有办法根据业务规则再进行拆分了,就会导致单表单库上的读写性能出现瓶颈。此时就只能进行水平拆分了。

2.1 水平切分的优点:

  • 解决高并发时单库单库数据量过大的问题,提升系统稳定性和负载能力,够较好的应对高并发,同时可以将热点数据打散。
  • 水平扩展能无线扩展。不存在某个库某个表过大的情况。
  • 应用侧的改动较小,不需要根据业务来拆分。

2.2 水平切分的缺点:

  • 需要处理分布式事务的一致性问题。
  • 跨库的join关联查询性能较差。
  • 扩容的难度和维护量较大。

2.3 水平分库: 水平分库是把同一个表的数据按一定规则拆到不同的数据库中,每个库可以放在不同的服务器上。它带来的提升是:

  • 解决了单库大数据,高并发的性能瓶颈。
  • 提高了系统的稳定性及可用性。

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

2.4 水平分表: 假设当我们的订单表达到了5000万行记录的时候,非常影响数据库的读写效率,怎么办呢?我们可以考虑按照订单编号的进行rang分区,就是把订单编号在1-1000万的放在order1表中,将编号在1000万-2000万的放在order2中。**水平分表是在同一个数据库内,把同一个表的数据按一定规则拆到多个表中。库内的水平分表,解决了单一表数据量过大的问题,分出来的小表中只包含一部分数据,从而使得单个表的数据量变小,提高检索性能。**它带来的提升是:

  • 优化单一表数据量过大而产生的性能问题

  • 避免IO争抢并减少锁表的几率

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

如果数据库因为表太多而造成海量数据,并且项目的各项业务逻辑划分清晰、低耦合,那么规则简单明了、容易实施的垂直切分必是首选。如果数据库中的表不多,但单表的数据量很大、或数据热度很高,就选择水平切分。


二、应用扩展

垂直扩展:提升单机处理能力,通过向现有计算机中添加更多的电源(CPU,RAM)来进行扩展。例如:增加CPU核数如32核,升级更好的网卡如万兆,升级更好的硬盘如SSD,扩充硬盘容量如2T,扩充系统内存如128G;

优缺点: 在互联网业务发展非常迅猛的早期,如果预算不是问题,强烈建议使用“增强单机硬件性能”的方式提升系统并发能力,因为这个阶段,公司的战略往往是发展业务抢时间,而“增强单机硬件性能”往往是最快的方法。不管是提升单机硬件性能,还是提升单机架构性能,都有一个致命的不足:单机性能总是有极限的。所以互联网分布式架构设计高并发终极解决方案还是水平扩展。

水平扩展:通过向资源池中添加更多计算机来进行扩展。只要增加服务器数量,就能线性扩充系统性能。

优缺点: 易扩容,但是水平扩展对系统架构设计是有要求的,难点在于:如何在架构各层进行可水平扩展的设计、可扩展性。

服务层的水平扩展,是通过“服务连接池”实现的。站点层通过RPC-client调用下游的服务层RPC-server时,RPC-client中的连接池会建立与下游服务多个连接,当服务成为瓶颈的时候,只要增加服务器数量,新增服务部署,在RPC-client处建立新的下游服务连接,就能扩展服务层性能,做到理论上的无限高并发。如果需要优雅的进行服务层自动扩容,这里可能需要注册中心里服务自动发现功能的支持。

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

数据层的水平扩展:在数据量很大的情况下,数据层(缓存,数据库)涉及数据的水平扩展,将原本存储在一台服务器上的数据(缓存,数据库)水平拆分到不同服务器上去,以达到扩充系统性能的目的。


三、应用拆分

应用垂直拆分:对业务的不同进行分类,不同的业务划分到不同的应用和数据库中。这种拆分往往是根据系统的改造,将原来的功能模块按照更加细粒度的拆分成多个弱耦合的服务。

示例:例如,可以根据业务逻辑,将“电商项目”拆分成“订单项目”、“用户项目”和“秒杀项目”。显然这三个拆分后的项目,仍然可以作为独立的项目使用。像这种拆分的方法,就成为垂直拆分,每个拆分项都可以进行独立处理

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

应用水平拆分:将一个大的应用根据分层的概念拆分为几部分进行协助处理。可以将一个项目根据“三层架构”拆分成 表示层(jsp+servlet)、业务逻辑层(service)和数据访问层(dao);然后再分开部署:把表示层部署在服务器A上,把service和dao层部署在服务器B上,然后服务器A和服务器B之间通过dubbo等RPC进行进行整合

示例:最经典的就是将整个应用分层。数据库访问层和业务逻辑层拆分、网关层和业务逻辑层拆分等等。

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记


四 :一对多、多对的关系的处理

1、一对多关系建立

一对多关系处理:

通过学生和班级问题了解一对多:

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

​ 设计数据库表:只需在 学生表 中多添加一个班级号的ID;

注:在数据库中创建表时,还是通过添加主外键约束,避免删除数据时造成数据混乱!

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

2、多对多关系处理:

通过学生选课了解多对多问题的处理:

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

在多对多中在一个表中添加一个字段就行不通了,所以处理多对多表问题时,就要考虑建立关系表了

例:

学生表:mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记 课程表:mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记 关系表:mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

注:所以对于多对多表,通过关系表就建立起了两张表的联系!多对多表时建立主外键后,要先删除约束表内容再删除主表内容

mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记


五:级联更新 外键约束

外键的功能:设置外键的作用很明显,就是将两个表的数据产生关联,就比如个人信息表和部门表之间,部门表的部门id就可以作为一个外键存放于个人信息表中(特性什么的就不再赘述~),除此之外设定外键在父子表中的维护关系,可以达到一个类似于级联的操作,就是当父级表(主键作为外键的表)数据变化时,子表(拿着别的表的主键作为自己的外键的表)就会有相应的数据变化;外键的约束(外键的限制、外键对父子表数据的维护关系)
CASCADE、NO ACTION、RESTRICT、SET NULL这几项可以理解为是:外键对父子表之间数据关系维护(限制)级别;

|-- 父表:被拿主键作为外键的表;
|-- 子表:拿别的表的主键作为外键的表;

REFERENCES : 引用作用, 引用其它表中的字段
mysql的分库分表和表关系建立 _ JavaClub全栈架构师技术笔记

create table sc(
scid int UNSIGNED primary key auto_increment,
sid int UNSIGNED not null,
score varchar(20) default '0',
index (sid),   --外键必须加索引
FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)
TYPE=InnoDB charset=utf8;

作者:YY迪迪
来源链接:https://blog.csdn.net/u014618114/article/details/114752967

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。


本文链接:https://www.javaclub.cn/database/68707.html

分享给朋友:

“mysql的分库分表和表关系建立” 的相关文章

JDBC如何连接mysql数据库附详细步骤

JDBC如何连接mysql数据库附详细步骤

JDBC连接数据库在学习中是很重要的一个环节,今天给大家详细说明JDBC连接数据库需要的步骤 1.加载驱动 驱动包的下载地址 https://dev.mysql.com/downloads/connector/j/ 到时候要将这个加载到项目中然后输入...

Linux (centos8)安装 MySQL 8 数据库(图文详细教程)

Linux (centos8)安装 MySQL 8 数据库(图文详细教程)

我的公众号 今天2021年4月23日。我买了阿里云centos服务器,安装mysql8.0,做一笔记,以供大家使用。 本教程手把手教你如何在 Linux 安装 MySQL 数据库,以 CentOS 8为例。 1. 下载并...

cloudera-scm-server启动出现Error creating bean with name 'entityManagerFactoryBean'与HHH010003: JDBC Driver class not found: com.mysql.jdbc.Driver错误解决办法(图文详解)

cloudera-scm-server启动出现Error creating bean with name 'entityManagerFactoryBean'与HHH010003: JDBC Driver class not found: com.mysql.jdbc.Driver错误解决办法(图文详解)

     不多说,直接上干货!       问题详情 2017-07-31 22:19:40,342 INFO main:com.cloudera.server.cmf.Main: Starting SCM Serv...

mysql 查询1小时内

mysql 查询1小时内

由于项目的需要经常查询一个小时之内的数据,接下来吾爱编程为大家分享一下使用Mysql查询一个小时之内的数据的sql,有需要的小伙伴可以参考一下: 1、方法一: (1)、sql写法: SELECT * FROM 表名&...

mysql递归查询

  在Oracle 中我们知道有一个 Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但很遗憾,在MySQL的目前版本中还没有对应的功能。   在MySQL中如果...

MySQL查询数据库所有表名及其注释

1、查看Mysql 数据库 "ori_data"下所有表的表名、表注释及其数据量 SELECT  TABLE_NAME 表名,TABLE_COMMENT 表注释,TABLE_ROWS 数据量 FROM information_schema.tables WHERE...

mysql 查询或

想对一张表进行查询,满足任意一个条件即可,可以用union实现或查询。 id age gender 1 20 female 2 21 male 3 22 male  ...

mysql 查询表中前10条数据

mysql 查询表中前10(X)条数据 select * from user_dongtai LIMIT 0,10(X) 作者:Curry·DK 来源链接:https://blog.csdn.net/Wangdiankun/art...

mysql数据查询——复杂查询

《实验五 数据查询——复杂查询》实验说明 一、 适用课程:《数据库原理与应用》 实验学时:3 二、实验目的: (1) 掌握SELECT语句的语法格式; (2) 熟练运用SELECT语句进行多表连接查询; (3) 熟练运用SELECT语句进行各种复杂类型的...

Mysql查询用户最后一次登陆时间

Mysql查询用户最后一次登陆时间

类似的问题还有,学生得分最高的课程 数据库如下: 第一种方案: SELECT sid,max(add_time) from test GROUP BY sid 第二种方案 S...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。