MS系列： MySQL 通用調優

這裡是 MySQL5.6 及以上的調優參數，主要是提升多個 database/table 的寫入和查詢性能：

避免使用 Swap 記憶體

首先 MySQL 要絕對避免使用 Swap 記憶體，網上有多種辦法，可以參考。

調整heap大小

當 Order By 或者 Group By 等需要用到結果集時，參數中設定的臨時表的大小小于結果集的大小時，就會将該表放在磁盤上，這個時候在硬碟上的 IO 要比内銷差很多。所耗費的時間也多很多，Mysql 會取 min(tmp_table_size, max_heap_table_size)的值，是以兩個設定為一樣大小，除非是大量使用記憶體表的情況，此時 max_heap_table_size

要設定很大。

max_heap_table_size=M
tmp_table_size=M
           

調整查詢緩存

下面這部分是 Select 查詢結果集的緩存控制，query_cache_limit 表示緩存的 Select 結果集的最大位元組數，這個可以限制哪些結果集緩存，query_cache_min_res_unit 表示結果集緩存的記憶體單元大小，若需要緩存的 SQL 結果集很小，比如傳回幾條記錄的，則 query_cache_min_res_unit 越小，記憶體使用率越高，query_cache_size 表示總共用多少記憶體緩存 Select 結果集，query_cache_type 則是控制是否開啟結果集緩存，預設 0 不開啟，1 開啟，2 為程式控制方式緩存，比如 SELECT SQL_CACHE …這個語句表明此查詢 SQL 才會被緩存，對于執行頻率比較高的一些查詢 SQL，進行指定方式的緩存，效果會最好。

FLUSH QUERY CACH 指令則清理緩存，以更好的利用它的記憶體，但不會移除緩存，RESET QUERY CACHE 使命從查詢緩存中移除所有的查詢結果。

query_cache_type =
query_cache_limit=
query_cache_size = 
query_cache_min_res_unit=
           

調整最大連接配接數

MySQL 最大連接配接數，這個通常在 1000-3000 之間比較合适，根據系統硬體能力，需要對 Linux 打開的最大檔案數做修改

調整索引緩存

下面這個參數是 InnoDB 最重要的參數，是緩存 innodb 表的索引，資料，插入資料時的緩沖，盡可能的使用記憶體緩存，對于 MySQL 專用伺服器，通常設定作業系統記憶體的 70%-80%最佳，但需要注意幾個問題，不能導緻 system的 swap 空間被占用，要考濾你的系統使用多少記憶體，其它應用使用的内在，還有你的 DB 有沒有 myisa 引擎，最後減去這些才是合理的值。

innodb_additional_mem_pool_size 除了緩存表資料和索引外,可以為操作所需的其他内部項配置設定緩存來提升InnoDB 的性能。這些記憶體就可以通過此參數來配置設定。推薦此參數至少設定為 2MB，實際上，是需要根據項目的InnoDB 表的數目相應地增加

innodb_max_dirty_pages_pct 值的争議，如果值過大，記憶體也很大或者伺服器壓力很大，那麼效率很降低，如果設定的值過小，那麼硬碟的壓力會增加.

獨立表空間模式

MyISAM 表引擎的資料庫會分别建立三個檔案：表結構、表索引、表資料空間。我們可以将某個資料庫目錄直接遷移到其他資料庫也可以正常工作。然而當你使用 InnoDB 的時候，一切都變了。InnoDB 預設會将所有的資料庫InnoDB 引擎的表資料存儲在一個共享空間中：ibdata1，這樣就感覺不爽，增删資料庫的時候，ibdata1 檔案不會自動收縮，單個資料庫的備份也将成為問題。通常隻能将資料使用 mysqldump 導出，然後再導入解決這個問題。

innodb_file_per_table=1

可以修改 InnoDB 為獨立表空間模式，每個資料庫的每個表都會生成一個資料空間。

獨立表空間

優點：

1. 每個表都有自已獨立的表空間。

2. 每個表的資料和索引都會存在自已的表空間中。

3. 可以實作單表在不同的資料庫中移動。

4. 空間可以回收（drop/truncate table 方式操作表空間不能自動回收）

5. 對于使用獨立表空間的表，不管怎麼删除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。

缺點：

