This is the 20th day of my participation in the August Text Challenge.More challenges in August

Preface: This article is my 20th article about MySQL, the level is general, ability is limited. The article is relatively shallow, suitable for novice.

Show profile command

In daily development, we often encounter slow query SQL. If you want to analyze the overhead of this SQL, you can use the show profile command to see. Then find the problem, and targeted optimization. The main function of this command is to view the specific time occupied by each part of the SQL.

Open the show profile

The show profile command is normally disabled by default, so we need to enable it.

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Copy the code

When enabled, all statements executed are logged with their time details.

The internal logic is that when the server makes a query, the internal execution is parsed and logged into a temporary table. And give me an ID.

Start by executing a query SQL

mysql> select * from food;
+-------+--------+------+
10003 rows in set (0.02 sec)
Copy the code

Querying Execution Records

This query returned 10003 rows in 0.02 seconds. We execute show profiles; Take a look at all the execution records.

mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                            |
+----------+------------+----------------------------------------------------------------------------------+
|        1 | 0.00012475 | select * from food                                                               |
|        2 | 0.00017675 | SELECT DATABASE()                                                                |
|        3 | 0.00017900 | SELECT DATABASE()                                                                |
|        4 | 0.00087125 | show databases                                                                   |
|        5 | 0.00093550 | show tables                                                                      |
|       6 | 0.01715250 | select * from food                                                               |
+----------+------------+----------------------------------------------------------------------------------+
6 rows in set.1 warning (0.00 sec)
Copy the code

The last query with query_id 6 is the SQL I just executed;

Query the specific execution time

Let’s query this entry in detail based on its ID.

mysql> show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000082 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000009 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000035 |
| init                           | 0.000005 |
| System lock                    | 0.000012 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000016 |
| preparing                      | 0.000019 |
| executing                      | 0.016889 |
| end                            | 0.000021 |
| query end                      | 0.000010 |
| closing tables                 | 0.000012 |
| freeing items                  | 0.000012 |
| cleaning up                    | 0.000015 |
+--------------------------------+----------+
16 rows in set.1 warning (0.00 sec)
Copy the code

The following is a detailed explanation of the process

  • Starting / /
  • Checking permissions // Checking permissions
  • Opening tables // open tables
  • Init // Initialize
  • System lock // Lock mechanism
  • Optimizing // optimizing
  • // Parse the syntax tree
  • Prepareing // Prepare
  • Executing engine starts
  • End // The engine execution is complete
  • Query end // Query end
  • Closing tables // Closing tables are closed
  • Freeing items // Freeing memory
  • Cleaning up // Cleaning up

Reference documentation

High Performance mysql