sd
作為 MySQL 5.5 和 5.6 性能比較的一部分，我研究了下兩個版本默認(rèn)參數(shù)的差異，為了了解差異內(nèi)容，我使用如下的 SQL 語句分別在 MySQL 5.5 和 5.6 版本進(jìn)行查詢，得出下表：
讓我們來看看這些差異的配置中最重要的也是影響最大的部分：
performance_schema 在 MySQL 5.6 中默認(rèn)是開啟的，但相關(guān)的很多參數(shù)相比 MySQL 5.5 卻是降低了，例如 performance_schema 自動調(diào)整到 445 個表和 224 線程，比 MySQL 5.5 低。盡管默認(rèn) max_connections 只是 150 ，比 200 還小。
innodb_stats_on_metadata 在 MySQL 5.6 默認(rèn)關(guān)閉，使得 information_schema 的查詢速度快很多。
innodb_log_file_size – 默認(rèn)值從 5MB 提升到 50MB，這是一個好的改變，雖然我覺得這個默認(rèn)數(shù)值還可以再大些。對于寫負(fù)載高的情況下，默認(rèn)配置的 MySQL 5.6 性能更好。
back_log 改動比較小，從 50 改為 80。如果系統(tǒng)每秒處理的連接數(shù)很高，還需要繼續(xù)提高這個配置的值。
open_files_limit 由原來的 1024 改為 5000
innodb_auto_extend_increment 由 8MB 改為 64MB，可幫助降低碎片。
max_connect_errors 從 10 改為 100，可降低潛在的連接堵塞，但還可以更高些。
sort_buffer_size 從 2M 將為 256K，這可避免小排序?qū)е碌馁Y源浪費(fèi)，但是對大的排序有負(fù)面的影響。
max_allowed_packet 從 1MB 改為 4MB 讓 MySQL 可處理更大的查詢。
join_buffer_size 從 128K 改為 256K，我覺得這個改動影響不大。
table_open_cache 從 400 提高到 2000，挺好！
innodb_buffer_pool_instances 從 1 改為 8，用于優(yōu)化更高并發(fā)的負(fù)載。
query_cache_type 和 query_cache_size. The behavior is “no cache” by default still but it is achieved differently now. The query_cache_type is now off by default with default size of 1MB while in MySQL 5.5 and before it was “ON” by default with query cache size of 0 which makes it disabled. I wish query_cache_size though would be larger by default as value of 1M is too small to be practical if someone tries to enable it.
sql_mode has NO_ENGINE_SUBSTITUTION value by default which is good change as trying to create Innodb table but getting MyISAM because Innodb was disabled for some reason was very error prone gotcha. Note this is as far as MySQL 5.6 goes - STRICT_MODE and other safer behaviors are not enabled by default.
innodb_old_blocks_time 設(shè)置為 1000，很好的改變，默認(rèn)掃描 InnoDB 緩沖池大小。
thread_cache_size 默認(rèn)啟用，對很多連接和斷開連接操作的情況下有幫助。
sync_relay_log_info and sync_master_info 默認(rèn)值有原來的 0 改為 10000. 該改動幾乎不會影響負(fù)載。
secure_auth 默認(rèn)開啟，要求新的密碼握手，特別是阻止老的不安全的做法，很好！
innodb_concurrency_tickets has been increased from 500 to 5000. If you’re using innodb_thread_concurrencythis will reduce overhead associated with grabbing and releasing innodb_thread_concurrency slot but will increase potential starvation of queued threads especially for IO bound workloads. Most users will not be affected though as innodb_thread_concurrency is 0 by default so this queuing feature is disabled.
innodb_purge_threads 默認(rèn)為 1 ，使用專用的后臺 purge 線程，好！
innodb_open_files 由 300 改為 2000，好！
innodb_data_file_path got a small change with starting ibdata1 size raised from 10M to 12M. I’m not sure what is the purpose of this change but it is unlikely to have any practical meaning for users. Considering the default innodb_auto_extend_increment is 64 starting with 64M might have made more sense.
innodb_purge_patch_size 從 20 改為 300.
innodb_file_per_table 默認(rèn)啟用，這個改變很大，而且很棒。特別是當(dāng)你的表非常大的時候。
optimizer_switch is the catch all variable for a lot of optimizer options. I wonder why was not it implemented as number of different variables which would make more sense in my opinion. MySQL 5.6 adds a lot more optimizer switches which you can play with:
mysql [localhost] {msandbox} (test) > select * from var55 where variable_name='OPTIMIZER_SWITCH' G
*************************** 1. row ***************************
VARIABLE_NAME: OPTIMIZER_SWITCH
VARIABLE_VALUE: index_merge=on,index_merge_union =on,index_merge_sort_union =on,index_merge_intersection=on,engine_condition_pushdown=on
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select * from var56 where variable_name='OPTIMIZER_SWITCH' G
*************************** 1. row ***************************
VARIABLE_NAME: OPTIMIZER_SWITCH
VARIABLE_VALUE: 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
1 row in set (0.00 sec)