MySql详解（七）--分库分表篇

MySQL分库分表篇

分库分表介绍

使用背景

• 当【表的数量】达到了几百上千张表时，众多的业务模块都访问这个数据库，压力会比较大，考虑对其进行分库。

• 当【表的数据】达到了几千万级别，在做很多操作都比较吃力,所以，考虑对其进行分库或者分表

数据切分（sharding）方案

数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式：

• 垂直切分：按照业务模块进行切分，将不同模块的表切分到不同的数据库中。

• 水平切分：将一张大表按照一定的切分规则，按照行切分成不同的表或者切分到不同的库中。

切分规则

常用的切分规则有以下几种：

• 按照ID取模：对ID进行取模，余数决定该行数据切分到哪个表或者库中按照日期：按照年月日，将数据切分到不同的表或者库中

• 按照范围：可以对某一列按照范围进行切分，不同的范围切分到不同的表或者数据库中。

切分原则

• 第一原则：能不切分尽量不要切分。

• 第二原则：如果要切分一定要选择合适的切分规则，提前规划好。

• 第三原则：数据切分尽量通过数据冗余或表分组（Table Group）来降低跨库 Join 的可能。

• 第四原则：由于数据库中间件对数据 Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取尽量少使用多表 Join。

分库分表需要解决的问题

分布式事务问题：

• 强一致性事务（同步）

• 最终一致性事务（异步思想）

分布式主键ID问题

• redis incr命令

• 数据库（生成主键）

• UUID

• snowflake算法（https://www.sohu.com/a/232008315_453160）

跨库join问题：

• 通过业务分析，将不同库的join查询拆分成多个select

• 建立全局表（每个库都有一个相同的表）

• 冗余字段（不符合数据库三范式）

• E-R分片（将有ER关系的记录都存储到一个库中） 最多支持跨两张表跨库的join

跨库count、order by、group by问题

分库分表实现技术

• 阿里的TDDL、Cobar

• 基于阿里Cobar开发的Mycat

• 当当网的sharding-jdbc

一、为什么要分库分表

关系型数据库以MySQL为例，单机的存储能力、连接数是有限的，它自身就很容易会成为系统的瓶颈。当单表数据量在百万以里时，我们还可以通过添加从库、优化索引提升性能。一旦数据量朝着千万 以上趋势增长，再怎么优化数据库，很多操作性能仍下降严重。为了减少数据库的负担，提升数据库响 应速度，缩短查询时间，这时候就需要进行分库分表 。

二、如何分库分表

分库分表就是要将大量数据分散到多个数据库中，使每个数据库中数据量小响应速度快，以此来提升数据库整体性能。核心理念就是对数据进行切分（Sharding ），以及切分后如何对数据的快速定位与整合。

针对数据切分类型，大致可以分为：垂直（纵向）切分和水平（横向）切分两种。

1、垂直切分

垂直切分又细分为垂直分库和垂直分表

垂直分库

垂直分库是基于业务分类的，和我们常听到的微服务治理观念很相似，每一个独立的服务都拥有自己的 数据库，需要不同业务的数据需接口调用。而垂直分库也是按照业务分类进行划分，每个业务有独立数 据库，这个比较好理解。

垂直分表

垂直分表 是基于数据表的列为依据切分的，是一种大表拆小表的模式。

例如：一个 order 表有很多字段，把长度较大且访问不频繁的字段，拆分出来创建一个单独的扩展表work_extend进行存储。

数据库是以行为单位将数据加载到内存中，这样拆分以后核心表大多是访问频率较高的字段，而且字段长度也都较短，可以加载更多数据到内存中，增加查询的命中率，减少磁盘IO，以此来提升数据库性能。

优点：

业务间解耦，不同业务的数据进行独立的维护、监控、扩展在高并发场景下，一定程度上缓解了数据库的压力

缺点：

提升了开发的复杂度，由于业务的隔离性，很多表无法直接访问，必须通过接口方式聚合数据，分布式事务管理难度增加。数据库还是存在单表数据量过大的问题，并未根本上解决，需要配合水平切分

