首页 > 数据库 > MySQL > 正文

MySQL中使用SHOW PROFILE命令分析性能的用法整理

2024-07-24 13:08:09
字体：大 中 小
来源：转载
供稿：网友

这篇文章主要介绍了MySQL中使用show profile命令分析性能的用法整理,show profiles是数据库性能优化的常用命令,需要的朋友可以参考下

show profile是由Jeremy Cole捐献给MySQL社区版本的。默认的是关闭的，但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源。为了统计报表,把profiling设为1

 

 
  1. mysql> SET profiling = 1; 

之后在运行一个查询

 

 
  1. mysql> SELECT COUNT(DISTINCT actor.first_name) AS cnt_name, COUNT(*) AS cnt 
  2. -> FROM sakila.film_actor 
  3. -> INNER JOIN sakila.actor USING(actor_id) 
  4. -> GROUP BY sakila.film_actor.film_id 
  5. -> ORDER BY cnt_name DESC; 
  6. ... 
  7. 997 rows in set (0.03 sec) 

这个执行语句的剖析信息存储在这个会话中。使用SHOW PROFILES进行查看。

 

 
  1. mysql> SHOW PROFILES/G 

 

 
  1. *************************** 1. row *************************** 
  2. Query_ID: 1 
  3. Duration: 0.02596900 
  4. Query: SELECT COUNT(DISTINCT actor.first_name) AS cnt_name,... 

你可以使用SHOW PROFILE语句来获取已经存储的剖析数据。如果不加参数，会显示状态以及它们持续的时间。

 

 
  1. mysql> SHOW PROFILE; 

 

 
  1. +------------------------+-----------+ 
  2. | Status | Duration | 
  3. +------------------------+-----------+ 
  4. | (initialization) | 0.000005 | 
  5. | Opening tables | 0.000033 | 
  6. | System lock | 0.000037 | 
  7. | Table lock | 0.000024 | 
  8. | init | 0.000079 | 
  9. | optimizing | 0.000024 | 
  10. | statistics | 0.000079 | 
  11. | preparing | 0.00003 | 
  12. | Creating tmp table | 0.000124 | 
  13. | executing | 0.000008 | 
  14. | Copying to tmp table | 0.010048 | 
  15. | Creating sort index | 0.004769 | 
  16. | Copying to group table | 0.0084880 | 
  17. | Sorting result | 0.001136 | 
  18. | Sending data | 0.000925 | 
  19. | end | 0.00001 | 
  20. | removing tmp table | 0.00004 | 
  21. | end | 0.000005 | 
  22. | removing tmp table | 0.00001 | 
  23. | end | 0.000011 | 
  24. | query end | 0.00001 | 
  25. | freeing items | 0.000025 | 
  26. | removing tmp table | 0.00001 | 
  27. | freeing items | 0.000016 | 
  28. | closing tables | 0.000017 | 
  29. | logging slow query | 0.000006 | 
  30. +------------------------+-----------+ 

每行都是状态变化的过程以及它们持续的时间。Status那一列和SHOW FULL PROCESSLIST的State应该是一致的。

这个值是来自于thd->proc_info变量。因此你所查看的这个值是直接来自MySQL内部的。尽管这些数值是比较直接易懂的，你也可以查看MySQL手册。

你可以给SHOW PROFILES指定一个Query_ID来查看指定的语句，还可以给输出添加新的列。如，查看用户和CPU使用。可以用如下命令。

 

 
  1. mysql> SHOW PROFILE CPU FOR QUERY 1; 

SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能，不能查看和剖析其他连接的语句，以及剖析时所引起的消耗。

SHOW PROFILES显示最近发给服务器的多条语句，条数根据会话变量profiling_history_size定义，默认是15，最大值为100。设为0等价于关闭分析功能。

SHOW PROFILE FOR QUERY n，这里的n就是对应SHOW PROFILES输出中的Query_ID。

例如：

 

 
  1. mysql> show profiles; 

 

 
  1. +----------+-------------+---------------------------------------+ 
  2. | Query_ID | Duration | Query | 
  3. +----------+-------------+---------------------------------------+ 
  4. | 1 | 0.00037700 | alter table table1 drop column c3 int | 
  5. | 2 | 70.37123800 | alter table table1 drop column c3 | 
  6. | 3 | 0.00124500 | show tables | 
  7. | 4 | 0.00569800 | select * from table1 where id=2 | 
  8. | 5 | 0.00068500 | select count(1) from tables1 | 
  9. | 6 | 0.00197900 | select count(1) from table1 | 
  10. | 7 | 0.00105900 | alter table tables1 drop c1 | 
  11. | 8 | 0.00800200 | alter table table1 drop c1 | 
  12. +----------+-------------+---------------------------------------+ 
  13. 8 rows in set (0.00 sec) 

 

  1. mysql> SHOW PROFILE FOR QUERY 2; #查看alter table table1 drop column c3的分析 

 

 
  1. +------------------------------+-----------+ 
  2. | Status | Duration | 
  3. +------------------------------+-----------+ 
  4. | starting | 0.000183 | 
  5. | checking permissions | 0.000057 | 
  6. | checking permissions | 0.000059 | 
  7. | init | 0.000060 | 
  8. | Opening tables | 0.000071 | 
  9. | System lock | 0.000062 | 
  10. | setup | 0.000080 | 
  11. | creating table | 0.005052 | 
  12. | After create | 0.000220 | 
  13. | copy to tmp table | 0.000244 | 
  14. | rename result table | 70.364027 | 
  15. | end | 0.000575 | 
  16. | Waiting for query cache lock | 0.000062 | 
  17. | end | 0.000075 | 
  18. | query end | 0.000057 | 
  19. | closing tables | 0.000061 | 
  20. | freeing items | 0.000080 | 
  21. | logging slow query | 0.000056 | 
  22. | logging slow query | 0.000098 | 
  23. | cleaning up | 0.000059 | 
  24. +------------------------------+-----------+ 
  25. 20 rows in set (0.00 sec) 

