mysql分库分表

最新推荐文章于 2026-01-07 14:05:42 发布

转载最新推荐文章于 2026-01-07 14:05:42 发布 · 57 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/rui28899/article/details/122595511

文章标签：

#mysql

<div id="article_content" class="article_content clearfix">
<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/kdoc_html_views-1a98987dfd.css">
<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-044f2cf1dc.css">

<div id="content_views" class="markdown_views prism-atom-one-light">
<svg xmlns="http://www.w3.org/2000/svg" style="display: none;">
<path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path>
</svg>
<h3><a name="t0"></a><a id="_0"></a>一、如何进行分库分表</h3>
<h6><a id="1_2"></a>1、概念：</h6>
将原本存储在单个数据库上的数据拆分到多个数据库中（分库），把原来存储在单张表的数据拆分到多张数据表中，实现数据切分，从而提生数据库操作性能。分库分表的实现方式分为两种：垂直切分，水平切分。
<h6><a id="2_6"></a>2、水平拆分：</h6>
将数据分散到多张表，涉及到分区键。
<ul><li>分库：每个库结构一样，数据不一样，没有交集。库多了可以缓解io和cpu压力。</li><li>分表：每个表结构一样，数据不一样，没有交集。表数量减少，可以提高sql执行效率，减轻cpu压力。</li></ul>
优缺点：
<ul><li>优点：
<ul><li>单库（表）的数据保持在一定的量（减少），有助于性能的提高。</li><li>拆分结构相同，程序改造较小</li></ul> </li><li>缺点：
<ul><li>数据扩容有难度，维护量大。</li><li>拆分规则很难抽象出来</li><li>分片事务的一致性的问题，部分业务无法关联。</li></ul> </li></ul>
<h6><a id="3_23"></a>3、垂直拆分：</h6>
将字段拆分为多张表，需要一定的重构。
<ul><li>分库：每个库结构，数据都不一样，所有库的并集为全量数据。</li><li>分表：每个表结构、数据不一样，至少有一列交集，用于关联数据，所有表的并集为全量数据。</li></ul>
优缺点：
<ul><li>优点：
<ul><li>拆分后业务清晰（专库专用按业务拆分）</li><li>数据维护简单、按业务不同放到不同机器上。</li></ul> </li><li>缺点：
<ul><li>单表数据量大，写和读的压力大</li><li>受某种业务来决定或者被限制。也就是说一个业务往往会影响到数据库的瓶颈（性能问题）。</li></ul> </li></ul>
<h3><a name="t1"></a><a id="_39"></a>二、分库分表开源框架</h3>
<h6><a id="1jdbc_41"></a>1、jdbc直连层</h6>
shardingshpere、tddl（淘宝）
<ul><li> 优点：快速性能高、支持跨数据库 </li><li> 缺点：不支持跨语言。 </li></ul>
<h6><a id="2proxy_49"></a>2、proxy代理层</h6>
mycat、mysql-proxy
<ul><li> 优点：需要网络、支持跨平台、跨语言 </li><li> 缺点：性能比jdbc低、不支持跨数据库 </li></ul>
<h3><a name="t2"></a><a id="_57"></a>三、分库分表存在的问题</h3>
<h6><a id="1_59"></a>1、分布式事务</h6>
ACID（原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性）
当我们在进行多个数据库CURD（增删改查）操作的时候，如果出现错误，那么事务在回滚时只支持一个数据库的回滚，而其他数据库则是会操作成功，那么就会出现脏数据的出现。数据一致性不会得到保证
<h6><a id="2id_65"></a>2、分布式id</h6>
在分表中存在，自增id会存在id重复的情况。
<h6><a id="3_join_69"></a>3、分布式查询 join</h6>
join查询存在数据组合问题。
<h6><a id="4_73"></a>4、多数据库源管理</h6>
<h4><a name="t3"></a><a id="springbootshareingshere_75"></a>四、springboot整合shareingshere</h4>
<h6><a id="1_77"></a>1、导入依赖</h6>
<pre data-index="0" class="set-code-show prettyprint"><code class="prism language-xml has-numbering" onclick="mdcp.copyCode(event)" style="position: unset;"><dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li></ul></pre>
<h6><a id="2_87"></a>2、配置</h6>
<pre data-index="1" class="set-code-hide prettyprint"><code class="prism language-yml has-numbering" onclick="mdcp.copyCode(event)" style="position: unset;">spring:
shardingsphere:
datasource:
names: master,slave
commoun:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/shop?serverTimezone=UTC&characterEncoding=utf8&useUnicode=tr&ueuseSSL=false
username: root
password: root
hikari:
# 连接池最小空闲连接数
minimum-idle: 5
# 连接池允许的最大连接数
maximum-pool-size: 10
# 等待连接池分配连接的最大时长（毫秒）
connection-timeout: 30000
master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/shop?serverTimezone=UTC&characterEncoding=utf8&useUnicode=tr&ueuseSSL=false
username: root
password: root
slave:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/shop?serverTimezone=UTC&characterEncoding=utf8&useUnicode=tr&ueuseSSL=false
username: root
password: root
props:
sql:
show: true
mybatis:
configuration:
# 数据库下划线转驼峰配置
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath*:/mybatis/mapper/*.xml
<div class="hljs-button {2}" data-title="复制"></div></code><div class="hide-preCode-box"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></div><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li><li style="color: rgb(153, 153, 153);">10</li><li style="color: rgb(153, 153, 153);">11</li><li style="color: rgb(153, 153, 153);">12</li><li style="color: rgb(153, 153, 153);">13</li><li style="color: rgb(153, 153, 153);">14</li><li style="color: rgb(153, 153, 153);">15</li><li style="color: rgb(153, 153, 153);">16</li><li style="color: rgb(153, 153, 153);">17</li><li style="color: rgb(153, 153, 153);">18</li><li style="color: rgb(153, 153, 153);">19</li><li style="color: rgb(153, 153, 153);">20</li><li style="color: rgb(153, 153, 153);">21</li><li style="color: rgb(153, 153, 153);">22</li><li style="color: rgb(153, 153, 153);">23</li><li style="color: rgb(153, 153, 153);">24</li><li style="color: rgb(153, 153, 153);">25</li><li style="color: rgb(153, 153, 153);">26</li><li style="color: rgb(153, 153, 153);">27</li><li style="color: rgb(153, 153, 153);">28</li><li style="color: rgb(153, 153, 153);">29</li><li style="color: rgb(153, 153, 153);">30</li><li style="color: rgb(153, 153, 153);">31</li><li style="color: rgb(153, 153, 153);">32</li><li style="color: rgb(153, 153, 153);">33</li><li style="color: rgb(153, 153, 153);">34</li><li style="color: rgb(153, 153, 153);">35</li><li style="color: rgb(153, 153, 153);">36</li><li style="color: rgb(153, 153, 153);">37</li><li style="color: rgb(153, 153, 153);">38</li></ul></pre>
<h6><a id="3_130"></a>3、分片配置</h6>
<pre data-index="2" class="set-code-hide prettyprint"><code class="prism language-yml has-numbering" onclick="mdcp.copyCode(event)" style="position: unset;"> sharding:
tables:
employee:
actualDataNodes: master.employee_$->{0..2}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: employee_$->{id % 2}
keyGenerator:
type: SNOWFLAKE
column: id
#进行分库配置按照id进行分库
binding-tables: employee
# databaseStrategy:
# inline:
# shardingColumn: id
# algorithmExpression: employee_$->{id % 2}
<div class="hljs-button {2}" data-title="复制"></div></code><div class="hide-preCode-box"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></div><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li><li style="color: rgb(153, 153, 153);">10</li><li style="color: rgb(153, 153, 153);">11</li><li style="color: rgb(153, 153, 153);">12</li><li style="color: rgb(153, 153, 153);">13</li><li style="color: rgb(153, 153, 153);">14</li><li style="color: rgb(153, 153, 153);">15</li><li style="color: rgb(153, 153, 153);">16</li><li style="color: rgb(153, 153, 153);">17</li></ul></pre>
<h6><a id="4_152"></a>4、读写分离与分片整合配置</h6>
<pre data-index="3" class="set-code-hide prettyprint"><code class="prism language-yml has-numbering" onclick="mdcp.copyCode(event)" style="position: unset;"> sharding:
binding-tables: employee
tables:
employee:
actualDataNodes: ms.employee_$->{0..2}
tableStrategy:
inline:
shardingColumn: d_id
algorithmExpression: employee_$->{d_id % 2}
keyGenerator:
type: SNOWFLAKE
column: d_id
default-table-strategy:
none:
default-key-generator:
type: SNOWFLAKE
column: d_id
default-data-source-name: master
master-slave-rules:
ms:
slave-data-source-names: slave
load-balance-algorithm-type: round_robin
master-data-source-name: master
<div class="hljs-button {2}" data-title="复制"></div></code><div class="hide-preCode-box"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></div><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li><li style="color: rgb(153, 153, 153);">10</li><li style="color: rgb(153, 153, 153);">11</li><li style="color: rgb(153, 153, 153);">12</li><li style="color: rgb(153, 153, 153);">13</li><li style="color: rgb(153, 153, 153);">14</li><li style="color: rgb(153, 153, 153);">15</li><li style="color: rgb(153, 153, 153);">16</li><li style="color: rgb(153, 153, 153);">17</li><li style="color: rgb(153, 153, 153);">18</li><li style="color: rgb(153, 153, 153);">19</li><li style="color: rgb(153, 153, 153);">20</li><li style="color: rgb(153, 153, 153);">21</li><li style="color: rgb(153, 153, 153);">22</li><li style="color: rgb(153, 153, 153);">23</li></ul></pre>
<h3><a name="t4"></a><a id="_180"></a>五、测试</h3>
<h6><a id="1_182"></a>1、测试查询</h6>
<pre data-index="4" class="set-code-show prettyprint"><code class="prism language-java has-numbering" onclick="mdcp.copyCode(event)" style="position: unset;">@GetMapping("select")
public List<Employee> insertDept(Employee employee) {
return employeeMapper.getEmp(employee);
}
<div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li></ul></pre>
<ul><li>数据库中进行水平分表。</li></ul>
<img src="https://img-blog.csdnimg.cn/ba1988c987454498aec4ba2c809286d4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA4YOm4a2E6qa_6aSY56yZ4KaP4oG1wrLCuuGthA==,size_20,color_FFFFFF,t_70,g_se,x_16" alt="[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zZmpyAfw-1642644484182)(E:\Typora文件存储\image\image-20211228160421572.png)]">
<ul><li>查询所有：</li></ul>
<pre data-index="5" class="set-code-show prettyprint"><code class="prism language-xml has-numbering" onclick="mdcp.copyCode(event)" style="position: unset;"><select id="getEmp" resultType="com.beans.Employee">
select *
from employee
</select>
<div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li></ul></pre>
<ul><li>查询在slave中进行查询</li></ul>
<img src="https://img-blog.csdnimg.cn/e528f1679aef4b1992f1aa2ca80a34d1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA4YOm4a2E6qa_6aSY56yZ4KaP4oG1wrLCuuGthA==,size_20,color_FFFFFF,t_70,g_se,x_16" alt="[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tYn4Tq6W-1642644484183)(E:\Typora文件存储\image\image-20211228160254893.png)]">
<h6><a id="2_206"></a>2、测试插入</h6>
<pre data-index="6" class="set-code-show prettyprint"><code class="prism language-java has-numbering" onclick="mdcp.copyCode(event)" style="position: unset;">@GetMapping("insert")
public Long insert(Employee employee) {
employee.setDId(12L);
employee.setGender(1);
employee.setDate(new Date());
employee.setEmail("123121312@12312");
employeeMapper.insertEmp(employee);
return employee.getId();
}
<div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li></ul></pre>
<ul><li>插入一条数据</li></ul>
<pre data-index="7" class="set-code-show prettyprint"><code class="prism language-xml has-numbering" onclick="mdcp.copyCode(event)" style="position: unset;"><insert id="insertEmp" useGeneratedKeys="true" keyProperty="id">
INSERT INTO employee(lastName, email, gender, d_id, date)
VALUES (#{lastName}, #{email}, #{gender}, #{dId}, #{date})
</insert>
<div class="hljs-button {2}" data-title="复制"></div></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li></ul></pre>
<ul><li>插入会在master进行 <img src="https://img-blog.csdnimg.cn/7a1b9b5dfc3b4c70b8ae5e445cc07ff0.png" alt="在这里插入图片描述"></li></ul>
</div><div data-report-view="{"mod":"1585297308_001","spm":"1001.2101.3001.6548","dest":"https://blog.youkuaiyun.com/rui28899/article/details/122595511","extend1":"pc","ab":"new"}"><div></div></div>
<link href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/markdown_views-f23dff6052.css" rel="stylesheet">
<link href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/style-c216769e99.css" rel="stylesheet">
</div>