WM_分库分表的深入实战剖析v1.0 跨节点join、分页排序分表 io和cpu瓶颈一般有用看4 速

分库分表的深入实战剖析

内容大纲

分库分表概念

电商系统下订单性能瓶颈问题

分库分表原则剖析

&产生的问题剖析

电商系统亿级订单数据分库分表实战指导

一、分库分表概念

概念：

在数据爆炸的年代，单表数据达到千万级别，甚至过亿的量，都是很常见的情景。这时候再对数据库进

行操作就是非常吃力的事情了，select个半天都出不来数据，这时候业务已经难以维系。不得已，分库

分表提上日程，我们的目的很简单，减小数据库的压力，缩短表的操作时间。

1、分表：

分表是指在一个或者多个数据库实例内，将一张表拆分为多张表存储。一般来说，分表是因为该表需要

存储很庞大的记录数，如果将其堆积到一起，就会导致数据量过于庞大（一般 MySQL 的表是 5000 万

条记录左右）引发性能瓶颈。一般分表会按照某种算法进行拆分，如交易记录，可能按年份拆分:

2、分库：

分库是指将一套数据库的设计结构，部署到多个数据库实例的节点中去，在应用的时候，按照一定的方

法通过多个数据库实例节点访问数据。请注意，这里的数据库实例节点是一个逻辑概念，不是一个物理

概念，什么意思呢？简单地说，一个机器节点可以部署多个数据库实例节点，也可以一个机器节点只部

署一个数据库实例节点，所以机器节点不一定等于数据库节点。而机器节点是物理概念，是看得到的真

实的机器；数据库节点是逻辑概念，是看不到的东西。为了更好地说明分库的概念,如下图

Why分库分表：

1、前提：

移动互联网时代，海量的用户每天产生海量的数量，比如：

用户表

订单表

交易流水表

以支付宝用户为例，8亿；微信用户更是多大10亿。订单表更夸张，比如美团外卖，每天都是几千万的

订单。淘宝的历史订单总量应该是百亿，甚至是千亿级别，这些海量数据远不是一张表能Hold住的。事

实上MySQL单表可以存储10亿级数据，只是这时候性能比较差，业界公认MySQL单表容量在1KW以下

是最佳状态，因为这是它的BTREE索引树高在3~5之间。

既然一张表无法搞定，那么就想办法将数据放到多个地方，目前比较普遍的方案有3个：

分区

分库分表

NoSQL/NewSQL

说明：只分库，或者只分表，或者分库分表融合方案都统一认为是分库分表方案，因为分库，或

者分表只是一种特殊的分库分表而已。NoSQL比较具有代表性的是MongoDB，es。NewSQL比

较具有代表性的是TiDB。

2、Why Not 分区：

分区原理：

分区技术与分表技术很类似，只是分区技术属于数据库内部的技术，对于开发者来说，它逻辑上

仍旧是一张表，开发时不需要改变 SQL 表名。将一张表切分为多个物理区块

它的缺点很明显：很多的资源都受到单机的限制，例如连接数，网络吞吐等！虽然每个分区可以独立存

储，但是分区表的总入口还是一个MySQL示例。从而导致它的并发能力非常一般，远远达不到互联网高

并发的要求！

使用分区，你的业务必须具备两个特点：

数据不是海量(分区数有限，存储能力有限)

并发能力要求不高

3、Why Not NoSQL/NewSQL：

目前绝大部分公司的核心数据都是：以RDBMS存储为主，NoSQL/NewSQL存储为辅！互联网公司又

以MySQL为主，国企&银行等不差钱的企业以Oracle/DB2为主！NoSQL/NewSQL宣传的无论多牛逼，

就现在各大公司对它的定位，都是RDBMS的补充，而不是取而代之！

4、Why 分库分表：

首先目前互联网行业处理海量数据的通用方法：分库分表。

虽然大家都是采用分库分表方案来处理海量核心数据，但是还没有一个一统江湖的中间件，这里列举一

些有一定知名度的分库分表中间件：

阿里的TDDL,DRDS和cobar

开源社区的Sharding-jdbc（3.x已经更名为sharding-sphere）

民间组织的MyCAT

360的Atlas

备注：sharding-jdbc的作者张亮大神原来在当当，现在在京东金融。但是sharding-jdbc的版权

属于开源社区，不是公司的，也不是张亮个人的！

分库分表中间件归结类型

分库分表的中间件很多，但是可以归结为两大类型：

CLIENT模式

PROXY

模式

1、CLIENT

模式：

代表有阿里的TDDL，开源社区的sharding-jdbc（sharding-jdbc的3.x版本即sharding-sphere已经支持

了proxy模式）。架构如下：

2、PROXY模式：

代表有阿里的cobar，民间组织的MyCAT。架构如下：

维度

Sharding-JDBC

MyCAT

性能

高

中

应用场景限制

java应用

无

是否支持自定

义sharding路

由

是

最大支持

sharding路由

维度

分布式事务

标准事务，XA强一致事务，柔性事务

支持弱xa、支持XA分布式事

务(1.6.5)

限制

不支持子语句，不支持union和union all,不

支持批量插入，不支持distinct聚合

详见《MYCAT权威指

南》-5.6 MyCat目前存在的

限制

是否开源

是

3、小结

从以上两种模式可以看出sharding-jdbc作为一个组件集成在应用内，而mycat则作为一个独立的应用需

要单独部署。

但是，无论是CLIENT模式，还是PROXY模式。几个核心的步骤是一样的：SQL解析，重写，路由，执

行，结果归并。

client模式，架构简单，性能损耗比较小，运维成本低。

mycat的单机模式无法保证可靠性，一旦宕机则服务就变得不可用，你又不得不引入HAProxy来实现他

的高可用集群部署方案，为了解决HAProxy的高可用问题，有需要采用keepalived来实现。

二、电商系统下订单性能瓶颈问题

1、性能瓶颈的产生

1）、原大众点评的订单单表早就已经突破200G,由于查询维度较多，即使加了两个从库，优化索引，

仍然存在很多查询不理想的情况。去年大量抢购活动的开展，使数据库达到瓶颈，应用智能通过限速、

异步队列等对其进行保护；业务需求层出不穷，原有的订单模型很难满足业务需求，但是基于原订单表

的DDL又非常吃力，无法达到业务要求。

2）、订单表在电商项目中存储的数量是最多的，但是电商项目中使用

mysql数据库较多，那么mysql的

存储量最佳大概在

1000w条，那么当存储量大于1000w条的时候，查询订单就会性能很差。

3）、IO瓶颈

磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询是会产生大量的IO,降低查询速度 ->

分库操作和垂直分表操作

网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库操作

4）、CPU瓶颈

SQL问题，如SQL中包含join,group by ,order by ,非索引字段条件查询等，增加CPU运算的操作 -

>SQL优化，建立一个合适的索引，在业务中进行运算优化。

单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -->水平分表操作

2、性能瓶颈的破除

随着以上问题越来越突出，订单数据库的切分就愈发急迫了。本次切分，我们的目标是未来几年内不需

要担心订单容量的问题。

使用分库分表和SQL优化来解决这个订单性能瓶颈问题。

三、分库分表原则剖析&产生的问题剖析

1、分库分表原则

1）、能不分就不分

MySQL是关系型数据库，数据库表之间的关系从一定的角度上映射了业务逻辑。任何分库分表的行为都

会在某种程度上提升业务逻辑的复杂度，数据库除了承载数据的存储和访问外，协助业务更好的实现需

