mysql分库分表 之 sharding-jdbc 使用姿势

后端程序员Aska
923 阅读18分钟

mysql架构师之路

业务增长-数据库性能优化思路讲解

  • 面试官：这边有个数据库-单表1千万数据，未来1年还会增长多500万，性能比较慢，说下你的优化思路
  • 思路
    • 千万不要一上来就说分库分表，这个是最忌讳的事项
    • 一定要根据实际情况分析，两个角度思考
      • 不分库分表
        • 软优化
          • 数据库参数调优
          • 分析慢查询SQL语句，分析执行计划，进行sql改写和程序改写
          • 优化数据库索引结构
          • 优化数据表结构优化
          • 引入NOSQL和程序架构调整
        • 硬优化
          • 提升系统硬件（更快的IO、更多的内存）：带宽、CPU、硬盘
      • 分库分表
        • 根据业务情况而定，选择合适的分库分表策略（没有通用的策略）
          • 外卖、物流、电商领域
        • 先看只分表是否满足业务的需求和未来增长
          • 数据库分表能够解决单表数据量很大的时,数据查询的效率问题，
          • 无法给数据库的并发操作带来效率上的提高，分表的实质还是在一个数据库上进行的操作，受数据库IO性能的限制
        • 如果单分表满足不了需求，再分库分表一起
  • 结论
    • 在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案
    • 如果数据量极大，且业务持续增长快，再考虑分库分表方案

mysql分库分表后带来的优点

  • 数据库本身问题
    • 1连接数：连接数过多时，就会出现too many connections的错误，访问量太大或者数据库设置的最大连接数太小的原因
    • 2mysql默认的最大连接数为100，可以修改，而mysql服务允许的最大连接数为16384
    • 3数据库分表可以解决单表海量数据的查询性能问题
    • 4数据库分库可以解决单台数据库的并发访问压力问题
  • 系统本身的问题
    • 1磁盘读写IO瓶颈，热点数据太多，尽管使用了数据库本身缓存，但是依旧有大量IO,导致sql执行速度慢
    • 2网络IO瓶颈，请求的数据太多，数据传输大，网络带宽不够，链路响应时间变长
    • 3CPU瓶颈，尤其在基础数据量大单机复杂SQL计算，SQL语句执行占用CPU使用率高，也有扫描行数大、锁冲突、锁等待等原因
      • 可以通过 show processlist; 、show full processlist，发现 CPU 使用率比较高的SQL
      • 常见的对于查询时间长，State 列值是 Sending data，Copying to tmp table，Copying to tmp table on disk，Sorting result，Using filesort 等都是可能有性能问题SQL，清楚相关影响问题的情况可以kill掉
      • 也存在执行时间短，但是CPU占用率高的SQL，通过上面命令查询不到，这个时候最好通过执行计划分析explain进行分析

Mysql数据库分库分表后的六大问题你是否知道怎么解决

  • 问题一：跨节点数据库Join关联查询
    • 数据库切分前，多表关联查询，可以通过sql join进行实现
    • 分库分表后，数据可能分布在不同的节点上，sql join带来的问题就比较麻烦
  • 问题二：分库操作带来的分布式事务问题
    • 操作内容同时分布在不同库中，不可避免会带来跨库事务问题，即分布式事务
  • 问题三：执行的SQL排序、翻页、函数计算问题
    • 分库后，数据分布再不同的节点上， 跨节点多库进行查询时，会出现limit分页、order by排序等问题
    • 而且当排序字段非分片字段时，更加复杂了，要在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序（也会带来更多的CPU/IO资源损耗）
  • 问题四：数据库全局主键重复问题
    • 常规表的id是使用自增id进行实现，分库分表后，由于表中数据同时存在不同数据库中，如果用自增id，则会出现冲突问题
  • 问题五：容量规划,分库分表后二次扩容问题
    • 业务发展快，初次分库分表后，满足不了数据存储，导致需要多次扩容
  • 问题六：分库分表技术选型问题
    • 市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择

