MySQL中不同类型的参数与参数配置笔记整理

99%的焦虑都来自于虚度时间和没有好好做事，所以唯一的解决办法就是行动起来，认真做完事情，战胜焦虑，战胜那些心里空荡荡的时刻，而不是选择逃避。不要站在原地想象困难，行动永远是改变现状的最佳方式

写在前面

---

• 博文内容极客时间老师的专栏学习笔记整理:
• https://time.geekbang.org/column/article/802420
• 理解不足小伙伴帮忙指正 :),生活加油

99%的焦虑都来自于虚度时间和没有好好做事，所以唯一的解决办法就是行动起来，认真做完事情，战胜焦虑，战胜那些心里空荡荡的时刻，而不是选择逃避。不要站在原地想象困难，行动永远是改变现状的最佳方式

持续分享技术干货，感兴趣小伙伴可以关注下 ^_^

---

正式环境使用的 MySQL 就不能仅仅依赖基础的默认配置了，我们需要根据部署 MySQL 的主机配置、使用 MySQL 的业务场景等因素，设置合理的参数，使 MySQL 能以比较高的性能运行，并满足业务对数据一致性的要求。

MySQL 参数设置机制

MySQL 8.0 总共有六百多个配置参数。在 MySQL 中，我们使用命令 show variables查看参数的当前值。

session_variables
---
| Variable_name | Value | 
| --- | --- | 
| activate_all_roles_on_login | OFF | 
......

设置参数

有几个方法都可以用来设置参数。首先 mysqld 进程启动时，可以指定一系列的命令行参数。下面是一个比较典型的例子

$ ps -elf | grep mysqld
... /usr/local/mysql/bin/mysqld \
    --defaults-file=/data/mysql01/my.cnf \
    --basedir=/usr/local/mysql \
    --datadir=/data/mysql01/data \
    --plugin-dir=/usr/local/mysql/lib/plugin \
    --user=mysql \
    --log-error=/data/mysql01/log/alert.log \
    --open-files-limit=1024 \
    --pid-file=/data/mysql01/run/mysqld.pid \
    --socket=/data/mysql01/run/mysql.sock

把参数写到配置文件中。MySQL 默认会从一些固定位置读取配置。执行 mysqld --verbose --help 命令可以观察到 mysqld 读取默认参数文件的路径。

/opt/mysql# ./bin/mysqld --verbose --help | head -30
......

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysql_cluster mysqld server mysqld-8.0

mysqld 默认会从下面多个文件依次读取配置

• /etc/my.cnf、
• /etc/mysql/my.cnf、
• /usr/local/mysql/etc/my.cnf
• ~/.my.cnf

多个文件以最后读取到的那个参数值为准,建议一个 MySQL 实例只使用一个配置文件。

mysqld --defaults-file=/data/mysql3306/my.cnf --datadir=......

在启动 MySQL 时，可以使用命令行参数 defaults-file 指定配置文件

指定这个参数后，MySQL 只会从这个文件中读取配置项。需要注意，defaults-file 在所有命令行参数中必须排在最前面才有效。

只读参数

MySQL 的参数中，有的是只读的，你不能修改这些参数。比如参数 lower_case_file_system

反映了数据目录所在的文件系统是否区分文件名大小写，它是由底层操作系统的特性决定的，无法通过参数来修改。一般 Linux 和 macOS 文件名区分大小写，Windows 不区分文件名大小写。

启动参数

MySQL 只有在启动时才会读取这些参数。比如参数 port 指定了数据库的监听端口，要修改这个参数，只能重启数据库。

动态参数

还有很多参数可以动态修改，你可以通过 SET 命令修改这些参数

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.01 sec)

通过 SET 命令设置的参数，只对当前运行中的实例生效，实例重启后，这些设置就失效了。MySQL 8.0 开始支持参数修改持久化，通过 SET PERSIST命令来设置。

set persist 命令不仅修改了参数的当前值，还会将参数的设置保存在数据目录（datadir）下的 mysqld-auto.cnf 文件中。MySQL 重启时，会加载 mysqld-auto.cnf 文件中保存的参数。

mysql> reset persist slow_query_log;
Query OK, 0 rows affected (0.00 sec)

可以通过 reset persist命令将参数从mysqld-auto.cnf中移除。reset persist不会修改变量的当前值。

按参数的作用范围来看，MySQL 的参数分为全局参数和会话参数。

全局参数

全局参数对整个实例生效，需要用 SET GLOBAL 命令设置，使用 SET 命令会报错。

