MySQL分库分表

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