Mysql数据库分库分表总结

  • 1技术负责人前瞻性思维
    • 1需要提前考虑系统一年到两年左右的业务增长情况
    • 2对数据库服务器的QPS、连接数、容量等做合理评估和规划
    • 3很多人觉得“分库分表”是宜早不宜迟
  • 2常规开发里面单表建议1千万内，推荐是百万级别单表存储，常规sql和索引优化先行，然后结合缓存+异步+nosql+mq
  • 3垂直角度（表结构不一样）
    • 垂直分表: 将一个表字段拆分多个表，每个表存储部分字段
      • 好处: 避免IO时锁表的次数，分离热点字段和非热点字段，避免大字段IO导致性能下降
      • 原则：业务经常组合查询的字段一个表；不常用字段一个表；text、blob类型字段作为附属表
    • 垂直分库：根据业务将表分类，放到不同的数据库服务器上
      • 1好处：避免表之间竞争同个物理机的资源，比如CPU/内存/硬盘/网络IO
      • 2原则：根据业务相关性进行划分，领域模型，微服务划分一般就是垂直分库
  • 4水平角度（表结构一样）
    • 1水平分库：把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上
      • 1- 好处: 多个数据库，降低了系统的IO和CPU压力
      • 2原则
        • 选择合适的分片键和分片策略，和业务场景配合
        • 避免数据热点和访问不均衡、避免二次扩容难度大
    • 水平分表：同个数据库内，把一个表的数据按照一定规则拆分到多个表中，对数据进行拆分，不影响表结构
      • 1单个表的数据量少了，业务SQL执行效率高，降低了系统的IO和CPU压力
      • 2原则
        • 选择合适的分片键和分片策略，和业务场景配合
        • 避免数据热点和访问不均衡、避免二次扩容难度大
  • 5互联网公司实际使用和跳槽面试
    • 公司业务稳定发展，多数情况是为了解决【单库单表】数据量过多问题
    • 重点是水平角度的【分库分表】

Mysql数据库垂直分表

  • 需求：商品表字段太多，每个字段访问频次不一样，浪费了IO资源，需要进行优化
  • 垂直分表介绍
    • 1也就是“大表拆小表”，基于列字段进行的
    • 2拆分原则一般是表中的字段较多，将不常用的或者数据较大，长度较长的拆分到“扩展表 如text类型字段
    • 3访问频次低、字段大的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中
    • 4垂直拆分原则
      • 1把不常用的字段单独放在一张表;
      • 2把text，blob等大字段拆分出来放在附表中;
      • 3业务经常组合查询的列放在一张表中

Mysql数据库垂直分库

  • 1需求：C端项目里面，单个数据库的CPU、内存长期处于90%+的利用率，数据库连接经常不够，需要进行优化
  • 2垂直分库
    • 1垂直分库针对的是一个系统中的不同业务进行拆分， 数据库的连接资源比较宝贵且单机处理能力也有限
    • 2没拆分之前全部都是落到单一的库上的，单库处理能力成为瓶颈，还有磁盘空间，内存，tps等限制
    • 3拆分之后，避免不同库竞争同一个物理机的CPU、内存、网络IO、磁盘，所以在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈
    • 4垂直分库可以更好解决业务层面的耦合，业务清晰，且方便管理和维护
    • 5一般从单体项目升级改造为微服务项目，就是垂直分库
  • 3问题：垂直分库分表可以提高并发，但是依然没有解决单表数据量过大的问题

