溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
簡單介紹
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
-----------------------------------------------------------------------------------------------------------------------------------------------
下面是線上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
就可以把死鎖信息打印到錯誤日志里
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
