MySQL8.0新增配置參數詳解

MySQL8.0新增或改進了數據字典、原子DDL、安全和賬戶、資源管理、InnoDB增強、字符集支持、JSON增強、字段類型支持、優化器、通用表達式、窗口函數、正則表達式支持、內部臨時表、日志、備份鎖等特性，MySQL本身是一個配置比較多比較復雜的數據庫，那么新的版本中有哪些參數項是新增的用來控制這些新特性的使用那？接下來我們對新增的配置項進行逐一分析。
? activate_all_roles_on_login
此參數在版本8.0.2引入，是一個可以動態調整的global級參數，默認值為OFF。此參數用于控制在賬戶登錄時是否激活已經授予的角色，如果為ON則授予的角色會被激活，設置為OFF時只能通過SET DEFAULT ROLE顯式激活用戶角色。activate_all_roles_on_login設置只在賬戶登錄或者開始執行存儲過程時生效，如果想更改session的role需要執行SET ROLE語句。
? binlog_expire_logs_seconds
參數在版本8.0.1中引入，是可以動態調整的global級參數，8.0.4之前默認值為0，8.0.11之后為2592000也就是30天。之前版本binlog自動清理時間以參數expire_logs_days也就是以天為單位，當前兩個參數并存并且有一個非0時則以非0的參數為binlog自動清理時間，如果兩個都為非0值則以binlog_expire_logs_seconds為binlog清理時間忽略expire_logs_days參數設置。
? binlog_row_metadata
此參數在8.0.1版本引入，是可以動態調整的global級參數，默認值為MINIMAL,可以設置為FULL。此參數用于控制row格式下binlog中表的元數據數量，設置為MINMAL時記錄符號標記、列字符集和空間類型，設置為FULL時會記錄表所有的元數據，例如列名、枚舉或集合所有的值、主鍵信息等等。
? binlog_row_value_options
此參數在8.0.3版本引入，是可以動態調整global級參數，默認值為’’，可以設置為PARTIAL_JSON。
當設置為PARTIAL_JSON并且binlog格式為ROW或者MIXED模式時，使用JSON_SET(),JSON_REPLACE()和 JSON_REMOVE()對JSON的列進行操作時，binlog只記錄更新的部分，而不是記錄整個JSON的變更，進而減少binlog大小。不過當對JSON文檔的更改需要比當前文檔更大的空間或者SERVER不能夠進行部分更新時，binlog還是會記錄整個JSON文檔。
? binlog_transaction_dependency_history_size
此參數在版本8.0.1中引入，是可以動態調整的global級參數，默認值為25000，可以設置為0-1000000之間的任意整數。8.0基于WriteSet進行并行復制時，WriteSet是一個hash數組，binlog_transaction_dependency_history_size值就是這個hash數組的最大值。
? binlog_transaction_dependency_tracking
此參數在8.0.1版本引入，是可以動態調整的global級枚舉類型參數，默認值為COMMIT_ORDER，也可以設置為WRITESET、WRITESET_SESSION。此參數用于主庫決定事務間在從庫進行多線程復制的依賴模式。
COMMIT_ORDERE：根據主庫事務提交時間戳進行并行，也就5.7的GroupCommit；
WRITESET：根據WriteSet進行并行，只要是不在同一個隊列里的都可以并行；
WRITESET_SESSION: 根據WriteSet進行并行，但相同session的事務不會并行。
? caching_sha2_password_auto_generate_rsa_keys
在版本8.0.4引入，是不可動態調整的global級參數，默認值為ON，當OpenSSL被編譯進MySQL時，根據此參數判斷是否在啟動時自動生成公鑰和私鑰文件。
? caching_sha2_password_private_key_path
同上，用于指定私鑰文件路徑及文件名，當設置為相對路徑時存儲在數據文件路徑下。
? caching_sha2_password_public_key_path
同上，用于存放公鑰文件路徑及文件名稱。
? cte_max_recursion_depth
在8.0.3版本引入，是可以動態調整的global、session級的整數類型參數，默認值為1000，可以設置為0 – 4294967295之間的任意整數。該參數用于設置公共表表達式的最大遞歸深度。如果SQL執行過程中遞歸深度超過該值則會中斷執行。
? default_collation_for_utf8mb4
在8.0.11版本中引入，是可以動態調整的global、session級枚舉類型參數。此參數值會從主庫傳遞到從庫，主要用于主從同步或者MGR中從8.0之前版本向8.0同步數據時來保持主從間以一致的排序規則來處理數據。
? histogram_generation_max_mem_size
8.0.2版本引入，是可以動態設置的global、session級參數，默認值為20000000，可以設置為1000000到18446744073709551615（32位系統為4294967295）之間任意值。當建立直方圖時，需要將表的數據讀入內存，但是當表過大時可能會將將大量數據讀入內存，為規避這個風險，server層根據histogram_generation_max_mem_size設置進行計算，計算出讀出多少行進入內存，進行取樣。

