MySQL性能应该从哪些维度进行优化？

来源：陈木峰（ID：chanmufeng1994）

如果面试官问你：你想从哪些维度来优化MySQL性能？你会如何回答？

所谓的性能优化一般都是针对MySQL查询的优化。既然我们在优化查询，自然首先要知道查询经过了哪些环节，然后再思考哪些环节可以优化。

如何执行我之前写的SQL查询语句？ ，有兴趣的朋友可以阅读。我使用其中一张图像来显示查询操作必须经过的基本链接。

部分SQL查询

下面从5个角度介绍一些MySQL优化的策略。

1.连接配置优化 处理连接是MySQL客户端和MySQL服务器建立关系的第一步。第一步迈得不好，就别谈后面的故事了。既然连接是双方的事情，我们自然会从服务器端和客户端两方面进行优化。 1。服务器配置 服务器需要做的是接受尽可能多的客户端连接。也许您遇到过错误错误1040：连接太多？这是服务器心胸不够宽，布局太小！

我们可以从两个方面解决连接数不足的问题：

• 增加可用连接数，修改环境变量max_connections。默认情况下，服务器最大连接数为151；

mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+1 row in set (0.01 sec)

• 及时释放不活跃的连接。系统默认的客户端超时时间为28800秒（8小时）。我们可以降低这个值。

mysql> show variables like 'wait_timeout';+---------------+-------+| Variable_name | Value |+---------------+-------+| wait_timeout  | 28800 |+---------------+-------+1 row in set (0.01 sec)

MySQL 有很多配置参数，并且大多数参数都提供默认值。默认值是MySQL作者精心设计的，完全可以满足大多数情况的需要。在不了解参数含义的情况下不建议使用该参数。快点改变吧。 ？每个语句都会创建一个新连接，服务器和客户端上的资源都会被淹没。解决方案是使用连接池来重用连接。已部署的数据库复合池包括 arkedbcp、C3P0、 issues SHOULD、HIKARI、 目前正在全面展开。

但是需要注意的是，连接池越大越好。例如，Druid的最大默认连接池大小为8，Hikari的最大默认连接池大小为10blind。增加连接池的大小会降低系统执行的效率。为什么？

对于每个连接，服务器都会创建一个单独的线程来处理它。连接越多，服务器创建的线程就越多。当线程数量超过CPU数量时，CPU必须分配时隙来进行线程上下文切换。频繁的上下文切换会带来大量的性能开销。

Hikari官方提供了PostgreSQL数据库连接池大小的推荐值公式，CPU核心数*2+1。假设服务器CPU核数为4，连接池设置为9即可。这个公式在某种程度上也适用于其他数据库，面试时可以吹牛。 2。架构优化1.使用缓存 系统中难免会出现一些慢查询。这些查询要么数据量大，要么是复杂查询（很多相关表或者复杂计算），导致查询长时间消耗连接。如果这类数据的有效性不是很强（不是每时每刻都在变化，比如日报），我们可以将这类数据放入缓存系统中，在缓存有效期内直接从缓存系统中检索的数据。数据，减轻数据库压力，提高查询效率。

缓存的使用2。读写分离（集群、主从复制）项目初期，数据库通常运行在一台服务器上，毕竟用户的所有读写请求都会直接影响并发量单个服务器可以承载的该数据库服务器是有限的。为了解决这个问题，我们可以同时使用多台数据库服务器，将其中一台作为组长，称为主节点，其他节点作为组员，称为从机。用户只将数据写入主节点，读请求则分发到不同的从节点。这种解决方案称为读写分离。命名由组长和组成员组成的小组，并将其称为集群。

这就是集群许多开发者对主从这个冒犯性的词感到不满（因为他们认为这会与种族歧视、黑奴等联系在一起），因此发起了更名运动。受此影响，MySQL将逐渐停止使用master、slave等术语，取而代之的是source和replica。每个人遇到他们时都能理解他们。 使用集群时必须面对的一个问题是如何保持多个节点之间的数据一致性。毕竟写请求只是发送到主节点，只有主节点的数据才是最新的数据。如何将主节点上的写操作同步到各个从节点上？ 主从复制技术来了！ Binlog是实现MySQL主从复制功能的核心组件。主节点会将所有的写操作记录在binlog中。从节点会有专用的I/O线程来读取主节点的binlog，并将写操作同步到当前从节点。

