MySQL参数配置优化

千家信息网最后更新 2025年01月25日MySQL参数配置优化max_connections
Variable Scope： Global
Dynamic Variable： Yes
Default： 151 （mysql5.5+）
Meaning： 允许客户端同时连接的最大数
默认值以前是100,MySQL5.5+后151，但是默认值对大部分应用来说这都不够。通过观察Max_used_connections
状态变量随着时间的变化。可以告诉你服务器连接是不是在某个时间点有个尖峰。如果这个值达到了max_connections，说明客户端至少被拒绝了一次。
建议值： 500+ （设置为你认为正常情况下有300或者更多连接，则可以设置为500或更多）


thread_cache_size
Variable Scope： Global
Dynamic Variable： Yes
Default： 0（mysql5.6.7-）
Meaning： 有多少线程应该缓存重用
其默认值在mysql5.6.8+（autosized），根据如下公式得到：8 + (max_connections / 100)，其上限值为100.设置这个变量，可以通过观察服务器一段时间的活动，来计算一个有理有据的值。
观察Threads_connected状态变量并且找到它在一般情况下的最大值和最小值。例如：若Threads_connected状态从150变化到175，可以设置线程缓存为75。但是不用设置的非常大，因为保持大量
等待连接的空闲线程并没有什么真正的用处。也可以观察Threads_created状态随时间的变化。如果这个值很大或一直增长，这是另一个线索，告诉你可能需要调大thread_cache_size变量。
Threads_cached来查看有多少线程已经在缓存中了。
建议值：50-100


table_open_cache
Variable Scope: Global
Dynamic Variable: Yes


Default: 400( mysql5.6.7- )
Meaning: 所有线程打开表的数量
从官方文档看出在MySQL5.6.8+开始默认值为2000，就能简单的判断出原来默认值是不够的。可以通过观察Opened_tables其值及其一段时间的变化来检查该变量。如果看到Opened_tables的值很大
并且又不经常执行FLUSH TABLES（执行其命令强制所有的表重新关闭且打开），那么可能你应该增加该变量的值。
建议值： 4096（有另一种说法：这个值从max_connections的10倍开始设置）


open_files_limit
Variable Scope： Global
Dynamic Variable： No


Default： 0 （mysql5.6.7-）
Meaning： 操作系统允许mysqld服务打开的文件数。
其默认值在mysql5.6.8+（autosized），根据如下公式得到：
1) 10 + max_connections + (table_open_cache * 2)
2) max_connections * 5
3) open_files_limit value specified at startup, 5000 if none


要知道每个MyISAM表打开需要2个文件句柄；每个客户端的连接也是一个文件句柄。有效的open_files_limit的值是基于系统启动时所指定的值和max_connections，table_open_cache有关联。
建议值：65535 （其值在大多操作系统是最安全的）


table_definition_cache
Variable Scope ： Global
Dynamic Variable ： Yes


Default ： 400（mysql5.6.7-）
Meaning： 缓存表定义的的数量（以.frm结尾的文件）
其默认值在mysql5.6.8+（autosized），根据如下公式得到：400 + (table_open_cache / 2) 其上限值为：2000。常可以把table_definition_cache 设置得足够高，以缓存所有的表定义。
除非有上万张表，否则这可能是最简单的方法。
建议值： 根据真正的数据库中表的数量（例如：数据库实例有1000张表，可以将其设置为1000+）


back_log
Variable Scope: Global
Dynamic Variable: No


Default: 50( mysql5.6.5- )
Meaning: 在很短时间内，可以有多少个请求链接在堆栈中等待被处理。
其默认值在mysql5.6.6+（autosized），根据如下公式得到： 50 + (max_connections / 5) 其上限值为：900。
如果每秒的连接数很多，可以将其值调大。其值和OS的TCP/IP链接有关联，和内核参数net.ipv4.tcp_max_syn_backlog的值相关，back_log的值不能大于其值。
建议值：2048


max_allowed_packet
Variable Scope： Global
Dynamic Variable： Yes


Default: 1MB（mysql5.6.5-）
Meaning: 这个设置防止服务器发送太大的包，也会控制多大的包可以被接受。
其默认值在mysql5.6.6+为4MB，其默认值可能太小了，但设置太大也可能有危险。如果设置太小，有时复制上会出现问题，通常表现为备库不能接收主库发过来的复制数据。使用mysql和mysqldump
客户端程序都可以指定其值的大小。
建议值： 16MB


max_connect_errors
Variable Scope： Global
Dynamic Variable： Yes


Default： 100（mysql5.6.6+）
Meaning： 最大的连接错误数
如果有时网络短暂抽风了，或者应用配置出现错误，或另有问题，如权限，在短暂的时间内不断地尝试链接，客户端可能被列入黑名单，然后将无法连接，知道再次刷新主机缓存（FLUSH HOSTS）。
这个选项的默认设置太小了，很容易导致问题。你也许希望增加这个值，实际上，如果知道服务器可以充分抵御蛮力攻击，可以把这个值设置的非常大，以有效地禁用主机黑名单。这个选项也就是
所谓的可以防止暴力破解。
建议值： 1000000 （其值为Percona 给出的建议值，但是应该确定其主机的已有抵御蛮力攻击的能力）