求和逻辑也是其重要工作之一。分库分表会带来数据的合并，查询或者更新条件的分离，事务的分离等

等多种后果，业务实现的复杂程度往往会翻倍或者指数级上升。所以，在分库分表之前，不要为分而

分，去做其他力所能及的事情，比如升级硬件，升级网络，升级数据库库版本，读写分离，负载均衡

等。所有分库分表的前提是，这些都已经尽力了。

2）、数据量太大，正常的运维不满足正常业务访问

对数据库的备份。如果单表或者单个实例太大，在做备份的时候需要大量的磁盘IO或者网络IO资源。例

如1T的数据，网络传输占用50MB的时候，需要20000秒才能传输完毕，在此整个过程中的维护风险都

是高于平时的。

对数据表的修改。如果某个表过大，对此表做DDL的时候，MySQL会锁住全表，这个时间可能很长，在

这段时间业务不能访问此表，影响甚大。

字段名称

字段类型

备注

bigint

用户的ID

name

varchar

用户名字

last_login_time

datetime

最近登录时间

personal_info

text

私人信息

xxxx

其他信息字段

整个表热点，数据访问和更新频繁，经常有锁等待，你又没有能力去修改源码，降低锁的粒度，那么只

会把其中的数据物理拆开，用空间换时间，变相降低访问压力。

3）、表设计不合理，需要对某些字段垂直拆分

举一个例子，如果一个用户表，在最初设计表的时候是这样的：

users表

这种设计是很常见的，但是：

情况:1：

你的业务中如果用户数从100W增长到10亿。你为了统计活跃用户，在每个人登录的时候都会记录一下

他的最近登录时间。并且用户活跃的很，不断的去update这个login_time字段值，那么这个表就会压力

很大。站在业务的角度上，最好的办法就是先把last_login_time字段拆分出去，我们暂且叫他

user_time。这样做的好处是业务的代码只有在用到这个字段的时候修改一下就行了。如果不这么做，

直接把users表水平切分了，那么所有访问users表的地方都要进行修改。

情况2：

personal_info这个字段本来没啥用，就是让用户注册的时候填一些个人爱好而已，基本不查询。一开

始的时候有没有无所谓，但是后来发现两个问题：一，这个字段占用了大量的空间，因为是text类型，

有很多人喜欢长篇大论地介绍自己。更糟糕的是二，不知道哪天哪个产品经理心血来潮，说允许个人信

息公开吧，以方便让大家更好的相互了解。那么在所有人猎奇窥私心理的影响下，对此字段的访问大幅

度增加。数据库压力瞬间扛不住了，这个时候，只好考虑对这个表进行垂直拆分。

4）、某些数据表出现了无穷增长

在目前项目中，各种的评论，消息，日志记录。这个增长不是跟人口成比例的，而是不可控的，例如微

博的feed的广播，我发一条消息，会扩散给很多很多人。虽然主体可能只存一份，但不排除一些索引或

者路由有这种存储需求。这个时候，增加存储，提升机器配置已经苍白无力了，水平切分是最佳实践。

拆分的标准很多，按用户的，按时间的，按用途的等等方式进行拆分。

5）、安全性和可用性的考虑

这个很容易理解，鸡蛋不要放在一个篮子里，我不希望我的数据库出问题，但我希望在出问题的时候不

要影响到100%的用户，这个影响的比例越少越好，那么，水平切分可以解决这个问题，把用户，库

存，订单等等本来同统一的资源切分掉，每个小的数据库实例承担一小部分业务，这样整体的可用性就

会提升。这对Qunar(去哪儿)这样的业务还是比较合适的，人与人之间，某些库存与库存之间，关联不

太大，可以做一些这样的切分。

6）、业务耦合性考虑

这个跟上面有点类似，主要是站在业务的层面上，我们的火车票业务和烤羊腿业务是完全无关的业务，

虽然每个业务的数据量可能不太大，放在一个MySQL实例中完全没问题，但是很可能烤羊腿业务的DBA

或者开发人员水平很差，动不动给你出一些幺蛾子，直接把数据库搞挂。这个时候，火车票业务的人员

虽然技术很优秀，工作也很努力，照样被老板打屁股。解决的办法很简单:惹不起，躲得起。

2、分库分表架构方案

1）、垂直切分

垂直切分常见有垂直分库和垂直分表两种。

A.垂直分库：

是根据数据库里面的数据库表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单

数据，那么垂直拆分可以吧用户数据放在用户库，把订单数据放到订单库。另外在“微服务”盛行的今天

已经非常普及，按照业务模块来划分不同的数据库，也是一种垂直拆分，而不是像早期一样将所有的数

据表都放在同一个数据库中。如下图：

以表为依据，按照业务归属不同，将不同的表拆分到不同的库中

注意事项：

1、每个库的结构都一样

2、每个库的数据也不一样，没有交集

3、所有库的并集是全部数据

使用垂直分库的场景：

当系统绝对并发量上来了，并且可以抽象出单独的业务模块的情况下使用垂直分库方案。

B.垂直分表

在日常开发和设计中比较常见，通俗的说法叫做“大表拆小表”。拆分是基于关系型数据库中的“列”（字

段）进行的。通常情况，某个表中的字段比较多，可以新建立一张"扩展表"，将不经常使用或者长度较

大的字段拆分出去放到“扩展表”中，如下图所示：

以字段为依据，按照字段的活跃性，将表中字段拆到不同的表中（主表和扩展表）。

说明：拆分字段的操作建议在数据库设计阶段就做好。如果是在发展过程中拆分，则需要改写以

前的查询语句，会额外带来一定的成本和风险，建议谨慎。

注意事项：

1、每个表的结构不一样

2、每个表的数据也不一样，一般来说，每个表的字段只有有一列交集，一般是主键，用于关联数

据

3、所有表的并集是全部数据

使用垂直分表的场景：

当系统绝对并发量没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行

数据所需要的存储空间较大，以致于数据库缓存的数据行减少，查询时回去读磁盘数据产生大量随机读

IO,产生IO瓶颈的时候使用垂直分表。

C.垂直拆分的优点和缺点：

优点：

拆分后业务清晰，拆分规则明确

系统之间进行整合或扩展很容易

按照成本，应用的等级，应用的类型等将表放在不同的机器上，便于管理

便于实现动静分离，冷热分离的数据库表的设计模式

数据维护简单。

缺点：

主键出现冗余，需要管理冗余列。

会引起表连接JOIN操作(增加CPU开销)可以通过在业务服务器上进行join来减少数据库压力。

依然存在单表数据量过大的问题(需要水平拆分)。

事务处理复杂。

2）、水平切分

水平切分是通过某种策略将数据分片来存储，分为水平分表和水平分库两部分，每片数据会分散到不同

的MySQL表或者库中，达到分布式的效果，能够支持非常大的数据量。

A.水平分库

以字段为依据，按照一定策略(hash、range等)，将一个库中的数据拆分到多个库中。

注意事项：

1、每个库的结构都一样

2、每个库中的数据不一样，没有交集

3、所有库的数据并集是全部数据

使用水平分库的场景：

当系统绝对并发量上来了，分表难以从根本上解决问题，并且还没有明显的业务归属进行抽取业务的情

况下使用水平分库方案。

B.水平分表

以字段为依据，按照一定策略(hash,range等)，将一个表中的数据拆分到多个表中。

注意事项:

1、每个表的结构都一样

2、每个表的数据不一样，没有交集，所有表的并集是全部数据

使用水平分表的场景：

