MySQL Query statement optimization tool

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/8A/7C/wKioL1gyjJygqpqXAAKNg5HRZs4455.png-wh_500x0-wm_3 -wmp_4-s_1737671810.png "title=" Capture.png "alt=" Wkiol1gyjjygqpqxaakng5hrzs4455.png-wh_50 "/>

With this profiling feature open, you can view the consumption of various resources in the entire process of SQL query statements.

Mysql>Show Profiles;
+----------+------------+---------------------------------+
| query_id | Duration | Query |
+----------+------------+---------------------------------+
| 1 | 0.00049600 | Show variables like "Profiling" |
| 2 | 0.02267400 | Show Databases |
| 3 | 0.04235200 | SELECT DATABASE () |
| 4 | 0.00012825 | SELECT DATABASE () |
| 5 | 0.00025125 | Show Databases |
| 6 | 0.00145125 | Show Tables |
| 7 | 0.04207925 | Show Tables |
| 8 | 0.03485225 | Select COUNT (*) from Host_item |
+----------+------------+---------------------------------+

Mysql>Show profile CPU, block IO for query 8;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | Cpu_user | Cpu_system | block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| Starting | 0.000634 |   0.000000 |            0.000000 |             0 | 0 |
| Checking Permissions | 0.000013 |   0.000000 |            0.000000 |             0 | 0 |
| Opening Tables | 0.000051 |   0.000000 |            0.000000 |             0 | 0 |
| System Lock | 0.000014 |   0.000000 |            0.000000 |             0 | 0 |
| init | 0.000015 |   0.000000 |            0.000000 |             0 | 0 |
| Optimizing | 0.033657 |   0.000000 |           0.000000 |             40 | 0 |
| Statistics | 0.000029 |   0.000000 |            0.000000 |             0 | 0 |
| Preparing | 0.000012 |   0.000000 |            0.000000 |             0 | 0 |
| Executing | 0.000006 |   0.000000 |            0.000000 |             0 | 0 |
| Sending Data | 0.000313 |   0.000000 |            0.000000 |             0 | 0 |
| End | 0.000008 |   0.000000 |            0.000000 |             0 | 0 |
| Query End | 0.000008 |   0.000000 |            0.000000 |             0 | 0 |
| Closing Tables | 0.000047 |   0.000000 |            0.000000 |             0 | 0 |
| Freeing items | 0.000042 |   0.000000 |            0.000000 |             0 | 0 |
| Logging Slow Query | 0.000003 |   0.000000 |            0.000000 |             0 | 0 |
| Cleaning Up | 0.000003 |   0.000000 |            0.000000 |             0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------

The show profile can be followed by parameters: All or cpu,block io,page faults and so on.

You can also query directly in the Information_schema.profiling database table:

Select State,sum (Duration) as Total_r,round (100*sum (Duration)/(select SUM (duration) from Information_ schema.profiling where [email protected]_id), 2) as Pct_r,count (*) as Calls,sum (duration)/count (*) as "R/call" from inform ation_schema.profiling where [email protected]_id GROUP BY state ORDER by Total_r desc;

Finally, turn off set profiling=0 this feature.

This article is from the "Victor's Struggle" blog, please be sure to keep this source http://victor2016.blog.51cto.com/6768693/1875012

MySQL Query statement optimization tool