如果没有指定FOR QUERY，那么输出最近一条语句的信息。

LIMIT部分的用法与SELECT中LIMIT子句一致，不赘述。

type是可选的，取值范围可以如下：

ALL 显示所有性能信息

BLOCK IO 显示块IO操作的次数

CONTEXT SWITCHES 显示上下文切换次数，不管是主动还是被动

CPU 显示用户CPU时间、系统CPU时间

IPC 显示发送和接收的消息数量

MEMORY [暂未实现]

PAGE FAULTS 显示页错误数量

SOURCE 显示源码中的函数名称与位置

SWAPS 显示SWAP的次数

例：

 

  1. mysql> show profile cpu for query 2; 

 

 
  1. +------------------------------+-----------+----------+------------+ 
  2. | Status | Duration | CPU_user | CPU_system | 
  3. +------------------------------+-----------+----------+------------+ 
  4. | starting | 0.000183 | 0.000000 | 0.000000 | 
  5. | checking permissions | 0.000057 | 0.000000 | 0.000000 | 
  6. | checking permissions | 0.000059 | 0.000000 | 0.000000 | 
  7. | init | 0.000060 | 0.000000 | 0.000000 | 
  8. | Opening tables | 0.000071 | 0.000000 | 0.000000 | 
  9. | System lock | 0.000062 | 0.000000 | 0.000000 | 
  10. | setup | 0.000080 | 0.000000 | 0.001000 | 
  11. | creating table | 0.005052 | 0.003000 | 0.001000 | 
  12. | After create | 0.000220 | 0.000000 | 0.000000 | 
  13. | copy to tmp table | 0.000244 | 0.000000 | 0.000000 | 
  14. | rename result table | 70.364027 | 7.470864 | 41.612674 | 
  15. | end | 0.000575 | 0.000000 | 0.001000 | 
  16. | Waiting for query cache lock | 0.000062 | 0.000000 | 0.000000 | 
  17. | end | 0.000075 | 0.000000 | 0.000000 | 
  18. | query end | 0.000057 | 0.000000 | 0.000000 | 
  19. | closing tables | 0.000061 | 0.000000 | 0.000000 | 
  20. | freeing items | 0.000080 | 0.000000 | 0.000000 | 
  21. | logging slow query | 0.000056 | 0.000000 | 0.000000 | 
  22. | logging slow query | 0.000098 | 0.000000 | 0.000000 | 
  23. | cleaning up | 0.000059 | 0.000000 | 0.000000 | 
  24. +------------------------------+-----------+----------+------------+ 
  25. 20 rows in set (0.00 sec) 

ps:

SHOW PROFILE ALL FOR QUERY 2;的信息还可以通过SELECT * FROM information_schema.profiling WHERE query_id = 2 ORDER BY seq;获取。

作用范围

这个命令只是在本会话内起作用，即无法分析本会话外的语句。

开启分析功能后，所有本会话中的语句都被分析(甚至包括执行错误的语句)，除了SHOW PROFILE和SHOW PROFILES两句本身。

应用示例：使用SHOW PROFILE分析查询缓存命中的性能优势。

 

 
  1. mysql> set profiling=1; 

 

 
  1. Query OK, 0 rows affected (0.00 sec) 

 

 
  1. mysql> select count(1) as cnt from tran_excution; 

 

 
  1. +-------+ 
  2. | cnt | 
  3. +-------+ 
  4. | 14225 | 
  5. +-------+ 
  6. 1 row in set (0.00 sec) 

由于已经启用了查询缓存，相同查询(非分区表)可以直接在查询缓存中命中。

 

 
  1. mysql> select count(1) as cnt from tran_excution; 

