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 |
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;
Query OK, 0 rows affected (0.01 sec)
| 402653184 |
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;
| 2147483648 |
mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
| 4 |
# Chunk size was set to 1GB (1073741824 bytes) on startup but was
# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances
| 536870912 |
監控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. |
檢視錯誤日志：
（增大）
[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: 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: completed to resize buffer pool from 4294967296 to 134217728.
innodb_additional_mem_pool_size
用來存放Innodb的内部目錄，這個值不用配置設定太大，系統可以自動調。通常設定16Ｍ夠用了，如果表比較多，可以适當的增大。
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個日值組。預設為兩個。
innodb_log_files_in_group=3
innodb_log_buffer_size：
作用：事務在記憶體中的緩沖，也就是日志緩沖區的大小， 預設設定即可，具有大量事務的可以考慮設定為16M。
如果這個值增長過快，可以适當的增加innodb_log_buffer_size
另外如果你需要處理大理的TEXT，或是BLOB字段，可以考慮增加這個參數的值。
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程序的崩潰會删除崩潰前最後一秒的事務。
從以上分析，當這個值不為１時，可以取得較好的性能，但遇到異常會有損失，是以需要根據自已的情況去衡量。
innodb_flush_logs_at_trx_commit=1
3）檔案IO配置設定，空間占用方面
innodb_file_per_table
作用：使每個Innodb的表，有自已獨立的表空間。如删除檔案後可以回收那部分空間。預設是關閉的，建議打開（innodb_file_per_table=1）
配置設定原則：隻有使用不使用。但DB還需要有一個公共的表空間。
innodb_file_per_table=1
innodb_file_io_threads
作用：檔案讀寫IO數，這個參數隻在Windows上起作用。在Linux上隻會等于4，預設即可！
innodb_file_io_threads=4
innodb_open_files
作用：限制Innodb能打開的表的資料。
配置設定原則：這個值預設是300。如果庫裡的表特别多的情況，可以适當增大為1000。innodb_open_files的大小對InnoDB效率的影響比較小。但是在InnoDBcrash的情況下，innodb_open_files設定過小會影響recovery的效率。是以用InnoDB的時候還是把innodb_open_files放大一些比較合适。
innodb_open_files=800
innodb_data_file_path
指定表資料和索引存儲的空間，可以是一個或者多個檔案。最後一個資料檔案必須是自動擴充的，也隻有最後一個檔案允許自動擴充。這樣，當空間用完後，自動擴充資料檔案就會自動增長（以8MB為機關）以容納額外的資料。
例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend 兩個資料檔案放在不同的磁盤上。資料首先放在ibdata1 中，當達到900M以後，資料就放在ibdata2中。
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了
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct
作用：在buffer pool緩沖中，允許Innodb的髒頁的百分比，值在範圍1-100,預設為90，建議保持預設。
這個參數的另一個用處：當Innodb的記憶體配置設定過大，緻使Swap占用嚴重時，可以适當的減小調整這個值，使達到Swap空間釋放出來。建義：這個值最大在90%，最小在15%。太大，緩存中每次更新需要緻換資料頁太多，太小，放的資料頁太小，更新操作太慢。
innodb_max_dirty_pages_pct＝90
動态更改需要有管理者權限：
set global innodb_max_dirty_pages_pct=50;
innodb_thread_concurrency
同時在Innodb核心中處理的線程數量。建議預設值。
innodb_thread_concurrency = 16
5）公共參數調優
skip-external-locking
MyISAM存儲引擎也同樣會使用這個參數，MySQL4.0之後，這個值預設是開啟的。
作用是避免MySQL的外部鎖定(老版本的MySQL此參數叫做skip-locking)，減少出錯幾率增強穩定性。建議預設值。
skip-name-resolve
禁止MySQL對外部連接配接進行DNS解析（預設是關閉此項設定的，即預設解析DNS），使用這一選項可以消除MySQL進行DNS解析的時間。
但需要注意，如果開啟該選項，則所有遠端主機連接配接授權都要使用IP位址方式，否則MySQL将無法正常處理連接配接請求！如果需要，可以設定此項。
設定方法，在my.cnf檔案裡：（我這線上mysql資料庫中打開了這一設定）
skip-name-resolve
max_connections
設定最大連接配接（使用者）數，每個連接配接MySQL的使用者均算作一個連接配接，max_connections的預設值為100。此值需要根據具體的連接配接數峰值設定。
max_connections = 3000
query_cache_size
查詢緩存大小，如果表的改動非常頻繁，或者每次查詢都不同，查詢緩存的結果會減慢系統性能。可以設定為0。
query_cache_size = 512M
sort_buffer_size
connection級的參數，排序緩存大小。一般設定為2-4MB即可。
sort_buffer_size = 1024M
read_buffer_size
connection級的參數。一般設定為2-4MB即可。
read_buffer_size = 1024M
max_allowed_packet
網絡包的大小，為避免出現較大的網絡包錯誤，建議設定為16M
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即可滿足需求。
table_open_cache = 512
myisam_sort_buffer_size
實際上這個myisam_sort_buffer_size參數意義不大，這是個字面上蒙人的參數，它用于ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 等指令時需要的記憶體。預設值即可。
myisam_sort_buffer_size = 8M
thread_cache_size
線程緩存，如果一個用戶端斷開連接配接，這個線程就會被放到thread_cache_size中（緩沖池未滿），SHOW STATUS LIKE 'threads%';如果 Threads_created 不斷增大，那麼目前值設定要改大，改到 Threads_connected 值左右。（通常情況下，這個值改善性能不大），預設8即可
thread_cache_size = 8
線程并發數，建議設定為CPU核心數*2
innodb_thread_concurrency = 8
key_buffer_size
僅作用于 MyISAM存儲引擎，用來設定用于緩存 MyISAM存儲引擎中索引檔案的記憶體區域大小。如果我們有足夠的記憶體，這個緩存區域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引，以盡可能提高性能。不要設定超過可用記憶體的30%。即使不用MyISAM表，也要設定該值8-64M，用于臨時表。
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]
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
back_log = 600
max_connect_errors = 3000
binlog_cache_size = 16M
max_heap_table_size = 16M
tmp_table_size = 256M
read_rnd_buffer_size = 1024M
join_buffer_size = 1024M
key_buffer_size = 8192M
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
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_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_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800
[mysqldump]
quick
[myisamchk]
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
--------------------------------------------------------------------------------------------------------------------------------------
下面分享一個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.
# 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_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
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
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(這裡主要是複制功能，也就是主從,提前配置好，後面講主從配置)
#二進制日志
#為了在最大程式上保證複制的InnoDB事務持久性和一緻性
#啟用此兩項,可用于實作在崩潰時保證二進制及從伺服器安全的功能
#設定清除日志時間
#行複制
#mysql資料庫事務隔離級别有四種(READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE)
#内部記憶體臨時表的最大值
#即跳過外部鎖定
#MySQL能暫存的連接配接數量(根據實際設定)
#指定索引緩沖區的大小,隻對MyISAM表起作用,這裡寫上也沒有關系
#這條指令限定用于每個資料庫線程的棧大小
#當一個查詢不斷地掃描某一個表,MySQL會為它配置設定一段記憶體緩沖區
#線程緩存
#查詢緩存大小
#内部記憶體臨時表的最大值,每個線程都要配置設定
#将查詢結果放入查詢緩存中
#代表在事務過程中容納二進制日志SQL語句的緩存大小
#同樣是緩存表大小
#緩存線程
#推薦設定為伺服器 CPU核數的2倍
#表和表聯接的緩沖區的大小
#是一個connection級參數,在每個connection第一次需要使用這個buffer的時候,一次性配置設定設定的記憶體
sort_buffer_size=8M
#随機讀取資料緩沖區使用記憶體
#是一個MySQL中與安全有關的計數器值,它負責阻止過多嘗試失敗的用戶端以防止暴力破解密碼
#連接配接數
#開啟查詢緩存
#mysql伺服器能夠工作在不同的模式下,并能針對不同的用戶端以不同的方式應用這些模式
下面列出了對性能優化影響較大的主要變量，主要分為連接配接請求的變量和緩沖區變量。
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檔案
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';
+-----------------+-------+
| 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 |
配置設定了512MB記憶體給key_buffer_size，我們再看一下key_buffer_size的使用情況：
mysql> show global status like 'key_read%';
+------------------------+-------------+
| Variable_name | Value |
| Key_read_requests | 27813678764 |
| Key_reads | 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%';
| 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%';
+---------------+-------+
| 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';
| 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';
| 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%
查詢緩存使用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%
查詢緩存命中率 = (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';
| Open_files | 1410 |
mysql> show variables like 'open_files_limit';
| 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%';
+-----------------------+-------------+
| 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
就可以把死鎖資訊列印到錯誤日志裡
*************** 當你發現自己的才華撐不起野心時，就請安靜下來學習吧！***************