Mysql数据库水平分表

  • 1需求：当一张表的数据达到几千万时，查询一次所花的时间长，需要进行优化，缩短查询时间
  • 2都是大表拆小表
    • 1垂直分表：表结构拆分
    • 2水平分表：数据拆分
  • 3水平分表
    • 1把一个表的数据分到一个数据库的多张表中，每个表只有这个表的部分数据
    • 2核心是把一个大表，分割N个小表，每个表的结构是一样的，数据不一样，全部表的数据合起来就是全部数据
    • 3针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去
    • 4但是这些表还是在同一个库中，所以单数据库操作还是有IO瓶颈，主要是解决单表数据量过大的问题
    • 5减少锁表时间，没分表前，如果是DDL(create/alter/add等)语句，当需要添加一列的时候mysql会锁表，期间所有的读写操作只能等待

Mysql数据库水平分库

  • 1需求：高并发的项目中，水平分表后依旧在单个库上面，1个数据库资源瓶颈 CPU/内存/带宽等限制导致响应慢，需要进行优化
  • 2水平分库
    • 1把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上
    • 2水平分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构
    • 3每个库的结构都一样,但每个库的数据都不一样，没有交集，所有库的并集就是全量数据
    • 4水平分库的粒度，比水平分表更大

水平分库分表常见策略讲解

  • 1Mysql数据库水平分库分表常见策略介绍-range
    • 1方案一：自增id，根据ID范围进行分表（左闭右开） image.png
    • 2方案二：水平分库分表常见策略介绍-Hash取模 image.png
  • 2
    • 1
    • 2

分库分表和Sharding-Jdbc常见概念术语

  • 1数据节点Node
    • 1- 数据分片的最小单元，由数据源名称和数据表组成
    • 2- 比如：ds_0.product_order_0
  • 2真实表
    • 1 在分片的数据库中真实存在的物理表
    • 2 比如订单表 product_order_0、product_order_1、product_order_2
  • 3逻辑表
    • 1 水平拆分的数据库（表）的相同逻辑和数据结构表的总称
    • 2 比如订单表 product_order_0、product_order_1、product_order_2，逻辑表就是product_order
  • 4绑定表
    • 1 指分片规则一致的主表和子表
    • 2 比如product_order表和product_order_item表，均按照order_id分片，则此两张表互为绑定表关系
    • 3 绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升
  • 5广播表
    • 1 指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致
    • 2 适用于数据量不大且需要与海量数据的表进行关联查询的场景
    • 3 例如：字典表、配置表

分库分表和Sharding-Jdbc常见分片算法讲解

  • 1数据库表分片（水平库、表）- 包含分片键和分片策略
    • 1分片键 (PartitionKey)
      • 1- 用于分片的数据库字段，是将数据库(表)水平拆分的关键字段
      • 2- 比如prouduct_order订单表，根据订单号 out_trade_no做哈希取模，则out_trade_no是分片键
      • 3- 除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片
    • 2分片策略（先了解，后面有案例实战）
      • 1行表达式分片策略 InlineShardingStrategy（必备）
        • 1 只支持【单分片键】使用Groovy的表达式，提供对SQL语句中的 =和IN 的分片操作支持
        • 2 可以通过简单的配置使用，无需自定义分片算法，从而避免繁琐的Java代码开发
        • 3 prouduct_order_$->{user_id % 8} 表示订单表根据user_id模8，而分成8张表，表名称为prouduct_order_0到prouduct_order_7
      • 2标准分片策略StandardShardingStrategy（需了解）
        • 1
        • 2
        • 3
  • 2
    • 1

codeDemo

Sharding-Jdbc 分库分表执行流程原理

image.png

image.png

  • 1
  • 2
  • 3

