Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

Author’s other platforms:

| CSDN:blog.csdn.net/qq_4115394…

| the nuggets: juejin. Cn/user / 651387…

| zhihu: www.zhihu.com/people/1024…

| GitHub:github.com/JiangXia-10…

| public no. : 1024 notes

This article is about 1,734 words long and takes five minutes to read

1 introduction

Early in the process of application development, due to the development of the relatively small amount of data is generally, so the development process is compared commonly pay attention to the realization of the function, but when completed an application or system, the rapid growth as the amount of production data, so many before written SQL statement will show a certain performance issues, the impact on the production will be more and more big, These improper SQL statements can become the bottleneck of the entire system performance, and must be optimized in order to pursue the ultimate performance of the system.

2 the body

Explain how to use Explain commands to analyze SQL execution plans. The specific command format is as follows

The explain + select languageCopy the code

Today’s article will show you how to use the show profile command to analyze SQL performance and time consumption.

MySQL has added support for show profile and show Profiles statements since version 5.0.37, so to use this feature, make sure that version is later than 5.0.37. Show Profiles can help us understand the performance cost of SQL statements when doing SQL tuning.

In MySQL databases, SQL profiling can be enabled by configuring the profiling parameter. This parameter can be set at the global and session levels. For the global level, this applies to the entire MySQL instance, while the session level only affects the current session. When this parameter is enabled, subsequent SQL statements record their resource costs, such as IO, context switching, CPU, Memory, and so on. Based on these costs, the current SQL bottleneck is further analyzed for optimization and tuning.

The have_profiling parameter allows you to see if MySQL currently supports profiles:

Profiling is turned off by default, so profiling can be turned on at the session level with the set statement:

set profiling=1
Copy the code

Profiles help you understand the SQL execution process.

For example, after entering the show profiles command, we can see the execution time of each SQL statement:

Queryid Specifies the ID of the current query. Query represents the SQL operation executed. Duration Duration of SQL operation execution.

After executing the show profiles command, you can run the following command to analyze the time consumption of a CERTAIN SQL statement:

show profile for query query_id
Copy the code

For example, to parse the SQL operation with query_ID 175:

show profile for query 175
Copy the code

The results are as follows:

Status indicates the Status phase, and Duration indicates the Duration of the Status phase.

Mysql supports all, CPU, block IO, page faults, and other details to see what resources mysql is using that are consuming too much time if you type:

show profile for all query query_id
Copy the code

All information can be displayed, as follows:

You can also type

Show profile for query Query_id For example, show profile for CPU query query_idCopy the code

View the details.

3 summary

This article mainly introduces the show profiles and show profile commands to view the specific elapsed time of SQL operations in each phase, which facilitates the subsequent SQL optimization operations.

Other methods and steps for SQL tuning will be shared later.

Finally, if you think this article is good, you can recommend it to more people.

Today’s recommendation

Today we are going to talk about MySQL’s storage engine

SQL language: DDL, DML, DQL, DCL details

Check how often SQL statements are executed

How to locate inefficient SQL execution

Explain the execution plan

Use the Trace tool to analyze the optimizer execution plan