1.簡單介紹
InnoDB給MySQL提供了具有提交，回滾和崩潰恢復能力的事務安全（ACID相容）儲存引擎。InnoDB鎖定在行級並且也在SELECT語句提供一個Oracle風格一致的非鎖定讀。這些特色增加了多使用者部署和效能。沒有在InnoDB中擴大鎖定的需要，因為在InnoDB中行級鎖定適合非常小的空間。InnoDB也支援FOREIGN KEY強制。在SQL查詢中，你可以自由地將InnoDB型別的表與其它MySQL的表的型別混合起來，甚至在同一個查詢中也可以混合。
2.之所以選用innodb作為儲存引擎的考慮
目前來說，InnoDB是為Mysql處理巨大資料量時的最大效能設計。它的CPU效率可能是任何其它基於磁碟的關聯式資料庫引擎所不能匹敵的。在資料量大的網站或是應用中Innodb是倍受青睞的。
另一方面，在資料庫的複製操作中Innodb也是能保證master和slave資料一致有一定的作用。
3.下面是對線上mysql5.6版本的資料庫的配置進行的優化分析記錄：
1）記憶體利用方面：
innodb_buffer_pool_size
這個是Innodb最重要的引數，和MyISAM的key_buffer_size有相似之處，但也是有差別的。
這個引數主要快取innodb表的索引，資料，插入資料時的緩衝。
該引數分配記憶體的原則：
這個引數預設分配只有8M，可以說是非常小的一個值。
如果是一個專用DB伺服器，那麼他可以佔到記憶體的70%-80%。
這個引數不能動態更改，所以分配需多考慮。分配過大，會使Swap佔用過多，致使Mysql的查詢特慢。
如果你的資料比較小，那麼可分配是你的資料大小+10%左右做為這個引數的值。
例如：資料大小為50M,那麼給這個值分配innodb_buffer_pool_size＝64M
設定方法，在my.cnf檔案裡：
innodb_buffer_pool_size=4G
----------------------------------------------------------------------------------------------------------
注意：
在Mysql5.7版本之前，調整innodb_buffer_pool_size大小必須在my.cnf配置裡修改，然後重啟mysql程式才可以生效。
如今到了Mysql5.7版本，就可以直接動態調整這個引數，方便了很多。
尤其是在伺服器記憶體增加之後，運維人員不能粗心大意，要記得調大Innodb_Buffer_Pool_size這個引數。
資料庫配置後，要注意檢查Innodb_Buffer_Pool_size這個引數的設定是否合理
需要注意的地方：
在調整innodb_buffer_pool_size 期間，使用者的請求將會阻塞，直到調整完畢，所以請勿在白天調整，在凌晨3-4點低峰期調整。
調整時，內部把資料頁移動到一個新的位置，單位是塊。如果想增加移動的速度，需要調整innodb_buffer_pool_chunk_size引數的大小，預設是128M。
Mysql5.7中動態調整這個引數的操作記錄（例如由128M增大為384M）：
134217728/1024*1024=128M
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 134217728 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 134217728 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 402653184 |
+---------------------------+
1 row in set (0.00 sec)
innodb_buffer_pool_chunk_size的大小，計算公式是innodb_buffer_pool_size/innodb_buffer_pool_instances
比如現在初始化innodb_buffer_pool_size為2G，innodb_buffer_pool_instances例項為4，innodb_buffer_pool_chunk_size設定為1G，那麼會自動把innodb_buffer_pool_chunk_size 1G調整為512M.
例：
./mysqld --innodb_buffer_pool_size=2147483648 --innodb_buffer_pool_instances=4
--innodb_buffer_pool_chunk_size=1073741824;
mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 2147483648 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
| 4 |
+--------------------------------+
1 row in set (0.00 sec)
# Chunk size was set to 1GB (1073741824 bytes) on startup but was
# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances
mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
| 536870912 |
+---------------------------------+
1 row in set (0.00 sec)
監控Buffer Pool調整程式
mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+
1 row in set (0.00 sec)
檢視錯誤日誌：
（增大）
[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.
（減少）
[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.
[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 0 pages. (253952/253952)
[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.
[Note] InnoDB: re-enabled adaptive hash index.
----------------------------------------------------------------------------------------------------------
innodb_additional_mem_pool_size
用來存放Innodb的內部目錄，這個值不用分配太大，系統可以自動調。通常設定16Ｍ夠用了，如果表比較多，可以適當的增大。
設定方法，在my.cnf檔案裡：
innodb_additional_mem_pool_size = 16M
2）關於日誌方面：
innodb_log_file_size
作用：指定在一個日誌組中，每個log的大小。
結合innodb_buffer_pool_size設定其大小，25%-100%。避免不需要的重新整理。
注意：這個值分配的大小和資料庫的寫入速度，事務大小，異常重啟後的恢復有很大的關係。一般取256M可以兼顧效能和recovery的速度。
分配原則：幾個日值成員大小加起來差不多和你的innodb_buffer_pool_size相等。上限為每個日值上限大小為4G.一般控制在幾個Log檔案相加大小在2G以內為佳。具體情況還需要看你的事務大小，資料大小為依據。
說明：這個值分配的大小和資料庫的寫入速度，事務大小，異常重啟後的恢復有很大的關係。
設定方法：在my.cnf檔案裡：
innodb_log_file_size = 256M
innodb_log_files_in_group
作用：指定你有幾個日值組。
分配原則： 一般我們可以用2-3個日值組。預設為兩個。
設定方法：在my.cnf檔案裡：
innodb_log_files_in_group=3
innodb_log_buffer_size：
作用：事務在記憶體中的緩衝，也就是日誌緩衝區的大小， 預設設定即可，具有大量事務的可以考慮設定為16M。
如果這個值增長過快，可以適當的增加innodb_log_buffer_size
另外如果你需要處理大理的TEXT，或是BLOB欄位，可以考慮增加這個引數的值。
設定方法：在my.cnf檔案裡：
innodb_log_buffer_size=3M
innodb_flush_logs_at_trx_commit
作用：控制事務的提交方式,也就是控制log的重新整理到磁碟的方式。
分配原則：這個引數只有3個值（0，1，2）.預設為1，效能更高的可以設定為0或是2，這樣可以適當的減少磁碟IO（但會丟失一秒鐘的事務。），遊戲庫的MySQL建議設定為0。主庫請不要更改了。
其中：
0：log buffer中的資料將以每秒一次的頻率寫入到log file中，且同時會進行檔案系統到磁碟的同步操作，但是每個事務的commit並不會觸發任何log buffer 到log file的重新整理或者檔案系統到磁碟的重新整理操作；
1：（預設為1）在每次事務提交的時候將logbuffer 中的資料都會寫入到log file，同時也會觸發檔案系統到磁碟的同步；
2：事務提交會觸發log buffer 到log file的重新整理，但並不會觸發磁碟檔案系統到磁碟的同步。此外，每秒會有一次檔案系統到磁碟同步操作。
說明：
這個引數的設定對Ｉｎｎｏｄｂ的效能有很大的影響，所以在這裡給多說明一下。
當這個值為1時：innodb 的事務LOG在每次提交後寫入日值檔案，並對日值做重新整理到磁碟。這個可以做到不丟任何一個事務。
當這個值為2時：在每個提交，日誌緩衝被寫到檔案，但不對日誌檔案做到磁碟操作的重新整理,在對日誌檔案的重新整理在值為2的情況也每秒發生一次。但需要注意的是，由於程式呼叫方面的問題，並不能保證每秒１００％的發生。從而在效能上是最快的。但作業系統崩潰或掉電才會刪除最後一秒的事務。
當這個值為0時：日誌緩衝每秒一次地被寫到日誌檔案，並且對日誌檔案做到磁碟操作的重新整理，但是在一個事務提交不做任何操作。mysqld程式的崩潰會刪除崩潰前最後一秒的事務。
從以上分析，當這個值不為１時，可以取得較好的效能，但遇到異常會有損失，所以需要根據自已的情況去衡量。
設定方法：在my.cnf檔案裡：
innodb_flush_logs_at_trx_commit=1
3）檔案IO分配，空間佔用方面
innodb_file_per_table
作用：使每個Innodb的表，有自已獨立的表空間。如刪除檔案後可以回收那部分空間。預設是關閉的，建議開啟（innodb_file_per_table=1）
分配原則：只有使用不使用。但DB還需要有一個公共的表空間。
設定方法：在my.cnf檔案裡：
innodb_file_per_table=1
innodb_file_io_threads
作用：檔案讀寫IO數，這個引數只在Windows上起作用。在Linux上只會等於4，預設即可！
設定方法：在my.cnf檔案裡：
innodb_file_io_threads=4
innodb_open_files
作用：限制Innodb能開啟的表的資料。
分配原則：這個值預設是300。如果庫裡的表特別多的情況，可以適當增大為1000。innodb_open_files的大小對InnoDB效率的影響比較小。但是在InnoDBcrash的情況下，innodb_open_files設定過小會影響recovery的效率。所以用InnoDB的時候還是把innodb_open_files放大一些比較合適。
設定方法：在my.cnf檔案裡：
innodb_open_files=800
innodb_data_file_path
指定表資料和索引儲存的空間，可以是一個或者多個檔案。最後一個資料檔案必須是自動擴充的，也只有最後一個檔案允許自動擴充。這樣，當空間用完後，自動擴充資料檔案就會自動增長（以8MB為單位）以容納額外的資料。
例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend 兩個資料檔案放在不同的磁碟上。資料首先放在ibdata1 中，當達到900M以後，資料就放在ibdata2中。
設定方法，在my.cnf檔案裡：
innodb_data_file_path =ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:1G:autoextend
innodb_data_home_dir
放置表空間資料的目錄，預設在mysql的資料目錄，設定到和MySQL安裝檔案不同的分割槽可以提高效能。
設定方法，在my.cnf檔案裡：（比如mysql的資料目錄是/data/mysql/data，這裡可以設定到不通的分割槽/home/mysql下）
innodb_data_home_dir = /home/mysql
4）其它相關引數（適當的增加table_cache）
這裡說明一個比較重要的引數：
innodb_flush_method
作用：Innodb和系統打交道的一個IO模型
分配原則：
Windows不用設定。
linux可以選擇：O_DIRECT
直接寫入磁碟，禁止系統Cache了
設定方法：在my.cnf檔案裡：
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct
作用：在buffer pool緩衝中，允許Innodb的髒頁的百分比，值在範圍1-100,預設為90，建議保持預設。
這個引數的另一個用處：當Innodb的記憶體分配過大，致使Swap佔用嚴重時，可以適當的減小調整這個值，使達到Swap空間釋放出來。建義：這個值最大在90%，最小在15%。太大，快取中每次更新需要致換資料頁太多，太小，放的資料頁太小，更新操作太慢。
設定方法：在my.cnf檔案裡：
innodb_max_dirty_pages_pct＝90
動態更改需要有管理員許可權：
set global innodb_max_dirty_pages_pct=50;
innodb_thread_concurrency
同時在Innodb核心中處理的執行緒數量。建議預設值。
設定方法，在my.cnf檔案裡：
innodb_thread_concurrency = 16
5）公共引數調優
skip-external-locking
MyISAM儲存引擎也同樣會使用這個引數，MySQL4.0之後，這個值預設是開啟的。
作用是避免MySQL的外部鎖定(老版本的MySQL此引數叫做skip-locking)，減少出錯機率增強穩定性。建議預設值。
設定方法，在my.cnf檔案裡：
skip-external-locking
skip-name-resolve
禁止MySQL對外部連線進行DNS解析（預設是關閉此項設定的，即預設解析DNS），使用這一選項可以消除MySQL進行DNS解析的時間。
但需要注意，如果開啟該選項，則所有遠端主機連線授權都要使用IP地址方式，否則MySQL將無法正常處理連線請求！如果需要，可以設定此項。
設定方法，在my.cnf檔案裡：（我這線上mysql資料庫中開啟了這一設定）
skip-name-resolve
max_connections
設定最大連線（使用者）數，每個連線MySQL的使用者均算作一個連線，max_connections的預設值為100。此值需要根據具體的連線數峰值設定。
設定方法，在my.cnf檔案裡：
max_connections = 3000
query_cache_size
查詢快取大小，如果表的改動非常頻繁，或者每次查詢都不同，查詢快取的結果會減慢系統效能。可以設定為0。
設定方法，在my.cnf檔案裡：
query_cache_size = 512M
sort_buffer_size
connection級的引數，排序快取大小。一般設定為2-4MB即可。
設定方法，在my.cnf檔案裡：
sort_buffer_size = 1024M
read_buffer_size
connection級的引數。一般設定為2-4MB即可。
設定方法，在my.cnf檔案裡：
read_buffer_size = 1024M
max_allowed_packet
網路包的大小，為避免出現較大的網路包錯誤，建議設定為16M
設定方法，在my.cnf檔案裡：
max_allowed_packet = 16M
table_open_cache
當某一連線訪問一個表時，MySQL會檢查當前已快取表的數量。如果該表已經在快取中開啟，則會直接訪問快取中的表，以加快查詢速度；如果該表未被快取，則會將當前的表新增進快取並進行查詢。
通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_open_cache的值。
如果發現open_tables等於table_open_cache，並且opened_tables在不斷增長，那麼就需要增加table_open_cache的值;設定為512即可滿足需求。
設定方法，在my.cnf檔案裡：
table_open_cache = 512
myisam_sort_buffer_size
實際上這個myisam_sort_buffer_size引數意義不大，這是個字面上蒙人的引數，它用於ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 等命令時需要的記憶體。預設值即可。
設定方法，在my.cnf檔案裡：
myisam_sort_buffer_size = 8M
thread_cache_size
執行緒快取，如果一個客戶端斷開連線，這個執行緒就會被放到thread_cache_size中（緩衝池未滿），SHOW STATUS LIKE 'threads%';如果 Threads_created 不斷增大，那麼當前值設定要改大，改到 Threads_connected 值左右。（通常情況下，這個值改善效能不大），預設8即可
設定方法，在my.cnf檔案裡：
thread_cache_size = 8
innodb_thread_concurrency
執行緒併發數，建議設定為CPU核心數*2
設定方法，在my.cnf檔案裡：
innodb_thread_concurrency = 8
key_buffer_size
僅作用於 MyISAM儲存引擎，用來設定用於快取 MyISAM儲存引擎中索引檔案的記憶體區域大小。如果我們有足夠的記憶體，這個快取區域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引，以儘可能提高效能。不要設定超過可用記憶體的30%。即使不用MyISAM表，也要設定該值8-64M，用於臨時表。
設定方法，在my.cnf檔案裡：
key_buffer_size = 8M
－－－－－－－－－－－影響InnoDB效能的一些重要引數－－－－－－－－－－－－－－
1）InnoDB_buffer_pool_size
這個引數定義InnoDB儲存引擎的表資料和索引資料的最大記憶體緩衝區,InnoDB_buffer_pool_size引數同時提供為資料塊和索引塊做快取.這個值設定的越高,訪問表中資料需要的磁碟IO就越少.
2）InnoDB_flush_log_at_trx_commit
這個引數控制緩衝區的資料寫入到日誌檔案以及日誌檔案資料重新整理到磁碟的操作時機.在正式環境中建議設定成1。
設定0時日誌緩衝每秒一次被寫到日誌檔案,並且對日誌檔案做向磁碟重新整理的操作,但是在一個事物提交不做任何操作.
設定1時在每個事物提交時,日誌緩衝被寫到日誌檔案,並且對日誌檔案做向磁碟重新整理的操作
設定2時在每個事物提交時,日誌緩衝被寫到日誌檔案,但不對日誌檔案做向磁碟重新整理的操作,對日誌檔案每秒向磁碟做一次重新整理操作.
3）InnoDB_additional_mem_pool_size
這個引數是InnoDB用來儲存資料庫結構和其他內部資料結構的記憶體池.應用程式的表越多,則需要從這裡分配越多的記憶體,如果用光這個池,則會從OS層分配.
4）InnoDB_lock_wait_timeout
這個引數自動檢測行鎖導致的死鎖並進行相應處理,但是對於表鎖導致的死鎖不能自動檢測預設值為50秒.
5）InnoDB_support_xa
這個引數設定MySQL是否支援分散式事務
6）InnoDB_log_buffer_size
這個引數日誌緩衝大小
7）InnoDB_log_file_size
這個引數是一個日誌組中每個日誌檔案的大小,此引數在高寫入負載尤其是大資料集的情況下很重要.這個值越大則效能相對越高,但好似副作用是一旦系統崩潰恢復的時間會加長.
8）Innodb_io_capacity
這個引數重新整理髒頁數量和合並插入數量，改善磁碟IO處理能力
9）Innodb_use_native_aio
非同步I/O在一定程度上提高系統的併發能力，在Linux系統上，可以通過將MySQL的伺服器此引數的值設定為ON設定InnoDB可以使用Linux的非同步I/O子系統.
10）Innodb_read_io_threads
這個引數可調整的讀請求的後臺執行緒數
11）Innodb_write_io_threads
這個引數可調整的寫請求的後臺執行緒數
12）InnoDB_buffer_pool_instances
這個引數能較好的執行於多核處理器，支援使用 此引數對伺服器變數建立多個緩衝池例項，每個緩衝池例項分別自我管理空閒列表、列表刷寫、LRU以及其它跟緩衝池相關的資料結構，並通過各自的互斥鎖進行保護
13）InnoDB_purge_threads
MySQL5.5以前碎片回收操作是主執行緒的一部分，這經定期排程的方式執行，但會阻塞資料庫的其他操作.到5.5以後，可以將這個執行緒獨立出來 ；這個能讓碎片回收得更及時而且不影響其他執行緒的操作
14）Innodb_flush_method
這個引數控制著innodb資料檔案及redo log的開啟、刷寫模式，對於這個引數，文件上是這樣描述的：
有三個值：fdatasync(預設)，O_DSYNC，O_DIRECT
預設是fdatasync，呼叫fsync()去刷資料檔案與redo log的buffer
為O_DSYNC時，innodb會使用O_SYNC方式開啟和刷寫redo log,使用fsync()刷寫資料檔案
為O_DIRECT時，innodb使用O_DIRECT開啟資料檔案，使用fsync()刷寫資料檔案跟redo log
總結一下三者寫資料方式：
fdatasync模式：寫資料時，write這一步並不需要真正寫到磁碟才算完成（可能寫入到作業系統buffer中就會返回完成），真正完成是flush操作，buffer交給作業系統去flush,並且檔案的後設資料資訊也都需要更新到磁碟。
O_DSYNC模式：寫日誌操作是在write這步完成，而資料檔案的寫入是在flush這步通過fsync完成
O_DIRECT模式：資料檔案的寫入操作是直接從mysql innodb buffer到磁碟的，並不用通過作業系統的緩衝，而真正的完成也是在flush這步,日誌還是要經過OS緩衝
使用下面命令就可以檢視到上面引數的設定：
mysql> show variables like "%innodb%";
-----------------------------------------------------------------------------------------------------------------------------------------------
下面是線上mysql（innodb）的my.cnf配置參考：
[client]
port = 3306
socket = /usr/local/mysql/var/mysql.sock
[mysqld]
port = 3306
socket = /usr/local/mysql/var/mysql.sock
basedir = /usr/local/mysql/
datadir = /data/mysql/data
pid-file = /data/mysql/data/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1
sync_binlog=1
log_bin = mysql-bin
skip-name-resolve
back_log = 600
max_connections = 3000
max_connect_errors = 3000
table_open_cache = 512
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 16M
tmp_table_size = 256M
read_buffer_size = 1024M
read_rnd_buffer_size = 1024M
sort_buffer_size = 1024M
join_buffer_size = 1024M
key_buffer_size = 8192M
thread_cache_size = 8
query_cache_size = 512M
query_cache_limit = 1024M
ft_min_word_len = 4
binlog_format = mixed
expire_logs_days = 30
log_error = /data/mysql/data/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/data/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
skip-external-locking
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 1024M
innodb_write_io_threads = 1000
innodb_read_io_threads = 1000
innodb_thread_concurrency = 8
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port = 3306
--------------------------------------------------------------------------------------------------------------------------------------
下面分享一個mysql5.6下my.cnf的優化配置，能使mysql效能大大提升：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
##################################################
#innodb
user=mysql
innodb_buffer_pool_size=6G
innodb_log_file_size=4G
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_file_io_threads=4
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
innodb_thread_concurrency = 0
innodb_additional_mem_pool_size=16M
innodb_autoinc_lock_mode = 2
##################################################
# Binary log/replication
log-bin
sync_binlog=1
sync_relay_log=1
relay-log-info-repository=TABLE
master-info-repository=TABLE
expire_logs_days=7
binlog_format=ROW
transaction-isolation=READ-COMMITTED
#################################################
#cache
tmp_table_size=512M
character-set-server=utf8
collation-server=utf8_general_ci
skip-external-locking
back_log=1024
key_buffer_size=1024M
thread_stack=256k
read_buffer_size=8M
thread_cache_size=64
query_cache_size=128M
max_heap_table_size=256M
query_cache_type=1
binlog_cache_size = 2M
table_open_cache=128
thread_cache=1024
thread_concurrency=8
wait_timeout=30
join_buffer_size = 1024M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M
#################################################
#connect
max-connect-errors=100000
max-connections=1000
#################################################
explicit_defaults_for_timestamp=true
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
##################################################
引數解釋：
# Binary log/replication(這裡主要是複製功能，也就是主從,提前配置好，後面講主從配置)
#二進位制日誌
log-bin
#為了在最大程式上保證複製的InnoDB事務永續性和一致性
sync_binlog=1
sync_relay_log=1
#啟用此兩項,可用於實現在崩潰時保證二進位制及從伺服器安全的功能
relay-log-info-repository=TABLE
master-info-repository=TABLE
#設定清除日誌時間
expire_logs_days=7
#行復制
binlog_format=ROW
#mysql資料庫事務隔離級別有四種(READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE)
transaction-isolation=READ-COMMITTED
#cache
#內部記憶體臨時表的最大值
tmp_table_size=512M
character-set-server=utf8
collation-server=utf8_general_ci
#即跳過外部鎖定
skip-external-locking
#MySQL能暫存的連線數量(根據實際設定)
back_log=1024
#指定索引緩衝區的大小,只對MyISAM表起作用,這裡寫上也沒有關係
key_buffer_size=1024M
#這條指令限定用於每個資料庫執行緒的棧大小
thread_stack=256k
#當一個查詢不斷地掃描某一個表,MySQL會為它分配一段記憶體緩衝區
read_buffer_size=8M
#執行緒快取
thread_cache_size=64
#查詢快取大小
query_cache_size=128M
#內部記憶體臨時表的最大值,每個執行緒都要分配
max_heap_table_size=256M
#將查詢結果放入查詢快取中
query_cache_type=1
#代表在事務過程中容納二進位制日誌SQL語句的快取大小
binlog_cache_size = 2M
#同樣是快取表大小
table_open_cache=128
#快取執行緒
thread_cache=1024
#推薦設定為伺服器 CPU核數的2倍
thread_concurrency=8
wait_timeout=30
#表和表聯接的緩衝區的大小
join_buffer_size = 1024M
#是一個connection級引數,在每個connection第一次需要使用這個buffer的時候,一次性分配設定的記憶體
sort_buffer_size=8M
#隨機讀取資料緩衝區使用記憶體
read_rnd_buffer_size = 8M
#connect
#是一個MySQL中與安全有關的計數器值,它負責阻止過多嘗試失敗的客戶端以防止暴力破解密碼
max-connect-errors=100000
#連線數
max-connections=1000
#開啟查詢快取
explicit_defaults_for_timestamp=true
#mysql伺服器能夠工作在不同的模式下,並能針對不同的客戶端以不同的方式應用這些模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
下面列出了對效能優化影響較大的主要變數，主要分為連線請求的變數和緩衝區變數。
1.連線請求的變數：
1) max_connections
MySQL的最大連線數，增加該值增加mysqld 要求的檔案描述符的數量。如果伺服器的併發連線請求量比較大，建議調高此值，以增加並行連線數量，當然這建立在機器能支撐的情況下，因為如果連線數越多， 介於MySQL會為每個連線提供連線緩衝區，就會開銷越多的記憶體，所以要適當調整該值，不能盲目提高設值。
數值過小會經常出現ERROR 1040: Too many connections錯誤，可以過’conn%’萬用字元檢視當前狀態的連線數量，以定奪該值的大小。
show variables like ‘max_connections’ 最大連線數
show status like ‘max_used_connections’響應的連線數
如下：
mysql> show variables like ‘max_connections‘;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| max_connections | 256 |
+———————–+——-+
mysql> show status like ‘max%connections‘;
+———————–+——-+
| Variable_name | Value |
+—————————-+——-+
| max_used_connections | 256|
+—————————-+——-+
max_used_connections / max_connections * 100% （理想值≈ 85%）
如果max_used_connections跟max_connections相同 那麼就是max_connections設定過低或者超過伺服器負載上限了，低於10%則設定過大。
2) back_log
MySQL能暫存的連線數量。當主要MySQL執行緒在一個很短時間內得到非常多的連線請求，這就起作用。如果MySQL的連線資料達到 max_connections時，新來的請求將會被存在堆疊中，以等待某一連線釋放資源，該堆疊的數量即back_log，如果等待連線的數量超過 back_log，將不被授予連線資源。
back_log值指出在MySQL暫時停止回答新請求之前的短時間內有多少個請求可以被存在堆疊中。只有如果期望在一個短時間內有很多連線，你需要增加它，換句話說，這值對到來的TCP/IP連線的偵聽佇列的大小。
當觀察你主機程式列表（mysql> show full processlist），發現大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連線程式時，就要加大back_log 的值了。
預設數值是50，可調優為128，對系統設定範圍為小於512的整數。
3) interactive_timeout
一個互動連線在被伺服器在關閉前等待行動的秒數。一個互動的客戶被定義為對mysql_real_connect()使用CLIENT_INTERACTIVE 選項的客戶。
預設數值是28800，可調優為7200。
2. 緩衝區變數
全域性緩衝：
4) key_buffer_size
key_buffer_size指定索引緩衝區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態值 Key_read_requests和Key_reads，可以知道key_buffer_size設定是否合理。比例key_reads / key_read_requests應該儘可能的低，至少是1:100，1:1000更好（上述狀態值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。
key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁碟表是MyISAM表，也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。
舉例如下：
mysql> show variables like ‘key_buffer_size‘;
+——————-+————+
| Variable_name | Value |
+———————+————+
| key_buffer_size | 536870912 |
+———— ———-+————+
key_buffer_size為512MB，我們再看一下key_buffer_size的使用情況：
mysql> show global status like ‘key_read%‘;
+————————+————-+
| Variable_name | Value |
+————————+————-+
| Key_read_requests| 27813678764 |
| Key_reads | 6798830 |
+————————+————-+
一共有27813678764個索引讀取請求，有6798830個請求在記憶體中沒有找到直接從硬碟讀取索引，計算索引未命中快取的概率：
key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%，設定在1/1000左右較好
預設配置數值是8388600(8M)，主機有4GB記憶體，可以調優值為268435456(256MB)。
5) query_cache_size
使用查詢緩衝，MySQL將查詢結果存放在緩衝區中，今後對於同樣的SELECT語句（區分大小寫），將直接從緩衝區中讀取結果。
通過檢查狀態值Qcache_*，可以知道query_cache_size設定是否合理（上述狀態值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況，如果Qcache_hits的值也 非常大，則表明查詢緩衝使用非常頻繁，此時需要增加緩衝大小；如果Qcache_hits的值不大，則表明你的查詢重複率很低，這種情況下使用查詢緩衝反 而會影響效率，那麼可以考慮不用查詢緩衝。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩衝。
與查詢緩衝有關的引數還有query_cache_type、query_cache_limit、query_cache_min_res_unit。
query_cache_type指定是否使用查詢緩衝，可以設定為0、1、2，該變數是SESSION級的變數。
query_cache_limit指定單個查詢能夠使用的緩衝區大小，預設為1M。
query_cache_min_res_unit是在4.1版本以後引入的，它指定分配緩衝區空間的最小單位，預設為4K。檢查狀態值 Qcache_free_blocks，如果該值非常大，則表明緩衝區中碎片很多，這就表明查詢結果都比較小，此時需要減小 query_cache_min_res_unit。
舉例如下：
mysql> show global status like ‘qcache%‘;
+——————————-+—————–+
| Variable_name | Value |
+——————————-+—————–+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+——————————-+—————–+
mysql> show variables like ‘query_cache%‘;
+————————————–+————–+
| Variable_name | Value |
+————————————–+———–+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+————————————–+—————+
查詢快取碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢快取碎片率超過20%，可以用FLUSH QUERY CACHE整理快取碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小資料量的話。
查詢快取利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查詢快取利用率在25%以下的話說明query_cache_size設定的過大，可適當減小；查詢快取利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。
查詢快取命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例伺服器查詢快取碎片率＝20.46％，查詢快取利用率＝62.26％，查詢快取命中率＝1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。
每個連線的緩衝
6) record_buffer_size
每個進行一個順序掃描的執行緒為其掃描的每張表分配這個大小的一個緩衝區。如果你做很多順序掃描，你可能想要增加該值。
預設數值是131072(128K)，可改為16773120 (16M)
7) read_rnd_buffer_size
隨機讀緩衝區大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀快取區。進行排序查詢時，MySQL會首先掃描一遍該緩衝，以避 免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySQL會為每個客戶連線發放該緩衝空間，所以應儘量適當設定該值，以避免記憶體開 銷過大。
一般可設定為16M
8) sort_buffer_size
每個需要進行排序的執行緒分配該大小的一個緩衝區。增加這值加速ORDER BY或GROUP BY操作。
預設數值是2097144(2M)，可改為16777208 (16M)。
9) join_buffer_size
聯合查詢操作所能使用的緩衝區大小
record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size為每個執行緒獨佔，也就是說，如果有100個執行緒連線，則佔用為16M*100
10) table_cache
表快取記憶體的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如 果你發現open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了 （上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables’獲得）。注意，不能盲目地把table_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能 不穩定或者連線失敗。
1G記憶體機器，推薦值是128－256。記憶體在4GB左右的伺服器該引數可設定為256M或384M。
11) max_heap_table_size
使用者可以建立的記憶體表(memory table)的大小。這個值用來計算記憶體表的最大行數值。這個變數支援動態改變，即set @max_heap_table_size=#
這個變數和tmp_table_size一起限制了內部記憶體表的大小。如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將記憶體中的heap表改為基於硬碟的MyISAM表。
12) tmp_table_size
通過設定tmp_table_size選項來增加一張臨時表的大小，例如做高階GROUP BY操作生成的臨時表。如果調高該值，MySQL同時將增加heap表的大小，可達到提高聯接查詢速度的效果，建議儘量優化查詢，要確保查詢過程中生成的臨時表在記憶體中，避免臨時表過大導致生成基於硬碟的MyISAM表。
mysql> show global status like ‘created_tmp%‘;
+——————————–+———+
| Variable_name | Value |
+———————————-+———+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+——————————–+———–+
每次建立臨時表，Created_tmp_tables增加，如果臨時表大小超過tmp_table_size，則是在磁碟上建立臨時 表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務建立的臨時檔案檔案數，比較理想的配 置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的伺服器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，應該相當好了
預設為16M，可調到64-256最佳，執行緒獨佔，太大可能記憶體不夠I/O堵塞
13) thread_cache_size
可以複用的儲存在中的執行緒的數量。如果有，新的執行緒從快取中取得，當斷開連線的時候如果有空間，客戶的線置在快取中。如果有很多新的執行緒，為了提高效能可以這個變數值。
通過比較 Connections和Threads_created狀態的變數，可以看到這個變數的作用。
預設值為110，可調優為80。
14) thread_concurrency
推薦設定為伺服器 CPU核數的2倍，例如雙核的CPU, 那麼thread_concurrency的應該為4；2個雙核的cpu, thread_concurrency的值應為8。預設為8
15) wait_timeout
指定一個請求的最大連線時間，對於4GB左右記憶體的伺服器可以設定為5-10。
3. 配置InnoDB的幾個變數
innodb_buffer_pool_size
對於InnoDB表來說，innodb_buffer_pool_size的作用就相當於key_buffer_size對於MyISAM表的作用一樣。InnoDB使用該引數指定大小的記憶體來緩衝資料和索引。對於單獨的MySQL資料庫伺服器，最大可以把該值設定成實體記憶體的80%。
根據MySQL手冊，對於2G記憶體的機器，推薦值是1G（50%）。
innodb_flush_log_at_trx_commit
主要控制了innodb將log buffer中的資料寫入日誌檔案並flush磁碟的時間點，取值分別為0、1、2三個。0，表示當事務提交時，不做日誌寫入操作，而是每秒鐘將log buffer中的資料寫入日誌檔案並flush磁碟一次；1，則在每秒鐘或是每次事物的提交都會引起日誌檔案寫入、flush磁碟的操作，確保了事務的 ACID；設定為2，每次事務提交引起寫入日誌檔案的動作，但每秒鐘完成一次flush磁碟操作。
實際測試發現，該值對插入資料的速度影響非常大，設定為2時插入10000條記錄只需要2秒，設定為0時只需要1秒，而設定為1時則需要229秒。因此，MySQL手冊也建議儘量將插入操作合併成一個事務，這樣可以大幅提高速度。
根據MySQL手冊，在允許丟失最近部分事務的危險的前提下，可以把該值設為0或2。
innodb_log_buffer_size
log快取大小，一般為1-8M，預設為1M，對於較大的事務，可以增大快取大小。
可設定為4M或8M。
innodb_additional_mem_pool_size
該引數指定InnoDB用來儲存資料字典和其他內部資料結構的記憶體池大小。預設值是1M。通常不用太大，只要夠用就行，應該與表結構的複雜度有關係。如果不夠用，MySQL會在錯誤日誌中寫入一條警告資訊。
根據MySQL手冊，對於2G記憶體的機器，推薦值是20M，可適當增加。
innodb_thread_concurrency=8
推薦設定為 2*(NumCPUs+NumDisks)，預設一般為8
MySQL 5.6相比於前代GA版本效能提升顯著，但預設快取設定對於小型站點並不合理。通過修改my.ini檔案中的performance_schema_max_table_instances引數，能夠有效降低記憶體佔用。
以下是5.6預設的設定
performance_schema_max_table_instances 12500
table_definition_cache 1400
table_open_cache 2000
可以調成，或者在小點都可以。
performance_schema_max_table_instances=600
table_definition_cache=400
table_open_cache=256
performance_schema_max_table_instances
The maximum number of instrumented table objects 檢測的表物件的最大數目。
table_definition_cache
The number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache. The minimum and default values are both 400.
快取frm檔案
table_open_cache
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
table_open_cache 指的是快取資料檔案的描述符(Linux/Unix)相關資訊
這個很重要啊，之前mount個單獨的檔案，資料庫一直不成功，原來是這個在作怪啊。
chcon -R -t mysqld_db_t /home/myusqldata
mysql> show variables;
一、慢查詢
mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+------------------+-------+
mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 4148 |
+---------------------+-------+
配置中開啟了記錄慢查詢，執行時間超過2秒的即為慢查詢，系統顯示有4148個慢查詢，你可以分析慢查詢日誌，找出有問題的SQL語句，慢查詢時間不宜設定過長，否則意義不大，最好在5秒以內，如果你需要微秒級別的慢查詢，可以考慮給MySQL打補丁：http://www.percona.com/docs/wiki/release:start，記得找對應的版本。
開啟慢查詢日誌可能會對系統效能有一點點影響，如果你的MySQL是主－從結構，可以考慮開啟其中一臺從伺服器的慢查詢日誌，這樣既可以監控慢查詢，對系統效能影響又小。
二、連線數
經常會遇見”MySQL: ERROR 1040: Too manyconnections”的情況，一種是訪問量確實很高，MySQL伺服器抗不住，這個時候就要考慮增加從伺服器分散讀壓力，另外一種情況是MySQL配置檔案中max_connections值過小：
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
這臺MySQL伺服器最大連線數是256，然後查詢一下伺服器響應的最大連線數：
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 245 |
+----------------------+-------+
MySQL伺服器過去的最大連線數是245，沒有達到伺服器連線數上限256，應該沒有出現1040錯誤，比較理想的設定是：
Max_used_connections / max_connections * 100% ≈ 85%
最大連線數占上限連線數的85％左右，如果發現比例在10%以下，MySQL伺服器連線數上限設定的過高了。
三、Key_buffer_size
key_buffer_size是對MyISAM表效能影響最大的一個引數，下面一臺以MyISAM為主要儲存引擎伺服器的配置：
mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+
分配了512MB記憶體給key_buffer_size，我們再看一下key_buffer_size的使用情況：
mysql> show global status like 'key_read%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+
一共有27813678764個索引讀取請求，有6798830個請求在記憶體中沒有找到直接從硬碟讀取索引，計算索引未命中快取的概率：
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%
比 如上面的資料，key_cache_miss_rate為0.0244%，4000個索引讀取請求才有一個直接讀硬碟，已經很BT 了，key_cache_miss_rate在0.1%以下都很好（每1000個請求有一個直接讀硬碟），如果key_cache_miss_rate在 0.01%以下的話，key_buffer_size分配的過多，可以適當減少。
MySQL伺服器還提供了key_blocks_*引數：
mysql> show global status like 'key_blocks_u%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_blocks_unused | 0 |
| Key_blocks_used | 413543 |
+------------------------+-------------+
Key_blocks_unused 表示未使用的快取簇(blocks)數，Key_blocks_used表示曾經用到的最大的blocks數，比如這臺伺服器，所有的快取都用到了，要麼 增加key_buffer_size，要麼就是過渡索引了，把快取佔滿了。比較理想的設定：
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
四、臨時表
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+-------------------------+---------+
每次建立臨時表，Created_tmp_tables增加，如果是在磁碟上建立臨時表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服務建立的臨時檔案檔案數，比較理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
比如上面的伺服器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 1.20%，應該相當好了。我們再看一下MySQL伺服器對臨時表的配置：
mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+-----------+
| Variable_name | Value |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
| tmp_table_size | 536870912 |
+---------------------+-----------+
只有256MB以下的臨時表才能全部放記憶體，超過的就會用到硬碟臨時表。
五、Open Table情況
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 919 |
| Opened_tables | 1951 |
+---------------+-------+
Open_tables 表示開啟表的數量，Opened_tables表示開啟過的表數量，如果Opened_tables數量過大，說明配置中 table_cache(5.1.3之後這個值叫做table_open_cache)值可能太小，我們查詢一下伺服器table_cache值：
mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 2048 |
+---------------+-------+
比較合適的值為：
Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%
六、程式使用情況
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+
如 果我們在MySQL伺服器配置檔案中設定了thread_cache_size，當客戶端斷開之後，伺服器處理此客戶的執行緒將會快取起來以響應下一個客戶 而不是銷燬（前提是快取數未達上限）。Threads_created表示建立過的執行緒數，如果發現Threads_created值過大的話，表明 MySQL伺服器一直在建立執行緒，這也是比較耗資源，可以適當增加配置檔案中thread_cache_size值，查詢伺服器 thread_cache_size配置：
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
示例中的伺服器還是挺健康的。
七、查詢快取(query cache)
mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+-------------------------+-----------+
MySQL查詢快取變數解釋：
Qcache_free_blocks：快取中相鄰記憶體塊的個數。數目大說明可能有碎片。FLUSH QUERY CACHE會對快取中的碎片進行整理，從而得到一個空閒塊。
Qcache_free_memory：快取中的空閒記憶體。
Qcache_hits：每次查詢在快取中命中時就增大
Qcache_inserts：每次插入一個查詢時就增大。命中次數除以插入次數就是不中比率。
Qcache_lowmem_prunes： 快取出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者記憶體 很少。（上面的 free_blocks和free_memory可以告訴您屬於哪種情況）
Qcache_not_cached：不適合進行快取的查詢的數量，通常是由於這些查詢不是 SELECT 語句或者用了now()之類的函式。
Qcache_queries_in_cache：當前快取的查詢（和響應）的數量。
Qcache_total_blocks：快取中塊的數量。
我們再查詢一下伺服器關於query_cache的配置：
mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
各欄位的解釋：
query_cache_limit：超過此大小的查詢將不快取
query_cache_min_res_unit：快取塊的最小大小
query_cache_size：查詢快取大小
query_cache_type：快取型別，決定快取什麼樣的查詢，示例中表示不快取 select sql_no_cache 查詢
query_cache_wlock_invalidate：當有其他客戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成再讀表獲取結果。
query_cache_min_res_unit的配置是一柄”雙刃劍”，預設是4KB，設定值大對大資料查詢有好處，但如果你的查詢都是小資料查詢，就容易造成記憶體碎片和浪費。
查詢快取碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查詢快取碎片率超過20%，可以用FLUSH QUERY CACHE整理快取碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小資料量的話。
查詢快取利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%
查詢快取利用率在25%以下的話說明query_cache_size設定的過大，可適當減小；查詢快取利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。
查詢快取命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%
示例伺服器 查詢快取碎片率 ＝ 20.46％，查詢快取利用率 ＝ 62.26％，查詢快取命中率 ＝ 1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。
八、排序使用情況
mysql> show global status like 'sort%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Sort_merge_passes | 29 |
| Sort_range | 37432840 |
| Sort_rows | 9178691532 |
| Sort_scan | 1860569 |
+-------------------+------------+
Sort_merge_passes 包括兩步。MySQL 首先會嘗試在記憶體中做排序，使用的記憶體大小由系統變數Sort_buffer_size 決定，如果它的大小不夠把所有的記錄都讀到記憶體中，MySQL 就會把每次在記憶體中排序的結果存到臨時檔案中，等MySQL 找到所有記錄之後，再把臨時檔案中的記錄做一次排序。這再次排序就會增加 Sort_merge_passes。實際上，MySQL會用另一個臨時檔案來存再次排序的結果，所以通常會看到 Sort_merge_passes增加的數值是建臨時檔案數的兩倍。因為用到了臨時檔案，所以速度可能會比較慢，增加 Sort_buffer_size 會減少Sort_merge_passes 和 建立臨時檔案的次數。但盲目的增加 Sort_buffer_size 並不一定能提高速度，
另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點的好處，
九、檔案開啟數(open_files)
mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 1410 |
+---------------+-------+
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 4590 |
+------------------+-------+
比較合適的設定：Open_files / open_files_limit * 100% <= 75％
十、表鎖情況
mysql> show global status like 'table_locks%';
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| Table_locks_immediate | 490206328 |
| Table_locks_waited | 2084912 |
+-----------------------+-----------+
Table_locks_immediate 表示立即釋放表鎖數，Table_locks_waited表示需要等待的表鎖數，如果Table_locks_immediate / Table_locks_waited >5000，最好採用InnoDB引擎，因為InnoDB是行鎖而MyISAM是表鎖，對於高併發寫入的應用InnoDB效果會好些。示例中的服務 器Table_locks_immediate / Table_locks_waited ＝ 235，MyISAM就足夠了。
十一、表掃描情況
mysql> show global status like 'handler_read%';
+-----------------------+-------------+
| Variable_name | Value |
+-----------------------+-------------+
| Handler_read_first | 5803750 |
| Handler_read_key | 6049319850 |
| Handler_read_next | 94440908210 |
| Handler_read_prev | 34822001724 |
| Handler_read_rnd | 405482605 |
| Handler_read_rnd_next | 18912877839 |
+-----------------------+-------------+
各欄位解釋參見,調出伺服器完成的查詢請求次數：
mysql> show global status like 'com_select';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Com_select | 222693559 |
+---------------+-----------+
計算表掃描率：
表掃描率 ＝ Handler_read_rnd_next / Com_select
如果表掃描率超過4000，說明進行了太多表掃描，很有可能索引沒有建好，增加read_buffer_size值會有一些好處，但最好不要超過8MB。
要檢視死鎖，你要show engine innodb status\G;
在MySQL5.6版本，在my.cnf配置檔案裡，加入
innodb_print_all_deadlocks = 1
就可以把死鎖資訊列印到錯誤日誌裡