[MySQL 5.6] 5.6新参数 （完全版）

以下列出了mysql 5.6的一些新参数 || 老参数新功能，有一些的说明只是参照的文档，还没有从代码求证。

这些参数的分类也显示了5.6在不同层面的改进(or regression?)

不定期更新ing…

/////////////////////////////////////////

server 层参数

table cache

meta data lock

slow_log

控制项

disconnect_on_expired_password#用于控制客户端如何处理失效的密码，默认打开

server id

优化器

optimizer trace

<a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_optimizer_trace">optimizer_trace</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_optimizer_trace_features">optimizer_trace_features</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_optimizer_trace_limit">optimizer_trace_limit</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_optimizer_trace_max_mem_size">optimizer_trace_max_mem_size</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_optimizer_trace_offset">optimizer_trace_offset</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_end_markers_in_json">end_markers_in_json</a>

performance schema

大量用于控制ps表大小的参数，以performance_schema_作为前缀，不一一列出

replication/gtid

<a href="http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_mode">gtid_mode</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_enforce_gtid_consistency">enforce_gtid_consistency</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_next">gtid_next</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_owned">gtid_owned</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_purged">gtid_purged</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_executed">gtid_executed</a>

replication/crash safe

replication/control option

<a href="http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html#sysvar_slave_rows_search_algorithms">slave_rows_search_algorithms</a>

replication/binlog

binlog_order_commits    #开启该选项时，事务提交顺序和binlog记录顺序是相同的，默认打开；设置为关闭时，事务提交顺序可能是并行的；关闭可能提升性能

binlog_rows_query_log_events   #行模式下，是否记录query

innodb层

innodb buffer pool restore/dump

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_load_at_startup">innodb_buffer_pool_load_at_startup</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_dump_at_shutdown">innodb_buffer_pool_dump_at_shutdown</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_dump_now">innodb_buffer_pool_dump_now</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_load_abort">innodb_buffer_pool_load_abort</a>

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_filename">innodb_buffer_pool_filename</a>

flush strategy

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_neighbors">innodb_flush_neighbors</a>

change buffer

全文索引

innodb_ft_aux_table

innodb_ft_cache_size

innodb_ft_enable_diag_print

innodb_ft_enable_stopword

innodb_ft_max_token_size

innodb_ft_min_token_size

innodb_ft_num_word_optimize

innodb_ft_server_stopword_table

innodb_ft_sort_pll_degree

innodb_ft_user_stopword_table

innodb_optimize_fulltext_only

索引统计

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_persistent">innodb_stats_persistent</a>

#物化统计信息，默认打开

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages">innodb_stats_persistent_sample_pages</a>

#当打开innodb_stats_persistent选项时，这个设置才生效

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_transient_sample_pages">innodb_stats_transient_sample_pages</a>

#当关闭innodb_stats_persistent选项时生效，采样page数（尤其是后者）不应该设置的太大，否则会产生额外的io开销，但也不应设置的太小，否则会导致查询计划不准确

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_auto_recalc">innodb_stats_auto_recalc</a>

#用于决定是否在表上存在大量更新时（超过10%的记录更新）重新计算统计信息。默认打开，如果关闭该选项，就需要在每次创建索引或者更改列之后，运行一次analyze table命令来更新统计信息，否则可能选择错误的执行计划。同样的，也可以在create table/alter table命令中指定stats_auto_recalc值

compressed table

<a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_compression_level">innodb_compression_level</a>

#定义压缩表的压缩级别，在具有较好压缩特性的数据集上，可以适当调小该值，还获得更好的tps性能

control option

online ddl&amp;&amp; index create

innodb api

innodb_api_bk_commit_interval

innodb_api_disable_rowlock

innodb_api_enable_binlog

innodb_api_enable_mdl

innodb_api_trx_level

innodb_autoextend_increment

memcache plugin

daemon_memcached_enable_binlog

daemon_memcached_engine_lib_name

daemon_memcached_engine_lib_path

daemon_memcached_option

daemon_memcached_r_batch_size

daemon_memcached_w_batch_size

undo log

read ahead

innodb_random_read_ahead

innodb_read_ahead_threshold  #将其设置为0 ，可以关闭线性预读，对于随机读写，我们推荐关闭