有很多的伺服器選項會影響這MySQL伺服器的性能，比如記憶體中臨時表的大小、排序緩衝區等。有些針對特定存儲引擎（如InnoDB）的選項，也會對查詢優化很有用。 ...
有很多的伺服器選項會影響這MySQL伺服器的性能，比如記憶體中臨時表的大小、排序緩衝區等。有些針對特定存儲引擎（如InnoDB）的選項，也會對查詢優化很有用。
調整伺服器的配置從某種程度來說是一個影響全局的行為，因為每個修改都可能對該伺服器上的每個查詢造成影響。不過有些選項是針對特定類型的優化的，如果你的請求沒有滿足條件，它將沒有任何作用。
首先我們需要檢查的選項是緩衝區大小（buffer size）
每個緩衝區都有其存在的特定原因，一般的規律是大緩衝區意味著高性能，不過僅當請求可以針對該緩衝區扮演的特定角色使用大容量緩存的時候。
當然，增加緩衝區大小是有代價的，下麵是一些大緩衝區可能帶來的影響。
•交換區
大容量緩衝區可能會導致會使用到操作系統級別的交換區從而造成性能緩慢，這取決於系統記憶體大小。通常情況下，MySQL伺服器在它所需的所有記憶體都來自物理記憶體的時候運行最快。當它使用到交換區的時候，性能顯著下降。
當為緩衝區分配的記憶體大小超過伺服器的物理記憶體大小的時候就會使用到交換區。有一些緩衝區是針對每個用戶線程的。要確定這些緩衝區究竟需要多少記憶體，可以用公式 max_connections * buffer_size 來計算。計算出所有緩衝區的記憶體和，並確保小於mysqld伺服器可以使用的記憶體大小。
•啟動時間
mysqld需要分配的記憶體越多，其啟動時間就越長。
•過期數據
我們還會有伸縮性問題，大部分時候是來自線程間的緩存共用。在這些場景中，擴充緩衝區做緩存會產生記憶體碎片。你通常會在伺服器運行數小時後發現記憶體碎片問題，該問題發生在舊的數據需要從緩衝區中移除以給新數據騰出空間的時候。這會導致高速運轉的伺服器變慢。
其次我們不僅關註性能優化選項（如優化器選項），我們還會關註一些控制高可用的選項。事務運行得越安全，就需要更多的檢查和更慢地執行性能。針對這些選項，只有在你可以為了性能犧牲安全的時候才可以調優它們。
在此，當你調優分配的時候，把性能作為整體來考慮尤為重要，因為每個選項都會影響整個伺服器。
MySQL伺服器提供了大量的選項，我們可以通過多種方式來對這些選項進行設置。
1.在my.cnf配置文件中進行設置
2.在使用命令行啟動伺服器的時候設置
3.在伺服器正在運行的時候使用變數來設置
其中這些變數有些是GLOBAL類型的，有些是SESSION級別的，本文將闡述一些可以創建或能夠產生變化的選項，能夠幫助你解決MySQL發生的一些問題。
我們可以根據變數的用途來把它們分成多個不同的組：用來設置伺服器的配置目錄。限制對硬體資源的使用，改變mysqld應該如何應對一個或多個場景等。依照它們分配時間的不同，它們也可以分為不同的組，例如：當伺服器啟動的時候，一個線程連接創建的時候， 或者當伺服器啟動一個特定操作的時候。
一、配置選項介紹
伺服器選項
當我們使用mysql客戶端可執行文件連接mysql伺服器時，需要指定IP地址、用戶名及密碼等信息，這些信息就是mysql客戶端程式啟動時的選項，通過這些選項可以連接到具體的mysql服務端上。
對於mysql服務端，在啟動時可以指定同時連入的客戶端數量、客戶端/服務端的通信方式、表的預設存儲引擎、查詢緩存的大小等信息，
例如：向伺服器指定目錄或文件，提醒伺服器是否打開一個特定的日誌等諸如此類的功能。
有倆個典型的故障排除情景是由這種選項導致的：
當某選項使用錯誤路徑的時候，你通常能夠在伺服器啟動的時候註意到此類問題。例如，如果你對 datadir 選項指定一條錯誤路徑，那麼 mysqld 會拒絕啟動並輸出有關的錯誤消息
[root@dba-test ~]# /export/servers/mysql/bin/mysqld --defaults-file=/export/restore/tool/general_mysql_backup_extract_tool/mysql-5.cnf --user=mysql --datadir=/export/restore/
2023-03-22T05:50:50.004095Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-03-22T05:50:50.004203Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2023-03-22T05:50:50.004237Z 0 [Note] /export/servers/mysql/bin/mysqld (mysqld 5.7.24) starting as process 4232 ...
2023-03-22T05:50:50.012400Z 0 [Note] InnoDB: PUNCH HOLE support available
2023-03-22T05:50:50.012440Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-03-22T05:50:50.012450Z 0 [Note] InnoDB: Uses event mutexes
2023-03-22T05:50:50.012458Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2023-03-22T05:50:50.012468Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-03-22T05:50:50.012475Z 0 [Note] InnoDB: Using Linux native AIO
2023-03-22T05:50:50.013078Z 0 [Note] InnoDB: Number of pools: 1
2023-03-22T05:50:50.013294Z 0 [Note] InnoDB: Using CPU crc32 instructions
2023-03-22T05:50:50.017366Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2023-03-22T05:50:50.027751Z 0 [Note] InnoDB: Completed initialization of buffer pool
2023-03-22T05:50:50.030019Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-03-22T05:50:50.040071Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
2023-03-22T05:50:50.040081Z 0 [Note] InnoDB: Need to create a new innodb_system data file 'ibdata2'.
2023-03-22T05:50:50.040290Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2023-03-22T05:50:50.040295Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2023-03-22T05:50:50.040298Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2023-03-22T05:50:50.040301Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2023-03-22T05:50:50.040305Z 0 [ERROR] InnoDB: Cannot open datafile './ibdata1'
2023-03-22T05:50:50.040311Z 0 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
2023-03-22T05:50:50.040316Z 0 [ERROR] InnoDB: InnoDB Database creation was aborted with error Cannot open a file. You may need to delete the ibdata1 file before trying to start up again.
2023-03-22T05:50:50.641211Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2023-03-22T05:50:50.641243Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2023-03-22T05:50:50.641256Z 0 [ERROR] Failed to initialize builtin plugins.
2023-03-22T05:50:50.641264Z 0 [ERROR] Aborting
2023-03-22T05:50:50.641291Z 0 [Note] Binlog end
2023-03-22T05:50:50.641384Z 0 [Note] Shutting down plugin 'CSV'
2023-03-22T05:50:50.641402Z 0 [Note] Shutting down plugin 'MyISAM'
2023-03-22T05:50:50.642273Z 0 [Note] /export/servers/mysql/bin/mysqld: Shutdown complete
但是，當然，如果你在系統啟動文件並用守護進程的方式啟動 mysqld ，那麼你無法在命令行中看到這些錯誤信息。
[root@exps-test2 ~]# /export/servers/mysql/bin/mysqld_safe --defaults-file=/export/restore/tool/general_mysql_backup_extract_tool/mysql-5.cnf --user=mysql --datadir=/export/restore/
Logging to '/export/restore/exps-test2.err'.
2023-03-22T05:53:47.138456Z mysqld_safe Starting mysqld daemon with databases from /export/restore
2023-03-22T05:53:47.347547Z mysqld_safe mysqld from pid file /export/restore/exps-test2.pid ended
在這種情況下，需要檢查錯誤日誌文件中的信息，或者，如果沒有任何錯誤日誌文件，那麼需要檢查操作系統日誌中有關mysqld相關的消息。
打開或關閉一個特定功能的時候。例如，關閉特性的選項。
當InnoDB引擎啟動失敗的時候，雖然伺服器成功啟動，但是InnoDB引擎並沒有成功載入。
如果SQL模式不包含 NO_ENGINE_SUBSTITUTION，我們仍然能夠成功創建引擎為InnoDB的表：
mysql> CREATE TABLE t1(f1 int) ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
我們需要檢查上面的警告信息，在這個例子中，我們在創建表的時候使用了錯誤的存儲引擎，因此我們在嘗試啟動 InnoDB 引擎的時候發生了錯誤：
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1286 | UnKnown table engine 'InnoDB' |
+---------+------+--------------------------------------------+
| Warning | 1266 | Using storage engine MyISAM for table 't1' |
+---------+------+--------------------------------------------+
所以如果某個依賴的功能出現問題，該功能是否存在於伺服器實例上。而錯誤消息清楚的說明瞭問題出在哪裡。
可更改伺服器運行方式的變數
這種類型的變數會影響 MySQL 伺服器如何處理用戶的輸入，舉一個簡單的例子來清楚地說明設這種這種類型的變數而帶來的效果。在這個例子中，將SQL模式設置為STRICT_TRANS_TABLES使無效數據插入事務性表的嘗試被拒絕，而不是被忽略。
有關硬體資源限制的選項
本分類中的配置選項能夠對硬體資源的利用施加限制。它們通常有兩個用途:優化性能及限制某此操作。當你希望對客戶端與伺服器之間的流量施加一些限制，或者防止拒絕服務攻擊時，剛纔提到的兩種用途中，後者對該場景非常有用。它能更好地讓特定用戶得到更優雅的錯誤，因為資源不足要好過 mysqld 進程終止，因為 mysqld 進程終止後便不能處理所有傳入的請求。
使用--no-defaults 選項
檢查 MySQL 伺服器是否沒有指定選項是較容易的，即，如果它的選項全部使用預設值如果你對於mysqld如何在未配置自定義選項的場景下運行有一個粗略的猜測,那麼你就可以使用--no-defaults 選項啟動 MySQL 伺服器並與你設置了自定義選項的情況進行比較
性能選項
這一類選項通常不會引發錯誤，但它們有可能對伺服器的性能產生巨大的影響。通常可以在不同的真實負載的生產伺服器上調整這些選項，直到找到一個適合特定環境的性能選項配置組合。
然而，當你選擇了這些選項的時候，有一種可能出現的情況會導致錯誤，所以從配置文件中移除或降低性能配置選項的權重是有意義的。這個場景是:你的伺服器遇到一個資源不足錯誤。最常見的情況涉及缺少記憶體或者文件描述符。如果伺服器存在此類問題，可以使用--no-defaults 辦法來找明設置了太大的選項。
二、整體優化思路
欲速則不達
調整 MySQL 伺服器的時候，至少是當你無法 100%確定你知道自己在做什麼的時候。
這意味著，如果你認為一組配置選項可以改變 MySQL 伺服器的行為，並使它更好地為你的應用程式提服務，那麼你可以改變一個選項，然後進行測試，如果結果是有效的，那麼你可以繼續添加其他選項，以此類推，直到你已經檢查完畢每一個相關選項。這可能是一個很緩慢的過程，但如果在這一過程中發生錯誤，你可以放心的回滾到本次變化之前，並能夠迅速地讓你的伺服器回到工作狀態。
在你調整記憶體緩衝區或者其他選項以限制對硬體資源的利用時，這種方式是非常重要的。但這種方式也同樣能夠用於伺服器行為配置選項的修改。即使你對變數到底在做什麼有著良好的認知，這也僅僅能讓你可以更容易地發現和修複某個錯誤，而不是在幾十種選項擇中找出錯誤的根源。
當你在使用該方法的時候，需要保存每一個測試結果。例如，如果你正在嘗試提升伺服器的性能，那麼你需要運行基準測試或測試查詢執行時間在配置選項改變前後的變化，然後重覆同樣的測試，再去修改每個選項。
通過基準測試迭代優化
你也許期望 (或者相信自己會期望) 通過建立一套基準測試方案，然後不斷迭代地驗證對配置項的修改來找到最佳配置方案。通常我們都不建議大家這麼做。這需要做非常名的工作和研究，並且大部分情況下潛在的收益是非常小的，這可能導致巨大的時間浪費而把時間花在檢查備份、監控執行計劃的變動之類的事情上，可能會更有意義。
即使更改一個選項後基準測試出現了提升，也無法知道長期運行後這個變更會有什麼副作用。基準測試也不能衡量一切，或者沒有運行足夠長的時間來檢測系統的長期穩定性修改就可能導致如周期性性能抖動或者周期性的慢查詢等問題。這是很難察覺到的。
有的時候我們運行某些組合的基準測試，來仔細驗證或壓測伺服器的某些特定部分，使得我們可以更好地理解這些行為。一個很好的例子是，我們使用了很多年的一些基準測試，用來理解InnoDB 的刷新行為，來尋找更好的刷新演算法，以適應多種工作負載和多種硬體類型。我們經常測試各種各樣的設置，來理解它們的影響以及怎麼優化它們。但這不是一件簡單的事一一這可能會花費很多天甚至很多個星期一一而且對大部分人來說這沒有收益，因為伺服器特定部分的認識局限往往會掩蓋了其他問題。例如，有時我們發現，特定的設置項組合，在特定的邊緣場景可能有更好的性能，但是在實際生產環境這些配置項並不真的合適，例如，浪費大量的記憶體，或者優化了吞吐量卻忽略了崩潰恢復的影響。
如果必須這樣做，我們建議在開始配置伺服器之前，開發一個定製的基準測試包。你必須做這些事情來包含所有可能的工作負載，其至包含一些邊緣的場景，例如很龐大很複雜的查詢語句。在實際的數據上重放工作負載通常是一個好辦法。如果已經定位到了一個特定的問題點一一例如一個查詢語句運行很慢一一也可以嘗試專門優化這個點，但是可能不知道這會對其他查詢有什麼負面影響。
最好的辦法是一次改變一個或兩個變數，每次一點點，每次更改後運行基準測試，確保運行足夠長的時間來確認性能是否穩定。有時結果可能會令你感到驚訝，可能把一個變數調大了一點，觀察到性能提升，然後再調大一點，卻發現性能大幅下降。如果變更後性能有隱患，可能是某些資源用得太多了，例如，為緩衝區分配太多記憶體、頻繁地申請和釋放記憶體。另外，可能導致 MySQL 和操作系統或硬體之間的不匹配。例如，我們發現 sort_buffer_size的最佳值可能會被 CPU 緩存的工作方式影響，還有 read_buffer_size需要伺服器的預讀和 I/O 子系統的配置相匹配。更大並不總是更好，還可能更糟糕。一些變數也依賴於一些其他的東西，這需要通過經驗和對系統架構的理解來學習。
三、優化配置
影響伺服器與客戶端行為的選項
伺服器相關選項
這些選項影響所有的連接與語句。
•限制與 max_ 變數*
如果你發現mysqld限制了你發送語句或返回結果的大小，你只須檢查它們的值。
•許可權
如果語句失敗，你的用戶是否有執行它的許可權，或特定資料庫或者表的許可權。
•SQL模式
當遇到“奇怪”的查詢結果時，請檢查 SQL 模式並分析它是否影響查詢。
•字元集與排序規則
每當你懷疑某些關於字元集與排序規則的事情有錯誤時，請運行以下兩個查詢，然後分析查詢結果與上下文。
mysql> SHOW VARIABLES LIKE '%char%';
mysql> SHOW VARIABLES LIKE '%coll%';
通常的安全規則是獲取所有的 character_set_* 變數、 collation_* 變數，並且創建選項相同的任何表與一起協同的連接。設置客戶端選項最簡單方式使用 SET NAMES 語句。
當在排序或者比較過程中遇到問題時，請檢查字元集選項與表的定義。
•操作系統處理 lower_case* 參數
lower_case_filesystem 與 lower_case_table_names 選項跟字元集選項的作用非常相似。這些變數確定操作系統如何處理資料庫對象的大小寫情況。
最好不要修改它們的值，特別是操作系統不區分大小寫時。
•初始 SQL
這些選項確定伺服器在不同的時間是否應該自動執行某些 SQL 語句
如果指定，此變數將命名一個文件，其中包含在啟動過程中要讀取和執行的 SQL 語句。每條語句必須在一行中，並且不應包含註釋。
伺服器為每個連接的客戶端執行的字元串。該字元串由一個或多個 SQL 語句組成，以分號字元分隔。
該變數類似於init_connect，但是是一個字元串，每次複製 SQL 線程啟動時由副本伺服器執行。字元串的格式與變數的格式相同init_connect。該變數的設置對後續START SLAVE語句生效。
這個重要選項限制 MySQL 伺服器同時打開文件句柄的數量。限制數量越高，打開的表文件與臨時表越多，因此處理的併發連接量數越多。如果這個限制在你的環境中設置得太低，在你試圖連接、打開一個表或者執行一個需要創建臨時表的查詢時就會出現錯誤。
是否向錯誤日誌生成額外的警告消息。
當此選項打開(非零)時，就會在伺服器的錯誤日誌文件中寫人警告信息。它們不是在 SOL 執行期間發出的警告,而是顯示伺服器內到底是怎麼回事的調試消息。如果設置為 2，此選項告訴伺服器記錄連接錯誤。當你正在對客戶端無法連接或正在失去連接的情況做故障排除時，這非常重要。日誌並非總是能找到問題所在，但其警告消息往往對要做什麼能給出一些啟發。當使用同步複製時，在主伺服器上開啟此選項非常重要，因為你能確定從伺服器 IO 線程何時失去連接。反過來它是網路故障的一種癥狀，這在將來可能導致更嚴重的問題。
當在從伺服器上設置為 1(預設值) 時，它將輸出自己的診斷消息，例如:在二進位日誌和中繼日誌中的位置及其複製狀態。從 5.1.38 版本開始，在基於語句模式的同步複製中，需要啟用此選項，以便在從伺服器輸出不安全語句的信息。( 5.1.38版本之前，從伺服器在任何情況下都會輸出此類消息。) 從 5.1.38 版本開始，可以關閉此選項( 設置為 0)，來丟棄你確定不需要該消息的日誌。
伺服器將錯誤、警告和註釋消息寫入錯誤日誌的詳細程度。下表顯示了允許的值。預設值為 3。
| log_error_verbosity value | 允許的消息 |
|---|---|
| 1 | 錯誤訊息 |
| 2 | 錯誤和警告消息 |
| 3 | 錯誤、警告和信息消息 |
log_error_verbosity在 MySQL 5.7.2 中添加。它優於舊的系統變數，應該使用它來代替舊的log_warnings系統變數。log_warnings有關該變數如何與 相關的信息，請參閱 的描述log_error_verbosity。特別是，賦值給log_warnings賦值給log_error_verbosity，反之亦然。
複製選項
這些選項確定了主從伺服器之間的關係。
•binlog-* 與 replicate-* 過濾器
通過 binlog-do-、 replicate-do-、 binllog-ignore-* 與 replicate-ignore-* 選項，在複製過程中， MySQL有能力過濾對象。 binlog-* 選項減少在主伺服器上寫入二進位日誌文件的事件，而 replicate-* 指定在從伺服器上記錄到二進位日誌。從伺服器還有replicate-wild-do-* 與 replicate- wild-ignore* 選項，二者允許通過模式匹配，指定哪些應該或哪些不應該同步。
•二進位日誌格式a. binlog_format
變數允許你選擇複製的格式：STATEMENT、ROW、或MIXED。這是一個動態變數，它能在 SESSION 級別調整。
◦binlog_direct_non_transactional_updates
此選項指定何時非事務表更新應該寫入二進位日誌。
預設情況下，當使用事務時，MySQL將非事務表的更新寫入事務緩存里，僅當事務提交後，才把緩存刷新到二進位日誌里。這樣做以便從伺服器更有可能與主伺服器數據最終一致，即使依賴事務表中的數據來更新非事務表，並且主伺服器在許多併發線程中同步更新相同的表。
這是一個動態變數，可以在 SESSION 級別改變它，所以，可以在特定語句下使用它。它的工作原理決定了它只能在基於語句模式的複製中オ生效。
•log_bin_trust_function_creators
這個選項告訴 mysqld 當用戶沒有 SUPER 許可權卻試圖創建一個不確定的函數時，不要觸發警告。
•binloig_cache_size 與類似選項
此條目包括以下選項：
在寫入二進位日誌之前，這些緩存保存在事務期間提交的事務與非事務語句。如果一個事務需要超過這個位元組數的記憶體，伺服器會生成一個 “Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage” 錯誤。
檢查 Binlog_cache_use、Binlog_stmt_cache_use、Binlog_cache_disk_use 與 Binlog_stmt_cache_disk_use狀態變數來找出 binlog緩存使用的頻率以及事務大小超過Binlog_cache_use與 Binlog_stmt_cache_use的頻率。當事務大小操作緩存大小時，將會創建臨時表來存儲事務緩存。
此選項允許從伺服器SOL線程即使遇到某類錯誤時還能運行。例如，主伺服器運行在寬鬆的 SQL 模式而從伺服器卻有一個嚴格的SQL模式，當插入字元串到整數欄位時，由於數據格式不一致，而報告1366( ERROR1366(HY000): Incorrect integer value)錯誤，可以設置 slave skip errors ，以便從伺服器不會出現故障。
這個選項可能導致主伺服器從伺服器數據不一致而又很難診斷，所以，如果你遇到這樣的問題，請檢查該選項是否沒有設置。
該選項使從伺服器伺服器只讀。這意味著，僅僅只有從庫SQL線程才能更新其數據，而其他連接只能讀數據。該選項對於保持從伺服器數據的一致很重要。然而，這個選項並不能限制具有 SUPER 許可權的用戶更改表。另外，所有用戶仍允許創建臨時表。
該選項使伺服器甚至禁止擁有 SUPER 。
引擎選項
本節主要介紹 InnoDB 相關選項。
該選項用於生成自動增量值的鎖定模式。
Innodb預設在共用表空間中存放表和索引數據。使用此選項，你可以告知它將表的索引和數據存放在單獨的文件里。共用表空間仍然用來存放表定義。此選項在設置後創建的表上生效；之前創建的表依然使用共用表空間。
此變數定義了 InnoDB 是如何處理 LOCK TABLES 語句發出的表鎖請求。預設（當設置了這刻返回並且內部將表鎖住。當關閉時（設置為0），它會接收 LOCK TABLE 語句，線程直到所有鎖釋放後才從 LOCK TABLES ...WRITE返回。
這是 InnoDB 等待行鎖直到放棄的秒數。在 innodb_lock_wait_timeout秒後，它會返回錯誤 “ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction” 至客戶端。我經常看到人們將這個變數設得很大來防止查詢失敗，這隻會導致更嚴重的問題,因為許多阻塞的事務會互相鎖住。嘗試在應用程式層處理鎖等待錯誤，並不要將它設置得過高。此參數的最佳值取決於應用程式，通常應該大約為正常事務所消耗的時間。它的預設值是50秒，對於需要立即返回結果的應用程式有些大。
當査詢因鎖等待錯誤而中斷時，只有最後一條語句回滾了，整個事務還沒有中止。如果將選項設置為1你將會改變此行為。這種情況下事務會在鎖等待超時後立刻回滾。
指定 InnoDB 是否應該使用 Linux下原生的AIO介面，或者是自己來實現,稱作 “模擬AIO“。如果設置 innodb_use native_aio，lmoD將分發IO請求至內核。這提高了可擴展性因為比起模擬AIO新內核能夠處理更多的並行IO請求。
此選項預設開啟,在正常操作下不應該改變。
運行大量InnoDBI/O 線程，尤其是在同一臺伺服器上運行多個此類實例，可能會超出 Linux 系統的容量限制。在這種情況下，您可能會收到以下錯誤：
EAGAIN: The specified maxevents exceeds the user's limit of available events.
您通常可以通過將更高的限制寫入 來解決此錯誤/proc/sys/fs/aio-max-nr。
•innodb_locks_unsafe_for_binlog
此變數定義 InnoDB如何使用間隙鎖來搜索和掃描索引。預設值（設為0）下，間隙鎖開啟。如果設為1，大多數操作下會禁用間隙鎖。其工作原理類似於隔離級別中的 READ COMMITTED，但由於不太好調節應儘量避免。即使它允許你來處理鎖問題，當並行事務插入新行至間隙時它也會帶來新的問題。所以推薦用 READ COMMITTED替代它。這個變數不能設置為 SESSION 級別,它會彩響所有事各
連接相關的選項
超時
這是 InnoDB 等待行鎖直到放棄的秒數。在 innodb_lock_wait_timeout秒後，它會返回錯誤 “ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction” 至客戶端。我經常看到人們將這個變數設得很大來防止查詢失敗，這隻會導致更嚴重的問題,因為許多阻塞的事務會互相鎖住。嘗試在應用程式層處理鎖等待錯誤，並不要將它設置得過高。此參數的最佳值取決於應用程式，通常應該大約為正常事務所消耗的時間。它的預設值是50秒，對於需要立即返回結果的應用程式有些大。
它適用於元數據鎖。這個鎖對所有需要元數據鎖的操作有效：DML（數據操縱語言語句,如 INSERT、 UPDATE和 DELETE），DDL、LOCKTABLES等。預設值是3153600秒，也就是一年。所以預設情況下,有效MDL鎖永遠不會解除。然而，可以更改值為大於1秒的任何值。它是一個動態變數,可以在SESSION級別更改。
這個超時使用在 MySQL伺服器在響應之前等待連接數據包的秒數。從5.1版本開始,此值預設設置為10秒。
伺服器在關閉互動式連接之前等待其活動的秒數。
在斷開連接前等待任何客戶端中活動的時間。如果客戶端是互動式的並且 interactive_timeout的值不同於wait_timeout,則以 interactive_timeout為準。
來自主機的連續連接請求在沒有成功連接的情況下被中斷後max_connect_errors，伺服器阻止該主機進一步連接。如果在先前的連接中斷後，在少於max_connect_errors嘗試次數的時間內成功建立了來自主機的連接，則主機的錯誤計數將被清除為零。要取消阻止被阻止的主機，請刷新主機緩存。
允許的最大併發客戶端連接數。最大有效值是的有效值和實際設置的值中的較小者。
檢查客戶端連接時是否解析主機名。如果這個變數是OFF，mysqld 在檢查客戶端連接時解析主機名。如果是ON，mysqld 只使用 IP 號碼；在這種情況下，Host授權表中的所有列值都必須是 IP 地址。
從客戶端寫入 MySQL伺服器等待應答的時間。例如,此超時會在客戶端執行大的插入操作時起作用。
客戶端從伺服器中讀取時等待應答的時間。例如,當客戶端發送一個 SELECT查詢讀取結果,如果客戶端等待一段時間未收到數據,這個超時會斷開此連接。如果客戶端需要在處理結果前做一些工作,檢查工作的持續時間是否長於這個超時。
與安全相關的選項
--skip-grant-tables導致伺服器不讀取mysql系統資料庫中的授權表，從而在根本不使用特權系統的情況下啟動。這使任何有權訪問伺服器的人都可以不受限制地訪問所有資料庫。
不允許使用GRANT語句來創建用戶,除非用戶有mysql.user 表的INSERT許可權才可以。
如果啟用此變數，則伺服器會阻止嘗試使用以舊（4.1 之前）格式存儲密碼的帳戶的客戶端連接。啟用此變數以防止所有使用舊格式的密碼（因此通過網路進行不安全的通信）。
此變數已棄用；希望在未來的 MySQL 版本中將其刪除。它始終處於啟用狀態，嘗試禁用它會產生錯誤。
限制LOAD FILE函數以及LOAD DATA和SELECT… INTO OUTFILE語句只能使用指定目錄。
與性能相關的選項
緩衝區和最大值
這是為連接操作分配的最小緩存大小，這些連接使用普通索引掃描、範圍掃描，或者連接不使用索引。兩表之間進行全連接時分配緩存。因此，連接兩個表的一條查詢分配一塊緩存，連接3個表的一個分配兩塊查詢緩存，以此類推。這個選項可在 SESSION級使用，能對於特定連接設置。
為了查出是否需要增加join_buffer_size,可以檢查 Select_scan狀態選項,它包括第一張表執行完整掃描的連接數量,同樣 Select_ full_range_ join,它包含使用範圍搜索的連接數量。這些狀態變數的值不會隨著 join buffer size的值的變化而變化,這樣你可以利用它們來查出是否需要大的 join buffer size,而不是衡量該值改變後的有效性
伺服器在客戶端連接後立刻創建的緩存大小,用來保存請求和結果。根據需要這個大小可以增長至 max_allowed_packet正常情況下不用改變預設值(16384位元組)，但當設置 max_connections選項時要記住此值。
此緩存為語句解析和執行而分配。語句間緩存是不釋放的。如果運行複雜查詢,增加緩存是合理的,這樣 mysqld不會在執行查詢的時候在分配記憶體上耗時。增加此大小到最大查詢的位元組數。
該變數是MySQL讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySQL會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一緩衝區的大小。如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其性能。
此變數用來控制在排序和發送結果至客戶端之間存放讀取結果的大小。大的值能提高包含ORDER BY的查詢的性能。
每個線程需要排序的時候會分配此緩存。查明你是否需要增加此緩存的大小，檢查 ort merge_ passes狀態變數。也可以檢查查明你執行了多少個排序操作。sort_range、 sort_rows和sort_scan來這些狀態變數顯示了排序操作的數量。
為了找出緩存合適的大小，需要檢查一條或多條查詢排序的行數，並乘以行大小。或者簡單地設置不同的值直到sort_merge_passes停止增長。
sort_buffer_size緩存經常會分配,所以大的 GLOBAL值會降低性能而不是增加性能。因此,最好不要設置此選項為 GLOBAL變數,而是當需要時使用 SET SESSION增加它。
如果啟用，則sql_buffer_result強制將語句的結果SELECT放入臨時表中。這有助於 MySQL 儘早釋放表鎖，並且在需要很長時間才能將結果發送到客戶端的情況下非常有用。預設值為OFF。
為了找出查詢是否在發送結果集上消耗過多時間，執行 SHOW PROCESSLIST來檢查查詢在“ Sending data”狀態下的時間。
為將來使用緩存起來的線程數量。當一個客戶端斷開連接時,通常其線程也被銷毀。如果該選項設置為正值 N,那麼連接斷開後 N個線程將被緩存起來。在具有良好線程實現的系統上，該選項不能顯著地提高性能，但是，對於一個應用使用成百上千連接的情況，還是很有用的。
每個線程的棧大小。如果該變數設置過小，將會限制SQL語句的複雜性、存儲過程的遞歸深度，以及伺服器上其他記憶體消耗型的操作。對於大部分安裝來說,預設值(32位系統是192KB;64位系統是256KB)就可以。如果類似"Thread stack overrun"的錯誤消息，請增大該參數。
記憶體中，內部臨時表的最大值。伺服器預設設置為max_heap_table_size和tmp_table_size二者中的最小值。如果你有足夠的記憶體,並且Created_tmp_disk_tables狀態變數在增大，請增大該變數。把需要臨時表的所有結果集放在記憶體中，可以大大提高性能。
MySQL伺服器存儲查詢及其結果集的緩存大小。預設情況下，查詢緩存是禁用的。於5.7.20版本之後棄用。
存儲在緩存中的表定義的數量。當表數量很大時，可以增大該值。如果需要，可以調整該值，以便最近的表刷新( FLUSH TABLES)後,保持 Opened_table_definitions於或等於Open_table_definitions。
存儲在緩存中的表描述符的數量。調整該值,以便 Opened_tables仍小於或等於Open_tables。
控制優化器的選項
這些變數可以在 SESSION 級別設置，所以，你可以實驗它們是怎樣影響特點查詢的。
如果該變數設為on，優化器刪除即時搜索發現的不太有效的計劃；如果設置為off，優化器使用詳盡的搜索。預設值為1(on)。如果你懷疑優化器選擇的不是最優計劃，你可以改變它的值。
優化器搜索的最大深度。該值越大，優化器越有可能為複雜的查詢找到最優計劃。提高該值的代價就是優化器在搜索計劃時的時間開銷增大。如果設置為0，伺服器會自動選擇一個合理的值，預設值為62(最大值)
該變數變數可以控制優化器的行為。
啟用或禁用索引合併優化，該優化幾個從合併掃描中獲取行記錄,並把結果合併為一條記錄。在 EXPLAIN的結果輸出中，Merge列顯示的就是這個選項。
啟用或禁用索引合併交叉訪問演算法
當 where從句包含key表示的範圍條件並且與AND關鍵字時，將會使用該演算法。
如：key_coll < 10 AND key_col2 = 'foo'
使 key_col2 = 'foo' 得到唯一值，優化器也把它當做範圍條件
啟用或禁用索引合併聯合訪問演算法，當 where從句包含 key 表示的範圍條件和OR關鍵字時，會用到該演算法。
如：key_col1 = 'foo' OR (key_co12 = 'bar' AND key_col3 ='baz')
啟用或禁用索引合併排序聯合訪問演算法，當 where從句包含key表示的範圍條件和OR關鍵字時，將使用該演算法,但是,不會應用索引合併聯合訪問演算法,如(key coll >10 OR key co12 ='bar)AND key co13
對於估計可能超過一定限制的 select 語句，(該選項)阻止優化器進行優化(如查看多於 max_join_size 的行記錄 )。當調試找出沒有使用索引的查詢時，該選項會有很大幫助。
當無法使用索引時，如果對 ORDER BY 進行條件優化，MySQL 將使用文件排序演算法。該演算法有兩個變體。原始演算法讀取所有匹配的行記錄，在緩存里存儲戰對的鍵值和行指針，該緩存大小受 sort_buffer_size 限制。緩存中的值排序後，該演算法再次讀取表記錄，但是，這次按照一定順序讀取。該演算法的缺點是兩次讀取行記錄。
改進後的方法是讀取整個行記錄到緩衝區，然後排序鍵值，從緩衝區中讀取行記錄。該方法的問題是結果集通常超過sort_buffer_size，所以，對於大數據集，磁碟I/O 操作使得該演算法很慢。max_length_for_sort_data 變數限制鍵值和行記錄指針對的大小，因此原始演算法適用於鍵值和行指針對中額外列的總大小超過了該限制的情況。
磁碟活動多，並且 CPU 活動少是需要調低該變數的一個信號。
根據表掃描必須檢查的記錄行數量，為使用鍵值而不是表掃描設置閾值。設置該參數為一個較小的值，比如 100，在表掃描時，可以強制優化器優先查看索引。
設置對 BLOB 或 TEXT 值排序時用到的初始位元組數，後面的部分將被忽略。
與引擎相關的選項
自適應InnoDB哈希索引是啟用還是禁用。根據您的工作負載，可能需要動態啟用或禁用自適應哈希索引以提高查詢性能。由於自適應哈希索引可能並非對所有工作負載都有用，因此在啟用和禁用它的情況下使用實際工作負載進行基準測試。
innodb_additional_mem_pool_size
InnoDB 用來存儲數據字典信息和其他內部數據結構的記憶體池的大小。在 MySQL 5.7.4 中刪除。
用於生成 自動增量值的 鎖定模式 。允許的值為 0、1 或 2，分別表示傳統、連續或交錯。預設設置為 1（連續）。
InnoDB 為存儲數據、索引、表結構、自適應散列索引等分配的記憶體大小，這是影響 innodb 性能最重要的選項。可以將其設置為物理記憶體的 80%。理想情況下，該緩衝區足夠大，以致可以包含所有活動的 InnoDB 表和額外空間。同時，也要把其他緩衝區計算在內，尋找一個好的平衡。
匹配Innodb buffer pool %的，狀態變數顯示 InnoDB 緩衝池的當前狀態。
該變數的設置需要經過實際環境中非常嚴謹的驗證。
該選項設置 InnoDB 緩衝池應切分的實例數量。每個實例有它自己的空閑列表、刷新列表、使用LRU演算法的存儲對象的列表，以及其他數據結構，並且受到自身互斥體的保護。設置該變數大於 1，可以提高大型系統的併發性能。每個實例的(緩中區)大小是 innodb_buffer_pool_size/innodb_buffer_pool_instances，並且至少是1GB。如果 innodb_buffer_pool_size小於 1GB，該選項不生效。
innodb_buffer_pool_instances 切分緩衝區互斥體，所以，如果有 8個或者更多並行SESSION 同時訪問 InnoDB 緩衝池，該選項可以設置為 4~16。該選項取決於innodb_buffer_pool_size值和可用的記憶體。
預設情況，InnoDB 使用校驗和驗證磁碟上所有頁。該選項可以立即確定數據文件是否由於磁碟壞道或者其他原因而損壞。通常需要開啟該功能，但是，在很少的情況下，當不關心數據時( 比如，只讀從伺服器，不提供(線上) 服務，只做備份 )，關閉該功能，可以提升性能。innodb_checksums已棄用，替換為innodb_checksum_algorith。
指定如何生成和驗證存儲在InnoDB表空間的磁碟塊中的校驗和。
可以同時提交事務的線程數量，預設值為 0( 沒有限制 )。
InnoDB 內部同時運行的線程數量，不要把它和 MySQL 伺服器創建的連接線程的數量混淆。預設值是 0:不限制並行或不檢查並行。
儘管大量線程並行運行一般意味著高性能，但是，如果同時並行運行很多用戶會話，你可能遇到互斥體爭用，如果同時運行的用戶線程不超過 16 個，通常不用擔心該參數。如果有更多用戶 SESSION，可以通過查詢 Performance Schema 或者SHOW ENGINE INNODBMUTEX 來監控互斥鎖。
如果出現互斥體爭用，可以嘗試限制該變數為 16 或 32，或者把 mysqld 進程放置到 Linux 的任務集里或者 Solaris 的處理器集里，同時限制它為更少的內核而不是所有內核。
當允許一個線程進人InnoDB 時，它接收 innodb_concurrency_tickets張併發“票”( ticket)，這些票允許線程離開和重新進人 InoDB，直到它使用完這此票。
預設值是 500。用完這些票後，把線程放置到等待隊列中，以獲取一組新票。
預設情況下，InnoDB 分兩次存儲數據: 第一次寫人雙寫緩衝，第二次寫人數據文件。像innodb_checksums一樣，對於數據安全不是最重要的場景，這個安全選項可以關閉 of，以提升性能。
Innodb dblwr_writes 和 Innodb_dblwr pages written 狀態變數分別顯示兩次寫操作的數量和寫頁的數量。
•innodb_flush_log_at_trx_commit
定義何時把更改寫人(重做日誌文件以及刷新到磁碟。如果設置為 1(預設值)在每個事務提交時更改均會寫人和刷新到磁碟。為了得到更好的性能，可以設置該值為 0( 每秒寫人日誌文件和刷新到磁碟，而每個事務提交時，不做操作)或者2( 每次提交事務時寫人日誌文件，但是，每秒刷新到磁碟 。註意，只有該選項為1時才符合 ACID 事務要求的。
Innodb_os_log_fsyncs 狀態變數存儲 fsync() 到日誌文件的操作次數。Innodb_os_log_pending_writes 包含掛起的 fsync0寫次數。Innodb_log_writes 和 Innodb_os_log_pending_writes分別包含寫入次數和掛起的寫次數。
預設情況下，fdatasync()是用來刷新數據文件的，fsync()是用來刷新日誌文件到磁碟的，該值可以更改為以下值中的一個:
O_DSYNC
操作系統使用 O_SYNC 打開和刷新日誌文件，同時使用 fsync()刷新數據文件
O_DIRECT
操作系統使用 O_DIRECT打開數據文件，並且使用 fsync()刷新數據文件。更改innodb_flush_method
