MySQL query optimization: profile function

MySQL query optimization: profile function

MySQL query optimization: profile function. Note that you need to install the profile module.

1. The version is later than 5.0.37. (Show profiles and show profile were added in MySQL 5.0.37 .)

SELECT @ profiling;
To check whether the profile has been enabled. If the profilng value is 0, you can use

SET profiling = 1;
. After profiling is enabled, we execute a query statement, for example:

Select count (*) from roi_summary;
Then, view show profiles as follows:

+ ---------------- + ------------ + ------------------------------------ +
| Query_ID | Duration | Query |
+ ---------------- + ------------ + ------------------------------------ +
| 1 | 0.00021500 | select @ profiling |
| 1 | 0.05522700 | select count (*) from roi_summary |
+ ---------------- + ------------ + ------------------------------------ +
2 rows in set (0.00 sec)

The statement with ID 5 is the query statement just executed.

2. The variable profiling is the user variable and must be re-enabled each time.
Below are some of my experiments. The data is obvious, so I will not explain it much.

Mysql> use test
Database changed
Mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Bag_item |
| Bag_user |
| Score |
| T |
+ ---------------- +
4 rows in set (0.03 sec)

Mysql> select count (*) from t;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 2097152 |
+ ---------- +
1 row in set (0.74 sec)

Mysql> show profiles;
+ ---------- + ------------ + ------------------------ +
| Query_ID | Duration | Query |
+ ---------- + ------------ + ------------------------ +
| 1 | 0.02717000 | show tables |
| 1 | 0.74770100 | select count (*) from t |
+ ---------- + ------------ + ------------------------ +
2 rows in set (0.00 sec)

Mysql> show profile for query 2;
+ -------------------------------- + ---------- +
| Status | Duration |
+ -------------------------------- + ---------- +
| (Initialization) | 0.000004 |
| Checking query cache for query | 0.000044 |
| Opening tables | 0.000012 |
| System lock | 0.000017 |
| Table lock | 0.00003 |
| Init | 1, 0.000013 |
| Optimizing | 0.000008 |
| Statistics | 0.000013 |
| Preparing | 0.000011 |
| Executing | 0.000006 |
| Sendingdata | 0.747313 |
| End| 0.000014 |
| Query end | 0.000006 |
| Storing result in query cache | 0.000006 |
| Freeing items | 0.000012 |
| Closing tables | 0.000009 |
| Logging slow query | 0.000183 |
+ -------------------------------- + ---------- +
17 rows in set (0.00 sec)

Mysql> show profile block io, cpu for query 2;
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
| (Initialization) | 0.000004 | 0 | 0 | 0 | 0 |
| Checking query cache for query | 0.000044 | 0 | 0 | 0 | 0 |
| Opening tables | 0.000012 | 0 | 0 | 0 | 0 |
| System lock | 0.000017 | 0 | 0 | 0 | 0 |
| Table lock | 0.00003 | 0 | 0 | 0 | 0 |
| Init | 0.000013 | 0 | 0 | 0 | 0 |
| Optimizing | 0.000008 | 0 | 0 | 0 | 0 |
| Statistics | 0.000013 | 0 | 0 | 0 | 0 |
| Preparing | 0.000011 | 0 | 0 | 0 | 0 |
| Executing | 0.000006 | 0 | 0 | 0 | 0 |
| Sending data | 0.747313 | 0.746887 | 0 | 0 | 0 |
| End| 0.000014 | 0 | 0 | 0 | 0 |
| Query end | 0.000006 | 0 | 0 | 0 | 0 |
| Storing result in query cache | 0.000006 | 0 | 0 | 0 | 0 |
| Freeing items | 0.000012 | 0 | 0 | 0 | 0 |
| Closing tables | 0.000009 | 0 | 0 | 0 | 0 |
| Logging slow query | 0.000183 | 0 | 0 | 0 | 0 |
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
17 rows in set (0.00 sec)

Mysql> insert into t (username) select username from t;
Query OK, 2097152 rows affected (34.17 sec)
Records: 2097152 Duplicates: 0 Warnings: 0

Mysql> show profiles;
+ ---------- + ------------- + ------------------------------------------------ +
| Query_ID | Duration | Query |
+ ---------- + ------------- + ------------------------------------------------ +
| 1 | 0.02717000 | show tables |
| 1 | 0.74770100 | select count (*) from t |
| 3 | 0.00004200 | show prifile for query 2 |
| 4 | 34.30410100 | insert into t (username) select username from t |
+ ---------- + ------------- + ------------------------------------------------ +
4 rows in set (0.00 sec)

Mysql> show profile cpu, block io, memory, swaps for query 4;

Mysql> select count (*) from t;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 4194304 |
+ ---------- +
1 row in set (1.51 sec)

Mysql> show profiles;
+ ---------- + ------------- + ------------------------------------------------ +
| Query_ID | Duration | Query |
+ ---------- + ------------- + ------------------------------------------------ +
| 1 | 0.02717000 | show tables |
| 1 | 0.74770100 | select count (*) from t |
| 3 | 0.00004200 | show prifile for query 2 |
| 4 | 34.30410100 | insert into t (username) select username from t |
| 5 | 1.50563800 | select count (*) from t |
+ ---------- + ------------- + ------------------------------------------------ +
5 rows in set (0.00 sec)

Mysql> show profile cpu, block io, memory, swaps, context switches, source for query 5;
......
Mysql> update t set username = 'waill ';
Query OK, 4194304 rows affected (44.82 sec)
Rows matched: 4194304 Changed: 4194304 Warnings: 0

Mysql> show profiles;
+ ---------- + ------------- + ------------------------------------------------ +
| Query_ID | Duration | Query |
+ ---------- + ------------- + ------------------------------------------------ +
| 1 | 0.02717000 | show tables |
| 1 | 0.74770100 | select count (*) from t |
| 3 | 0.00004200 | show prifile for query 2 |
| 4 | 34.30410100 | insert into t (username) select username from t |
| 5 | 1.50563800 | select count (*) from t |
| 6 | 44.82054700 | update t set username = 'waill' |
+ ---------- + ------------- + ------------------------------------------------ +
6 rows in set (0.00 sec)

Mysql> show profile cpu, block io, memory, swaps, context switches, source for query 6;

This article permanently updates the link address: