要想优化一条 query,我们就需要清楚的知道这条 query 的性能瓶颈到底在哪里,是消耗的 cpu计算太多,还是需要的的 io 操作太多?要想能够清楚的了解这些信息,在 mysql 5.0 和 mysql 5.1正式版中已经可以非常容易做到了,那就是通过 query profiler 功能。
mysql 的 query profiler 是一个使用非常方便的 query 诊断分析工具,通过该工具可以获取一条query 在整个执行过程中多种资源的消耗情况,如 cpu,io,ipc,swap 等,以及发生的 page faults,context switche 等等,同时还能得到该 query 执行过程中 mysql 所调用的各个函数在源文件中的位置。
下面我们看看 query profiler 的具体用法。
1、 开启 profiling 参数
mysql> set profiling=1;
query ok, 0 rows affected
通过执行 “set profiling”命令,可以开启关闭 query profiler 功能。
2、 执行 query
mysql> select * from user;
+----+---------+------+----------+---------------+-----------------+------+---------------------+-------------+
| id | account | name | password | department_id | department_name | salt | create_time | update_time |
+----+---------+------+----------+---------------+-----------------+------+---------------------+-------------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1s | 2018-10-01 14:46:55 | null |
+----+---------+------+----------+---------------+-----------------+------+---------------------+-------------+
1 row in set
在开启 query profiler 功能之后,mysql 就会自动记录所有执行的 query 的 profile 信息了。
3、获取系统中保存的所有 query 的 profile 概要信息
mysql> show profiles;
+----------+------------+-----------------------------------------------------------------+
| query_id | duration | query |
+----------+------------+-----------------------------------------------------------------+
| 1 | 0.00427225 | select * from user |
| 2 | 0.00023 | select * from user |
| 3 | 0.00019475 | select * from user |
| 4 | 0.000192 | select * from user |
| 5 | 8.725e-5 | select * from user where id='1'
select * from user where id='1' |
| 6 | 0.00028875 | select * from user where id=1 |
| 7 | 7.5e-5 | set profiling=1 |
| 8 | 0.00020075 | select * from user |
+----------+------------+-----------------------------------------------------------------+
8 rows in set
通过执行 “show profile” 命令获取当前系统中保存的多个 query 的 profile 的概要信息。
4、针对单个 query 获取详细的 profile 信息。
在获取到概要信息之后,我们就可以根据概要信息中的 query_id 来获取某个 query 在执行过程中
详细的 profile 信息了,具体操作如下:
mysql> show profile cpu,block io for query 8
;
+----------------------+----------+----------+------------+--------------+---------------+
| status | duration | cpu_user | cpu_system | block_ops_in | block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 3.1e-5 | 0 | 0 | null | null |
| checking permissions | 6e-6 | 0 | 0 | null | null |
| opening tables | 1.4e-5 | 0 | 0 | null | null |
| system lock | 6e-6 | 0 | 0 | null | null |
| init | 1.3e-5 | 0 | 0 | null | null |
| optimizing | 3e-6 | 0 | 0 | null | null |
| statistics | 8e-6 | 0 | 0 | null | null |
| preparing | 5e-6 | 0 | 0 | null | null |
| executing | 1e-6 | 0 | 0 | null | null |
| sending data | 4.6e-5 | 0 | 0 | null | null |
| end | 3e-6 | 0 | 0 | null | null |
| query end | 2e-6 | 0 | 0 | null | null |
| closing tables | 5e-6 | 0 | 0 | null | null |
| freeing items | 5.4e-5 | 0 | 0 | null | null |
| logging slow query | 2e-6 | 0 | 0 | null | null |
| cleaning up | 3e-6 | 0 | 0 | null | null |
+----------------------+----------+----------+------------+--------------+---------------+
16 rows in set
上面的例子中是获取 cpu 和 block io 的消耗,非常清晰,对于定位性能瓶颈非常适用。希望得到取其他的信息,都可以通过执行 “show profile *** for query n” 来获取,各位读者朋友可以自行测试熟悉。
希望与广大网友互动??
点此进行留言吧!