1. 除非遇到異常情況，否則不需要調整配置

1.1. 不要“調優”伺服器，不要使用比率、公式或“調優腳本”作為設置配置變數的基礎

1.1.1. 在互聯網上搜索配置建議並不總是一個好主意，你會在博客、論壇等找到很多糟糕的建議

1.1.2. 很難判斷誰是真正的專家

1.1.3. 不要相信流行的記憶體消耗公式

1.2. 可靠的、有信譽的MySQL服務提供商通常比簡單的互聯網搜索結果更安全，因為那些需要擁有滿意的客戶的人可能正在做正確的事情

1.2.1. 即使是他們的建議，在沒有經過測試和理解的情況下進行應用也可能是危險的，因為它可能針對的是一種與你不同的情況，而你卻沒有理解

1.3. MySQL的不同版本會刪除、棄用和更改一些選項，欲瞭解詳細信息請查看相關文檔

1.4. 應該始終通過閱讀相關的官方手冊來檢查任何更改並仔細測試

1.5. MySQL有許多可以更改但不應該更改的設置

1.5.1. MySQL的預設設置是有充分理由的

1.5.2. 修改配置的潛在缺點可能是巨大的

1.5.3. 很多預設設置都是安全的，很多人都會直接使用。這使預設設置成為測試最徹底的設置。當沒必要改變這些設置而改變它們時，可能會引起意想不到的錯誤

1.5.4. 節省時間和避免麻煩的好方法是使用預設設置，除非你明確知道不應該使用預設設置

1.6. 更好的做法是正確地配置基本設置（在大多數情況下，只有少數設置是重要的），並將更多的時間花在schema優化、索引和查詢設計上

1.7. 如果問題是由伺服器的某個部分引起的，而該部分的行為可以通過配置選項進行糾正，那麼可能需要對其進行更改

1.8. 應該只在發現它們解決的特定性能問題時，才設置它們

1.9. 如果需要改進配置，應該會在查詢響應時間中體現出來

1.10. 最好從查詢及其響應時間開始分析，而不是從配置選項開始

1.10.1. 節省很多時間，避免很多問題

2. 專用資料庫伺服器

2.1. 可以設置的最佳選項是innodb_dedicated_server

2.1.1. 可以處理90%的性能配置

2.1.2. 配置了4個額外的變數（innodb_buffer_pool_size、innodb_log_file_size、innodb_log_files_in_group和innodb_flush_method）

2.1.3. 通常會占用50%～75%的記憶體

2.1.3.1. MySQL只需要少量的記憶體就能保持一個連接（通常是一個相關的專用線程）打開

2.2. 無法使用innodb_dedicated_server

2.2.1. innodb_buffer_pool_size

2.2.1.1. InnoDB緩衝池大小

2.2.1.2. 需要的記憶體比其他任何組件都多

2.2.1.3. 不僅緩存索引，還緩存行數據、自適應哈希索引、更改緩衝區、鎖和其他內部結構等

2.2.1.4. InnoDB嚴重依賴緩衝池，應該確保為其分配足夠的記憶體

2.2.1.5. 大型緩衝池會帶來一些挑戰，比如更長的關閉時間和預熱時間

2.2.1.6. 當MySQL再次啟動時，緩衝池緩存是空的，也稱為冷緩存

2.2.1.7. 預設情況下，innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup這兩個配置可以配合使用，以在啟動時預熱緩存池

2.2.2. innodb_log_file_size

2.2.2.1. 日誌文件大小

2.2.3. 解決了我們所看到的絕大多數實際配置問題

2.3. 應該設置一些安全選項

2.3.1. 通常不會提高性能，只會避免問題

3. MySQL的配置

3.1. 需要永久使用的任何設置都應該寫入全局配置文件，而不是在命令行中指定

3.2. 將所有配置文件保存在一個地方也是一個好主意，這樣可以方便地檢查它們

3.3. 一定要知道伺服器的配置文件在哪裡

3.3.1. Debian伺服器上預設不存在/etc/my.cnf，而是會在/etc/mysql/my.cnf中查找配置

