原文链接：
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后立即就有效果。