原文連結：
key_buffer_size
本節提到的MySQL配置變量：
key_buffer_size
設定索引表可以使用的記憶體總空間，越大說明會有越多的 MyISAM
表的索引檔案*.MYI 儲存在内容中。
本節提到的MySQL狀态變量：
key_reads
通路索引表的查詢通路了硬碟的總次數，特指查詢使用的索引表沒有放入記憶體， 不得不去通路硬碟的情況，這個值越小越好。
key_read_requests
通路索引表的查詢總次數(包括cache在記憶體中的索引表和硬碟上的)， 這個值和前者的比例越大越好。
The key_buffer_size
is probably the most useful single variable to tweak. The larger you set it,
the more of your MyISAM table indexes you store in memory. With most queries
making use of an index, and memory being an order of magnitude faster than
disk, the importance of this variable cannot be overestimated.
key_buffer_size 可能是最有用的單獨的優化變量。設定的越大，将會有越多的MyISAM
索引表存儲在記憶體中。
On dedicated MySQL
servers, the rule-of-thumb is to aim to set the key_buffer_size to at
least a quarter, but no more than half, of the total amount of memory on the
server. Ideally, it will be large enough to contain all the indexes (the total
size of all .MYI files on the server). If you are unable to make it large
enough for this, the best way to fine-tune the setting is to compare the key_reads
and the key_read_requests status variables. The latter is the total
number of requests making use of an index, while the former is the total number
of those requests that had to read from disk. You want at least 100 requests to
every request from disk, preferably a lot more. Have a look at scenario 1, with
the same query run a few seconds apart.
在專用的MySQL伺服器上，根據經驗設定 key_buffer_size 至少是總記憶體的1/4，但是
不能超過總記憶體的一半，完美的情況是，這個值足夠大，應該大于所有索引表.MYI檔案的
總和。如果你不确定到底多大的值合适，最好的辦法是在調整設定時比較
key_reads 和 key_read_requests 這個狀态值。
後面的值是使用索引的請求的總數，而前面的值是這些請求中去讀磁盤的總次數。
情景分析1：
mysql> SHOW STATUS LIKE '%key_read%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Key_read_requests | 3606100254 |
| Key_reads | 2594030 |
+-------------------+------------+
mysql> SHOW STATUS LIKE '%key_read%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Key_read_requests | 3606102741 |
| Key_reads | 2594030 |
+-------------------+------------+
This is a healthy ratio,
around 1400 to 1. Of the 2500 index requests between the two samples, none
required the disk. On this server, the key_buffer is set to 768M, while
the total memory available is 3GB.
上面顯示的情況是比較正常的比例，每 1400 個請求中有 1 個通路磁盤。
這裡 key_buffer 設定為 768M，總記憶體是 3GB.
情景分析2：
mysql> SHOW STATUS LIKE '%key_read%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| Key_read_requests | 609601541 |
| Key_reads | 46729832 |
+-------------------+-----------+
In scenario 2, it is
shocking, about 13 to 1. On this server, the key_buffer_size was set to
16MB out of 64MB. If you are in a similar situation, it is clear what your next
hardware upgrade should be. RAM is always the primary hardware upgrade you can
do to improve your system.
情形2非常糟糕，請求和磁盤通路的比例為： 13:1, key_buffer_size 設定的是
16M ，系統記憶體是 64M. 如果你處在同樣的情況下，那麼非常明顯你需要更新硬體增加
系統記憶體。
The table_cache
本節提到的MySQL設定變量：
table_cache
允許 MySQL
打開的表的最大個數，并且這些都cache在記憶體中。
本節提到的MySQL狀态變量：
Open_tables
打開的表的個數，并這些表在記憶體cache中。
Opened_tables
累計打開過的表的個數，如果這個值很大并且張的很快，說明
table_cache 設定太小。
The table_cache
remains a useful variable to tweak. Each time MySQL accesses a table, it places
it in the cache. If your system accesses many tables, it is faster to have
these in the cache. One thing to note is that you may have more open tables
than there are database tables on the server. MySQL, being multi-threaded, may
be running many queries on the table at one time, and each of these will open a
table. A good way to see whether your system needs to increase this is to
examine the value of open_tables at peak times. If you find it stays at
the same value as your table_cache value, and then the number of opened_tables starts
rapidly increasing, you should increase the table_cache if you have enough
memory. Look at these three scenarios, during peak times.
每次 MySQL 通路一個表，将會把它放入 cache 中，如果你的系統要通路很多表，
能在cache中通路是非常塊的。
有一個情況是你的伺服器上有很多打開的表，而且遠遠大于你的資料庫中實際擁有的
表，這是因為MySQL是多線程的，同一時間也許會有很多表被查詢，每個線程都會打開
一個表，是以總數會比較多。一個比較好的判斷你的系統是否需要增加這個值的辦法
是在通路峰值的時候檢查 open_tables 這個值，如果這個值和你設定的 table_cache
值一樣，而且 opened_tables 的增長速度很快，那麼你應該增加這個值，如果你有
空閑的記憶體。
下面列舉3個在峰值情況下的例子：
情景分析1：
mysql> SHOW STATUS LIKE 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 98 |
| Opened_tables | 1513 |
+---------------+-------+
The table_cache is
set to 512, and the server has been running for a long time. If the server is
taking strain elsewhere, the table_cache setting could probably be reduced
safely.
在上面這個情形中，table_cache 設定的為 512，資料庫已經運作了很長一段時間，
如果伺服器在其他方面存在性能問題或者資源比較緊張，可以考慮适當的減小這個
值。
情景分析2：
mysql> SHOW STATUS LIKE 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 64 |
| Opened_tables | 517 |
+---------------+-------+
The table_cache is
set to 64, and the server has been running for a long time. Even though open_tables
is at its maximum, the number of open_tables is very low considering that the
server has been up for ages. There is probably not much benefit in upping the table_cache.
This example came from a development server.
上面的這個情形，table_cache 設定的是 64, 伺服器也運作了好久，即使 open_tables
的值已經達到了最大值，但是 opend_tables 的值比較小，可以認為伺服器的狀況正常，
增加 table_cache 的值并不一定會有好的效果。
情景分析3：
mysql> SHOW STATUS LIKE 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 64 |
| Opened_tables | 13918 |
+---------------+-------+
The table_cache is
set to 64, and the server has been running for a short time. This time the table_cache
is clearly set too low. The open_tables is running at maximum, and the
number of opened_tables is already high. If you have the memory, up the table_cache.
上面的情形中， table_cache 設定為 64, 伺服器運作的時間很短，open_tables 已經達到了
最大值，而 opened_tables 的值非常大，如果有足夠的記憶體，應該繼續增大 table_cache 的
值。
sort_buffer
本節提到的MySQL的配置變量：
sort_buffer
執行 myisamchk 和查詢中有 sort
函數時使用的記憶體，在mysqld的配置小節 中設定為2M-8M比較合适.
The sort_buffer is
very useful for speeding up myisamchk operations (which is why it is set
much higher for that purpose in the default configuration files), but it can
also be useful everyday when performing large numbers of sorts. It defaults to
2M in the my-huge.cnf sample file, but I have successfully upped it to
9MB on a 3GB server running quite a few sorts.
這個值非常有助于提高執行 myisamchk 時的速度，是以在配置檔案中關于 myisamchk 的
配置中這個的值設定的比較大。
這個值對于執行大量的 sort 排序操作時也非常有幫助，在my-huge.cnf配置檔案中設定
為2M，但在我的3G記憶體的伺服器上設定的是9M，排序的查詢比較少。
read_rnd_buffer_size
The read_rnd_buffer_size
is used after a sort, when reading rows in sorted order. If you use many
queries with ORDER BY, upping this can improve performance. Remember
that, unlike key_buffer_size and table_cache, this buffer is allocated
for each thread. This variable was renamed from record_rnd_buffer in
MySQL 4.0.3. It defaults to the same size as the read_buffer_size, which
defaults to 128KB. A rule-of-thumb is to allocate 1KB for each 1MB of memory on
the server, for example 3MB on a machine with 3GB memory.
read_rnd_buffer_size 用在 sort 之後，以排序後的順序讀取記錄時。如果有許多
查詢都使用了 ORDER BY 函數，增大這個值可以提高性能。需要注意的是，這個值
不象 key_buffer_size 和 table_cache ，它的記憶體配置設定是對每個線程的，預設
會被設定的和 read_buffer_size 一樣大小，為 128KB，經驗值是每1M記憶體配置設定1KB，
比如3G記憶體的伺服器可以配置設定3M。
tmp_table_size
This variable determines
the maximum size for a temporary table in memory. If the table becomes too
large, a MYISAM table is created on disk. Try to avoid temporary tables by
optimizing the queries where possible, but where this is not possible, try to
ensure temporary tables are always stored in memory. Watching the processlist
for queries with temporary tables that take too long to resolve can give you an
early warning that tmp_table_size needs to be upped. Be aware that
memory is also allocated per-thread. An example where upping this worked for
more was a server where I upped this from 32MB (the default) to 64MB with
immediate effect. The quicker resolution of queries resulted in less threads
being active at any one time, with all-round benefits for the server, and
available memory.
這個值決定記憶體中臨時表的大小，如果表太大，那麼MyISAM表将會建立在磁盤上。
盡量優化查詢避免使用臨時表，如果不行，確定臨時表總是建立在記憶體中。
查詢 processlist 如果查詢使用臨時表使用的時間比較長，說明有必要增加
tmp_table_size 的大小。也需要注意這個值是for 每個線程的。一個例子是我
在一個伺服器上把這個值從32M增加到64M後立即就有效果。