本文详细介绍了MySQL中的系统变量，包括排序、JOIN连接、查询缓存和Innodb缓存相关的变量，如sort_buffer_size、join_buffer_size、optimizer_switch、query_cache_size等。内容涵盖变量的查看、修改、含义及调优方法，旨在帮助优化数据库性能。
目录
3、innodb_additional_mem_pool_size
一、系统变量的查看和修改
以 join_buffer_size 为例
SHOW VARIABLES LIKE '%join_buffer_size%';
SELECT @@join_buffer_size; //@@为系统变量，@为用户变量，默认指session级别
SET SESSION join_buffer_size = 131072; //当前会话中改变值，会话结束后恢复
SET @@session.join_buffer_size = 262144;
SET GLOBAL join_buffer_size = 262144; //改变值一直到重启
SET @@global.join_buffer_size = 262144;
//在my.conf文件中配置，永久改变值二、系统状态的查看
SHOW GLOBAL STATUS;
SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select';
SHOW GLOBAL STATUS WHERE Variable_name in ('Com_select','Com_insert','Com_delete','Com_update');
三、系统变量的含义
（一）排序相关
1、sort_buffer_size
There is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.
sort buffer 是系统中对数据进行排序的时候用到的Buffer。
sort buffer 同样是针对单个线程的，所以当多个线程同时进行排序的时候，系统中就会出现多个sort buffer。
我们一般可以通过增大 sort buffer 的大小来提高order by或者group by的处理性能。
系统默认大小为2MB，最大限制和 join buffer 一样。
（二）JOIN 连接相关
1、join_buffer_size
Setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation (…). Experiment to find the best value for your workload.
当 join 是 ALL, index, rang 或者 Index_merge 的时候使用的 buffer，系统默认是128KB或256KB。
实际上，参与 join 连接的每一个表都需要一个join buffer，所以在 join 出现的时候，至少是2个。
join buffer在mysql5.1.23版本之前最大为4G，但是从5.1.23版本开始，再出了windows之外的64位平台上可以超出4GB的限制。
2、optimizer_switch
It is a server variable that one can use to enable/disable specific optimizations.
Syntax
To set or unset the various optimizations, use the following syntax:
SET [GLOBAL|SESSION] optimizer_switch='cmd[,cmd]...';
The cmd takes the following format:
| Syntax | Description |
|---|---|
| default | Reset all optimizations to their default values. |
| optimization_name=default | Set the specified optimization to its default value. |
| optimization_name=on | Enable the specified optimization. |
| optimization_name=off | Disable the specified optimization. |
There is no need to list all flags - only those that are specified in the command will be affected.
Available Flags
Below is a list of all optimizer_switch flags available in MariaDB:
- ↑ replaced split_grouping_derived, introduce
Related JOIN algorithms.
join_cache_incremental 增量缓存算法
join_cache_hashed 哈希算法
join_cache_bka BKA算法
默认集成了 NLJ算法 和 BNL算法。
To use BKA/BKAH join algorithms for InnoDB/MyISAM, one must set the optimizer switch mrr to 'on'. When using these algorithms for InnoDB/MyISAM the overall performance of the join operations can be dramatically improved if the optimizer switch mrr_sort_keys is set 'on'.
参考：mysql 5.7中的MRR和BKA算法 https://www.cnblogs.com/longy2012/articles/12770511.html
MySQL 的 MRR 到底是什么？https://zhuanlan.zhihu.com/p/110154066
支持Batched Key Access（BKA）索引优化 https://book.51cto.com/art/201701/529466.htm
3、join_cache_level
It is used to manage usage of join algorithms.
Currently 4 different types of block-based join algorithms are supported. For a particular join operation each of them can be employed with a regular (flat) join buffer or with an incremental join buffer.
Three optimizer switches - join_cache_incremental, join_cache_hashed, join_cache_bka – and the system variable join_cache_level control which of the 8 variants of the block-based algorithms will be used for join operations.
If join_cache_bka is off then BKA and BKAH join algorithms are not allowed. If join_cache_hashed is off then BNLH and BKAH join algorithms are not allowed. If join_cache_incremental is off then no incremental variants of the block-based join algorithms are allowed.
By default the switches join_cache_incremental, join_cache_hashed, join_cache_bka are set to 'on'. However it does not mean that by default any of block-based join algorithms is allowed to be used. All of them are allowed only if the system variable join_cache_level is set to 8. This variable can take an integer value in the interval from 0 to 8.
If the value is set to 8 no block-based algorithm can be used for a join operation. The values from 1 to 8 correspond to the following variants of block-based join algorithms :
- 1 – Flat BNL
- 2 – Incremental BNL
- 3 – Flat BNLH
- 4 – Incremental BNLH
- 5 – Flat BKA
- 6 – Incremental BKA
- 7 – Flat BKAH
- 8 – Incremental BKAH
If the value of join_cache_level is set to N, any of block-based algorithms with the level greater than N is disallowed.
So if join_cache_level is set to 5, no usage of BKAH is allowed and usage of incremental BKA is not allowed either while usage of all remaining variants are controlled by the settings of the optimizer switches join_cache_incremental, join_cache_hashed, join_cache_bka.
By default join_cache_level is set to 2. In other words only usage of flat or incremental BNL is allowed.
By default block-based algorithms can be used only for regular (inner) join operations. To allow them for outer join operations (left outer joins and right outer joins) the optimizer switch outer_join_with_cache has to be set to 'on'. Setting the optimizer switch semijoin_with_cache to 'on' allows using these algorithms for semi-join operations.
Currently, only incremental variants of the block-based join algorithms can be used for nested outer joins and nested semi-joins.
参考：https://mariadb.com/kb/en/block-based-join-algorithms/
4、join_buffer_space_limit
Dynamic Size of Join Buffers
The maximum size of join buffers used by block-based algorithms is controlled by setting the join_buffer_size system variable. This value must be large enough in order for the join buffer employed for a join operation to contain all relevant fields for at least one joined record.
MariaDB 5.3 introduced the system variable join_buffer_space_limit that limits the total memory used for join buffers in a query.
To optimize the usage of the join buffers within the limit set by join_buffer_space_limit, one should use the optimizer switch optimize_join_buffer_size=on. When this flag is set to 'off' (default until MariaDB 10.4.2), the size of the used join buffer is taken directly from the join_buffer_size system variable. When this flag is set to 'on' (default from MariaDB 10.4.3) then the size of the buffer depends on the estimated number of rows in the partial join whose records are to be stored in the buffer.
（三）查询缓存相关
1、have_query_cache
It will show whether the query cache is available.
SHOW VARIABLES LIKE 'have_query_cache';If this is set to NO, you cannot enable the query cache unless you rebuild or reinstall a version of MariaDB with the cache available.
2、query_cache_size
It will show the size of query cache.
SET GLOBAL query_cache_size = 134217728;3.query_cache_limit
It will show the limit size of once query cache.
SET GLOBAL query_cache_limit = 8388608;4、query_cache_min_res_unit
It will show the minimum block size of query cache.
SET GLOBAL query_cache_min_res_unit= 4096;5、query_cache_strip_comments
If set to
1(0is default), the server will strip any comments from the query before searching to see if it exists in the query cache. Multiple space, line feeds, tab and other white space characters will also be removed.
SET @@query_cache_strip_comments= 1;6、query_cache_type
If the
query_cache_typesystem variable （OFF, ON, DEMAND）is set to1, orON, all queries fitting the size constraints will be stored in the cache unless they contain aSQL_NO_CACHEclause, or are of a nature that caching makes no sense, for example making use of a function that returns the current time.
If any of the following functions are present in a query, it will not be cached.
7、查看查询缓存状态
SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1158 | 尚且空闲的块
| Qcache_free_memory | 3760784 | 尚且空闲的空间
| Qcache_hits | 31943398 | 缓存命中次数
| Qcache_inserts | 42998029 | 写入缓存数据的次数
| Qcache_lowmem_prunes | 34695322 | 修剪释放的内存次数
| Qcache_not_cached | 652482 | 没被缓存的查询次数
| Qcache_queries_in_cache | 4628 | 缓存的查询数
| Qcache_total_blocks | 11123 | 查询缓存总块数
+-------------------------+----------+缓存碎片整理：
如果空闲空间很多，但是总是被修剪（修剪释放次数也很多），考虑可能存在大量碎片而导致无法缓存。
FLUSH QUERY CACHE 将对查询缓存进行碎片整理，而不会删除任何查询。
缓存命中率和内存使用率：
query_cache_min_res_unit 估计值：(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
查询缓存命中率 ≈ Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%
查询缓存内存使用率 ≈ (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
QPS每秒查询率 ≈ Com_select / Uptime* 100%
TPS每秒事务数：
SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select';
8、清空并禁用查询缓存
清空查询缓存中的所有结果，请使用RESET QUERY CACHE或FLUSH TABLES。
将 query_cache_type 或 query_cache_size 设置为0，将禁用查询缓存。但是要释放最多的资源，当您禁用缓存时将两者都设置为0。
9、其它变量
query_cache_wlock_invalidate：设置为ON，每个写锁都会使表查询缓存无效；
SELECT SQL_NO_CACHE .... FROM (SELECT SQL_CACHE ...) AS temp_table
SELECT SQL_CACHE .... FROM (SELECT SQL_NO_CACHE ...) AS temp_table （四）Innodb 缓存相关
1、Innodb_buffer_pool_size
假设是一台单独给 MySQL 使用的主机，物理内存总大小为 8G，MySQL 最大连接数为 500，同时还使用 了 MyISAM 存储引擎，这时候我们的整体内存该如何分配呢？
内存分配为如下几大部分：
a) 系统使用，假设预留 800M；
b) 线程独享，最大500个线程（Nginx配置），需要内存约 2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB)，组成大概如下:
sort_buffer_size：1MB
join_buffer_size：1MB
read_buffer_size：1MB
read_rnd_buffer_size：512KB
thread_statck：512KB
c) MyISAM Key Cache，假设大概为 1.5GB；
d) Innodb Buffer Pool 最大可用量：8GB - 800MB - 2GB - 1.5GB = 3.7GB；
这里仅仅列出了一些使用内存较大的地方，如果进一步细化，很可能可用内存会更少。
当系统上线之后，可以通过 Innodb 存储引擎提供给我们的关于 Buffer Pool 的实时状态信息作出进一步分析，来确定系统中 Innodb 的 Buffer Pool 使用情况是否正常高效：
show status like 'Innodb_buffer_pool_%';
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_pages_data | 70 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 1978 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 2048 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 329 |
| Innodb_buffer_pool_reads | 19 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
+-----------------------------------+-------+从上面的值我们可以看出总共 2048 pages，其中 1978 pages 是 Free 空闲状态的，仅仅只有 70 个 page 有数据，read 请求 329 次，其中有 19 次所请求的数据在 buffer pool 中没有，也就是说有 19 次是通过读取物理磁盘来读取数据的，所以很容易也就得出了 Innodb Buffer Pool 的 Read 命中率大概在为：(329 - 19) / 329 * 100% = 94.22%。
当然，通过上面的数据，我们还可以分析出 write 命中率，可以得到发生了多少次read_ahead_rnd，多少次 read_ahead_seq，发生过多少次 latch，多少次因为 Buffer 空间大小不足而产生 wait_free 等等。
单从这里的数据来看，我们设置的 Buffer Pool 过大，仅仅使用 70 / 2048 * 100% = 3.4%。
2、innodb_log_buffer_size
这个参数就是用来设置 Innodb 的 Log Buffer 大小的，系统默认值为 1MB。Log Buffer 的主要作用就是缓冲 Log 数据，提高写 Log 的 IO 性能。一般来说，如果你的系统不是写负载非常高且以大事务居多的话，8MB 以内的大小就完全足够了。
我们也可以通过系统状态参数提供的性能统计数据来分析 Log 的使用情况：
show status like 'innodb_log%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 6 |
| Innodb_log_writes | 2 |
+---------------------------+-------+当然，如果完全从 Log Buffer 本身来说，自然是大一些会减少更多的磁盘 IO。但是由于 Log 本身是为了保护数据安全而产生的，而 Log 从 Buffer 到磁盘的刷新频率和控制数据安全一致的事务直接相关，并且也有相关参数来控制（innodb_flush_log_at_trx_commit），所以关于 Log 相关的更详细的实现机制和优化在后面的“事务优化”中再做更详细的分析，这里就不展开了。
3、innodb_additional_mem_pool_size
所设置的是用于存放 Innodb 的字典信息和其他一些内部结构所需要的内存空间。所以我们的 Innodb 表越多，所需要的空间自然也就越大，系统默认值仅有 1MB。当然，如果 Innodb 实际运行过程中出现了实际需要的内存比设置值更大的时候，Innodb 也会继续通过 OS 来申请内存空间，并且会在 MySQL 的错误日志中记录一条相应的警告信息让我们知晓。
从我个人的经验来看，一个常规的几百个 Innodb 表的 MySQL，如果不是每个表都是上百个字段的话，20MB 内存已经足够了。当然，如果你有足够多的内存，完全可以继续增大这个值的设置。实际上，innodb_additional_mem_pool_size参数对系统整体性能并无太大的影响，所以只要能存放需要的数据即可，设置超过实际所需的内存并没有太大意义，只是浪费内存而已。
四、系统变量调优
1996
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