Sharding-Jdbc多种分片策略实战总结

  • 1 自己实现分片策略的优缺点
    • 1 优点：可以根据分片策略代码里面自己拼装 真实的数据库、真实的表，灵活控制分片规则
    • 2 缺点：增加了编码，不规范的sql容易造成全库表扫描，部分sql语法支持不友好
  • 2 行表达式分片策略 InlineShardingStrategy
    • 1 只支持【单分片键】使用Groovy的表达式，提供对SQL语句中的 =和IN 的分片操作支持
    • 2 可以通过简单的配置使用，无需自定义分片算法，从而避免繁琐的Java代码开发
    • 3 prouduct_order_$->{user_id % 8} 表示订单表根据user_id模8，而分成8张表，表名称为prouduct_order_0到prouduct_order_7
  • 3 标准分片策略StandardShardingStrategy
    • 1 只支持【单分片键】，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法
    • 2 PreciseShardingAlgorithm 精准分片 是必选的，用于处理=和IN的分片
    • 3 RangeShardingAlgorithm 范围分配 是可选的，用于处理BETWEEN AND分片
    • 4 如果不配置RangeShardingAlgorithm，如果SQL中用了BETWEEN AND语法，则将按照全库路由处理，性能下降
  • 4 复合分片策略ComplexShardingStrategy
    • 1 支持【多分片键】，多分片键之间的关系复杂，由开发者自己实现，提供最大的灵活度
    • 2 提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持
  • 5 Hint分片策略HintShardingStrategy
    • 1 这种分片策略无需配置分片健，分片健值也不再从 SQL中解析，外部手动指定分片健或分片库，让 SQL在指定的分库、分表中执行
    • 2 用于处理使用Hint行分片的场景，通过Hint而非SQL解析的方式分片的策略
    • 3 Hint策略会绕过SQL解析的，对于这些比较复杂的需要分片的查询，Hint分片策略性能可能会更好

Sharding-Jdbc分库分表后已经解决的问题

image.png

  • 1 问题一：执行的SQL排序、翻页、函数计算问题
    • 1 分库后，数据分布再不同的节点上， 跨节点多库进行查询时，会出现limit分页、order by排序等问题
    • 2 而且当排序字段非分片字段时，更加复杂了，要在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序（也会带来更多的CPU/IO资源损耗）
    • 3 解决方式：
      • 1 业务上要设计合理，利用好PartitionKey，查询的数据分布同个数据节点上，避免 跨节点多库进行查询时
      • 2 sharding-jdbc在结果合并层自动帮我们解决很多问题（流式归并和内存归并）
  • 2 问题二：数据库全局主键重复问题
    • 1 - 常规表的id是使用自增id进行实现，分库分表后，由于表中数据同时存在不同数据库中，如果用自增id，则会出现冲突问题
    • 2 解决方式：
      • 1 UUID
      • 2 自研发号器 redis
      • 3 雪花算法
  • 3问题三：分库分表技术选型问题
    • 1 市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择
    • 2 解决方式： 开源产品：主要是Mycat和ShardingJdbc区别，也是被面试官问比较多的
      • 1 两者设计理念相同，主流程都是SQL解析-->SQL路由-->SQL改写-->结果归并
        • 1 sharding-jdbc（推荐）
          • 2 基于jdbc驱动，不用额外的proxy，在本地应用层重写Jdbc原生的方法，实现数据库分片形式
          • 3 是基于 JDBC 接口的扩展，是以 jar 包的形式提供轻量级服务的，性能高
          • 4 代码有侵入性
        • 2 Mycat
          • 1 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个 MySQL 数据库
          • 2 客户端所有的jdbc请求都必须要先交给MyCat，再有MyCat转发到具体的真实服务器
          • 3 缺点是效率偏低，中间包装了一层
          • 4 代码无侵入性

Sharding-Jdbc 分库分表-跨节点数据库Join关联和多维度查询

  • 1 问题：跨节点数据库Join关联查询 和 多维度查询
    • 1 数据库切分前，多表关联查询，可以通过sql join进行实现
    • 2 分库分表后，数据可能分布在不同的节点上，sql join带来的问题就比较麻烦
    • 3 不同维度查看数据，利用的partitionKey是不一样的
    • 4 解决方案
      • 1 冗余字段
      • 2 广播表
      • 3 NOSQL汇总
    • 5 案例一
      • 1 订单需要用户的基本信息，但是分布在不同库上
      • 2 进行字段冗余，订单表冗余用户昵称、头像
    • 6 案例二
      • 1 订单表 的partionKey是user_id，用户查看自己的订单列表方便
      • 2 但商家查看自己店铺的订单列表就麻烦，分布在不同数据节点
        • 1 订单冗余存储在es上一份
        • 2 业务架构流程 image.png

