MySQL变量

注：相关变量存在INFORMATION_SCHEMA.GLOBAL_VARIABLES/SESSION_VARIABLES表中。

一. mysql变量的操作

1.1 查看

SHOW VARIABLES
SHOW VARIABLES LIKE "%max_allowed_packet%";
SELECT @@max_allowed_packet
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'max_allowed_packet'
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME = 'max_allowed_packet'

 

　　

2.2 设置

SET GLOBAL max_allowed_packet = 2 * 1024 * 1024 * 10
SET @@GLOBAL.max_allowed_packet = 2 * 1024 * 1024 * 10
SET autocommit = 1
SET autocommit = ON // 设置隔离级别
SET GLOBAL tx_isolation='REPEATABLE-READ';

　　

二. 常用变量

MySQL Server变量：Server System Variables

InnoDB变量：InnoDB Startup Options and System Variables

 

变量名称	意义	默认值
version	MySQL版本
sql_safe_updates	update/delete语句的where条件需要使用索引或limit	OFF
log_error	错误日志文件的路径。
general_log	查询日志是否打开，用于调试。	OFF
general_log_file	查询日志文件的路径。
auto_increment_offset	自增字段起始值，取值1-65535	1
auto_increment_increment	自增字段步长，取值1-65535	1
tmp_table_size	内存临时表的大小。如果临时表大小超过此值，那么内存临时表就会转成磁盘临时表。	16MB
max_allowed_packet	客户端请求的数据量阈值(B)。如果超过此值，服务端会拒绝接收更多的数据并抛出相应的错误	1048576
net_buffer_length	MySQL server获取一行，写到net_buffer中，它的内存的大小由参数net_buffer_length确定。重复获取行，直到net_buffer写满，调用网络接口发出去。	16384
max_connections	同时连接的最大客户端数	100
max_user_connections	同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。
wait_timeout	当一个连接的空闲时间超过该值后，MySQL 就会断开该连接（并会把线程交还线程池）。(非交互式，如JDBC)	8h
interactive_timeout	当一个连接的空闲时间超过该值后，MySQL 就会断开该连接（并会把线程交还线程池）。(交互式，如MySQL客户端)	8h
max_length_for_sort_data	文件排序算法选择的根据。当查询需要所有列的长度小于此阈值时，mysql使用单次传输排序算法(一次性读入所有的列，排完序直接返回)，否则使用两次传输排序算法(第一次读入需要排序的字段和行指针，排完序后根据行指针读取查询需要的列，造成大量的随机IO)。	1024
innodb_sort_buffer_size	为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。	1048576
read_rnd_buffer_size	MRR缓存区大小
thread_cache_size	数据库连接池的缓存线程个数	8
tx_isolation	隔离级别，可选：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE	REPEATABLE-READ
autocommit	事务是否自动提交。每一条单独的查询都是一个事务，并且自动开始，自动提交。	ON
query_cache_type	是否打开查询缓存。可选：OFF/ON/DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才放入查询缓存。	ON,查询缓存使得可扩展性受限，并且可能造成性能瓶颈，8.0已建议取消。
query_cache_size	查询缓存使用的总内存空间（B），必须是1024的倍数，否则实际分配也会是1024的倍数。	16MB
query_cache_limit	能够缓存的最大查询结果。如果查询结果大于此值，则不会被缓存。	1MB
sync_binlog	每N次事务提交，MySQL调用文件系统的刷新操作将缓存刷盘。1表示每次事务提交都刷盘，最安全，但性能最差；0表示MySQL不控制刷盘，完全交由文件系统，性能最好，但最不安全。
log_bin	是否启用binlog	0
log_bin_basename
log_bin_index
max_binlog_size	binlog文件大小，4K~1G	1G
expire_logs_days	binlog保存天数
log_slow_queries	是否启用慢日志	0
long_query_time	慢查询时间阈值。高于此值的查询语句会被记录在慢日志里。	10秒
slow_query_log_file	慢日志文件的路径
log_queries_not_using_indexes	是否将没有应用索引的查询记入慢日志里。	1
log_throttle_queries_not_using_indexes	每分钟允许记录到slow log的未使用索引的SQL数（0表示不限）	0
group_concat_max_len	group_concat()函数的结果长度限制	1024
innodb_version	innodb版本
innodb_file_format	innodb文件格式
innodb_data_file_path	共享表空间配置（默认，所有innodb表的数据都会放在该文件里ibdata1）
innodb_file_per_table	是否为每个表生成独立的表空间（这样，每个innodb表的数据都会放在自己的表空间里，名称为*.ibd）
innodb_read_io_threads	innodb读线程数
innodb_write_io_threads	innodb写线程数
innodb_purge_threads	innodb purge线程数(l回收undo页)
innodb_page_size	innodb里页的大小	16KB
innodb_log_buffer_size	redo log缓存池（每秒会同步到日志文件）	8MB
innodb_log_file_size	redo log的文件大小(ib_logfile0、ib_logfile1)	48MB
innodb_log_files_in_group	redo log的文件数(ib_logfile0、ib_logfile1...)	2
sync_binlog	binlog的刷盘策略。0：只刷到操作系统缓存中；1：每次提交都刷新到磁盘中；N(>1)：每N次提交，MySQL将操作系统中缓存刷盘。	0
max_binlog_size	单个binlog文件的大小，文件达到999999时又会回到000001。	1G
binlog_cache_size	未提交的二进制日志会被记录到该缓存中去，等该事务提交时直接将缓冲中的二进制日志写入binlog文件。（会话级）	32KB
innodb_flush_log_at_trx_commit	redo日志的刷盘策略。0：都不刷，等待Master线程每秒的刷盘；1：每次都刷盘；2：每次只刷到操作系统缓存中。	0
innodb_buffer_pool_size	保存索引页、数据页、undo页等的内存缓存区大小(B，所有缓冲池实例的总和)，官方建议设置为物理内存的50%~75%	134217728
innodb_buffer_pool_instances	内存缓冲池(buffer pool)实例个数。把原来一整块buffer pool分割为多块内存空间，每个空间管理独立的空闲链表、LRU、锁等数据结构，大大增加并发性。	1
innodb_old_blocks_pct	缓冲池LRU列表中old列表占的百分比，也即midpoint的位置（之前的为new列表，可以认为热点数据，之后的为old列表）。新读取的页面会插入到此位置，而不是头部。	37
innodb_old_blocks_time	页读取到LRU列表的midpoint位置后，需要等待多久才会被加入到LRU列表的热端。
innodb_max_dirty_pages_pct	缓冲池中脏页的数量超过该百分比时，强制进行Checkpoint。	75
innodb_io_capacity	用来当刷新脏数据时，控制MySQL每秒执行的写IO量	200
innodb_io_capacity_max	在压力下，控制当刷新脏数据时MySQL每秒执行的写IO量	innodb_io_capacity的2倍
innodb_lru_scan_depth	每次用户查询时，LRU列表保证的可用页数。如果小于该数，则将移除LRU尾部的旧数据（有脏页的话，需要checkpoint）。	1024
innodb_purge_batch_size	每次full purge回收的undo页的数量。	20
innodb_adaptive_hash_index	是否启用自适应哈希索引	1
innodb_use_native_aio	是否启用native AIO（合并IO）	1
innodb_flush_neighbors	刷新脏页时，是否将物理邻接的脏页也一同刷新（利用AIO，提高性能）	1
innodb_status_output	是否开启innodb标准monitor输出	OFF
innodb_status_output_locks	是否开启innodb标准monitor的锁详细信息输出	OFF
innodb_show_verbose_locks	是否显示锁的详细信息
innodb_show_locks_held	记录锁详细信息的数量
innodb_lock_wait_timeout	锁等待时间（秒）	50