高性能 MySQL 第四版（GPT 重译）（一）(2)https://developer.aliyun.com/article/1484257
调整 Performance Schema 的内存大小
Performance Schema 将数据存储在使用PERFORMANCE_SCHEMA引擎的表中。此引擎将数据存储在内存中。默认情况下，performance_schema表中的一些表是自动调整大小的；其他表具有固定数量的行。您可以通过更改启动变量来调整这些选项。变量的名称遵循模式performance_schema_object_[size|instances|classes|length|handles]，其中object可以是消费者、设置表或特定事件的仪器化实例。例如，配置变量performance_schema_��events_stages_history_size定义了performance_schema_events_stages_history表将存储的每个线程的阶段数。变量performance_schema_max_memory_classes定义了可以使用的内存仪器的最大数量。
默认值
MySQL 不同部分的默认值随版本而变化；因此，在依赖于此处描述的值之前，最好先查阅用户参考手册。但是，对于 Performance Schema，它们会影响服务器的整体性能，因此我想涵盖重要的部分。
自版本 5.7 以来，默认情况下启用了 Performance Schema，大多数仪器被禁用。只有全局、线程、语句和事务仪器被启用。自版本 8.0 以来，默认还额外启用了元数据锁和内存仪器。
mysql、information_schema和performance_schema数据库未被仪器化。所有其他对象、线程和执行者都被仪器化。
大多数实例、句柄和设置表都是自动调整大小的。对于_history表，每个线程存储最后的 10 个事件。对于_history_long表，每个线程存储最新的 10,000 个事件。最大存储的 SQL 文本长度为 1,024 字节。最大的 SQL 摘要长度也是 1,024 字节。超出长度的部分会被右侧修剪。
使用性能模式
现在我已经介绍了性能模式的配置方式，我想提供一些示例来帮助您解决常见的故障排除情况。
检查 SQL 语句
正如我在“仪器元素”中提到的，性能模式支持一套丰富的仪器，用于检查 SQL 语句的性能。您将找到用于标准准备语句和存储例程的工具。通过performance_schema，您可以轻松找到哪个查询导致性能问��以及原因。
要启用语句仪表化，您需要启用类型为statement的仪器，如表 3-3 中所述。
表 3-3。Statement工具及其描述
| 仪器类 | 描述 |
statement/sql |
SQL 语句，如SELECT或CREATE TABLE |
statement/sp |
存储过程控制 |
statement/scheduler |
事件调度器 |
statement/com |
命令，如quit、KILL、DROP DATABASE或Binlog Dump。有些命令对用户不可用，由mysqld进程自身调用。 |
statement/abstract |
四个命令的类：clone、Query、new_packet和relay_log |
常规 SQL 语句
性能模式将语句指标存储在events_statements_current、events_statements_history和events_statements_history_long表中。这三个表具有相同的结构。
直接使用 performance_schema
这是一个event_statement_history条目的示例：
THREAD_ID: 3200 EVENT_ID: 22 END_EVENT_ID: 23 EVENT_NAME: statement/sql/select SOURCE: init_net_server_extension.cc:94 TIMER_START: 878753511280779000 TIMER_END: 878753544491277000 TIMER_WAIT: 33210498000 LOCK_TIME: 657000000 SQL_TEXT: SELECT film.film_id, film.description FROM sakila.film INNER JOIN ( SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5 ) AS lim USING(film_id) DIGEST: 2fdac27c4a9434806da3b216b9fa71aca738f70f1e8888a581c4fb00a349224f DIGEST_TEXT: SELECT `film` . `film_id` , `film` . `description` FROM `sakila` . `film` INNER JOIN ( SELECT `film_id` FROM `sakila` . `film` ORDER BY `title` LIMIT?, ... ) AS `lim` USING ( `film_id` ) CURRENT_SCHEMA: sakila OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: NULL MYSQL_ERRNO: 0 RETURNED_SQLSTATE: NULL MESSAGE_TEXT: NULL ERRORS: 0 WARNINGS: 0 ROWS_AFFECTED: 0 ROWS_SENT: 5 ROWS_EXAMINED: 10 CREATED_TMP_DISK_TABLES: 0 CREATED_TMP_TABLES: 1 SELECT_FULL_JOIN: 0 SELECT_FULL_RANGE_JOIN: 0 SELECT_RANGE: 0 SELECT_RANGE_CHECK: 0 SELECT_SCAN: 2 SORT_MERGE_PASSES: 0 SORT_RANGE: 0 SORT_ROWS: 0 SORT_SCAN: 0 NO_INDEX_USED: 1 NO_GOOD_INDEX_USED: 0 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL NESTING_EVENT_LEVEL: 0 STATEMENT_ID: 25
这些列在官方文档中有解释，所以我不会逐一介绍它们。表 3-4 列出了可用作识别需要优化查询的指标的列。并非所有这些列都是相等的。例如，大多数情况下CREATED_TMP_DISK_TABLES是一个糟糕优化查询的迹象，而四个与排序相关的列可能只是表明查询结果需要排序。列的重要性表示指标的严重程度。
表 3-4。event_statement_history中可用作优化指标的列
| 列 | 描述 | 重要性 |
CREATED_TMP_DISK_TABLES |
查询创建了这么多基于磁盘的临时表。您有两种解决此问题的选择：优化查询或增加内存临时表的最大大小。 | 高 |
CREATED_TMP_TABLES |
查询创建了这么多基于内存的临时表。使用内存临时表本身并不是坏事。但是，如果底层表增长，它们可能会转换为基于磁盘的表。最好提前为这种情况做好准备。 | 中 |
SELECT_FULL_JOIN |
如果JOIN执行了全表扫描，因为没有好的索引来解决查询。除非表很小，否则您需要重新考虑您的索引。 |
高 |
SELECT_FULL_RANGE_JOIN |
如果JOIN使用了引用表的范围搜索。 |
中 |
SELECT_RANGE |
如果JOIN使用范围搜索来解决第一个表中的行。这通常不是一个大问题。 |
低 |
SELECT_RANGE_CHECK |
如果JOIN没有索引，每行后都会检查键。这是一个非常糟糕的症状，如果这个值大于零，您需要重新考虑表索引。 |
高 |
SELECT_SCAN |
如果 JOIN 对第一个表进行了全扫描。如果表很大，这是一个问题。 |
中等 |
SORT_MERGE_PASSES |
排序执行的合并次数。如果值大于零且查询性能较慢，可能需要增加 sort_buffer_size。 |
低 |
SORT_RANGE |
如果排序是通过范围完成的。 | 低 |
SORT_ROWS |
排序行数。与返回行数的值进行比较。如果排序行数较高，可能需要优化查询。 | 中等（见描述） |
SORT_SCAN |
如果排序是通过扫描表来完成的。这是一个非常糟糕的迹象，除非您��意选择表中的所有行而不使用索引。 | 高 |
NO_INDEX_USED |
未使用索引解析查询。 | 高，除非表很小 |
NO_GOOD_INDEX_USED |
用于解析查询的索引不是最佳的。如果此值大于零，则需要重新考虑索引。 | 高 |
要找出哪些语句需要优化，您可以选择任何列并将其与零进行比较。例如，要查找所有不使用良好索引的查询，请运行以下操作：
SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES, NO_INDEX_USED, NO_GOOD_INDEX_USED FROM performance_schema.events_statements_history_long WHERE NO_INDEX_USED > 0 OR NO_GOOD_INDEX_USED > 0;
要查找所有创建临时表的查询，请运行：
SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES, CREATED_TMP_DISK_TABLES FROM performance_schema.events_statements_history_long WHERE CREATED_TMP_TABLES > 0 OR CREATED_TMP_DISK_TABLES > 0;
您可以使用这些列中的值来单独显示潜在问题。例如，要查找所有返回错误的查询，使用条件 WHERE ERRORS > 0；要查找执行时间超过五秒的所有查询，使用条件 WHERE TIMER_WAIT > 5000000000；等等。
或者，您可以创建一个查询，通过长条件查找所有存在问题的语句，如下所示：
WHERE ROWS_EXAMINED > ROWS_SENT OR ROWS_EXAMINED > ROWS_AFFECTED OR ERRORS > 0 OR CREATED_TMP_DISK_TABLES > 0 OR CREATED_TMP_TABLES > 0 OR SELECT_FULL_JOIN > 0 OR SELECT_FULL_RANGE_JOIN > 0 OR SELECT_RANGE > 0 OR SELECT_RANGE_CHECK > 0 OR SELECT_SCAN > 0 OR SORT_MERGE_PASSES > 0 OR SORT_RANGE > 0 OR SORT_ROWS > 0 OR SORT_SCAN > 0 OR NO_INDEX_USED > 0 OR NO_GOOD_INDEX_USED > 0
使用 sys schema
sys schema 提供了可用于查找存在问题的语句的视图。例如，statements_with_errors_or_warnings 列出了所有带有错误和警告的语句，而 statements_with_full_table_scans 列出了所有需要执行全表扫描的语句。sys schema 使用摘要文本而不是查询文本，因此您将获得摘要查询文本，而不是在访问原始 performance_schema 表时获得的 SQL 或摘要文本：
mysql> SELECT query, total_latency, no_index_used_count, rows_sent, -> rows_examined -> FROM sys.statements_with_full_table_scans -> WHERE db='employees' AND -> query NOT LIKE '%performance_schema%'\G ********************** 1\. row ********************** query: SELECT COUNT ( 'emp_no' ) FROM ... 'emp_no' ) WHERE 'title' = ? total_latency: 805.37 ms no_index_used_count: 1 rows_sent: 1 rows_examined: 397774 …
其他可用于找到需要优化的语句的视图在 Table 3-5 中有描述。
Table 3-5. 可用于找到需要优化的语句的视图
| 视图 | 描述 |
statement_analysis |
一个带有聚合统计信息的标准化语句视图，按照标准化语句的总执行时间排序。类似于 events_statements_summary_by_digest 表，但更简略。 |
statements_with_errors_or_warnings |
所有引发错误或警告的标准化语句。 |
statements_with_full_table_scans. |
所有执行全表扫描的标准化语句。 |
statements_with_runtimes_in_95th_percentile |
所有平均执行时间位于前 95% 的标准化语句。 |
statements_with_sorting |
所有执行排序的标准化语句。该视图包括所有类型的排序。 |
statements_with_temp_tables |
所有使用临时表的标准化语句。 |
预处理语句
prepared_statements_instances 表包含服务器中存在的所有预处理语句。它具有与 events_statements_[current|history|history_long] 表相同的统计信息，此外还包含拥有预处理语句的线程信息以及语句执行次数。与 events_statements_[current|history|history_long] 表不同，统计数据是累加的，表中包含所有语句执行的总次数。
警告
COUNT_EXECUTE列包含语句执行的次数，因此您可以通过将总值除以此列中的数字来获得每个语句的平均统计信息。但请注意，任何平均统计信息可能是不准确的。例如，如果您执行了 10 次语句，而列SUM_SELECT_FULL_JOIN中的值为 10，则平均值将是每个语句一个完全连接。如果您然后添加一个索引并再次执行该语句，SUM_SELECT_FULL_JOIN将保持为 10，因此平均值将为 10/11 = 0.9。这并不表明问题现在已解决。
要启用准备语句的仪器，您需要启用表 3-6 中描述的仪器。
表 3-6. 用于准备语句仪器的启用
| 仪器类别 | 描述 |
statement/sql/prepare_sql |
在文本协议中的PREPARE语句（通过 MySQL CLI 运行时） |
statement/sql/execute_sql |
在文本协议中的EXECUTE语句（通过 MySQL CLI 运行时） |
statement/com/Prepare |
在二进制协议中的PREPARE语句（如果通过 MySQL C API 访问） |
statement/com/Execute |
在二进制协议中的EXECUTE语句（如果通过 MySQL C API 访问） |
一旦启用，您可以准备一个语句并执行几次：
mysql> PREPARE stmt FROM -> 'SELECT COUNT(*) FROM employees WHERE hire_date > ?'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql1> SET @hd='1995-01-01'; Query OK, 0 rows affected (0.00 sec) mysql1> EXECUTE stmt USING @hd; +----------+ | count(*) | +----------+ | 34004 | +----------+ 1 row in set (1.44 sec) -- Execute a few more times with different values
然后您可以检查诊断信息：
mysql2> SELECT statement_name, sql_text, owner_thread_id, -> count_reprepare, count_execute, sum_timer_execute -> FROM prepared_statements_instances\G *************************** 1\. row *************************** statement_name: stmt sql_text: select count(*) from employees where hire_date > ? owner_thread_id: 22 count_reprepare: 0 count_execute: 3 sum_timer_execute: 4156561368000 1 row in set (0.00 sec)
请注意，只有在服务器中存在时，您才会在prepared_statements_instances表中看到语句。一旦它们被删除，您将无法再访问它们的统计信息：
mysql1> DROP PREPARE stmt; Query OK, 0 rows affected (0.00 sec) mysql2> SELECT * FROM prepared_statements_instances\G Empty set (0.00 sec)
存储过程
使用performance_schema，您可以检索有关存储过程执行情况的信息：例如，IF … ELSE流程控制语句的哪个分支被选择，或者是否调用了错误处理程序。
要启用存储过程仪器，您需要启用遵循模式'statement/sp/%'的仪器。statement/sp/stmt仪器负责例程内调用的语句，而其他仪器负责跟踪事件，例如进入或离开过程、循环或任何其他控制指令。
为了演示存储过程仪器的工作原理，使用存储过程：
CREATE DEFINER='root'@'localhost' PROCEDURE 'sp_test'(val int) BEGIN DECLARE CONTINUE HANDLER FOR 1364, 1048, 1366 BEGIN INSERT IGNORE INTO t1 VALUES('Some string'); GET STACKED DIAGNOSTICS CONDITION 1 @stacked_state = RETURNED_SQLSTATE; GET STACKED DIAGNOSTICS CONDITION 1 @stacked_msg = MESSAGE_TEXT; END; INSERT INTO t1 VALUES(val); END
然后用不同的值调用它：
mysql> CALL sp_test(1); Query OK, 1 row affected (0.07 sec) mysql> SELECT THREAD_ID, EVENT_NAME, SQL_TEXT -> FROM EVENTS_STATEMENTS_HISTORY -> WHERE EVENT_NAME LIKE 'statement/sp%'; +-----------+-------------------------+----------------------------+ | THREAD_ID | EVENT_NAME | SQL_TEXT | +-----------+-------------------------+----------------------------+ | 24 | statement/sp/hpush_jump | NULL | | 24 | statement/sp/stmt | INSERT INTO t1 VALUES(val) | | 24 | statement/sp/hpop | NULL | +-----------+-------------------------+----------------------------+ 3 rows in set (0.00 sec)
在这种情况下，错误处理程序没有被调用，而存储过程将参数值(1)插入到表中：
mysql> CALL sp_test(NULL); Query OK, 1 row affected (0.07 sec) mysql> SELECT THREAD_ID, EVENT_NAME, SQL_TEXT -> FROM EVENTS_STATEMENTS_HISTORY -> WHERE EVENT_NAME LIKE 'statement/sp%'; +-----------+-------------------------+------------------------------+ | THREAD_ID | EVENT_NAME | SQL_TEXT | +-----------+-------------------------+------------------------------+ | 24 | statement/sp/hpush_jump | NULL | | 24 | statement/sp/stmt | INSERT INTO t1 VALUES(val) | | 24 | statement/sp/stmt | INSERT IGNORE INTO t1 VALUES(‘Some str... | | 24 | statement/sp/stmt | GET STACKED DIAGNOSTICS CONDITION 1 @s... | | 24 | statement/sp/stmt | GET STACKED DIAGNOSTICS CONDITION 1 @s... | | 24 | statement/sp/hreturn | NULL | | 24 | statement/sp/hpop | NULL | +-----------+-------------------------+------------------------------+ 7 rows in set (0.00 sec)
然而，在第二次调用中，events_statements_history表的内容不同：它包含了来自错误处理程序的调用以及替换错误语句的 SQL 语句。
虽然存储过程本身的返回值没有改变，但我们清楚地看到它已经以不同的方式执行。了解例程执行流程中的这些差异可以帮助理解为什么同一个例程如果被调用一次几乎立即完成，而另一次调用时可能需要更长的时间。
语句分析
events_stages_[current|history|history_long]表包含了诸如 MySQL 在创建临时表、更新或等待锁时花费的时间等分析信息。要启用分析，您需要启用相应的消费者以及遵循模式'stage/%'的仪器。一旦启用，您可以找到答案，比如“查询执行的哪个阶段花费了非常长的时间？”以下示例搜索了花费超过一秒的阶段：
mysql> SELECT eshl.event_name, sql_text, -> eshl.timer_wait/10000000000 w_s -> FROM performance_schema.events_stages_history_long eshl -> JOIN performance_schema.events_statements_history_long esthl -> ON (eshl.nesting_event_id = esthl.event_id) -> WHERE eshl.timer_wait > 1*10000000000\G *************************** 1\. row *************************** event_name: stage/sql/Sending data sql_text: SELECT COUNT(emp_no) FROM employees JOIN salaries USING(emp_no) WHERE hire_date=from_date w_s: 81.7 1 row in set (0.00 sec)
使用events_stages_[current|history|history_long]表的另一种技术是关注那些在已知会导致性能问题的阶段中花费超过一定阈值的语句。表 3-7 列出了��些阶段。
表 3-7. 表现问题的指标阶段
| 阶段类别 | 描述 |
stage/sql/%tmp% |
与临时表相关的所有内容。 |
stage/sql/%lock% |
与锁相关的所有内容。 |
stage/%/Waiting for% |
一切等待资源的内容。 |
stage/sql/Sending data |
这个阶段应该与语句统计中的 ROWS_SENT 数量进行比较。如果 ROWS_SENT 很小，一个在这个阶段花费大量时间的语句可能意味着它必须创建一个临时文件或表来解决中间结果。这通常会在向客户端发送数据之前对行进行过滤。这通常是一个查询优化不佳的症状。 |
stage/sql/freeing items``stage/sql/cleaning up``stage/sql/closing tables``stage/sql/end |
这些是清理资源的阶段。不幸的是，它们的细节不够详细，每个阶段包含的任务不止一个。如果你发现你的查询在这些阶段花费了很长时间，很可能是由于高并发导致资源争用。你需要检查 CPU、I/O 和内存使用情况，以及你的硬件和 MySQL 选项是否能够处理应用程序创建的并发。 |
非常重要的一点是，性能分析仅适用于一般服务器阶段。存储引擎不支持使用 performance_schema 进行性能分析。因此，诸如 stage/sql/update 这样的阶段意味着作业在存储引擎内部，并且可能包括不仅仅是更新本身，还包括等待存储引擎特定锁或其他争用问题。
检查读写性能
在 Performance Schema 中的语句仪表化非常有用，可以帮助理解你的工作负载是读取还是写入受限。你可以从统计语句的类型开始：
mysql> SELECT EVENT_NAME, COUNT(EVENT_NAME) -> FROM events_statements_history_long -> GROUP BY EVENT_NAME; +----------------------+-------------------+ | EVENT_NAME | COUNT(EVENT_NAME) | +----------------------+-------------------+ | statement/sql/insert | 504 | | statement/sql/delete | 502 | | statement/sql/select | 6987 | | statement/sql/update | 1007 | | statement/sql/commit | 500 | | statement/sql/begin | 500 | +----------------------+-------------------+ 6 rows in set (0.03 sec)
在这个示例中，SELECT 查询的数量大于任何其他查询的数量。这表明在这个设置中，大多数查询都是读取查询。
如果想了解语句的延迟，可以按 LOCK_TIME 列进行聚合：
mysql> SELECT EVENT_NAME, COUNT(EVENT_NAME), -> SUM(LOCK_TIME/1000000) AS latency_ms -> FROM events_statements_history -> GROUP BY EVENT_NAME ORDER BY latency_ms DESC; +----------------------------------+-------------------+------------+ | EVENT_NAME | COUNT(EVENT_NAME) | latency_ms | +----------------------------------+-------------------+------------+ | statement/sql/select | 194 | 7362.0000 | | statement/sql/update | 33 | 1276.0000 | | statement/sql/insert | 16 | 599.0000 | | statement/sql/delete | 16 | 470.0000 | | statement/sql/show_status | 2 | 176.0000 | | statement/sql/begin | 4 | 0.0000 | | statement/sql/commit | 2 | 0.0000 | | statement/com/Ping | 2 | 0.0000 | | statement/sql/show_engine_status | 1 | 0.0000 | +----------------------------------+-------------------+------------+ 9 rows in set (0.01 sec)
高性能 MySQL 第四版（GPT 重译）（一）(4)https://developer.aliyun.com/article/1484267
