Profiler tool of MySQL

In applications with large data volumes, you often need to optimize database configurations and SQL statements to ensure the concurrency of applications when loading large data volumes. MySQL has a built-in analyzer Profiler. With Profiler, developers can easily and quickly understand the approximate performance of MySQL.

It is very easy to use Profiler. You only need to set the system variable "profiling = on" or "profiling = 1". By default, Profiler is disabled. You can run the command "SELECT @ profiling; "or" SHOW variables LIKE '% profiling %'; "to view the Profiler status. :

Run "SET profiling = on;" or "SET profiling = 1;" to use Profiler.

For example, suppose there is a database demo, and the demo contains a data table tab_demo. Its structure is as follows:

Run the "SELECT * FROM tab_demo;" command to query all records in the table. The following result is returned:

The following describes how to use Profiler to view the above database operations. Run the "SHOW profiles;" command to return the time spent in each operation ,:

To better understand what the database has done each time, FOR example, to view the specific process of the 4th Query operation, run the "SHOW profile FOR query 4;" command ;", returned results:

You can clearly see the specific process for executing an SQL statement, including initialization, opening the table, and locking the table. In this way, you can probably know which step takes more time to optimize the database or SQL statements.

In general, when performing database performance analysis, Profiler can be used as a simple and fast analysis tool to obtain some information first, and then cooperate with other dedicated performance analysis tools, it can achieve good results.