本文完整阅读约需 125 分钟，如时间较长请考虑收藏后慢慢阅读~

MySQL数据库的易用性和弹性使其在各行业都得到了广泛应用，大部分知名网站（如淘宝、Facebook、哔哩哔哩等）发展早期都使用过MySQL作为其数据库。相信大部分读者都使用过MySQL数据库，如果提到MySQL数据库的优化，可能我们脑中都会不约而同的想到一些『最佳实践』。但是这些最佳实践真的有效吗？换而言之，它们真的适用于所有规模的数据库吗？本文将为读者介绍一个可以对MySQL进行精准性能优化的实用工具MySQLTuner，并详细讲解其背后的工作机制。

本文内容较多，全文阅读约需30分钟，感兴趣的读者可以先收藏再慢慢阅读。

0x01 优化对象

考虑到具体的例子有助于帮助读者进一步理解本文的优化流程，这里我选择了一台数据库体积400GB，QPS高达300，包含4200多张表，且开启了主从同步的数据库服务器作为优化目标。

服务器基本参数如下：

  • 平台：VMware vSphere ESXi 6.7 Enterprise
  • 处理器：Intel(R) Xeon(R) Gold 6130 CPU @ 2.10GHz （32虚拟核心）
  • 内存：32GB（虚拟）
  • 磁盘：1.45TB企业级SSD（PCIE直通）
  • 操作系统：CentOS 7.6.1810
  • 数据库：Oracle MySQL 5.6.47

需要注意的是，服务器没有进行任何性能调优配置，即my.cnf只包含基础配置。

0x02 情况概述

为什么这台服务器需要进行性能优化呢？因为它在业务运行过程中经常会遇到如下图所示的情况：

图1. 服务器资源占用极度异常，多次触发报警，截图来自内部服务器监控平台

从图1中可以明显看出：CPU占用率极高，而内存却只使用了2GB左右。

最初负责这台服务器的维护人员认为是IO瓶颈的问题，于是登录服务器，执行iotop，却发现服务器的IO负载并不大，遂将该情况报告给我。

我在接手该服务器后，通过以上观察的情况基本排除了服务器硬件瓶颈，因此将重心放在了对MySQL的性能优化上。

0x03 性能优化

前言部分我提到了互联网上广为流传的『最佳实践』存在诸多问题，但在这里我们先不着急分析这些问题。

我们首先来使用规范的方法来对数据库进行性能评估和调优，等到优化完成后再使用已经优化完成的参数来对比互联网上各种各样的『最佳实践』，看看他们是否合理。这样子既能让读者们了解规范的性能调优策略，也能通过对比的方式来分析这些广为流传的资料究竟存在哪些问题。

Step1. 安装和运行MySQLTuner

首先我们来安装MySQLTuner。MySQLTuner是一款用于分析MySQL配置和性能基准的Perl软件，因其简单和全面的优势，受到了很多数据库管理员的欢迎。该脚本在GitHub开源，地址为major/MySQLTuner-perl。

需要注意的是该脚本目前对Windows支持不佳，但考虑到一般很少有高负载MySQL服务器运行在Windows下，因此这一小瑕疵可以基本忽略，就算有，使用WSL或Cygwin/MinGW也是可以解决的。

安装该脚本的方式非常简单：

wget http://mysqltuner.pl/ -O mysqltuner.pl

这样，MySQLTuner就被下载到了本地，只有一个Perl文件，非常小巧。

运行这个脚本的方式同样非常简单，大部分UNIX/Linux发行版自带Perl，因此只要按如下方式执行即可：

perl ./mysqltuner.pl --port [port] --host [host] --user [user] --pass [pass]

四个参数从左到右分别为端口、主机、用户名、密码。其他参数可以通过--help参数查阅，以开启导出到JSON/HTML、控制输出内容等选项。

需要注意的是对于--user参数，最好使用MySQL的root用户（或其他拥有最高权限的用户）。此外在生产环境下如果担心密码泄露，可以省略--pass参数，然后在接下来的提示符中手动输入。

