MySQL分库分表
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
垂直（纵向）切分
垂直切分的优点： • 解决业务系统层面的耦合，业务清晰 • 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等 • 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈 缺点： • 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度 • 分布式事务处理复杂 • 依然存在单表数据量过大的问题（需要水平切分）
MySQL分库分表
徐旭
目录
一、背景 二、 Why Not NoSQL/NewSQL? 三、 Why Not 分区? 四、 Why 分库分表? 五、如何数据迁移 六、分库分表中间件 七、分库分表带来的问题
背景
移动互联网时代，海量的用户每天产生海量的数量， 比如：
• 用户表 • 订单表 • 交易流水表
所有数据还在一个表中，但物理存储根据一定的规则放在不同的文件中。这个是mysql支持 的功能，业务代码无需改动。
Why Not 分区?
表分区的类型
• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。 • LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某
Why Not 分区?
看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展 咧？
• 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁 • 一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难 • 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，开发写了一个sql，都不确定
Hash路由
优点： •数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈。 缺点： •数据迁移复杂 •容易面临跨分片查询的复杂问题。比如上例中，如果频繁用 到的查询条件中不带org_id时，将会导致无法定位数据库， 从而需要同时向4个库发起查询，再在内存中合并数据，取最 小集返回给应用，分库反而成为拖累。 •后期分片集群扩容时，需要迁移旧的数据（使用一致性hash 算法能较好的避免这个问题）；
综合了垂直和水平拆分方式的一种混 合方式，垂直拆分把不同类型的数据存储 到不同库中，再结合水平拆分，使单表数 据量保持在合理范围内，提升总 TPS，提 升性能。
Why 分库分表?
水平（横向）切分
几种典型的分片规则： 范围路由 Hash路由 配置路由
Why 分库分表?
范围路由
按照时间区间或ID区间来切分。
目前绝大部分公司的核心数据都是：以RDBMS存储为主，NOSQL/NEWSQL存储为辅！互联网 公司又以MySQL为主，国企&银行等不差钱的企业以Oracle/DB2为主！ NoSQL/NewSQL宣传 的无论多牛逼，就现在各大公司对它的定位，都是RDBMS的补充，而不是取而代之！
Why Not 分区?
个值来进行选择。 • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表
中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表 达式。 • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务 器提供其自身的哈希函数。必须有一列或多列包含整数值。
Why Not 分区?
举个分区常用的例子：RANGE分区
基于属于一个给定连续区间的列值，把多行分 配给分区。这些区间要连续且不能相互重叠， 使用VALUES LESS THAN操作符来进行定义。
按照这种分区方案，在商店1到5工作的雇员相 对应的所有行被保存在分区P0中，商店6到10 的雇员保存在P1中，依次类推。注意，每个分 区都是按顺序进行定义，从最低到最高。类似 于”switch … case”语句
Why 分库分表?
Hash路由
分库分表 例如：将 amazon_order表根据 org_id 字段 切分到10个库中，余数为0的放到第一个库，余 数为1的放到第二个库，以此类推。这样同一个 用户的数据会分散到同一个库中，如果查询条 件带有org_id字段，则可明确定位到相应库去 查询。
Why 分库分表?
分库分表根据其切分类型，可以分为两种方式： 垂直（纵向）切分 水平（横向）切分
Why 分库分表?
垂直（纵向）切分
垂直切分常见有垂直分库和垂直分表两种。
垂直分库就是根据业务耦合性，将关联度 低的不同表存储在不同的数据库。做法与 大系统拆分为多个小系统类似，按业务分 类进行独立划分。与“微服务治理”的做 法相似，每个微服务使用单独的一个数据 库。
范围路由
优点： • 单表大小可控； • 可以随着数据的增加平滑地扩充新表，无需对其他分片的数据进行迁移； • 使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询
的问题。 缺点： • 热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最
• 迁移到新机器上最快的方式是用xtrabackup • 迁移到已有mysql库的机器上只能用mysqldump是最快的 • 覆盖date文件（不推荐）
ps: • 用官方的mysqldump把数据都缩减在一行里，导入耗时可以缩减3、4倍（--extended-insert ） • 像阿里的云数据库是没提供文件操作权限的,不支持into outfile等操作
Why 分库分表?
垂直（纵向）切分
垂直分表在日常开发和设计中比较常 见，通俗的说法叫做“大表拆小表”，拆 分是基于关系型数据库中的“列”（字段） 进行的。通常情况，某个表中的字段比较 多，可以新建立一张“扩展表”，将不经 常使用或者长度较大的字段拆分出去放到 “扩展表”中。
Why 分库分表?
mysql是怎么玩的，不太可控
Why 分库分表?
最后要介绍的就是目前互联网行业处理海量数据 的通用方法：分库分表
把一个很大的库（表）的数据分到几个库（表） 中，每个库（表）的结构都相同，但他们可能分 布在不同的mysql实例，甚至不同的物理机器上， 以达到降低单库（表）数据量，提高访问性能的 目的。
Why 来自百度文库库分表?
水平（横向）切分
水平切分是把单表按某个规则把数据分散 到多个表。
当一个应用难以再细粒度的垂直切分，或切 分后数据量行数巨大，存在单库读写、存储性 能瓶颈，这时候就需要进行水平切分了。
水平拆分可以降低单表数据量，让每个单表 的数据量保持在一定范围内，从而提升单表读 写性能。但水平拆分后，同一业务数据分布在 不同的表或库中，可能需要把单表事务改成跨 表事务，需要转变数据统计方式等。
Why 分库分表?
水平（横向）切分
水平切分的优点： • 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力 • 应用端改造较小，不需要拆分业务模块 缺点： • 跨分片的事务一致性难以保证 • 跨库的join关联查询性能较差 • 数据多次扩展难度和维护量极大
Why 分库分表?
垂直水平切分
• 跨节点关联查询 join 问题 • 跨节点分页、排序、函数问题 • 事务一致性问题 • 数据迁移、扩容问题
分库分表带来的问题
跨节点关联查询 join 问题
切分之前，系统中很多列表和详情页所需的数据可以通过sql join来完成。而切分之后，数据可能 分布在不同的节点上，此时join带来的问题就比较麻烦了，考虑到性能，尽量避免使用join查询。
分库分表中间件
PROXY模式
PROXY模式代表有阿里的cobar，民间组织的 MyCAT。
但是，无论是CLIENT模式，还是PROXY模式。 几个核心的步骤是一样的：SQL解析，重写，路 由，执行，结果归并。
分库分表带来的问题
分库分表能有效的缓解单机和单库带来的性能瓶颈和压力，突破网络IO、硬件资源、连接数的瓶颈， 同时也带来了一些问题。 下面简要描述一些技术挑战以及对应的解决思路。
其他比如网易，58，京东等公司都有自研的中间件。总之各自为战，也可以说是百花齐放。
分库分表中间件
但是这么多的分库分表中间件全部可以归结为两 大类型：
• CLIENT模式 • PROXY模式
CLIENT模式
CLIENT模式代表有阿里的TDDL，开源社区的 sharding-jdbc（sharding-jdbc的3.x版本即 sharding-sphere已经支持了proxy模式）
近时间段内的数据，可能会被频繁的读写；而有些分片存储的历史数据，则很少被查询。 • 可能存在数据分布不均
Why 分库分表?
Hash路由
采用hash对sharding column取模。 库内分表 例如：将 amazon_order表根据 org_id 字段 切分到10张表中，余数为0的放到第一张表，余 数为1的放到第二张，以此类推。这样同一个用 户的数据会分散到同一个库中，如果查询条件 带有org_id字段，则可明确定位到相应表去查 询。
以支付宝用户为例，8亿；微信用户更是10亿。订 单表更夸张，比如美团外卖，每天都是几千万的订单。 淘宝的历史订单总量应该百亿，甚至千亿级别，这些海 量数据远不是一张表能Hold住的。事实上MySQL单表可 以存储10亿级数据，只是这时候性能比较差，业界公认 MySQL单表容量在1KW以下是最佳状态，因为这时它的 BTREE索引树高在3~5之间
Why 分库分表?
配置路由
使用独立的表（或者配置文件）记录路由配置信息
优点： • 使用起来简单灵活，扩充表时只需迁移指定的数据，然后修改路由表 缺点： • 必须多查询一次，影响整体性能 • 路由表本身太多影响系统整体性能
如何数据迁移？
数据迁移一般分为停机迁移、平滑迁移
迁移数据的常用方法：
我们再看分区表方案。了解这个方案之前，先了解它的原理：
分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直 接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用 相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的 角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分 区表的一部分。
背景
既然一张表无法搞定，那么就想办法将数据放到多个地方，目前比较普遍的方案有3个： 1. 分区 2. 分库分表 3. NoSQL/ NewSQL
说明：只分库，或者只分表，或者分库分表融合方案都统一认为是分库分表方案，因为分库/分表 只是一种特殊的分库分表而已。NoSQL比较具有代表性的是MongoDB，es。NewSQL比较具有 代表性的是TiDB。
如何数据迁移？
水平拆分数据迁移可以分为三个阶段：
• 新数据数据双写 • 历史数据导入补平 • 切换新旧数据库
新数据数据双写
• 数据库双写（事务成功以老模型为准），查询走老 模型。
• 每日job数据对账，并将差异补平。 • 通过job导历史数据。
分库分表中间件
虽然大家都是采用分库分表方案来处理海量核心数据，但是还没有一个一统江湖的中间件，这 里列举一些有一定知名度的分库分表中间件： • 阿里的TDDL，DRDS和cobar • 开源社区的sharding-jdbc（3.x已经更名为sharding-sphere） • 民间组织的MyCAT • 360的Atlas • 美团的zebra
例如：按日期将不同月甚至是日的数据分散到 不同的库中；将userId为1~9999的记录分到第 一个库，10000~20000的分到第二个库，以此 类推。某种意义上，某些系统中使用的"冷热数 据分离"，将一些使用较少的历史数据迁移到其 他库中，业务功能上只提供热点数据的查询， 也是类似的实践。
Why 分库分表?
Why Not NoSQL/NewSQL?
首先，为什么不选择第三种方案NoSQL/NewSQL ，我认为主要是RDBMS有以下几个 优点： RDBMS生态完善 RDBMS绝对稳定 RDBMS的事务特性
NoSQL/NewSQL作为新生儿，在我们把可靠性当做首要考察对象时，它是无法与RDBMS相 提并论的。RDBMS发展几十年，只要有软件的地方，它都是核心存储的首选。
垂直切分的优点： • 解决业务系统层面的耦合，业务清晰 • 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等 • 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈 缺点： • 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度 • 分布式事务处理复杂 • 依然存在单表数据量过大的问题（需要水平切分）
MySQL分库分表
徐旭
目录
一、背景 二、 Why Not NoSQL/NewSQL? 三、 Why Not 分区? 四、 Why 分库分表? 五、如何数据迁移 六、分库分表中间件 七、分库分表带来的问题
背景
移动互联网时代，海量的用户每天产生海量的数量， 比如：
• 用户表 • 订单表 • 交易流水表
所有数据还在一个表中，但物理存储根据一定的规则放在不同的文件中。这个是mysql支持 的功能，业务代码无需改动。
Why Not 分区?
表分区的类型
• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。 • LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某
Why Not 分区?
看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展 咧？
• 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁 • 一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难 • 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，开发写了一个sql，都不确定
Hash路由
优点： •数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈。 缺点： •数据迁移复杂 •容易面临跨分片查询的复杂问题。比如上例中，如果频繁用 到的查询条件中不带org_id时，将会导致无法定位数据库， 从而需要同时向4个库发起查询，再在内存中合并数据，取最 小集返回给应用，分库反而成为拖累。 •后期分片集群扩容时，需要迁移旧的数据（使用一致性hash 算法能较好的避免这个问题）；
综合了垂直和水平拆分方式的一种混 合方式，垂直拆分把不同类型的数据存储 到不同库中，再结合水平拆分，使单表数 据量保持在合理范围内，提升总 TPS，提 升性能。
Why 分库分表?
水平（横向）切分
几种典型的分片规则： 范围路由 Hash路由 配置路由
Why 分库分表?
范围路由
按照时间区间或ID区间来切分。
目前绝大部分公司的核心数据都是：以RDBMS存储为主，NOSQL/NEWSQL存储为辅！互联网 公司又以MySQL为主，国企&银行等不差钱的企业以Oracle/DB2为主！ NoSQL/NewSQL宣传 的无论多牛逼，就现在各大公司对它的定位，都是RDBMS的补充，而不是取而代之！
Why Not 分区?
个值来进行选择。 • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表
中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表 达式。 • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务 器提供其自身的哈希函数。必须有一列或多列包含整数值。
Why Not 分区?
举个分区常用的例子：RANGE分区
基于属于一个给定连续区间的列值，把多行分 配给分区。这些区间要连续且不能相互重叠， 使用VALUES LESS THAN操作符来进行定义。
按照这种分区方案，在商店1到5工作的雇员相 对应的所有行被保存在分区P0中，商店6到10 的雇员保存在P1中，依次类推。注意，每个分 区都是按顺序进行定义，从最低到最高。类似 于”switch … case”语句
Why 分库分表?
Hash路由
分库分表 例如：将 amazon_order表根据 org_id 字段 切分到10个库中，余数为0的放到第一个库，余 数为1的放到第二个库，以此类推。这样同一个 用户的数据会分散到同一个库中，如果查询条 件带有org_id字段，则可明确定位到相应库去 查询。
Why 分库分表?
分库分表根据其切分类型，可以分为两种方式： 垂直（纵向）切分 水平（横向）切分
Why 分库分表?
垂直（纵向）切分
垂直切分常见有垂直分库和垂直分表两种。
垂直分库就是根据业务耦合性，将关联度 低的不同表存储在不同的数据库。做法与 大系统拆分为多个小系统类似，按业务分 类进行独立划分。与“微服务治理”的做 法相似，每个微服务使用单独的一个数据 库。
范围路由
优点： • 单表大小可控； • 可以随着数据的增加平滑地扩充新表，无需对其他分片的数据进行迁移； • 使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询
的问题。 缺点： • 热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最
• 迁移到新机器上最快的方式是用xtrabackup • 迁移到已有mysql库的机器上只能用mysqldump是最快的 • 覆盖date文件（不推荐）
ps: • 用官方的mysqldump把数据都缩减在一行里，导入耗时可以缩减3、4倍（--extended-insert ） • 像阿里的云数据库是没提供文件操作权限的,不支持into outfile等操作
Why 分库分表?
垂直（纵向）切分
垂直分表在日常开发和设计中比较常 见，通俗的说法叫做“大表拆小表”，拆 分是基于关系型数据库中的“列”（字段） 进行的。通常情况，某个表中的字段比较 多，可以新建立一张“扩展表”，将不经 常使用或者长度较大的字段拆分出去放到 “扩展表”中。
Why 分库分表?
mysql是怎么玩的，不太可控
Why 分库分表?
最后要介绍的就是目前互联网行业处理海量数据 的通用方法：分库分表
把一个很大的库（表）的数据分到几个库（表） 中，每个库（表）的结构都相同，但他们可能分 布在不同的mysql实例，甚至不同的物理机器上， 以达到降低单库（表）数据量，提高访问性能的 目的。
Why 来自百度文库库分表?
水平（横向）切分
水平切分是把单表按某个规则把数据分散 到多个表。
当一个应用难以再细粒度的垂直切分，或切 分后数据量行数巨大，存在单库读写、存储性 能瓶颈，这时候就需要进行水平切分了。
水平拆分可以降低单表数据量，让每个单表 的数据量保持在一定范围内，从而提升单表读 写性能。但水平拆分后，同一业务数据分布在 不同的表或库中，可能需要把单表事务改成跨 表事务，需要转变数据统计方式等。
Why 分库分表?
水平（横向）切分
水平切分的优点： • 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力 • 应用端改造较小，不需要拆分业务模块 缺点： • 跨分片的事务一致性难以保证 • 跨库的join关联查询性能较差 • 数据多次扩展难度和维护量极大
Why 分库分表?
垂直水平切分
• 跨节点关联查询 join 问题 • 跨节点分页、排序、函数问题 • 事务一致性问题 • 数据迁移、扩容问题
分库分表带来的问题
跨节点关联查询 join 问题
切分之前，系统中很多列表和详情页所需的数据可以通过sql join来完成。而切分之后，数据可能 分布在不同的节点上，此时join带来的问题就比较麻烦了，考虑到性能，尽量避免使用join查询。
分库分表中间件
PROXY模式
PROXY模式代表有阿里的cobar，民间组织的 MyCAT。
但是，无论是CLIENT模式，还是PROXY模式。 几个核心的步骤是一样的：SQL解析，重写，路 由，执行，结果归并。
分库分表带来的问题
分库分表能有效的缓解单机和单库带来的性能瓶颈和压力，突破网络IO、硬件资源、连接数的瓶颈， 同时也带来了一些问题。 下面简要描述一些技术挑战以及对应的解决思路。
其他比如网易，58，京东等公司都有自研的中间件。总之各自为战，也可以说是百花齐放。
分库分表中间件
但是这么多的分库分表中间件全部可以归结为两 大类型：
• CLIENT模式 • PROXY模式
CLIENT模式
CLIENT模式代表有阿里的TDDL，开源社区的 sharding-jdbc（sharding-jdbc的3.x版本即 sharding-sphere已经支持了proxy模式）
近时间段内的数据，可能会被频繁的读写；而有些分片存储的历史数据，则很少被查询。 • 可能存在数据分布不均
Why 分库分表?
Hash路由
采用hash对sharding column取模。 库内分表 例如：将 amazon_order表根据 org_id 字段 切分到10张表中，余数为0的放到第一张表，余 数为1的放到第二张，以此类推。这样同一个用 户的数据会分散到同一个库中，如果查询条件 带有org_id字段，则可明确定位到相应表去查 询。
以支付宝用户为例，8亿；微信用户更是10亿。订 单表更夸张，比如美团外卖，每天都是几千万的订单。 淘宝的历史订单总量应该百亿，甚至千亿级别，这些海 量数据远不是一张表能Hold住的。事实上MySQL单表可 以存储10亿级数据，只是这时候性能比较差，业界公认 MySQL单表容量在1KW以下是最佳状态，因为这时它的 BTREE索引树高在3~5之间
Why 分库分表?
配置路由
使用独立的表（或者配置文件）记录路由配置信息
优点： • 使用起来简单灵活，扩充表时只需迁移指定的数据，然后修改路由表 缺点： • 必须多查询一次，影响整体性能 • 路由表本身太多影响系统整体性能
如何数据迁移？
数据迁移一般分为停机迁移、平滑迁移
迁移数据的常用方法：
我们再看分区表方案。了解这个方案之前，先了解它的原理：
分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直 接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用 相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的 角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分 区表的一部分。
背景
既然一张表无法搞定，那么就想办法将数据放到多个地方，目前比较普遍的方案有3个： 1. 分区 2. 分库分表 3. NoSQL/ NewSQL
说明：只分库，或者只分表，或者分库分表融合方案都统一认为是分库分表方案，因为分库/分表 只是一种特殊的分库分表而已。NoSQL比较具有代表性的是MongoDB，es。NewSQL比较具有 代表性的是TiDB。
如何数据迁移？
水平拆分数据迁移可以分为三个阶段：
• 新数据数据双写 • 历史数据导入补平 • 切换新旧数据库
新数据数据双写
• 数据库双写（事务成功以老模型为准），查询走老 模型。
• 每日job数据对账，并将差异补平。 • 通过job导历史数据。
分库分表中间件
虽然大家都是采用分库分表方案来处理海量核心数据，但是还没有一个一统江湖的中间件，这 里列举一些有一定知名度的分库分表中间件： • 阿里的TDDL，DRDS和cobar • 开源社区的sharding-jdbc（3.x已经更名为sharding-sphere） • 民间组织的MyCAT • 360的Atlas • 美团的zebra
例如：按日期将不同月甚至是日的数据分散到 不同的库中；将userId为1~9999的记录分到第 一个库，10000~20000的分到第二个库，以此 类推。某种意义上，某些系统中使用的"冷热数 据分离"，将一些使用较少的历史数据迁移到其 他库中，业务功能上只提供热点数据的查询， 也是类似的实践。
Why 分库分表?
Why Not NoSQL/NewSQL?
首先，为什么不选择第三种方案NoSQL/NewSQL ，我认为主要是RDBMS有以下几个 优点： RDBMS生态完善 RDBMS绝对稳定 RDBMS的事务特性
NoSQL/NewSQL作为新生儿，在我们把可靠性当做首要考察对象时，它是无法与RDBMS相 提并论的。RDBMS发展几十年，只要有软件的地方，它都是核心存储的首选。