参数设置的三种方式

set [session]

set [session] 参数名=参数值，设置当前会话（连接）参数，范围最小，当这个连接被关闭了之后，会话会自动失效。其中，session关键字可以省略，默认就是session级别。

这个设置的优先级比set global还高。

set global

set global 参数名=参数值，设置全局参数，只要mysql没有关闭重启，就一直生效，但是需要注意的是：部分参数需要并不会立即生效，需要重新建立连接才会生效。

这个命令设置的优先级会比配置文件里的配置高，如果配置文件里max_connections = 100，但是执行了SET GLOBAL max_connections = 200;那么，实际使用的会被改成200.

设置应用配置文件

对于一些关键性的参数，我们会在配置文件里配置，window和linux虽然文件名称不一样，但是配置内容是一样的

  1. Window存放到将my.ini应用程序根目录
  2. Linux保存在/etc/my.cnf

配置内容如下所示，因为我们配置一般都是针对服务端配置，所以要写在[mysqld]底下：

[mysqld]
port = 3306
# 最大连接数，如果没有书写的情况下，默认是151
max_connections = 100

connection连接参数

因为数据库连接是客户端和服务端的通信管道，所以设置的是否合理，对我们整应用程序就至关重要了

max_connections

代表mysql数据允许的最大连接数。mysql连接有两种常见的状态：

  1. sleep，代表出于闲置，等待的状态
  2. query，代表正在工作状态

着两个状态的总数量和不能超过max_connections设置的值的，否则会出现"ERROR 1040:Too many connetcions"的错误提示。可以用以下sql查看连接状态：

SHOW status LIKE 'Threads%';

l6rpxbtq.png

  1. Threads_connected，代表当前已经有多少连接，包括sleep和query状态的连接
  2. Threads_created，代表从服务启动，到现在为止，历史总共创建过多少个数据库连接，包括打开的，正在工作的，和已经销毁的等，这个数量不知道为啥比想象的小
  3. Threads_running，代表当前有几个连接出于工作的状态，这个数值其实就是我们系统的并发数了
  4. Threads_cached，代表共缓存过多少连接，如果我们设置了thread_cache_size，当客户端断开之后，连接不会销毁，给其它客户端复用，因为频繁的创建销毁太消耗资源，而是缓存起来，不够如果数量超出设置的缓存上限后，还是会销毁。

那么这个连接数设置多大才是最合适的呢，可以用以下命令来查看：

SHOW status LIKE 'Threads%';

l6rqsxbw.png

  1. Max_used_connections，历史出现的最大连接数，通常这个数值上涨20%左右，就可以作为最大连接数，刚上线的时候，不知道最大的连接数，可先设置的大一些，比如3000，或者5000等，等后续运行一段时间后，在调整为合适的值。但是如果有秒杀等活动的时候，要调整的高一些。
  2. Max_used_connections_time，出现这个最大连接数的时间

back_log

设置保存多少数据库请求到堆栈（缓冲区）中。也就是说，如果mysql的连接数达到max_connections时，新的请求不会被直接拒绝，而是存在缓存中，以等待某一连接释放资源后，就从堆栈中把最顶层的请求拿出来执行。只有缓存的请求，超过了back_log设置的数量之后，将拒绝连接，报错："unauthenticated user | XXX .XXX.XXX.XXX | NULL | Connect | NULL | login | NULL 的待连接进程."

-- 默认值是80，可以适当的调高，比如3000
SHOW variables LIKE 'back_log';

连接时间

wait_timeout和interactive_timeout参数，这两个连接都是设置数据库连接超过一段时间后，连接自动关闭，默认的28800秒是8个小时，线上可以调整3600。

  1. wait_timeout，代表是交互式连接，也就是mysql客户端连接数据库的都叫交互式连接，这就是很多客户端需要心跳包重新连接
  2. interactive_timeout，代表是非交互式连接，通过jdbc等连接的都是非交互式连接

以下sql可以查看设置的连接时间

SHOW variables LIKE 'wait_timeout';
SHOW variables LIKE 'interactive_timeout';

我们还可可以通过以下sql，查看连接的时间

SHOW processlist;

