How to use profile to analyze SQL execution state in MySQL

Open profile

Mysql> SELECT @ @have_profiling;

+------------------+

| @ @have_profiling |

+------------------+

| YES |

+------------------+

1 row in Set (0.00 sec)

Mysql> SELECT @ @profiling;

+-------------+

| @ @profiling |

+-------------+

| 0 |

+-------------+

1 row in Set (0.00 sec)

Mysql> set session profiling=1;

Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT @ @profiling;

+-------------+

| @ @profiling |

+-------------+

| 1 |

+-------------+

1 row in Set (0.00 sec)

Using profile Analysis SQL, you can see the change in execution efficiency of Send data and sending cached results to clien two times after execution

Mysql> Select COUNT (*) from sakila.payment;

+----------+

| COUNT (*) |

+----------+

| 16049 |

+----------+

1 row in Set (0.03 sec)

Mysql> Show Profiles;

+----------+------------+-------------------------------------+

| query_id | Duration | Query |

+----------+------------+-------------------------------------+

| 1 | 0.00020400 | SELECT @ @profiling |

| 2 | 0.00008900 | Select COUNT (*) from payment |

| 3 | 0.00006800 | Show Databaes |

| 4 | 0.02102800 | Show Databases |

| 5 | 0.02847600 | Select COUNT (*) from Sakila.payment |

This column more highlights: http://www.bianceng.cn/database/MySQL/

+----------+------------+-------------------------------------+

5 rows in Set (0.00 sec)

Mysql> Show profiles for query 5;

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| Starting | 0.000030 |

| Waiting for query Cache lock | 0.000005 |

| Checking query cache for Query | 0.000043 |

| Checking Permissions | 0.000007 |

| Opening Tables | 0.000027 |

| System Lock | 0.000010 |

| Waiting for query Cache lock | 0.000010 |

| init | 0.000000 |

| Optimizing | 0.023255 |

| Statistics | 0.000118 |

| Preparing | 0.000041 |

| Executing | 0.000033 |

| Sending Data | 0.003833 |

| End | 0.000054 |

| Query End | 0.000045 |

| Closing Tables | 0.000045 |

| Freeing items | 0.000072 |

| Waiting for query Cache lock | 0.000033 |

| Freeing items | 0.000785 |

| Waiting for query Cache lock | 0.000016 |

| Freeing items | 0.000002 |

| Storing result in query cache | 0.000005 |

| Logging Slow Query | 0.000003 |

| Cleaning Up | 0.000004 |

+--------------------------------+----------+

Rows in Set (0.00 sec)

Mysql> Select COUNT (*) from sakila.payment;

+----------+

| COUNT (*) |

+----------+

| 16049 |

+----------+

1 row in Set (0.00 sec)

Mysql> Show Profiles;

+----------+------------+-------------------------------------+

| query_id | Duration | Query |

+----------+------------+-------------------------------------+

| 1 | 0.00020400 | SELECT @ @profiling |

| 2 | 0.00008900 | Select COUNT (*) from payment |

| 3 | 0.00006800 | Show Databaes |

| 4 | 0.02102800 | Show Databases |

| 5 | 0.02847600 | Select COUNT (*) from Sakila.payment |

| 6 | 0.00006900 | Select COUNT (*) from Sakila.payment |

+----------+------------+-------------------------------------+

6 rows in Set (0.00 sec)

Mysql> Show profiles for query 6;

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| Starting | 0.000029 |

| Waiting for query Cache lock | 0.000004 |

| Checking query cache for Query | 0.000007 |

| Checking privileges on Cached | 0.000004 |

| Checking Permissions | 0.000008 |

| Sending cached result to Clien | 0.000012 |

| Logging Slow Query | 0.000002 |

| Cleaning Up | 0.000003 |

+--------------------------------+----------+

8 rows in Set (0.00 sec)