MySQL 分库分表实践

article/2025/8/22 2:30:42

文章目录

    • 一、为什么要分库分表
    • 二、库表太大产生的问题
    • 三、垂直拆分
      • 1. 垂直分库
      • 2. 垂直分表
    • 四、水平分表
      • 1. 配置水平分表
      • 2. 测试水平分表

一、为什么要分库分表

数据库架构演变

刚开始多数项目用单机数据库就够了，随着服务器流量越来越大，面对的请求也越来越多，我们做了数据库读写分离， 使用多个从库副本（Slave）负责读，使用主库（Master）负责写，master和slave通过主从复制实现数据同步更新，保持数据一致。slave 从库可以水平扩展，所以更多的读请求不成问题

但是当用户量级上升，写请求越来越多，怎么保证数据库的负载足够？增加一个Master是不能解决问题的， 因为数据要保存一致性，写操作需要2个master之间同步，相当于是重复了，而且架构设计更加复杂

这时需要用到分库分表（sharding），把库和表存放在不同的MySQL Server上，每台服务器可以均衡写请求的次数

二、库表太大产生的问题

  1. 单库太大：单库处理能力有限、所在服务器上的磁盘空间不足、遇到IO瓶颈，需要把单库切分成更多更小的库

  2. 单表太大：CRUD效率都很低，数据量太大导致索引文件过大，磁盘IO加载索引花费时间，导致查询超时。所以只用索引还是不行的，需要把单表切分成多个数据集更小的表。MyCat提供的分表算法都在rule.xml，可以根据不同的分表算法进行拆分，比如根据时间拆分、一致性哈希、直接用主键对分表的个数取模等

拆分策略

单个库太大，先考虑是表多还是数据多：

  • 如果因为表多而造成数据过多，则使用垂直拆分，即根据业务拆分成不同的库
  • 如果因为单张表的数据量太大，则使用水平拆分，即把表的数据按照某种规则（mycat/conf/rule.xml定义的分表算法）拆分成多张表

分库分表的原则应该是先考虑垂直拆分，再考虑水平拆分

三、垂直拆分

分库分表和读写分离可以共同进行

1. 垂直分库

server.xml

<user name="root">
<property name="password">123456</property>
<property name="schemas">USERDB1,USERDB2</property>
</user>

配置了USERDB1、USERDB2这两个逻辑库

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/"><!-- 逻辑数据库 --><schema name="USERDB1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" /> <!-- 两个逻辑库对应两个不同的数据节点 --><schema name="USERDB2" checkSQLschema="false" sqlMaxLimit="100"dataNode="dn2" /><!-- 存储节点 --><dataNode name="dn1" dataHost="node1" database="mytest1" />  <!-- 两个数据节点对应两个不同的物理机器 --><dataNode name="dn2" dataHost="node2" database="mytest2" />  <!-- USERDB1对应mytest1，USERDB2对应mytest2 --><!-- 数据库主机 --><dataHost name="node1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"><heartbeat>select user()</heartbeat><writeHost host="192.168.131.129" url="192.168.131.129:3306" user="root" password="123456" /></dataHost><dataHost name="node2" maxCon="1000" minCon="10" balance="0"writeType="0" dbType="mysql" dbDriver="native"><heartbeat>select user()</heartbeat><writeHost host="192.168.0.6" url="192.168.0.6:3306" user="root" password="123456" /></dataHost>
</mycat:schema>

两个逻辑库对应两个不同的数据节点，两个数据节点对应两个不同的物理机器

在这里插入图片描述

mytest1和mytest2分成了不同机器上的不同的库，各包含一部分表，它们原来是合在一块的，在一台机器上，现在做了垂直的拆分。
客户端就需要去连接不同的逻辑库了，根据业务操作不同的逻辑库

在这里插入图片描述
然后配置了两个写库，两台机器把库平分了，分担了原来单机的压力。分库伴随着分表，从业务上对表拆分

2. 垂直分表

垂直分表，基于列字段进行。一般是针对几百列的这种大表，也避免查询时，数据量太大造成的“跨页”问题。

一般是表中的字段较多，将不常用的， 数据较大，长度较长（比如text类型字段）的拆分到扩展表。访问频率较高的字段单独放在一张表