当系统绝对并发量没有上来，只是单表的数据量太多，影响SQL效率，加重了CPU负担，以致于成为瓶

颈的情况下使用水平分表方案。

C.水平拆分的优点和缺点：

优点：

单库单表的数据保持在一定的量级，有助于性能的提高

切分的表结构相同，应用层改造较少，只需要增加路由规则即可

提高了系统的稳定性和负载能力

缺点：

切分后，数据是分散的，很难利用数据库的Join操作，跨库Join性能较差

拆分规则难以抽象

分片事务的一致性难以解决

数据多次拓展难度跟维护量极

3）、小结

垂直分库和水平分库的区别

垂直分库是按照业务模块的不同进行拆分

水平分库是按照一定的策略进行拆分，达到把一个库中的数据进行拆分到不同的数据库中。

垂直分表和水平分表的区别

垂直分表是按照表中的冷热数据(活跃数据)进行拆分的，并且拆分完了之后，一般是分主表和

拓展表，那么两张表的结构不一样，两张表的关联靠id来支持。

水平分表是按照一定的策略进行拆分，目的是把单表的庞大的数量按照一定的策略分摊到拆

分之后的小表中，拆分之后，每张表的结构是一样的，但是数据不一样。

3、分库分表产生的问题剖析

分库分表能有效的缓解单机单库和单表带来的性能瓶颈和压力，突破网络IO、硬件资源、连接数的瓶

颈，同时也带来一些问题，问题如下：

1）、事务一致性问题

分布式事务：

当更新内容同时存在于不同库找那个，不可避免会带来跨库事务问题。跨分片事务也是分布式事务，没

有简单的方案，一般可使用“XA 协议”和“两阶段提交”处理。

分布式事务能最大限度保证了数据库操作的原子性。但在提交事务时需要协调多个节点，推后了提交事

务的时间点，延长了事务的执行时间，导致事务在访问共享资源时发生冲突或死锁的概率增高。

随着数据库节点的增多，这种趋势会越来越严重，从而成为系统在数据库层面上水平扩展的枷锁。

最终一致性：

对于那些性能要求很高，但对一致性要求不高的系统，往往不苛求系统的实时一致性，只要在允许的时

间段内达到最终一致性即可，可采用事务补偿的方式。

与事务在执行中发生错误立刻回滚的方式不同，事务补偿是一种事后检查补救的措施，一些常见的实现

方法有：对数据进行对账检查，基于日志进行对比，定期同标准数据来源进行同步等。

解决办法：

使用分布式事务，比如阿里的Seata分布式事务，XA两阶段事务，saga柔性事务。

2）、跨节点关联查询Join问题

切分之前，系统中很多列表和详情表的数据可以通过 Join 来完成，但是切分之后，数据可能分布在不同

的节点上，此时 Join 带来的问题就比较麻烦了，考虑到性能，尽量避免使用 Join 查询。

解决的办法：

全局表：

全局表，也可看做“数据字典表”，就是系统中所有模块都可能依赖的一些表，为了避免库 Join 查询，可

以将这类表在每个数据库中都保存一份。这些数据通常很少修改，所以不必担心一致性的问题。

字段冗余：

一种典型的反范式设计，利用空间换时间，为了性能而避免 Join 查询。

例如，订单表在保存 userId 的时候，也将 userName 也冗余的保存一份，这样查询订单详情表就可以

查到用户名 userName，就不用查询买家 user 表了。

但这种方法适用场景也有限，比较适用依赖字段比较少的情况，而冗余字段的一致性也较难保证。

数据组装(常用)：

在系统 Service 业务层面，分两次查询，第一次查询的结果集找出关联的数据 id，然后根据 id 发起器

第二次请求得到关联数据，最后将获得的结果进行字段组装。这是比较常用的方法。

ER分片：

关系型数据库中，如果已经确定了表之间的关联关系（如订单表和订单详情表），并且将那些存在关联

关系的表记录存放在同一个分片上，那么就能较好地避免跨分片 Join 的问题。

可以在一个分片内进行 Join，在 1：1 或 1：n 的情况下，通常按照主表的 ID 进行主键切分。

3）、跨节点分页、排序、函数问题

跨节点多库进行查询时，会出现

limit 分页、order by 排序等问题。

分页需要按照指定字段进行排序，当排序字段就是分片字段时，通过分片规则就比较容易定位到指定的

分片；当排序字段非分片字段时，就变得比较复杂。

需要先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排

序。

最终返回给用户如下图：

上图只是取第一页的数据，对性能影响还不是很大。但是如果取得页数很大，情况就变得复杂的多。

因为各分片节点中的数据可能是随机的，为了排序的准确性，需要将所有节点的前N页数据都排序好做

合并，最后再进行整体排序，这样的操作很耗费 CPU 和内存资源，所以页数越大，系统性能就会越

差。

在使用 Max、Min、Sum、Count 之类的函数进行计算的时候，也需要先在每个分片上执行相应的函

数，然后将各个分片的结果集进行汇总再次计算。

4）、全局主键避重问题

在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某

个分区数据库自生成 ID 无法保证全局唯一。

因此需要单独设计全局主键，避免跨库主键重复问题。这里有一些策略：

1. Twitter的Snowflake（又名“雪花算法”）

2. UUID/GUID（一般应用程序和数据库均支持）

3. MongoDB ObjectID（类似UUID的方式）

4. Ticket Server（数据库生存方式，Flickr采用的就是这种方式）

5）、数据迁移、扩容问题

当业务高速发展、面临性能和存储瓶颈时，才会考虑分片设计，此时就不可避免的需要考虑历史数据的

迁移问题。

一般做法是先读出历史数据，然后按照指定的分片规则再将数据写入到各分片节点中。

此外还需要根据当前的数据量个 QPS，以及业务发展速度，进行容量规划，推算出大概需要多少分片

（一般建议单个分片的单表数据量不超过 1000W）。

四、电商系统亿级订单数据分库分表实战指导

1、Sharding-JDBC

中间件：

1）、sharding-jdbc是什么?

Sharding-JDBC是分布式数据中间件Sharding-Sphere中的重要组成部分，官方的介绍如下：

官网地址：https://shardingsphere.apache.org/

Sharding-Sphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding

JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。他们均提供

标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云

原生等各种多样化的应用场景。

定位为轻量级Java框架，在Java的JDBC层提供的额外服务，它使用客户端直连数据库，以jar包形式提供

服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

适用于任何基于JDBC的ORM框架，如JPA,Hibernate,Mybatis,Spring JDBC Template或直接使用

JDBC。

支持任何第三方的数据库连接池，如DBCP,C3P0,Druid,HikariCP等。

支持任意实现JDBC规范的数据库。目前支持MySQL,Oracle,SQLServer以及任何遵循SQL92标准的

数据库。

2）、sharding-jdbc能做什么？

Sharding-JDBC的核心功能为数据分片和读写分离，通过sharding-JDBC，应用可以透明的使用jdbc访

问已经分库，读写分离的多个数据源，而不用关心数据源的数量以及数据如何分布。

A.sharding-jdbc核心概念：

逻辑表（LogicTable）：具有相同逻辑和数据结构的水平分片数据库(表)。例如订单数据根据主键

最后一个数字划分为10个表，他们是从tb_order_0到tb_order_9,其逻辑名称为tb_order。

实际表（ActualTable）：真正存在于分片数据库的物理表，即tb_order_0到tb_order_9。

数据节点（DataNode）：数据分片的最小单元。由数据源名称和数据表组成，例：

