mysql 主从复制 主从延迟 分库分表 分区

为甚分库分表？
先集群：主从，且读写分离。
分表：数据量过大 查询慢 锁事物冲突。一般先垂直分，再考虑水平分
分库：机器的写压力大，并发量数千级别，读压力大，太多slave  
分库分表后：降低磁盘使用率 单表数据量少 并发冲突低

主备延迟的原因： 机器性能不均衡 使用不均衡 大事物 没有开启并行同步 确实写入并发过大
主从复制延迟解决：show status查看 Seconds_Behind_Master，可以看到从库复制主库的数据落后了几 ms
8个方案：
从库机器性能差，可以增加机器性能，或者多从
打开MySQL的并行复制：多个库并行复制。某个库的写入并发就是特别高时无意义
开启半同步复制：拉取binlog日志要是多线程的

业务：
代码修改：业务控制，插入后不要立刻查询，等待后查询
避免大事物：避免删除大量数据；建立合适的索引，减少冲突；
使用不均衡，读多写少时，可以把部分读放到主库
必须先插入，立马要求就查询到：对主查询设置直连主库

分库：一个主库拆分为多个主库，每个主库的写并发就减少了几倍，降低延迟

分库分表的技术方案，比如：
mycat：中间件，代码无需修改。通过改写与转发sql，然后汇总结果后返回给客户端。
shading-jdbc：jar包，代码需要修改。client层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，升级的话需要修改系统。
优缺点对比：mycat需要单独部署与运维。sharding-jdbc,需要修改代码。
分库分表引入问题：
1）分布式全局唯一id snowflake+mysqlIndex
2）分库规则和策略
3）跨库查询问题
4）跨库事物问题，即分布式事务
跨库join解决：
1）全局表，表重复，需要开启全局表一致性检测，配置全局表。
2）share join，简单的跨分片join；如mycat，自动sql拆分查询，并汇总结果
3）ER join，子表的记录与所关联的父表记录存放在同一个数据分片上，从而解决跨库join的问题
Federal引擎 或 带上库名
mycat和shading-jdbc：https://www.cnblogs.com/leeSmall/p/9539370.html
比较全的分库分表： https://blog.csdn.net/qq_36625757/article/details/90477131
mycat分页中的坑：LIMIT 1000100, 100;，他会查出所有取后几条，解决：先查id，再根据id查询具体记录
 

不同分表方式的查询

sharding jdbc方式：
按照订单hash分表
按照订单查询：查询的时候直接按照hash查询对应表
按照用户查询订单：建立订单号和用户的关系表，先根据用户获取对应的订单号，然后查询。

mycat方式
mycat分表的实现：首先在mycat的scheme.xml中配置逻辑表，并且在配置中说明此表在哪几个物理库上。此逻辑表的名字与真实数据库中的名字一致！然后需要配置分片规则，即按照什么逻辑分库！
推荐:通过改写与转发sql，然后汇总结果后返回给客户端。

分库分表

1、水平分库

1. 概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
2. 结果：

• 每个库的结构都一样；
• 每个库的数据都不一样，没有交集；
• 所有库的并集是全量数据；

• 场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
• 分析：库多了，io和cpu的压力自然可以成倍缓解。

2、水平分表

1. 概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
2. 结果：

• 每个表的结构都一样；
• 每个表的数据都不一样，没有交集；
• 所有表的并集是全量数据；

• 场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
• 分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

3、垂直分库

1. 概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
2. 结果：

• 每个库的结构都不一样；
• 每个库的数据也不一样，没有交集；
• 所有库的并集是全量数据；

• 场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
• 分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

4、垂直分表

1. 概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
2. 结果：

• 每个表的结构都不一样；
• 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
• 所有表的并集是全量数据；

• 场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
• 分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

三、分库分表工具

1. sharding-sphere：jar，前身是sharding-jdbc；
2. TDDL：jar，Taobao Distribute Data Layer；
3. Mycat：中间件。

注：工具的利弊，请自行调研，官网和社区优先。

四、分库分表步骤

