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+树。

char和varchar的区别：

1、 char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。

2、 varchar可变长度，可以设置最大长度；适合用在长度可变的属性。

3、 text不设置长度， 当不知道属性的最大长度时，适合用text。

按照查询速度： char最快， varchar次之，text最慢。

char：char(n)中的n表示字符数，最大长度是255个字符； 如果是utf8编码方式， 那么char类型占255 * 3个字节。（utf8下一个字符占用1至3个字节）

varchar实际范围是65532或65533， 因为内容头部会占用1或2个字节保存该字符串的长度；如果字段default null（即默认值为空），整条记录还需要1个字节保存默认值null。

       如果是utf8编码， 那么varchar最多存65532/3 = 21844个字符。

varchar频繁的更新变长字段可能导致存储页的分裂 产生存储碎片 

• InnoDB存储引擎：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

原文链接：https://blog.csdn.net/brycegao321/article/details/78038272