如何优化MySQL参数：达到性能最佳状态

MySQL参数优化是指尽可能地调整MySQL的参数以达到最佳性能状态。MySQL> 有很多参数可以用来影响它的行为，这些参数可以分为两类：

• 动态参数：可以在MySQL运行的过程中随时修改，不需要重新启动MySQL。
• 静态参数：需要在MySQL启动前进行设置，在启动后不能再修改。

优化MySQL参数的目标是使MySQL尽可能地使用系统资源，如内存、CPU和磁盘等，以提高其性能水平。这样可以让MySQL更加快速和稳定地处理大量的事务求。

🐕 参数优化之 Max_connections

简介：
Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。

# 查看设置的连接数
mysql> show variables like 'max_connections';
 +-----------------+-------+
 | Variable_name   | Value |
 +-----------------+-------+
 | max_connections | 200   |
 +-----------------+-------+
# 查看当前最大的连接数
mysql> show status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 166   |
+----------------------+-------+

#可以通过更改配置文件的方式修改该参数
> vim /etc/my.cnf 
Max_connections=1024

🦌 参数优化之 key_buffer_size

MySQL的key_buffer_size参数是指用于缓存MyISAM表索引的大小，它表示索引缓存的字节数。MyISAM表是一种基于磁盘的存储模式，索引对查询性能有着很大的影响，因此优化索引缓存是提升MySQL性能的重要方式之一。

key_buffer_size的默认值是8M，对于大型的数据库应用来说，可以考虑将其调整为更大的值，如256M，512M等。在调整key_buffer_size参数时，需要考虑系统内存的大小以及其他应用程序所需要的内存，以避免出现内存不足的情况。

调整key_buffer_size参数的方法如下：

可以通过MySQL客户端连接到MySQL数据库，使用以下命令查看当前key_buffer_size的值。

show variables like ‘%key_buffer_size%’;

修改MySQL的配置文件（my.cnf或my.ini），在[mysqld]标签下添加key_buffer_size=xxx，其中xxx为你需要设置的值。如果你不确定应该设置多少值的话，可以通过下面两种方式来计算：

（1）通过hrm (key_blocks_used*1024) / key_blocks_size进而反推出来

（2）根据MySQL当前的运行状态动态调整，当查询语句中“key_read_requests/key_reads”的比例接近1时，说明效率不高，需要考虑适当增加key_buffer_size的值。

停止MySQL服务，重新启动MySQL以使配置生效。

注意：key_buffer_size仅适用于MyISAM表，在使用InnoDB引擎时，需要使用innodb_buffer_pool_size参数对缓存进行优化。

🐘 参数优化之 max_allowed_packet

max_allowed_packet是MySQL中的一个参数，用于限制网络传输中数据包的大小。该参数指定了在MySQL服务器和客户端之间传输的最大数据包大小，包括binlog传输和网络传输等。

有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数

要调整max_allowed_packet参数的值，可以采取以下步骤：

• 1、打开MySQL的配置文件my.cnf（或my.ini）。
• 2、以管理员身份打开终端/命令提示符。
• 3、修改参数max_allowed_packet的值。例如，将其修改为16MB：
• 4、设置为16MB（单位为字节）：max_allowed_packet=16777216。
• 5、设置为32MB：max_allowed_packet=33554432。注意：如果你想使max_allowed_packet的值大于1GB，则需要在修改配置参数my.cnf时，将max_allowed_packet的值定义为以字母“G”为结尾的数字，例如1G。
• 6、保存配置文件。
• 7、重新启动MySQL服务以使配置生效。

🦔 参数优化之 thread_cache_size

MySQL的thread_cache_size参数是指线程缓存的大小。当MySQL服务器需要处理连接请求时，会为每个连接创建一个线程，创建线程的过程非常耗费CPU和内存资源。为了避免重复创建和销毁线程，MySQL提供了线程缓存功能，使得MySQL可以从缓存中使用一个已经存在的线程，而不是每次都创建一个新的线程。当一个线程关闭连接时，该线程会重新加入到线程缓存中，等待下一个连接请求的到来。

通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

mysql>  show status like 'threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 4     |
| Threads_created   | 1234  |
| Threads_running   | 2     |
+-------------------+-------+

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpu SYS资源，可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
(3)配置方法：
thread_cache_size=32

