MySQL :: MySQL 8.4 参考手册 :: 14.21 性能模式函数

MySQL 8.4 参考手册 · 中文镜像

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

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

MySQL 8.4 参考手册 / 14 函数和运算符 / 14.21 性能模式函数

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

14.21 性能模式函数14.21 Performance Schema Functions

MySQL 包含内置的 SQL 函数，可用于格式化或检索 Performance Schema 数据，并且可以作为对应 sys 模式存储函数的等效函数使用。内置函数可以在任何模式中调用，不需要限定符，不像 sys 函数，需要 sys. 模式限定符或者 sys 必须是当前模式。MySQL includes built-in SQL functions that format or retrieve Performance Schema data, and that may be used as equivalents for the corresponding sys schema stored functions. The built-in functions can be invoked in any schema and require no qualifier, unlike the sys functions, which require either a sys. schema qualifier or that sys be the current schema.

表 14.31 Performance Schema 函数Table 14.31 Performance Schema Functions

名称Name	说明Description
`FORMAT_BYTES()`	将字节数转换为带单位的数值Convert byte count to value with units
`FORMAT_PICO_TIME()`	将皮秒时间转换为带单位的数值Convert time in picoseconds to value with units
`PS_CURRENT_THREAD_ID()`	当前线程的 Performance Schema 线程 IDPerformance Schema thread ID for current thread
`PS_THREAD_ID()`	给定线程的 Performance Schema 线程 IDPerformance Schema thread ID for given thread

内置函数取代了相应的sys函数，这些函数已被弃用；预计它们将在未来版本的 MySQL 中被移除。使用sys函数的应用程序应调整为改用内置函数，同时注意sys函数与内置函数之间的一些细微差别。有关这些差异的详细信息，请参阅本节中的函数描述。The built-in functions supersede the corresponding sys functions, which are deprecated; expect them to be removed in a future version of MySQL. Applications that use the sys functions should be adjusted to use the built-in functions instead, keeping in mind some minor differences between the sys functions and the built-in functions. For details about these differences, see the function descriptions in this section.

FORMAT_BYTES(count)

给定一个数值字节数，将其转换为人类可读的格式，并返回一个由数值和单位标识组成的字符串。该字符串包含四舍五入到小数点后两位且至少有三个有效数字的字节数。小于 1024 字节的数字将以整数形式表示且不进行四舍五入。如果count为NULL，则返回NULL。Given a numeric byte count, converts it to human-readable format and returns a string consisting of a value and a units indicator. The string contains the number of bytes rounded to 2 decimal places and a minimum of 3 significant digits. Numbers less than 1024 bytes are represented as whole numbers and are not rounded. Returns NULL if count is NULL.

单位指示器取决于字节计数参数的大小，如下表所示。The units indicator depends on the size of the byte-count argument as shown in the following table.

参数值Argument Value	结果单位Result Units	结果单位指示器Result Units Indicator
最多 1023Up to 1023	字节bytes	字节bytes
最多 1024² − 1Up to 1024² − 1	千字节（KiB，二进制千字节）kibibytes	KiB
最多 1024³ − 1Up to 1024³ − 1	兆字节（MiB）mebibytes	MiB
最多 1024⁴ − 1Up to 1024⁴ − 1	吉比字节gibibytes	GiB
最多 1024⁵ − 1Up to 1024⁵ − 1	tebibytes	TiB
最多 1024⁶ − 1Up to 1024⁶ − 1	佩比字节pebibytes	PiB
1024⁶及以上1024⁶ and up	艾字节exbibytes	EiB

mysql> SELECT FORMAT_BYTES(512), FORMAT_BYTES(18446644073709551615);
+-------------------+------------------------------------+
| FORMAT_BYTES(512) | FORMAT_BYTES(18446644073709551615) |
+-------------------+------------------------------------+
|  512 bytes        | 16.00 EiB                          |
+-------------------+------------------------------------+

FORMAT_BYTES() 可以代替 sys 模式下的 format_bytes() 函数，但需要注意以下区别：FORMAT_BYTES() may be used instead of the sys schema format_bytes() function, keeping in mind this difference:

