这样的优化对 MySQL 来说作用微乎其微

2023-05-09 16:18:25 浏览数 (1)

对于 MySQL 数据库来说，我们最常遇到的就是关于其优化的问题。

在面试的过程中，面试官必问的一个问题也是 MySQL 的优化问题。

通常，我们在回答 MySQL 数据库优化的相关问题时，一般会从三个层面来说明，分别是：

  • 硬件层面；
  • 存储引擎层面；
  • SQL 语句层面。

今天，我们在这里不展开说明这些问题，而是跟大家介绍在这些优化的层面中，有哪些是优化对 MySQL 数据库来说作用微乎其微，以便我们在产生环境中调优 MySQL 数据库时，避免一些不必要的优化。

一、硬件层面

首先，我们介绍一下关于 MySQL 数据库硬件层面的优化。

要说硬件层面的优化，无外乎是 CPU、网络、磁盘和内存条四个方面。

其中，CPU 处理数据的能力的强弱直接影响着 MySQL 数据库处理数据的时间，也就是说 CPU 处理数据的能力越强，MySQL 数据库在处理数据时的速度就越快。

网卡的转发能力的强弱影响着网络延时的长短。举个例子，去年公司开年会的时候，一个必要的环节就是老板发红包，我用的是iPhone 13，我旁边的一个同事用的是华为，基本上我没有抢到什么红包，但是华为手机却抢到了很多。这个原因其实大家都知道，是华为手机的信号基带延时是非常低的，在电梯里面都有信号，用过的小伙伴都知道（这里建议大家等华为再次开卖的时候可以去尝试一下华为手机）。这个例子其实可以说明的是在同等条件下，网卡的网络转发能力越强，MySQL 数据库接收处理的信号的速度就越快。

上面两个方面的优化一般可以说性能越好效果就越好，但是磁盘和内存条的性能越好，效果不一定越好。

我们这就介绍一下磁盘。

我们通常接触到的一个说法是固态硬盘的性能要比机械硬盘的性能要好。那么假设我们现在有一台 MySQL 数据库的服务器，它的底层存储是机械硬盘；我们现在需要优化这台 MySQL 数据库，按照前面的说法，我们可以得知只需要将机械硬盘更换成固态硬盘即可。

但是事实是这样吗？不一定。

这个时候，有很多朋友肯定很想问：为什么更换成了固态硬盘还不一定能够优化 MySQL 数据库的性能呢？

这是因为，MySQL 数据库并不是直接通过磁盘去了解磁盘的 IO 能力的，而是通过其自身的一个叫 innodb_io_capacity 的参数来控制的。

假设，你是按照机械硬盘的 IO 能力来设置 innodb_io_capacity 的，那么此时哪怕你将机械硬盘更换成固态硬盘，MySQL 数据库仍然认为底层使用的是机械硬盘，以至于更换固态硬盘之后的 MySQL 数据库性能的提升微乎其微。

内存也是一样。

在 第七篇文章：InnoDB 存储引擎的底层逻辑架构 中，我们在了解 InnoDB 存储引擎的底层原理时，我们可以清楚地了解到，要想使 MySQL 数据库处理数据的能力有所提升，那么单纯地提升 MySQL 部署的服务器的内存空间是不行的。而是需要在提升服务器的内存空间大小时，同时修改 InnoDB 存储引擎的 buffer pool 的内存空间大小，这样才能有效地提升 MySQL 数据库的性能。

二、存储引擎层面

存储引擎层面在上面我们已经介绍过了，这里不再赘述。

三、SQL 语句层面

SQL 层面，是我们优化 MySQL 数据库最直接的一个层面，在这个层面中，有很多需要我们注意的地方，下面我们就来了解一下这个话题。

注意：在详细说明 SQL 层面相关问题之前，我们需要先阅读 第二篇：一条 SQL 的生命周期，了解 SQL 的运行周期。

1. 索引是不是越多越好？

要想搞清楚索引是不是越多越好，首先我们需要搞清楚索引是什么？

MySQL 官方给的解释是：MySQL 数据库中索引是一种用作一列或多列值之间排序的数据结构。

所以，一般常用排序的字段我们是需要加上索引的，不常用的字段通常情况下不建议添加索引。

而且，索引我们也可以形象地将其理解为 MySQL 数据库中的一种特殊的虚拟数据表，这个虚拟数据表中的字段只有当前这个索引所包含的字段，然后存储在 .ibd 文件中；也就是说，当我们建立的索引越多， MySQL 数据库维护的索引文件就越多，那么如果某一个数据表中的数据比较少时，建立过多的索引对于 MySQL 数据库来说其实也是一种负担。

2. 是不是每次连接数据库就一定能够成功？

