前言
MySQL数据库包含的系统变量有好几百个,想要全部熟记于心,大概没这个必要,只有记住一些常用的即可。本篇主要把一些常见的系统变量进行分类处理,分类有助于记忆。
系统变量
变量的作用范围可以分为global
级别和session
级别。
global
级别:服务级别的设定,对整个服务生效,但是已经连接session
不生效,重新连接才生效。session
级别:仅对当前会话生效,其他session
和新建session
不受影响,session
结束值即销毁。
要查看正在运行的服务器使用的当前值,可以使用类似的语句。
show [session] variables like 'sort_buffer_size'
如果想查看全局设置,只需要加上global
show global variables like 'sort_buffer_size'
想修改系统变量,需要使用类似语句
set [session] sort_buffer_size = 262144
想修改全局设置,也要加上global
。但是有的变量不支持这样的动态修改方式,需要在配置文件中修改,linux系统目录一般是/etc/my.cnf
,修改后需要重启数据库。
通用部分
datadir
:查看MySQL数据存储路径mysql> show variables like 'datadir'; +---------------+---------------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------------+ | datadir | C:\ProgramData\MySQL\MySQL Server 5.6\Data\ | +---------------+---------------------------------------------+ 1 row in set (0.00 sec)
socket
:在Unix平台上,此变量是用于本地客户端连接的socket文件的名称;在Windows上,此变量是用于本地客户端连接的命名管道的名称。默认值为MySQL(不区分大小写)。mysql> show variables like 'socket'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | socket | MySQL | +---------------+-------+ 1 row in set (0.00 sec)
pid_file
:服务器在其中写入其进程ID的文件的路径名。mysql> show variables like 'pid_file'; +---------------+---------------------------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------------------------+ | pid_file | C:\ProgramData\MySQL\MySQL Server 5.6\Data\sicimike.pid | +---------------+---------------------------------------------------------+ 1 row in set (0.00 sec)
port
:端口号mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.00 sec)
default_storage_engine
:默认存储引擎mysql> show variables like 'default_storage_engine'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.00 sec)
字符集部分
character_set_client
:来自客户端的语句的字符集。当客户端连接到服务器时,使用客户端请求的字符集设置此变量的会话值character_set_connection
:mysql处理客户端发来的信息时,会把这些数据转换成连接的字符集格式character_set_results
:mysql发送给客户端的结果集所用的字符集,包括结果集、列名、错误信息character_set_server
:mysql服务默认字符集。character_set_database
:默认数据库使用的字符集。每当默认数据库更改时,服务器都会设置此变量。如果没有默认数据库,则该变量的值与character_set_server
相同。
连接部分
max_connections
:允许的最大同时连接数。mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec)
max_user_connections
:限制每个用户的连接个数,值0(默认值)表示“无限制”。mysql> show variables like 'max_user_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | max_user_connections | 0 | +----------------------+-------+ 1 row in set (0.00 sec)
back_log
:mysql能够暂存的连接数量阈值,当mysql服务在一个很短时间内得到非常多的连接请求时,就会起作用。如果mysql的连接数量达到max_connections
时,新的请求会被存储在堆栈中,以等待某一个连接释放资源,如果等待连接的数量超过back_log
值,则不再暂存新的连接。默认值是根据50 + (max_connections / 5)
算出来的,上限是900。mysql> show variables like 'back_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | back_log | 80 | +---------------+-------+ 1 row in set (0.00 sec)
wait_timeout
:服务在关闭非交互式连接之前等待活动的秒数。mysql> show variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.00 sec)
interactive_timeout
:服务在关闭交互式连接之前等待活动的秒数。mysql> show variables like 'interactive_timeout'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | interactive_timeout | 28800 | +---------------------+-------+ 1 row in set (0.00 sec)
至于什么是交互式连接,什么是非交互式连接?官方文档 interactive_timeout 只给出了一句话
An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect()
mysql_real_connect()
方法使用了CLIENT_INTERACTIVE
选项的连接就是交互式连接。
日志部分
log_error
:指定错误日志文件名称,用于记录当mysql启动和停止时,以及服务器在运行中发生任何严重错误时的相关信息。显示的路径是相对路径,是相对于datadir
变量的路径。如果为空,表示把日志写到控制台而不是文件。mysql> show variables like 'log_error'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | log_error | .\ATAO.err | +---------------+------------+ 1 row in set (0.00 sec)
log_bin
:二进制日志记录(默认关闭)。启用二进制日志记录后,服务器会将所有更改数据的语句记录到二进制日志中,该日志用于备份和复制。mysql> show variables like 'log_bin%'; +---------------------------------+------------------------------------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------------------------------------+ | log_bin | ON | | log_bin_basename | C:\ProgramData\MySQL\MySQL Server 5.6\Data\mysql-bin | | log_bin_index | C:\ProgramData\MySQL\MySQL Server 5.6\Data\mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+------------------------------------------------------------+ 5 rows in set (0.00 sec)
binlog-do-db
:指定将更新记录到二进制日志的数据库,其他所有没有显式指定的数据库更新将忽略,不记录在日志中binlog-ignore-db
:指定不将更新记录到二进制日志的数据库sync_binlog
:控制MySQL服务器将二进制日志同步到磁盘的频率general_log
:是否启用一般查询日志。该值可以为0(或OFF)以禁用日志,或为1(或ON)以启用日志。日志输出的目的地由log_output
系统变量控制;如果该值为NONE,则即使启用了日志,也不会写入任何日志。general_log_file
:指定查询日志文件名,用于记录所有的查询语句slow_query_log
:是否启用慢查询日志slow_query_log_file
:指定慢查询日志文件名称,用于记录耗时比较长的查询语句long_query_time
:设置慢查询的时间,超过这个时间的查询语句才会记录日志log_slow_admin_statements
:是否将管理语句写入慢查询日志,管理语句包括ALTER TABLE
,ANALYZE TABLE
,CHECK TABLE
,CREATE INDEX
,DROP INDEX
,OPTIMIZE TABLE
和REPAIR TABLE
缓存部分
缓存部分中的查询缓存(query cache)功能,在高版本中会被删除。
key_buffer_size
:索引缓存区的大小(只对MyISAM
表起作用)mysql> show variables like 'key_buffer_size'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------+ 1 row in set (0.00 sec)
query_cache_size
:查询缓存大小mysql> show variables like 'query_cache_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_size | 0 | +------------------+-------+ 1 row in set (0.00 sec)
query_cache_limit
:超出此大小的查询将不被缓存mysql> show variables like 'query_cache_limit'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | query_cache_limit | 1048576 | +-------------------+---------+ 1 row in set (0.00 sec)
query_cache_min_res_unit
:缓存块最小大小mysql> show variables like 'query_cache_min_res_unit'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | query_cache_min_res_unit | 4096 | +--------------------------+-------+ 1 row in set (0.00 sec)
query_cache_type
:缓存类型,决定缓存什么样的查询。0 or OFF
表示不要在查询缓存中缓存结果或从查询缓存中检索结果。不会取消分配查询缓存缓冲区。因此,应该将query_cache_size
设置为0;1 or ON
缓存所有以SELECT SQL_NO_CACHE
开头的可缓存查询结果;2 or DEMAND
表示仅对以SELECT SQL_CACHE
开头的可缓存查询的缓存结果。mysql> show variables like 'query_cache_type'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_type | OFF | +------------------+-------+ 1 row in set (0.00 sec)
sort_buffer_size
:每个必须执行排序的会话都会分配此大小的缓冲区。sort_buffer_size
并非特定于任何存储引擎mysql> show variables like 'sort_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | sort_buffer_size | 262144 | +------------------+--------+ 1 row in set (0.00 sec)
max_allowed_packet
:限制server接受的数据包大小,默认4MBmysql> show variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | +--------------------+---------+ 1 row in set (0.00 sec)
join_buffer_size
:连接缓存的大小,默认256KBmysql> show variables like 'join_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | join_buffer_size | 262144 | +------------------+--------+ 1 row in set (0.00 sec)
thread_cache_size
:服务器应缓存多少线程以供重用。当客户端断开连接时,如果那里的线程少于thread_cache_size
,则将客户端的线程放入缓存中。通过尽可能地重用从缓存中获取的线程来满足线程请求,并且仅当缓存为空时才创建新线程。如果有许多新连接,则可以增加此变量以提高性能。默认值是通过8 + (max_connections / 100)
计算出来的,上限为100。Threads_cache
:代表此时此刻线程缓存中有多少空闲线程mysql> show status like 'Threads_cache%'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | Threads_cached | 0 | +----------------+-------+ 1 row in set (0.00 sec)
Threads_connected
:代表当前已建立连接的数量mysql> show status like 'Threads_connected%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 1 | +-------------------+-------+ 1 row in set (0.00 sec)
Threads_created
:代表最近一次服务启动,已创建线程的数量mysql> show status like 'Threads_created%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Threads_created | 1 | +-----------------+-------+ 1 row in set (0.00 sec)
Threads_running
:代表当前激活的线程数mysql> show status like 'Threads_running%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Threads_running | 1 | +-----------------+-------+ 1 row in set (0.00 sec)
InnoDB部分
innodb_buffer_pool_size
:该参数指定大小的内存来缓冲数据和索引,最大可以设置为物理内存的80%mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 193986560 | +-------------------------+-----------+ 1 row in set (0.00 sec)
innodb_flush_log_at_trx_commit
:主要控制InnoDB将log buffer
中的数据写入OS buffer
,并刷到磁盘的时间点,值分别为0,1,2mysql> show variables like 'innodb_flush_log_at_trx_commit'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 1 row in set (0.00 sec)
innodb_thread_concurrency
:设置InnoDB线程的并发数,默认为0表示不受限制,如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍mysql> show variables like 'innodb_thread_concurrency'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_thread_concurrency | 9 | +---------------------------+-------+ 1 row in set (0.00 sec)
innodb_log_buffer_size
:InnoDB用于写入磁盘上的日志文件的缓冲区大小(以字节为单位)。默认值为8MB。较大的日志缓冲区使大型事务可以运行,而无需在事务提交之前将日志写入磁盘。mysql> show variables like 'innodb_log_buffer_size'; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | innodb_log_buffer_size | 3145728 | +------------------------+---------+ 1 row in set (0.00 sec)
innodb_log_file_size
:日志组中每个日志文件的大小(以字节为单位)。日志文件的总大小(innodb_log_file_size * innodb_log_files_in_group
)不能超过略小于512GB的最大值。mysql> show variables like 'innodb_log_file_size'; +----------------------+----------+ | Variable_name | Value | +----------------------+----------+ | innodb_log_file_size | 50331648 | +----------------------+----------+ 1 row in set (0.00 sec)
innodb_log_files_in_group
:日志组中的日志文件数。 InnoDB以循环方式写入文件。默认值(推荐)为2。文件的位置由innodb_log_group_home_dir
指定mysql> show variables like 'innodb_log_files_in_group'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_log_files_in_group | 2 | +---------------------------+-------+ 1 row in set (0.00 sec)
read_buffer_size
:mysql读入缓冲区大小,对表进行顺序扫描的请求将分配到一个读入缓冲区mysql> show variables like 'read_buffer_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | read_buffer_size | 65536 | +------------------+-------+ 1 row in set (0.00 sec)
read_rnd_buffer_size
:mysql随机读的缓冲区大小mysql> show variables like 'read_rnd_buffer_size'; +----------------------+--------+ | Variable_name | Value | +----------------------+--------+ | read_rnd_buffer_size | 262144 | +----------------------+--------+ 1 row in set (0.00 sec)
innodb_file_per_table
:启用时,将在每个表文件表空间中创建表。禁用后,将在系统表空间中创建表。mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec)
总结
本篇大概讲解了六类参数,共40多个,基本上都是常用的参数或者说值得关注的参数。对于MySQL参数,或者其他工具的一些参数,大可以记住一些常用的,并且知道去哪里找那些不常用的。
参考
- https://dev.mysql.com/doc/refman/5.6/en/server-option-variable-reference.html
- https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html
- https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
- https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html