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.6binlog的格式为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_BYERROR_FOR_DIVISION_BY_ZERONO_ZERO_DATENO_ZERO_IN_DATE 等。

  • sync_binlog

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

 

posted @ 2018-11-29 16:13  AYard  阅读(1136)  评论(0编辑  收藏