mysql> set innodb_flush_log_at_timeout=1;
ERROR 1229 (HY000): Variable 'innodb_flush_log_at_timeout' is a GLOBAL variable and should be set with SET GLOBAL

mysql> set global innodb_flush_log_at_timeout=1;
Query OK, 0 rows affected (0.00 sec)

会话参数

会话参数只对某一个会话生效，使用 SET 命令设置，不能加 GLOBAL 关键字

mysql> set global timestamp=0;
ERROR 1228 (HY000): Variable 'timestamp' is a SESSION variable and can't be used with SET GLOBAL

mysql> set timestamp=0;
Query OK, 0 rows affected (0.00 sec)

MySQL 中很多会话参数同时拥有同名的全局参数。使用 SET GLOBAL 命令时，设置的是全局变量的值，不影响现有会话的 Session 值，包括执行 SET GLOBAL 命令的那个会话。会话真正使用的是 Session 变量的值，会话创建时，会话变量会默认设置成全局变量的值，也可以使用 SET 命令修改会话变量。

## 查看参数当前值
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.00 sec)

## 设置全局值
mysql> set global long_query_time=10;
Query OK, 0 rows affected (0.00 sec)

## 当前值没有变化
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.00 sec)

## 全局值被修改了
mysql> show global variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

percona 的 MySQL 分支对参数 long_query_time 做了特殊处理，修改全局值时，也会修改当前已经存在的那些会话的参数值，这样更符合我们在这个场景下的需求。如果想了解某个参数的具体含义，我的建议是查阅官方文档

查看参数

一般我们使用 show variables 和 show global variables 命令查看参数的当前值。show global variables 显示全局参数的配置值，show variables 命令显示当前会话的所有参数值。

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

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

在以前的 MySQL 版本中，如果想查看别的会话的某个参数值，没有很简便的方法。8.0 中，可以到 performance_schema.variables_by_thread 表查看其他会话的变量值。

mysql>  select * from performance_schema.variables_by_thread 
    where variable_name = 'wait_timeout';
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
|        64 | wait_timeout  | 28800          |
|        65 | wait_timeout  | 3600           |
+-----------+---------------+----------------+

这个表的 THREAD_ID 是线程 ID，不是SHOW PROCESSLIST命令输出中的 ID。PROCESSLIT_ID 和 THREAD_ID 的关系可以到 performance_schema.threads 表查找。

参数文件的格式

MySQL 参数文件一般命名为 my.cnf，当然你也可以使用不同的文件名，但需要在启动 MySQL 时，通过 defaults-file 参数指定配置文件的路径。

参数文件分为多个组，组名用中括号[]括起来。一般将 MySQL 服务端的参数放到[mysqld]中。

[mysqld_safe]
pid-file=/data/mysql01/run/mysqld.pid


[mysqld]

basedir=/opt/mysql
lc_messages_dir=/opt/mysql/share
datadir=/data/mysql01/data
tmpdir=/data/mysql01/tmp
log-error=/data/mysql01/log/alert.log
slow_query_log_file=/data/mysql01/log/slow.log
general_log_file=/data/mysql01/log/general.log

socket=/data/mysql01/run/mysql.sock
skip_name_resolve

...

参数设置的一般格式:

• variable_name=variable_value
• skip_name_resolve 也有的参数值需要提供参数名，不需要提供参数值

设置合理的参数

设置文件路径

MySQL 有一系列与文件路径相关的参数，用来指定程序文件的路径和其他文件的存放路径

basedir

basedir 指定 MySQL 程序的安装路径，如果 MySQL 程序没有安装在默认的路径，需要指定 basedir。datadir 指定数据文件的存放路径，默认路径是在构建 MySQL 二进制时确定的。我建议在配置文件中显式指定 datadir。如果因为各种原因需要将数据目录移动到其他路径下，要同步修改 datadir 参数，否则数据库会无法启动。

datadir 同时也决定了其他很多文件的存放路径，如 innodb 系统表空间、innodb REDO 日志、binlog、relaylog 默认都存放在 datadir 下。当然，我们可以通过一些参数修改这些文件的存放路径。下面这个表格总结了平时比较常用的文件路径相关的参数。

日志相关参数

设置日志文件MySQL 的日志文件包括错误日志、慢 SQL 日志、General Log。建议开启慢 SQL 日志，这对 SQL 优化有比较重要的作用。general log 一般不开启，只有在一些排查问题的场景下短暂开启。

