开发指南—变量—存储层变量

名称	是否重启	默认值	修改范围	备注
loose_enable_gts	是	1	[0|1]	全局时间戳开关
loose_gts_lease		5000	[0-10000]	全局时间戳的租约时间
loose_performance-schema-instrument	否	wait/lock/metadata/sql/mdl=ON	.*	用于获取MySQL运行时的MDL锁信息
performance_schema		ON	[ON,OFF]	用于监控MySQL服务器的执行细节
binlog_rows_query_key_content				打印sql到binlog日志
loose_polarx_max_allowed_packet		16777216	(0-2147483648)	限制私有协议下Server接受和发送的数据包大小
innodb_buffer_pool_load_at_startup			[ON|OFF]	是否启动之后,重新加载buffer pool
bulk_insert_buffer_size		4194304	[0-4294967295]	用来缓存批量插入数据的时候临时缓存写入数
show_old_temporals		OFF
ft_query_expansion_limit		20	[0-1000]	指定MyISAM引擎表使用 with query expansion 进行全文搜索的最大匹配数
innodb_old_blocks_time		1000	[0-1024]	用来表示页读取到mid位置后，需要等待多久才会被加入到LRU列表的热端
innodb_stats_sample_pages		8	[1-4294967296]	控制采集精度
thread_stack		262144	[131072-18446744073709551615]	每个连接线程被创建时，MySQL给它分配的内存大小
lc_time_names		en_US	[ja_JP|pt_BR|en_US]	控制用于显示日期和月份名称及缩写的语言
innodb_thread_concurrency			并发数限制
default_time_zone		SYSTEM	[SYSTEM|-12:00|-11:00|-10:00|-9:00|-8:00|-7:00|-6:00|-5:00|-4:00|-3:00|-2:00|-1:00|\+0:00|\+1:00|\+2:00|\+3:00|\+4:00|\+5:00|\+5:30|\+5:45|\+6:00|\+6:30|\+7:00|\+8:00|\+9:00|\+10:00|\+11:00|\+12:00|\+13:00]	时区设置
old_passwords		[0|2]
optimizer_search_depth		62	[0-62]	在多表关联场景下，控制优化器递归深度
innodb_compression_level		6	[0-9]
loose_innodb_log_optimize_ddl
max_sort_length		1024	[4-8388608]
slave_pending_jobs_size_max		1073741824	[1024-18446744073709551615]
innodb_online_alter_log_max_size		134217728	[134217728-2147483647]
key_cache_block_size			[512-16384]
mysql_native_password_proxy_users
innodb_adaptive_max_sleep_delay		150000	[1-1000000]
innodb_purge_rseg_truncate_frequency		128	[1-128]
query_alloc_block_size		8192	[1024-16384]
innodb_lock_wait_timeout		50	[1-1073741824]
innodb_purge_threads			[1-32]
innodb_compression_failure_threshold_pct		5	[0-100]
innodb_compression_pad_pct_max			[0-70]
binlog_rows_query_log_events
innodb_stats_persistent_sample_pages
innodb_ft_total_cache_size		640000000	[32000000-1600000000]
innodb_flush_method		O_DIRECT	[fsync|O_DSYNC|littlesync|nosync|O_DIRECT|O_DIRECT_NO_FSYNC]
eq_range_index_dive_limit		10
loose_max_execution_time
loose_optimizer_trace_features		greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
rds_reserved_connections		512	[0-512]
connect_timeout			[1-3600]
innodb_purge_batch_size		300	[1-5000]
div_precision_increment		4	[0-30]
avoid_temporal_upgrade
innodb_sync_array_size			[1-64]
sync_binlog			[0-2147483647]
innodb_stats_method		nulls_equal	[nulls_equal|nulls_unequal|nulls_ignored]
lock_wait_timeout		31536000
net_read_timeout		30	[1-18446744073709551615]
innodb_deadlock_detect
innodb_write_io_threads
end_markers_in_json
ngram_token_size		2	[0-20]
loose_innodb_numa_interleave
max_binlog_stmt_cache_size		18446744073709547520	[4096-18446744073709547520]
innodb_checksum_algorithm		crc32	[innodb|crc32|none|strict_innodb|strict_crc32|strict_none]
query_cache_type		[0|1|2]
innodb_ft_enable_diag_print
innodb_ft_enable_stopword
innodb_io_capacity		20000	[0-18446744073709551615]
slow_launch_time			[1-1024]
innodb_table_locks
innodb_stats_persistent
tmp_table_size		2097152	[262144-134217728]
disconnect_on_expired_password
default_storage_engine		InnoDB	[InnoDB|innodb]
net_retry_count			[1-4294967295]
innodb_ft_cache_size		8000000	[1600000-80000000]
binlog_cache_size			[4096-16777216]
innodb_max_dirty_pages_pct		75	[0-99]
query_cache_limit		1048576	[1-1048576]
innodb_disable_sort_file_cache
innodb_lru_scan_depth			[100-18446744073709551615]
innodb_ft_result_cache_limit		2000000000	[1000000-4294967295]
long_query_time			[0.03-31536000]
interactive_timeout		7200	[10-86400]
innodb_read_io_threads
transaction_prealloc_size		4096	[1024-131072]
open_files_limit		65535
innodb_open_files		3000	[10-4294967295]
max_heap_table_size		67108864	[16384-1844674407370954752]
automatic_sp_privileges
explicit_defaults_for_timestamp
ft_max_word_len		84
innodb_autoextend_increment		64	[1-1000]
sql_mode		(\s*|REAL_AS_FLOAT|PIPES_AS_CONCAT|ANSI_QUOTES|IGNORE_SPACE|ONLY_FULL_GROUP_BY|NO_UNSIGNED_SUBTRACTION|NO_DIR_IN_CREATE|POSTGRESQL|ORACLE|MSSQL|DB2|MAXDB|NO_KEY_OPTIONS|NO_TABLE_OPTIONS|NO_FIELD_OPTIONS|MYSQL323|MYSQL40|ANSI|NO_AUTO_VALUE_ON_ZERO|NO_BACKSLASH_ESCAPES|STRICT_TRANS_TABLES|STRICT_ALL_TABLES|NO_ZERO_IN_DATE|NO_ZERO_DATE|ALLOW_INVALID_DATES|ERROR_FOR_DIVISION_BY_ZERO|TRADITIONAL|HIGH_NOT_PRECEDENCE|NO_ENGINE_SUBSTITUTION|PAD_CHAR_TO_FULL_LENGTH|NO_AUTO_CREATE_USER)(,NO_AUTO_CREATE_USER|,REAL_AS_FLOAT|,PIPES_AS_CONCAT|,ANSI_QUOTES|,IGNORE_SPACE|,ONLY_FULL_GROUP_BY|,NO_UNSIGNED_SUBTRACTION|,NO_DIR_IN_CREATE|,POSTGRESQL|,ORACLE|,MSSQL|,DB2|,MAXDB|,NO_KEY_OPTIONS|,NO_TABLE_OPTIONS|,NO_FIELD_OPTIONS|,MYSQL323|,MYSQL40|,ANSI|,NO_AUTO_VALUE_ON_ZERO|,NO_BACKSLASH_ESCAPES|,STRICT_TRANS_TABLES|,STRICT_ALL_TABLES|,NO_ZERO_IN_DATE|,NO_ZERO_DATE|,ALLOW_INVALID_DATES|,ERROR_FOR_DIVISION_BY_ZERO|,TRADITIONAL|,HIGH_NOT_PRECEDENCE|,NO_ENGINE_SUBSTITUTION|,PAD_CHAR_TO_FULL_LENGTH)*
innodb_stats_transient_sample_pages
innodb_random_read_ahead
session_track_state_change
table_open_cache		2000	[1-524288]	表文件句柄高速缓存的大小
range_optimizer_max_mem_size		8388608
innodb_status_output
innodb_log_compressed_pages
slave_net_timeout		60	[15-300]
delay_key_write			[ON|OFF|ALL]
query_cache_wlock_invalidate
max_points_in_geometry		65536	[3-1048576]
max_prepared_stmt_count		16382	[0-1048576]
wait_timeout		86400	[1-31536000]
query_cache_min_res_unit			[512-18446744073709551608]	结果缓存配置
innodb_print_all_deadlocks			[OFF|ON]
loose_thread_pool_size		32		分组的数量，默认值：32。线程池中的线程被平均分到多个组中进行管理
binlog_stmt_cache_size		32768
transaction_isolation		READ-COMMITTED	[READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE]	事务隔离级别策略
innodb_buffer_pool_dump_at_shutdown
query_prealloc_size			[8192-1048576]
key_cache_age_threshold			[100-4294967295]
loose_rds_kill_connections			[0, 18446744073709551615]
transaction_alloc_block_size
optimizer_trace_limit
metadata_locks_cache_size
optimizer_prune_level
innodb_max_purge_lag
innodb_buffer_pool_dump_pct		25	[1-100]
innodb_max_dirty_pages_pct_lwm
max_sp_recursion_depth		[0-255]
innodb_status_output_locks
updatable_views_with_limit		YES	[YES|NO]
binlog_row_image		full	[full|minimal]
innodb_change_buffer_max_size			[0-50]
innodb_optimize_fulltext_only
loose_opt_rds_last_error_gtid
innodb_ft_max_token_size			[10-84]
innodb_max_undo_log_size			[10485760-18446744073709551615]
slave_parallel_type		LOGICAL_CLOCK	DATABASE,LOGICAL_CLOCK
loose_rds_check_core_file_enabled
innodb_adaptive_hash_index
innodb_sync_spin_loops
net_write_timeout
flush_time		[0-31536000]
lower_case_table_names
sha256_password_proxy_users
character_set_filesystem		binary	[utf8|latin1|gbk|binary]
innodb_flush_sync
tls_version		TLSv1,TLSv1.1,TLSv1.2	[TLSv1,TLSv1.1,TLSv1.2|TLSv1,TLSv1.1]
key_cache_division_limit		100
delayed_insert_timeout
preload_buffer_size			[1024-1073741824]
innodb_read_ahead_threshold		56
loose_optimizer_switch		index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
concurrent_insert				并发插入功能设置
block_encryption_mode		"aes-128-ecb"	["aes-128-ecb"|"aes-192-ecb"|"aes-256-ecb"|"aes-128-cbc"|"aes-192-cbc"|"aes-256-cbc"]
slow_query_log				记录慢日志
net_buffer_length		16384	[1024-1048576]
query_cache_size		3145728	[0-104857600]
delayed_insert_limit
innodb_large_prefix
innodb_monitor_disable		all
innodb_adaptive_flushing_lwm
innodb_log_checksums
delayed_queue_size
session_track_gtids			[OFF|OWN_GTID|ALL_GTIDS]
innodb_thread_sleep_delay		10000	[0-1000000]
loose_rds_set_connection_id_enabled
innodb_old_blocks_pct		37	[5-95]
innodb_ft_sort_pll_degree			[1-16]
log_slow_admin_statements
innodb_stats_on_metadata
stored_program_cache		256	[16-524288]
group_concat_max_len			[4-1844674407370954752]
innodb_sort_buffer_size			[65536-67108864]
innodb_page_cleaners
innodb_spin_wait_delay
myisam_sort_buffer_size			[262144-16777216]
innodb_rollback_segments
innodb_commit_concurrency
innodb_concurrency_tickets
table_definition_cache			[400-524288]
auto_increment_increment			[1-65535]
binlog_checksum		CRC32
max_seeks_for_key		18446744073709500000
sync_relay_log
max_length_for_sort_data			[0-838860]
back_log			[0-65535]
max_error_count
innodb_io_capacity_max		40000
innodb_strict_mode
binlog_order_commits
min_examined_row_limit
innodb_ft_min_token_size		3	[0-16]
innodb_stats_auto_recalc
max_connect_errors
session_track_schema
join_buffer_size			[128-4294967295]
innodb_change_buffering			[none|inserts|deletes|changes|purges|all]
optimizer_trace_max_mem_size
innodb_autoinc_lock_mode
innodb_rollback_on_timeout
loose_opt_rds_enable_show_slave_lag
max_write_lock_count		102400	[1-102400]
master_verify_checksum
innodb_ft_num_word_optimize
max_join_size		18446744073709551615
loose_validate_password_length			[1-12]
log_throttle_queries_not_using_indexes
innodb_max_purge_lag_delay		[0-10000000]
loose_optimizer_trace		enabled=off,one_line=off
loose_thread_handling		one-thread-per-connection	[one-thread-per-connection|pool-of-threads]
default_week_format		[0-7]
innodb_cmp_per_index_enabled
show_compatibility_56
host_cache_size		644		host缓存大小
low_priority_updates
auto_increment_offset				自增列的自增步长
range_alloc_block_size			[4096-18446744073709551615]
ft_min_word_len
sort_buffer_size			[32768-4294967295]	排序申请的buffer
max_allowed_packet			[16384-1073741824]	限制Server接受的数据包大小
thread_cache_size			[0-16384]	执行线程缓存
optimizer_trace_offset		-1
character_set_server		utf8	[utf8|latin1|gbk|gb18030|utf8mb4]	数据库级的字符集
innodb_adaptive_flushing				自适应刷新脏页开关
log_queries_not_using_indexes				没有使用索引的查询就不会记录到慢日志去
innodb_monitor_enable
table_open_cache_instances		16		控制 table cache instance的个数
innodb_flush_neighbors				用来控制buffer pool刷脏页时是否把脏页邻近的其他脏页一起刷到磁盘
innodb_buffer_pool_instances				缓冲池实例的大小
innodb_data_file_purge				是否启用异步清除策略。
innodb_data_file_purge_all_at_shutdown				正常关机时全部清理。
innodb_data_file_purge_immediate				取消数据文件的链接但不清理。
innodb_data_file_purge_interval				清理时间间隔。单位：ms。
innodb_data_file_purge_max_size				每次清理单个文件大小的最大值。单位：MB。
hotspot				热点更新的开关
hotspot_lock_type				热点更新锁类型