数据库——MySQL分库分表的演进以及引申
https://blog.csdn.net/wolf_love666/article/details/82773300
1、分库分表相关术语
读写分离: 不同的数据库，同步相同的数据，分别只负责数据的读和写；
分区: 指定分区列表达式，把记录拆分到不同的区域中(必须是同一服务器，可以是不同硬盘)，应用看来还是同一张表，没有变化；
分库：一个系统的多张数据表，存储到多个数据库实例中；
分表: 对于一张多行(记录)多列(字段)的二维数据表，又分两种情形：
(1) 垂直分表: 竖向切分，不同分表存储不同的字段，可以把不常用或者大容量、或者不同业务的字段拆分出去；
(2) 水平分表(最复杂): 横向切分，按照特定分片算法，不同分表存储不同的记录。
2、MySQL为什么分库分表
当一张表的数据达到几千万时，你查询一次所花的时间会变多，如果有联合查询的话，可能会死在那儿。分表的目的就在于此，减小数据库的负担，缩短查询时间。目前我们系统将近20亿数据每张表最大的接近519w条/表，每条数据大约3k，1131M将近1G的表数据。查询经常超时，单条SQLcount(*)查询时间达到最大260ms，0.26s。标准是超过0.1s的数据为慢SQL。
3、分库分表会遗留的问题
事务问题
跨表join，聚合查询order by，group by等问题
数据倾斜问题
分库分表下的主键id问题
业务上涨，伸缩性问题
2.1、事务问题
前提了解：单条SQL即为一个事务
Spring下的事务的传播性（7）
Spring下的默认超时设置（TIMEOUT_DEFAULT）
PROPAGATION_REQUIRED（默认事务传播机制）
支持当前交易; 如果不存在则创建一个新的
PROPAGATION_REQUIRES_NEW
创建一个新事务，暂停当前事务（如果存在）
PROPAGATION_NESTED
如果存在当前事务，则在嵌套事务中执行,异常回滚到上个事务的事务点
PROPAGATION_SUPPORT
支持当前交易; 如果不存在则执行非事务性。
PROPAGATION_NOT_SUPPORT
不支持当前交易; 而是总是以非交易方式执行
PROPAGATION_NEVER
不支持当前交易; 如果当前事务存在则抛出异常
PROPAGATION_MANDATORY
支持当前交易; 如果不存在当前事务则抛出异常。
Spring下的事务5种隔离级别：
ISOLATION_DEFAULT
使用基础数据存储的默认隔离级别。
ISOLATION_READ_COMMITTED
表示禁止脏读; 可以发生不可重复的读取和幻像读取。
ISOLATION_READ_UNCOMMITTED
表示可能发生脏读，不可重复读和幻像读。
ISOLATION_REPEATABLE_READ
表示防止脏读和不可重复读; 可以发生幻像读取。
ISOLATION_SERIALIZABLE
表示禁止脏读，不可重复读和幻像读
酸（ACID）碱(BASE)平衡理论
了解单表下的事务符合ACID模型。
A(Atomicity)原子性
事务通常由多个语句组成。原子性保证每个事务被视为单个“单元”，它可以完全成功，也可以完全失败：如果构成事务的任何语句都无法完成，整个事务将失败并且数据库保持不变。原子系统必须保证每种情况下的原子性，包括电源故障，错误和崩溃。
C(Consistency)一致性
一致性确保事务只能将数据库从一个有效状态带到另一个有效状态，维护数据库不变量：写入数据库的任何数据必须根据所有定义的规则有效，包括约束，级联，触发器及其任何组合。这可以防止非法事务导致数据库损坏，但不能保证事务正确。
分布式环境下如何保证一致性？
一致性，可用性，容错性------其实考察CAP原理。
基本可用性、软状态、最终一致性-----------BASE理论
I(Isolation)隔离性
事务通常同时执行（例如，同时读取和写入多个表）。隔离确保事务的并发执行使数据库处于与按顺序执行事务时获得的状态相同的状态。隔离是并发控制的主要目标; 根据使用的方法，不完整交易的影响甚至可能对其他交易不可见。
隔离性的四种和造成的三种影响？
mysql四钟隔离级别
默认的是可重复读，这样就会导致幻读，那么我们实际应用中又是如何避免幻读的呢？
多个事务提交执行如何保证隔离性？
如果是单台应用则spring事务控制就可以了参考上面的spring事务申明传播特性和隔离性控制。
如果是分布式应用则这里需要考虑的是分布式事务。
暂时引用下链接：https://www.e-learn.cn/content/qita/1659193
D(Durability)持久性
持久性保证一旦提交了事务，即使系统出现故障（例如停电或崩溃），它也将保持提交状态。这通常意味着已完成的事务（或其影响）记录在非易失性存储器中。
引申mysql如何保证的持久性？
redo log缓冲区
redo log缓冲区是一块内存区域，保存将要写入redo log的数据。
mysql 崩溃恢复是需要redo log的。
redo log缓冲区大小由innodb_log_buffer_size配置选项定义。
redo log缓冲区会定期把内存中的回滚日志刷到磁盘上。一个大的redo log缓冲区意味着允许大事务运行，而无需在事务提交之前将redo log写入磁盘。因此，如果您有更新，插入或删除多行的事务，则使用更大的redo log缓冲区可节省磁盘I/O。
redo log缓存
innodb_flush_log_at_commit过程
2.2、跨表join，聚合查询order by，group by等问题
将原本处于mysql执行的跨表查询以及聚合查询等操作提前到网关层进行聚合。比如说现在你有这样一条SQL: select * from tableXX order by create_time desc limit 0,10;
则会从512张表中每张表都获取10条数据，然后再网关层就会出现512*10条数据，然后重新排序聚合提取10条数据返回给应用。带来的就是性能响应时间增加。
2.3、数据倾斜问题
数据倾斜来源于固定hash算法，一般如果设定值比如M条数据，N张表那么每张表的合理数据应该为M/N。但是我们知道往往有些现实问题就不是那么理想的状态。比如我这个例子：
固定Hash算法：
为了确保唯一值，采用来活动id和运营人员id取模这样，运营人员如果下面报名的商品很多，则导致该商品表下面的商品数量会急剧增多，而这个世界往往经常又是那么巧合，你越优秀，什么事情都越顺利。你越倒霉，什么破事都会找你。而数据库这里也存在这个问题，假设A运营人员一直优秀，那么他的数据量只能越来越多，这样就会导致某个表的数据量占据第一名与最后一名表的数据量偏差可能相差60%甚至更多。
一致性hash算法：
定义：
关于一致性Hash算法，先构造一个长度为2的32次方的整数环（这个环被称之为一致性Hsah环），根据节点名称的Hash值将服务器节点放置在这个Hash环上，然后根据数据的Key值计算得到其Hash值（其分布也为0-2的32次方），接着在Hash环上顺时针查找距离这个key的Hash值最近的服务器接到哪，完成Key到服务器的映射查找。
普通的余数hash（hash(比如用户id)%服务器机器数）算法伸缩性很差，当新增或者下线服务器机器时候，用户id与服务器的映射关系会大量失效。一致性hash则利用hash环对其进行了改进。
为了能直观的理解一致性hash原理，这里结合一个简单的例子来讲解，512张表的问题，地址为ip0~ip511，由于太多则以ip1/ip2/ip3/ip4来说明。
一致性hash是首先计算四个ip地址对应的hash值
hash(ip1),hash(ip2),hash(ip3),hash(ip3)，计算出来的hash值是0~最大正整数直接的一个值，这四个值在一致性hash环上呈现如下图：
一致性hash
hash环上顺时针从整数0开始，一直到最大正整数，我们根据四个ip计算的hash值肯定会落到这个hash环上的某一个点，至此我们把服务器的四个ip映射到了一致性hash环
当用户在客户端进行请求时候，首先根据hash(用户id)计算路由规则（hash值），然后看hash值落到了hash环的那个地方，根据hash值在hash环上的位置顺时针找距离最近的ip作为路由ip.
一致性hash过程
如上图可知user1,user2的请求会落到服务器ip2进行处理，User3的请求会落到服务器ip3进行处理，user4的请求会落到服务器ip4进行处理，user5,user6的请求会落到服务器ip1进行处理。
下面考虑当ip2的服务器挂了的时候会出现什么情况？
当ip2的服务器挂了的时候，一致性hash环大致如下图：
根据顺时针规则可知user1,user2的请求会被服务器ip3进行处理，而其它用户的请求对应的处理服务器不变，也就是只有之前被ip2处理的一部分用户的映射关系被破坏了，并且其负责处理的请求被顺时针下一个节点委托处理。
hash过程
下面考虑当新增机器的时候会出现什么情况？
当新增一个ip5的服务器后，一致性hash环大致如下图：
根据顺时针规则可知之前user1的请求应该被ip1服务器处理，现在被新增的ip5服务器处理，其他用户的请求处理服务器不变，也就是新增的服务器顺时针最近的服务器的一部分请求会被新增的服务器所替代。
一致性hash环
一致性hash的特性
单调性(Monotonicity)，单调性是指如果已经有一些请求通过哈希分派到了相应的服务器进行处理，又有新的服务器加入到系统中时候，应保证原有的请求可以被映射到原有的或者新的服务器中去，而不会被映射到原来的其它服务器上去。 这个通过上面新增服务器ip5可以证明，新增ip5后，原来被ip1处理的user6现在还是被ip1处理，原来被ip1处理的user5现在被新增的ip5处理。
分散性(Spread)：分布式环境中，客户端请求时候可能不知道所有服务器的存在，可能只知道其中一部分服务器，在客户端看来他看到的部分服务器会形成一个完整的hash环。如果多个客户端都把部分服务器作为一个完整hash环，那么可能会导致，同一个用户的请求被路由到不同的服务器进行处理。这种情况显然是应该避免的，因为它不能保证同一个用户的请求落到同一个服务器。所谓分散性是指上述情况发生的严重程度。好的哈希算法应尽量避免尽量降低分散性。 一致性hash具有很低的分散性
平衡性(Balance)：平衡性也就是说负载均衡，是指客户端hash后的请求应该能够分散到不同的服务器上去。一致性hash可以做到每个服务器都进行处理请求，但是不能保证每个服务器处理的请求的数量大致相同，如下图
服务器ip1,ip2,ip3经过hash后落到了一致性hash环上，从图中hash值分布可知ip1会负责处理大概80%的请求，而ip2和ip3则只会负责处理大概20%的请求，虽然三个机器都在处理请求，但是明显每个机器的负载不均衡，这样称为一致性hash的倾斜，虚拟节点的出现就是为了解决这个问题。
一致性hash环
当服务器节点比较少的时候会出现上节所说的一致性hash倾斜的问题，一个解决方法是多加机器，但是加机器是有成本的，那么就加虚拟节点，比如上面三个机器，每个机器引入1个虚拟节点后的一致性hash环的图如下：
其中ip1-1是ip1的虚拟节点，ip2-1是ip2的虚拟节点，ip3-1是ip3的虚拟节点。
一致性hash环
可知当物理机器数目为M，虚拟节点为N的时候，实际hash环上节点个数为M*N。比如当客户端计算的hash值处于ip2和ip3或者处于ip2-1和ip3-1之间时候使用ip3服务器进行处理。
我们使用虚拟节点后的图看起来比较均衡，但是如果生成虚拟节点的算法不够好很可能会得到下面的环：
可知每个服务节点引入1个虚拟节点后，情况相比没有引入前均衡性有所改善，但是并不均衡。
一致性hash环
均衡的一致性hash应该是如下图：
均匀一致性hash的目标是如果服务器有N台，客户端的hash值有M个，那么每个服务器应该处理大概M/N个用户的。也就是每台服务器负载尽量均衡
均衡的一致性hash
引申Q：一致性Hash算法不足以及如何修改。
上面的一致性Hash倾斜以及虚拟节点，以及如果节点非常庞大的情况
引申Q：数据节点再增加的情况下，几十万几百万的节点，一致性hash算法如何优化？如何在动态的网络拓扑中分布存储和路由。每个节点仅需维护少量相邻节点的信息，并且在节点加入/退出系统时，仅有相关的少量节点参与到拓扑的维护中。所有这一切使得一致性哈希成为第一个实用的DHT算法。
但是一致性哈希的路由算法尚有不足之处。在查询过程中，查询消息要经过O(N)步(O(N)表示与N成正比关系，N代表系统内的节点总数)才能到达被查询的节点。不难想象，当系统规模非常大时，节点数量可能超过百万，这样的查询效率显然难以满足使用的需要。换个角度来看，即使用户能够忍受漫长的时延，查询过程中产生的大量消息也会给网络带来不必要的负荷。
使用二分查找算法可以将时间复杂度降低为O(log2n)
2.4、分库分表下的主键id问题
分库分表下的hash数据到每个表，会存在两种情况，一种是数据库自增id，一种是分布式全局共用一处生成主键id。
先说数据库自增id，会导致我们刚才提到的假设我们现在要聚合查询，这样可能导致会出现512条id一致的数据，这样前端应用就会出现困扰。因为id是必须唯一的才能保证我们获取数据，那么我们不使用自增id，我们必须通过每条数据的某个值能够确定该行唯一数据，并使512张表的主键id都不一致但是有序，为什么需要补充有序？主键id不一致大家都知道防止冲突。是因为我们如果使用Innodb数据存储引擎的话底层是红黑树，那么对于连续存储的key值可以有效减少随机访问次数和IO次数提升我们查询的性能，达到每次读取page页可以预读取。
接下来说如何实现分布式全局主键id的几种方式：
Sequence ID
数据库自增长序列或字段，最常见的方式。由数据库维护，数据库唯一。
优点：
简单，代码方便，性能可以接受。
数字ID天然排序，对分页或者需要排序的结果很有帮助。
缺点：
不同数据库语法和实现不同，数据库迁移的时候或多数据库版本支持的时候需要处理。
在单个数据库或读写分离或一主多从的情况下，只有一个主库可以生成。有单点故障的风险。
在性能达不到要求的情况下，比较难于扩展。
如果遇见多个系统需要合并或者涉及到数据迁移会相当痛苦。
分表分库的时候会有麻烦。
优化方案：
针对主库单点，如果有多个Master库，则每个Master库设置的起始数字不一样，步长一样，可以是Master的个数。
比如：Master1 生成的是 1，4，7，10，Master2生成的是2,5,8,11 Master3生成的是 3,6,9,12。这样就可以有效生成集群中的唯一ID，也可以大大降低ID生成数据库操作的负载。
UUID
常见的方式,128位。可以利用数据库也可以利用程序生成，一般来说全球唯一。
优点：
简单，代码方便。
全球唯一，在遇见数据迁移，系统数据合并，或者数据库变更等情况下，可以从容应对。
缺点：
没有排序，无法保证趋势递增。
UUID往往是使用字符串存储，查询的效率比较低。
存储空间比较大，如果是海量数据库，就需要考虑存储量的问题。
传输数据量大
不可读。
优化方案：
为了解决UUID不可读，可以使用UUID to Int64的方法。
GUID
GUID：是微软对UUID这个标准的实现。UUID还有其它各种实现，不止GUID一种。优缺点同UUID。
COMB
COMB（combine）型是数据库特有的一种设计思想，组合的方式，保留UniqueIdentifier的前10个字节，用后6个字节表示GUID生成的时间（DateTime），将时间信息与UniqueIdentifier组合起来，在保留UniqueIdentifier的唯一性的同时增加了有序性，以此来提高索引效率。
优点：
解决UUID无序的问题，在其主键生成方式中提供了Comb算法(combined guid/timestamp)。保留GUID的10个字节，用另6个字节表示GUID生成的时间(DateTime)。
性能优于UUID。
Twitter的snowflake算法
使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数据中心，5个bit的机器ID），12bit作为毫秒内的流水号（意味着每个节点在每毫秒可以产生 4096 个 ID），最后还有一个符号位，永远是0。snowflake算法可以根据自身项目的需要进行一定的修改。比如估算未来的数据中心个数，每个数据中心的机器数以及统一毫秒可以能的并发数来调整在算法中所需要的bit数。
优点：
不依赖于数据库，灵活方便，且性能优于数据库。
ID按照时间在单机上是递增的。
缺点：
在单机上是递增的，但是由于涉及到分布式环境，每台机器上的时钟不可能完全同步，也许有时候也会出现不是全局递增的情况。
2.5、业务上涨伸缩性问题Case
201809201216创建
一、背景:
联合营销活动中心负责收品,伴随业务线上化的推广，使用率覆盖面的增高，原有数据库商品表不满足业务支撑。
特于2017年双十一前进行了垂直切分商品表分别到4个数据库，通过活动ID和提报人ID取模存放512张表中，迎接报名sku数量加剧情况。
二、目前现状与问题：
【目前的数据量与未来发展问题】
目前4个数据库数据量统计如下（时间截止20180919）：
总数据量(包含有效和无效sku) 大约19.6亿条数据,
失效大约SKU 1.37亿条数据
平均每张表 20亿/512张表=3906250条/表,Max表数据量达到519w条/表
每张表目前由于索引和字段一致，数据内容一致，采取最高数据量表计算其大小为：1131M，每条数据大约3k.
Mysql并没有定义一个大表的定义，根据Google团队的一些帖子，我查到达到2个亿数据量占用9G的时候查询是非常煎熬耗时的事情。
通过和DBA沟通，根据DBA建议单表范围保证性能高应该每张表大小控制在2G以内，以及数据量控制在千万级以下,防止产生亿级大表问题（表结构也会有影响，我们这里的表结构比较简单可以忽略）。
亿级大表主要危害有如下：
维护的不便利，查询的性能低
计算 sum、count过于集中
由于我们使用的索引目前是（InnoDB）索引及表数据都是放在 innodb_buffer_pool里面, 数据区间太大，读写热点不交集，造成命中率下降。
表数据那么多，总是冷不丁去查询时老数据，那么这种不频繁的page就会被挤出innodb_buffer_pool之外，使得之后的SQL查询会产生磁盘IO，从而导致响应速度变慢 。
另外根据索引（B+tree）查询的问题，索引涉及到聚焦索引也就是我们常用的主键索引，左面聚集索引，右面非聚集索引，聚集索引通过B+tree的查询直接拿到row数据，而非聚集索引只能拿到他的主键标记，然后通过主键才能查询到数据（图片来自百度的谷歌图片）
B-Tree
【查询慢SQL问题以及使用情况】：
查询慢sql
【QPS查询过高问题】
QPS过高
【CPU超过10%问题】
【磁盘IO次数】
三、排查与分析
1）数据量问题：目前来看按照去年双十一和今年6.18数据量的增速，今年双十一数据量应对没有问题。
2）慢SQL举例：
可以从图中看出是由于单次查询占用行数接近27w数据，虽然SQL简单外加索引机制在数据量问题上依然是个鸡肋。
慢查询
3）QPS偶尔查询过高的问题
可以定位到接口排查到调用数据库之前，根据冷热数据进行redis缓存处理。
4）CPU超过10%的问题：
根据前面的排查，应该是慢SQL的索引过滤性不强导致的全表扫描外加order by排序，具体还需要长时间观察与DBA沟通
5）磁盘IO次数目前还正常基本在75%以下。
四、6.18和去年双十一数据统计综合统计分布图
五、方案参考首页banner图
banner
网友评论