文章目录
- 1.为什么要分库分表
- 2.分库分表有哪些中间件,不同的中间件都有什么优点和缺点?
- 3.分库分表的方式(水平分库,垂直分库,水平分表,垂直分表)
- 3.1 水平分库
- 3.2 垂直分库
- 3.3 水平分表
- 3.4 垂直分表
- 4.分库分表带来的问题
- 4.1 事务一致性问题
- 4.2 跨节点关联查询
- 4.3 跨节点分页、排序函数
- 4.4 主键避重
- 5.现在有一个未分库分表的系统,未来要分库分表, 如何设计才可以让系统从未分库分表动态切换到分库分表上?
- 6.如何设计可以动态扩容缩容的分库分表方案?
- 7.如何实现 mysql 的读写分离?MySQL 主从复制原理的是啥?
- 如何评估分库数量
- 分库分表之后,id 主键如何处理?(分布式ID)
- 分表要停服嘛?不停服怎么做?
- 分库分表后的分页问题
- order by,group by等聚合函数问题
- MySQL分区
- Sharding-JDBC
- Sharding-JDBC基础入门
- Sharding-JDBC作用
- 分片规则配置
- 流程分析
- Sharding-JDBC集成方式
1.为什么要分库分表
(1)为什么要分库
①问题背景:
在业务量剧增的情况下:
a.磁盘容量被撑爆;
b.数据库的连接数有限,高并发场景下,会出现too many connections报错。
②好处:
a.解决了单库大数据,高并发的性能瓶颈;
b.降低单机硬件资源的瓶颈。
(2)为什么要分表
①问题背景:
a.单表数据量太大,做了很多优化仍然无法提升效率
b.索引一般是B+树存储结构,B+树高度增高,查询会过慢
②好处
a.优化单一表数据量过大而产生的性能问题
b.避免IO争抢并减少锁表的几率
2.分库分表有哪些中间件,不同的中间件都有什么优点和缺点?
(1)目前流行的分库分表中间件比较多:
Sharding-JDBC
cobar
Mycat
Atlas
TDDL(淘宝)
vitess
(2)不同的中间件都有什么优点和缺点
①sharding-jdbc:优点:不用部署,运维成本低; 缺点:耦合度高,各个系统都依赖sharding-jdbc,系统升级困难
②mycat:优点:耦合度低,系统升级容易 缺点:需要部署,运维成本高
3.分库分表的方式(水平分库,垂直分库,水平分表,垂直分表)
3.1 水平分库
(1)水平分库是什么?
把同一个表的数据按一定规则拆到不同的数据库中,每个库可以放在不同的服务器上。
(2)例子:将店铺ID为单数的和店铺ID为双数的商品信息分别放在两个库中
3.2 垂直分库
(1)垂直分库是什么?
将表按业务分类,然后分布在不同数据库,并且可以将这些数据库部署在不同服务器上,从而达到多个服务器共同分摊压力的效果
(2)例子:由于商品信息与商品描述业务耦合度较高,因此一起被存放在PRODUCT_DB(商品库);而店铺信息相对独立,因此单独被存放在STORE_DB(店铺库)。
3.3 水平分表
(1)水平分表是什么?
是在同一个数据库内,把同一个表的数据按一定规则拆到多个表中
3.4 垂直分表
(1)垂直分表是什么?
将一个表按照字段分成多表,每个表存储其中一部分字段。
(2)通常我们按以下原则进行垂直拆分:
①把不常用的字段单独放在一张表;
②把text,blob等大字段拆分出来放在附表中;
③经常组合查询的列放在一张表中;
4.分库分表带来的问题
4.1 事务一致性问题
由于分库分表把数据分布在不同库甚至不同服务器,不可避免会带来分布式事务问题。
4.2 跨节点关联查询
(1)在没有分库前,我们检索商品时可以通过以下SQL对店铺信息进行关联查询
SELECT p.*,r.[地理区域名称],s.[店铺名称],s.[信誉]FROM [商品信息] p
LEFT JOIN [地理区域] r ON p.[产地] = r.[地理区域编码]LEFT JOIN [店铺信息] s ON p.id = s.[所属店铺]WHERE...ORDER BY...LIMIT...
(2)但垂直分库后[商品信息]和[店铺信息]不在一个数据库,甚至不在一台服务器,无法进行关联查询。可将原关联查询分为两次查询,第一次查询的结果集中找出关联数据id,然后根据id发起第二次请求得到关联数据,最后将获得到的数据进行拼装。
4.3 跨节点分页、排序函数
跨节点多库进行查询时,limit分页、order by排序等问题,就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回,然后将不同分片返回的结果集进行汇总和再次排序。
4.4 主键避重
在分库分表环境中,由于表中数据同时存在不同数据库中,主键值使用的自增长将无法使用,某个分区数据库生成的ID无法保证全局唯一。因此需要单独设计全局主键,以避免跨库主键重复问题。
5.现在有一个未分库分表的系统,未来要分库分表, 如何设计才可以让系统从未分库分表动态切换到分库分表上?
简单来说,就是在线上系统里面,之前所有写库的地方,增删改操作,都除了对老库增
删改,都加上对新库的增删改,这就是所谓双写,同时写俩库,老库和新库。
然后系统部署之后,新库数据差太远,用之前说的导数工具,跑起来读老库数据写新
库,写的时候要根据 gmt_modified 这类字段判断这条数据最后修改的时间,除非是读出来
的数据在新库里没有,或者是比新库的数据新才会写。
接着导万一轮之后,有可能数据还是存在不一致,那么就程序自动做一轮校验,比对
新老库每个表的每条数据,接着如果有不一样的,就针对那些不一样的,从老库读数据再次
写。反复循环,直到两个库每个表的数据都完全一致为止。
接着当数据完全一致了,就 ok 了,基于仅仅使用分库分表的最新代码,重新部署一次,
不就仅仅基于分库分表在操作了么,还没有几个小时的停机时间,很稳。所以现在基本玩儿
数据迁移之类的,都是这么干了。
6.如何设计可以动态扩容缩容的分库分表方案?
一开始上来就是 32 个库,每个库 32 个表,1024 张表
我可以告诉各位同学说,这个分法,第一,基本上国内的互联网肯定都是够用了,第
二,无论是并发支撑还是数据量支撑都没问题, 每个库正常承载的写入并发量是 1000,那么
32 个库就可以承载 32 * 1000 = 32000 的写并发,如果每个库承载 1500 的写并发,32 * 1500
= 48000 的写并发,接近 5 万/s 的写入并发,前面再加一个 MQ,削峰,每秒写入 MQ 8 万
条数据,每秒消费 5 万条数据。
有些除非是国内排名非常靠前的这些公司,他们的最核心的系统的数据库,可能会出
现几百台数据库的这么一个规模,128 个库,256 个库,512 个库 1024 张表,假设每个表放
500 万数据,在 MySQL 里可以放 50 亿条数据 每秒的 5 万写并发,总共 50 亿条数据,对于
国内大部分的互联网公司来说,其实一般来说都够了 谈分库分表的扩容,第一次分库分表,
就一次性给他分个够,32 个库,1024 张表,可能对大部分的中小型互联网公司来说,已经
可以支撑好几年了 一个实践是利用 32 * 32 来分库分表,即分为 32 个库,每个库里一个表
分为 32 张表。一共就是 1024 张表。根据某个 id 先根据 32 取模路由到库,再根据 32 取模
路由到库里的表。
刚开始的时候,这个库可能就是逻辑库,建在一个数据库上的,就是一个 mysql 服务器
可能建了 n 个库,比如 16 个库。后面如果要拆分,就是不断在库和 mysql 服务器之间做迁
移就可以了。然后系统配合改一下配置即可。
7.如何实现 mysql 的读写分离?MySQL 主从复制原理的是啥?
(1)如何实现 mysql 的读写分离?
基于主从复制架构,一个主库写,多个从库读
(2)MySQL 主从复制原理:
①主库将变更写入binlog日志
②从库连接到主库,通过IO线程将主库binlog日志拷贝到本地,写入到中继日志中
③从库有一个sql线程会从中继日志读取binlog,相当于在本地再执行一遍SQL
(3)存在两个问题:
①主库是并行执行,从库是串行执行,有概率出现刚写入主库的数据是读不到的
②主库突然宕机,恰好数据还没同步到从库,造成数据丢失
(4)解决:
①并行复制,解决主从同步延时问题:从库开启多个线程并行读取日志
②半同步复制,解决主库数据丢失问题:主库接收到从库ack后才认为操作成功
如何评估分库数量
(1)对于MySQL来说的话,一般单库超过5千万记录,DB的压力就非常大了。所以分库数量多少,需要看单库处理记录能力有关。
(2)如果分库数量少,达不到分散存储和减轻DB性能压力的目的;如果分库的数量多,对于跨多个库的访问,应用程序需要访问多个库。
(3)一般是建议分4~10个库,我们公司的企业客户信息,就分了10个库
分库分表之后,id 主键如何处理?(分布式ID)
(1)使用UUID或者雪花算法,
(2)好处:基于本地生成,不基于数据库
(3)缺点:太长,作为主键性能太差;UUID不具有有序性,会造成B+树有过多的随机写操作,频繁修改树结构,从而导致性能下降
分表要停服嘛?不停服怎么做?
不用停服,主要分五个步骤:
(1)编写代理层,加个开关(控制访问新的DAO还是老的DAO,或者是都访问),灰度期间,还是访问老的DAO。
(2)开启双写,既在旧表新增和修改,也在新表新增和修改。日志或者临时表记下新表ID起始值,旧表中小于这个值的数据就是存量数据,这批数据就是要迁移的。
(3)通过脚本把旧表的存量数据写入新表。
(4)停读旧表改读新表,此时新表已经承载了所有读写业务,但是这时候不要立刻停写旧表,需要保持双写一段时间。
(5)当读写新表一段时间之后,如果没有业务问题,就可以停写旧表啦
简单回答:
1.加开关,控制访问老的dao还是新的dao,或者都访问,灰度期间还是访问老的
2.开启双写,旧表和新表都进行新增和修改,日志记录新表id起始值,旧表中小于这个值就是要迁移的
3.通过脚本把旧表数据写入新表
4.停读旧表改读新表,此时不要立刻停写旧表,需要保持一段时间
5.当读写新表一段时间,如果业务没有问题,就可以停写新表了
分库分表后的分页问题
(1)在各个数据库节点查到对应结果,在代码端汇聚再分页。优点是业务无损,精准返回所需数据;缺点:返回过多数据,增大网络传输
(2)业务妥协,不允许跳页查询
order by,group by等聚合函数问题
跨节点的count,order by,group by以及聚合函数等问题,都是一类的问题,它们一般都需要基于全部数据集合进行计算。可以分别在各个节点上得到结果后,再在应用程序端进行合并。
MySQL分区
(1)分区和分表相似,都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表,而分区是将数据分段划分在多个位置存放,分区后,表还是一张表,但数据分散到各个分散的位置了。
(2)但是分区通常比较不建议使用,因为在mysql规范中写到对分区表的缺点:分区表对分区键有严格要求;分区表在表变大后,执⾏行DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使用分区表,并建议业务端手动SHARDING。
Sharding-JDBC
Sharding-JDBC基础入门
Sharding-JDBC作用
Sharding-JDBC的核心功能为数据分片和读写分离,通过Sharding-JDBC,应用可以透明的使用jdbc访问已经分库分表、读写分离的多个数据源,而不用关心数据源的数量以及数据如何分布。
分片规则配置
分片规则配置是sharding-jdbc进行对分库分表操作的重要依据,配置内容包括:数据源、主键生成策略、分片策略等
(1)首先定义数据源m1,并对m1进行实际的参数配置。
(2)指定t_order表的数据分布情况,他分布在m1.t_order_1,m1.t_order_2
(3)指定t_order表的主键生成策略为SNOWFLAKE,SNOWFLAKE是一种分布式自增算法,保证id全局唯一
(4)定义t_order分片策略,order_id为偶数的数据落在t_order_1,为奇数的落在t_order_2,分表策略的表达式为
t_order_$->{order_id % 2 + 1}
# 以下是分片规则配置
# 定义数据源
spring.shardingsphere.datasource.names = m1
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
# 指定t_order表的数据分布情况,配置数据节点
spring.shardingsphere.sharding.tables.t_order.actual‐data‐nodes = m1.t_order_$‐>{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key‐generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key‐generator.type=SNOWFLAKE
# 指定t_order表的分片策略,分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.sharding‐column = order_id
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.algorithm‐expression =
t_order_$‐>{order_id % 2 + 1}
流程分析
(1)解析sql,获取片键值,在本例中是order_id
(2)Sharding-JDBC通过规则配置 t_order_$->{order_id % 2 + 1},知道了当order_id为偶数时,应该往t_order_1表插数据,为奇数时,往t_order_2插数据。
(3)于是Sharding-JDBC根据order_id的值改写sql语句,改写后的SQL语句是真实所要执行的SQL语句。
(4)执行改写后的真实sql语句
(5)将所有真正执行sql的结果进行汇总合并,返回。
Sharding-JDBC集成方式
(1)Spring Boot Yaml 配置
server:port: 56081servlet:context‐path: /sharding‐jdbc‐simple‐demospring:application:name: sharding‐jdbc‐simple‐demohttp:encoding:enabled: truecharset: utf‐8force: truemain:allow‐bean‐definition‐overriding: trueshardingsphere:datasource:names: m1m1:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/order_db?useUnicode=trueusername: rootpassword: mysqlsharding:tables:t_order:actualDataNodes: m1.t_order_$‐>{1..2}tableStrategy:inline:shardingColumn: order_idalgorithmExpression: t_order_$‐>{order_id % 2 + 1}keyGenerator:type: SNOWFLAKEcolumn: order_idprops:sql:show: truemybatis:configuration:map‐underscore‐to‐camel‐case: trueswagger:enable: true
(2)Java 配置类
@Configurationpublic class ShardingJdbcConfig {// 定义数据源Map<String, DataSource> createDataSourceMap() {DruidDataSource dataSource1 = new DruidDataSource();dataSource1.setDriverClassName("com.mysql.jdbc.Driver");dataSource1.setUrl("jdbc:mysql://localhost:3306/order_db?useUnicode=true");dataSource1.setUsername("root");dataSource1.setPassword("root");Map<String, DataSource> result = new HashMap<>();result.put("m1", dataSource1);return result;}// 定义主键生成策略private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {KeyGeneratorConfiguration result = new
KeyGeneratorConfiguration("SNOWFLAKE","order_id");return result;}// 定义t_order表的分片策略TableRuleConfiguration getOrderTableRuleConfiguration() {TableRuleConfiguration result = new TableRuleConfiguration("t_order","m1.t_order_$‐>{1..2}");result.setTableShardingStrategyConfig(new
InlineShardingStrategyConfiguration("order_id", "t_order_$‐>{order_id % 2 + 1}"));result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());return result;}// 定义sharding‐Jdbc数据源@BeanDataSource getShardingDataSource() throws SQLException {ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());//spring.shardingsphere.props.sql.show = trueProperties properties = new Properties();properties.put("sql.show","true");return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig,properties);}}
(3)Spring Boot properties配置
# 定义数据源
spring.shardingsphere.datasource.names = m1spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root # 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key‐generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key‐generator.type=SNOWFLAKE# 指定t_order表的数据分布情况
spring.shardingsphere.sharding.tables.t_order.actual‐data‐nodes = m1.t_order_$‐>{1..2}# 指定t_order表的分表策略
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.sharding‐column = order_id
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.algorithm‐expression = t_order_$‐>{order_id % 2 + 1}