1  MySQL引數調整

1.1    tx_isolation事務引數

1.1.1   事務的特徵

事務具有四個特性：原子性（Atomiocity）、一致性（Consistency）、隔離性（Isolation）和永續性（Durability），這四個特性簡稱ACID特性。

原子性：事務是資料庫的邏輯工作單位，事務中包括的所有操作要麼都做，要麼都不做。

一致性：事務執行的結果必須是使資料庫從一個一致性的狀態變到另外一個一致性狀態。

隔離性：一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的資料對其他事務是隔離的，併發執行的各個事務之間互相不干擾。

永續性：一個事務一旦成功提交，對資料庫中資料的修改就是永續性的。接下來其他的其他。

1.1.2   四種事務隔離級別

READ UNCOMMITED：SELECT的時候允許髒讀，即SELECT會讀取其他事務修改而還沒有提交的資料。

READ COMMITED：SELECT的時候不能重複讀，即同一個事務中兩次執行同樣的查詢語句，若在第一次與第二次查詢之間時間段，其他事務又剛好修改了其查詢的資料且提交了，則兩次讀到的資料不一致。

REPEATABLE READ：SELECT的時候可以重複讀，即同一個事務中兩次執行同樣的查詢語句，得到的資料始終都是一致的。

SERIALIZABLE：與可重複讀的唯一區別是，預設把普通的SELECT語句改成SELECT …. LOCK IN SHARE MODE。即為查詢語句涉及到的資料加上共享瑣，阻塞其他事務修改真實資料。

1.1.3   隔離級別選擇

綜合考慮系統的應用和效能，建議使用預設的REPEATABLE READ級別，或者READ COMMITED級別。

1.2    innodb_buffer_pool_size

這是InnoDB最重要的設定，對InnoDB效能有決定性的影響。預設的設定只有8M，所以預設的資料庫設定下面InnoDB效能很差。在只有 InnoDB儲存引擎的資料庫伺服器上面，可以設定60-80%的記憶體。更精確一點，在記憶體容量允許的情況下面設定比InnoDB tablespaces大10%的記憶體大小。它用來設定用於快取 InnoDB 索引及資料塊的記憶體區域大小，類似於 MyISAM 儲存引擎的 key_buffer_size 引數，當然，可能更像是 Oracle 的 db_cache_size。簡單來說，當我們操作一個 InnoDB 表的時候，返回的所有資料或者去資料過程中用到的任何一個索引塊，都會在這個記憶體區域中走一遭。

和key_buffer_size 對於 MyISAM 引擎一樣，innodb_buffer_pool_size 設定了 InnoDB 儲存引擎需求最大的一塊記憶體區域的大小，直接關係到 InnoDB儲存引擎的效能，所以如果我們有足夠的記憶體，儘可將該引數設定到足夠打，將盡可能多的 InnoDB 的索引及資料都放入到該快取區域中，直至全部。

我們可以透過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算快取命中率，並根據命中率來調整 innodb_buffer_pool_size 引數大小進行最佳化。

1.3    innodb_flush_log_at_trx_commit

該引數設定了事務提交時記憶體中log資訊的重新整理頻率，有3個值, 預設設定的是1， 也就是同步重新整理：

=0時， 日誌緩衝每秒一次地被寫到日誌檔案，並且對日誌檔案做到磁碟操作的重新整理。任何mysqld程式的崩潰會刪除崩潰前最後一秒的事務；

=1時，在每個事務提交時，日誌緩衝被寫到日誌檔案，對日誌檔案做到磁碟操作的重新整理。Truly ACID。速度慢；

=2時，在每個事務提交時，日誌緩衝被寫到檔案，但不對日誌檔案做到磁碟操作的重新整理。只有作業系統崩潰或掉電才會刪除最後一秒的事務，不然不會丟失事務。

1.4    innodb_log_buffer_size

INNODB日誌快取的大小，磁碟速度是很慢的，直接將log寫道磁碟會影響InnoDB的效能，該引數設定了log buffer的大小，一般4M。如果有大的blob操作，可以適當增大。

1.5    innodb_log_file_size

INNODB日誌檔案的大小，該引數決定了recovery speed。太大的話recovery就會比較慢，太小了影響查詢效能，一般取1GB可以兼顧效能和recovery的速度。

1.6    innodb_data_file_path