根据容量（当前容量和增长量）评估分库或分表个数 -> 选key（均匀）-> 分表规则（hash或range等）-> 执行（一般双写）-> 扩容问题（尽量减少数据的移动）。

五、分库分表问题

1、非partition key的查询问题

基于水平分库分表，拆分策略为常用的hash法。

1. 端上除了partition key只有一个非partition key作为条件查询

• 映射法

• • 基因法

注：写入时，基因法生成user_id，如图。关于xbit基因，例如要分8张表，23=8，故x取3，即3bit基因。根据user_id查询时可直接取模路由到对应的分库或分表。根据user_name查询时，先通过user_name_code生成函数生成user_name_code再对其取模路由到对应的分库或分表。id生成常用snowflake算法。

1. 端上除了partition key不止一个非partition key作为条件查询

• 映射法

• • 冗余法

注：按照order_id或buyer_id查询时路由到db_o_buyer库中，按照seller_id查询时路由到db_o_seller库中。感觉有点本末倒置！有其他好的办法吗？改变技术栈呢？

1. 后台除了partition key还有各种非partition key组合条件查询

• NoSQL法

• • 冗余法

2、非partition key跨库跨表分页查询问题

基于水平分库分表，拆分策略为常用的hash法。

注：用**NoSQL法**解决（ES等）。

3、扩容问题

基于水平分库分表，拆分策略为常用的hash法。

1. 水平扩容库（升级从库法）

注：扩容是成倍的。

1. 水平扩容表（双写迁移法）

第一步：（同步双写）修改应用配置和代码，加上双写，部署；第二步：（同步双写）将老库中的老数据复制到新库中；第三步：（同步双写）以老库为准校对新库中的老数据；第四步：（同步双写）修改应用配置和代码，去掉双写，部署；

注：双写是通用方案。

六、分库分表总结

1. 分库分表，首先得知道瓶颈在哪里，然后才能合理地拆分（分库还是分表？水平还是垂直？分几个？）。且不可为了分库分表而拆分。
2. 选key很重要，既要考虑到拆分均匀，也要考虑到非partition key的查询。
3. 只要能满足需求，拆分规则越简单越好。

参考：https://zhuanlan.zhihu.com/p/137368446

分区

1）为什么要分区？

如果一张表的数据量太大的话，索引和数据空间会很大，查找数据就会变的很慢。这个时候我们可以利用mysql的分区功能

2）分区的具体实现：

分为range分区，list，hash，key

List分区实现：

1. mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
2.  ->   `id` int(11) NOT NULL  COMMENT '用户ID', 
3.  ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
4.  ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
5.  ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'  
6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
7.  -> PARTITION BY LIST (province_id) (  
8.  ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
9.  ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
10.  ->     PARTITION p2 VALUES IN (13,14,15,19),  
11.  ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
12.  -> ); 

Hash分区的实现

1. mysql> CREATE TABLE IF NOT EXISTS `hash_part` (  
2.  ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',  
3.  ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',  
4.  ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
5.  ->   PRIMARY KEY (`id`)  
6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
7.  -> PARTITION BY HASH(id)  
8.  -> PARTITIONS 3;  

对分区的管理

#删除分区

alter table list_part drop partition p2; 

查询

select count(*) from aa; 

3）分区内部实现与原理

分区表是一个独立的逻辑表，但是底层MySQL将其分成了多个物理子表，这对用户来说是透明的，每一个分区表都会使用一个独立的表文件。
MySQL内部将表分成多个物理字表，但是客户端并无感知，仍然认为操作的是一个表

1. 分区的优缺点

1，分区可以分在多个磁盘，存储更大一点

2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了

3，进行大数据搜索时可以进行并行处理。

4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

分区优势：
1）代码完全无需修改
2）可以使用多磁盘，存储更大一点，且跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

但是：

1）mysql应该就做简单的事。别的都应该在代码层面实现。
2）mysql5.7开始，server提供的通用分区处理器已经弃用了；后期mysqlServer彻底不再支持,少数引擎单独支持

MySQL :: MySQL 5.7 Reference Manual :: 22 Partitioning

重点：

As of MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is removed in MySQL 8.0, when the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do this.

结论：现在一般不再使用分区了。