MySQL 5.7-8.8.5 Estimating Query Performance(评估查询性能)

In most cases, you can estimate query performance by counting disk seeks. For small tables, you can usually find a row in one disk seek (because the index is probably cached). For bigger tables, you can estimate that, using B-tree indexes, you need this many seeks to find a row: log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1.

在大多数情况下，您可以通过计算磁盘搜索次数来估计查询性能。对于小表，通常可以在一个磁盘搜索中找到一行(因为索引可能被缓存)。对于更大的表，您可以使用b -树索引估计，您需要这么多的搜索来查找一行:log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1。

In MySQL, an index block is usually 1,024 bytes and the data pointer is usually four bytes. For a 500,000-row table with a key value length of three bytes (the size of MEDIUMINT), the formula indicates log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks.

在MySQL中，索引块通常是1024字节，数据指针通常是4字节。对于一个50万行、键值长度为3字节(MEDIUMINT的大小)的表，公式表示log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seek。

This index would require storage of about 500,000 * 7 * 3/2 = 5.2MB (assuming a typical index buffer fill ratio of 2/3), so you probably have much of the index in memory and so need only one or two calls to read data to find the row.

这个索引将需要大约500,000 * 7 * 3/2 = 5.2MB的存储空间(假设典型的索引缓冲区填充率为2/3)，所以您可能在内存中有很多索引，因此只需要一到两次调用来读取数据来查找行。

For writes, however, you need four seek requests to find where to place a new index value and normally two seeks to update the index and write the row.

然而，对于写操作，您需要4个搜索请求来找到放置新索引值的位置，通常需要2个搜索请求来更新索引并写入行。

The preceding discussion does not mean that your application performance slowly degenerates by log N. As long as everything is cached by the OS or the MySQL server, things become only marginally slower as the table gets bigger. After the data gets too big to be cached, things start to go much slower until your applications are bound only by disk seeks (which increase by log N). To avoid this, increase the key cache size as the data grows.

前面的讨论并不意味着您的应用程序性能会因为日志n而缓慢下降，只要所有的东西都被操作系统或MySQL服务器缓存，那么随着表的增大，事情只会变得稍微慢一些。当数据变得太大而无法缓存时，事情就会变得非常慢，直到你的应用程序只被磁盘搜索(增加log N)所限制。为了避免这种情况，随着数据的增长增加关键缓存的大小。

For MyISAM tables, the key cache size is controlled by the key_buffer_size system variable. See Section 5.1.1, “Configuring the Server”.