該引數指定表資料和索引儲存的空間，可以是一個或者 多個檔案。最後一個資料檔案必須是自動擴充的，也只有最後一個檔案允許自動擴充。這樣，當空間用完後，自動擴充資料檔案就會自動增長（以8MB為單位）以 容納額外的資料。例如： innodb_data_file_path=/disk1 /ibdata1:900M;/disk2/ibdata2:50M:autoextend兩個資料檔案放在不同的磁碟上（）。資料首先放在ibdata1 中，當達到900M以後，資料就放在ibdata2中。一旦達到50MB，ibdata2將以8MB為單位自動增長。如果磁碟滿了，需要在另外的磁碟上面 增加一個資料檔案。

1.7    innodb_data_home_dir

該引數設定了放置表空間資料的目錄，預設在mysql的資料目錄，由MySQL引數datadir指定。設定到和MySQL 安裝檔案不同的分割槽可以提高效能。可以與innodb_data_file_path配合使用，用innodb_data_home_dir來指定共同位置，然後在透過 inndo_data_file_path來指定檔名即可。

1.8    innodb_file_io_threads

此引數指定InnoDB表可用的檔案I／O執行緒數，建議在非Windows平臺中這個引數設定為4。

1.9    innodb_flush_logs_at_trx_commit

該引數設定了事務提交時記憶體中log資訊的處理。

1) =1時，在每個事務提交時，日誌緩衝被寫到日誌檔案，對日誌檔案做到磁碟操作的重新整理。Truly ACID。速度慢。預設為此值。

2) =2時，在每個事務提交時，日誌緩衝被寫到檔案，但不對日誌檔案做到磁碟操作的重新整理。只有作業系統崩潰或掉電才會刪除最後一秒的事務，不然不會丟失事務。

3) =0時， 日誌緩衝每秒一次地被寫到日誌檔案，並且對日誌檔案做到磁碟操作的重新整理。任何mysqld程式的崩潰會刪除崩潰前最後一秒的事務。

1.10  innodb_flush_method

影響了伺服器flush資料或日誌檔案的方法。設定InnoDB同步IO的方式

1)   Default – 使用fsync（）。

2)   O_SYNC 以sync模式開啟檔案，通常比較慢。

3)   O_DIRECT，在Linux上使用Direct IO。跳過了作業系統的檔案系統Disk Cache，讓MySQL直接讀寫磁碟，可以顯著提高速度，特別是在RAID系統上。避免額外的資料複製和double buffering（mysql buffering 和OS buffering）。避免雙緩衝(double buffering)和降低swap的壓力。大多數情況下可以提高效能.但是注意如果RAID cache不夠的話,寫IO的操作會有麻煩。

1.11  tmp_table_size

tmp_table_size，它規定了記憶體臨時表的最大值，每個執行緒都要分配。（實際起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果記憶體臨時表超出了限制，MySQL就會自動地把它轉化為基於磁碟的MyISAM表，此引數不限制create tables建立的記憶體表， 儲存在指定的tmpdir目錄下，預設:

mysql> show variables like "tmpdir";

1.12  max_heap_table_size

這個變數定義了使用者可以建立的記憶體表(memory table)的大小。這個值用來計算記憶體表的最大行數值。如果記憶體內的臨時表超過該值，MySQL自動將它轉換為硬碟上的MyISAM表。這個變數支援動態改變，即set @max_heap_table_size=# ，但是對於已經存在的記憶體表就沒有什麼用了，除非這個表被重新建立(create table)或者修改(alter table)或者truncate table。服務重啟也會設定已經存在的記憶體表為全域性max_heap_table_size的值。這個變數和tmp_table_size一起限制了內部記憶體表的大小。

1.13  long_query_time

long_query_time，對SQL執行設定一個時間限制，單位為秒，超出這個時間，測返回錯誤。該引數可動態設定：set global long_query_time=1800;

1.14  join_buffer_size

如果兩個表關聯查詢，但關聯的欄位又沒有索引時，使用此引數分配的記憶體空間。

1.15  key_buffer_size

MyISAM表的索引塊分配了緩衝區，由所有執行緒共享。key_buffer_size是索引塊緩衝區的大小。鍵值緩衝區即為鍵值快取.用key_buffer_size結合Key_blocks_unused狀態變數和緩衝區塊大小，可以確定使用的鍵值緩衝區的比例。從key_cache_block_size伺服器變數可以獲得緩衝區塊大小。使用的緩衝區的比例為：

1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)

注意：MyISAM的資料檔案讀取依賴於作業系統自身的IO快取，如果有MyISAM表，要預留更多的記憶體給作業系統。

1.16  wait_timeout

wait_timeout，設定非互交連線會話的空閒超時時間，單位為s。

1.17  interactive_timeout

interactive_timeout，設定互交連線會話的空閒超時時間，單位為s。