-- error
log_error=/data/mysql01/log/error.log
slow_query_log_file=/data/mysql01/log/slow.log
general_log_file=/data/mysql01/log/general.log

-- slow log，建议开启慢SQL日志
slow_query_log=ON
long_query_time=1 ## (单位秒，可以精确到1微秒)
log_queries_not_using_indexes=1
log_slow_admin_statements=1

设置资源限制参数

open_files_limit

参数 open_files_limit 限制了 MySQL 进程允许同时打开的文件句柄数。如果 MySQL 进程打开的文件句柄数达到 open_files_limit，将无法打开新的文件，就会导致数据库访问异常。以下这些操作都需要占用文件句柄：

• 访问 innodb 数据文件
• 访问临时文件
• 建立 TCP 连接
• 访问其他文件（如日志文件）

open_files_limit 最终的取值受几个因素影响。

1. open_files_limit 受参数 max_connections 和 table_open_cache 的影响。MySQL 会将 open_files_limit 调整为以下几项的最大值：

• open_files_limit 参数的设置值
• max_connections + 2 * table_open_cache + 10
• max_connections * 5

2. open_files_limit 受操作系统 open files 限制。

启动 mysqld 进程时，如果父进程在操作系统中有 open files（ulimit -n）限制，那么 open_files_limit 不能超过父进程的 open files 限制。 下面这个例子中，我们先在操作系统中 su 到 MySQL，ulimit -n 设置为 1024，然后再启动 MySQL 数据库。

# su - mysql
$ ulimit -n 1024
$ ulimit -n
1024

$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql01/my.cnf &

在 MySQL 的错误日志中，可以看到 max_open_files 被缩小为 1024，而且参数 max_connections 和 table_open_cache 也被改小了。

[Server] Could not increase number of max_open_files to more than 1024 (request: 30000)
[Server] Changed limits: max_connections: 214 (requested 1000)
[Server] Changed limits: table_open_cache: 400 (requested 1000)

为了避免这个问题，需要在操作系统中提高用户的资源限制。

### /etc/security/limits.conf
mysql       -       nofile  1000000
mysql       -       nproc   65535

max_connections

max_connections 限制了数据库的最大连接数。需要注意的是 max_connections 受参数 max_open_files 影响，它不能超过 max_open_files - 810，

因此在上面的例子中，虽然 max_connection 配置为 1000，但最终生效的值为 214（1024 - 810）。MySQL 的每个连接都会占用一定的资源，max_connections 参数需要根据业务的实际连接需求以及服务器的可用资源来综合评估。

table_open_cache

table_open_cache 控制数据库中允许同时打开的表的数量，这个参数的最终取值会受参数 max_open_files 和 max_connections 实际运行值的影响。table_open_cache 上限为 (max_open_files - max_connections - 10) / 2 或 400。在我们的例子中，虽然在参数文件中 table_open_cache 设置为 1000，但实际运行时，该参数被调整为 400。当数据库中表的数量比较大时，可以适当增加 table_open_cache。

innodb_open_files

innodb_open_files 控制允许同时打开的 InnoDB 文件的数量。该参数默认取 table_open_cache 的运行值。

table_definition_cache

MySQL 将表结构定义也缓存在内存中，参数 table_definition_cache 设置了允许缓存的表结构定义的数量。如果 InnoDB 表的数量比较多，可以把这个参数也设置得大一些。

设置会话级内存参数

SQL 执行的过程中，可能会需要分配一些临时的内存空间，会话级内存参数控制这些临时内存的大小。这些内存参数用来控制单个会话的内存，当多个会话同时执行时，无法限制这些内存的总大小。

sort_buffer_size

SQL 执行时如果需要排序，会先在内存中排序，sort_buffer_size 控制每个会话可用于排序的内存空间。8.0.12 版本之前，如果 SQL 需要排序，会一次性分配 sort_buffer_size 指定的内存，即使需要排序的数据很少。

8.0.12 版本进行了优化，会根据实际需要的排序的数据按需分配排序内存，最多不超过 sort_buffer_size。一般 sort_buffer_size 可以设置为 256K-2M。

join_buffer_size

执行表连接的时候，如果被驱动表缺少索引，会使用 BNL 连接或 Hash 连接算法，优化器会根据参数 join_buffer_size 的设置，分配连接缓存，用来缓存驱动表的记录，以提高表连接操作的性能。

