什么是 MySQL“关键效率”

What is MySQL "Key Efficiency"

提问by tylerl

MySQL Workbench reports a value called "Key Efficiency" in association with server health. What does this mean and what are its implications?

MySQL Workbench 报告一个与服务器健康相关的名为“Key Efficiency”的值。这是什么意思？它的含义是什么？

From MySQL.com, "Key Efficiency" is:

从MySQL.com，“关键效率”是：

...an indication of the number of key_read_requeststhat resulted in actual key_reads.

...key_read_requests导致实际key_reads.

Ok, so what does that mean. What does it tell me about how I'm supposed to tune the server?

好的，那是什么意思。它告诉我应该如何调整服务器？

回答by Martin

"Key Efficiency" is an indication of how much value you are getting from the index caches held within MySQL's memory. If your key efficiency is high, then most often MySQL is performing key lookups from within memory space, which is much faster than having to retrieve the relevant index blocks from disk.

“关键效率”表明您从 MySQL 内存中的索引缓存中获得了多少价值。如果您的键效率很高，那么 MySQL 通常从内存空间内执行键查找，这比必须从磁盘检索相关索引块要快得多。

The way to improve key efficiency is to dedicate more of your system memory to MySQL's index caches. How you do this depends on the storage engine you use. For MyISAM, increase the value of key-buffer-size. For InnoDB, increase the value of innodb-buffer-pool-size.

提高密钥效率的方法是将更多的系统内存专用于 MySQL 的索引缓存。您如何执行此操作取决于您使用的存储引擎。对于 MyISAM，增加 key-buffer-size 的值。对于 InnoDB，增加 innodb-buffer-pool-size 的值。

However, as Michael Eakins points out, the operating system also holds caches of disk blocks which it has accessed recently. The more memory that your operating system has available, the more disk blocks it can cache. Further, the disk drives themselves (and disk controllers in some cases), also have caches - which again can speed up retrieving data from disk. The hierarchy is a bit like this:

然而，正如 Michael Eakins 指出的那样，操作系统还保存了它最近访问过的磁盘块的缓存。操作系统可用的内存越多，它可以缓存的磁盘块就越多。此外，磁盘驱动器本身（在某些情况下还有磁盘控制器）也有缓存——这再次可以加快从磁盘检索数据的速度。层次结构有点像这样：

1. fastest - retrieving index data from within MySQL's index cache. The cost is a few memory operations.
2. retrieving index data that is held in the OS file system cache. The cost is a system call (for the read), and some memory operations.
3. retrieving index data that is held in the disk system cache (controller and drives). The cost is a system call (for the read), communication with the disk device, and some memory operations.
4. slowest - retrieving index data from the disk surface. The cost is a system call, communication with the device, physical movement of the disk (arm movement + rotation).

1. 最快 - 从 MySQL 的索引缓存中检索索引数据。代价是一些内存操作。
2. 检索保存在操作系统文件系统缓存中的索引数据。成本是系统调用（用于读取）和一些内存操作。
3. 检索保存在磁盘系统缓存（控制器和驱动器）中的索引数据。代价是系统调用（用于读取）、与磁盘设备的通信以及一些内存操作。
4. 最慢 - 从磁盘表面检索索引数据。成本是系统调用、与设备的通信、磁盘的物理运动（手臂运动 + 旋转）。

In practice, the difference between 1 and 2 is almost unnoticeable unless your system is very busy. Also, it is unlikely (unless your system has less spare RAM than your disk controller) that scenario 3 will come into play.

实际上，除非您的系统非常繁忙，否则 1 和 2 之间的差异几乎不会引起注意。此外，场景 3 不太可能（除非您的系统的备用 RAM 比您的磁盘控制器少）。

I have used servers with MyISAM tables with relatively small index caches (512MB), but massive system memory (64GB) and have found it difficult to demonstrate the value of increasing the size of the index cache. I guess it depends on what else is happening on your server. If all you are running is a MySQL data base, it is quite likely that the OS cache will be quite effective. However, if you run other jobs on the same server and these use lots of memory / disk accesses, then these might evict valuable cached index blocks leading to MySQL hitting disk more often.