我们仔细看下两个同样的语句的分析。

 

 
  1. mysql> show profile source for query 1; 

 

 
  1. +--------------------------------+----------+-----------------------+---------------+-------------+ 
  2. | Status | Duration | Source_function | Source_file | Source_line | 
  3. +--------------------------------+----------+-----------------------+---------------+-------------+ 
  4. | starting | 0.000048 | NULL | NULL | NULL | 
  5. | Waiting for query cache lock | 0.000013 | try_lock | sql_cache.cc | 454 | 
  6. | checking query cache for query | 0.000040 | send_result_to_client | sql_cache.cc | 1561 | 
  7. | checking permissions | 0.000023 | check_access | sql_parse.cc | 4751 | 
  8. | Opening tables | 0.000040 | open_tables | sql_base.cc | 4831 | 
  9. | System lock | 0.000020 | mysql_lock_tables | lock.cc | 299 | 
  10. | Waiting for query cache lock | 0.000037 | try_lock | sql_cache.cc | 454 | 
  11. | init | 0.000020 | mysql_select | sql_select.cc | 2579 | 
  12. | optimizing | 0.000015 | optimize | sql_select.cc | 865 | 
  13. | statistics | 0.000017 | optimize | sql_select.cc | 1056 | 
  14. | preparing | 0.000016 | optimize | sql_select.cc | 1078 | 
  15. | executing | 0.000015 | exec | sql_select.cc | 1836 | 
  16. | Sending data | 0.003875 | exec | sql_select.cc | 2380 | 
  17. | end | 0.000018 | mysql_select | sql_select.cc | 2615 | 
  18. | query end | 0.000015 | mysql_execute_command | sql_parse.cc | 4440 | 
  19. | closing tables | 0.000016 | mysql_execute_command | sql_parse.cc | 4492 | 
  20. | freeing items | 0.000016 | mysql_parse | sql_parse.cc | 5640 | 
  21. | Waiting for query cache lock | 0.000012 | try_lock | sql_cache.cc | 454 | 
  22. | freeing items | 0.000032 | NULL | NULL | NULL | 
  23. | Waiting for query cache lock | 0.000017 | try_lock | sql_cache.cc | 454 | 
  24. | freeing items | 0.000016 | NULL | NULL | NULL | 
  25. | storing result in query cache | 0.000017 | end_of_result | sql_cache.cc | 1020 | 
  26. | logging slow query | 0.000018 | log_slow_statement | sql_parse.cc | 1461 | 
  27. | logging slow query | 0.000050 | log_slow_statement | sql_parse.cc | 1470 | 
  28. | cleaning up | 0.000018 | dispatch_command | sql_parse.cc | 1417 | 
  29. +--------------------------------+----------+-----------------------+---------------+-------------+ 
  30. 25 rows in set (0.00 sec) 

 

 
  1. mysql> show profile source for query 2; 

 

 
  1. +--------------------------------+----------+-----------------------+--------------+-------------+ 
  2. | Status | Duration | Source_function | Source_file | Source_line | 
  3. +--------------------------------+----------+-----------------------+--------------+-------------+ 
  4. | starting | 0.000051 | NULL | NULL | NULL | 
  5. | Waiting for query cache lock | 0.000014 | try_lock | sql_cache.cc | 454 | 
  6. | checking query cache for query | 0.000016 | send_result_to_client | sql_cache.cc | 1561 | 
  7. | checking privileges on cached | 0.000013 | send_result_to_client | sql_cache.cc | 1652 | 
  8. | checking permissions | 0.000015 | check_access | sql_parse.cc | 4751 | 
  9. | sending cached result to clien | 0.000036 | send_result_to_client | sql_cache.cc | 1749 | 
  10. | logging slow query | 0.000017 | log_slow_statement | sql_parse.cc | 1461 | 
  11. | cleaning up | 0.000018 | dispatch_command | sql_parse.cc | 1417 | 
  12. +--------------------------------+----------+-----------------------+--------------+-------------+ 
  13. 8 rows in set (0.00 sec) 

可以清晰地看到缓存中命中时，大大节省了后台的开销。当然缓存的使用也需要根据各种场景(表的数据规模，更新频率等)考察使用，并不是启用缓存就一定能够提高查询效率。这里仅仅作为SHOW PROFILE的一个应用示例。


注：相关教程知识阅读请移步到MYSQL教程频道。

上一篇：MySQL中的alter table命令的基本使用方法及提速优化

下一篇：关于Mysql中文乱码问题该如何解决(乱码问题完美解决方案)

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
学习交流
更多

硬盘分区如何设置准确的分区空间

硬盘分区如何设置准确的分区空间...
热门图片
更多
猜你喜欢的新闻
猜你喜欢的关注

新闻热点

最牛同桌！我考了696分我同桌考了703分
2024-06-26 22:28:41
650分！高二女生考入北大：遗憾不能上高三
2024-06-26 22:26:16
男生估分600只考了397 妈妈：高考虽重要，但不代表所有
2024-06-26 22:23:01
唐尚珺回应是否会直播带货：有人出100万想和他合作！
2024-06-25 19:29:23
名校抢人名场面：清华、北大太拼了！
2024-06-25 19:22:14
男生高考语文满分！网友：第一次听说
2024-06-25 19:19:15

疑难解答

图片精选

网友关注

关于本站 - 网上投稿 - 商务合作 - 隐私政策 - 网站地图
Copyright © 2008 - 2024 VEVB.COM. All Rights Reserved.武林网 版权所有