3.4. 配置文件採用標準INI格式，被分為多個部分，每個部分都以一行包含在方括弧中的該部分名稱開頭

3.5. 配置設置全部用小寫字母書寫，單詞之間以下畫線或短橫線分隔

3.5.1. 建議選擇一種風格並始終如一地使用它

3.6. 全局作用域

3.7. 會話作用域

3.8. 許多會話作用域的變數都有相應的全局變數，可以將相應的全局變數的值視為會話變數的預設值

3.9. 動態配置變數

3.9.1. 很多變數（但不是全部）還可以在伺服器運行時進行更改

3.9.2. 如果重新啟動MySQL，即使使用了SET GLOBAL來更改全局變數，它也將恢復到配置文件中的狀態

3.9.3. 必須同時管理MySQL的配置文件和運行時配置，並確保它們保持同步

3.10. MySQL 8.0引入了一個名為持久化系統變數的新功能

3.10.1. 新的語法SET PERSIST允許在運行時設置一次值，MySQL將把這個設置寫入磁碟，以便在下次重啟後繼續使用該值

3.11. table_open_cache

3.11.1. 設置此變數不會立即生效：下一次線程打開表時，MySQL會檢查變數的值

3.11.2. 如果該值大於緩存中的表的數目，線程可以將新打開的表插入緩存

3.11.3. 如果該值小於緩存中的表的數目，MySQL將從緩存中刪除未使用的表

3.12. thread_cache_size

3.12.1. 設置此變數不會立即生效：下一次關閉連接時，MySQL會檢查緩存中是否有空間來存儲線程

3.12.2. 如果有，則緩存線程以供其他連接將來重用

3.12.3. 如果沒有，則將線程終止而不是緩存它

3.12.4. 只有當查詢需要時，MySQL才會為該緩衝區分配記憶體，而且會立即分配此變數指定的整塊記憶體

3.12.5. 每個處於線程緩存或休眠狀態的線程通常使用大約256KB記憶體

3.12.6. 通常應該保持線程緩存足夠大，這樣Threads_created就不會經常增加

3.13. open_files_limit選項

3.13.1. 典型的Linux系統中，我們將其設置得儘可能大

3.13.2. 在現代操作系統中，打開文件句柄的成本很低

3.13.3. 如果這個設置不夠大，就會看到經典的24號錯誤，“too many openfiles”

3.14. 設置變數時要小心。並不總是越多越好

3.15. 理想情況下，應該使用版本控制系統來跟蹤配置文件的更改

3.16. MySQL並不是一個嚴格控制記憶體分配的資料庫伺服器

3.16.1. 事實是，你不能給MySQL的記憶體消耗設定上限

4. I/O行為

4.1. InnoDB不僅允許你控制其恢復方式，還允許控制其打開和刷新數據的方式，這將極大地影響恢復和總體性能

4.2. InnoDB使用日誌來降低提交事務的成本

4.3. InnoDB假定它使用的是傳統的磁碟，隨機I/O比順序I/O的開銷要大很多，因為隨機I/O需要在磁碟上尋找正確的位置，並等待將所需的磁碟部分旋轉到磁頭下

4.4. InnoDB最終必須將更改的數據寫入數據文件

4.4.1. 日誌的大小固定，採取的是迴圈寫入的方式

4.4.1.1. 當到達日誌的末尾時，它會環繞到日誌的開頭

4.4.1.2. 如果日誌記錄中包含的更改尚未應用於數據文件，則無法覆蓋日誌記錄，因為這將刪除已提交事務的唯一永久記錄

4.5. 日誌文件的總大小由innodb_log_file_size和innodb_log_files_in_group控制，這對寫入性能非常重要

4.6. 當緩衝區滿了、事務提交時，或者每秒1次（這三個條件以先滿足者為準），InnoDB會將緩衝區刷新到磁碟上的日誌文件中

4.7. 不需要將緩衝區設置得太大

4.7.1. 建議的範圍是1～8M B

4.8. innodb_flush_log_at_trx_commit

4.8.1. 1

