最佳化mysql之key_buffer_size設定

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得知詳情。
對於1G記憶體的機器，如果不使用MyISAM表，推薦值是16M（8-64M）

提升效能的建議:
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的值,用基於記憶體的臨時表代替基於磁碟的 

MySQL最佳化小案例：key_buffer_size
key_buffer_size是對MyISAM表效能影響最大的一個引數，下面一臺以MyISAM為主要儲存引擎伺服器的配置：

mysql> SHOW VARIABLES LIKE '%key_buffer_size%';
　　
下面檢視key_buffer_size的使用情況：

mysql> SHOW GLOBAL STATUS LIKE '%key_read%';
+-------------------+-----------------+
| Variable_name     | Value           |
+-------------------+-----------------+
| Key_read_requests | 2454354135490   |
| Key_reads         | 23490           |
+-------------------+-----------------+
2 rows in set (0.00 sec)
一共有Key_read_requests個索引請求，一共發生了Key_reads次物理IO

Key_reads/Key_read_requests ≈ 0.1%以下比較好。

key_buffer_size設定注意事項

1.單個key_buffer的大小不能超過4G，如果設定超過4G，就有可能遇到下面3個bug:

2.建議key_buffer設定為實體記憶體的1/4(針對MyISAM引擎)，甚至是實體記憶體的30%~40%，如果key_buffer_size設定太大，系統就會頻繁的換頁，降低系統效能。因為MySQL使用作業系統的快取來快取資料，所以我們得為系統留夠足夠的記憶體；在很多情況下資料要比索引大得多。

3.如果機器效能優越，可以設定多個key_buffer,分別讓不同的key_buffer來快取專門的索引

上面只是對"新手"來說的，我們還可以更深入地最佳化key_buffer_size，使用"show status"來檢視"Key_read_requests, Key_reads, Key_write_requests  以及Key_writes "，以調整到更適合你的應用的大小，Key_reads/Key_read_requests的大小正常情況下得小於0.01

參考資料：

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

根據上述情況指令碼之家小編把key_buffer_size設定為2048M解決了問題。