mysql数据库参数配置_MySQL数据库参数配置

最新推荐文章于 2024-05-10 11:24:36 发布

weixin_39898550

最新推荐文章于 2024-05-10 11:24:36 发布

阅读量693

点赞数

文章标签： mysql数据库参数配置

本文链接：https://blog.csdn.net/weixin_39898550/article/details/113324944

版权

配置参数的作用域

全局参数 set global 参数名=参数值或 set @@global.参数名:=参数值

会话参数 set [session] 参数名=参数值或 set @@session.参数名:=参数值

内存配置相关参数

MySQL对内存的使用可以分为两类来看：一类是无法通过配置参数所控制的，如MySQL运行、查询、解析、内部管理所消耗的内存；一类是可以通过配置参数所控制的，如各类的缓冲池所使用的内存；

要对MySQL内存进行配置要确定以下问题：

确定可以使用的内存上限。给MySQL运行配置的内存是不能超过服务器本身的物理内存的。

确定MySQL每个连接使用的内存。MySQL对于一些缓存是对每个连接单独分配的，所以连接数越大，对内存占用就越多。这类参数需要注意的有以下几个：

sort_buffer_size 排序缓冲区的尺寸。这个参数定义了MySQL每个线程排序缓冲区的大小。MySQL并不是在初始化的时候就为缓冲区分配内存，而是在有查询进行排序操作时分配内存，然而一旦查询中需要排序，MySQL会一次性分配这个参数配置大小的全部内存，而不管该排序需不需要内存。

join_buffer_size 连接缓冲区的尺寸。这个参数定义了MySQL每个线程所使用的连接缓冲区的大小。需要注意的是，一个查询关联了多张表，会为每个关联分配连接缓冲，每个查询可能会有多个连接缓冲，所以这个参数不会设置的太大。

read_buffer_size 定义了对MyISAM表进行全表扫描时所分配的读缓冲池的大小。MySQL只会在有查询时才会对该缓冲分配内存，同样会一次性分配这个参数配置大小的全部内存。这个参数大小是4K的倍数。

read_rnd_buffer_size 索引缓冲区的大小。只会在有查询时才会分配，而且只会分配所需要的大小，而不是参数指定的大小。

确定为操作系统保留多少内存。强烈建议在生产环境中单独为MySQL搭建服务器。

为缓存池分配内存。主要有以下两个：

Innodb_buffer_pool_size 这个参数定义了Innodb所使用的缓冲池的大小，这个对innodb存储引擎的性能是非常重要的，innodb存储引擎不仅要缓存索引，同时还要缓存数据，innodb存储引擎的性能严重依赖缓冲池的大小。MySQL手册中建议该参数设置的大小应该为服务器内存的75%以上，但是实际上不能完全按照75%来配置，还要考虑其他因素。

key_buffer_size 用于MyISAM存储引擎，只会缓存索引，数据则是依赖操作系统的缓存。

I/O相关的配置参数

这部分参数决定了MySQL如何同步缓存池中的数据到磁盘上以及持久化的保存，这对性能影响非常大，如果要保证数据修改后立即写入到磁盘上，是一种很消耗资源的操作。 I/O操作的成本是很高的，我们要在性能和安全性上做一些平衡，有时候我们为了提升性能不得不牺牲一些安全性。I/O配置总是和我们选择的存储引擎相关的，所以要根据存储引擎的选择来进行I/O配置。

Inoodb I/O的相关配置。事务日志从缓存刷新到磁盘需要两步，先从MySQL事务日志的缓冲区刷新到操作系统的cache，这实际上还是在内存中；然后从cache刷新到磁盘上才算是完成了事务的持久化

Innodb_log_file_size 单个事务日志的大小，Innodb_log_file_in_group 事务日志文件个数，事务日志总大小就是Innodb_log_file_size*Innodb_log_file_in_group。Innodb是事务型引擎，为了减少提交事务时产生的I/O开销，在事务提交时先写入事务日志，而不是每次都把修改的数据刷新到数据文件中，这样可以提高I/O的性能。如果业务繁忙，建议把事务日志设置的大一些。

事务日志并不是每次提交都直接写入事务日志文件中，而是先写入事务日志的缓冲区，然后刷新到磁盘中。Inoodb_log_buffer_size 控制日志缓冲区大小，通常不需要把这个参数设置很大，一般情况下1秒钟就会进行事务日志的刷新，所以缓冲区能够保存1秒钟的事务就可以了，当然设置大点也没关系，32M-128M已经足够用了。

相对于日志缓冲区大小，刷新日志到磁盘的频繁程度对性能影响更大一些。Innodb_flush_log_at_trx_commit参数决定刷新事务缓冲区的频繁程度。这个参数有三个可选的值，0：每秒进行一次log写入cache，并刷新日志到磁盘，在事务提交时不做任何事情，在MySQL崩溃时会丢失至少一秒钟事务；1(MySQL默认该参数设置为1)：在每次事务提交执行log数据写入cache，并刷新到磁盘文件并持久化存储，这是最安全的一个设置，该设置不会丢失任何已经提交的事务，除非磁盘或者系统做了伪刷新，但是设置为1也是性能最差的；2(建议设置为2)：在每次事务提交执行log数据写入cache，每秒执行一次刷新到磁盘文件持久化操作。0和2的区别在于，即使进程崩溃了，设置为2不会丢失事务，除非服务器宕机会丢失至少一秒钟事务。

Innodb_flush_method=O_DIRECT 刷新方式，决定了Innodb数据文件和日志文件如何与文件进行交互，不仅会影响Innodb写数据，也影响读数据。

Inoodb_file_pool_table=1 独立表空间

Innodb_doublewrite=1 双写缓存

MyISAM I/O的相关配置

delay_key_write

OFF:每次写操作后刷新键缓冲中的脏块到磁盘;

ON:只对在建表时指定了这个参数选项的表进行延迟刷新；

ALL：对所有mysiam表都使用延迟键写入

安全相关配置参数

expire_logs_days指定自动清理binlog的天数，清理二进制日志，防止二进制日志占用太多空间。

max_allowed_packet控制MySQL可以接受的包的大小。

skip_name_resolves禁用DNS查找