4.8.1.1. 每次事務提交時，將日誌緩衝區寫入日誌文件，並將其刷新到持久存儲中

4.8.1.2. 預設的（也是最安全的）設置

4.8.1.3. 保證你不會丟失任何已提交的事務，除非磁碟或操作系統“假裝”進行刷新操作（沒有將數據真正寫入磁碟）

4.8.1.3.1. 如果驅動器斷電，數據仍可能丟失

4.8.2. 0

4.8.2.1. 每秒定時將日誌緩衝區寫入日誌文件，並刷新日誌文件，但在事務提交時不做任何操作

4.8.3. 2

4.8.3.1. 與0設置最重要的區別是，如果只是MySQL進程崩潰，設置為2不會丟失任何事務。但是，如果整個伺服器崩潰或斷電，仍然可能丟失事務

4.8.4. 設置為0和2通常會導致最多1秒的數據丟失，因為數據可能只存在於操作系統的緩存中

4.9. 高性能事務需求的最佳配置是將innodb_flush_log_at_trx_commit設置為1，並將日誌文件放在具有備用電池的寫緩存和SSD的RAID捲上，這既安全又非常快

4.10. 最好為日誌文件和數據文件分別提供一個配置選項，但目前是組合在一起的

4.11. 如果你使用的是類UNIX操作系統，並且RAID控制器有備用電池的寫緩存，我們建議使用O_DIRECT

4.12. 如果不是，則default或O_DIRECT都可能是最佳選擇，具體取決於應用程式

5. InnoDB表空間

5.1. 表空間本質上是一個虛擬文件系統，由磁碟上的一個或多個文件組成

5.2. 包含了Undo日誌（重新創建舊行版本所需的信息）、修改緩衝區、雙寫緩衝區和其他內部結構

5.3. 對日誌文件也非常嚴格

5.4. innodb_file_per_table

5.4.1. 提供了額外的可管理性和可視性

5.4.2. 通過檢查單個文件來查找表的大小要比使用SHOWTABLE STATUS快得多

5.4.3. SHOW TABLE STATUS必須執行更複雜的工作來確定為一個表分配了空間

5.4.4. 會使DROPTABLE性能變差。嚴重時可能導致伺服器範圍內明顯的停頓

5.4.4.1. 先將.ibd文件鏈接到一個大小為零的文件，然後手動刪除該文件，而不是等待MySQL來刪除

5.4.4.2. 從8.0.23版本開始，這應該不再是一個問題了

5.5. 表空間在寫操作頻繁的環境中可能會變得非常大

5.5.1. 要限制寫操作，請將innodb_max_purge_lag變數設置為0以外的值

5.5.1.1. 表示在InnoDB開始延遲更多修改數據的查詢之前，可以等待清除的最大事務數

5.5.1.2. 設置innodb_max_purge_lag變數也會降低性能

5.5.2. 未清除的行版本會影響所有查詢，因為它們會使表和索引變大

5.5.3. 清除線程不能跟上進度，性能就會下降

5.5.4. 預設的可重覆讀取事務隔離級別，InnoDB將無法刪除行的舊版本，因為未提交的事務仍需要能夠看到它們

5.5.5. 清除過程是多線程的，但如果遇到清除延遲問題（innodb_purge_threads和innodb_purge_batch_size），則可能需要針對工作負載進行調優

5.5.6. 如果Undo日誌很大，並且表空間因此而增長，你可以強制MySQL放慢速度來讓InnoDB的清理線程跟上

5.5.6.1. InnoDB會不斷地寫入數據並填充磁碟，直到磁碟空間耗盡或者表空間達到所定義的上限

5.6. sync_binlog選項控制MySQL如何將二進位日誌刷新到磁碟，預設值是1

5.6.1. MySQL將執行刷新並保持二進位日誌的持久性和安全性

5.6.2. 不建議設置為任何其他值

6. MySQL併發

6.1. 如果遇到InnoDB併發問題，並且運行的MySQL版本低於5.7，解決方案通常是升級伺服器

6.2. 如果你發現自己遇到了併發性瓶頸，最好的選擇是對數據進行分片