l6rrv9pn.png
其中time列连接的时间，当这个值超过设置的连接时间值的时候，就会被自动的关闭。不过现在的应用一般都有连接池，连接池会对连接进行有效性检查，会自动重新连接，防止自动过期。

查询缓存相关

使用查询缓存，MySQL将查询结果存放在缓冲区中，今后对 同样的select语句（区分大小写） ，将直接从缓冲区中读取结果。

have_query_cache

若要查看MySQL服务器上的查询缓存是否已经打开，要在MySQL命令行界面执行以下命令，如果是YES表示启用查询缓存：

SHOW VARIABLES LIKE 'have_query_cache';

l7q0jfip.png

若要开启缓存，只需要配置以下几个参数即可：

query_cache_type = 1    
query_cache_size = 128MB    
query_cache_limit = 1M

query_cache_size

表是缓存的大小，需要注意的是，最小缓存单位是1024byte，所以最好设置1024的整数倍数，如果不是将会被四舍五入。可以通过以下语句查看缓存的大小

SHOW variables LIKE '%query_cache_size%';

如果要修改的话，可以执行

-- 单位是字节
SET GLOBAL query_cache_size = 16777216;

那如何知道，我们应该设置的大小呢，并不是越大越好，query_cache_size较合适的值是在100MB和200MB之间，我们对一个中等流量的Magento站点使用了128 MB的配置值，它工作得非常好。将这个值设置为0可以关闭查询缓存。
我们还可以通过以下sql来查看缓存状态来综合判断，这个需要不断的进行跟踪：

SHOW status LIKE 'Qcache%';

l6s05sp5.png

  1. Qcache_free_memory，query_cache中目前剩余内存的大小，通过这个参数，我们可较为准确的观察当前系统中query cached内存大小是否足够，看下是缓存大小设置的是否合适
  2. Qcache_lowmem_prunes，多少条Query因为内存不足而被清除出Query Cache，通过Qcache_lowmem_prunes和Qcache_free_memory相互结合，能够更清楚的了解到我们系统中Quey Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好是长时间来看，如果这个数字在不断增长，就表示可能碎片化非常严重，或者内存很少。
  3. Qcache_total_blocks，当前Query Cache中block的数量，每个sql语句都是以bloc块为单位进行保存，如果块数越多，这个数量就越大
  4. Qcache_free_blocks，缓存中相邻内存块的个数。如果该值显示过大，则说明Query Cache中的内存碎片较多了，这部分内存无法被有效利用。如果碎片比较多的话，可以用flush query cache来清理。但是需要注意的是，mysql的块默认是4kb为单位，并且这个是最基础的单位，无法拆分，如果数据大小是2kb的话，那么就空闲出了2kb，也会导致内存浪费。
  5. Qcache_hits，表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询能缓存的效果。数字越大缓存效果越理想。
  6. Qcache_inserts，表示多少次未命中而插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert带查询缓存中。这样的情况次数越多，表示查询缓存应用到的比较少，效果也就不理想。
  7. Qcache_queries_in_cache，当前缓存中缓存的查询数量
  8. Qcache_not_cached，未进入查询缓存的select个数

query_cache_limit

超出此大小的查询将不被缓存，用于限制单个sql语句，所能被缓存的数据大小，如果查询结果的体积大于这个值，将不会被缓存。这个配置值可以通过找到最大的SELECT查询结果的体积而推测出来。，我们的缓存尽可能的去保存数据量小的，访问频发的sql

SHOW variables LIKE 'query_cache_limit';

query_cache_type

缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置，必须设置为数字，可选值以及说明如下：

  1. 0：OFF相当于禁用了
  2. 1：ON将缓存所有结果，除非你的select语句使用了SQL NO CACHE禁用了查询缓存
  3. 2：DENAND则只缓存select语句中通过SQL CACHE指定需要缓存的查询。

mysql5.7中，默认是禁用qc的，要启用的话，需要在配置文件中配置，不可以通过set gloabal命令来设置！！！。

-- 结果是off
SHOW variables LIKE 'query_cache_type';

query_cache_min_res_unit

缓存块的最小大小，query_cache_min_res_unit的配置是一柄双刃剑，默认是4KB,设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。

排序优化

