MySQL 数据库优化记录

数据库优化原则

系统的吞吐量瓶颈往往出现在数据库的 访问速度 上，随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢，因为其数据是存放在 磁盘 上的，读写速度无法和内存相比，所以使用数据库时，十分有必要了解数据库优化问题。其优化原则为：减少系统瓶颈，减少资源占用，增加系统的反应速度。

数据库结构优化

在数据库设计中，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。可通过以下方式进行数据库结构的优化：

• 将字段很多的表分解成多个表
  对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

• 增加中间表
  对于需要经常 联合查询 的表，通过建立中间表以提高查询效率，具体地，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

• 增加冗余字段
  众所皆知，设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差，所以合理的加入冗余字段可以提高查询速度。

MySQL 数据库 CPU 使用率飙升到 500% 的话，如何处理？

当 CPU 飙升到 500% 时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
如果是 mysqld 造成的，通过 SHOW PROCESSLIST 查看正在运行的线程，是不是有消耗资源的 SQL 在运行，找出其中消耗高的 SQL，看看执行计划是否准确， index 是否缺失，或者是数据量太大造成。
然后 kill 掉这些线程（同时观察 CPU 使用率是否下降），等进行相应的调整（比如说加索引、改 SQL、改内存参数）之后，再重新跑这些 SQL。
若每个 SQL 消耗资源都不多，只是同一时间大量的 session 连进来导致 CPU 飙升，这种情况就需要分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

为什么要分库分表

数据库中的数据量不一定是可控的，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地数据操作，例如 增删改查的开销 也会越来越大；另外，若不进行分布式部署，而一台服务器的 资源 （CPU、磁盘、内存、IO 等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。所以，从 性能 和 可用性 角度考虑，会进行数据库拆分处理，具体地说，把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上，即 分库分表。

分库分表的具体实施策略

分库分表有 垂直切分 和 水平切分 两种方式，在复杂的业务场景中，也可能会选择两者结合的方式。

分库分表存在哪些问题

进行分库分表操作后，可能会面临以下几类问题：

• 事务问题

​ 分库分表后，就成了分布式事务。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

• 跨库跨表的 JOIN 问题

​ 在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法 JOIN 位于不同分库的表，也无法 JOIN 分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

• 额外的数据管理负担和数据运算压力

  额外的数据管理负担，最为常见的是数据的 定位问题 和数据的 增删改查 的重复执行问题，这些都可以通过应用程序来解决，但必然会引起额外的逻辑运算。

什么是 MySQL 主从复制

主从复制是指将 主数据库（Master）中的 DDL 和 DML 操作通过二进制日志传输到 从数据库（Slave） 上，然后将这些日志重新执行（重做），从而使得从数据库的数据与主数据库保持一致。MySQL 支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。

• 主从复制的作用有：

当主数据库出现问题时，可以切换到从数据库；
可以进行数据库层面的读写分离，实现负载均衡；
可以在从数据库上进行实时数据备份。

MySQL 主从复制的工作原理

MySQL 的主从复制是一个 异步 的复制过程（一般情况下感觉是实时的），数据将从一个 MySQL 数据库（Master）复制到另外一个 MySQL 数据库（Slave），在 Master 与 Slave 之间实现整个主从复制的过程是由三个线程参与完成的，其中有两个线程（SQL 线程和 I/O 线程）在 Slave 端，另外一个线程（ I/O 线程）在 Master 端。

• 基本原理流程

  Master 端：打开二进制日志（binlog ）记录功能 —— 记录下所有改变了数据库数据的语句，放进 Master 的 binlog 中；

  Slave 端：开启一个 I/O 线程 —— 负责从 Master上拉取 binlog 内容，放进自己的中继日志（Relay log）中；

  Slave 端：SQL 执行线程 —— 读取 Relay log，并顺序执行该日志中的 SQL 事件。

MySQL 读写分离的实现方案

MySQL 读写分离的实现方式主要基于 主从复制，通过 路由的方式 使应用对数据库的写请求只在 Master 上进行，读请求在 Slave 上进行。

具体地，有以下四种实现方案：

• 方案一：基于 MySQL proxy 代理

  在应用和数据库之间增加 代理层，代理层接收应用对数据库的请求，根据不同请求类型（即是读 read 还是写 write）转发到不同的实例，在实现读写分离的同时可以实现负载均衡。MySQL 的代理最常见的是 mysql-proxy、cobar、mycat、Atlas 等。

• 方案二：基于应用内路由

  基于应用内路由的方式即为在应用程序中实现，针对不同的请求类型去不同的实例执行 SQL。

  具体实现可基于 spring 的 aop：用 aop 来拦截 spring 项目的 dao 层方法，根据方法名称就可以判断要执行的类型，进而动态切换主从数据源。

• 方案三：基于 MySQL-Connector-Java 的 JDBC 驱动方式

  Java 程序通过在连接 MySQL 的 JDBC 中配置主库与从库等地址，JDBC 会自动将读请求发送给从库，将写请求发送给主库，此外， MySQL 的 JDBC 驱动还能够实现多个从库的负载均衡。

• 方案四：基于 sharding-jdbc 的方式

  sharding-sphere 是强大的读写分离、分表分库中间件，sharding-jdbc 是 sharding-sphere 的核心模块。

本作品采用《CC 协议》，转载必须注明作者和本文链接