mysql查询优化 profile功能_MySQL查询优化：profile功能

MySQL查询优化：profile功能，这里还需要注意一点就是，需要安装profile模块才能实现。

MySQL查询优化：profile功能，，这里还需要注意一点就是，需要安装profile模块才能实现。

1、不过版本要在5.0.37之后。(SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37.)

SELECT @@profiling;

来查看是否已经启用profile，如果profilng值为0，可以通过

SET profiling = 1;

来启用。启用profiling之后，我们执行一条查询语句，比如：

select count(*) from roi_summary;

然后show profiles查看如下：

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

| Query_ID | Duration | Query |

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

| 1 | 0.00021500 | select @@profiling |

| 2 | 0.05522700 | select count(*) from roi_summary |

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

2 rows in set (0.00 sec)

其中ID为5的语句是刚执行的查询语句

2、变量profiling是用户变量，每次都得重新启用。

以下是我做的一些实验。数据很明显，就不多解释了。

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(*) |

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

| 2097152 |

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

1 row in set (0.74 sec)

mysql> show profiles;

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

| Query_ID | Duration | Query |

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

| 1 | 0.02717000 | show tables |

| 2 | 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 | 0.000013 |

| optimizing | 0.000008 |

| statistics | 0.000013 |

| preparing | 0.000011 |

| executing | 0.000006 |

| Sending data | 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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网