skip_name_resolve
Variable Scope： Global
Dynamic Variable： No


Default： OFF
Meaning： DNS查找
这个选项禁用了另一个网络相关和鉴权认证的陷进：DNS查找。DNS是MySQL连接过程中的一个薄弱环节。当连接服务器时，它试图确定连接和使用的主机的主机名，作为身份验证凭据的一部分。
（就是说，你的凭据是用户名，主机名，以及密码，并不只是用户名和密码）但是验证主机来源，服务器需要执行DNS的正向和反向查找。要是DNS有问题就悲剧了，在某些时间点这是必然的事。
为了避免这种情况，我们强烈建议设置这个选项，在验证时关闭DNS查找，这样即快又安全。
建议值： ON


log_bin
Variable Scope： Global
Dynamic Variable： No


Meaning： 是否开启binlog
开启此选项用来支持复制和时间点恢复。
建议值： 设置其值为mysql-bin来避免其默认生成的文件名（也就是与主机名无关）


sync_binlog
Variable Scope： Global
Dynamic Variable： Yes


Default： 0
Meaning： 控制MySQL怎么刷新二进制日志到磁盘
默认值为0，意味着MySQL并不刷新，有操作系统自己决定什么时候刷新缓存到持久化设备。如果这个值比0大，它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作
（如果autocommit被设置了，每个独立的语句都是一次写，否则就是一个事务一次写）。如果没有设置sync_binlog为1，那么崩溃以后可能导致二进制日志没有同步事务数据。这可以轻易地导致复制中断，
并且使得及时恢复变得不可能。无论如何，可以把这个值设置为1来获得安全的保障。这样就会要求MySQL把二进制日志和事务日志两个文件刷新到不同的位置。这可能需要磁盘寻道，相对来说是个很慢的操作。
建议值： 1


expire_log_days
Variable Scope ： Global
Dynamic Variable： Yes


Default： 0
Meaning： 服务器在指定的天数之后清理旧的二进制日志
如果启用了二进制日志，应该打开这个选项，可以让服务器在指定的天数之后清理旧的二进制日志。如果不启用，最终服务器的空间会被耗尽，导致服务器卡住或崩溃。
建议值： 7~14


tmp_table_size和max_heap_table_size
这两个设置控制使用Memory引擎的内存临时表能使用多大的内存。如果隐式内存临时表的大小超过这两个设置的值，将会被转换为MyISAM表，所以它的大小可以继续增长。（隐式临时表是一种并非由自己创建，
而是服务器创建，用于保存执行行中的查询的中间结果的表）应该简单地把这两个变量设为同样的值。但是要谨防这个变量太大了，临时表最好呆在内存里，但是如果它们被撑得很大，实际上还是让它们使用
磁盘比较好，否则可能会让服务器内存溢出。假设查询语句没有创建庞大的临时表（通常可以通过合理的索引和查询设计来避免），那把这些变量设大一点，免得把内存临时表转换为磁盘临时表。这个过程可以
在SHOW PROCESSLIST中看到。使用临时表的情况可以通过状态变量Created_tmp_tables 和 Created_tmp_disk_tables 来监控。
建议值： 设置两个变量为同样的值（这个大小要根据自己的SQL查询级别及SQL语句的优化情况）


query_cache_size
Variable Scope： Global
Dynamic Variable ： Yes
Default： 0（mysql5.6.7-）
Meaning: 缓存查询结果的内存大小
查询缓存使用的总内存空间，单位是字节。这个值必须是1024的整数倍，否则MySQL实际分配的数据会和你指定的略有不同。
建议值：<512MB




sort_buffer_size
Variable Scope: Global, Session
Dynamic Variable: Yes
Meaning: 查询需要做排序操作时为该缓存分配内存大小
MySQL只会在有查询需要做排序操作时才会为该缓存分配内存，然后，一旦需要排序，MySQL就会立刻分配该参数指定大小的全部内存，而不管该排序是否需要这么大的内存。 通过SHOW GLOBAL STATUS查看如
Sort_merge_passes/s 有很多，可以考虑增加sort_buffer_size的值，以来加快order by 或 group by语句的查询速度（其排序或分组操作已不能查询优化或索引优化）。如果查询必须使用一个更大的排序缓存
才能更好的执行，可以考虑session级别的设置其值。
建议值：1MB


join_buffer_size
Variable Scope： Global, Session
Dynamic Variable ： Yes


Meaning： 设置使用连接查询缓存的大小
此选项可以提高没有使用索引的连接查询的性能。全局的建议不要设置太大，可以动态设置session级别的值。
建议值： 8MB


read_rnd_buffer_size
Variable Scope ： Global, Session
Dynamic Variable ： Yes
Default： 256kb
Meaning： 读取排序行的缓存的大小
MySQL只会在有查询需要时才会为该缓存分配内存，并且只会分配需要的内存大小而不是全部指定的大小。如果增加其值，可以提高order by 的操作性能。
建议值： 16MB