單表增加比共享空間方式更大。

結論：

共享表空間在 Insert 操作上有一些優勢，但在其它都沒獨立表空間表現好。實際測試，當一個 MySQL 伺服器作為 Mycat 分片表存儲伺服器使用的情況下，單獨表空間的通路性能要大大好于共享表空間，是以強烈建議使用獨立表空間。

當啟用獨立表空間時，由于打開檔案數也随之增大，需要合理調整一下innodb_open_files 、table_open_cache等參數。

innodb_file_per_table=
innodb_open_files=
table_open_cache=
           

配置Undo Log

Undo Log 是為了實作事務的原子性，在 MySQL 資料庫 InnoDB 存儲引擎中，還用 Undo Log 來實作多版本并發控制(簡稱：MVCC)。Undo Log 的原理很簡單，為了滿足事務的原子性，在操作任何資料之前，首先将資料備份到Undo Log，然後進行資料的修改。如果出現了錯誤或者使用者執行了 ROLLBACK 語句，系統可以利用 Undo Log 中的備份将資料恢複到事務開始之前的狀态。是以 Undo Log 的 IO 性能對于資料插入或更新也是很重要的一個因素。于是，從 MySQL 5.6.3 開始，這裡出現了重大優化機會：

As of MySQL 5.6.3, you can store InnoDB undo logs in one or more separate undo tablespaces outside of the system tablespace. This layout is different from the default configuration where the undo log is part of the system tablespace. The I/O patterns for the undo log make these tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage. innodb_rollback_segments 參數在此被重命名為 innodb_undo_logs

是以總共有 3 個控制參數：innodb_undo_tablespaces 表明總共多少個 undo 表空間檔案，innodb_undo_logs定義在一個事務中 innodb 使用的系統表空間中復原段的個數。如果觀察到同復原日志有關的互斥争用，可以調整這個參數以優化性能，預設是 128 最大值，官方建議先設小，若發現競争，再調大

注意這裡的參數是要安裝 MySQL 時候初始化 InnoDB 引擎設定的，innodb_undo_tablespaces 參數無法後期設定。

innodb_undo_tablespaces=
innodb_undo_directory= SSD 硬碟或者另外一塊硬碟，跟資料分開
innodb_undo_logs=
           

調整事務日志

下面是 InnoDB 的日志相關的優化選項

innodb_log_buffer_size 這是 InnoDB 存儲引擎的事務日志所使用的緩沖區。類似于 Binlog Buffer，InnoDB 在寫事務日志的時候，為了提高性能，也是先将資訊寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit參數所設定的相應條件(或者日志緩沖區寫滿)之後，才會将日志寫到檔案(或者同步到磁盤)中。innodb_log_buffer_size不用太大，因為很快就會寫入磁盤。innodb_flush_log_trx_commit 的值有 :

• 0：log buffer 中的資料将以每秒一次的頻率寫入到 log file 中，且同時會進行檔案系統到磁盤的同步操作
• 1：在每次事務送出的時候将 log buffer 中的資料都會寫入到 log file，同時也會觸發檔案系統到磁盤的同步;
• 2：事務送出會觸發 log buffer 到 log file 的重新整理，但并不會觸發磁盤檔案系統到磁盤的同步。此外，每秒會有一次檔案系統到磁盤同步操作。對于非關鍵交易型資料，采用 2 即可以滿足高性能的日志操作，若要非常可靠的資料寫入保證，則需要設定為 1，此時每個 commit 都導緻一次磁盤同步，性能下降。

innodb_log_file_size 此參數确定資料日志檔案的大小，以 M 為機關，更大的設定可以提高性能，但也會增加恢複故障資料庫所需的時間。

innodb_log_files_in_group 分割多個日志檔案，提升并行性。

innodb_autoextend_increment 對于大批量插入資料也是比較重要的優化參數（機關是 M）

innodb_log_buffer_size=M
innodb_log_file_size =M
innodb_log_files_in_group=
innodb_autoextend_increment=
innodb_flush_log_at_trx_commit=
#建議用 GTID 的并行複制，以下是需要主從複制的情況下，相關的設定參數。
#gtid_mode = ON
#binlog_format = mixed
#enforce-gtid-consistency=true
#log-bin=binlog
#log-slave-updates=true