MySql教程

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的分库分表及主从同步的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!