我们在读 第二篇：一条 SQL 的生命周期 的时候了解到，一条 SQL 执行之前客户端和服务端之间是需要建立 TCP 连接的。

要知道，建立 TCP 连接除了需要三次握手并且还需要权限认证以及安全认证，在这个认证和建立连接的过程中是非常消耗时间的。

那么，如果在某一个很短的时间内，建立了很多 MySQL 数据库的连接，此时的 MySQL 数据库很有可能会不堪重负进而导致宕机。

为了解决这一问题，MySQL 数据库提供了一个 max_connections 参数，这个参数的主要作用是限制同一时间创建 MySQL 数据库的连接的上限。如果同一时间创建的连接数超过了该参数设置的值之后，MySQL 数据库会返回一个 ERROR 1040 (HY000): Too many connections 的错误。具体如下：

代码语言：javascript复制
mysql> show variables like "max_connections";
 ----------------- ------- 
| Variable_name   | Value |
 ----------------- ------- 
| max_connections | 151   |
 ----------------- ------- 
1 row in set (0.01 sec)

-- 设置链接客户端上限
mysql> set global max_connections=1;
Query OK, 0 rows affected (0.00 sec)

-- 新开一个客户端
[root@dxd ~]# mysql -uroot -pTest123!
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
复制代码

细心的朋友肯定就会发现，这是有问题的。

如果我们在生产环境中将 max_connections 这个参数设置成某一个值时，恰好在某个时间段内创建的连接超过了 max_connections 的值时，那么此时 MySQL 数据库会直接拒绝连接，反应到业务层面的话，就是数据库连接失败。

我们知道，这会给用户造成非常不好的用户体验。

怎么解决这个问题呢？通常会有两个办法。

  • 第一个办法：释放使用较少的连接

通常，有部分业务，执行的 SQL 非常少，例如：查询用户信息，可能只需要执行几条 SQL 就结束了，那么此时很有可能该连接在执行结束之后，仍然占用该连接。怎么办呢？我们可以将一些不常用的连接释放掉。具体如下：

代码语言：javascript复制
mysql> show processlist;
 ---- ------------- ----------- ------ --------- ------ ------------------ ------------------ 
| Id | User        | Host      | db   | Command | Time | State            | Info             |
 ---- ------------- ----------- ------ --------- ------ ------------------ ------------------ 
|  4 | root        | dxd:54530 | NULL | Query   |    0 | starting         | show processlist |
|  5 | root        | dxd:54536 | NULL | Sleep   |   89 |                  | NULL             |
|  6 | root        | dxd:54544 | NULL | Sleep   |   84 |                  | NULL             |
 ---- ------------- ----------- ------ --------- ------ ------------------ ------------------ 
5 rows in set (0.00 sec)
复制代码

从上面的代码中，我们可以看出的是后面两个连接处于 sleep 状态，该状态就代表该连接处于空闲状态，我们可以直接将其断开。

但是，这种操作需要谨慎使用。

  • 第二个办法，在业务代码层保存某一个连接重复使用

前面我们说了，建立数据库的连接是非常消耗时间并且 MySQL 数据库的连接也可以无限创建。

那么，我们可不可以选择一个折中的方案，在客户端将建立好了的数据库连接保存下来，下一次使用理论上是可以直接使用的。

事实上，这种方式在客户端层面也叫连接池，主要是将创建好了的数据库连接保存在内存中，下一次其他请求需要使用可以直接拿出来使用，不用再修改连接的时间以及认证的时间。

总结

今天，主要介绍了 MySQL 数据库优化的过程中常见的、容易产生误解的优化方式。

在硬件层面的优化我们并不是单纯地认为硬件的性能越好对 MySQL 数据库优化的作用就越好，而是需要配合 MySQL 数据库的配置，以至于 MySQL 数据库能够更好地适配该硬件。

SQL 层面跟大家介绍了两个方面，分别是索引方面和连接方面。首先要说明的是对于 MySQL 数据库来说，并不是索引创建得越多越好，通常情况下是小表尽量不要使用索引，因为此时的索引会拖累 MySQL 数据库的性能。其次对于 MySQL 数据库的连接来说，创建一个连接的时间消耗是不可避免的，同时 MySQL 数据库的连接数是有限的，不能无限地创建 MySQL 数据库的连接；此时我们通常采用的措施是释放空闲中的连接同时在客户端保存创建好了的连接以便下次使用。

在实际工作中，我们释放空闲的连接时，并不能单纯地认为 show processlist; 语句中 Command 是 Sleep 状态就可以释放，这是因为一个连接处于一个事务中时，该字段仍然显示的是 Sleep，此时如果我们断开该连接，会造成事务丢失和客户端报错。

后端

0 人点赞