注:相关变量存在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';
二. 常用变量
变量名称
意义
默认值
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
https://www.cnblogs.com/waterystone/tag/MySQL/default.html?page=1