主从复制

这种集群架构对于减轻主数据库服务器的压力有非常好的效果。但随着业务数据越来越多，如果某个表的数据量大幅增加，单表查询性能会明显下降，即使采用读写分离也无法解决这个问题。毕竟，所有节点都存储完全相同的数据。如果单表的查询性能很差，按理说所有节点的性能也很差。

此时，我们就可以将数据从单个节点分散到多个节点进行存储。这是子数据库和子表。 3。子库和子表子库和子表中节点的含义比较广泛。如果数据库作为节点，则为子数据库；如果单个表作为节点，则为子表。大家都知道分库分表分为垂直分库、垂直分表、水平分库、水平分表，但每当不记得这些概念的时候，我都会详细解释一下，帮助大家理解。

1）垂直分库

垂直分库

在单个数据库的基础上做几个垂直切片，按照业务逻辑划分为不同的数据库。这是垂直子数据库La。

垂直切分

2）表格的垂直切分

表格的垂直切分垂直切分就是在单个表格的基础上进行一次垂直切分（或多次切分），将表格中的几个单词缩短为几个小桌子。这个操作要根据具体业务来判断。通常，经常使用的字段（热字段）分为一张表，不经常使用或不立即使用的字段（冷字段）分为另一张表。提高查询速度。

立式表格

以上图为例：通常产品信息比较长，在查看产品列表时，往往不需要立即显示产品信息（通常会点击详情按钮显示）。相反，将显示有关产品的更重要的信息（价格等）。根据这个业务逻辑，我们将原来的商品表拆分成了竖表。 ？ 水平桌

。

水平分片

水平分片

4) 水平分片

水平分片 硬分片 通常发生在水平方向上 单一数据库。

水平切片

水平切片

5）总结

水平切片主要是为了解决存储瓶颈；垂直切割主要是为了减少并发压力。 4。消息队列顶剃通常情况下，用户请求会直接访问数据库。如果同时在线的用户数量非常多，很有可能压垮数据库（看明星出轨或者公布恋情的微博）。这种情况下，可以通过使用消息队列来减轻数据库的压力。无论同时有多少个用户请求，都会首先存储在消息队列中，然后系统从消息队列中有序地消费请求。

队列调峰3。优化器——SQL分析与优化处理完连接、优化缓存等架构后，SQL查询语句就来到了解析器和优化器的区域。如果这一步出现问题，那只能是SQL语句的问题。只要你的语法没有问题，解析器就不会有问题。另外，为了防止你写的SQL运行效率低下，优化器会自动进行一些优化，但如果真的很糟糕，优化器也救不了你，只能看到你的SQL查询变成慢查询。 1。慢查询 慢查询是执行得很慢的查询（这句话听起来像是废话……）。只有知道MySQL中哪些是慢查询，我们才能执行有针对性的查询。优化。由于开启慢查询日志有性能成本，MySQL默认关闭慢查询日志功能。使用以下命令查看当前慢查询状态。

mysql> show variables like 'slow_query%';+---------------------+--------------------------------------+| Variable_name       | Value                                |+---------------------+--------------------------------------+| slow_query_log      | OFF                                  || slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |+---------------------+--------------------------------------+2 rows in set (0.00 sec)

slow_query_log表示当前是否开启慢查询日志，slow_query_log_file表示慢查询日志。

除了上面两个变量之外，我们还需要确定“慢”指标是什么，即执行慢速查询需要多长时间。默认为 10S。如果更改为0 记录所有 SQL。

mysql> show variables like '%long_query%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)

1）打开慢日志

打开慢日志有两种方法。

• 编辑配置文件my.cnf

此修改方法在系统重启后仍然有效。