FORMAT_BYTES() 使用 EiB 单位指标。sys.format_bytes() 则不使用该指标。FORMAT_BYTES() uses the EiB units indicator. sys.format_bytes() does not.

FORMAT_PICO_TIME(time_val)

给定以皮秒为单位的数字性能架构延迟或等待时间，将其转换为人类可读的格式，并返回一个由数值和单位指示符组成的字符串。字符串包含四舍五入到两位小数的十进制时间，并且至少有三个有效数字。小于 1 纳秒的时间表示为整数，不进行四舍五入。Given a numeric Performance Schema latency or wait time in picoseconds, converts it to human-readable format and returns a string consisting of a value and a units indicator. The string contains the decimal time rounded to 2 decimal places and a minimum of 3 significant digits. Times under 1 nanosecond are represented as whole numbers and are not rounded.

如果 time_val 是 NULL，此函数返回 NULL。If time_val is NULL, this function returns NULL.

单位指示器取决于时间值参数的大小，如下表所示。The units indicator depends on the size of the time-value argument as shown in the following table.

参数值Argument Value	结果单位Result Units	结果单位指示器Result Units Indicator
最高可达 10³ − 1Up to 10³ − 1	皮秒picoseconds	皮秒ps
最多 10⁶ − 1Up to 10⁶ − 1	纳秒nanoseconds	ns
最高可达 10⁹ − 1Up to 10⁹ − 1	微秒microseconds	微秒 (us)us
最高可达 10¹² − 1Up to 10¹² − 1	毫秒milliseconds	毫秒ms
最多 60×10¹² − 1Up to 60×10¹² − 1	秒seconds	ss
最多 3.6×10¹⁵ − 1Up to 3.6×10¹⁵ − 1	分钟minutes	分钟min
最多可达 8.64×10¹⁶ − 1Up to 8.64×10¹⁶ − 1	小时hours	hh
8.64×10¹⁶及以上8.64×10¹⁶ and up	天days	dd

mysql> SELECT FORMAT_PICO_TIME(3501), FORMAT_PICO_TIME(188732396662000);
+------------------------+-----------------------------------+
| FORMAT_PICO_TIME(3501) | FORMAT_PICO_TIME(188732396662000) |
+------------------------+-----------------------------------+
| 3.50 ns                | 3.15 min                          |
+------------------------+-----------------------------------+

FORMAT_PICO_TIME() 可以替代 sys schema 的 format_time() 函数，但需要注意以下差异：FORMAT_PICO_TIME() may be used instead of the sys schema format_time() function, keeping in mind these differences:

表示分钟时，sys.format_time() 使用 m 作为单位指示，而 FORMAT_PICO_TIME() 使用 min。To indicate minutes, sys.format_time() uses the m units indicator, whereas FORMAT_PICO_TIME() uses min.
sys.format_time() 使用 w（周）单位指示符。FORMAT_PICO_TIME() 不使用。sys.format_time() uses the w (weeks) units indicator. FORMAT_PICO_TIME() does not.