? information_schema_stats_expiry
參數由8.03版本引入，是可以動態設置的global、session級參數。默認值為86400，可以設置為0到315360000之間熱任意整數。
Information_schema庫中有一些列提供表的統計數據，統計數據會隨著表中內容的變化進行更新。默認情況下MySQL檢索mysql.index_stats 和mysql.table_stats兩個字典表進行表信息檢索，這樣遠比從存儲引擎層檢索相關信息高效的多。如果需要的表統計信息沒有被緩存或者已經過期，則會從存儲引擎獲取相關統計信息并緩存到mysql.index_stats 和mysql.table_stats兩個表中，在表統計信息過期之前，后來的查詢都會使用此緩存信息。每個session可以獨立設置information_schema_stats_expiry，默認值是86400秒也就是24小時，此數值最大可以設置為一年；如果想要更新某張表的統計信息執行ANALYZE TABLE即可；如果每次查詢都想獲取最新的統計信息，將此值設置成0即可，MySQL每次都會跳過mysql.index_stats 和mysql.table_stats兩個字典表直接通過存儲引擎獲取統計信息。
? innodb_dedicated_server
參數在8.03版本中引入，是一個只能在啟動時設置的global級參數。此參數設置為ON時，MySQL會根據檢測到的內存大小設置innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_method三個參數。有了這個參數我們就不用再寫腳本根據內存大小去修改配置文件的這三個參數了，運維自動化又省了一步。當服務器MySQL與其他應用共享服務器內存時建議設置為OFF。那么MySQL具體的設置策略是什么那？
innodb_buffer_pool_size：
Detected Server Memory Buffer Pool Size
小于1G 128MiB (the innodb_buffer_pool_size default)
小于等于 4G Detected server memory 0.5
大于 4G Detected server memory 0.75
innodb_log_file_size：
Detected Server Memory Log File Size
<小于1GB 48MiB (the innodb_log_file_size default)
小于等于4GB 128MiB
小于等于8GB 512MiB
小于等于16GB 1024MiB
大于16GB 2048MiB
innodb_flush_method
當開啟 innodb_dedicated_server時，刷盤方式會采用O_DIRECT_NO_FSYNC ，O_DIRECT_NO_FSYNC 不可用時將會采用默認的刷盤方式。需要注意的是，目前在linux中當文件大小發生變化時，O_DIRECT_NO_FSYNC 可能會導致系統hung住，因此不建議在linux中采用該刷盤方式。
? innodb_directories
參數在版本8.0.4中引入，只能在啟動時指定，不能夠動態調整。數據庫啟動時會根據此參數掃描innodb 表空間文件。在數據庫離線時，我們可以通過指定該參數用于移動或者恢復表空間文件到指定的路徑。
? innodb_log_spin_cpu_abs_lwm
參數在8.0.11版本中引入，是可以動態調整的全局參數，默認值為80，可以設置為0到4294967295之間的任意整數。此數值表示innodb用戶線程刷redo進行自旋等待時CPU的低水位，當cpu使用率低于此值時不進行自旋等待。如果此值設置超過100例如150時，則表示在多核CPU時第一個核使用率為100%，第二個核使用率為50%。
? innodb_log_spin_cpu_pct_hwm
同上一個參數，默認值為50，可以設置為0到100之間的任意整數值。此參數表示innodb用戶線程刷redo進行自旋等待的高水位，表示CPU總處理能力百分比，例如4核設置為50時，表示個2個核為100%。該參數核數計算以實際使用CPU為準，例如將mysqld進程綁定到48核中的4核，則該參數忽略其他44核。
? innodb_log_wait_for_flush_spin_hwm
參數在8.0.11版本中引入，是可以動態調整的全局整型參數，單位為毫秒，默認值為400可以設置為0到2^64-1（32位系統為2^32-1）之間的整數值，當用戶線程等待刷redo超過innodb_log_wait_for_flush_spin_hwm毫秒后不再進行自旋。
? innodb_print_ddl_logsz
這個參數在8.03版本中引入是可以動態設置的global級參數。默認值為OFF，設置為ON時能夠在標準錯誤輸出，根據系統和配置的不同可能是error log、Linux終端或windows控制臺。8.0引入了原子DDL的特性，InnoDB通過寫DDL日志到mysql.innodb_ddl_log的方式來實現DDL的重做和回滾，但除非在debug模式下，mysql.innodb_ddl_log對用戶是不可見的，可以通過設置innodb_print_ddl_logsz為ON來在錯誤輸出中查看DDL的重做和回滾信息。
? innodb_redo_log_encrypt
這個參數在8.0.1版本中引入，是可以動態設置的global級參數，默認值為OFF。此參數用于控制redo log的加密，將內存中的redo寫入磁盤時進行加密，從磁盤中讀取redo log進內存時進行解密。Redo的元數據和加密秘鑰存放第一個redo文件頭部，如果啟動前將此文件刪除則會自動取消加密，如果有redo log已經加密存儲，則會啟動失敗。
? innodb_stats_include_delete_marked
參數在8.0.1版本中引入，時可以動態設置的全局布爾類型參數，默認值為No。默認情況下，MySQL進行表統計信息計算時，會采用Repeatable-Read隔離級別，因此其他進程在采用Repeatable-Read及以上隔離級別查詢時，數據和索引的統計信息已經排除掉了其他未提交事務中刪除的數據，這樣會導致執行計劃不是最優。當開啟innodb_stats_include_delete_marked時，優化器在計算統計數據時會將其他未提交事務中已刪除的數據計算在內。
? innodb_undo_log_encrypt
這個參數在8.0.1版本引入，是可以動態設置的global級參數，默認值為OFF。只有在獨立開啟undo時此參數設置為ON才會生效。和redo一樣，undo在寫入磁盤時進行加密，從磁盤讀取到內存時進行解密。Undo的加密和解密使用表空間的加密key。Undo的元數據和加密的密碼存放于undo表空間文件頭部。
? internal_tmp_mem_storage_engine
參數在8.0.2版本引入，是可以動態調整的全局、session級枚舉類型參數，默認值為TempTable，也可以設置為Memory。優化器根據此參數選擇內存中內部臨時表的引擎類型。
? keyring_operations
參數在8.0.4版本引入，是可以動態設置的全局布爾型參數，默認值為ON。此參數設置是否允許對innodb表空間加密所用的秘鑰進行遷移等操作。
? log_error_services
這個參數在8.0.2版本引入，是可以動態設置的global級參數，默認值為”log_filter_internal; log_sink_internal”。
? mandatory_roles
這個參數在8.0.2版本引入，是可以動態設置的global級參數，默認值為空字符串。
? original_commit_timestamp
在8.0.1版本引入，是可以動態調整的session級整型參數。從庫在進行主從復制重放binlog時，該參數設置為事務在主庫提交時的時間戳，從公元紀元開始的毫秒數值。
? password_history
在版本8.0.3引入，是可以動態調整的全局整型參數，默認值為0，可以設置為0- 4294967295之間的任意整數。該參數數值用于設置歷史密碼可以再次使用之前需要進行密碼修改的次數。設置為0則不會對歷史密碼是否可以重用進行限制。
? password_reuse_interval
在版本8.0.3版本引入，是可以動態調整的整型參數，默認值為0，可以設置為0- 4294967295之間的任意整數。該參數用于設置歷史密碼可以重用的間隔天數，設置為0則不會對歷史密碼重用進行時間間隔設置。
? performance_schema_max_digest_sample_age
在8.0.3版本引入，是可以動態調整的全局整型參數，默認值為60，可以設置為0- 1048576之間任意整數。該參數用于設置events_statements_summary_by_digest表中語句重新采用時間，如果有新的相同摘要值得語句被采集到并且當前表中的數據執行時間為performance_schema_max_digest_sample_age秒之前，則會使用當前語句替換events_statements_summary_by_digest中采樣的語句。
? persisted_globals_load
參數只能在啟動時指定，不可動態調整，默認值為ON。當設置為ON時，在讀取完其他配置文件后會讀取mysqld-auto.cnf文件，mysqld-auto.conf中配置項會覆蓋其他配置文件的設置，參數設置以json形式存儲于mysqld-auto.conf文件中。
8.0中支持通過set persist 語法改變當前運行實例和配置文件mysqld-auto.conf文件中的配置項，set persist_only語法只更改mysqld-auto.conf中的配置項，不改變當前實例的配置項。可以通過RESET PERSIST、RESET PERSIST system_var_name、RESET PERSIST IF EXISTS system_var_name語法取消全部或者某一個配置項。
? regexp_stack_limit
參數在8.0.4版本中引入，是可以動態設置的全局參數，默認值為8000000，可以設置為0到2147483647之間的任意整數。此數值表示MySQL用于正則表達式匹配函數REGEXP_LIKE()以及相關函數的最大堆棧內存可用字節數。
? regexp_time_limit
參數在8.0.4版本中引入，是可以動態設置的全局參數，默認值32。此參數表示匹配引擎進行正則表達式匹配函數REGEXP_LIKE()以及相關函數的最大步數，因而間接影響時間限制。
? rpl_read_size
參數在8.0.11版本中引入，是可以動態設置的全局整型參數，默認值為8192，可以設置為8192-4294967295之間的任意4k的倍數。該參數設置server從binlog和relaylog中一次讀取的最小字節數。如果binlog或relaylog的IO壓力較大，影響系統性能，可適當增大該參數，減少IO次數，進而減小磁盤IO壓力。 master的dump線程和slave的coordinator 線程等所有讀取binlog和relaylog的線程都會分配rpl_read_size大小的buffer，因此設置過大會對服務器內存有一定影響。
? resultset_metadata
參數在8.0.3版本中引入，是可以動態設置的session級枚舉類型參數，默認值為FULL,可以設置為NONE。該參數控制傳輸元數據可選的鏈接server端是否傳輸元數據，設置為FULL時返回所有元數據，設置為NONE時不返回元數據。
? schema_definition_cache
是可以動態設置的global級參數，默認值為256，可以設置為256到524288之間的任意整數。此數值表示可以在字典對象緩存中保存的schema定義對象的最大數量。
? sha256_password_auto_generate_rsa_keys
此參數需要在MySQL啟動時指定，不可以動態設置。默認值為ON。當MySQL用OpenSSL編譯時，此參數可用。當data目錄下沒有RSA私有/公共密鑰對時，此參數用于控制是否在啟動時自動生成生成RSA私有/公共密鑰對。
? sha256_password_private_key_path
此參數需要在MySQL啟動時指定，不可以動態設置。默認值為private_key.pem。此參數用于設置私鑰文件，設置為相對路徑時保存在data目錄下。
? sha256_password_public_key_path
此參數用于設置公鑰文件。
ssl_fips_mode參數在8.0.11版本中引入，是可動態設置的global枚舉類型參數。默認值OFF，可以設置為OFF (0) 、ON (1)、STRICT (2)。
? show_create_table_verbosity
參數在8.0.11版本引入，是可以動態設置的全局和session級布爾型參數，默認值為OFF。設置為OFF并且表行格式為默認值時，執行show create table顯示的建表語句不會顯示row_format值；啟用該參數時，show create table的create table語句會一直顯示row_format值。
? ssl_fips_mode
參數在8.0.11版本中引入，是可以動態設置global級枚舉類型參數。默認值為OFF，可以設置為OFF (0) 、ON (1)、STRICT (2)。當將OpenSSL編譯進MySQL并且FIPS模塊可用時，用于設置FIPS模塊是否在服務端可用。
? temptable_max_ram
參數在8.0.2版本中引入，是可以動態設置的global級整型參數，默認值為1073741824（1GB）,可以設置為2097152-2^64-1的任意整數。當TempTable 存儲引擎的表小于temptable_max_ram時，臨時表不會存入磁盤。
MySQL8.0新增的的配置項較多，在配置線上應用時還是需要多注意參數的不同配置對數據庫整體運行的影響，結合自己的業務的實際情況設置最符合自己生產需要的配置文件。

本文由京東商城數據庫技術部張洋提供。