MYSQL查询优化：profile功能

淡淡的烟草味﹌ 2022-08-01 00:26 188阅读 0赞

# 日常数据库性能分析必备工具--李晓蒙  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是用户变量，每次都得重新启用。   以下是我做的一些实验。数据很明显，就不多解释了。  ![复制代码][copycode.gif]  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** mysql> show profiles; \+\--\--------+-------------+------------------------------------------------+ | Query\_ID | Duration | Query | \+\--\--------+-------------+------------------------------------------------+ | **1** | **0.02717000** | show tables | | **2** | **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(\*) | \+\--\--------+ | **4194304** | \+\--\--------+ **1** row in set (**1.51** sec) mysql> show profiles; \+\--\--------+-------------+------------------------------------------------+ | Query\_ID | Duration | Query | \+\--\--------+-------------+------------------------------------------------+ | **1** | **0.02717000** | show tables | | **2** | **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 | | **2** | **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**;      #

[copycode.gif]: /images/20220801/82f6637c73124d31bbffb8ce20dcb61b.png