连接成功后，MySQLTuner将会输出对于数据库的分析报表，如下所示（敏感内容使用〇填充）：

 >>  MySQLTuner 1.7.19 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[--] Performing tests on 〇〇〇〇
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.6.47-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /home/log/mysql/mysqld-error.log exists
[--] Log file: /home/log/mysql/mysqld-error.log(28M)
[OK] Log file /home/log/mysql/mysqld-error.log is readable.
[OK] Log file /home/log/mysql/mysqld-error.log is not empty
[OK] Log file /home/log/mysql/mysqld-error.log is smaller than 32 Mb
[!!] /home/log/mysql/mysqld-error.log contains 2968 warning(s).
[!!] /home/log/mysql/mysqld-error.log contains 283595 error(s).
[--] 33 start(s) detected in /home/log/mysql/mysqld-error.log
[--] 1) 2020-03-15 12:39:31 29398 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2020-03-12 15:51:33 30993 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2020-03-12 15:20:43 28081 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2020-03-11 21:40:05 32456 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2020-03-11 21:36:57 31633 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2020-03-11 21:33:40 30764 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2020-03-10 14:08:52 31573 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2020-03-10 10:59:18 24378 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2020-03-10 10:43:57 23062 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2020-03-10 10:39:07 22166 [Note] /usr/sbin/mysqld: ready for connections.
[--] 58 shutdown(s) detected in /home/log/mysql/mysqld-error.log
[--] 1) 2020-03-15 12:39:26 30993 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2020-03-12 15:51:30 28081 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2020-03-12 15:20:41 32456 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2020-03-11 21:40:03 31633 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2020-03-11 21:36:55 30764 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2020-03-11 21:33:36 31573 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2020-03-10 14:08:48 24378 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2020-03-10 10:59:15 23062 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2020-03-10 10:43:55 22166 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2020-03-10 10:39:01 27621 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 408.6G (Tables: 3695)
[--] Data in InnoDB tables: 2.7G (Tables: 411)
[--] Data in CSV tables: 0B (Tables: 3)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User '〇@%' does not specify hostname restrictions.
[!!] User '〇@%' does not specify hostname restrictions.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 47m 27s (859K q [301.837 qps], 58K conn, TX: 21G, RX: 360M)
[--] Reads / Writes: 70% / 30%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 1.3G
[--] Other process memory: 0B
[--] Total buffers: 169.0M global + 5.1M per thread (151 max threads)
[--] P_S Max memory usage: 403M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 854.8M (2.67% of installed RAM)
[OK] Maximum possible memory usage: 1.3G (4.21% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/859K)
[OK] Highest usage of available connections: 36% (55/151)
[OK] Aborted connections: 0.00%  (1/58224)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled byes: 1% (1K temp sorts / 131K sorts)
[!!] Joins performed without indexes: 546
[!!] Temporary tables created on disk: 73% (30K on disk / 41K total)
[OK] Thread cache hit rate: 96% (1K created / 58K connections)
[!!] Table cache hit rate: 9% (2K open / 22K opened)
[!!] table_definition_cache(1400) is lower than number of tables(4249)
[OK] Open file limit used: 13% (2K/20K)
[OK] Table locks acquired immediately: 86% (849K immediate / 989K locks)
[OK] Binlog cache memory access: 100.00% (178977 Memory / 178977 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 403.9M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[!!] Key buffer size / total MyISAM indexes: 8.0M/7G
[!!] Read Key buffer hit rate: 90.7% (233M cached / 21M reads)
[!!] Write Key buffer hit rate: 48.9% (62K cached / 30K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/2.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 97.87% (1869163854 hits/ 1909912511 total)
[OK] InnoDB Write log efficiency: 97.49% (186147 hits/ 190930 total)
[OK] InnoDB log waits: 0.00% (0 waits / 4783 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] This server is acting as master for 1 server(s).
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] No replication setup for this server or replication not started.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /home/log/mysql/mysqld-error.log file
    Control error line(s) into /home/log/mysql/mysqld-error.log file
    Restrict Host for '〇'@% to 〇@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='〇' AND host ='%'; FLUSH PRIVILEGES;
    Restrict Host for '〇'@% to 〇@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='〇' AND host ='%'; FLUSH PRIVILEGES;
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
         See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (20480) variable
    should be greater than table_open_cache (2000)
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 2000)
    table_definition_cache(1400) > 4249 or -1 (autosizing if supported)
    key_buffer_size (> 2.9G)
    innodb_buffer_pool_size (>= 2.7G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)

由于内容非常多，也非常详细，读者们可以不必要全部读完，接下来我会选取关键部分逐一讲解。

提示：报表项前面的[--]表示没有对应的优化建议，[OK]表示当前状态基本合格，[!!]表示有当前状态存在问题，需要考虑进行优化。

Step2. 报表解析(1)：性能指标

该部分将要讲解的是报表中的Performance Metrics和Storage Engine Statistics部分。

1. 基本信息

基本信息包含以下几项：

[--] Data in MyISAM tables: 408.6G (Tables: 3695)
[--] Data in InnoDB tables: 2.7G (Tables: 411)
[--] Up for: 47m 27s (859K q [301.837 qps], 58K conn, TX: 21G, RX: 360M)
[--] Reads / Writes: 70% / 30%
[--] Binary logging is enabled (GTID MODE: OFF)
[OK] Open file limit used: 13% (2K/20K)

