数据库优化方案

秒速五厘米 2024-03-22 20:17 27阅读 0赞

##  ##

**目录**

前言

一、MySQL 压测 结果

一、MySQL 5.6 测试结果

场景一：全缓存

场景二：磁盘 IO 型

二、MySQL 5.7 测试结果

场景一：全缓存

场景二：磁盘 IO 型

数据库读写分离架构

主从读写

读写分离方式

内嵌运行 在应用程序内部

数据库代理

自动读写分离

MySQL 数据变更记录分析

运行原理

--------------------

## 前言 ##

MySQL Binlog 是记录 MySQL 所有数据变动的二进制日志文件，用于 MySQL 主从复制和数据恢复。此外开发者还可以将订阅 MySQL Binlog 应用在**增量索引**、**缓存一致性**、**基于数据的任务分发**、**记录数据变更**等场景。

--------------------

## 一、MySQL 压测 结果 ##

我们一起来看看当查询请求增加时，应该如何去做数据库优化。我们先看看MySQL的压测结果，关注TPS和QPS，以下数据仅供参考。

### 一、MySQL 5.6 测试结果 ###

#### 场景一：全缓存 ####

全缓存指只有全部数据可以放到缓存里，查询过程中不需要读写磁盘更新缓存。

<table style="width:650px;"> 
 <tbody> 
  <tr> 
   <td style="width:111px;">CPU（core）</td> 
   <td style="width:111px;">内存（MB）</td> 
   <td style="width:73px;">并发度</td> 
   <td style="width:106px;">单表数据量</td> 
   <td style="width:90px;">表总数</td> 
   <td>TPS</td> 
   <td>QPS</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">4</td> 
   <td style="width:111px;">8000</td> 
   <td style="width:73px;">32</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>2307.1</td> 
   <td>46142</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">4</td> 
   <td style="width:111px;">16000</td> 
   <td style="width:73px;">32</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>2229.8</td> 
   <td>44596.1</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">8</td> 
   <td style="width:111px;">16000</td> 
   <td style="width:73px;">64</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>3771.25</td> 
   <td>75425</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">8</td> 
   <td style="width:111px;">32000</td> 
   <td style="width:73px;">64</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>3909.36</td> 
   <td>78187.1</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">32000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>6102.35</td> 
   <td>122047</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">64000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>6788.83</td> 
   <td>135777</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">96000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>6771.81</td> 
   <td>135436</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">128000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>7039.65</td> 
   <td>140793</td> 
  </tr> 
 </tbody> 
</table>

#### 场景二：磁盘 IO 型 ####

磁盘 IO 型场景指只有部分数据可以放到缓存里，查询过程中需要读写磁盘更新缓存。

<table style="width:650px;"> 
 <tbody> 
  <tr> 
   <td style="width:111px;">CPU（core）</td> 
   <td style="width:111px;">内存（MB）</td> 
   <td style="width:73px;">并发度</td> 
   <td style="width:106px;">单表数据量</td> 
   <td style="width:90px;">表总数</td> 
   <td>TPS</td> 
   <td>QPS</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">4</td> 
   <td style="width:111px;">8000</td> 
   <td style="width:73px;">32</td> 
   <td style="width:106px;">800000</td> 
   <td style="width:90px;">48</td> 
   <td>2189.87</td> 
   <td>43797.4</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">4</td> 
   <td style="width:111px;">16000</td> 
   <td style="width:73px;">32</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">13</td> 
   <td>2138.75</td> 
   <td>42775.1</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">8</td> 
   <td style="width:111px;">16000</td> 
   <td style="width:73px;">64</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">13</td> 
   <td>3589.44</td> 
   <td>71788.8</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">8</td> 
   <td style="width:111px;">32000</td> 
   <td style="width:73px;">64</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">25</td> 
   <td>3535.54</td> 
   <td>70710.8</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">32000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">25</td> 
   <td>5550.31</td> 
   <td>111006</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">64000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">49</td> 
   <td>6414.39</td> 
   <td>128288</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">96000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">74</td> 
   <td>5874.64</td> 
   <td>117493</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">128000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">98</td> 
   <td>5611.06</td> 
   <td>112221</td> 
  </tr> 
 </tbody> 
</table>

### 二、MySQL 5.7 测试结果 ###