sort_buffer_size

为每个需要排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作，如果是sql怎么调优化都没效果的话，可以试着调整这个缓存的大小。

  1. sort_buffer_size是一个connection级的参数，在每个connection(session)第一次需要使用这个buffer的时候，一次性分配设置的内存。
  2. sort_buffer_size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗508*sort_buffer_size(2M)=1G，很耗内存。

innodb相关的

innodb_buffer_pool_size

缓存池的大小，对innodb表读写的时候，会利用这个缓存池，对数据进行缓存，从而使我们数据的输入和输出的效率提高。虽然这个也是在内存中占用空间，但是和前面的说的查询缓存qc不一样，查询缓存服务于所有的查询语句，和底层的数据库引擎无关，这个innodb_buffer_pool_size是专门用于优化innodb的io。

-- 默认是134217723=128M(默认值)
SHOW GLOBAL variables LIKE "innodb_buffer_pool_size";

但是，这个128M是否够用，可以参考以下三个指标：

-- innodb已使用的缓存页数量，每页16k
SHOW GLOBAL status LIKE "Innodb_buffer_pool_pages_data";
-- 总共为innodb分配的中页数，如果使用率高的话，可以增加innodb_buffer_pool_size
SHOW GLOBAL status LIKE "Innodb_buffer_pool_pages_total";
-- innodb缓存的每个页的长度是多少
SHOW GLOBAL status LIKE "innodb_page_size";

如果想要调整innodb_buffer_pool_size的大小，可以

set GLOBAL innodb_buffer_pool_size = xxx;

但是需要注意的是，这个会使内存使用大大的增加，io读写效率也会大大的提高。

innodb_flush_log_at_trx_commit

事务提交相关的，在事务控制中，存在"事务区"来保证事务完整性，当开启事务后，首先先把数据提交到事务区中，在事务提交以后，这些事务区的数据会写入到硬盘上，同时事务操作日志(1og)也需要向硬盘中写入。这个参数就是用来控制何时写日志数据的。

它有三个值：

  1. 0：log_buffer，将每秒一次地写入1og file中，并且log_file的f1ush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。这种模式io读写效率最高，但是如果此刻进程崩溃，那么上一秒的所有的数据将会丢失，不太安全，不推荐使用。
  2. 1：每次事务提交时mySQL都会把log buffer的数据写入log_file,并且flush(刷到磁盘)中去，该模式为系统默认。这种模式虽然有很好的一致性，但是读写效率很差，因为每次都要读写io。只有主机崩溃的时候，才会丢失一个语句或者一个事务。强事务性系统，比如银行，使用这个。
  3. 2：每次事务提交时mySQL都会把log buffer的数据写入log_file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySOL会每秒执行一次flush(刷到磁盘)操作。这个比0会安全一点，只有操作系统崩溃，或者断电的时候，才会丢失上一秒的数据。应用在在互联网评论系统这种，就算丢失了，也影响不大的的系统。

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要两秒，设置为0时只需要一秒，设置为1时，则需要229秒。因此，MySOL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提高速度。

innodb_doublewrite

安全相关的，双写操作，一个数据写两份。因为在日常开发的时候，innodb会把数据存在磁盘上，硬盘有时候并不可靠，比如有坏道。为了杜绝这些情况，就要将数据写两份，存储在不同的地方，这样其中的一个发生了问题，另外一个就会弥补上。这个值默认是开启的：

SHOW GLOBAL variables LIKE "innodb_doublewrite";

这个这默认值就很好，了解就好。

innodb_file_per_table

设置独立的表空间文件，可以看 innodb存储引擎 。可以设置为1，为每个表设置为独立的表文件。

SHOW GLOBAL variables LIKE "innodb_file_per_table";

innodb_thread_concurrency

代表innodb线程的并发数，默认是0表示不限制并发数量。若要设置则与服务器的cPU核心数相同或是CPU的核心数的2倍。

SHOW GLOBAL variables LIKE "innodb_thread_concurrency";

并发数量并不是越多越好，因为同时执行的数量，只能是cpu核心的数量。这个最好在配置文件中设置，因为是相对底层的设置。

最后修改：2023 年 12 月 30 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