MySQL 8.0 Reference Manual（读书笔记59节--Understanding the Query Exec

王业祥

书接上回
• Recursive (JSON property: recursive)
This indicates that the row applies to the recursive SELECT part of a recursive common table expression.
• Rematerialize (JSON property: rematerialize)
Rematerialize (X,...) is displayed in the EXPLAIN row for table T, where X is any lateral【ˈlætərəl 横向的;侧面的;向侧面移动的;】 derived table whose rematerialization is triggered when a new row of T is read. For example:

1. SELECT
2. ...
3. FROM
4. t,
5. LATERAL (derived table that refers to t) AS dt
6. ...

复制代码

The content of the derived table is rematerialized to bring it up to date each time a new row of t is processed by the top query.
• Scanned N databases (JSON property: message)
This indicates how many directory scans the server performs when processing a query for INFORMATION_SCHEMA tables.The value of N can be 0, 1, or all.
• Select tables optimized away (JSON property: message)
The optimizer determined 1) that at most one row should be returned, and 2) that to produce this row, a deterministic【dɪˌtɜːrmɪˈnɪstɪk (思想、解释等)基于决定论的;(力量、因素)不可抗拒的，不可逆转的;】 set of rows must be read. When the rows to be read can be read during the optimization phase (for example, by reading index rows), there is no need to read any tables during query execution.
The first condition is fulfilled when the query is implicitly grouped (contains an aggregate function but no GROUP BY clause). The second condition is fulfilled when one row lookup is performed per index used. The number of indexes read determines the number of rows to read.
Consider the following implicitly grouped query:

1. SELECT MIN(c1), MIN(c2) FROM t1;

复制代码

Suppose that MIN(c1) can be retrieved by reading one index row and MIN(c2) can be retrieved by reading one row from a different index. That is, for each column c1 and c2, there exists an index where the column is the first column of the index. In this case, one row is returned, produced by reading two deterministic rows.
This Extra value does not occur if the rows to read are not deterministic. Consider this query:

1. SELECT MIN(c2) FROM t1 WHERE c1 <= 10;

复制代码

The Com_explain_other status variable indicates the number of EXPLAIN FOR CONNECTION statements executed.
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.
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.
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.
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.
The preceding discussion does not mean that your application performance slowly degenerates【dɪˈdʒenəreɪts 恶化;衰退;蜕变;】 by log N. As long as everything is cached by the OS or the MySQL server, things become only marginally【ˈmɑːrdʒɪnəli 轻微地;很少地;微不足道地;】 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. For MyISAM tables, the key cache size is controlled by the key_buffer_size system variable.
 

来源:https://www.cnblogs.com/xuliuzai/p/18212769
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！