MySQL 之 瓶颈及优化篇
数据库瓶颈
- 阶段一：企业刚发展的阶段，最简单，一个应用服务器配一个关系型数据库，每次读写数据库。
- 阶段二：无论是使用 MySQL 还是 Oracle 还是别的关系型数据库，数据库通常不会先成为性能瓶颈，通常随着企业规模的扩大，一台应用服务器扛不住上游过来的流量且一台应用服务器会产生单点故障的问题，因此加应用服务器并且在流量入口使用 Nginx 做一层负载均衡，保证把流量均匀打到应用服务器上。
- 阶段三：随着企业规模的继续扩大，此时由于读写都在同一个数据库上，数据库性能出现一定的瓶颈，此时简单地做一层读写分离，每次写主库，读备库，主备库之间通过binlog同步数据，就能很大程度上解决这个阶段的数据库性能问题
- 阶段四：企业发展越来越好了，业务越来越大了，做了读写分离数据库压力还是越来越大，这时候怎么办呢，一台数据库扛不住，那我们就分几台吧，做分库分表，对表做垂直拆分，对库做水平拆分。
上面只是一个简单图示，还有还有很多没有展现出来，例如 NoSQL 等，但在这里我们主要关注 MySQL 的优化，即读写分离和分库分表。
主从复制
建立和主数据库完全一样的数据库环境，称为从数据库；主从复制是指主数据库中的数据会复制到一个或多个从数据库中。
为什么有主从复制？
在实际的生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的；如果一个项目只有一个数据库服务器，而这个数据库一旦宕机，就会导致业务停顿，这是不能接受的。
因此多台数据库服务器就很有必要。而多个数据库之间如何同步数据？这就是主从复制提出的原因。
一般来说，主从复制和读写分离是一起使用的，主从复制来同步数据，在这基础上进行数据的读写分离。
主从复制的好处
- 支持读写分离，使数据库能支撑更大的并发。
- 可以在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。
- 数据备份，保证数据的安全。
MySQL主从复制模型
- 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：
- 一是性能很差，因为要复制到所有节点才返回响应；
- 二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
- 异步复制(默认)：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
- 半同步复制：介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。（常用于解决数据一致性问题）
原理
binlog 记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。
- 主库在收到提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回“操作成功”的响应。
- 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
- 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。
主从延迟
主从延迟，就是同一个事务，从库执行完成的时间与主库执行完成的时间之差，也就是上图的1到6的时间。
由于主从延迟的存在，我们可能会发现，数据刚写入主库，结果却查不到，因为可能还未同步到从库。主从延迟越严重，该问题也愈加明显。
主从延迟的来源
主要原因包括但不限于以下几种情况：
- 有些部署条件下，从库所在机器的性能要比主库性能差。
- 从库的压力较大，即从库承受了大量的请求。
- 执行大事务。因为主库上必须等事务执行完成才会写入 binlog，再传给备库。如果一个主库上语句执行 10 分钟，那么这个事务可能会导致从库延迟 10 分钟。
- 从库的并行复制能力。
主从延迟的解决方案
解决主从延迟主要有以下方案：
- 一主多从，分摊从库压力；
- 如果某些操作对数据的实时性要求比较苛刻，需要反映实时最新的数据，比如说涉及金钱的金融类系统、在线实时系统、又或者是写入之后马上又读的业务；此时应该放弃读写分离，强制走主库方案（强一致性）；
- sleep 方案：主库更新后，读从库之前先 sleep 一下；
- 发生主从延迟时自旋
好处：针对偶现的主从延迟情况做处理。
实现：当从数据库读到数据为空时，for循环3次，每次等待0.5S，如果还没查出来则报警。 - 并行复制；
并行复制
一般 MySQL 主从复制有三个线程参与，都是单线程：Binlog Dump 线程、IO 线程、SQL 线程。复制出现延迟一般出在两个地方：
- SQL 线程忙不过来（主要原因）；
- 网络抖动导致 IO 线程复制延迟（次要原因）。
日志在备库上的执行，就是备库上 SQL 线程执行中继日志（relay log）更新数据的逻辑。
在 MySQL 5.6 版本之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。从 MySQL 5.6 开始有了多个 SQL 线程的概念，可以并发还原数据，即并行复制技术。这可以很好的解决 MySQL 主从延迟问题。
主从切换
分布式事务
读写分离
读写分离就是只在主服务器上写，只在从服务器上读，依赖于主从复制。用来解决数据库的读性能瓶颈的。
从服务器可以使用MyISAM，提升查询性能及节约系统开销
为什么使用读写分离？
大多数互联网业务，往往读多写少，这时候，数据库的读会首先称为数据库的瓶颈，这时，如果我们希望能够线性的提升数据库的读性能，消除读写锁冲突从而提升数据库的写性能
实现原理
目前较为常见的 MySQL 读写分离分为两种。
- 基于程序代码内部实现
- 在代码中根据 select、 insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。
- 优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支；
- 缺点是需要开发人员来实现，运维人员无从下手。
- 基于中间代理层实现
- 代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库。
分库分表
简单来说，就是将数据分开存储。
为什么进行分库分表？
用来解决数据库的数据容量瓶颈的。例如订单表，数据量只增不减，历史数据又必须要留存，非常容易成为性能的瓶颈，而要解决这样的数据库瓶颈问题，分库分表是比较合适的。而分库分表分为垂直和水平两个方式，一般来说我们拆分的顺序是先垂直后水平。
分库分表可以按照业务进行划分，这样对于单点的写，就会分成多点的写，写性能方面也就会大大提高。
垂直拆分
垂直分表
将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。
好处;
- 避免IO竞争减少锁表的概率。因为大的字段效率更低，第一数据量大，需要的读取时间长。第二，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多。
- 可以更好地提升热门数据的查询效率。
垂直分库
按照业务对表进行分类，部署到不同的数据库上面，不同的数据库可以放到不同的服务器上面。
好处：
- 降低业务中的耦合，方便对不同的业务进行分级管理。
- 可以提升IO、数据库连接数、解决单机硬件资源的瓶颈问题。
垂直拆分的缺点
- 主键出现冗余，需要管理冗余列
- 事务的处理变得复杂
- 仍然存在单表数据量过大的问题
水平拆分
水平分表
在同一个数据库中创建多个类似结构的表，每个表存储不同的数据行。例如，根据数据的索引值将数据划分到不同的表中。
水平分库
把同一个表的数据按照一定规则（如范围、哈希、取模等）拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。
好处：
- 提高系统扩展性：通过将数据分散存储在多个数据库或表中，可以水平扩展系统的存储容量和计算能力，满足大规模数据和高并发负载的需求。
- 减少单个节点的压力：将数据分布在多个节点上减轻了单个节点的负载，降低了系统的风险和稳定性问题。
- 提高数据库的并发处理能力：由于数据被分散存储到多个数据库中，查询可以并行地在多个分片上执行，提高了查询的吞吐量和响应速度。可以减少数据库的负载，提高并发处理能力。
- 增加数据安全性：通过将不同的数据分散存储到不同的数据库中，可以提高数据的安全性，一旦某个数据库出现故障，其他数据库仍然可以正常工作。
缺点：
- 跨分片事务管理：涉及到多个数据库或表的操作时，需要考虑跨分片事务的管理和一致性保证，这可能会增加复杂性。
- 数据迁移和扩容：在系统扩容或重构时，可能需要进行数据迁移和重建分片的过程，需要谨慎规划和操作。
- 查询跨分片的性能损耗：某些查询可能需要跨多个分片进行数据聚合或关联查询，这可能会对性能产生一定影响。
注意
分库分表后，ID键如何处理？
分库分表后不能每个表的ID都是从1开始，所以需要一个全局ID，设置全局ID主要有以下几种方法：
- UUID：通用唯一标识码，UUID是基于当前时间、计数器和硬件标识等数据计算生成的。
- 优点：本地生成ID，不需要远程调用；全局唯一不重复。
- 缺点：占用空间大，不适合作为索引。
- 数据库自增ID：在分库分表表后使用数据库自增ID，需要一个专门用于生成主键的库，每次服务接收到请求，先向这个库中插入一条没有意义的数据，获取一个数据库自增的ID，利用这个ID去分库分表中写数据。
- 优点：简单易实现。
- 缺点：在高并发下存在瓶颈。
- Redis生成ID：
- 优点：不依赖数据库，性能比较好。
- 缺点：引入新的组件会使得系统复杂度增加
- 设定步长，比如1-1024张表我们设定1024的基础步长，这样主键落到不同的表就不会冲突了。
- 分布式ID，自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种
- 分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。