注意一个 SQL 可能会使用多个 Join Buffer。一般 join_buffer_size 设置为 256K-2M。不建议把全局 join_buffer_size 设置得很大，如果有大查询需要使用更多的 Join Buffer，可以在会话层面调整。

read_rnd_buffer_size

参数 read_rnd_buffer_size 用来控制 MRR 访问路径能使用的 buffer 的大小。关于 MRR 执行计划的更多信息，可以参考后续 SQL 优化的相关文章。

tmp_table_size

tmp_table_size 控制内存临时表的最大空间，当内存临时表内的数据超过 tmp_table_size 后，会转换成磁盘临时表。如果有 SQL 需要排序大量数据，可以在会话级别调整这个参数。

设置 InnoDB 存储引擎参数

InnoDB 存储引擎是 MySQL 实现事务 ACID 属性的关键所在，合理地设置 InnoDB 相关参数，是实现 MySQL 高性能和数据强一致的一个基本前提。

InnoDB 是一个复杂的系统，MySQL 8.0 中 InnoDB 有一百多个参数，当然这里面很多参数使用默认值就可以了，这里对需要重点关注的部分参数做一个介绍。我们按 InnoDB 的内部结构来介绍这些参数。

下面这个图中，我们将 InnoDB 分为 4 个大的结构，分别是 InnoDB Buffer Pool、InnoDB 数据文件、Redo Log Buffer、Redo 日志。

访问 InnoDB 存储引擎表时，需要将数据先缓存到 Buffer Pool，缓存的单位是一个数据页。数据页的大小通过参数 innodb_page_size 指定，默认为 16K，一般我们使用默认值就可以了。

innodb_buffer_pool_size

Buffer Pool 的大小通过参数 innodb_buffer_pool_size 指定，这可能是 MySQL 中最重要的一个参数。如果 innodb_buffer_pool_size 设置得太小，无法把大部分热点数据缓存到内存中，会影响数据库的读写性能。

但如果 innodb_buffer_pool_size 设置得太大，又会导致服务器内存资源耗尽，可能会出现 SWAP，或者触发 OOM-Killer。我们需要根据服务器和 MySQL 使用的实际情况来设置 innodb_buffer_pool_size 。

这里提供一个内存评估的方法，供你参考：

• 为操作系统预留一定的内存（min_free_kbytes，OS 内核运行需要的基础内存），比如 5%。
• OS 其他程序运行占用的内存，比如 MySQL 数据库备份程序和其他程序。
• 文件系统 Cache 会占用一定的内存，比如 InnoDB REDO 日志、binlog 文件。
• MySQL 线程分配的内存，包括运行时分配的内存（join buffer, sort buffer, net buffer 等）和 thread_stack。
• 如果大量使用 MyISAM，需要分配 key buffer。myisam 数据文件还会使用文件系统 cache。
• Inno DB buffer pool 管理需要额外占用一部分内存，大致为 innodb_buffer_pool_size * 5%。

除去上述各类内存，将剩余的内存分配给 Buffer Pool。假设我们的服务器总共有 100G 内存，系统内存按下面这个表格来评估。

那么，留给 InnoDB Buffer Pool 的内存为 79G，考虑到 InnoDB Buffer Pool 的管理开销，innodb_buffer_pool_size 可设置为 75G。当然在真实的业务场景下，MySQL 连接线程会动态分配、释放内存，需要根据真实的运行情况，适当地调整内存设置

innodb_buffer_pool_chunk_size

InnoDB Buffer Pool 分为多个内存块（Chunk），每个内存块的大小由参数 innodb_buffer_pool_chunk_size 指定，默认为 128M。对于大内存的机器，可以适当增加 innodb_buffer_pool_chunk_size，

一个经验值是保持总的 Chunk 数不超过 1000，比如 Buffer Pool 为 1T，可以把 innodb_buffer_pool_chunk_size 设置为 1G。

innodb_buffer_pool_instances

InnoDB Buffer Pool 中存在大量链表结构，并发访问这些链表结构时，需要通过一些互斥锁、读写锁来保证这些数据结构的一致性。当数据库的并发很高的时候，在这些锁结构上会产生严重的争用。可以设置 innodb_buffer_pool_instances，把 Buffer Pool 划分成多个区块，减少争用。

MySQL 8.4 中，这个参数默认值取以下两个数字中的较小值：

• 逻辑 CPU 核数 /4
• innodb_buffer_pool_size / innodb_buffer_pool_chunk_size / 2

innodb_page_cleaners

