MySQL高性能核心参数配置

前言

MySQL作为当前最流行的开源关系型数据库管理系统之一，其性能和稳定性经过多年的考验得到大家一致的肯定，因此一些中小型企业将MYSQL作为系统数据库的第一选择。然而MYSQL的很多默认配置参数在高并发、数据量过大的情况下，采用其默认配置会影响系统性能。因此优化MYSQL性能有时候是必要的，除了大多数情况下优化SQL性能之外，还可以通过合理的设置MYSQL参数来提升其处理能力和响应速度。因此本章节将介绍几个关键参数，如何调整这些参数从而优化MYSQL性能。

通常来说，数据库服务所在的机器应该留有足够的内存，内存肯定是影响MYSQL性能重要的因素之一，尽可能将更多的数据加载到内存中，从而减少磁盘的I/O操作，以达到提升性能的效果。除了增加内存之外，还可以通过调整MYSQL参数来提升性能

一、innodb_buffer_pool_size（控制InnoDB缓冲池大小）

参数描述:：

innodb_buffer_pool_size 是 MySQL 中 InnoDB 存储引擎中的一个重要参数，它用于控制 InnoDB 存储引擎的内存缓存池大小。从MYSQL5.5版本开始，
MySQL 默认的存储引擎为innoDB，它将数据和索引保存在表空间中，并且支持事务和行级锁等高级特性。其中内存缓存池buffer pool是 InnoDB
存储引擎的最重要的内存区域，用于缓存表中的数据和索引等信息，将磁盘上的数据读取到内存中，以加快数据库访问速度和提高性能。因此，通过合适的设置参数
innodb_buffer_pool_size ，可以控制 InnoDB 存储引擎使用的内存缓存池的大小。当 innodb_buffer_pool_size 的值越大，InnoDB 存储引擎所能利用的内存空间就越大，可以缓存更多的数据及索引，从而能够减少磁盘 I/O 的次数，提高数据库性能。

参数设置建议：

将innodb_buffer_pool_size设置为系统总内存的50%~80%这个范围比较合理，比如您的数据库服务器总内存为64GB，那么可以将innodb_buffer_pool_size参数设置为32GB或者再高一点。

参数配置：

配置参数分为两种方式，一种是通过变量配置，这种只是临时有效，当服务器重启后该配置将失效，您可以在MYSQL执行一下语句将innodb_buffer_pool_size参数改为32GB：
SET GLOBAL innodb_buffer_pool_size = 32G;
一般来说并不推荐变量配置的方式，因为它只是临时使用，通常而言我们是修改MYSQL配置文件my.cnf或者是my.ini文件，在[mysqld]部分，设置innodb_buffer_pool_size的值：
innodb_buffer_pool_size = 25G;

二、max_connections、thread_cache_size（并发与连接参数）

参数描述:：

max_connections：用于控制MYSQL允许最大的并发连接数，一个合适的max_connections值可以确保服务器在高负载情况下仍能正常运行，而不会因为连接资源耗尽而崩溃‌。如果服务器的CPU和内存资源充足，可以适当提高max_connections值以支持更多的并发连接。
‌thread_cache_size：用于指定MYSQL线程缓存的大小。‌ 该参数用于缓存空闲的线程，以便在需要时快速响应连接请求，减少线程的创建和销毁，从而提高数据库的性能‌。

参数设置建议：

max_connections：据服务器硬件和预期负载设置，避免设置过高导致资源耗尽。‌max_connections的合理设置范围通常在2000-10000之间
thread_cache_size：根据并发连接数情况进行调整，通常设置为几十到几百

参数配置：

‌max_connections：可以通过MYSQL命令行进行临时修改，使用
SET GLOBAL max_connections = 新的连接数;
进行修改，但这种修改仅对当前运行的MySQL服务实例有效，重启服务后设置会丢失‌。可以修改my.cnf配置文件实现永久生效：
max_connections = 200
thread_cache_size：临时修改可以执行以下SQL语句：
SET GLOBAL thread_cache_size = 50;
进行修改，但这种修改仅对当前运行的MySQL服务实例有效，重启服务后设置会丢失‌。可以修改my.cnf配置文件实现永久生效：
thread_cache_size = 50

三、innodb_flush_method

参数描述:：

innodb_flush_method参数控制MySQL将数据刷到InnoDB的数据文件和日志文件的动作。Linux系统上，常用的选项有一下几四种:

参数设置建议：

通常对于硬盘性能好的服务器，可以设置成O_DIRECT，这样避免在InnoDB缓存和操作系统缓存中存有两份数据，而且InnoDB缓存比操作系统缓存效率要高，因为InnoDB缓存是专门针为InnoDB的数据设计的，而操作系统缓存是为通用的数据设计。

参数配置：

innodb_flush_method=O_DIRECT

四、innodb_io_capacity

参数描述:：

innodb_io_capacity 用于控制Innodb引擎的I/O能力，这个参数直接影响到Innodb存储引擎在读写数据时的性能，该参数的设置取决于硬盘的IOPS的大小，IOPS就是每秒的读写次数。MYSQL默认innodb_io_capacity的值为200。

参数设置建议：

根据硬件类型和性能需求进行调整。

参数配置：

innodb_io_capacity = 2000 # 适用于SSD
innodb_io_capacity_max = 4000

五、tmp_table_size(控制临时表大小)

参数描述:：

tmp_table_size参数用于设置MYSQL内部内存临时表的大小。 该参数不适用与MEMORY引擎的表，MEMORY表用max_heap_table_size参数进行配置。MYSQL默认的临时表为16MB，要知道临时表只在本会话有效，会话断开后，临时表数据会自动清理。如果内存中的临时表超出限制，MySQL自动将其转换为磁盘上的MyISAM表。

参数设置建议：

如果要执行许多 GROUP BY或者union、order by、子查询、distinct等查询语句时，可以增加tmp_table_size的值。

参数配置：

tmp_table_size = 64M
max_heap_table_size = 64M

六、join_buffer_size

参数描述:：

‌join_buffer_size是MySQL中用于控制连接操作的缓冲区大小的一个参数，主要影响无索引的JOIN操作的性能。‌ 通过适当调整这个参数，可以提高连接操作的效率，从而加快查询的执行速度‌。连接操作是指在执行查询时使用JOIN关键字来连接多个表。这个缓冲区用于存储连接操作中的中间结果，正确设置join_buffer_size可以显著提高查询性能‌。

参数设置建议：

join_buffer_size在MySQL官方文档中的解释为用于类型为普通的索引扫描，范围扫描和全表扫描的连接缓冲区，两个表连接时会产生一个join buffer，多个表的连接可以产生多个join buffer，join buffer在表连接发生之前进行分配，在SQL语句执行完毕之后进行释放。
官方默认值为256K，这个值对大表间的连接查询多数情况下是不够用的，但是join_buffer_siz是一个连接级别的参数，并不是全局参数，受并发连接数影响，所以需要结合自身的实际情况来分配，如果内存充足可适量调大，如调整为2M/4M/8M/16M，如果内存资源紧张且并发连接数高需谨慎调整，调大可能会让你的数据库因内存不足可造成系统OOM实例宕机的风险

参数配置：

join_buffer_size= 4MB