Sharding-Jdbc 分库分表-二次扩容实施方案讲解

image.png

1方式一

  • 1 利用数据库主从同步
  • 3 新增两个数据库 A2、A3 作为从库，设置主从同步关系为：A0=>A2、A1=>A3,
  • 3 开启主从数据同步，早期数据手工同步过去
  • 4 发布新程序，某个时间点开始，利用MQ存储CUD操作
  • 5 关闭数据库实例的主从同步关系
  • 6 校验数据，消费原先MQ存储CUD操作,配置新分片规则和生效
  • 7 数据校验和修复
    • 1 依赖gmt_modified字段，所以常规数据表都需要加这个字段
    • 2 由数据库自己维护值，根据业务场景，进行修复对应的数据
    • 3 校验步骤
      • 1 开始迁移时间假如是2022-01-01 00:00:00
      • 2 查找 gmt_modified数据校验修复大于开始时间点，就是修改过的数据
  • 8 各个节点的冗余数据进行删除
  • 9 缺点
    • 1 同步的很多数据到最后都需要被删除
    • 2 一定要提前做，越晚做成本越高，因为扩容期间需要存储的数据更多
    • 3 基本都离不开代码侵入，加锁等操作
  • 10 优点
    • 1 利用mysql自带的主从同步能力
    • 2 方案简单，代码量相对少

1方式二

  • 1 小滴课堂-老王的最佳选择，对外发布公告，停机迁移

  • 2 严格一致性要求：比如证券、银行部分数据等

  • 3 优点：最方便、且安全

  • 4 缺点

    • 1 会造成服务不可用，影响业务
    • 2 根据停机的时间段，数据校验人员有压力

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

  • 1

定义

  • shardingSphere 是个什么鬼
    • 一套开源的分布式解决方案
    • 三个落地产品: sharding-jdbc 和 sharding-proxy
    • 定位为关系型数据库中间件，合理在分布式环境下使用关系型数据库进行操作
  • 分库分表是什么
    • 一套开源的分布式解决方案
    • 三个落地产品: sharding-jdbc 和 sharding-proxy
    • 定位为关系型数据库中间件，合理在分布式环境下使用关系型数据库进行操作
  • 分库分表方式
    • 垂直切分
      • 垂直分表 操作数据库中某张表，把这张表中一部分字段数据存到一张新表中，在把这张表另一部分字段数据存到另外一张表中 image.png
      • 垂直分库 把单一数据库按照业务进行划分，专库专表 image.png
    • 水平切分
      • 水平分表 image.png
      • 水平分库 image.png
  • 分库分表应用
    • 在数据库设计时候考虑垂直分库和垂直分表
    • 随着数据库数据量的增加，不要马上考虑做水平切分，首先考虑缓存处理，读写分离，使用索引等等方式，如果这些方式不能根本解决问题了，在考虑水平分库和水平分表
  • 分库分表问题

    • 跨节点连接查询问题（也叫跨库连接查询问题）分页，排序

    • 多数据源管理问题

sharding-JDBC

  • 定位为轻量级Java框架，在Java的JDBC层提供的额外服务，它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可以简单理解为增强版的JDBC驱动

  • sharding-JDBC：不是做分库分表 它主要提供两个能力

    • 数据分片
    • 读写分离

  • sharding-JDBC：主要作用：简化对分库分表之后数据相关操作 image.png

  • sharding-JDBC：主要作用：简化对分库分表之后数据相关操作

avatar
文章
阅读
粉丝