mySQL最佳化, my.ini 配置說明

[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock

skip-name-resolve       #禁止MySQL對外部串連進行DNS解析
skip-grant-tables
#禁止MySQL對外部串連進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間。但需要注意，如果開啟該選項，則所有遠程主機串連授權都要使用IP地址方式，否則MySQL將無法正常處理串連請求！註：如果用winform串連mysql，加入此句速度會有很大的提升

skip-locking
# 避免MySQL的外部鎖定，減少出錯幾率增強穩定性。

back_log = 384
指定MySQL可能的串連數量。當MySQL主線程在很短的時間內接收到非常多的串連請求，該參數生效，主線程花費很短的時間檢查串連並且啟動一個新線程。 back_log參數的值指出在MySQL暫時停止回應新請求之前的短時間內多少個請求可以被存在堆棧中。 如果系統在一個短時間內有很多串連，則需要增大該參數的值，該參數值指定到來的TCP/IP串連的偵聽隊列的大小。不同的作業系統在這個隊列大小上有它自己的限制。 試圖設定back_log高於你的作業系統的限制將是無效的。預設值為50。對於Linux系統推薦設定為小於512的整數。

key_buffer_size = 32M
# key_buffer_size這對MyISAM表來說非常重要。如果只是使用MyISAM表，可以把它設定為可用記憶體的 30-40%。合理的值取決於索引大小、資料量以及負載 -- 記住，MyISAM表會使用作業系統的緩衝來快取資料，因此需要留出部分記憶體給它們，很多情況下資料比索引大多了。儘管如此，需要總是檢查是否所有的 key_buffer 都被利用了 -- .MYI 檔案只有 1GB，而 key_buffer 卻設定為 4GB 的情況是非常少的。這麼做太浪費了。如果你很少使用MyISAM表，那麼也保留低於 16-32MB 的key_buffer_size 以適應給予磁碟的暫存資料表索引所需。

innodb_buffer_pool_size = 2.4G
#這對Innodb表來說非常重要。Innodb相比MyISAM表對緩衝更為敏感。MyISAM可以在預設的 key_buffer_size 設定下啟動並執行可以，然而Innodb在預設的innodb_buffer_pool_size 設定下卻跟蝸牛似的。由於Innodb把資料和索引都緩衝起來，無需留給作業系統太多的記憶體，因此如果只需要用Innodb的話則可以設定它高達 70-80% 的可用記憶體。-- 如果你的資料量不大，並且不會暴增，那麼無需把innodb_buffer_pool_size 設定的太大了。

innodb_additional_pool_size = 20M
#這個選項對效能影響並不太多，至少在有差不多足夠記憶體可分配的作業系統上是這樣。不過如果你仍然想設定為 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的記憶體有多少。

innodb_log_file_size = 512M
#在高寫入負載尤其是大資料集的情況下很重要。這個值越大則效能相對越高，但是要注意到可能會增加恢復。我經常設定為64-512MB，根據伺服器大小而異。

innodb_log_buffer_size =16M
#預設的設定在中等強度寫入負載以及較短事務的情況下，伺服器效能還可以。如果存在更新操作峰值或者負載較大，就應該考慮加大它的值了。如果它的值設定太高了，可能會浪費記憶體 -- 它每秒都會重新整理一次，因此無需設定超過1秒所需的記憶體空間。通常8-16MB就足夠了。越小的系統它的值越小。

innodb_flush_logs_at_trx_commit = 2
#是否為Innodb比MyISAM慢1000倍而頭大?看來也許你忘了修改這個參數了。預設值是 1，這意味著每次提交的更新事務(或者每個事務之外的語句)都會重新整理到磁碟中，而這相當耗費資源，尤其是沒有電池備用緩衝時。很多應用程式，尤其是從 MyISAM轉變過來的那些，把它的值設定為 2 就可以了，也就是不把日誌重新整理到磁碟上，而只重新整理到作業系統的緩衝上。日誌仍然會每秒重新整理到磁碟中去，因此通常不會丟失每秒1-2次更新的消耗。如果設定為0就快很多了，不過也相對不安全了 -- MySQL伺服器崩潰時就會丟失一些事務。設定為2指揮丟失重新整理到作業系統緩衝的那部分事務。

max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
#查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配記憶體是每串連獨佔！如果有100個串連，那麼實際分配的總共排序緩衝區大小為100 × 6 ＝ 600MB。所以，對於記憶體在4GB左右的伺服器推薦設定為6-8M。

read_buffer_size = 4M
#讀查詢操作所能使用的緩衝區大小。和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享！

join_buffer_size = 8M
#聯集查詢操作所能使用的緩衝區大小，和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享！

myisam_sort_buffer_size = 64M
table_cache = 512
#開啟一個表的開銷可能很大。例如MyISAM把MYI檔案頭標誌該表正在使用中。你肯定不希望這種操作太頻繁，所以通常要加大緩衝數量，使得足以最大限度地緩衝開啟的表。它需要用到作業系統的資源以及記憶體，對當前的硬體設定來說當然不是什麼問題了。如果你有200多個表的話，那麼設定為 1024 也許比較合適(每個線程都需要開啟表)，如果串連數比較大那麼就加大它的值。我曾經見過設定為100,000的情況。

thread_cache_size = 64
#線程的建立和銷毀的開銷可能很大，因為每個線程的串連/斷開都需要。我通常至少設定為 16。如果應用程式中有大量的跳躍並發串連並且 Threads_Created 的值也比較大，那麼我就會加大它的值。它的目的是在通常的操作中無需建立新線程。

query_cache_size = 64M
#指定MySQL查詢緩衝區的大小。可以通過在MySQL控制台執行以下命令觀察：

# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
# 如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩衝不夠的情況；如果Qcache_hits的值非常大，則表明查詢緩衝使用非常頻繁，如果該值較小反而會影響效率，那麼可以考慮不用查詢緩衝；Qcache_free_blocks，如果該值非常大，則表明緩衝區中片段很多。

tmp_table_size = 256M
max_connections = 768
#指定MySQL允許的最大串連進程數。如果在訪問論壇時經常出現Too Many Connections的錯誤提 示，則需要增大該參數值。

max_connect_errors = 10000000
wait_timeout = 10
#指定一個請求的最大連線時間，對於4GB左右記憶體的伺服器可以設定為5-10。

thread_concurrency = 8
#該參數取值為伺服器邏輯CPU數量×2，在本例中，伺服器有2顆物理CPU，而每顆物理CPU又支援H.T超執行緒，所以實際取值為4 × 2 ＝ 8

skip-networking
#開啟該選項可以徹底關閉MySQL的TCP/IP串連方式，如果WEB伺服器是以遠端連線的方式訪問MySQL資料庫伺服器則不要開啟該選項！否則將無法正常串連！

show status 命令
含義如下:
aborted_clients 用戶端非法中斷串連次數
aborted_connects 串連mysql失敗次數
com_xxx xxx命令執行次數,有很多條
connections 串連mysql的數量
Created_tmp_disk_tables 在磁碟上建立的暫存資料表
Created_tmp_tables 在記憶體裡建立的暫存資料表
Created_tmp_files 臨時檔案數
Key_read_requests The number of requests to read a key block from the cache
Key_reads The number of physical reads of a key block from disk
Max_used_connections 同時使用的串連數
Open_tables 開放的表
Open_files 開放的檔案
Opened_tables 開啟的表
Questions 提交到server的查詢數
Sort_merge_passes 如果這個值很大,應該增加my.cnf中的sort_buffer值
Uptime 伺服器已經工作的秒數

提升效能的建議:
1.如果opened_tables太大,應該把my.cnf中的table_cache變大
2.如果Key_reads太大,則應該把my.cnf中key_buffer_size變大.可以用Key_reads/Key_read_requests計算出cache失敗率
3.如果Handler_read_rnd太大,則你寫的SQL語句裡很多查詢都是要掃描整個表,而沒有發揮索引的鍵的作用
4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections計算cache命中率
5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基於記憶體的暫存資料表代替基於磁碟的