分析SQL执行带来的开销是优化SQL的重要手段，MySQL可以通过设置
profiling参数，将SQL语句的资源开销，如IO、上下文切换、CPU、Memory等记录下来
查看profiling系统变量
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
-
have_profiling： 当前版本是否支持
profiling功能 -
profiling： 是否开启
profiling功能 - profiling_history_size： 保留profiling的数目，默认是15，范围为0~100，为0时代表禁用profiling
开启profiling
启用session级别的profile
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
验证修改后的结果
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
进行profile分析
1. 进行Query操作
mysql> select count(*) from ttkk_user;
+----------+
| count(*) |
+----------+
| 55 |
+----------+
2. 查看当前session产生的profile
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00051550 | show variables like '%profil%' |
| 2 | 0.00016350 | select count(*) from ttkk_user |
+----------+------------+--------------------------------+
3. 获取指定查询的开销
mysql> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000053 |
| checking permissions | 0.000007 |
| Opening tables | 0.000019 |
| init | 0.000013 |
| System lock | 0.000008 |
| optimizing | 0.000008 |
| executing | 0.000009 |
| end | 0.000004 |
| query end | 0.000003 |
| closing tables | 0.000008 |
| freeing items | 0.000020 |
| cleaning up | 0.000013 |
+----------------------+----------+
12 rows in set, 1 warning (0.00 sec)
当查到最耗时的线程状态时，可以进一步选择all或者cpu、block io等明细类型来查看mysql在每个线程状态中使用什么资源上耗费了过高的时间
mysql> show profile block io,cpu for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000053 | 0.000022 | 0.000026 | 0 | 0 |
| checking permissions | 0.000007 | 0.000003 | 0.000004 | 0 | 0 |
| Opening tables | 0.000019 | 0.000008 | 0.000010 | 0 | 0 |
| init | 0.000013 | 0.000006 | 0.000008 | 0 | 0 |
| System lock | 0.000008 | 0.000004 | 0.000004 | 0 | 0 |
| optimizing | 0.000008 | 0.000003 | 0.000004 | 0 | 0 |
| executing | 0.000009 | 0.000004 | 0.000005 | 0 | 0 |
| end | 0.000004 | 0.000002 | 0.000002 | 0 | 0 |
| query end | 0.000003 | 0.000001 | 0.000001 | 0 | 0 |
| closing tables | 0.000008 | 0.000003 | 0.000005 | 0 | 0 |
| freeing items | 0.000020 | 0.000010 | 0.000011 | 0 | 0 |
| cleaning up | 0.000013 | 0.000005 | 0.000007 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
12 rows in set, 1 warning (0.00 sec)
一条query每个阶段的资源开销可以从information_schema.profiling表查询
mysql> select * from profiling where query_id = 2;
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
| 2 | 2 | starting | 0.000053 | 0.000022 | 0.000026 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| 2 | 3 | checking permissions | 0.000007 | 0.000003 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 5350 |
| 2 | 4 | Opening tables | 0.000019 | 0.000008 | 0.000010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5095 |
| 2 | 5 | init | 0.000013 | 0.000006 | 0.000008 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_prepare_select | sql_select.cc | 1051 |
| 2 | 6 | System lock | 0.000008 | 0.000004 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 304 |
| 2 | 7 | optimizing | 0.000008 | 0.000003 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 139 |
| 2 | 8 | executing | 0.000009 | 0.000004 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 110 |
| 2 | 9 | end | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_select | sql_select.cc | 1106 |
| 2 | 10 | query end | 0.000003 | 0.000001 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5049 |
| 2 | 11 | closing tables | 0.000008 | 0.000003 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5097 |
| 2 | 12 | freeing items | 0.000020 | 0.000010 | 0.000011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 6486 |
| 2 | 13 | cleaning up | 0.000013 | 0.000005 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1815 |
+----------+-----+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+------------------+-------------+
information_schema.profiling表主要字段含义
-
state : 当前
query所在的阶段 - CPU_user : CPU用户
- CPU_system : CPU系统
- Context_voluntary : 上下文主动切换
- Context_involuntary : 上下文被动切换
- Block_ops_in : 阻塞的输入操作
- Block_ops_out : 阻塞的输出操作
- Messages_sent : 消息发出
- Messages_received : 消息接受
- Page_faults_major : 主分页错误
- Page_faults_minor : 次分页错误
- Swaps : 交换次数
- Source_function : 源功能
- Source_file : 源文件
- Source_line : 源代码行
网友评论