#### 场景一：全缓存 ####

全缓存指只有全部数据可以放到缓存里，查询过程中不需要读写磁盘更新缓存。

<table style="width:650px;"> 
 <tbody> 
  <tr> 
   <td style="width:111px;">CPU（core）</td> 
   <td style="width:111px;">内存（MB）</td> 
   <td style="width:73px;">并发度</td> 
   <td style="width:106px;">单表数据量</td> 
   <td style="width:90px;">表总数</td> 
   <td>TPS</td> 
   <td>QPS</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">4</td> 
   <td style="width:111px;">8000</td> 
   <td style="width:73px;">32</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>2518.13</td> 
   <td>50362.6</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">4</td> 
   <td style="width:111px;">16000</td> 
   <td style="width:73px;">32</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>2424.66</td> 
   <td>48493.2</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">8</td> 
   <td style="width:111px;">16000</td> 
   <td style="width:73px;">64</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>4235.31</td> 
   <td>84706.2</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">8</td> 
   <td style="width:111px;">32000</td> 
   <td style="width:73px;">64</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>4112.97</td> 
   <td>82259.5</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">32000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>7154.77</td> 
   <td>143095</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">64000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>7888.35</td> 
   <td>157767</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">96000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>7902.25</td> 
   <td>158045</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">128000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">150</td> 
   <td>7910.24</td> 
   <td>158205</td> 
  </tr> 
 </tbody> 
</table>

#### 场景二：磁盘 IO 型 ####

磁盘 IO 型场景指只有部分数据可以放到缓存里，查询过程中需要读写磁盘更新缓存。

<table style="width:650px;"> 
 <tbody> 
  <tr> 
   <td style="width:111px;">CPU（core）</td> 
   <td style="width:111px;">内存（MB）</td> 
   <td style="width:73px;">并发度</td> 
   <td style="width:106px;">单表数据量</td> 
   <td style="width:90px;">表总数</td> 
   <td>TPS</td> 
   <td>QPS</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">4</td> 
   <td style="width:111px;">8000</td> 
   <td style="width:73px;">32</td> 
   <td style="width:106px;">800000</td> 
   <td style="width:90px;">48</td> 
   <td>2491.65</td> 
   <td>49832.9</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">4</td> 
   <td style="width:111px;">16000</td> 
   <td style="width:73px;">32</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">13</td> 
   <td>2343.67</td> 
   <td>46873.4</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">8</td> 
   <td style="width:111px;">16000</td> 
   <td style="width:73px;">64</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">13</td> 
   <td>4075.31</td> 
   <td>81506.2</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">8</td> 
   <td style="width:111px;">32000</td> 
   <td style="width:73px;">64</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">25</td> 
   <td>3895.22</td> 
   <td>77904.3</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">32000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">25</td> 
   <td>6612.82</td> 
   <td>132256</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">64000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">49</td> 
   <td>6995.9</td> 
   <td>139918</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">96000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">6000000</td> 
   <td style="width:90px;">74</td> 
   <td>6985.35</td> 
   <td>139707</td> 
  </tr> 
  <tr> 
   <td style="width:111px;">16</td> 
   <td style="width:111px;">128000</td> 
   <td style="width:73px;">128</td> 
   <td style="width:106px;">25000</td> 
   <td style="width:90px;">98</td> 
   <td>6980.42</td> 
   <td>139608</td> 
  </tr> 
 </tbody> 
</table>

## 数据库读写分离架构 ##

其实，大部分系统的访问模型是读多写少，读写请求量的差距可能达到几个数量级。 这很好理解一个商品的浏览量肯定远远大于它的下单量。因此我们优先考虑数据库如何扛住更高的查询请求。那么我们首先想到的就是把读写流量区分开，这样我们可以单独的对读流量做扩展。就是我们通常说的读写分离。

在我们的实际项目中也出现过当前端流量突增时从库负载过高的问题，这时候DBA管理员会做一个从库扩容上去，这样前端请求的流量就会均衡的落到多个从库上，从库的负载就会降下来。

![827fb889c7bbe692f171009f03b9194a.png][]

### **主从读写** ###

一般来说在主从读写分离机制中，我们将一个数据库的数据拷贝为一份或者多份，并且写入到其它的数据库服务器中，原始的数据库我们称为主库，主要负责数据的写入，拷贝的目标数据库称为从库，主要负责支持数据查询。