ds_0.tb_order_0。

动态表（DynamicTable）:逻辑表和物理表不一定需要在配置规则中静态配置。如，按照日期分片

的场景，物理表的名称随时间的推移会产生变化。比如： tb_order_0一定是静态表，tb_order_$

有可能是tb_order_1,也有可能是tb_order_2。

绑定表（BindingTable）:指分片规则一致的主表和子表。例如：tb_order表和tb_order_item表，

均按照order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积

关联，关联查询效率将大大提升。

举例说明，如果SQL为：

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE

o.order_id in (10, 11);

在不配置绑定表关系时，假设分片键order_id 将数值10路由至第0片，将数值11路由至第1片，那么路

由后的SQL应该为4条，他们会出现笛卡尔积现象：

在配置绑定表配置后，路由的SQL应该为2条：

其中t_order在FROM的最左侧，ShardingSphere将会以它作为整个绑定表的主表。所有路由计算将会

只使用主表的策略，那么t_order_item表的分片计算将会使用t_order的条件。故绑定表之间的分区键

要完全相同。

B.sharding-jdbc分片键，算法，策略：

分片键：用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主

键的尾数取模分片，则订单主键为分片字段。

分片算法：通过分片算法将数据分片，支持通过 = 、 >= 、 <= 、 > 、 < 、 BETWEEN 和 IN 分片。分

片算法需要应用方开发者自行实现，可实现的灵活度非常高。

目前提供4种分片算法。由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通

过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算

法。

精确分片算法

对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景。需要

配合StandardShardingStrategy使用。

范围分片算法

对应RangeShardingAlgorithm，用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、

<=进行分片的场景。需要配合StandardShardingStrategy使用。

复合分片算法

对应ComplexKeysShardingAlgorithm，用于处理使用多键作为分片键进行分片的场景，包含多

个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合

ComplexShardingStrategy使用。

Hint分片算法

对应HintShardingAlgorithm，用于处理使用Hint行分片的场景。需要配合HintShardingStrategy

使用。

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id

WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id

WHERE o.order_id in (10, 11);

SELECT i.*

FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id

WHERE

(

)

;

SELECT

OIN t_order_item_1 i ON o.order_id=i.order_id

WHERE

o.order_id in (10, 11);

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id

WHERE o.order_id in (10, 11);

SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id

WHERE o.order_id in (10, 11);

分片策略：包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作

的是分片键 + 分片算法，也就是分片策略。目前提供5种分片策略。

标准分片策略

对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片

操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和

RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN

的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN AND, >, <, >=, <=分片，如果

不配置RangeShardingAlgorithm

，SQL中的BETWEEN AND将按照全库路由处理。

复合分片策略

对应ComplexShardingStrategy

。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和

BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间

的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算

法，完全由应用开发者实现，提供最大的灵活度。

行表达式分片策略

对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支

持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码

开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为

t_user_0 到 t_user_7 。

Hint分片策略

对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策

略。

不分片策略

对应NoneShardingStrategy。不分片的策略。

C.sharding-jdbc快速入门:

1、需求说明：

使用sharding-jdbc完成对订单表的水平分表，通过快速入门的开发，了解sharding-jdbc使用方法

人工创建两张表，t_order_1和t_order_2，这两张表是订单表拆分后的表，通过sharding-jdbc向订单表

插入数据，按照一定的分片规则，主键为偶数的进入t_order_1，另一部分数据进入t_order_2，通过

sharding-jdbc查询数据，根据SQL语句的内容从t_order_1或t_order_2查询数据。

2、环境搭建：

1、创建订单数据库order_db

2、在order_db中创建t_order_1、t_order_2表

CREATE DATABASE order_db CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

DROP TABLE IF EXISTS t_order_1;

CREATE TABLE t_order_1(

`order_id` BIGINT(20) NOT NULL COMMENT '订单id',

`price` DECIMAL(10,2) NOT NULL COMMENT '订单价格',

`user_id` BIGINT(20) NOT NULL COMMENT '下单用户id',

`status` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT

NULL COMMENT '订单状态',

PRIMARY KEY (`order_id`) USING BTREE

3、引入maven

依赖坐标pom.xml

3、配置分片规则：

) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT

= Dynamic;

DROP TABLE IF EXISTS t_order_2;