# 是否开启慢查询日志slow_query_log=ON# long_query_time=2slow_query_log_file=/var/lib/mysql/slow.log

• 动态更改参数（重启后无效）

mysql> set @@global.slow_query_log=1;Query OK, 0 rows affected (0.06 sec)
mysql> set @@global.long_query_time=2;Query OK, 0 rows affected (0.00 sec)

2）慢日志分析

mysql不仅为我们保存了慢日志，还为我们提供了慢日志查询工具mysqldumpslow来演示一下这个工具我们先构造一个慢查询：

mysql> SELECT sleep(5);

然后查询耗时最多的慢查询：

[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log
Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.logCount: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost  SELECT sleep(N)

其中，

• Count: 次，代表该 SQL 已被执行。
• 时间：表示执行时间，括号内为累计时间。
• 锁定：表示锁定时间，括号内为累计时间。
• 行数：表示返回的记录条数，括号内的数字为累计条数。

有关如何使用mysqldumpslow的更多信息，可以查看官方文档或执行mysqldumpslow --help寻求帮助。 ？

其中，

• Id：线程的唯一标识符。您可以使用 id 来杀死指定的线程。
• 用户：发起此话题的用户。普通帐户只能看到自己的线程。
• 主机：哪个IP和端口开始连接。
• db： 由线程驱动的数据库。
• 命令：线程的命令。
• 时间：操作持续时间，以秒为单位。
• 状态：线程的状态。
• 信息：SQL 语句的前 100 个字符。 ？全局

两个范围，一般使用+通配符进行过滤。

-- 查看select的次数mysql> SHOW GLOBAL STATUS LIKE 'com_select';+---------------+--------+| Variable_name | Value  |+---------------+--------+| Com_select    | 168241 |+---------------+--------+1 row in set (0.05 sec)

4。查看存储引擎运行信息 SHOW ENGINE 用于显示存储引擎当前运行信息，包括事务持有的表锁和行锁信息；事务锁定挂起状态；线程信号量等待；文件IO请求；缓冲池统计信息和其他数据。例如：

SHOW ENGINE INNODB STATUS;

上面的语句可以显示innodb存储引擎当前运行的各种信息。可以根据这个找到目前MySQL存在的问题。由于篇幅限制，这里我就不解释信息的含义了。只要你知道MySQL提供了这个，一个监控工具就可以了，你可以在需要的时候使用它。 5。 EXECUTION PLAN EXPLANATION 通过慢查询日志，我们可以知道哪些SQL语句执行得很慢，但是为什么慢呢？慢在哪里？ MySQL提供了执行计划查询命令EXPLAIN。通过这个命令我们可以看到SQL的执行计划。所谓执行计划就是：优化器会优化我们写的SQL语句（比如改变外连接）吗？内连接查询，子查询针对连接查询进行了优化...），优化器估计执行这条SQL的哪些索引的成本，最终决定使用哪个索引（或者最终选择不使用索引，而是全表扫描）、优化器对单表执行的策略是什么等等。

EXPLAIN 在 MySQL 5.6.3 之后也可以解析 UPDATE、DELETE 和 INSERT 语句，但通常我们还是用它来进行 SELECT 查询。

本文主要从几个宏观角度介绍MySQL的优化策略，所以EXPLAIN的细节这里就不详细解释了。 6。 SQL和索引优化1）SQL优化SQL优化是指SQL语法本身没有问题，但是有更好的写法来达到同样的目的。例如：

• 用小表跑大表；使用join重写子查询；或转入工会；
• 在连接查询中，尽量减少驱动表的扇出（条目数），访问驱动表的成本要尽可能低，尽量创建索引在驱动表的join列上，减少访问成本；动力表中的连接列最好是主键或表中唯一的二级索引列，这样动力表的成本可以降低到较低水平。低的;
• 对于大偏移量的限制，先过滤再排序。

最后我们举一个简单的例子。下面两条语句可以达到同样的目的，但是第二条语句的执行效率比第一条语句高很多（存储引擎使用InnoDB）。你怎么认为？我们看一下：

-- 1. 大偏移量的查询mysql> SELECT * FROM user_innodb LIMIT 9000000,10;Empty set (8.18 sec)
-- 2.先过滤ID（因为ID使用的是索引），再limitmysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;Empty set (0.02 sec)

2）索引优化

为慢查询创建合适的索引是一个很常见也很有效的方法，但是索引是否会被有效利用则是另一回事了。4。轴承电机及工作台结构1．选择存储引擎 一般情况下，我们会选择MySQL的标准存储引擎InnoDB，但随着数据库性能要求的不断提高，存储引擎的选择也成为一个重要因素。建议根据不同的公司选择不同的存储引擎。例如：

• 对于查询、插入操作较多的业务表，推荐使用MyISAM；
• 推荐用于临时表的内存；
• 高并发、更新频繁的商机推荐使用InnoDB；
• 如果您不知道该选择什么，那就选择默认即可。

2。优化字段字段优化的最后一个原则是：使用能够正确存储数据的最小数据类型。 1) 整数类型