这些信息是为了帮助了解服务器的基本信息（例如服务器的QPS、流量、读写比率等）。

以该服务器为例，我们可以看到在不到一小时的启动时间内，发送出去的数据量就已经高达21GB，连接数达到了58000，且平均QPS在300左右，这说明了这台服务器的负载不小，且对网络传输要求高。后续对流量进行分析发现后端很少在查询中使用LIMIT语句，也没有对数据进行任何缓存，因此流量较大。

我们还可以发现该服务器的写操作相当频繁，占据了所有查询的30%；此外MyISAM表数量远大于InnoDB表，这在写操作较为频繁的场景会对性能造成影响（MyISAM写入的时候会锁住整张表，这个我们稍后会展开讲）。

除此之外，我们还可以看到当前打开文件数和当前允许的最大打开文件数。CentOS中Service的默认最大文件打开数为1024，这里将其提前提升到了20480，具体可以参考这篇文章。如果保留默认，无疑会导致服务器无法打开所需数据文件，进一步恶化服务器性能表现，甚至导致数据库崩溃。这一点是之前的DBA已经设置好的，无需我们进行修改。

这些基本信息对于后续的具体优化帮助不大，但是可以让我们迅速了解服务器状态，以明确接下来优化的方向。

2. 内存信息

内存信息包含了当前内存占用和预计内存占用等信息，如下所示：