我们通过 SQL 语句修改表中的数据时，先修改缓存在 Buffer Pool 中的页面。页面被修改后称为脏页。脏页中的数据最终需要写回到数据文件中。Page Cleaner 线程定期扫描 Buffer Pool 中的脏页，发起 IO 请求，将脏页写回磁盘。innodb_page_cleaners 控制 Page Cleaner 线程数量，可以将该参数和 innodb_buffer_pool_instances 设置成一样。

innodb_purge_threads

InnoDB 事务执行过程中，还会生成 Undo 日志。事务提交时，并不会立刻就清理 Undo 日志。Purge 线程会在合适的时机回收 Undo 日志。参数 innodb_purge_threads 控制 Purge 线程的数量。

InnoDB 数据文件

InnoDB Buffer Pool 中的数据，最终会持久化到数据文件中。InnoDB 使用 IO 线程来进行 IO 操作。参数 innodb_read_io_threads、innodb_write_io_threads 分别指定了读 IO 和写 IO 的线程数，默认值都为 4，如果服务器上 CPU 核数多，可以适当增加这 2 个参数。

Redo Log Buffer

为了保障数据的持久化，修改 Buffer Pool 中的页面时，需要生成 Redo 日志。如果数据库或服务器异常崩溃，可以使用 Redo 日志来恢复数据。

innodb_log_buffer_size

事务执行过程中，Redo 日志会先写入到 Redo Log Buffer 中，Buffer 的大小由参数 innodb_log_buffer_size 控制。一般分配几十兆就可以，比如 8.4 中默认为 64M。如果你的数据库并发写入量高，可适当把这个参数增加到几百兆。

innodb_flush_log_at_trx_commit

事务提交时，需要将事务产生的 Redo 日志持久化到 Redo 文件中，这样才能保证数据不丢。参数 innodb_flush_log_at_trx_commit 控制事务提交时，Redo 日志的刷盘行为。设置为 1 时，每个事务提交时都会等待 Redo 日志刷盘完成，这是最安全的设置。但由于要等待 Redo 日志刷盘完成，性能上有一定的开销。这个参数设置为 2 时，事务提交时只会将 Redo 日志写到 Redo 文件中，然后每隔 1 秒刷新一次 Redo 文件，如果服务器异常崩溃，可能会导致部分数据丢失。

Redo 日志

innodb_redo_log_capacity

早期版本中，通过参数 innodb_log_file_size 和 innodb_log_files_in_group 控制 Redo 日志文件的大小和数量。8.0.30 后，新增了 innodb_redo_log_capacity 参数，就不再需要单独设置 innodb_log_file_size 和 innodb_log_files_in_group 了。Redo 文件循环使用，随着数据库事务不停地执行，新的 Redo 日志最终会覆盖老的 Redo 日志。

数据库崩溃恢复时，需要通过 Redo 日志来恢复数据，那么覆盖老的 Redo 文件会不会导致数据丢失呢？或者 MySQL 如何保证覆盖 Redo 文件不影响数据库恢复？其实只要保证覆盖 Redo 文件时，数据库的 Checkpoint LSN 比 Redo 文件最大的 LSN 号更大就行了。

mysql> select file_id, start_lsn, end_lsn from innodb_redo_log_files;
+---------+------------+------------+
| file_id | start_lsn  | end_lsn    |
+---------+------------+------------+
|     656 | 2168369664 | 2171644416 |
+---------+------------+------------+

mysql> show global status like '%lsn%';
+-------------------------------------+------------+
| Variable_name                       | Value      |
+-------------------------------------+------------+
| Innodb_redo_log_checkpoint_lsn      | 2170307263 |
| Innodb_redo_log_current_lsn         | 2170307263 |
| Innodb_redo_log_flushed_to_disk_lsn | 2170307263 |
+-------------------------------------+------------+

如果 innodb_redo_log_capacity 设置得太小，数据库写入量又比较大，那么覆盖 Redo 文件时，就可能需要等待数据库 Checkpoint，这会严重影响数据库写入的性能。对于 Buffer Pool 比较大，写入频繁的数据库，需要把 innodb_redo_log_capacity 设置得大一些，设置成几个 G 到几十 G 都是可以的。

下面以这个表格对 InnoDB 参数的设置做一个简单的总结。

博文部分内容参考

© 文中涉及参考链接内容版权归原作者所有，如有侵权请告知 :)

---

---

© 2018-至今 liruilonger@gmail.com, 保持署名-非商用-相同方式共享(CC BY-NC-SA 4.0)