MySQL SQL Tuning: Profile locates the performance bottleneck of a single QUERY

Not only does it not help, but it also wastes a lot of manpower and time costs, and may even drag the server down
Therefore, when we accept the optimization of an SQL statement, the first thing is to understand where the query problem is?
Is it IO? Is it a CPU? Only by knowing where the bottleneck is can you take the right medicine and get rid of the disease
MySQL QUERY Profiler is a very convenient QUERY diagnostic tool, introduced in 5.0. 5.1GA embedded
This tool presents in detail every action of SQL throughout the lifecycle, which is similar to enabling the 1046 event in Oracle.
We can clearly understand whether the SQL statement costs much in data access or operations (sorting or grouping ).
So we won't blindly see order by, so we will go to tuning sort buffer and ignore sorting result time is so little

SHOW PROFILE [type [, type] ... ]    [FOR QUERY n]    [LIMIT row_count [OFFSET offset]]type:    ALL  | BLOCK IO  | CONTEXT SWITCHES  | CPU  | IPC  | MEMORY  | PAGE FAULTS  | SOURCE  | SWAPS

The default output result only shows Status and Duration. We can specify the type to extend the output.
I usually use CPU and BLOCK IO to output the CPU and IO load. In fact, these are enough.

The profile is disabled by default. It is controlled by the profiling parameter and is session-level.
Enabled: SET profiling = 1
Close: set profiling = 0
Query: select @ profiling
The number of query entries saved by show profiles is controlled by the profiling_history_size parameter. The default value is 15. If it is exceeded, the previous query entries will be removed.

mysql> set profiling=1;mysql> select @@profiling;+-------------+| @@profiling |+-------------+|           1 |+-------------+mysql> select * from employees.t order by first_name;mysql> show profiles;+----------+------------+-----------------------------------------------+| Query_ID | Duration   | Query                                         |+----------+------------+-----------------------------------------------+|        1 | 0.21138800 | show create table employees.t                 ||        2 | 8.21691600 | select * from employees.t order by first_name |+----------+------------+-----------------------------------------------+2 rows in set (0.00 sec)mysql> show profile cpu,block io for query 2;+----------------------+----------+----------+------------+--------------+---------------+| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting             | 0.000160 | 0.000000 |   0.000000 |            0 |             0 || checking permissions | 0.000026 | 0.000000 |   0.000000 |            0 |             0 || Opening tables       | 0.000055 | 0.000000 |   0.000000 |            0 |             0 || System lock          | 0.000033 | 0.000000 |   0.000000 |            0 |             0 || init                 | 0.000050 | 0.000000 |   0.000000 |            0 |             0 || optimizing           | 0.000026 | 0.000000 |   0.000000 |            0 |             0 || statistics           | 0.000145 | 0.000000 |   0.000000 |            0 |             0 || preparing            | 0.000118 | 0.000000 |   0.000000 |            0 |             0 || executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 || Sorting result       | 2.838465 | 1.396087 |   1.140071 |            0 |             0 || Sending data         | 0.928078 | 0.544034 |   0.056003 |            0 |             0 || end                  | 0.000026 | 0.000000 |   0.000000 |            0 |             0 || query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 || closing tables       | 0.000021 | 0.000000 |   0.000000 |            0 |             0 || freeing items        | 4.449672 | 0.000000 |   0.000000 |            0 |             0 || logging slow query   | 0.000014 | 0.000000 |   0.000000 |            0 |             0 || cleaning up          | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |+----------------------+----------+----------+------------+--------------+---------------+17 rows in set (0.00 sec)

Because the Profile is sorted by execution order by default, but in fact we are more concerned about how much time is spent, so that we can easily know which overhead is large.

mysql> SELECT STATE, FORMAT(DURATION, 6) AS DURATION    -> FROM INFORMATION_SCHEMA.PROFILING    -> WHERE QUERY_ID = 2 ORDER BY DURATION DESC;+----------------------+----------+| STATE                | DURATION |+----------------------+----------+| freeing items        | 4.449672 || Sorting result       | 2.838465 || Sending data         | 0.928078 || starting             | 0.000160 || statistics           | 0.000145 || preparing            | 0.000118 || Opening tables       | 0.000055 || init                 | 0.000050 || System lock          | 0.000033 || end                  | 0.000026 || optimizing           | 0.000026 || checking permissions | 0.000026 || closing tables       | 0.000021 || logging slow query   | 0.000014 || query end            | 0.000011 || executing            | 0.000011 || cleaning up          | 0.000005 |+----------------------+----------+17 rows in set (0.00 sec)

Http://dev.mysql.com/doc/refman/5.5/en/show-profile.html


By night
2013-10-3
Good Luck