2、水平切分

前边说了垂直切分还是会存在单表数据量过大的问题，当我们的应用已经无法在细粒度的垂直切分时，依旧存在单库读写、存储性能瓶颈，这时就要配合水平切分一起了。

水平切分将一张大数据量的表，切分成多个表结构相同，而每个表只占原表一部分数据，然后按不同的条件分散到多个数据库中。

假如一张 order 表有2000万数据，水平切分后出来四个表，order_1 、 order_2 、 order_3 、order_4 ，每张表数据500万，以此类推。

水平切分又分有 库内分表 和 分库分表

库内分表

库内分表虽然将表拆分，但子表都还是在同一个数据库实例中，只是解决了单一表数据量过大的问题， 并没有将拆分后的表分布到不同机器的库上，还在竞争同一个物理机的CPU、内存、网络IO。

分库分表

分库分表则是将切分出来的子表，分散到不同的数据库中，从而使得单个表的数据量变小，达到分布式的效果。

优点：

• 解决高并发时单库数据量过大的问题，提升系统稳定性和负载能力业务系统改造的工作量不是很大

缺点：

• 跨分片的事务一致性难以保证跨库的join关联查询性能较差。

• 扩容的难度和维护量较大，（拆分成几千张子表想想都恐怖）

三、数据该往哪个库的表存？

分库分表以后会出现一个问题，一张表会出现在多个数据库里，到底该往哪个库的表里存呢？

1、根据取值范围

按照 时间区间 或 ID区间 来切分，举个栗子：假如我们切分的是用户表，可以定义每个库的 User表 里只存10000条数据，第一个库 userId 从1 ~ 9999，第二个库10000 ~ 20000，第三个库20001~30000......以此类推。

优点：

• 单表数据量是可控的

• 水平扩展简单只需增加节点即可，无需对其他分片的数据进行迁移

• 能快速定位要查询的数据在哪个库

缺点：

• 由于连续分片可能存在数据热点，如果按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

2、hash取模

hash取模mod（对hash结果取余数 (hash() mod N)）的切分方式比较常见，还拿 User表 举例，对数据库从0到N-1进行编号，对User表 中 userId 字段进行取模，得到余数 i ， i=0 存第一个库， i=1 存第二个库， i=2 存第三个库....以此类推。

这样同一个用户的数据都会存在同一个库里，用userId 作为条件查询就很好定位了

优点：

• 数据分片相对比较均匀，不易出现某个库并发访问的问题

缺点：

• 但这种算法存在一些问题，当某一台机器宕机，本应该落在该数据库的请求就无法得到正确的处理，这时宕掉的实例会被踢出集群，此时算法变成hash(userId) mod N-1，用户信息可能就不再在同一个库中。

四、分库分表后会有哪些问题

1、事务一致性问题

由于表分布在不同库中，不可避免会带来跨库事务问题。一般可使用"XA协议"和"两阶段提交"处理，但是这种方式性能较差，代码开发量也比较大。

通常做法是做到最终一致性的方案，往往不苛求系统的实时一致性，只要在允许的时间段内达到最终一致性即可，可采用事务补偿的方式。

2、分页、排序的坑

日常开发中分页、排序是必备功能，而多库进行查询时limit 分页、 order by 排序，着实让人比较头疼。

分页需按照指定字段进行排序，如果排序字段恰好是分片字段时，通过分片规则就很容易定位到分片的位置；一旦排序字段非分片字段时，就需要先在不同的分片节点中将数据进行排序并返回，然后将不同 分片返回的结果集进行汇总和再次排序，最终返回给用户，过程比较复杂。

3、全局唯一主键问题

由于分库分表后，表中的数据同时存在于多个数据库，而某个分区数据库的自增主键已经无法满足全局 唯一，所以此时一个能够生成全局唯一ID的系统是非常必要的。那么这个全局唯一ID就叫分布式ID 。