四、水平分表

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE、HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈，不建议采用

将单张表的数据切分到多个服务器上去，每个服务器具有一部分库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈

分库分表可以和主从复制同时进行，但不基于主从复制；读写分离才基于主从复制

1. 配置水平分表

server.xml

<user name="root"><property name="password">123456</property><property name="schemas">USERDB</property>
</user>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/"><!-- 逻辑数据库 --><schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100"><table name="user" dataNode="dn1" /> <!-- 这里的user和student都是实际存在的物理表名 --><table name="student" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long"/> <!-- student表按照id取模拆分 --></schema><!-- 存储节点 --><dataNode name="dn1" dataHost="node1" database="mytest1" /><dataNode name="dn2" dataHost="node2" database="mytest2" /><!-- 数据库主机 --><dataHost name="node1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"><heartbeat>select user()</heartbeat><writeHost host="192.168.131.129" url="192.168.131.129:3306" user="root" password="123456" /></dataHost><dataHost name="node2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"><heartbeat>select user()</heartbeat><writeHost host="192.168.0.6" url="192.168.0.6:3306" user="root" password="123456" /></dataHost>
</mycat:schema>

在这里插入图片描述

user表示一个普通的表，直接放在数据节点dn1上，放在一台机器上，这张表不用进行拆分

student表的primaryKey是id，根据id拆分，放在dn1和dn2上，最终这个表要分在两台机器上，在物理上分开了，但是在逻辑上还是一个，往哪张表里增加，在2台机器上查询然后如何合并这些操作都是由mycat完成的

拆分的规则是取模（mod - long)，每次插入用id模上存在的机器数（2）

此外还需要在rule.xml中配置以下拆分算法

找到算法mod-long，因为我们将逻辑表student分开映射到两台主机上，所以修改数据节点的数量为2
在这里插入图片描述

2. 测试水平分表

Linux主机
在这里插入图片描述

Windows主机
在这里插入图片描述

登录到mycat的8066端口
在这里插入图片描述
使用MyCat给user表插入两条数据
在这里插入图片描述
由于schema.xml配置文件中，逻辑表user只在Linux主机的mytest1库中存在，mycat操作的逻辑表user会影响Linux主机上的物理表，而不会影响Windows主机上的表。我们分别查看一下Linux和Windows主机的user表：
在这里插入图片描述
在这里插入图片描述
我们再通过MyCat给student表插入两条数据
在这里插入图片描述
我们知道schema.xml配置文件中，逻辑表student对应两台主机上的两个库mytest1、mytest2中的两张表，所以对逻辑表插入的两条数据，会实际影响到两张物理表（用id%机器数，决定插入到哪张物理表）。我们分别查看一下Linux和Windows主机的student表：
在这里插入图片描述
再通过MyCat插入id=3和id=4的数据，应该插入不同主机上的不同物理表
在这里插入图片描述
在这里插入图片描述
这就相当于把student表进行水平拆分了

通过MyCat查询的时候只需要正常输入就行，我们配置的是表拆分后放在这2个数据节点上，MyCat会根据配置在两个库上查询并进行数据合并
在这里插入图片描述


http://chatgpt.dhexx.cn/article/ig8DmmO8.shtml

相关文章

MySQL如何分库分表

MySQL如何分库分表

1. 我们为什么需要分库分表 在分库分表之前&#xff0c;就需要考虑为什么需要拆分。我们做一件事&#xff0c;肯定是有充分理由的。所以得想好分库分表的理由是什么。我们现在就从两个维度去思考它&#xff0c;为什么要分库&#xff1f;为什么要分表&#xff1f; 1.1 为什么要…
阅读更多...
MYSQL 之 分库分表

MYSQL 之 分库分表

分库分表 关系型数据库本身⽐较容易成为系统瓶颈&#xff0c;单机存储容量、连接数、处理能⼒都有限。当单表的数据量 达到2000W或100G以后&#xff0c;由于查询维度较多&#xff0c;即使添加从库、优化索引&#xff0c;做很多操作时性能仍下降严 重。此时就要考虑对其进⾏切分…
阅读更多...
MySQL-分库分表详解（一）

MySQL-分库分表详解（一）

