MySQL :: MySQL 8.4 参考手册 :: 10.10.3 准备语句和存储程序的缓存

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.10 缓冲和高速缓存 / 10.10.3 准备语句和存储程序的缓存

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

10.10.3 预处理语句和存储程序的缓存10.10.3 Caching of Prepared Statements and Stored Programs

对于客户在会话期间可能执行多次的某些语句，服务器会将语句转换为内部结构，并缓存该结构以供执行时使用。缓存使服务器能够更高效地执行，因为它避免了如果在会话期间再次需要该语句时重新转换的开销。以下语句会进行转换和缓存：For certain statements that a client might execute multiple times during a session, the server converts the statement to an internal structure and caches that structure to be used during execution. Caching enables the server to perform more efficiently because it avoids the overhead of reconverting the statement should it be needed again during the session. Conversion and caching occurs for these statements:

预处理语句，包括在 SQL 级别处理的语句（使用 PREPARE 语句）以及使用二进制客户端/服务器协议处理的语句（使用 mysql_stmt_prepare() C API 函数）。max_prepared_stmt_count 系统变量控制服务器缓存的语句总数。（所有会话中预处理语句数量的总和。）Prepared statements, both those processed at the SQL level (using the PREPARE statement) and those processed using the binary client/server protocol (using the mysql_stmt_prepare() C API function). The max_prepared_stmt_count system variable controls the total number of statements the server caches. (The sum of the number of prepared statements across all sessions.)
存储程序（存储过程和函数、触发器和事件）。在这种情况下，服务器会转换并缓存整个程序体。stored_program_cache 系统变量指示服务器每个会话大约缓存的存储程序数量。Stored programs (stored procedures and functions, triggers, and events). In this case, the server converts and caches the entire program body. The stored_program_cache system variable indicates the approximate number of stored programs the server caches per session.

服务器会为每个会话维护已准备语句和存储程序的缓存。为某个会话缓存的语句无法被其他会话访问。当会话结束时，服务器会丢弃为该会话缓存的任何语句。The server maintains caches for prepared statements and stored programs on a per-session basis. Statements cached for one session are not accessible to other sessions. When a session ends, the server discards any statements cached for it.

当服务器使用缓存的内部语句结构时，必须确保该结构不会过时。语句使用的对象的元数据可能发生变化，从而导致当前对象定义与内部语句结构中表示的定义不匹配。元数据的变化发生在 DDL 语句中，例如创建、删除、修改、重命名或截断表，或者分析、优化或修复表。表内容的变化（例如，使用 INSERT 或 UPDATE）不会改变元数据，SELECT 语句也不会改变元数据。When the server uses a cached internal statement structure, it must take care that the structure does not go out of date. Metadata changes can occur for an object used by the statement, causing a mismatch between the current object definition and the definition as represented in the internal statement structure. Metadata changes occur for DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables. Table content changes (for example, with INSERT or UPDATE) do not change metadata, nor do SELECT statements.

这是问题的一个示例。假设客户准备了如下报表：Here is an illustration of the problem. Suppose that a client prepares this statement:

PREPARE s1 FROM 'SELECT * FROM t1';

SELECT * 在内部结构中会扩展为表中的列列表。如果使用 ALTER TABLE 修改了表中的列集合，预处理语句就会过期。如果服务器在客户端下次执行 s1 时没有检测到此更改，预处理语句将返回不正确的结果。The SELECT * expands in the internal structure to the list of columns in the table. If the set of columns in the table is modified with ALTER TABLE, the prepared statement goes out of date. If the server does not detect this change the next time the client executes s1, the prepared statement returns incorrect results.

为了避免由预处理语句所引用的表或视图的元数据更改引起的问题，服务器会检测这些更改，并在下次执行该语句时自动重新准备该语句。也就是说，服务器会重新解析该语句并重建内部结构。当被引用的表或视图从表定义缓存中刷新后，也会进行重新解析，无论是由于隐式操作为缓存中新条目腾出空间，还是由于FLUSH TABLES的显式操作。To avoid problems caused by metadata changes to tables or views referred to by the prepared statement, the server detects these changes and automatically reprepares the statement when it is next executed. That is, the server reparses the statement and rebuilds the internal structure. Reparsing also occurs after referenced tables or views are flushed from the table definition cache, either implicitly to make room for new entries in the cache, or explicitly due to FLUSH TABLES.

同样，如果存储程序使用的对象发生变化，服务器会重新解析程序中受影响的语句。Similarly, if changes occur to objects used by a stored program, the server reparses affected statements within the program.

服务器还会检测表达式中对象的元数据更改。这些更改可能用于存储程序中特定的语句，例如 DECLARE CURSOR，或用于流程控制语句，例如 IF、CASE 和 RETURN。The server also detects metadata changes for objects in expressions. These might be used in statements specific to stored programs, such as DECLARE CURSOR or flow-control statements such as IF, CASE, and RETURN.

为了避免重新解析整个已存储程序，服务器仅在需要时重新解析程序中受影响的语句或表达式。示例：To avoid reparsing entire stored programs, the server reparses affected statements or expressions within a program only as needed. Examples:

假设表或视图的元数据发生了更改。程序中访问该表或视图的 SELECT * 会重新解析，但不访问该表或视图的 SELECT * 不会重新解析。Suppose that metadata for a table or view is changed. Reparsing occurs for a SELECT * within the program that accesses the table or view, but not for a SELECT * that does not access the table or view.
当语句受到影响时，服务器会尽可能仅部分重新解析它。考虑这个 CASE 语句：When a statement is affected, the server reparses it only partially if possible. Consider this CASE statement:
```
CASE case_expr
  WHEN when_expr1 ...
  WHEN when_expr2 ...
  WHEN when_expr3 ...
  ...
END CASE
```
如果元数据更改仅影响 WHEN when_expr3，则该表达式会被重新解析。case_expr 和其他 WHEN 表达式不会被重新解析。If a metadata change affects only WHEN when_expr3, that expression is reparsed. case_expr and the other WHEN expressions are not reparsed.

重新解析使用的是原始转换为内部形式时生效的默认数据库和 SQL 模式。Reparsing uses the default database and SQL mode that were in effect for the original conversion to internal form.

服务器最多尝试重新解析三次。如果所有尝试都失败，则会发生错误。The server attempts reparsing up to three times. An error occurs if all attempts fail.

重新解析是自动进行的，但在发生时，会降低预处理语句和存储程序的性能。Reparsing is automatic, but to the extent that it occurs, diminishes prepared statement and stored program performance.

对于预处理语句，Com_stmt_reprepare 状态变量跟踪重新准备的次数。For prepared statements, the Com_stmt_reprepare status variable tracks the number of repreparations.

首页上一级下一节