mysql存储高性能

读写分离，主从复制

思想

1. 搭建主从集群，一主一从或多从
2. 主机负责读，从机负责写
3. 主从之间数据复制，通过binlog日志
4. 业务服务或中间件将写操作路由给主机，读操作路由给从机

主从复制实现

1. 主机确保启用binlog日志
2. 确认从机唯一服务器id
3. 在主机创建从机可连接主机用户账户
4. 配置从机连接主机进行数据复制

-- 主机启用二进制日志
[mysqld]
log-bin=mysql-bin
server-id=1

-- 主机创建复制用的用户并授权
CREATE USER 'replica'@'%' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;
 
-- 获取当前二进制日志位置
SHOW MASTER STATUS;

-- 设置服务器ID（每个从服务器必须不同）
[mysqld]
server-id=2
 
-- 在从服务器上执行以下命令来连接到主服务器并开始复制
CHANGE MASTER TO
  MASTER_HOST='主服务器IP',
  MASTER_USER='replica',
  MASTER_PASSWORD='replica_password',
  MASTER_LOG_FILE='记录的log文件名',
  MASTER_LOG_POS=记录的log位置;
 
-- 启动从服务器复制线程
START SLAVE;

读写分离的实现

1. 应用代码分装，缺点是代码复杂，故障情况下如果主从切换需要程序修改配置和重启
2. 使用中间件，如mysql-route、shardingsphere、mycat、tddl、cobar、atlas等。当然这些中间件还用于解决分库分表等问题。

带来的问题

        主从复制过程中，大量数据导致同步延迟，在延迟时间内主从数据不一致

        解决方案：

解决方案：
1、写操作后的读操作在业务中指定发给从服务器
缺点，业务侵入性较大，易出bug

2、读从机后再读一次主机，二次读取，可在数据访问层封装api，代码侵入性小
缺点：过量二次读取，增加主机压力

3、关键业务读写操作指向主机，非关键业务使用读写分离
缺点：需要区分好业务，业务粒度如果较大，那么灵活性不足，对性能可能造成影响。

分库分表

        读写分离分散了读写压力，但是存储量到达千万上亿的时候，单台数据库存储能力成为了瓶颈，如下

1、数据量大，索引也大，硬件资源受限，读写性能下降

2、数据磁盘文件大、数据备份、恢复、主从数据复制将更耗时

3、数据文件大，故障时数据丢失风险越高

        因此需要通过分散存储的方式解决瓶颈问题，如分库分表。当然也带来新的问题和更高的复杂度。

分库

        将不同业务或模块数据落到不同的数据库，以分散存储压力，也分散了访问压力。但同时带来更高系统复杂度，因此架构设计时需要因时制宜。

分库产生的问题

1. join问题，不同库关联查询，由于数据分散在不同库中，之前单库join已经不适用，需要在代码中一个库一个库去查，最后再合并。增加了复杂度。
2. 事务问题，单库时一个事务对应一个数据库连接，分库后将变成分布式事务问题。对于分布式事务目前也有很多解决方案。如seata，基于两阶段提交协议（2pc），衍生几种分布式事务模式 - XA、TCC、SAGA、AT等。
3. 成本问题，机器增多，如果考虑容灾备份成本更是成倍增长；业务实现、机器运维复杂度变高，人力成本将增加。

分表

1. 将不同业务数据分散到不同库中，相当于既分了库又分了表。
2. 水平拆分，将单表行切割到不同库或者同库（取决于是否达到数据库性能瓶颈），避免单表过大，一般在千万级别时就需要考虑；
3. 垂直拆分、将单表列数据切割到同库中，减少查询冗余。

垂直拆分问题

        垂直拆分后，因为列的分割、可能导致关联查询次数变多，并且数据更新、插入、删除等相未拆更繁琐。

水平拆分问题

        1、路由问题，水平拆分后，数据分段存在，CURD等操作要路由到对应的段，因此需要引入路由规则，增加了系统复杂度。

        2、join问题

        3、count查询问题

        4、分页问题

        5、order by问题

水平拆分路由规则

范围路由

        选取有序数据列，比如自增id、时间戳，将数据按照一定数量（一般100w到2000w）水平分割到不同数据库表中，那么每个数据库表就包含了原表一部分数据。

优点是：可以平滑的扩充新表，之前分割的表数据不需要迁移。

缺点是：

        1、分段大小的选取，如果太大切出的表依然存在单表性能问题，太小表就会很多，不便维护使用。

        2、最后分到数据的表，与前面已经分满数据的表对比，数据量要小，总体来看分布不够均匀。

hash路由

        选取一个或多个列的值进行Hash运算，根据hash值与数据库数量取模的结果分散到不同数据库表中。

优点是：数据分布比较均匀。

缺点是：

        1、分表数量的选取，分的少数据量多，可能会存在单表性能问题，太多不便于维护使用。

        2、不可以平滑的扩充新表，增加新表后所有表数据需要重新分布。

配置路由

        增加一个路由表，用来映射数据路由信息，比如user_id映射到table_id，根据user_id就可以查到table_id，个人感觉配置路由单库分表比较方便，分到多库比较复杂。

优点是：使用灵活，扩充新表时，只需迁移部分数据（路由表数据不断增加，最后一段路由映射数据超过一定量，那么就需要扩充新表、调整路由表、迁移超出的数据到新表中）。

缺点是：

        1、多查一次表，也就是要先查路由表，再定位数据到哪个表中，

        2、路由表本身数据量太大，同样存在性能问题

部分参考《从零开始学架构》