MySQL一直以來(lái)提供show profile命令來(lái)獲取某一條SQL執(zhí)行過(guò)程中的資源使用與耗時(shí)情況瘟裸，這個(gè)命令對(duì)于分析具體SQL的性能瓶頸有非常大的幫助客叉，但是這個(gè)功能在MySQL新的版本里將會(huì)被廢棄，取而代之的是使用Performance Schema來(lái)提供同樣的功能话告。本文將介紹如何使用Performance Schema來(lái)實(shí)現(xiàn)show profile SQL性能分析的功能兼搏。
原文地址：
https://mytecdb.com/blogDetail.php?id=79
1. 測(cè)試環(huán)境配置
- 測(cè)試版本：MySQL 5.7.19
- 配置參數(shù)：performance_schema=ON，該參數(shù)配置在my.cnf文件中超棺，生效需要重啟MySQL向族。
2. 配置performance_schema
2.1 配置表setup_actors
默認(rèn)情況下，performance_schema功能打開(kāi)后棠绘，將會(huì)收集所有用戶的SQL執(zhí)行歷史事件件相，因?yàn)槭占男畔⑻啵瑢?duì)數(shù)據(jù)庫(kù)整體性能有一定影響氧苍，而且也不利于排查指定SQL的性能問(wèn)題夜矗，因此需要修改setup_actors表的配置，只收集特定用戶的歷史事件信息让虐。setup_actors表配置如下：
mysql> select * from performance_schema.setup_actors;
+-----------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+-----------+------+------+---------+---------+
| % | % | % | NO | NO |
| localhost | root | % | YES | YES |
+-----------+------+------+---------+---------+
2 rows in set (0.00 sec)
只收集本地root用戶的SQL執(zhí)行歷史事件紊撕。
2.2 配置表setup_instruments
啟用statement和stage監(jiān)視器。
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
2.3 配置表setup_consumers
啟用events_statements_*赡突，events_stages_* 開(kāi)頭的事件類型消費(fèi)对扶。
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
3. 收集具體SQL的性能分析
3.1 執(zhí)行業(yè)務(wù)SQL
在上述配置完成之后，執(zhí)行一個(gè)需要分析的業(yè)務(wù)SQL惭缰，比如：
select * from blog;
3.2 獲取業(yè)務(wù)SQL的事件ID
通過(guò)以下SQL先查詢事件ID浪南。
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%blog%';
+----------+----------+-------------------------+
| EVENT_ID | Duration | SQL_TEXT |
+----------+----------+-------------------------+
| 243 | 0.002698 | select * from blog.blog |
+----------+----------+-------------------------+
1 row in set (0.00 sec)
3.3 根據(jù)事件ID，獲取各階段執(zhí)行耗時(shí)
根據(jù)上一步獲取的事件ID(EVENT_ID)漱受，查詢?cè)揝QL各個(gè)階段的耗時(shí)情況络凿。如下：
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=243;
+--------------------------------+----------+
| Stage | Duration |
+--------------------------------+----------+
| stage/sql/starting | 0.000117 |
| stage/sql/checking permissions | 0.000010 |
| stage/sql/Opening tables | 0.000031 |
| stage/sql/init | 0.000055 |
| stage/sql/System lock | 0.000024 |
| stage/sql/optimizing | 0.000003 |
| stage/sql/statistics | 0.000020 |
| stage/sql/preparing | 0.000015 |
| stage/sql/executing | 0.000001 |
| stage/sql/Sending data | 0.002321 |
| stage/sql/end | 0.000004 |
| stage/sql/query end | 0.000019 |
| stage/sql/closing tables | 0.000018 |
| stage/sql/freeing items | 0.000048 |
| stage/sql/cleaning up | 0.000001 |
+--------------------------------+----------+
15 rows in set (0.00 sec)
上述結(jié)果與show profile的輸出結(jié)果類似，能夠看到每個(gè)階段的耗時(shí)情況昂羡。相對(duì)于show profile來(lái)說(shuō)絮记，似乎更加繁瑣，不過(guò)performance schema是MySQL未來(lái)性能分析的趨勢(shì)虐先，提供了非常豐富的性能診斷工具怨愤，熟悉performance schema的使用將有助于更好的優(yōu)化MySQL。
