mysql 配置文件在哪_MySQL优化之服务器参数调优

向右看齐 2022-10-25 14:07 204阅读 0赞

![b8c80d8d5d37409b316bce466588a5ac.png][]

点击上方蓝字关注我

之前我的文章里写了SQL语句优化，还写了如何使用sysbench对MySQL进行压测。本文我再来讲一讲如何通过调整MySQL参数提高MySQL服务器性能。需要注意的是，要想达到一个比较好的效果，需要不断的观察和调试，如果参数调节不当反而会影响服务器性能。

**｜调优方式**

--------------------

以Linux为例：

需要修改的文件是：my.cnf或mysqld.cnf，具体是哪一个配置文件，以及配置文件的路径在何处取决于你所用的Linux发行版以及MySQL的安装方式，需要灵活变通。

我在这里使用Centos进行演示，我的数据库是采用的yum安装的，要操作的配置文件是/etc/my.cnf

在修改了配置文件后，要想让其生效，需要重启MySQL服务。不同的MySQL安装方式以及不同的Linux发行版重启MySQL服务的方式不尽相同，下面列出两个常用的。

service mysqld restart

service mysql restart

**｜首选调节参数**

--------------------

在这里列出来的是常用的调节参数，这些参数的改动对数据库的性能影响较为明显。

**调整最大连接数**

最大连接数指的是能同时连接到服务器的客户端数量，对应的变量名称是max\_connections，默认值是151。MySQL允许的最大连接数上限是16384，此值不能盲目的调大，毕竟系统的CPU和内存硬件资源有限，盲目的调大反而可能降低服务器性能。

可以通过以下命令查看MySQL的实际最大连接数

show status like '%max_used_connections%';

![d9e2477205a4bbd7fc67d9e5da250a41.png][]

实际连接数是最大连接数的85%较为合适,在这里将最大连接数设置成了300。

**调整请求堆栈**

当MySQL的当前连接达到max\_connections所设定的值后，新来的请求将会被放到请求堆栈中。对应的变量名称是back\_log，默认值是50。一般将堆栈大小设置成最大连接数的1/3。

![87b03d955bb02d556bf94b21f44242db.png][]

**调整InnoDB并发线程数**

这个参数设置的是InnoDB并发线程数，对应的变量名称是innodb\_thread\_concurrency，默认是0(不限制)。并发线程数一般设置为CPU核心数的两倍。

**调整InnoDB缓存**

InnoDB使用该参数所设定的空间大小缓存数据和索引，对应的变量名称是innodb\_buffer\_pool\_size，默认是128M。可以把此参数调大，这样就能尽可能的保证InnnoDB能在内存中缓存更多的索引和数据，从而提高InnoDB的查询效率，此参数越大越好，但原则上不超过主机内存的80%，推荐设置成主机内存的70%～80%，我当前主机的内存是1G所以将其设置成了896M(需要算成128的整数倍,如果不是，MySQL会将其提高到离设定值最近的整数数值)。

![31cdfe59c7ecfd048a4e932d778f9f1d.png][]

**调整超时时间**

这个时间指的是MySQL在关闭一个非交互连接之前所等待的时间，如果你不希望存在过多长时间不用又没有关闭的链接，可以将此时间调小。对应的变量名称是wait-timeout，单位是秒，默认是8小时。连接长期不用又不销毁，浪费资源，所以这个参数可以调小一点，比如设置成2小时。

通过命令show processlist查看当前的连接,注意查看有没有太多长时间不用的连接。

![c5c2d49029cac5f20427c0189b9c30a9.png][]

![794c09965a6fe5b807e878161d0f7f37.png][]

**｜其他调节参数**

--------------------

上面我列出来了比较好调节并且对服务器性能影响较大的参数，接下来再说一说其他可能需要调节的参数，在这里也是只列出用的比较多的。

**调整MyISAM索引缓存**

这个参数设定的是缓存索引的空间的大小，设置大了可以增加索引的检索速度。变量名称是key\_buffer\_size。这个参数只针对MyISAM存储引擎生效。当然了，如果你用的是InnoDB存储引擎，其实这个值也有优化的必要，因为临时表使用的是MyISAM引擎。

可以通过以下命令查看当前的设定是否合理：

show status like "key_read%"

![f4fab68a71a823d85262d5aaee73fbf9.png][]

可以看到，一共进行了6次读索引的请求，其中有3次在缓存中没有找到，而是直接从硬盘中获取索引。

