Use MySQLprofiles to view the SQL statement execution plan

Source: Internet
Author: User
To use this function, mysql version must be 5.0.37 or later. Otherwise, you can only use the explain method to check. Profiling function can be used to learn more about cpuio and so on.

To use this function, mysql version must be 5.0.37 or later. Otherwise, you can only use the explain method to check. The profiling function can be used to learn more about cpu io and so on.

To use this function, mysql version must be 5.0.37 or later. Otherwise, you can only use the explain method to check.

The profiling function provides more detailed information such as cpu io.

The show profile format is as follows:

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

By default, this function is disabled:

Mysql> select @ profiling;

+ ------------- +
| @ Profiling |
+ ------------- +
| 0 |
+ ------------- +
1 row in set (0.00 sec)

Enable Function

Mysql> set profiling = 1;

+ ------------- +
| @ Profiling |
+ ------------- +
| 1 |
+ ------------- +
1 row in set (0.00 sec)

Enter the SQL statement to be executed:

Mysql> select count (*) from sySUSEr;

Mysql> select count (*) from sysuser;

Mysql> show profiles \ G;
* *************************** 1. row ***************************
Query_ID: 1
Duration: 0.00007550
Query: select count (*) from sysuser
1 row in set (0.00 sec)

Query the execution information of a specified SQL statement using the specified Query_ID:

Mysql> show profile for query 1;

+ -------------------------------- + ---------- +
| Status | Duration |
+ -------------------------------- + ---------- +
| Startling | 0.000028 |
| Checking query cache for query | 0.000008 |
| Checking privileges on cached| 0.000009 |
| Sending cached result to clien | 0.000023 |
| Logging slow query | 0.000004 |
| Cleaningup | 0.000003 |
+ -------------------------------- + ---------- +
6 rows in set (0.00 sec)

Mysql> show profile cpu, block io for query 1;
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
| Starting | 0.000028 | NULL |
| Checking query cache for query | 0.000008 | NULL |
| Checking privileges on cached | 0.000009 | NULL |
| Sending cached result to clien | 0.000023 | NULL |
| Logging slow query | 0.000004 | NULL |
| Cleaning up | 0.000003 | NULL |
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
6 rows in set (0.00 sec)

If the for parameter is not included, the profile information of the last statement is listed:

Mysql> show profile cpu, block io for query 1;
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
| Starting | 0.000028 | NULL |
| Checking query cache for query | 0.000008 | NULL |
| Checking privileges on cached | 0.000009 | NULL |
| Sending cached result to clien | 0.000023 | NULL |
| Logging slow query | 0.000004 | NULL |
| Cleaning up | 0.000003 | NULL |
+ ------------------------------ + ---------- + ------------ + ---------------- + --------------- +
6 rows in set (0.00 sec)

Close parameters:

Mysql> set profiling = 0

+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Profiling | OFF |
+ --------------- + ------- +
1 row in set (0.00 sec)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.