需要注意的是：调整thread_cache_size的值也可能影响到MySQL的性能。如果线程缓存的大小过大，会浪费系统资源，造成额外负担。如果线程缓存的大小过小，则会降低MySQL的响应速度。在调整thread_cache_size参数时，需要综合考虑服务器资源和负载等因素，以寻找最佳的缓存大小。

🐦 参数优化之 innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit是MySQL的一个参数，用于控制在事务提交时将事务日志写入磁盘的方式。事务日志是在处理事务时生成的日志，记录了事务所做的所有更改内容。innodb_flush_log_at_trx_commit参数的默认值为1，表示在每个事务提交时都要将事务日志写入磁盘中，这是最常用的设置。

innodb_flush_log_at_trx_commit有三个可选值：

• 0：表示不将事务日志同步到磁盘，即不保证事务的持久性。这是最快的方式，但是在服务器停机时可能会导致数据丢失。

• 1：表示在每次事务提交时都将事务日志写入磁盘，这是最安全的设置，但也是最慢的方式，会影响写入性能。

• 2：表示在每次事务提交时将事务日志写入操作系统的缓存中，而不是直接写入磁盘。只有当缓存被刷新时，才将事务日志同步到磁盘中。这种设置比较灵活，不会对性能产生太大的影响，同时也可以保证数据的安全性。

在调整innodb_flush_log_at_trx_commit参数时，应该慎重考虑应用程序的需求以及服务器的性能和可靠性等因素，以找到最佳的设置。如果数据的安全性比写入性能更重要，可以将innodb_flush_log_at_trx_commit参数设置为1或者更高的值。如果写入性能更为重要，则可以将该参数设置为0或较小的值（如2）。

🦚 参数优化之 innodb_log_file_size

innodb_log_file_size是MySQL中用来设置InnoDB事务日志文件大小的参数。InnoDB事务日志文件用于保存事务操作过程中产生的所有内容，以便在系统崩溃或重启时，能够恢复到之前的状态。因此，InnoDB事务日志文件的大小和数量，对于MySQL数据库的安全和性能都有很大的影响。

默认情况下，innodb_log_file_size的值为48M。如果读写操作较为频繁，可以适当增加日志文件的大小来减少文件切换的次数，提高InnoDB事务的性能。但是，innodb_log_file_size也不是越大越好，过大的日志文件会占用太多的硬盘空间，也可能影响系统性能。

要调整innodb_log_file_size的大小，可以采取以下步骤：

> vim /etc/my.cnf 
innodb_log_file_size=***
停止MySQL服务，删除原有的日志文件，以便MySQL重新创建新的日志文件。
重新启动MySQL服务，以使配置生效。

需要注意的是：修改innodb_log_file_size参数的值需要重新启动MySQL服务，并删除原有的日志文件。如果日志文件的大小不当，可能会引起应用程序崩溃、数据库性能下降、系统闪退等问题。因此，在设置innodb_log_file_size参数时，需要综合考虑服务器资源、应用程序的负载和性能、数据库的可靠性和安全等因素，找到最佳的日志文件大小和数量。

🦖 参数优化之 innodb_log_files_in_group

innodb_log_files_in_group是MySQL中用来设置InnoDB事务日志文件数量的参数。在InnoDB存储引擎中，事务日志文件用于保存事务操作过程中产生的所有内容，以便在系统崩溃或重启时，能够恢复到之前的状态。因此，InnoDB事务日志文件的大小和数量，对于MySQL数据库的安全和性能都有很大的影响。

innodb_log_files_in_group指定了每个日志文件组中所包含的日志文件数量。默认情况下，innodb_log_files_in_group的值为2，这意味着每个日志文件组中包含两个事务日志文件，日志文件的命名规则为：ib_logfile0和ib_logfile1。如果读写操作较为频繁，可以适当增加日志文件的数量来减少文件切换的次数，提高InnoDB事务的性能。但是，每个日志组中日志文件的数量应该控制在适当的范围内，以避免过多的日志文件占用过多的硬盘空间。

💐 参数优化之 安全参数

Innodb_flush_method=(O_DIRECT, fsync) 
1、fsync    ：
（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
2、 Innodb_flush_method=O_DIRECT
（1）在数据页需要持久化时，直接写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘

最安全模式：
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
最高性能模式：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

---

后续会继续增加其他参数。