♥️作者&#xff1a;小刘在C站 ♥️个人主页&#xff1a; 小刘主页 ♥️努力不一定有回报&#xff0c;但一定会有收获加油&#xff01;一起努力&#xff0c;共赴美好人生&#xff01; ♥️学习两年总结出的运维经验&#xff0c;以及思科模拟器全套网络实验教程。专栏&#xf…
阅读更多...
如何实现MYSQL分库分表

如何实现MYSQL分库分表

我们学习了在高并发下数据库的一种优化方案&#xff1a;读写分离&#xff0c;它就是依靠主从复制的技术使得数据库实现了数据复制为多份&#xff0c;增强了抵抗大量并发读请求的能力&#xff0c;提升了数据库的查询性能的同时&#xff0c;也提升了数据的安全性。当某一个数据库…
阅读更多...
MySQL 分库分表，写得太好了！

MySQL 分库分表，写得太好了！

https://www.toutiao.com/a6603492496779510276/?tt_frommobile_qq&utm_campaignclient_share&timestamp1549497188&appnews_article&utm_sourcemobile_qq&iid59568063679&utm_mediumtoutiao_android&group_id6603492496779510276 Mysql分库分表…
阅读更多...
MySQL数据库怎么进行分库分表？

MySQL数据库怎么进行分库分表？

▲ 点击上方“分布式实验室”关注公众号 回复“1”抽取纸质技术书 提起分库分表&#xff0c;对于大部分服务器开发来说&#xff0c;其实并不是一个新鲜的名词。随着业务的发展&#xff0c;我们表中的数据量会变的越来越大&#xff0c;字段也可能随着业务复杂度的升高而逐渐增多…
阅读更多...
超详细的mysql分库分表方案

超详细的mysql分库分表方案

我们都知道&#xff0c;随着业务量的增长&#xff0c;数据量也会随之增加&#xff0c;这个时候就需要关注业务大表&#xff0c;因为大表会影响查询性能&#xff0c;DDL变更时间很长&#xff0c;影响业务的可用性&#xff0c;同时导致从库延迟很大&#xff0c;如果业务做了读写分…
阅读更多...
Mysql分库分表实战（一）——一文搞懂Mysql数据库分库分表

Mysql分库分表实战（一）——一文搞懂Mysql数据库分库分表

由于业务需要&#xff0c;需要对Mysql数据库进行分库分表&#xff0c;故而最近一直在整理分库分表的相关知识&#xff0c;现手上的工作也告一段落了&#xff0c;抽空将自己最近的学习结果转化为博文&#xff0c;分享给大家&#xff0c;本博文打算做成一个系列的&#xff0c;首先…
阅读更多...
MySQL 常用分库分表方案，都在这里了！

MySQL 常用分库分表方案，都在这里了！

点击上方关注 “终端研发部” 设为“星标”&#xff0c;和你一起掌握更多数据库知识 转自&#xff1a;尜尜人物 www.cnblogs.com/littlecharacter/p/9342129.htm 一、数据库瓶颈 不管是IO瓶颈&#xff0c;还是CPU瓶颈&#xff0c;最终都会导致数据库的活跃连接数增加&#xff0…
阅读更多...
MySQL：互联网公司常用分库分表方案汇总

MySQL：互联网公司常用分库分表方案汇总

作者&#xff1a;尜尜人物 原文&#xff1a;cnblogs.com/littlecharacter/p/9342129.html 本文目录 一、数据库瓶颈 IO瓶颈CPU瓶颈 二、分库分表 水平分库水平分表垂直分库垂直分表 三、分库分表工具 四、分库分表步骤 五、分库分表问题 非partition key的查询问题非partition…
阅读更多...
MySQL分库分表

MySQL分库分表

1.分库分表产生的背景 采用单数据库存储存在以下的性能瓶颈&#xff1a; ①IO瓶颈&#xff1a;热点数据太多&#xff0c;数据库缓存不足&#xff0c;产生大量磁盘IO&#xff0c;效率较低。请求数据太多&#xff0c;带宽不够&#xff0c;网络IO瓶颈。 ②CPU瓶颈&#xff1a;排…
阅读更多...
MySQL-如何分库分表？一看就懂

MySQL-如何分库分表？一看就懂

