MySQL 5.7 和 MySQL 5.6参数默认值比较

 

这里只拿出默认值有改变的参数，默认值没有改变的不在下面的列表中。这些数据是通过 ” show global variables ;” 获得，可能不是所有的参数。

参数	MySQL 5.6	MySQL 5.7
binlog_error_action	IGNORE_ERROR	ABORT_SERVER
binlog_format	STATEMENT	ROW
binlog_gtid_simple_recovery	OFF	ON
eq_range_index_dive_limit	10	200
innodb_buffer_pool_dump_at_shutdown	OFF	ON
innodb_buffer_pool_instances	8	1
innodb_buffer_pool_load_at_startup	OFF	ON
innodb_checksum_algorithm	innodb	crc32
innodb_file_format	Antelope	Barracuda
innodb_file_format_max	Antelope	Barracuda
innodb_large_prefix	OFF	ON
innodb_log_buffer_size	8388608	16777216
innodb_purge_threads	1	4
innodb_strict_mode	OFF	ON
innodb_use_native_aio	ON	OFF
log_warnings	1	2
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	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,duplicateweedout=on, subquery_materialization_cost_based=on,use_index_extensions=on, condition_fanout_filter=on,derived_merge=on
performance_schema_accounts_size	100	-1
performance_schema_max_cond_instances	3504	-1
performance_schema_max_file_classes	50	80
performance_schema_max_file_instances	7693	-1
performance_schema_max_mutex_classes	200	210
performance_schema_max_mutex_instances	15906	-1
performance_schema_max_rwlock_instances	9102	-1
performance_schema_max_socket_instances	322	-1
performance_schema_max_statement_classes	168	193
performance_schema_max_table_handles	4000	-1
performance_schema_max_table_instances	12500	-1
performance_schema_max_thread_instances	402	-1
performance_schema_setup_actors_size	100	-1
performance_schema_setup_objects_size	100	-1
performance_schema_users_size	100	-1
slave_net_timeout	3600	60
sql_mode	STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sync_binlog	0	1
table_open_cache_instances	1	16

 

• binlog_format

binlog的格式默认为ROW，在mysql5.6时binlog的格式为STATEMENT。

• innodb_buffer_pool_instances

Buffer pool 的数量，默认值为1，在MySQL5.6该值为8。

• innodb_checksum_algorithm

指定如何生成和验证存储在InnoDB表空间的磁盘块中的校验和。默认值使用性能更好的crc32 。

• innodb_log_buffer_size

innodb重做日志缓冲空间默认为16M,在 MySQL 5.6时这个值为8M。

• innodb_purge_threads

Purge线程的数量默认为4，在MySQL5.6时默认值为1。

• sql_mode

默认sql mode 增加了ONLY_FULL_GROUP_BY、ERROR_FOR_DIVISION_BY_ZERO、 NO_ZERO_DATE、 NO_ZERO_IN_DATE 等。

• sync_binlog

默认值为1，表示binlog日志同步写入磁盘。MySQL 5.6该参数默认值为0。