五、分库分表工具？

自己开发分库分表工具的工作量是巨大的，好在业界已经有了很多比较成熟的分库分表中间件，我们可以将更多的时间放在业务实现上：

• sharding-jdbc（当当）

• TSharding（蘑菇街）

• Atlas（ 奇虎 360）

• Cobar（阿里巴巴）

• MyCAT（基于Cobar）

• Oceanus（58同 城 ）

• Vitess（谷歌）

Sharding JDBC介绍

什么是ShardingJDBC

官方网站：http://shardingsphere.apache.org/index_zh.html

Apache ShardingSphere(Incubator) 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（规划中）这3款相互独立，却又能够混合部署配 合使用的产品组成。

Sharding JDBC架构

Sharding JDBC核心概念

数据分片

数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中以达到提升性能瓶颈以及可用性的效果。 数据分片的有效手段是对关系型数据库进行分库和分表。

数据分片分为垂直分片和水平分片。

• 垂直分片：按照业务拆分的方式称为垂直分片，又称为纵向拆分，它的核心理念是专库专用。在拆分之前，一个数据库由多个数据表构成，每个表对应着不同的业务。而拆分之后，则是按照业务将表进行归类，分布到不同的数据库中，从而将压力分散至不同的数据库。

• 水平分片：水平分片又称为横向拆分。相对于垂直分片，它不再将数据根据业务逻辑分类，而是通过某个字段（或某几个字段），根据某种规则将数据分散至多个库或表中，每个分片仅包含数据的一部分。

在实际应用中我们常采用水平分片。

SQL

• 逻辑表：水平拆分的数据库（表）的相同逻辑和数据结构表的总称。真实表：在分片的数据库中真实存在的物理表。

• 数据节点：数据分片的最小单元。由数据源名称和数据表组成。

• 绑定表：指分片规则一致的主表和子表。例如： t_order 表和t_order_item 表，均按照order_id 分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将 大大提升。

• 广播表：指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。

分片策略

包含分片键和分片算法。分片键是用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。

分片算法

通过分片算法将数据分片，支持通过= 、 BETWEEN 和IN 分片。分片算法需要应用方开发者自行实现， 可实现的灵活度非常高。

• 精确分片算法（PreciseShardingAlgorithm）用于处理使用单一键作为分片键的=与IN进行分片的 场景。需要配合StandardShardingStrategy使用。

• 范围分片算法（RangeShardingAlgorithm）用于处理使用单一键作为分片键的BETWEEN AND进行分片的场景。需要配合StandardShardingStrategy使用。

• 复合分片算法（ComplexKeysShardingAlgorithm）用于处理使用多键作为分片键进行分片的场 景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用。

• Hint分片算法（HintShardingAlgorithm）用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用。

目前提供4种分片策略 ：

• 标准分片策略（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代码开发。

• 行表达式的使用非常直观，只需要在配置中使用${ expression } 或$->{ expression } 标识行表达式即可。 目前支持数据节点和分片算法这两个部分的配置。行表达式的内容使用的是Groovy的语法，Groovy能够支持的所有操作，行表达式均能够支持。

• Hint分片策略（HintShardingStrategy）通过Hint而非SQL解析的方式分片的策略。对于分片字段 非SQL决定，而由其他外置条件决定的场景，可使用SQL Hint灵活的注入分片字段。例：内部系统，按照员工登录主键分库，而数据库中并无此字段。SQL Hint支持通过Java API和SQL注释(待实现)两种方式使用。

配置

• 分片规则：分片规则配置的总入口。包含数据源配置、表配置、绑定表配置以及读写分离配置等。

• 数据源配置：真实数据源列表，结合数据库连接池使用

• 表配置：逻辑表名称、数据节点与分表规则的配置。

• 数据节点配置：用于配置逻辑表与真实表的映射关系。可分为均匀分布和自定义分布两种形式。 分片策略配置:对于分片策略存有数据源分片策略和表分片策略两种维度。

数据源分片策略