一、为什么要分库分表 如果一个网站业务快速发展&#xff0c;那这个网站流量也会增加&#xff0c;数据的压力也会随之而来&#xff0c;比如电商系统来说双十一大促对订单数据压力很大&#xff0c;Tps十几万并发量&#xff0c;如果传统的架构&#xff08;一主多从&#xff09;&…
阅读更多...
黑盒测试是用什么软件,黑盒测试的主要方法和常用的工具有什么？

黑盒测试是用什么软件,黑盒测试的主要方法和常用的工具有什么？

黑盒测试是测试人员比较常用的一种测试方法&#xff0c;它主要是通过测试来检测每个功能是否都能正常使用的。黑盒测试的方法是有许多的&#xff0c;但有一些方法是比较主要的&#xff0c;比如边界值测试、等价类划分、决策表以及场景法等等。除了方法外&#xff0c;黑盒测试工…
阅读更多...
黑盒测试简介和常用方法

黑盒测试简介和常用方法

黑盒测试简介和常用方法 定义 黑盒测试俗称功能测试&#xff0c;它站在用户的角度上主要是对系统或软件的界面、功能上进行测试。它把程序当成不能打开的盒子&#xff0c;这样不用考虑系统内部的逻辑和内部特性&#xff0c;只需要在程序的外部接口上检查程序是否能按照软件设计…
阅读更多...
黑盒测试的测试方法

黑盒测试的测试方法

一般我们在做软件测试的时候，会遇到黑盒测试，白盒测试，我们今天主要说的是黑盒测试的 主要测试方法有那些。接下来就是干货了。 最常见的是 边界值 等价类 错误推测法 场景法 因果图法 判定表组成法 正交实验设计 下面是详细的解释： 前言：在期末考到来的时候复习…
阅读更多...
黑盒测试方法一

黑盒测试方法一

黑盒测试是一种基于证明功能需求和用户最终需求的测试方法&#xff0c;设计黑盒测试用例的方法有如下8种&#xff1a; 等价类划分法。 边界值分析法。 因果图法。 判定表驱动测试。 场景法。 功能图法。 错误推测法。 正交试验设计法。 在实际测试工作中&#xff0c;往往是综合…
阅读更多...
软件测试方法——黑盒测试九大用例设计方法

软件测试方法——黑盒测试九大用例设计方法

笔者&#xff1a;风起怨江南 出处&#xff1a;https://blog.csdn.net/JackMengJin 笔者原创&#xff0c;文章转载需注明&#xff0c;如果喜欢请点赞关注&#xff0c;感谢支持&#xff01; 导读&#xff1a;面试和工作必备的九大黑盒软件测试方法。 目录 黑盒测试九大用例设计…
阅读更多...
黑盒测试简介与其测试方法

黑盒测试简介与其测试方法

黑盒测试又叫功能测试、数据驱动测试或基于需求规格说明书的功能测试。该类测试注重于测试软件的功能性需求。把测试对象看作一个黑盒子&#xff0c;完全不考虑程序内部的逻辑结构和内部特性&#xff0c;只依据程序的《需求规格说明书》&#xff0c;检查程序的功能是否符合它的…
阅读更多...
简述软件黑盒测试的方法,简述什么是黑盒测试方法

简述软件黑盒测试的方法,简述什么是黑盒测试方法

黑盒(又叫功能测试、数据驱动测试)&#xff1a; 1.黑盒测试发现错误类型&#xff1a; 功能错误和遗漏 界面错误 数据库错误 性能错误 初始化和终止错误 2.黑盒测试&#xff1a;程序外部接口进行的 3.黑盒测试就是根据功能需求来设计测试用例&#xff0c;验证软件是否按照预期要…
阅读更多...
黑盒（功能）测试基本方法

黑盒（功能）测试基本方法

1、黑盒测试的概念 1、什么是黑盒测试 &#xff08;1&#xff09;黑盒测试又称功能测试、数据驱动测试或基于规格说明书的测试&#xff0c;是一种从用户观点出发的测试。 &#xff08;2&#xff09;测试人员把被测程序当作一个黑盒子。 2、黑盒测试主要测试的错误类型有 &…
阅读更多...
推荐文章

Copyright @ 2022~2023