关于 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的错误。详细如下：

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 就结束了，那么此刻很有或许该衔接在履行结束之后，依然占用该衔接。怎样办呢？咱们可以将一些不常用的衔接开释掉。详细如下：

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，此刻假如咱们断开该衔接，会形成事务丢失和客户端报错。