当发现不合理时，可以逐步调大此参数，从而进一步提高缓存命中率，此值能存下所有的MyISAM索引时最佳，原则上也是越大越好。和innodb\_buffer\_pool\_size不同的是，此缓存不会存储数据，只能存储索引。

![3c1dc843129ffcc917437cec3f7d7573.png][]

**调整批量插入数据缓存**

如果你经常进行批量插入数据操作，那可以调高批量插入数据的缓存。对应的变量名称是bulk\_insert\_buffer\_size。默认大小8M，如果需要调整，推荐16～64M。

![a1c0c1accaedbf3450afd6071feb5515.png][]

**调整binlog缓存**

binlog缓存每个session独享，作用是在事务过程中为binlog提供缓存。对应的变量是binlog\_cache\_size,默认大小是32k。因为是每个session独享，此参数不建议调的太大，调大可能会导致内存爆满。如果经常性的有大事务操作，可以把此缓存提高到2～4M。

**调整排序缓存**

当需要执行ORDER BY或GROUP BY操作时，MySQL会为其分配一个缓存，对应的变量名称是sort\_buffer\_size，默认大小是256K。将此缓存调大有助于提高ORDER BY或GROUP BY的执行速度。该缓存是每个session独享的，此参数不建议调的太大，调大可能会导致内存爆满。建议此缓存不超过2M。

**调整随机读缓存**

当需要执行随机读操作时，将会用到该缓存，对应的变量名称是read\_rnd\_buffer\_size，默认大小是256k。增加该缓存大小有助于提高随机读的速度。修改随机读缓存的大小可以提高ORDER BY或GROUP BY的速度。

关于此缓存的介绍，可以参考博客：https://blog.csdn.net/weixin\_33712881/article/details/92981048

**调整表连接缓存**

当进行多表关联时，MySQL会为其分配一个缓存，对应的变量名称是join\_buffer\_size，默认大小是256K。该缓存同样是session独享的。不建议调大，建议调整为2M。

**｜查询缓存问题**

--------------------

如果开启了MySQL查询缓存，MySQL会将查询结果存放在缓冲区中，今后对于同样的select语句(区分大小写、空格等),将直接从缓冲区中读取结果。听起来查询缓存好像是一个很不错的东西，然而MySQL 8 已经取消了对查询缓存的支持。那这是为什么呢？

主要基于以下原因：

【1】开启查询缓存后每一次读、写操作都要付出额外的工作。

【2】对于一个表，如果有数据被写入，整个表的查询缓存全部失效。

【3】对于多表连接，其中一个表数据被更改了，查询缓存全部失效。

推荐的缓存使用方式：

【1】在数据库层面关闭查询缓存。

【2】可以在应用程序层整合Redis做查询缓存。

关闭MySQL层的查询缓存(MySQL 8以下才需要关闭)：

query_cache_type=0query_cache_size=0

本篇文章就写到这里了，后面将会介绍数据库集群、读写分离、水平/垂直切分等相关知识，敬请期待！

##  ##

非常感谢你能看到这里。如果你感觉这篇文章确实对你有所帮助，不妨点个在看，让更多人能够看到这篇文章。

![c091b8b94b04b6870fec17a0e8081084.png][]

[b8c80d8d5d37409b316bce466588a5ac.png]: /images/20221024/8a6346e45a9244e6a69467b0f65bc627.png
[d9e2477205a4bbd7fc67d9e5da250a41.png]: /images/20221024/f1aa4712891640ecade6f77c46758ade.png
[87b03d955bb02d556bf94b21f44242db.png]: /images/20221024/f35489c6db504849888db7957876b5ae.png
[31cdfe59c7ecfd048a4e932d778f9f1d.png]: /images/20221024/1cf1d5a6847046a7a2ef80eb30747cc3.png
[c5c2d49029cac5f20427c0189b9c30a9.png]: /images/20221024/75cd4eb330bf4767a601cf4153f0edce.png
[794c09965a6fe5b807e878161d0f7f37.png]: /images/20221024/905a18d213bc4b7a9094f4d46325d3ed.png
[f4fab68a71a823d85262d5aaee73fbf9.png]: /images/20221024/bf83803480e04be8ab191fb486c89430.png
[3c1dc843129ffcc917437cec3f7d7573.png]: /images/20221024/6709609a40ae408fb33bcf72b0d80993.png
[a1c0c1accaedbf3450afd6071feb5515.png]: /images/20221024/59d1b7a1dbfd46a2b81ce387ccf7d5d4.png
[c091b8b94b04b6870fec17a0e8081084.png]: /images/20221024/a87fdad61f6246fe90bf13e4a845ff8a.png