key_buffer_size
Variable Scope: Global
Dynamic Variable : Yes


Default: 8MB
Meaning: MyISAM 表索引缓存的大小
如果MySQL server的表全部是或者说大多为MyISAM存储引擎的，可以考虑将其设置为OS内存的30%。其缓存仅仅缓存的是索引块，而不缓存数据。
建议值： 根据其数据库中表的存储引擎的类型来作为参考


myisam_sort_buffer_size
Variable Scope： Global, Session
Dynamic Variable ： Yes
Default： 8MB
Meaning： 排序MyISAM的索引缓存的大小
当REPAIR TABLE或者创建索引，修改索引时操作（CREATE INDEX，ALTER TABLE）时，分配给用于其MyISAM索引排序的大小。
建议值: 8MB~256MB


innodb_buffer_pool_size
Variable Scope : Global
Dynamic Variable: No
Default： 128MB
Meaning： InnoDB缓冲池的大小
如果大部分都是InnoDB表，InnoDB缓冲池或许比其他任何东西更需要内存。InnoDB缓冲池并不仅仅缓存索引：它还会缓存行的数据，自适应哈希索引，插入缓冲，锁，以及其他内部数据结构。
建议值：80%+ 物理内存


innodb_buffer_pool_instances


Variable Scope ： Global
Dynamic Variable ： No
Default： 1（mysql5.6.5-）
Meaning： InnoDB缓存池的实例个数
从InnoDB1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同的缓冲池实例中。这样做的好处是减少数据库内部资源竞争，增加数据库的并发处理能力。可以通过参数
innodb_buffer_pool_instances来配置。在MySQL5.6.6+其默认值为：（autosized），除了Window 32bit其值是根据innodb_buffer_pool_size的大小动态得到，其它默认值为8.
建议值：4+ （mysql5.5+）


innodb_log_buffer_size
Variable Scope： Global
Dynamic Variable ： No
Default： 8MB
Meaning： InnoDB写日志文件到磁盘上的缓冲大小
一个较大的日志缓冲，可以使一个大的事务在commit之前不用将log写到磁盘上。同样的在update，delete，Insert
很多行时，也可以减少磁盘I/O的调用。
建议值：8~128MB


innodb_flush_log_at_trx_commit


Variable Scope： Global
Dynamic Variable： Yes
Default： 1
Meaning： 控制日志缓冲刷新的频繁程度
日志缓冲必须刷新到持久化存储，以确保提交的事务完全被持久化了。如果和持久相比更在乎性能，可以修改
innodb_flush_log_at_trx_commit变量来控制日志缓冲刷新的频繁程度。可能的设置如下：
0
把日志缓冲写到日志文件，并且每秒刷新一次，但是事务提交时不做任何事情。
1
将日志缓冲写到日志文件，并且每次事务提交都刷新到持久化存储。这是默认的（并且是最安全的）设置，该设置
能保证不会丢失任何已经提交的事务，除非磁盘或者OS是'伪'刷新。
2
每次提交时把日志缓冲写到日志文件，但是并不刷新。Innodb每秒做一次刷新。0与2最重要的不同是，如果MySQL
进程"挂了"，2不会丢失事务。
建议值： 1


innodb_log_file_size


Variable Scope： Global
Dynamic Variable ： No
Meaning： 指定每个重做日志文件的大小
重做日志文件的大小设置对于InnoDB存储引擎的性能有着非常大的影响。一方面重做日志文件不能设置的太大，如果设置得很大，在恢复时可能需要很长的时间；另一方面又不能设置太小了，
否则可能导致一个事务的日志需要多次切换重做日志文件。
建议值： 根据自己能接受的方面（更好的性能or更短的恢复时间）决定


innodb_io_capacity
Variable Scope： Global
Dynamic Variable： Yes
Default： 200
InnoDB曾经在代码里写死了假设服务器运行在每秒100个I/O操作的单硬盘上。默认值很糟糕。现在可以告诉InnoDB服务器有多大的I/O能力。有时需要把这个值设置得相当高（像SSD这样极快的存储
设备上需要设置为上万）才能稳定地刷新脏页。
建议值： 根据server的I/O能力有关系


innodb_read_io_threads和innodb_write_io_threads
这些选项控制有多少后台线程可以被I/O操作使用。最近版本的MySQL里，默认值4个读线程和4个写线程，对大部分
服务器这都足够了，尤其是MySQL5.5里面可以用操作系统原生的异步I/O以后。
建议值：各为4（即默认值）


innodb_log_files_in_group


Variable Scope： Global
Dynamic Variable： No
Default ： 2
Meaning： 每组InnoDB重做日志文件的个数
建议值： 2


innodb_file_per_table


Variable Scope ： Global
Dynamic Variable ： Yes


Default： OFF（mysql5.6.5-）
Meaning： 控制InnoDB表空间存储形式
其默认值在mysql5.6.6+后为ON。开启此选项后，关于InnoDB表的数据和索引单独存储在自己的表空间中（.ibd结尾的文件）。否则，存储在系统的表空间中（ibdata）。
建议值： ON