![4ba769eb6ba34894bd8d509bfeea366e.png][]

你会发现，基于性能的考虑，主库的写入流程并没有等待主从同步完成就会返回结果，那么在极端的情况下，比如说主库上 binlog 还没有来得及刷新到磁盘上就出现了磁盘损坏或者机器掉电，就会导致 binlog 的丢失，最终造成主从数据的不一致。不过，这种情况出现的概率很低，对于互联网的项目来说是可以容忍的。

做了主从复制之后，我们就可以在写入时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响到读请求的执行。同时呢，在读流量比较大的情况下，我们可以部署多个从库共同承担读流量，这就是所说的“一主多从”部署方式，在你的垂直电商项目中就可以通过这种方式来抵御较高的并发读流量。另外，从库也可以当成一个备库来使用，以避免主库故障导致数据丢失。

那么你可能会说，是不是我无限制地增加从库的数量就可以抵抗大量的并发呢？实际上并不是的。因为随着从库数量增加，从库连接上来的 IO 线程比较多，主库也需要创建同样多的log dump 线程来处理复制的请求，对于主库资源消耗比较高，同时受限于主库的网络带宽，所以在实际使用中，一般一个主库最多挂 3～5 个从库。

### 读写分离方式 ###

#### 内嵌运行 在应用程序内部 ####

第一类以淘宝的 TDDL（ Taobao Distributed Data Layer）为代表，以代码形式内嵌运行在应用程序内部。你可以把它看成是一种数据源的代理，它的配置管理着多个数据源，每个数据源对应一个数据库，可能是主库，可能是从库。当有一个数据库请求时，中间件将SQL 语句发给某一个指定的数据源来处理，然后将处理结果返回。这一类中间件的优点是简单易用，没有多余的部署成本，因为它是植入到应用程序内部，与应用程序一同运行的，所以比较适合运维能力较弱的小团队使用；缺点是缺乏多语言的支持，目前业界这一类的主流方案除了 TDDL，还有早期的网易 DDB，它们都是 Java 语言开发的，无法支持其他的语言。另外，版本升级也依赖使用方更新，比较困难。

#### 数据库代理 ####

另一类是单独部署的代理层方案，这一类方案代表比较多，如早期阿里巴巴开源的Cobar，基于 Cobar 开发出来的 Mycat，360 开源的 Atlas，美团开源的基于 Atlas 开发的 DBProxy 等等。这一类中间件部署在独立的服务器上，业务代码如同在使用单一数据库一样使用它，实际上 它内部管理着很多的数据源，当有数据库请求时，它会对 SQL 语句做必要的改写，然后发 往指定的数据源。它一般使用标准的 MySQL 通信协议，所以可以很好地支持多语言。由于它是独立部署的，所以也比较方便进行维护升级，比较适合有一定运维能力的大中型团队使用。它的缺陷是所有的 SQL 语句都需要跨两次网络：从应用到代理层和从代理层到数据源，所以在性能上会有一些损耗。

### 自动读写分离 ###

用户业务场景中存在读多写少、业务负载无法预测等场景，有大量读请求的应用场景下，单个实例可能无法承受读取压力，甚至对业务产生影响。 为了实现读取能力的弹性扩展，分担数据库压力，可以创建一个或多个只读实例，利用只读实例满足大量的数据库读取需求。但此类解决方案需要业务侧支持读写分离改造，其代码的健壮性决定了业务读写分离的质量，对客户的技术要求较高，而且灵活性和可扩展性较差。

## MySQL 数据变更记录分析 ##

![626eb54150e6818d4d3201f31fc7d246.png][]

### 运行原理 ###

Kafka 连接器通过 Binlog 同步组件订阅 MySQL Binlog，然后将数据变更记录转化为 JSON 格式的消息生产到 Kafka 中。

[827fb889c7bbe692f171009f03b9194a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/fc4a1ca256194fd3b93d43e4503e3f75.png
[4ba769eb6ba34894bd8d509bfeea366e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/92ba90fbcbb94c368f71137e098c82cc.png
[626eb54150e6818d4d3201f31fc7d246.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/5ee7b1220042409991f414e5f8ae3cbf.png