Use of parsing Mysql Profiling

Profiling is a very useful mysql performance analysis tool. Today, we will test the profiling function. Thanks to the blog post "love Rose:
The SQL Performance Analyzer of mysql is mainly used to display the usage of various resources during SQL Execution. Analyzer can better demonstrate the performance problems of poor SQL statements.
The following is an example of how to use MySQL SQL Profiler:
First, enable MySQL SQL Profiler
Copy codeThe Code is as follows:
Mysql> SELECT @ profiling;
+ ------------- +
| @ Profiling |
+ ------------- +
| 0 |
+ ------------- +
1 row in set (0.00 sec)
Mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)
Mysql> SELECT @ profiling;
+ ------------- +
| @ Profiling |
+ ------------- +
| 1 |
+ ------------- +

1 row in set (0.00 sec) by default, the value of profiling is 0, indicating that MySQL SQL Profiler is OFF. After enabling SQL Performance Analyzer, the value of profiling is 1.
Through the SQL Performance Analyzer, we can compare the two execution processes before and after the following statements, which is very helpful for us to understand the detailed execution process of SQL.
Copy codeThe Code is as follows:
Mysql> create table t_engines select * from t_engines1;
Query OK, 57344 rows affected (0.10 sec)
Records: 57344 Duplicates: 0 Warnings: 0
Mysql> select count (*) from t_engines;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 57344 |
+ ---------- +
1 row in set (0.00 sec)
Mysql> select count (*) from t_engines;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 57344 |
+ ---------- +
1 row in set (0.00 sec)
Mysql> show profiles;
+ ---------- + ------------ + ------------------------------------------------- +
| Query_ID | Duration | Query |
+ ---------- + ------------ + ------------------------------------------------- +
| 26| 0.10213775 | create table t_engines select * from t_engines1 |
| 27| 0.00032775 | select count (*) from t_engines |
| 28 | 0.00003850 | select count (*) from t_engines |
+ ---------- + ------------ + ------------------------------------------------- +
15 rows in set (0.01 sec)
Mysql> show profile for query 27;
+ -------------------------------- + ------------ +
| Status | Duration |
+ -------------------------------- + ------------ +
| (Initialization) | 0.00000425 |
| Checking query cache for query | 0.00004050 |
| Check permissions | 0.00001050 |
| Opening tables | 0.00018250 |
| System lock | 0.00000450 |
| Table lock | 0.00001775 |
| Init | 1, 0.00001075 |
| Optimizing | 0.00000550 |
| Executing | 0.00002775 |
| End| 0.00000450 |
| Query end | 0.00000325 |
| Storing result in query cache | 0.00000400 |
| Freeing items | 0.00000400 |
| Closing tables | 0.00000500 |
| Logging slow query | 0.00000300 |
+ -------------------------------- + ------------ +
15 rows in set (0.00 sec)
Mysql> show profile for query 28;
+ ------------------------------------- + ------------ +
| Status | Duration |
+ ------------------------------------- + ------------ +
| (Initialization) | 0.00000350 |
| Checking query cache for query | 0.00000750 |
| Checking privileges on cached query | 0.00000500 |
| Check permissions | 0.00000525 |
| Sending cached result to client | 0.00001275 |
| Logging slow query | 0.00000450 |
+ ------------------------------------- + ------------ +
6 rows in set (0.00 sec) mysql> SELECT sum (FORMAT (DURATION, 6) as duration from INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 27 order by seq;
+ ---------- +
| DURATION |
+ ---------- +
| 1, 0.000326 |
+ ---------- +
1 row in set (0.00 sec) mysql> SELECT sum (FORMAT (DURATION, 6) as duration from INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 28 order by seq;
+ ---------- +
| DURATION |
+ ---------- +
| 1, 0.000039 |
+ ---------- +
1 row in set (0.00 sec)

From the above example, we can clearly see the difference between two count statements. show profile for query 27 shows the execution process of the first count statistics, including Opening tables and Table lock. Show profile for query 28 shows the execution process of the second count statistics. The second count directly returns the count statistical result from the QUERY cache. It is found by comparing the total execution time of the two statistics, the cache read speed is 10 times faster than the physical read speed. Using SQL Performance Analyzer can help us diagnose SQL statements that are difficult to identify performance problems and find out the root cause of the problem.