[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 1.3G
[--] Other process memory: 0B
[--] Total buffers: 169.0M global + 5.1M per thread (151 max threads)
[OK] Maximum reached memory usage: 854.8M (2.67% of installed RAM)
[OK] Maximum possible memory usage: 1.3G (4.21% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available

其中最大内存的计算公式为全局内存占用+单一线程内存占用*最大线程数，全局和单一线程内存占用计算方式较为复杂，这里我给出对应的源码以作参考：

# Server-wide memory
$mycalc{'max_tmp_table_size'} =
  ( $myvar{'tmp_table_size'} > $myvar{'max_heap_table_size'} )
  ? $myvar{'max_heap_table_size'}
  : $myvar{'tmp_table_size'};
$mycalc{'server_buffers'} =
  $myvar{'key_buffer_size'} + $mycalc{'max_tmp_table_size'};
$mycalc{'server_buffers'} +=
  ( defined $myvar{'innodb_buffer_pool_size'} )
  ? $myvar{'innodb_buffer_pool_size'}
  : 0;
$mycalc{'server_buffers'} +=
  ( defined $myvar{'innodb_additional_mem_pool_size'} )
  ? $myvar{'innodb_additional_mem_pool_size'}
  : 0;
$mycalc{'server_buffers'} +=
  ( defined $myvar{'innodb_log_buffer_size'} )
  ? $myvar{'innodb_log_buffer_size'}
  : 0;
$mycalc{'server_buffers'} +=
  ( defined $myvar{'query_cache_size'} ) ? $myvar{'query_cache_size'} : 0;
$mycalc{'server_buffers'} +=
  ( defined $myvar{'aria_pagecache_buffer_size'} )
  ? $myvar{'aria_pagecache_buffer_size'}
  : 0;

# Per-thread memory
if ( mysql_version_ge(4) ) {
    $mycalc{'per_thread_buffers'} =
      $myvar{'read_buffer_size'} +
      $myvar{'read_rnd_buffer_size'} +
      $myvar{'sort_buffer_size'} +
      $myvar{'thread_stack'} +
      $myvar{'max_allowed_packet'} +
      $myvar{'join_buffer_size'};
}
else {
    $mycalc{'per_thread_buffers'} =
      $myvar{'record_buffer'} +
      $myvar{'record_rnd_buffer'} +
      $myvar{'sort_buffer'} +
      $myvar{'thread_stack'} +
      $myvar{'join_buffer_size'};
}
$mycalc{'total_per_thread_buffers'} =
  $mycalc{'per_thread_buffers'} * $myvar{'max_connections'};
$mycalc{'max_total_per_thread_buffers'} =
  $mycalc{'per_thread_buffers'} * $mystat{'Max_used_connections'};

$myval中存储的是当前的配置信息，每一项配置都可以在MySQL的官方文档查询到。篇幅所限，不再展开，感兴趣的读者可以逐一查询配置含义。后文我也会单独介绍如何阅读MySQLTuner的源码。

通过上面的结果我们可以看出，当前的MySQL配置就算是满载也只能使用到1.3GB的内存。这对于一台有32GB内存的服务器来说，很明显是一种浪费。

因此我们后面的优化目标之一就是着重提升内存使用率，使其在安全范围内内达到最高以充分利用服务器资源。

3. 查询信息

[OK] Slow queries: 0% (0/859K)
[OK] Highest usage of available connections: 36% (55/151)
[OK] Aborted connections: 0.00%  (1/58224)
[OK] Sorts requiring temporary tables: 1% (1K temp sorts / 131K sorts)
[!!] Joins performed without indexes: 546
[!!] Table locks acquired immediately: 86% (849K immediate / 989K locks)

这部分主要是查询和连接相关的统计信息。

需要注意的是，提示慢查询为0%并非意味着没有慢查询，只是因为我们没有进行相关的配置，导致服务器不记录慢查询。此外我们可以看到尽管QPS较高，但连接数并不高，也几乎没有中断的连接。

但需要留意的是：这里我们看到了一个警告信息，告诉我们存在没有使用索引的JOIN语句。由于不清楚后端具体实现，且本篇文章以性能调优为主，这次我们不主动建立索引（对业务不了解，不知道建立哪些表、哪些字段的索引），但如果读者遇到类似的警告信息，务必要对业务进行优化，尽可能避免这一情况。

除此之外，还有一个警告信息提示我们：只有86%的请求没有被阻塞。上文我提到过，MyISAM表数量过多，在写入较多的情况下会导致整张表被锁死，严重影响性能，而这里就是相关的证据。

为了证实MyISAM对性能的影响，我在服务器又一次飙升到100%CPU占用率时执行了show processlist查询。图2中可以看到大量的查询被Waiting for table level lock阻塞，而这也是我们后续优化的目标之一。

图2. 在CPU占用率爆满的时候，有500多个请求被MyISAM的表锁阻塞

4. 缓存信息

[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 495K selects)
[OK] Query cache prunes per day: 0
[OK] Thread cache hit rate: 96% (1K created / 58K connections)
[!!] Table cache hit rate: 9% (2K open / 22K opened)
[!!] table_definition_cache(1400) is lower than number of tables(4249)
[OK] Binlog cache memory access: 100.00% (178977 Memory / 178977 Total)

缓存信息中包含了非常多的警告，也说明了我们上面提到的内存利用率不足问题的确对性能造成了影响（大部分缓存都是存储在内存中的）。

其中需要特别注意的是查询缓存，根据官方文档，查询缓存将会在查询执行后被写入，在下一次修改表中数据时被删除。那么为什么这里还提示应该禁用呢？这就要涉及到MySQL查询缓存的工作原理了，这部分我们会在文章最后进行展开解析。

此外还需要注意的是Table cache和table_definition_cache，这两个参数分别决定了打开表的缓存和表结构的缓存。前者占比应该越高越好，而后者的数值最好大于总的表数量。很显然这台服务器在默认配置下，两者都未能满足。

5. 配置信息

[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance

关于配置信息的报表中只有一条需要留意，即关闭反向DNS查询功能。

非常可惜的是很多文章只提到了『应该关闭这一选项』，却没有讲清楚这一功能为什么存在。这里我稍微解释一下：

根据MySQL文档，反向DNS查询的主要目的是为了了解连接到服务器的主机来自哪个单位（即IP地址属于哪个域名），这是因为TCP/IP的数据包中只包含IP地址而没有域名信息，因此需要反向查询来帮助了解『对方是谁』。

实际上如果数据库和客户端都在本地（127.0.0.1），反向DNS查询是不会被启用的，只有通过远程连接MySQL的方式（例如DBA远程维护数据库）才会进行反向DNS查询。

尽管反向DNS存在缓存机制（同样参考上面的MySQL文档），考虑到IPv4环境下，用户IP地址通常是动态变化的，因此很难从IP反推到域名，也就造成了缓存几乎永远不会命中。

这就是为什么大部分时候我们应该关闭此选项，以避免连接的时候等待查询，造成延迟大幅提高。

Step3. 报表解析(2)：存储引擎指标

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[!!] Key buffer size / total MyISAM indexes: 8.0M/7G
[!!] Read Key buffer hit rate: 90.7% (233M cached / 21M reads)
[!!] Write Key buffer hit rate: 48.9% (62K cached / 30K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/2.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 97.87% (1869163854 hits/ 1909912511 total)
[OK] InnoDB Write log efficiency: 97.49% (186147 hits/ 190930 total)
[OK] InnoDB log waits: 0.00% (0 waits / 4783 writes)

存储引擎指标根据服务器所使用的存储引擎决定，因为这台服务器只使用了MyISAM和InnoDB，因此我只摘录这两个存储引擎的报表。

1. MyISAM引擎指标

MyISAM引擎的性能指标并不多，但就在这四个指标中居然出现了三个警告：

  • 第一个警告：索引缓存大小/总MyISAM索引大小的比率过低，默认的索引缓存大小居然只有8MB，而数据库中所有MyISAM索引体积高达7GB！
  • 第二个警告：从缓存读取索引次数/从磁盘读取索引次数比率太小。
  • 第三个警告：写入索引到缓存次数/写入索引到磁盘次数太小。

其中第二个和第三个警告可能理解起来比较绕，这是因为MySQL对于读取/写入索引的指标是将缓存读写和磁盘读写分开计算，没有合并在一起。因此这两个警告中的百分比是缓存相对于所有读写的百分比；而后面的数字则是缓存/磁盘读写分开列举。

2. InnoDB引擎指标

InnoDB引擎相对来说会复杂一些，对于缓存的需求也更大。在InnoDB引擎指标的信息中同样也有三个警告：

  • 第一个警告：InnoDB数据缓存池/InnoDB数据总体积的比率过低，缓存池大小在内存允许的情况下最好大于总数据大小。
  • 第二个警告：InnoDB日志文件大小/InnoDB数据缓冲池大小过高，应该在20%到30%之间，25%最佳。

InnoDB日志文件是InnoDB实现事务回滚和崩溃恢复的核心，因此如果日志文件较小，则会存在频繁写回数据库的情况，影响IO；如果日志文件过大，则会在遭遇崩溃后等待很长时间才能恢复状态。需要注意的是，第二个警告中的InnoDB数据缓冲池并非指innodb_buffer_pool_size这个变量，而是还要除以innodb_log_files_in_group。这是因为日志文件可以存储在多个文件中，实际的日志文件大小是innodb_log_file_size * innodb_log_files_in_group。

  • 第三个警告：InnoDB数据缓冲池太小，且缓冲池实例不为1，这个是和第一个警告类似的问题。需要额外注意的是，在缓冲池太小的情况下，缓冲池实例应该为1即可。

这里稍微解释一下原因：缓冲池实例数量越多，越能解决缓冲池的竞争问题（因为缓冲池只允许被一个查询所访问），但是通常情况下只有在总缓冲池体积大于1G的情况下菜有必要引入多个缓冲池。该建议来自于MySQL官方，具体信息可以参考MySQL的这篇文档。

Step4. 报表解析(3)：优化建议和推荐配置

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /home/log/mysql/mysqld-error.log file
    Control error line(s) into /home/log/mysql/mysqld-error.log file
    Restrict Host for '〇'@% to 〇@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='〇' AND host ='%'; FLUSH PRIVILEGES;
    Restrict Host for '〇'@% to 〇@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='〇' AND host ='%'; FLUSH PRIVILEGES;
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
         See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (20480) variable
    should be greater than table_open_cache (2000)
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 2000)
    table_definition_cache(1400) > 4249 or -1 (autosizing if supported)
    key_buffer_size (> 2.9G)
    innodb_buffer_pool_size (>= 2.7G) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances (=1)

这部分就是对于上面所有报表情况的一个总结。优化建议在上半部分，推荐配置在下半部分。其中的推荐配置不仅提供了需要调优的变量名称，还根据当前的情况给予了推荐的参数值供我们进行接下来的性能优化。

Step5. 其他杂项

实际上MySQLTuner除了可以进行性能检查以外，还可以检查安全性问题。在报表的其他部分，我们可以看到如下信息：

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User '〇@%' does not specify hostname restrictions.
[!!] User '〇@%' does not specify hostname restrictions.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

可以看到MySQLTuner提供了一些安全建议，由于本文主要讲解的是MySQL的性能优化，因此不再展开说明，读者可以自行测试。此外如果按照官方文档引入CVE漏洞索引文件，还可以对MySQL服务器的漏洞进行检查。

另外，MySQLTuner还会给出一些其他信息如日志状态、冗余状态等，篇幅所限不再赘述。如果读者对这些安全检查的原理感兴趣，请继续阅读本文，文章的最后会详细讲解如何分析MySQLTuner的源码，帮助进一步了解。

0x04 精准优化

根据上文的分析，我们总结出了以下几点优化建议：

  1. 使用MyISAM存储引擎的表过多，可以考虑将部分写入较多的表转换为InnoDB
  2. 有频繁的JOIN、GROUP BY和ORDER BY操作，造成了大量临时表的建立，很大一部分临时表直接建立在了磁盘中，因此需要增大排序缓存和JOIN缓存，还需要增大临时表缓存
  3. MyISAM索引缓存太小，造成了索引读取/更新时频繁的磁盘读写
  4. InnoDB数据缓冲池配置不合理，没有发挥InnoDB的性能
  5. 无论是打开表缓存还是表结构缓存都太小，导致了打开表时需要读取磁盘
  6. 需要关闭查询缓存以进一步优化性能

其实大家只要亲自读一读MySQLTuner的报表，便能心里有数，迅速了解优化的方向和具体措施。那么接下来我们要做的就是根据实际需求，参考MySQLTuner的建议来优化/etc/my.cnf配置。

0x05 最终配置展示

最终配置如下所示：

# 慢查询时间阈值
# 单次查询超过该阈值则被视为慢查询
# 慢查询会被记录进慢查询日志以供性能分析
long_query_time = 1

# 慢查询日志位置
slow_query_log = ON
slow_query_log_file = /home/log/mysql/slow.log

# key_buffer_size指定用于索引的缓冲区大小
key_buffer_size = 8192M

# 允许最大插入（更新）大小
max_allowed_packet = 16M

# 进行查询内排序时的缓冲区大小
# 每个连接独享
sort_buffer_size = 512M
myisam_sort_buffer_size = 512M

# InnoDB缓冲池大小，建议设置大一些
innodb_buffer_pool_size = 8G
innodb_log_file_size = 1G
innodb_buffer_pool_instances = 8

# 执行查询时的缓冲区大小
# 每个连接独享
read_buffer_size = 512M

# 外键查询操作的缓冲区大小
# 每个连接独享
# 外键查询操作会有大量跨表查询，因此设置相对较大
join_buffer_size = 512M

# 同时允许最大连接数
max_connections = 256

# 总查询缓存，直接关闭屌
query_cache_type=0
query_cache_size=0

# 长连接超时时间（秒）
wait_timeout = 30

# 临时表大小
# 每个连接独享
# 此处临时表指MySQL查询操作时动态建立的临时表
tmp_table_size = 512M
max_heap_table_size = 512M

# 配置打开表缓存
# 避免频繁的数据表开关降低性能
table_open_cache = 2048M
table_definition_cache = 10240

这里为了方便读者阅读，每个配置都附上了对应的解释。

比较可惜的是，这一配置依旧不够完美。当我重新运行MySQLTuner的时候，依旧发现了一些严重的问题：

[!!] Maximum reached memory usage: 70.2G (224.57% of installed RAM)
[!!] Maximum possible memory usage: 405.3G (1296.66% of installed RAM)
[!!] Joins performed without indexes: 32
[!!] Temporary tables created on disk: 59% (1K on disk / 2K total)
[!!] Write Key buffer hit rate: 28.5% (13K cached / 3K writes)
...
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 512.0M, or always use indexes with JOINs)

以上问题可以拆分为三部分：

  1. 预计最大内存占用高达405GB。这是因为该MySQL服务器使用的版本是Oracle的MySQL Community Edition，只支持one-thread-per-connection，即每个请求一个线程的模式。如果读者使用的是MySQL的其他发行版如MariaDB、Percona等则不用担心，这些发行版大多都免费带有线程池模式，以MariaDB为例，只需要将thread_handling变量设置为pool-of-threads即可开启线程池，具体可以参考这篇文档。其他发行版同理，不再赘述。线程池可以有效避免高并发情况下内存占用过高的问题，也可以更合理利用系统资源（比如Nginx就支持worker_processes和worker_cpu_affinity，通过将Worker和CPU核心直接绑定的方式来降低线程/进程启停的开销，而PHP-FPM更是得到了广泛的应用）。

  2. 写入索引缓存依旧很难被命中。由于MyISAM数据库的写入性能较差，而这个数据库接受的请求很多都是写入请求（尤其以UPDATE为主），因此只能通过将写入较多的数据库转换为InnoDB来提升写入性能。可惜和负责后端开发的程序员沟通后，了解到数据库中很多MyISAM表的自增字段不在最前面，直接转换为InnoDB会出现错误，只能作罢。具体原因可以参考这篇问答。

  3. JOIN语句中，部分字段未使用索引导致频繁的全表扫描。因为这4000多张表中很多都是使用ORM自动生成的，逐一添加索引也不太实际，同样只能作罢。

尽管上面三个问题没能得到妥善解决，但当我重启服务器并上线运行三天后，发现优化还是有效的——CPU平均占用率降低到了20%（之前是70%），内存使用率稳定在24GB左右（之前只有2GB），而根据Nginx的日志反馈，业务的平均响应时间也从3s下降到了0.8s。

当然，这样的性能依旧不够达到完美，但考虑到业务的特殊性（还在使用PHP5.2和JDK5.0的远古业务，最大的一张表记录数超过了1000万却只配置了主键，此外拥有4000多张表，大部分都还是ORM派生类自动生成的表，每张表的结构都不一样，几乎不可能手动进行优化），仅通过修改配置文件的方式，将其优化到这样的地步也不算太差。

0x06 对比『最佳实践』

读到这里，读者们应该发现了一个特点：MySQLTuner给出的很多建议都是基于比率和当前状态来计算的，而非是一个固定的值。那我们在搜索引擎搜索一下my.cnf配置，来看看这些被浏览了无数次的『最佳实践』是怎么说的呢？

图3. 搜索引擎中的结果，大多都没有提到任何计算公式、辅助工具或比率

图4. 某国内博客站点上的『推荐配置』。

从图3可以看出来搜索到的大部分文章结果都只给了配置文件，却没有告诉读者『为什么要这样配置』，甚至个别点击量上万的文章连配置项对应的中文解释都舍不得给一个。

而图4更是夸张：一篇发布于2019年的文章，却没有包含任何关于InnoDB的配置，而且20MB的key_buffer_size真的适用于所有情况吗？我觉得至少在本文的案例中是远远不够的。

可能很多读者会想：是否只有中文互联网社区是这样的呢？并不是。就算是StackOverflow等国外社区或博客，同样存在这样不分青红皂白的『优化』，其中很多甚至起到了逆优化的作用，让本来流畅的服务器因为缓存过大或过小反而变得缓慢。

图5. 就算是StackOverflow，也存在诸多这样的『最佳实践』。的确，文中的作者考虑到了服务器内存大小的因素，但仅考虑内存大小，而忽视业务性质和业务负载的『优化』真的有效吗？

0x07 为什么需要关闭查询缓存？

上文我们提到，MySQLTuner建议优化关闭查询缓存。乍一看，缓存应该是一件很好的事情，尤其是查询缓存。数据库说白了就是一个接受查询-返回数据的机器，如果连查询都能缓存岂不是性能成倍提升？

实际上MySQL的开发者们也是这么想的。在2004年的MySQL 4.0版本，MySQL的开发者们引入了名为query cache的机制，将每次查询的结果放入内存，下次查询直接从内存将数据读取出来；如果在下一次查询前表被修改，则清除之前的缓存。

图6. 2004年MySQL4.0引入查询缓存时，几乎所有资料都介绍了这一『令人激动』的功能。截图摘自图书《Web Database Applications with PHP and MySQL: Building Effective Database-Driven Web Sites》。

然而很快，用户们发现了这一功能所存在的问题：开启查询缓存后，每个查询都会先调用缓存模块寻找是否有现成的缓存。由于缓存模块需要处理竞争问题，MySQL团队引入了查询缓存的读写锁机制，这在2004年还好（大部分处理器依旧是单核心单线程，几乎所有操作都是原子的），随着多核心处理器的普及，这一『特性』却成为了降低MySQL并发性能的一大障碍。

图7. MySQL 5.6源码摘录，摘自sql/sql_cache.cc。

我们可以从图7中看出，MySQL的查询缓存模块引入了mutex的机制，只能一次处理一个查询。MySQL当初引入查询缓存的目的是为了缩短查询时间，而引入后反而因为需要等待互斥锁被解除，延长了查询时间。

这就是为什么MySQLTuner不推荐开启查询缓存的原因。而另一方面，InnoDB作为MySQL新的默认存储引擎，已经自带了内存缓存功能，而且不是缓存查询结果，而是缓存数据库，借助InnoDB的MVCC机制，从另一个角度解决了并发请求情况下的竞争问题。关于InnoDB的MVCC机制，可以在这篇文章中进行详细了解。

当然了，不推荐打开不代表不能打开。查询缓存在使用MyISAM存储引擎，且读远多于写（甚至完全不写）的情况下，依旧具有巨大的潜力，只是这一潜力随着InnoDB读取性能的不断提升，正在慢慢减小，而现代应用中，只读不写的场景也屈指可数。

MySQL8.0的发布已经有接近两年，遗憾的是国内很少有相关文章讲解为什么MySQL决定在8.0版本移除查询缓存，因此我觉得有必要专门在这部分告诉读者们这背后的历史原因，并结合源码来了解到底存在怎么样的性能问题。

如果读者希望了解更多内容，可以阅读MySQL开发团队的这篇博客：MySQL 8.0: Retiring Support for the Query Cache。

0x08 MySQLTuner如何工作？

上文我使用一台真实存在的服务器向读者展示了使用MySQLTuner优化数据库性能的流程，但限于篇幅，很多具体的配置（以及为什么要这么配置）我没能来得及讲解。

但授人以鱼不如授人以渔，因此我觉得有必要告诉读者如何根据MySQLTuner的源码来了解数据库各性能指标的最优值，让读者们就算不借助MySQLTuner也能轻松做到『千人千面』的专业数据库优化。

打开mysqltuner.pl文件，会发现这一文件有接近7000行代码，更致命的是，这是一个Perl脚本，即大家戏称『自带加密』的语言。这么多代码应该怎么读？其实也很简单。

首先需要了解的是，这个脚本中存在几个全局变量，贯穿了整个脚本的每一部分：

  • $myvar：包含了SHOW VARIABLES和SHOW GLOBAL VARIABLES的所有结果，在get_all_vars()函数中被赋值
  • $mystat：包含了SHOW STATUS和SHOW GLOBAL STATUS的所有结果，在get_all_vars()函数中被赋值
  • $mycalc：包含了所有的计算比率，在calculations()函数中被赋值

了解这三个变量对于我们的阅读有极大帮助，因为所有的判断几乎都是以这三个变量中参数的比较来实现的。具体有什么帮助呢，我们依旧来看一个例子：

[OK] Read Key buffer hit rate: 99.9% (461M cached / 474K reads)

这一条是我在优化完数据库后，使用MySQLTuner工具输出的结果之一，即MyISAM读取索引的命中率已经达到了99.9%。那么问题来了：

  1. 比率如何计算？
  2. 这个比率多大才算OK？

如果只是想了解这两点就去阅读官方文档，无疑是大海捞针（我在初学MySQL的时候经常要翻上一两个小时才能在手册中翻到想要的『最佳建议』）。但在MySQLTuner的源码中，那可太好找了：

图8. 简单的代码编辑器和Ctrl+F就可以找到真正的『最佳建议』

不用担心没有接触过Perl，因为代码中大部分都只是简单的if/else。我们只要简单的Ctrl+F，输入对应指标的标题，就可以快速找到我们想要的代码。

从代码中我们可以清晰的看出这两个问题的答案。对于第一个问题，我们只要找到$mycalc{'pct_keys_from_mem'}的定义位置即可：

if ( $mystat{'Key_read_requests'} > 0 ) {
        $mycalc{'pct_keys_from_mem'} = sprintf(
            "%.1f",
            (
                100 - (
                    ( $mystat{'Key_reads'} / $mystat{'Key_read_requests'} ) *
                      100
                )
            )
        );
    }

即比率的计算方法是100 - (Key_reads / Key_read_requests) * 100。其中的Key_reads和Key_read_requests可以通过SHOW GLOBAL STATUS LIKE "Key_read%";这样的方法简单获取，而这两个状态的含义，在MySQL官方文档也是写得一清二楚（这个还是很好找的）。

对于第二个问题，代码中的if ( $mycalc{'pct_keys_from_mem'} < 95 )也足以给出我们要的答案：小于95%属于异常情况。

相信读完这个例子，读者们应该学会了如何从MySQLTuner的源码中学习数据库优化。MySQLTuner的这些计算公式和性能基准其实都是官方文档中写明的，只是通过代码将它们进行集中，再以脚本的方式被自动化执行。

也就是说：如果读者们有时间结合输出结果，逐一阅读MySQLTuner源码，了解这些性能建议背后的机制，那么今后就算没有这个工具，光是在MySQL命令行中查询参数，大家也是可以迅速了解MySQL服务器的状态，并对其进行针对性优化的。

0x09 一点感想

实际上MySQL数据库的优化还有很多种方式：索引优化、查询优化、表优化、架构优化等，本文只是摘取了其中的一小部分，即配置优化。

MySQLTuner也并非是MySQLTuner性能评估的唯一选择，除此之外还有其他很多的同类工具，但它们背后的思想都是相同的，是根据数据库中的各项性能指标来给出优化建议。

这也是为什么我加入了『MySQLTuner如何工作』这一节，希望启发读者们主动尝试在源码中找到MySQL配置优化的所有内容，将MySQLTuner的源码作为MySQL配置优化的最佳『教材』。

我的精力有限，本文篇幅也有限，如果要将所有的配置项列举出来，一方面大家可能读起来吃力（那就不能算是文章，而是文档了），另一方面随着时间变化，我所写的内容难免会过时。但我觉得教会读者们如何分析、如何自己寻找想要的答案，这是永远不会过时的。

在写作的过程中我查阅了非常多的资料以确保我没有写出误导大家的内容，但写作过程中难免会出现遗漏，如果大家发现文章中存在错误，欢迎指正。

我实在不擅长写短篇文章，总觉得三言两语说不清楚事情，因此又不小心写了上万字。倘若有读者能愿意阅读到这里，我实在是感激不尽，请接受我提前的感谢。