数据库服务器配置

充分了解MySQL配置文件中各个变量的意义对于优化MySQL数据库的性能具有重要意义。通常，我们需要根据不同的数据级别和不同的生产环境来优化MySQ配置文件。

Linux下的MySQL配置文件是存放在/etc/my.cnf、/etc/MySQL/my.cnf、/usr/etc/my.cnf、~/.my.cnf中的my.cnf，如果没有设置使用指定目录的my.cnf，MySQl会按照优先级使用上述文件，优先级越高，

本教程将带领您逐一分析最新的MySQL 8.0配置文件，并尝试理解每个变量。当然，除了理解变量的含义，更重要的是在实践中进行微调，以达到优化性能的目的。

1. 通用配置1.1 数据文件存放目录

Skip-grant-tables复制代码2。连接数2.1最大连接数MySQL >；显示变量，如& # 39；% max _ connections % & # 39；+ – + – +|变量名称|值|+-+|最大连接数| 151 |+-++集合中的1行(0.01秒)mysql & gt设置全局max _ connections = 256查询正常，0行受影响(0.00秒)复制mysql的最大连接数max_connections。如果数据库的并发连接请求相对较大，则应该提高该值。调整连接数时，应充分考虑硬件资源等因素。如果连接数太多，容易造成阻塞。

2.2 单个用户最大连接数

2.3 最大等待连接数

2.4 交互式最大连接时长

interactive_timeout的默认值为28800，单位秒为8小时。有兴趣的朋友可以把这个参数改成10s，10s后再重新连接看看有没有错误。

2.5 非交互式最大连接时长

3.日志3.1 错误日志文件名称

cat/etc/my . CNF # log-error =/var/log/mysqld . log复制代码log-error用于指定错误日志文件的名称，用于记录mysqld启动和停止时，以及服务器发生任何严重错误时的相关信息。

3.2 开启binlog

要启动MySQL的binlog，需要在配置文件my.cnf中添加以下配置，然后重新启动服务:

Server_id = 2 log _ bin = MySQL-binbinlog _ format = rowexpire _ logs _ days = 30复制代码注意，server _ id是必填项，其他配置可选；重启后，登录MySQL查看binlog是否打开:

mysql & gt显示变量，如& # 39；% log _ bin % & # 39；+–+|变量名称|值|+-+-+| log _ bin | ON | | log _ bin _ basename | | var/lib/MySQL/MySQL-bin | | log _ bin _ index |/var/lib/MySQL/MySQL-bin . index | | log _ bin _ trust _ function _ creators | OFF | | log _ bin _ use _ v1 _ row _ events | OFF | | SQL _ log _ bin | ON |+—–。

3.3 指定数据库开启binlog

默认情况下，如果我们开启binlog，MySQL会默认记录所有数据库的binlog日志；这样我们的MySQL binlog日志会变得很大，数据恢复会变得很慢，所以有时候，我们只能指定一些核心的重要库来打开binlog。

如下，在MySQL开启binlog的前提下，只在数据库测试时开启binlog。

Binlog_do_db = test复制的代码重启后，登录MySQL并检查binlog _ do _ db选项:

3.5 查询日志记录

General_log允许您记录服务器执行的所有SQL语句。这对于调试、故障排除和审计非常有用。

启用general_log后，MySQL在日志文件中写入所有执行的SQL语句，包括SELECT、INSERT、UPDATE、DELETE等操作，以及连接和断开的事件。

mysql & gt显示变量，如& # 39；general _ log & # 39；+ -变量名称|值|+ -常规日志|关闭。关于& # 39；；命令来启用general_log，或者使用SET GLOBAL general _ log = & # 39关& # 39；；命令来禁用它。你也可以使用显示变量，比如& # 39；general _ log % & # 39；命令查看有关general_log的当前设置。但是，启用general_log可能会对服务器性能产生负面影响，并且可能会生成大量日志数据。因此，建议仅在必要时启用它，并定期清理日志文件。

3.6 慢查询日志记录

Slow_query_log可以将执行时间超过预设阈值的查询定义为brute SQL，这对于优化查询性能非常有用，因为它可以发现和分析长时间的查询语句并进行优化。

启用slow_query_log时，MySQL会将所有执行时间超过预设阈值的查询语句记录到一个日志文件中:

mysql & gt显示变量，如& # 39；slow _ query _ log & # 39；+ -变量名称|值|+ -慢速查询。

3.7 慢查询的时间

Long_query_time表示慢速查询的时间，超过这个时间的查询语句会被记录在慢速查询文件中，如下图。默认值为10秒。

mysql & gt显示变量，如& # 39；long _ query _ time & # 39；+ – + – +|变量名称|值|+-+-+| long _ query _ time | 10.000000 |+-+复制代码4。缓存4.1查询缓存MySQL8中已经移除了查询缓存。如果小伙伴用的是MySQL7以上，就不要关注这段内容；如下，通过执行命令show status like & # 39% query _ cache % & # 39查看查询缓存的相关属性。

mysql & gt显示变量，如& # 39；% query _ cache % & # 39；+-+-+| Variable _ name | Value |+-+-+| he _ Query _ cache | YES | | Query _ cache _ limit | 1048576 | | Query _ cache _ min _ RES _ unit | 4096 | | Query _ cache _ size | 1048576 | | Query _ cache _ type | OFF | | Query _ cache _ wlock _ invalid | OFF |+-复制代码如上所示，下面将简要说明一些核心属性:

query_cache_size：查询缓存的大小，未来版本被删除query_cache_limit: 超出此大小的查询将不被缓存query_cache_min_res_unit：缓存块最小大小，默认是4kquery_cache_type：缓存类型，决定缓存什么样的查询 0：表示禁用 1：表示将缓存所有结果，除非sql语句中使用sql_no_cache禁用查询缓存 2：表示只缓存select语句中通过sql_cache指定需要缓存的查询

虽然在MySql8中去掉了查询缓存模块，但是如果使用MySql7及以下版本，也可以充分利用查询缓存的特性来提高MySQL查询的效率，比如一些常量配置表。

4.2 排序缓存区大小

Sort_buffer_size意味着每个需要排序的线程分配一个这样大小的缓冲区:

mysql & gt显示变量，如& # 39；% sort _ buffer _ size % & # 39；+–+|变量名称|值|+-+-+| innodb _ sort _ buffer _ size | 1048576 | | Myisam _ sort _ buffer _ size | 8388608 | | sort _ buffer _ size | 262144 |+-。Innodb_sort_buffer_size表示InonoDB存储引擎，默认大小为1M，myisam_sort_buffer_size表示myisam存储引擎，默认大小为8M。

4.3 join 缓冲区大小

当MySQL执行连接查询时，它需要匹配两个或更多表中的数据。为了提高连接操作的性能，MySQL会先将其中一个表中的数据读入缓存，然后在缓存中执行连接操作，而不是每次都从磁盘中读取数据。默认情况下:

mysql & gt显示变量，如& # 39；% join _ buffer _ size % & # 39；+ – variable _ name | value |+ -如果您的MySQL服务器经常执行大型连接查询，您可以考虑增加该值以提高查询性能。但需要注意的是，大缓冲区可能会导致内存使用率高，需要根据实际情况进行调整。

4.5 thread_cache_size-线程缓存池

MySQL中的Thread_cache_size是一个控制线程缓存的系统变量。这个变量指定MySQL服务器在缓存中保留的线程数量。线程缓存可以减少创建和销毁线程的开销，从而提高MySQL的性能。当客户端连接到MySQL服务器时，服务器将检查是否有可用的现有线程。如果存在，该线程用于服务客户端请求。否则，服务器将创建一个新线程来处理客户端请求；

5.Innodb存储引擎5.1 buffer pool 数据缓冲区

当查询需要访问表中的数据时，InnoDB存储引擎会首先检查缓冲池中是否有相关数据。如果缓冲池中已经有数据，InnoDB存储引擎可以直接从缓冲池中获取数据，而无需从磁盘中读取数据。这样可以大大提高查询速度，默认大小为128M:

5.2 事务日志刷盘时间点

Innodb_flush_log_at_trx_commit用于控制重做日志缓冲区的数据何时写入磁盘。

mysql & gt显示变量，如& # 39；% innodb _ flush _ log _ at _ Trx _ commit % & # 39；；+ – + – +|变量名称|值|+-+-+| innodb _ flush _ log _ at _ Trx _ commit | 1 |+-+复制代码该参数有以下三个可能的值:

0：表示事务提交时不立即将日志写入磁盘，而是将日志缓存在内存中，直到发生检查点或者缓冲池满时才写入磁盘。这种方式可以提高性能，但是在数据库崩溃时可能会丢失最近提交的事务数据。1：表示事务提交时立即将日志写入磁盘，并等待磁盘写操作完成后才返回客户端。这种方式可以保证最大程度上的数据安全性，但会对性能产生较大影响。2：表示事务提交时将日志缓存在内存中，但每秒钟将缓冲区中的日志写入磁盘。这种方式可以在一定程度上平衡性能和数据安全性。5.3 innodb_thread_concurrency 并发线程数

Innodb_thread_concurrency定义了Innodb引擎中可以并发运行的线程数量，包括读取线程、写入线程、刷新线程和其他管理线程。InnoDB_thread_concurrency的原理其实就是限制InnoDB线程池中可以并发执行的线程数量，避免系统资源的过度使用，从而提高系统的性能和稳定性。但在实际应用中，由于不同的应用场景和系统配置可能会产生不同的结果，需要根据实际情况进行调整。一般来说，在高负载的数据库系统中，适当调整innodb_thread_concurrency的值可以有效提高系统的并发性能。

5.4 redolog文件大小

InnoDB_log_file_size参数用于设置InnoDB存储引擎的单个日志文件的大小，以字节为单位；默认大小为48MB，如下所示:

5.5 redolog文件个数

Innodb_log_files_in_group表示重做日志组中包含的重做日志文件的数量。其默认值为2:

mysql & gt显示变量，如& # 39；% innodb _ log _ files _ in _ group % & # 39；；+ – + – +|变量名称|值|+-+-+| innodb _ log _ files _ in _ group | 2 |+-+复制代码。一般来说，建议将innodb_log_files_in_group设置为2或3，这样，当重做日志文件已满时，修改innodb_log_files_in_group参数的值将涉及到重建重做日志文件，因此，请确保在修改参数之前备份数据，以防止数据丢失。

5.6 innodb_file_per_table

InnoDB_file_per_table的功能是控制InnoDB存储引擎是否将每个表的数据和索引存储在单独的文件中:

当innodb_file_per_table设置为OFF时，所有表的数据和索引将存储在共享的ibdata文件中。这样做的好处是可以减少文件系统的碎片，更高效地利用磁盘空空间。但是，在这种情况下，如果需要恢复一个表的数据，就需要恢复整个ibdata文件，这可能比较耗时，也比较麻烦。

因此，根据具体的应用场景和需求，可以选择开启或关闭innodb_file_per_table配置选项。

原文链接:https://juejin.cn/post/7207718128939221049

免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。