对应于DatabaseShardingStrategy。用于配置数据被分配的目标数据源。

表分片策略

对应于TableShardingStrategy。用于配置数据被分配的目标表，该目标表存在与该数据的目标数据源内。故表分片策略是依赖与数据源分片策略的结果的。

自增主键生成策略,通过在客户端生成自增主键替换以数据库原生自增主键的方式，做到分布式主键无重复。(UUID和雪花算法)

读写分离

与将数据根据分片键打散至各个数据节点的水平分片不同，读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。读写分离的数据节点中的数据内容是一致的，而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用，能够更加有效的提升系统性能。

编排治理

提供注册中心、配置动态化、数据库熔断禁用、调用链路等治理能力。

分布式事务

• 支持数据分片后的跨库XA事务

• 两阶段提交保证操作的原子性和数据的强一致性

• 服务宕机重启后，提交/回滚中的事务可自动恢复

• 同时支持XA和非XA的连接池

• 提供spring-boot和namespace的接入端

Sharding JDBC对多数据库的支持

Sharding JDBC安装

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

<artifactId>sharding-jdbc-core</artifactId>

<version>3.0.0</version>

ShardingJDBC核心组件

解析引擎

解析过程分为词法解析和语法解析。 词法解析器用于将SQL拆解为不可再分的原子符号，称为Token。并根据不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符。 再使用语法解析器将SQL转换为抽象语法树。

第三代SQL解析器则从3.0.x版本开始，ShardingSphere尝试使用ANTLR作为SQL解析的引擎。

ANTLR是指可以根据输入自动生成语法树并可视化的显示出来的开源语法分析器

路由引擎

据解析上下文匹配数据库和表的分片策略，并生成路由路径。 对于携带分片键的SQL，根据分片键的不同可以划分为单片路由(分片键的操作符是等号)、多片路由(分片键的操作符是IN)和范围 路由(分片键的操作符是BETWEEN)。 不携带分片键的SQL则采用广播路由。

用于根据分片键进行路由的场景，又细分为直接路由、标准路由和笛卡尔积路由这3种类型。

广播路由，对于不携带分片键的SQL，则采取广播路由的方式。根据SQL类型又可以划分为全库表路由、全库路由、全实例路由、单播路由和阻断路由这5种类型。

改写引擎

面向逻辑库与逻辑表书写的SQL，并不能够直接在真实的数据库中执行，SQL改写用于将逻辑SQL 改写为在真实数据库中可以正确执行的SQL。 它包括正确性改写和优化改写两部分。

正确性改写:在包含分表的场景中，需要将分表配置中的逻辑表名称改写为路由之后所获取的真实表名称。仅分库则不需要表名称的改写。除此之外，还包括补列和分页信息修正等内容。

标识符改写:需要改写的标识符包括表名称、索引名称以及Schema名称。

执行引擎

ShardingSphere采用一套自动化的执行引擎，负责将路由和改写完成之后的真实SQL安全且高效发送到底层数据源执行。 它不是简单地将SQL通过JDBC直接发送至数据源执行；也并非直接将执行请求放入线程池去并发执行。它更关注平衡数据源连接创建以及内存占用所产生的消耗，以及最 大限度地合理利用并发等问题。 执行引擎的目标是自动化的平衡资源控制与执行效率。

执行引擎分为准备和执行两个阶段。准备阶段用于准备执行的数据。它分为结果集分组和执行单元 创建两个步骤。该阶段用于真正的执行SQL，它分为分组执行和归并结果集生成两个步骤。

归并引擎

将从各个数据节点获取的多数据结果集，组合成为一个结果集并正确的返回至请求客户端，称为结果归并。

ShardingSphere支持的结果归并从功能上分为遍历、排序、分组、分页和聚合5种类型，它们是组合而非互斥的关系。

从结构划分，可分为流式归并、内存归并和装饰者归并。流式归并和内存归并是互斥的，装饰者归并可以在流式归并和内存归并之上做进一步的处理。