PS_CURRENT_THREAD_ID()
返回一个 BIGINT UNSIGNED 值，表示分配给当前连接的 Performance Schema 线程 ID。Returns a BIGINT UNSIGNED value representing the Performance Schema thread ID assigned to the current connection.
线程 ID 返回值的类型与 Performance Schema 表中的 THREAD_ID 列的类型相同。The thread ID return value is a value of the type given in the THREAD_ID column of Performance Schema tables.
Performance Schema 配置对 PS_CURRENT_THREAD_ID() 的影响与 PS_THREAD_ID() 相同。详情请参见该函数的描述。Performance Schema configuration affects PS_CURRENT_THREAD_ID() the same way as for PS_THREAD_ID(). For details, see the description of that function.
```
mysql> SELECT PS_CURRENT_THREAD_ID();
+------------------------+
| PS_CURRENT_THREAD_ID() |
+------------------------+
|                     52 |
+------------------------+
mysql> SELECT PS_THREAD_ID(CONNECTION_ID());
+-------------------------------+
| PS_THREAD_ID(CONNECTION_ID()) |
+-------------------------------+
|                            52 |
+-------------------------------+
```
PS_CURRENT_THREAD_ID() 可用作调用 sys 模式下 ps_thread_id() 函数的快捷方式，其参数为 NULL 或 CONNECTION_ID()。PS_CURRENT_THREAD_ID() may be used as a shortcut for invoking the sys schema ps_thread_id() function with an argument of NULL or CONNECTION_ID().
PS_THREAD_ID(connection_id)
给定一个连接 ID，返回一个 BIGINT UNSIGNED 值，表示分配给该连接 ID 的 Performance Schema 线程 ID，如果该连接 ID 没有线程 ID，则返回 NULL。如果线程未被检测，或者 connection_id 为 NULL，就可能出现后者情况。Given a connection ID, returns a BIGINT UNSIGNED value representing the Performance Schema thread ID assigned to the connection ID, or NULL if no thread ID exists for the connection ID. The latter can occur for threads that are not instrumented, or if connection_id is NULL.
连接 ID 参数是 Performance Schema threads 表中 PROCESSLIST_ID 列或 Id 输出中的 SHOW PROCESSLIST 列的类型值。The connection ID argument is a value of the type given in the PROCESSLIST_ID column of the Performance Schema threads table or the Id column of SHOW PROCESSLIST output.
线程 ID 返回值的类型与 Performance Schema 表中的 THREAD_ID 列的类型相同。The thread ID return value is a value of the type given in the THREAD_ID column of Performance Schema tables.
Performance Schema 配置会影响 PS_THREAD_ID() 的操作，如下所示。（这些说明也适用于 PS_CURRENT_THREAD_ID()。）Performance Schema configuration affects PS_THREAD_ID() operation as follows. (These remarks also apply to PS_CURRENT_THREAD_ID().)
- 禁用 thread_instrumentation 消费器会禁用在线程级别收集和汇总统计信息，但对 PS_THREAD_ID() 没有影响。Disabling the thread_instrumentation consumer disables statistics from being collected and aggregated at the thread level, but has no effect on PS_THREAD_ID().
- 如果 performance_schema_max_thread_instances 不为 0，Performance Schema 会为线程统计分配内存，并为每个可用实例内存的线程分配一个内部 ID。如果有线程的实例内存不可用，PS_THREAD_ID() 会返回 NULL；此时，Performance_schema_thread_instances_lost 不为零。If performance_schema_max_thread_instances is not 0, the Performance Schema allocates memory for thread statistics and assigns an internal ID to each thread for which instance memory is available. If there are threads for which instance memory is not available, PS_THREAD_ID() returns NULL; in this case, Performance_schema_thread_instances_lost is nonzero.
- 如果 performance_schema_max_thread_instances 为 0，则 Performance Schema 不分配线程内存，并且 PS_THREAD_ID() 返回 NULL。If performance_schema_max_thread_instances is 0, the Performance Schema allocates no thread memory and PS_THREAD_ID() returns NULL.
- 如果 Performance Schema 本身被禁用，PS_THREAD_ID() 会产生错误。If the Performance Schema itself is disabled, PS_THREAD_ID() produces an error.
```
mysql> SELECT PS_THREAD_ID(6);
+-----------------+
| PS_THREAD_ID(6) |
+-----------------+
|              45 |
+-----------------+
```
PS_THREAD_ID() 可以替代 sys 模式中的 ps_thread_id() 函数，但需要注意以下区别：PS_THREAD_ID() may be used instead of the sys schema ps_thread_id() function, keeping in mind this difference:
- 当参数为 NULL 时，sys.ps_thread_id() 返回当前连接的线程 ID，而 PS_THREAD_ID() 返回 NULL。要获取当前连接的线程 ID，请改用 PS_CURRENT_THREAD_ID()。With an argument of NULL, sys.ps_thread_id() returns the thread ID for the current connection, whereas PS_THREAD_ID() returns NULL. To obtain the current connection thread ID, use PS_CURRENT_THREAD_ID() instead.

首页上一级下一节