MySQL 提供了 6 种整数类型，分别是：

• tinyint
• smallint
• mediumint
• large 不同存储类型的最大存储区域为不同，占用的存储空间自然不同。

  比如识别是否删除，建议使用tinyint，而不是bigint。

  2) 字符类型

  您是否将所有字符串字段直接设置为varchar 格式？虽然这还不够，但是你会直接将其设置为varchar(1024)的长度吗？

  如果不确定字段的长度，选择varchar，但是varchar需要额外的空间来记录字段当前占用的长度；因此，如果字段长度固定的话，尽量使用char，这样会省下不少钱。更少的内存空间。

  3）非空

  非空字段应尽可能设置为NOT NULL，并指定默认值或使用特殊值代替NULL。

  因为存储和优化NULL类型会产生性能不佳的问题，具体原因这里不讨论。

  4）不要使用外键、触发器和查看功能

  这也是《阿里巴巴开发手册》中提到的一个原则。原因有三个：

  • 降低了可读性，在检查代码的同时需要检查数据库的代码；
  • 把计算工作交给程序，数据库只做存储工作，而且做得很好。 ;
  • 数据完整性的验证应该由开发人员完成，而不是依赖于外键。一旦使用了外键，你会发现测试时删除垃圾数据变得极其困难。

  5）图像、音频、视频存储

  不要直接保存大文件，而是保存大文件的访问地址。

  6）大字段分区和数据冗余

  大字段分区其实就是前面提到的垂直表分区，将不常见的字段或者数据量较大的字段进行划分，避免列太多、数据量太大。数据，特别是如果你习惯了写SELECT *，列太多、数据量大带来的问题会被严重放大！

  字段冗余原则上不符合数据库设计范式，但非常有利于快速检索。例如，当合同表中存储客户ID时，可以冗余存储客户姓名，这样查询时就不需要根据客户ID获取用户名。因此，为业务逻辑创建一定程度的冗余也是一种更好的优化技术。 5。业务优化 严格来说，业务优化不再是MySQL调优的手段，但业务优化可以非常有效地降低数据库访问的压力。这方面的一个典型例子就是淘宝。下面举几个简单的例子，给大家一些思路：

  • 此前，购买模式是从双11当晚开始的，近年来，双11的预售战线越来越长，开始过半提前一个月。 ，各种存款红包模式层出不穷。这种方法称为售前重定向。这样可以重定向客户的服务请求，不用等到双十一凌晨才集体下单；
  • 双十一凌晨，您可能想查询当天以外的订单，但查询失败；就连支付宝口粮里的小鸡也被推迟了。这是一种降级策略，将计算资源集中用于非必要的服务，以保障当前的核心业务；
  • 双十一期间，支付宝强烈建议使用花呗付款。而不是用银行卡支付，虽然一部分考虑是为了提高软件的粘性，但另一方面，余额宝实际使用的阿里巴巴内部服务器访问速度快，而使用银行卡则需要调用银行的接口，相比之下速度较慢。很多。