mysql的分库分表及主从同步

本文主要是介绍mysql的分库分表及主从同步，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

⾸先分库分表分为垂直和⽔平两个⽅式，⼀般来说我们拆分的顺序是先垂直后⽔平。

垂直分库

基于现在微服务拆分来说，都是已经做到了垂直分库了

垂直分表

如果表字段⽐较多，将不常⽤的、数据较⼤的等等做拆分

⽔平分表

⾸先根据业务场景来决定使⽤什么字段作为分表字段(sharding_key)，⽐如我们现在⽇订单1000万，我

们⼤部分的场景来源于C端，我们可以⽤user_id作为sharding_key，数据查询⽀持到最近3个⽉的订

单，超过3个⽉的做归档处理，那么3个⽉的数据量就是9亿，可以分1024张表，那么每张表的数据⼤概

就在100万左右。

⽐如⽤户id为100，那我们都经过hash(100)，然后对1024取模，就可以落到对应的表上了。

分表后的主键冲突如何解决：

1. 设定步⻓，⽐如1-1024张表我们设定1024的基础步⻓，这样主键落到不同的表就不会冲突了。

查看步长命令：SHOW VARIABLES LIKE 'auto_inc%';   设置步长：SET @auto_increment_increment=1;

2. 分布式ID，⾃⼰实现⼀套分布式ID⽣成算法或者使⽤开源的⽐如雪花算法这种

3. 分表后不使⽤主键作为查询依据，⽽是每张表单独新增⼀个字段作为唯⼀主键使⽤，⽐如订单表订

单号是唯⼀的，不管最终落在哪张表都基于订单号作为查询依据，更新也⼀样。

非sharding_key的查询：

1. 可以做⼀个mapping表，⽐如这时候商家要查询订单列表怎么办呢？不带user_id查询的话你总不

能扫全表吧？所以我们可以做⼀个映射关系表，保存商家和⽤户的关系，查询的时候先通过商家查

询到⽤户列表，再通过user_id去查询。

2. 打宽表，⼀般⽽⾔，商户端对数据实时性要求并不是很⾼，⽐如查询订单列表，可以把订单表同步

到离线（实时）数仓，再基于数仓去做成⼀张宽表，再基于其他如es提供查询服务。

3. 数据量不是很⼤的话，⽐如后台的⼀些查询之类的，也可以通过多线程扫表，然后再聚合结果的⽅

式来做。或者异步的形式也是可以的。

主从同步

1. master提交完事务后，写⼊binlog

2. slave连接到master，获取binlog

3. master创建dump线程，推送binglog到slave

4. slave启动⼀个IO线程读取同步过来的master的binlog，记录到relay log中继⽇志中

5. slave再开启⼀个sql线程读取relay log事件并在slave执⾏，完成同步

6. slave记录⾃⼰的binglog

两种复制方式：

全同步复制

主库写⼊binlog后强制同步⽇志到从库，所有的从库都执⾏完成后才返回给客户端，但是很显然这个⽅式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写⼊⽇志成功后返回ACK确认给主库，主库收到⾄少⼀个从库的确认就认为写操作完成。

如何解决主从延迟：

1. 针对特定的业务场景，读写请求都强制⾛主库

2. 读请求⾛从库，如果没有数据，去主库做⼆次查询

补充一下mysql中的各种log：

undo log：用来实现事务的原子性，记录了事务的回滚记录，比如事务对数据进行了修改，undo log会记录修改前的记录和事务版本等信息

redo log：重做日志，因为如果修改数据同时进行磁盘持久化，会带来巨大的磁盘io，因此引入redo log，分为两部分，一部分是内存中的日志缓冲，一部分是磁盘上的重做日志文件，在提交事务的时候，必须将事务的所有日志写入到磁盘上的redo log file和undo logfile中进行持久化，通过调用fsync实现

MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘。

• 当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
• 当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
• 当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。

b+树的高度如何计算：

上文已详细介绍了这二种存储结构，这里我们直接分析：

• B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。
• B+树所有的Data域在叶子节点，并且所有叶子结点之间都有指针，这样遍历叶子结点就能获得全部数据。这样就极大的提升了排序区间操作，在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
• B树可以有多个子节点，从几十到上千，可以降低树的高度，极大减少IO次数提升读写效率。

举个例子：我们假设一行的数据是1K，按一页16KB大小计算，那一页能存储16行数据，也就是一个叶节点可以存储16条记录。再来看非叶节点，假设ID是bigint类型，那么长度为8B，指针大小在InnoDB源码中为64（6B），一共就是14B，那么一页里面就可以存储16K/14=1170个（主键+指针）

那么一颗高度为2的B+树能存储的数据为：1170*16=18720条，一颗高度为3的B+树可以存储1170*1170*16=21902400（千万条）。只要3层的树高就可以存储千万级别的表记录了。

• B+树的查找效率比B-树高，原因是因为B+树把数据放在叶子节点，所以就可以增加更多的非叶子节点数。而B-树节点和数据是保存在一起的，所以非叶节点的节点数要比B+树少，树的高度就比B+树高从而导致查询效率不如B+树。

这篇关于mysql的分库分表及主从同步的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！