🎻 MySQL高性能

1. 主从

1) 主从同步策略

1. 全同步复制
   • 所有从库均执行完同步操作后才返回给客户端
2. 半同步复制
   • 从库成功写入日志后，返回 ACK 确认给主库，主库收到至少一个从库的确认就认为写操作成功

2) 主从同步流程

三个线程：

• dump 线程
• IO 线程
• SQL 线程

1. Master 启用 binlog ，记录任何修改了数据库数据的事件
2. Slave 开启 IO 线程，通过 MySQL 协议，请求 Master 的 binlog，可能含位置参数
3. Master 启动 dump 线程，检查自己 binlog 日志中的事件，与 Slave 的请求位置对比
   • 若 Slave 没有位置参数，则从自己的 binlog 起始点开始传送所有 binlog
   • 若 Slave 含有位置参数，则从对方请求位置开始传送
4. Slave 接收到 Master 发来的 binlog 之后，将它放入 relay log 中继日志中，并记录此次请求到主节点的位置参数
5. Slave 启动 SQL 线程，读取 relay log，并在本地执行

3) 主从延迟

如何避免？

1. 避免长事务
   • 导致主从延迟增大
   • 回滚日志增大
   • 回滚时间长
   • 容易造成阻塞 & 锁超时
2. 提高从库机器配置
3. 重要数据直接读主库
4. MySQL 5.6 之后支持并行复制，可以多线程同时执行SQL 语句

4) 主从同步相关命令

---

2. 读写分离

1. 2 种常用方式：
   • 代码封装
     • 在代码中抽象出一个 数据访问层，实现读写分离 & 数据库服务器连接的管理
     • 淘宝的 TDDL
   • 数据库中间件
     • 独立一套系统出来，实现 读写分离 & 数据库服务器连接的管理，业务服务器无需自己进行 读写分离

---

3. 分库分表

1) 分库

1. 从一个大的数据中分出多个小的数据库，每个服务都对应一个数据库
2. 解决什么问题？
   • 解决 并发量大 的问题
     • 每个 数据库的连接数是有限的，通过 增加数据库实例 的方式可以提供更多的数据库连接
3. 分库方法：
   • 按业务分库
   • 按表分库

2) 分表

1. 当单表数据增量过快，一般超过500万的数据量就要考虑分表了
2. 解决什么问题？
   • 解决 数据量大 的问题
     • 数据量大，存储 & 查询的性能遇到了瓶颈
3. 分表方法：
   • 水平拆分
     • hash
     • 范围
     • 建立映射表
   • 垂直拆分

3) 带来的问题

① 垂直拆分

1. 跨库 join 问题
   • 问题：
     • MySQL 开发规范，一般是禁止跨库 Join
   • 解决方案：
     • 建立全局表，MyCat 中有个全局表的概念，每个 DataNode 上都有一份全量数据，例如一些数据字典表，数据很少修改，可以避免跨库 Join 的性能问题
     • 借助数据同步工具进行数据同步
     • 字段冗余：把需要关联的字段放入主表中，避免 join 操作
2. 分布式事务
   • 原本对单个表的 DML 操作就变成了多个子表的 DML 操作，涉及到分布式事务
   • 解决方案：
     • InnoDB 支持 分布式事务[XA 事务]
       • 采用 2PC 两阶段提交，如果任何一个 XA Client 否决了此次提交，所有数据库都要求 XA Manager 回滚它们在事务中的信息
       • 可以最大程度保证了数据的强一致，适合对数据强一致要求很高的业务场景
       • 实现复杂，牺牲了可用性，对性能影响较大，不适合高并发高性能场景
     • 本地消息表
       • 将远程分布式事务拆分成一系列的本地事务
       • 消息生产方：需要额外建一个消息表，并记录消息发送状态
       • 消息消费方：需要处理这个消息，并完成自己的业务逻辑
       • 生产方和消费方定时扫描本地消息表，把还没处理完成的消息或者失败的消息再发送一遍

② 水平拆分

1. 跨库 join 问题
2. 分布式全局唯一ID的生成
   • Snowflake 算法
   • uuid
   • redis 缓存
3. 跨库排序分页复杂度提高
4. 跨库函数处理变复杂

4) 分库分表工具

1. MyCat
   • 分布式关系型数据库中间件
   • 支持分布式查询，兼容 MySQL 通信协议，以 Java 生态支持多种后端数据库，通过 数据分片 提高数据查询处理能力
2. sharding-sphere
   • 轻量级 Java 框架，在 Java 的 JDBC 层提供额外服务
   • 使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署 & 依赖，完全兼容 JDBC & 各种 ORM 框架
3. TDDL
   • 淘宝开源的一个用于访问数据库的中间件
   • 集成 分库分表、读写分离、权重调配、动态数据源配置等功能
   • 封装 JDBC 的 DataSource 给用户提供统一的基于客户端的使用

---

4. 不停机扩容

1) 在线双写，查询走老库

• 建立好新的库表结构，数据写入同时写 新库 & 老库
• 数据迁移
  • 使用数据迁移程序，将老库中的历史数据迁移到新库
• 使用定时任务，将新老库的数据对比，将差异补齐

2) 在线双写，查询走新库

• 完成历史数据的同步 & 校验
• 查询走新库

3) 老库下线

• 老库不再提供读写服务

---

---

5. 运维问题

1) 百万级别以上的数据如何删除？

• MySQL 官方手册中提到，数据删除速度 与 索引数量 成正比

• 先删 索引

• 再删 无用数据

• 删除之后重新重建索引

2) 百万千万级别大表如何添加字段？

• 当数据量特别大时，加一个字段就没那么简单，可能会出现长时间的锁表
• 常用方法：
  • 通过 中间表 的转换
    • 创建一个 临时新表，将旧表的结构完全复制过去，添加字段，再把旧表的数据复制到新表，删除旧表，将新表命名为旧表的名称
  • 使用 pt-online-schema-change 工具
    • 该工具可以在线修改表结构，它的原理也是通过 中间表
  • 先在从库添加，再进行 主从切换
    • 若表数据量大 & 读写频繁，此时可以考虑在从库添加字段，然后进行 主从切换，切换后再在其他节点添加字段

3) MySQL 数据库 CPU 飙升的话，如何处理？

1. 排查过程：
   • 通过 top 命令查看是否是 MySQL 进程导致
   • 通过 show processlist，查看 session 情况，确定是否有消耗资源的 sql 运行
   • 找到消耗高的 sql，通过 explain 查看执行计划是否准确
2. 如何处理？
   • kill 掉消耗高的线程，观察 cpu 使用率是否下降
   • 进行相应的优化
   • 如果是突然的大量 session 连接，分析连接数为何会激增，可以限制连接数