mysql 配置文件my.cnf优化参数配置说明

skip-external-locking

跳过外部锁定,用于多进程条件下为MyISAM数据表进行锁定。现在我的实例数据库都使用了InnoDB，所以没有细细去品这个参数。

 

key_buffer_size = 64M

对MyISAM表性能影响很大，用于主健缓冲使用的。从内存读取索引率应高于 95%，使用的变量/公式:100 - (Key_reads / Key_read_requests * 100)

 

max_allowed_packet = 1M

最大允许提交的数据，比如你要插入一个很大的数据包到库里，大于1M就会报错。类似上传文件限制或者POST内容原理。如果设置超过1G，查看最终生效结果也只有1G。增大此配置的值并不会有太多危险，因为额外的内存仅仅只在需要的时候被分配。

 

table_open_cache = 2048

每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。

通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_open_cache的值。

使用：

SHOW GLOBAL STATUS LIKE 'open%tables'

 得到以下值：

Open_tables 2048

Opened_tables 84045

然后再使用：

show variables like '%table_open_cache%'

table_open_cache 2048

table_open_cache_instances 16

发现open_tables等于table_open_cache，都是2048，说明mysql正在将缓存的表释放以容纳新的表（通俗讲，就是缓存不够，需要加大了），4G内存的机器，建议设置为2048

比较适合的参考值：

Open_tables / Opened_tables >= 0.85

Open_tables / table_open_cache <= 0.95

 

sort_buffer_size = 1M

sort_buffer_size 是是一个connection级参数，意思是每个连接都分配内存的参数，调整这个参数要格外注意，不是越大越好，够用就行。过大的设置+高并发可能会耗尽系统内存资源。sort_buffer_size 来加速ORDER BY 或者GROUP BY 操作,不能通过查询或者索引优化的。如果你需要排序的语句比较多可以适当增加这个值。

 

net_buffer_length = 8K

每个客户端连接时。用于维持连接缓冲，初始分配预设值，在有需要时，则会自动扩大到max_allowed_packet大小，然后在回收预设的net_buffer_length大小 最小1k 最大1m默认16k

 

read_buffer_size = 2M

如果要对大型表进行快速的全表扫描，则应将此变量设置为较高的值。大内存大小的示例my.cnf值建议使用1M设置，MySQL内置默认值为128K。有些人拥有大量内存，并且很少有并发连接，因此将其设置为32M以希望获得更好的性能。让我们看看这是否真的是最好的策略。

 

read_rnd_buffer_size = 1M

read_rnd_buffer_size是MySQL的随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要大量数据可适当的调整该值，但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值，以免内存开销过大。表的随机的顺序缓冲 提高读取的效率。

 

myisam_sort_buffer_size =64M

MyISAM表发生变化时重新排序所需的缓冲。一般64M足矣。

 

thread_cache_size = 128

线程池缓存大小(当客户端断开连接后，将当前线程缓存起来，当在接到新的连接请求时快速响应，无需创建新的线程。 )因为每个线程的连接/断开都需要。如果应用程序中有大量的跳跃并发连接并且线程较多的话，就要加大它的值。它的目的是在通常的操作中无需创建新线程。内存大于3G左右内存的机器设置64~128基本足够使用。

 

query_cache_type = 0

为1是使用缓冲，2是除非使用SQL_CACHE才进行缓冲。对于缓冲而言，数据并不是实时的，有一定的延时。但是对于实时性要求不高的查询短时间内多次执行，是不划算的，这个时候就需要缓存。并且缓存中是区分空格和大小写的，如果大小写不一致和空格不一致，也会认为是不同的SQL，不会利用到缓存。虽然不设置查询缓冲，有时可能带来性能上的损失，但有一些SQL语句需要实时地查询数据，或并不经常使用(可能一天就执行一两次)，这样就需要把缓冲关了。

 

query_cache_size = 0

分配查询缓存的大小。如果应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不过不要设置太大，因为维护它也需要不少开销，这会导致MySQL变慢。由于我使用了REDIS做为缓存，所以这里我直接关闭了MYSQL的缓存。

注：mysql8.0+已删除了这个参数。我猜官方也觉得这个并没有什么太大的作用，反而还拖累了稳定性。造成的问题比它解决问题要多的多， 弊大于利就直接砍掉了。

 

tmp_table_size = 128M

max_heap_table_size = 128M

规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下。

 

performance_schema_max_table_instances = 2048

这个参数是检测的表对象的最大数目。这台服务器可能会有最多表的情况，比如我的系统只有60个表，我就可以适当改下这个值为600或者400即可，会大大减少Mysql的内存占用哦。官方默认12500

 

table_definition_cache = 2048

这个参数是缓存frm文件。

 

max_connections = 512

最大连接数，也就是支持多少个客户端的同时连接数，并不是越大越好。

 

max_connect_errors = 1000

如果MySQL服务器连续接收到了来自于同一个主机的请求，而且这些连续的请求全部都没有成功的建立连接就被中断了，当这些连续的请求的累计值大于max_connect_errors的设定值时，MySQL服务器就会阻止这台主机后续的所有请求。如果是远程服务器可适当的调高这个值。

 

open_files_limit = 65535

文件最大的打开数。系统一般默认可打开65535个

 

innodb_file_per_table = 1

独立表空间开关，如果设置为0即使用共享表空间。

共享表空间：可以将表空间分成多个文件存放到各个磁盘上。所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

独立表空间：每个表都有自已独立的表空间。每个表的数据和索引都会存在自已的表空间中。可以实现单表在不同的数据库中移动。

 

innodb_buffer_pool_size = 6G

看到这个值的时候是不是有点小惊呀！没错，我还算设置得比较小的，因为我还需要分配给Redis使用的内存，因为我是16G内存，如果我只跑Mysql的情况下这个值设置在12G左右是比较合适的。差不多是总内存的75%。

innodb_buffer_pool_size对InnoDB的效率影响很大。因为InnoDB会把尽可能多的数据和索引缓存在缓冲区。

 

innodb_log_file_size = 2G

为InnoDB引擎设置合适的Redo log空间对于写敏感的工作负载来说是非常重要的。然而，这项工作是要做出权衡的。你配置的Redo空间越大，InnoDB就能更好的优化写操作；然而，增大Redo空间也意味着更长的恢复时间当出现崩溃或掉电等意外时。

在高写入负载尤其是数据集很大的时候，这个值非常重要，值越高性能越好。

 

innodb_log_buffer_size = 16M

默认的设置在中等强度的写入负载及短事物处理时，性能还可以。但是存在大量更新操作或者负载较大时，就要慢慢增加这个参数的值了。不过不要设置太大，会浪费内存。它每秒都会刷新一次，所以不用设置超过1s所需的内存空间，16M足够了。

 

innodb_flush_log_at_trx_commit = 2

设置为0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

设置为1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

设置为2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

设置为2兼顾效率与安全。设置为1最安全！！！

 

innodb_lock_wait_timeout = 50

默认参数:innodb_lock_wait_timeout设置锁等待的时间是50s，一旦数据库锁超过这个时间就会报错。

除非注明，网络人的文章均为原创，转载请以链接形式标明本文地址：https://www.55mx.com/post/65