我使用了带有 MyISAM 表的服务器，索引缓存相对较小（512MB），但系统内存很大（64GB），并且发现很难证明增加索引缓存大小的价值。我想这取决于您的服务器上发生了什么。如果您运行的只是 MySQL 数据库，则操作系统缓存很可能会非常有效。但是，如果您在同一台服务器上运行其他作业并且这些作业使用大量内存/磁盘访问，那么这些可能会驱逐有价值的缓存索引块，从而导致 MySQL 更频繁地访问磁盘。

An interesting exercise (if you have time) is to tinker with your system to make it run slower. Running a standard workload on large tables, reduce the MySQL buffers until the impact becomes noticeable. Flush your file system cache by pumping huge amounts (greater than RAM) of irrelevant data through your file system ( cat large-file > /dev/null ). Watch iostat as your queries run.

一个有趣的练习（如果你有时间的话）是修补你的系统，让它运行得更慢。在大型表上运行标准工作负载，减少 MySQL 缓冲区，直到影响变得明显。通过通过文件系统（ cat large-file > /dev/null ）抽取大量（大于 RAM）不相关数据来刷新文件系统缓存。在查询运行时观察 iostat。

"Key Efficiency" is NOT a measure of how good your keys are. Well designed keys will have a much larger impact on performance than high "Key Efficiency". MySQL does not have much to help you there, unfortunately.

“密钥效率”不是衡量您的密钥有多好的标准。设计良好的键对性能的影响比高“键效率”要大得多。不幸的是，MySQL 在这方面没有太多帮助。

回答by Michael Eakins

Key_read_requests is the number of requests to read a key block from the cache. While key_reads is the number of physical reads of a key block from disk. So these 2 variables can increase independently. (http://bugs.mysql.com/bug.php?id=28384)

Key_read_requests 是从缓存中读取关键块的请求数。而 key_reads 是从磁盘物理读取关键块的次数。所以这两个变量可以独立增加。( http://bugs.mysql.com/bug.php?id=28384)

Which is still as clear as mud.

还是像泥一样清澈。

On to the next bit of explaination:

进入下一个解释：

A partially valid use of Key_reads

There is a partially valid reason to examine Key_reads, assuming that we care about the number of physical reads that occur, because we know that disks are very slow relative to other parts of the computer. And here's where I return to what I called "mostly factual" above, because Key_reads actually aren't physical disk reads at all. If the requested block of data isn't in the operating system's cache, then a Key_read is a disk read -- but if it is cached, then it's just a system call. However, let's make our first hard-to-prove assumption:

Hard-to-prove assumption #1: A Key_read might correspond to a physical disk read, maybe. If we take that assumption as true, then what other reason might we have for caring about Key_reads? This assumption leads to "a cache miss is significantly slower than a cache hit," which makes sense. If it were just as fast to do a Key_read as a Key_read_request, what use would the key buffer be anyway? Let's trust MyISAM's creators on this one, because they designed a cache hit to be faster than a miss. (http://planet.mysql.com/entry/?id=23679)

Key_reads 的部分有效使用

假设我们关心发生的物理读取次数，有一个部分有效的理由来检查 Key_reads，因为我们知道磁盘相对于计算机的其他部分非常慢。这就是我回到上面所说的“主要是事实”的地方，因为 Key_reads 实际上根本不是物理磁盘读取。如果请求的数据块不在操作系统的缓存中，则 Key_read 是磁盘读取——但如果它被缓存，那么它只是一个系统调用。但是，让我们做出第一个难以证明的假设：

难以证明的假设 #1：Key_read 可能对应于物理磁盘读取。如果我们认为这个假设是正确的，那么我们还有什么其他理由关心 Key_reads？这个假设导致“缓存未命中比缓存命中慢得多”，这是有道理的。如果执行 Key_read 和执行 Key_read_request 一样快，那么密钥缓冲区有什么用呢？让我们相信 MyISAM 的创建者，因为他们设计了比未命中更快的缓存命中。( http://planet.mysql.com/entry/?id=23679)