6.2.1. 如果分片不可行，那麼可能需要限制併發性

6.3. 限制併發性的最基本方法是使用innodb_thread_concurrency變數，該變數限制了內核中同時可以有多少線程

6.3.1. 首先將innodb_thread_concurrency設置為與可用CPU核數相同的值，然後根據需要調整大小

7. 安全設置

7.1. 安全性和可靠性的保障成本往往更高

7.2. max_connect_errors

7.2.1. 如果網路暫時出現問題、出現應用程式或配置錯誤，或者存在另一個問題導致連接無法在短時間內成功完成，則客戶端可能會被阻止連接，並且在刷新主機緩存之前無法再次連接

7.2.2. 預設設置（100）非常小，因此該問題很容易發生

7.2.3. 如果啟用了skip_name_resolve，則max_connect_errors選項將無效，因為其行為取決於主機緩存，而主機緩存被skip_name_resolve禁用

7.3. skip_name_resolve

7.3.1. DNS是MySQL連接過程中的一個薄弱環節

7.3.2. 某個時間點出現DNS故障的概率幾乎是確定性的

7.4. max_connections

7.4.1. 設置得足夠高，以滿足你認為將要經歷的正常負載的連接需求，並且額外保留一些連接以便管理伺服器時可以登錄

7.4.2. 預設值為151，這對於很多應用程式來說都不夠

7.4.3. 如果沒有使用持久連接，但是應用程式沒有正常斷開連接，也會出現伺服器連接占滿的情況

7.4.4. 可以顯示伺服器是否在某個時刻出現了連接高峰。如果到達max_connections，則客戶端可能至少被拒絕了一次

7.5. sql_mode

7.5.1. 最好讓MySQL在大多數方面保持原樣，而不要試圖讓它像其他資料庫伺服器那樣運行

7.5.2. 在計劃升級資料庫時，一定要檢查對預設sql_mode的更改

7.6. sysdate_is_now

7.6.1. 如果你不是明確地希望SYSDATE()函數具有不確定性行為（這會破壞複製並使從備份中進行的時間點恢復變得不可靠），那麼可以啟用該選項並使其行為具有確定性

7.7. read_only

7.7.1. 可防止未經授權的用戶對副本進行更改，副本應僅通過複製而不是從應用程式接收更改

7.7.2. 建議將副本設置為只讀模式

7.8. super_read_only

7.8.1. 可阻止擁有SUPER許可權的用戶寫入數據

7.8.2. 啟用此功能後，唯一可以將更改寫入資料庫的就是複製

7.8.3. 建議啟用super_read_only

7.8.4. 防止你意外地使用管理員賬戶將數據寫入只讀副本，從而引起數據不同步

8. 高級InnoDB設置

8.1. innodb_autoinc_lock_mode

8.1.1. 控制InnoDB如何生成自動遞增的主鍵值

8.2. innodb_buffer_pool_instances

8.2.1. 將緩衝池劃分為多個段，這可能是提高多核機器上MySQL在高併發工作負載下可伸縮性最重要的方法之一

8.2.2. 多個緩衝池對工作負載進行分區，這樣一些全局互斥體就不會成為爭用熱點

8.3. innodb_io_capacity

8.3.1. 告知InnoDB有多少I/O容量可供其使用

8.4. innodb_read_io_threads

8.5. innodb_write_io_threads

8.6. 如果你有很多硬碟和高併發工作負載，並且發現線程很難跟上，那麼可以增加線程的數量，或者簡單地將它們設置為執行I/O操作的物理磁碟數量

8.7. innodb_strict_mode

8.7.1. InnoDB在某些情況下拋出錯誤而不是警告，尤其是無效或可能導致危險的CREATE TABLE選項

8.8. innodb_old_blocks_time

8.8.1. 一個由兩部分組成的緩衝池LRU列表，設計目的是防止臨時查詢將長期多次使用的頁面驅逐出去

8.8.2. 預設情況下，它被設置為0

8.8.3. 將其設置為一個較小的值如1000（1秒），這在基準測試中被證明是非常有效的