MySQL主从架构
主从架构即设置主机和从机实现读写分离，提高数据库吞吐量，同时从机可以作为主机的数据备份，但是主从结构也会带来一些问题。
主从同步
主机和从机的数据要保持同步就必须执行主从复制，主从复制依靠三个线程实现:
- 数据更新时，主库将操作和数据写入binlog，主库的binlog dump线程将binlog日志发送给从库
- 从库的IO线程负责向主库请求binlog并写入自己的中继日志。
- 从库的SQL线程读取中继日志将更新操作写入数据库。
主从延迟
使用主从架构不可避免会带来主从延迟问题，即在某一时刻，主机与从机的数据存在一定的差异，这是因为主从数据同步的过程是异步的，不保证强一致性。正常情况下，主从延迟一般不会很大，但是如果某些SQL执行时间过长或某个事务锁表时间过长，就会导致较大程度的主从延迟。
- 使用半同步复制确保主从延迟不会导致数据丢失
- MySQL默认使用的是异步复制，主机执行完事务后直接提交，不管从机是否接收到同步数据。
- 开启半同步复制，主机执行完事务后至少要收到一个从机的ack后才认为同步成功，从而提交事务，这样可以确保即使主机宕机数据也不会丢失。
- 当然，主机等待时间有限制，若超时主机可以选择直接提交。
- 从机速度跟不上主机时让从机开启并行复制
- 从机开启多个SQL线程，并行读取relaylog中的日志，加快从机同步数据的速度。可以根据需要在不同表上或在同一个表上的不同行并行（行没有被两个事务同时更新时）。
- 如果从机承担大量查询请求，也可能会影响同步速度，可以开启多个从机分摊压力。
- 某个从机和主机的延迟达到阈值就不从这个从机上读取数据，避免读到这个从机的脏数据。
- 主机限制操作避免主从延迟，降低主从延迟影响
- 避免大事务，慢SQL，大数据量的操作，因为它们需要锁定资源且执行时间长，导致从机和主机之间的差距被拉大。
- 实时性要求强的业务强制走主机。
- 在从机读取数据失败时再去主机读取数据，即二次读取。
- 要求强一致性时需要放弃使用主从
- redis中的分布式锁实现redlock，为了避免主从延迟导致同时获取到多把锁，放弃了主从结构，而是使用多个实例共同获取锁的方式。
读写分离如何实现
读写分离可以分类为两种实现方式，分别是程序代码封装方式和中间件封装方式。
- 程序代码封装：在代码中根据不同条件动态切换数据源。常见实现为使用AOP拦截方法，获取其目标数据源并保存到上下文，执行sql时调整动态路由数据源类选择具体的数据源。但是这种方式存在一些问题：
- AOP注解只适用于方法级别粒度。如果想要更细粒度，可以使用手动编程的方式。
- 数据源信息保存在ThreadLocal上下文中，存在开启子线程导致上下文丢失的问题，可以使用InheritableThreadLocal。
- 不支持嵌套数据源设置，例如方法1指定了数据源，但是它又调用了方法2，方法2也指定了数据源，但是在ThreadLocal中数据源只能指定一次。解决方案是在上下文中设置一个栈，用来保存嵌套的数据源选择方式，即不止保存当前方法的数据源还保存之前方法设置过的数据源。
- 中间件封装：引入中间件实现读写分离，业务无需关注读写分离，而是直接访问数据库中间件，再由中间件根据一定规则判断去访问具体的数据库。中间件如ShardingSphere。
- 但是引入中间件由于需要更多次的数据传输，可能会导致性能降低。
MySQL主从结构中master宕机
- 此时要手动选择一个从库升级为主库。
- 检查确定所有从库的relaylog都写入完毕（保证主库完整性）。
- 查看所有从库看哪个从库与主库的数据最接近（主从复制位置最靠后），选为新主库。
- 修改其配置文件转化为主库，修改其他从库（包括旧主库）的配置文件将其指向新的主库。
MySQL分库分表
什么是分库分表
当一个数据库或一张表的数据量过大时，就需要对其进行分库分表。分库分表又分为水平和垂直两种方式。
- 分表
- 水平分表：把一个表的数据分散到多个表中，但是每个表的结构是一样的，只是存放的数据不同，如用户表1，用户表2。
- 垂直分表：把一个表的部分字段拆分到不同表中，每个表的结构不同，如冷热数据分离（商品表和商品详情表）。
- 分库
- 水平分库：将同一个库拆分为多个，如用户库1，用户库2。
- 垂直分库：按照业务模块划分为不同的库，如用户数据库，商品数据库。
水平分表/分库如何实现
以使用中间件ShardingSphere为例，需要在配置文件中设置分库分表规则，主要配置数据源，定义分片键和分片算法，业务代码逻辑不用变化。
- 分片键决定根据哪个字段分片，故分片键应该均匀分布避免数据倾斜，并且是经常被读写的字段。
- 分片算法决定如何根据分片键路由到不同表，常见路由方式有三种：
- 范围路由：按照某个字段值的范围划分，适用于分片键具有顺序性的场景，但可能数据倾斜。例如时间戳。
- Hash路由：即对分片字段进行哈希计算然后取模，但扩容麻烦且范围查询可能涉及多个表。
- 配置路由：即用一个具体的配置表来确定数据存储在哪个表中。
分库分表带来的问题
- 分库
- 分库后本地事务就失效了，只能用分布式事务。
- 分库后就无法使用join进行连表查询了，只能先查出一个表的数据并保存再去另一个表查。
- 分表
- 分表后跨表的计数，排序，聚合等操作困难，只能手动汇总数据再操作。
- 分表后ID就不能再依赖数据库自身主键了，而是需要使用分布式ID生成方案如雪花算法。
MySQL的CPU飙升处理
- top命令看哪个进程占用的cpu（并及时kill掉，观察cpu是否下降）。
- 如果是mysql进程，show processlist查看当前所有SQL语句执行情况，看哪个SQL语句在消耗资源多，执行时间长。
- show processList也可以查看session连接情况，看是否突然有大量请求打到MySQL上，例如缓存失效或瞬时高并发情况。
- explain看SQL的执行计划，看索引是否有问题，SQL语句本身是否有问题。
- 如果是数据量太大造成SQL查询缓慢，可能需要设置主从或分库分表。
MySQL调优手段
-
SQL语句是否合理
- 不要用select *查询不需要的字段，会增加网络负担和磁盘IO操作，且无法走覆盖索引导致大量回表操作。
- 减少使用order by，group by，distinct，union，这些语句较为耗费CPU，数据库的CPU资源很宝贵，最好将这些功能放到程序端做。
- 避免多表连接（适当增加冗余字段），禁止3个表以上的连接。实在要连接也要优化（小表驱动大表）。
- 小表驱动大表原理是MySQL在做join操作时会将驱动表加载进缓冲区，遍历驱动表的数据去匹配被驱动表的数据，此时驱动表不用索引，被驱动表用索引，如果将大表加载进缓冲区可能无法一次加载进来，导致多次IO。以左外连接为例（左为驱动表，右为被驱动表），join相当于每次取左表一条数据与右表所有数据进行匹配，应该优先设置右表的数据项作为索引（最好两者都设置索引）。
- 避免子查询，因为子查询需要创建临时表，且无法使用索引，可以使用join代替子查询。
- 分页优化
- 首先对于大表限制查询范围，禁止不带任何限制的查询。
- 对于深分页问题，例如“select 列名 from 表名 where 条件 limit 10000 10”，需要扫描10010条数据再抛弃前10000条，并且需要10010次回表从而使性能很差。
- 可以使用延迟关联，即先找出第10000条数据后10条数据的id（使用索引，不回表），再做关联去回表。
- 可以记住上次分页的最大id，下次查询直接利用id索引到要查找的数据的开始位置，再用limit。
- 使用between and将其转化为范围查询。
- 多次操作时可以合并为批量操作，避免多次连接数据库，当然批量操作也不是越多越好。
- 避免大事务，及时commit释放占用的资源，选择合适的锁和隔离级别，防止产生死锁。
- 写操作时禁用唯一性检查和外键检查，并且写或当前读时一定要走索引，否则锁表。
-
是否使用到了索引
- 使用explain分析，充分使用索引，索引覆盖，索引下推，最左前缀原则等。
- 避免索引失效，只为合适的列设置索引且不要设置过多索引。
- 防止加锁语句不走索引锁住全表。
-
数据量是否过大
- 分库分表。
- 主从架构，集群架构。
- 使用缓存。
MySQL如何实现不停机扩容
- 在线双写，查询走老库。即建立好新的库表结构，新数据同时写入老库和新库，然后将老库中的历史数据迁移到新库，定时对比新老库的数据并补齐差异。
- 在线双写，查询走新库。完成历史数据的同步和校验后，把读数据切换到新库。
- 老库下线。老库不再写入新数据，等老库完全没有请求后就可以下线了。