CREATE TABLE t_order_2(

`order_id` BIGINT(20) NOT NULL COMMENT '订单id',

`price` DECIMAL(10,2) NOT NULL COMMENT '订单价格',

`user_id` BIGINT(20) NOT NULL COMMENT '下单用户id',

`status` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT

NULL COMM

订

单

状

态

(

order_id`) USING BTREE

) ENG

INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT

= Dyn

;

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>2.1.0.RELEASE</version>

</parent>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<groupId>org.mybatis.spring.boot</groupId>

<artifactId>mybatis-spring-boot-starter</artifactId>

</dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid-spring-boot-starter</artifactId>

</dependency>

<groupId>org.apache.shardingsphere</groupId>

<artifactId>sharding-jdbc-spring-boot-starter</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-test</artifactId>

<version>2.1.0.RELEASE</version>

</dependency>

</dependencies>

分片规则配置是sharding-jdbc进行对分库分表操作的重要依据，配置内容包括：数据源、主键生成策

略、分片策略等。在application.properties中配置

4、测试新增订单：

1、创建订单实体

#端口号

server.port=56000

#实例名称

spring.application.name= sharding_quick

#表示后发现的bean会覆盖之前相同名称的bean

spring.ma

-o

#该配置项就

是

指

将

带

有

下

划

线

的

表

字

段

映

射

为

驼

峰

格

式

的

实

体

类

属

性

mybat

ration.map-underscore-to-camel-case=true

#以下是

分

片

规

则

配

置

##定

义

数

据

源

spri

ardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSou

rce

spring.shardingsphere.datasource.m1.driver-class-name =com.mysql.jdbc.Driver

spring.shardingsphere.datasource.m1.url=jdbc:mysql://118.31.18.203:3306/orde

r_db?useUnicode=true

spring.shardingsphere.datasource.m1.username=root

spring.shardingsphere.datasource.m1.password=root

#指定t_order表的数据分布情况，配置数据节点

spring.shardingsphere.sharding.tables.t_order.actualDataNodes=m1.t_order_$->

{1..2}

#指定t_order表的主键生成策略为SNOWFLAKE

spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id

spring.shardingsphere.sharding.tables.t_order.keyGenerator.type=SNOWFLAKE

#指定t_order表的分片策略，分片策略包括分片键和分片算法

spring.shardingsphere.sharding.tables.t_order.tableStrategy.inline.shardingC

olumn=order_id

spring.shardingsphere.sharding.tables.t_order.tableStrategy.inline.algorithm

Expression=t_order_$->{order_id % 2+1}

#打开sql输出日志

spring.shardingsphere.props.sql.show=true

#日志级别

logging.level.root=info

logging.level.org.springframework.web=info

logging.level.com.itheima=debug

logging.level.druid.sql=debug

2、创建mapper接口

3、测试新增订单

4、控制台打印sql

/**

* 订单实体

@Data

public class Order {

private Long orderId;

private BigDecimal price;

priva

;

priva

}

/**

* 订单mapper接口

@Mapper

public interface OrderMapper {

@Insert("insert into t_order(price,user_id,status) values(#{price},#

{userId},#{status})")

public int inserOrder(Order order);

}

@RunWith(SpringRunner.class)

@SpringBootTest

public class ShardingQuickApplicationTests {

@Autowired

private OrderMapper orderMapper;

//测试新增

@Test

public void testInsertOrder(){

for (int i = 0; i < 10; i++) {

Order order=new Order();

order.setPrice(new BigDecimal((i+1)*5));

order.setStatus("success"+"|"+i);

order.setUserId(1L);

orderMapper.inserOrder(order);

}

通过日志可以发现order_id为奇数的被插入到t_order_2表，为偶数的被插入到t_order_1表，达到预期

目标。

5、测试查询订单：

1、创建mapper接口

2、测试类方法

3、打印sql日志

通过日志可以发现，根据传入order_id的奇偶不同，sharding-jdbc分别去不同的表检索数据，达到预期

目标。

6、小结：

通过日志分析，Sharding-JDBC在拿到用户要执行的sql之后干了哪些事儿：

解析sql，获取分片键值，在本例中是order_id。

Sharding-JDBC通过规则配置 t_order_$->{order_id % 2 + 1}，知道了当order_id为偶数时，应该

往t_order_1表插数据，为奇数时，往t_order_2插数据。

于是Sharding-JDBC根据order_id的值改写sql语句，改写后的SQL语句是真实所要执行的SQL语

句。

执行改写后的真实sql语句。

将所有真正执行sql的结果进行汇总合并，返回。

D.sharding-jdbc执行原理：

//根据

查

询

多

个

订

单

@Sele

(

{

t>"+

ct "+

" from t_order t "+

rderIds' item='id' open='('

separator=',' close=')'>" +

"#{id}" +

"</foreach>" +

"</script>"})

public List<Map> selectListByOrderIds(@Param("orderIds") List<Long>

orderIds);

//根据id查询多个订单

@Test

public void testSelect(){

List<Long> orderIds=new ArrayList<>();

orderIds.add(490947906191228928L);

orderIds.add(490947906191228929L);

orderMapper.selectListByOrderIds(orderIds);

}

由图可知，在sql执行过程中需要经过几个过程：

例如现在有一条查询语句：

进行了分库分表操作，2个库ds0,ds1,采用的分片键为id,逻辑表为tb_user,真实表为

tb_user_0,tb_user_1两张表，分库、分表算法均为取余(%2)。

sql解析：通过解析sql语句提取分片键列与值进行分片，例如比较符 =、in、 between and,及查询

的表等

sql改写：根据解析结果，及采用的分片逻辑改写sql，上例经过sql改写之后，真实的语句为：

sql路由：找到sql需要去哪个库，哪个表执行语句，上例sql根据采用的策略可以得到将在ds0库

tb_user_0表执行语句。

sql执行：执行改写后的sql。

结果归并：当我们执行某些复杂语句时，sql可能会在多个库、多个表中执行，sql分别对应执行后

会对结果集进行归并操作，得到最终的结果。

3）、水平分库

A.需求说明：

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

接下来咱们继续对快速入门中的例子进行完善。

B.将原有order_db库拆分为order_db_1、order_db_2

select * from tb_user where id=10;

select * from tb_user_0 where id=10;

C.分片规则修改

由于数据库拆分了两个，这里需要配置两个数据源。分库需要配置分库的策略，和分表策略的意义类

似，通过分库策略实现数据操作针对分库的数据库进行操作

CREATE DATABASE order_db_1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE DATABASE order_db_2 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

DROP TABLE IF EXISTS t_order_1;

CREATE TABLE t_order_1(

`order_id` BIGINT(20) NOT NULL COMMENT '订单id',

`price` DECIMAL(10,2) NOT NULL COMMENT '订单价格',

`user_id` BIGINT(20) NOT NULL COMMENT '下单用户id',

`status` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT

NULL COMMENT '订单状态',

PRIMARY KEY (`order_id`) USING BTREE

) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT

= Dynamic;

DROP TABLE IF EXISTS t_order_2;

CREATE TABLE t_order_2(

`order_id` BIGINT(20) NOT NULL COMMENT '订单id',

`price` DECIMAL(10,2) NOT NULL COMMENT '订单价格',

`user_id` BIGINT(20) NOT NULL COMMENT '下单用户id',

`status` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT

NULL COMMENT '订单状态',

PRIMARY KEY (`order_id`) USING BTREE

) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT

= Dynamic;

##定义数据源

spring.shardingsphere.datasource.names=m1,m2

#数据源1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSou

rce

spring.shardingsphere.datasource.m1.driver-class-name =com.mysql.jdbc.Driver

spring.shardingsphere.datasource.m1.url=jdbc:mysql://118.31.18.203:3306/orde

r_db_1?useUnicode=true

spring.shardingsphere.datasource.m1.username=root

spring.shardingsphere.datasource.m1.password=root

分库策略，如何将一个逻辑表映射到多个数据源

spring.shardingsphere.sharding.tables.<逻辑表名称>.database‐strategy.<分片策略>.<分片策

略属性名>= #分片策略属性值

分表策略，如何将一个逻辑表映射为多个实际表

spring.shardingsphere.sharding.tables.<逻辑表名称>.table‐strategy.<分片策略>.<分片策略属

性名>= #分片策略属性值

目前例子中都使用inline分片策略，若对其他分片策略细节若感兴趣，请查阅官方文档：

https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/concep

t/sharding/

D.测试插入订单

1、测试代码

2、打印sql日志

#数据源2

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSou

rce

spring.shardingsphere.datasource.m2.driver-class-name =com.mysql.jdbc.Driver

spring.shardingsphere.datasource.m2.url=jdbc:mysql://118.31.18.203:3306/orde

r_db_2?useUnicode=true

spring.shardingsphere.datasource.m2.username=root

spring.shardingsphere.datasource.m2.password=root

。。。。。

#分库策略，

以user_id为分片键，分片策略为user_id%2+1，user_id为偶数操作m1数据源，否则

操作m2。

sprin

sphere.sharding.tables.t_order.databaseStrategy.inline.shardi

ngCol

spri

les.t_order.databaseStrategy.inline.algori

thmE

}

//-------------------水平分库

@Test

public void testInsertTable(){

for (int i = 0; i < 10; i++) {

Order order=new Order();

order.setPrice(new BigDecimal((i+1)*5));

order.setStatus("WAIT_PAY"+"|"+i);

order.setUserId(1L);

orderMapper.inserOrder(order);

}

for (int i = 0; i < 10; i++) {

Order order=new Order();

order.setPrice(new BigDecimal((i+1)*10));

order.setStatus("WAIT_PAY"+"|"+i);

order.setUserId(2L);

orderMapper.inserOrder(order);

}

3、查看数据库和表

通过日志和数据库可以看出，根据分片键user_id的奇偶不同，数据分别落在了不同数据源，达到目

标。

E.测试查询订单

1、测试代码

原快速入门代码

2、打印sql日志

1)、大家发现，所有查的都是m1数据源上的，这是不对的，因为测试代码中两个订单id都是在

order_db_2数据库m2中的

问题的原因：是我们的分片规则中已经是分库又分表了，我们发现我们对于表的分片规则中是这样的，

我们设定的是固定了m1数据源。

2)、更改配置数据节点数据源

3)、然后在进行测试

4)、我们发现出现了四条sql语句，这是什么原因呢？

大家一定要先确定一件事，就是我们目前的配置规则中是即分库又分表的规则，所以我们的sql语句中

只涉及到order_id，而这个order_id是分表的分片键，并没有分库的分片键，所以shardingjdbc将广播

路由到所有的库中，然后在每个库中去路由对应的数据节点表。

5)、此时我们去修改sql语句，加入user_id分片键

//根据id查询多个订单

@Test

public void testSelect(){

List<Long> orderIds=new ArrayList<>();

orderIds.add(491708667008122880L);//在order_db_2库中t_order_1表

orderIds.add(491708666341228545L);//在order_db_2库中t_order_2表

orderMapper.selectListByOrderIds(orderIds);

}

#指定t_order表的数据分布情况，配置数据节点

spring.shardingsphere.sharding.tables.t_order.actualDataNodes=m$->

{1..2}.t_order_$->{1..2}

6)、修正测试代码

7)、sql日志打印

查询条件user_id为1，根据分片策略m$->{user_id % 2 + 1}计算得出m2，此sharding-jdbc将sql路由到

4）、垂直分库

A.需求说明：

是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理

念是专库专用。接下来看一下在单库中实现垂直分库操作。

B.创建数据库：

//根据订单id和userId查询多个订单

@Select({"<script>"+

"select "+

" * "+

" from t_order t"+

" where t.order_id in "+

"<foreach collection='orderIds' item='id' open='(' separator=','

close=')'>" +

{

ch>" +

"AND t.user_id=#{userId}" +

}

)

ctListByUserIdAndOrderIds(@Param("userId") Long

user

Id,@Param("orderIds") List<Long> orderIds);

//根据orderId和userId查询多个订单

@Test

public void testSelectByUserIdAndOrderId(){

List<Long> orderIds=new ArrayList<>();

orderIds.add(491708667008122880L);//在order_db_2库中t_order_1表

orderIds.add(491708666341228545L);//在order_db_2库中t_order_2表

Long userId=1L;

List<Map> maps = orderMapper.selectListByUserIdAndOrderIds(userId,

orderIds);

System.out.println(maps);

}

C.分片规则修改：

在原有的基础之上加入

D. 数据操作:

userMapper接口

#创建数据库

CREATE DATABASE user_db CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

#创建表

DROP TABLE IF EXISTS t_user;

CREATE TABLE t_user(

user_id BIGINT(20) NOT NULL COMMENT '用户id',

fullname VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT

NULL COMM

'用

户

姓

名

user_

(

DEFAULT NULL COMMENT '用户类型',

PRIMARY KEY (user_id) USING BTREE

) ENG

ARACTER SET ='utf8' COLLATE ='utf8_general_ci'

ROW_F

;

##定义数据源

spring.shardingsphere.datasource.names=m0,m1,m2

#数据源0

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSou

rce

spring.shardingsphere.datasource.m0.driver-class-name =com.mysql.jdbc.Driver

spring.shardingsphere.datasource.m0.url=jdbc:mysql://118.31.18.203:3306/user

_db?useUnicode=true

spring.shardingsphere.datasource.m0.username=root

spring.shardingsphere.datasource.m0.password=root

#t_user分表策略

spring.shardingsphere.sharding.tables.t_user.actualDataNodes=m0.t_user

spring.shardingsphere.sharding.tables.t_user.tableStrategy.inline.shardingCo

lumn=user_id

spring.shardingsphere.sharding.tables.t_user.tableStrategy.inline.algorithmE

xpression=t_user

#指定t_user表的主键生成策略

spring.shardingsphere.sharding.tables.t_user.keyGenerator.column=user_id

spring.shardingsphere.sharding.tables.t_user.keyGenerator.type=SNOWFLAKE

/**

* user表的mapper接口

@Mapper

public interface UserMapper {

//插入用户

@Insert("insert into t_user(fullname,user_type)values(#{fullname},#

{userType})")

public int insertUser(@Param("fullname") String

fullname,@Param("userType") String userType);

//根据id查询多个用户

E.测试代码

F.sql日志打印：

插入用户：

查询用户：

通过日志可以看出t_user表的插入和查询数据均被落在了m0数据源，达到目标。

5）、读写分离

@Select("<script>" +

" select " +

" * " +

" from t_user t " +

" where t.user_id in " +

"<foreach collection='userIds' item='id' open='('

separator=',' close=')'>" +

"#{id}" +

"</foreach>" +

publi

lectListByUserIds(@Param("userIds") List<Long>

userI

ds);

//-----------垂直分库

@Autowired

private UserMapper userMapper;

//测试新增

@Test

public void testInsertUser(){

for (int i = 0; i < 10; i++) {

userMapper.insertUser("张三"+i,i+"");

}

//根据orderId和userId查询多个订单

@Test

public void testSelectUsers(){

List<Long> userIds=new ArrayList<>();

userIds.add(491758273087668224L);

userIds.add(491758273284800513L);

List<Map> maps = userMapper.selectListByUserIds(userIds);

System.out.println(maps);

}

A.理解读写分离：

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少

写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负

责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理

能力。使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升系统的可用性，可以达到在任何

一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。

读写分离的数据节点中的数据内容是一致的，而水平分片的每个数据节点的数据内容却并不相同。将水

平分片和读写分离联合使用，能够更加有效的提升系统的性能。Sharding-JDBC读写分离则是根据SQL

语义的分析，将读操作和写操作分别路由至主库与从库。它提供透明化读写分离，让使用方尽量像使用

一个数据库一样使用主从数据库集群。

Sharding-JDBC提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用，同一线程且同一

数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性。Sharding-JDBC不

提供主从数据库的数据同步功能，需要采用其他机制支持。

接下来，咱们对上面例子中user_db进行读写分离实现。为了实现Sharding-JDBC的读写分离，首先，

要进行mysql的主从同步配置。

B.mysql主从同步设置(window版)：

1、新增mysql实例

复制原有mysql如：

C:\Program Files (x86)\MySQL\MySQL Server 5.7(作为主库) -> C:\Program Files

(x86)\MySQL\MySQL Server 5.7-s1(作为从库)，并修改以下从库的my.ini：

然后将从库安装为windows服务，注意配置文件位置：

注意事项：

使用管理员身份进行cmd，否则会报“Install/Remove of the Service Denied”错误

由于从库是从主库复制过来的，因此里面的数据完全一致，可使用原来的账号、密码登录。

打开计算机服务列表可以查看到服务中有两个mysql服务

2.修改主、从库的配置文件(my.ini)，新增内容如下：

主库中添加

[mysqld]

# The

P/IP Port the MySQL Server will listen on

port=

# Path to installation directory. All paths are usually resolved relative to

this.

basedir="C:\Program Files (x86)\MySQL\MySQL Server 5.7-s1/"

# Path to the database root

datadir=C:\Program Files (x86)\MySQL\MySQL Server 5.7-s1/Data

C:\Program Files (x86)\MySQL\MySQL Server 5.7-s1\bin>mysqld install mysql-s1

--defaults-file="C:\Program Files (x86)\MySQL\MySQL Server 5.7-s1\my.ini"

从库中添加

重启主库和从库：

请注意，主从 MySQL下的数据(data)目录下有个文件auto.cnf，文件中定义了uuid，要保证主从数据库

实例的 uuid不一样，建议直接删除掉，重启服务后将会重新生成。

3.授权主从复制专用账号

切换到主库中

#开启日志

log-bin=mysql-bin

#设置服务id,主从服务不能一致

server-id=1

#设置需要同步的数据库

binlog-do-db=user_db

#屏蔽系统库同步

binlog-ig

rmation_schema

binlog-ignore-db=performance_schema

#开启日志

log-bin=mysql-bin

#设置服务id，主从不能一致

server-id=2

#设置需要同步的数据库

replicate_wild_do_table=user_db.%

#屏蔽系统库同步

replicate_wild_ignore_table=mysql.%

replicate_wild_ignore_table=information_schema.%

replicate_wild_ignore_table=performance_schema.%

net start [主库服务名称]

net start [从库服务名称]

4.设置从库向主库同步数据、并检查链路

最后测试在主库修改数据库，看从库是否能够同步成功。

在主库中修改user_db数据库表中的字段值，看从库中表中的相应字段值是否改变，如果改变则同步成

功，否则失败。

C.Sharding-jdbc读写分离：

写入主库，读取从库，主库和从库会进行主从复制操作

1、分片规则修改

#授权主备复制专用账号

GRANT replication SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED by 'db_sync';

#刷新权限

FLUSH PRIVILEGES;

#确认位点记录下文件名以及位点

SHOW MASTER STATUS;

#先停

止同步

STOP SLAVE;

#修改从库指向主库，使用上一步记录的文件名以及位点

CHANGE MASTER TO

master_host='localhost',

master_user='db_sync',

master_password='db_sync',

master_log_file='mysql-bin.000001',

master_log_pos=592;

#启动同步

START SLAVE;

#查看从库状态Slave_IO_Running和Slave_SQL_Runing都为yes说明同步成功，如果不为yes,请

检查error_Log，然后排查相关异常

SHOW SLAVE STATUS

##定义数据源

spring.shardingsphere.datasource.names=m0,m1,m2,s0

#数据源s0

spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSou

rce

spring.shardingsphere.datasource.s0.driver-class-name =com.mysql.jdbc.Driver

spring.shardingsphere.datasource.s0.url=jdbc:mysql://localhost:3307/user_db?

useUnicode=true

spring.shardingsphere.datasource.s0.username=root

spring.shardingsphere.datasource.s0.password=root

#数据源0

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSou

rce

spring.shardingsphere.datasource.m0.driver-class-name =com.mysql.jdbc.Driver

2、测试代码

3、sql日志打印：

插入数据，到主库中

通过日志可以看出，所有写操作落入m0数据源，主库中

当插入主库数据之后，mysql配置的主从复制功能就会马上把主库中的新数据同步到从库中表中

spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?

useUnicode=true

spring.shardingsphere.datasource.m0.username=root

spring.shardingsphere.datasource.m0.password=root

。。。。。

#主库从库逻辑数据源定义ds0为user_db，指定谁是主库，谁是从库

spring.shardingsphere.sharding.master-slave

rules.ds0.masterDataSourceName=m0

spring.shardingsphere.sharding.master-slave

rules.ds0

.slaveDataSourceNames=s0

。。。。。

#t_us

r分

表

策

略

#spri

ingsphere.sharding.tables.t_user.actualDataNodes=m0.t_user

#配置

指

定

主

库

还

是

从

库

中

的

r表

spri

ing.tables.t_user.actualDataNodes=ds0.t_user

spring.shardingsphere.sharding.tables.t_user.tableStrategy.inline.shardingCo

lumn=user_id

spring.shardingsphere.sharding.tables.t_user.tableStrategy.inline.algorithmE

xpression=t_user

spring.shardingsphere.sharding.tables.t_user.keyGenerator.column=user_id

spring.shardingsphere.sharding.tables.t_user.keyGenerator.type=SNOWFLAKE

//-----------垂直分库&读写分离

@Autowired

private UserMapper userMapper;

//测试新增

@Test

public void testInsertUser(){

for (int i = 10; i < 14; i++) {

userMapper.insertUser("张三"+i,"");

}

//根据userId查询多个订单

@Test

public void testSelectUsers(){

List<Long> userIds=new ArrayList<>();

userIds.add(491956648743534593L);

userIds.add(491956649020358656L);

List<Map> maps = userMapper.selectListByOrderIds(userIds);

System.out.println(maps);

}

读取数据，从从库中

通过日志可以看出，所有写操作落入

s0数据源，从库中，达到目标。

6）、sharding-jdbc

实战应用

A.需求说明

在电商项目中，每个订单项中都有订单id,订单价格，订单状态，而且该订单还必须绑定用户，不同的用

户所包含的订单不一样。

本案例实现的功能如下：

添加订单

订单分页查询

B.数据库设计

在本地数据库中创建order_db_1和order_db_2,并且导入百万级表数据

说明，本案例中操作的是订单库，订单表

对db_order数据库进行了水平分库操作，分片键是user_id，分片策略是{user_id % 2 + 1};

对t_order表进行水平分表，分片键为order_id,分片策略是{t_order % 2 + 1 }。

为避免主键冲突，ID生成策略采用雪花算法来生成全局唯一ID

使用读取分离来提高性能，可用性。

C.主从同步

参考读写分离章节，对以下库进行主从同步配置：重点

主库：

从库：

D.实现步骤

1、搭建maven工程

pom.xml

# 设置需要同步的数据库

binlog-do-db=order_db_1

binlog-do-db=order_db_2

#设置需要同步的数据库

replicate_wild_do_table=order_db_1.%

replicate_wild_do_table=order_db_2.%

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>2.1.0.RELEASE</version>

</parent>

<artifactId>sharding_order</artifactId>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

<project.reporting.outputEncoding>UTF-

8</project.reporting.outputEncoding>

<java.version>1.8</java.version>

</properties>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

>2.1.0.RELEASE</version>

oupId>

</dependency>

<groupId>javax.interceptor</groupId>

<artifactId>javax.interceptor-api</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<groupId>org.mybatis.spring.boot</groupId>

<artifactId>mybatis-spring-boot-starter</artifactId>

</dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid-spring-boot-starter</artifactId>

</dependency>

<groupId>org.apache.shardingsphere</groupId>

<artifactId>sharding-jdbc-spring-boot-starter</artifactId>

</dependency>

<groupId>com.baomidou</groupId>

<artifactId>mybatis-plus-boot-starter</artifactId>

</dependency>

<groupId>com.baomidou</groupId>

<artifactId>mybatis-plus-generator</artifactId>

</dependency>

2、分片规则配置

application.properties

<groupId>org.mybatis</groupId>

<artifactId>mybatis-typehandlers-jsr310</artifactId>

</dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-test</artifactId>

>2.1.0.RELEASE</version>

/dependencies>

#端口号

server.port=561000

#实例名称

spring.application.name= sharding_order

#表示后发现的bean会覆盖之前相同名称的bean

spring.main.allow-bean-definition-overriding=true

#该配置项就是指将带有下划线的表字段映射为驼峰格式的实体类属性

mybatis.configuration.map-underscore-to-camel-case=true

#以下是分片规则配置

##定义数据源

spring.shardingsphere.datasource.names=m1,m2,s1,s2

#------------------------------------------------------------------------

#定义db_order_1数据源主库

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSou

rce

spring.shardingsphere.datasource.m1.driver-class-name =com.mysql.jdbc.Driver

spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/order_db

_1?useUnicode=true

spring.shardingsphere.datasource.m1.username=root

spring.shardingsphere.datasource.m1.password=root

#定义db_order_1数据源从库

spring.shardingsphere.datasource.s1.type=com.alibaba.druid.pool.DruidDataSou

rce

spring.shardingsphere.datasource.s1.driver-class-name =com.mysql.jdbc.Driver

spring.shardingsphere.datasource.s1.url=jdbc:mysql://localhost:3307/order_db

_1?useUnicode=true

spring.shardingsphere.datasource.s1.username=root

spring.shardingsphere.datasource.s1.password=root

#读写分离，主库和从库绑定

spring.shardingsphere.sharding.master-slave

rules.ds1.masterDataSourceName=m1

spring.shardingsphere.sharding.master-slave

rules.ds1.slaveDataSourceNames=s1

#---------------------------------------------------------------------------

----------

#定义db_order_2数据源主库

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSou

rce

spring.shardingsphere.datasource.m2.driver-class-name =com.mysql.jdbc.Driver

spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/order_db

_2?useUnicode=true

spring.shardingsphere.datasource.m2.username=root

spring.shardingsphere.datasource.m2.password=root

#定义db_o

rder_2数据源从库

sprin

g.shardingsphere.datasource.s2.type=com.alibaba.druid.pool.DruidDataSou

rce

spri

_2?useUnicode=true

spring.shardingsphere.datasource.s2.username=root

spring.shardingsphere.datasource.s2.password=root

#读写分离，主库和从库绑定

spring.shardingsphere.sharding.master-slave

rules.ds2.masterDataSourceName=m2

spring.shardingsphere.sharding.master-slave

rules.ds2.slaveDataSourceNames=s2

#指定t_order表的数据分布情况，配置数据节点

#spring.shardingsphere.sharding.tables.t_order.actualDataNodes=m$->

{1..2}.t_order_$->{1..2}

spring.shardingsphere.sharding.tables.t_order.actualDataNodes=ds$->

{1..2}.t_order_$->{1..2}

#指定t_order表的主键生成策略为SNOWFLAKE

spring.shardingsphere.sharding.tables.t_order.keyGenerator.column=order_id

spring.shardingsphere.sharding.tables.t_order.keyGenerator.type=SNOWFLAKE

#t_order分表策略，指定t_order表的分片策略，分片策略包括分片键和分片算法

spring.shardingsphere.sharding.tables.t_order.tableStrategy.inline.shardingC

olumn=order_id

spring.shardingsphere.sharding.tables.t_order.tableStrategy.inline.algorithm

Expression=t_order_$->{order_id % 2+1}

#分库策略，以user_id为分片键，分片策略为user_id%2+1，user_id为偶数操作m1数据源，否则

操作m2。

spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.shardi

ngColumn=user_id

spring.shardingsphere.sharding.tables.t_order.databaseStrategy.inline.algori

thmExpression=ds$->{user_id % 2+1 }

#打开sql输出日志

spring.shardingsphere.props.sql.show=true

#日志级别

logging.level.root=info

logging.level.org.springframework.web=info

logging.level.com.itheima=debug

logging.level.druid.sql=debug

3、创建order实体

4、插入订单

新建orderMapper接口

测试代码

sql日志打印

/**

* 订单实体

@Data

public class Order {

priva

te Long orderId;

private BigDecimal price;

;

}

/**

* 订单mapper接口

@Mapper

public interface OrderMapper {

//插入订单

@Insert("insert into t_order(price,user_id,status) values(#{price},#

{userId},#{status})")

public int insertOrder(Order order);

}

@RunWith(SpringRunner.class)

@SpringBootTest

public class ShardingOrderApplicationTest {

@Autowired

private OrderMapper orderMapper;

//新增订单

@Test

public void insertTest(){

for (int i = 5000001; i < 5000010; i++) {

Order order=new Order();

order.setPrice(new BigDecimal((i+1)*5));

order.setStatus("wait pay"+"|"+i);

order.setUserId(1L);

orderMapper.insertOrder(order);

}

根据日志来看，数据插入的是

m2数据源，这是因为user_id为分片键进行分库操作，然后根据order_id

作为分片键进行分表操作的。

在分片规则中已经配置了主从库的绑定，从日志上看所有的插入写的操作均插入到主库中了，体现了读

写分离的特性。

查看主库和从库数据表截图

查看主库中的order_db_2数据库中的表数据和查看从库中的order_db_2数据库中的表数据是否同步，

如果同步表示主从同步ok，如果有问题没有同步，则参考读写分离篇章操作。

5、分页查询订单

新建orderMapper接口

测试代码

sql日志打印

通过日志来看，查询读操作，路由到了从库中，体现了读写分离的特性，达到了目标。但是page=2的

时候，sharding-jdbc解析sql还是limit 0,20? 但是最终sql归并结果是正确的。

//分页查询订单

@Select("select * from t_order where user_id=#{userId} order by order_id

desc limit #{start},#{size}")

public List<Order> selectByPage(@Param("userId") Long

userId,@Param("start") Integer start,@Param("size") Integer size);

//分页查询订单

@Test

public void testSelectByPage(){

Long userId=1L;

Integer page=2;

Integer size=10;

Integer start=(page-1)*size;

List<Order> orders = orderMapper.selectByPage(userId, start, size);

System.out.println(orders);

}

说明：

分页查询是业务中最常见的场景，Sharding-jdbc支持常用关系数据库的分页查询，不过Sharding-jdbc

的分页功能比较容易让使用者误解，用户通常认为分页归并会占用大量内存。在分布式的场景中，将

LIMIT 10000000 , 10改写为 LIMIT 0, 10000010 ，才能保证其数据的正确性。用户非常容易产生

ShardingSphere会将大量无意义的数据加载至内存中，造成内存溢出风险的错觉。其实大部分情况都

通过流式归并获取数据结果集，因此 ShardingSphere 会通过结果集的next方法将无需取出的数据全部

跳过，并不会将其存入内存。

但同时需要注意的是，由于排序的需要，大量的数据仍然需要传输到

Sharding-Jdbc的内存空间。因

此，采用LIMIT这种方式分页，并非最佳实践。

由于LIMIT并不能通过索引查询数据，因此如果可以保

证ID的连续性，通过

ID进行分页是比较好的解决方案，例如：

排序功能是由Sharding-jdbc的排序归并来完成，由于在SQL中存在 ORDER BY 语句，因此每个数据结

果集自身是有序的，因此只需要将数据结果集当前游标指向的数据值进行排序即可。这相当于对多个有

序的数组进行排序，归并排序是最适合此场景的排序算法。

2、MyCAT中间件：

说明，MyCAT中间件，本次不作为重点讲解，如果有兴趣的话可以查看《Mycat权威指南-全部-完整

版.pdf》

3、Sharding-JDBC与MyCAT的区别：

mycat 是一个基于第三方应用中间件的数据库代理框架，客户端所有的 jdbc 请求都必须要先交给

mycat ，再由 mycat 转发到具本的真实服务器中。sharding-jdbc 是一个jar形式，在本地应用层

重写的 jdbc 原生的方法，实现数据库分片形式。

mycat 属于服务器端的数据库中间件，而 sharding-jdbc 是一个本地数据库中间件框架。

从设计理念上看确实有一定的相似性。主要流程都是SQL解析 -> SQL路由 ->SQL改写 -> SQL执行-

>结果归并。但架构设计上是不同的。mycat 是基于Proxy，它复写了Mycat协议，将Mycat

server伪装成一个 mycat 数据库；而sharding-jdbc 是基于 jdbc 的扩展是以jar包的形式提供轻量

级服务的。

总结：mycat 与 sharding-jdbc 类似于 nginx 与springcloud ribbon

SELEC

T * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id;

posted @ 2023-05-19 23:32 十一vs十一阅读(25) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

WM_分库分表的深入实战剖析v1.0 跨节点join、分页排序 分表 io和cpu瓶颈 一般有用 看4 速

公告

WM_分库分表的深入实战剖析v1.0 跨节点join、分页排序分表 io和cpu瓶颈一般有用看4 速