Mysql 慢查询之showprofile
show profiles:返回服务器上最近执行的语句 资源的使用情况。
一、使用准备
Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。
mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.36 | +-----------+
profile 功能默认是关闭的 通过以下命令查看 ，开启
mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ mysql> show variables like '%profiling%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | | profiling | OFF | | profiling_history_size | 15 | +------------------------+-------+
mysql> set global profiling = 1;
mysql> set global profiling_history_size = 10;
其中 profiling_history_size为保存多少条最近记录。
重开会话后显示生效。
二、具体语法
查看记录中的语句
mysql> show profiles; +----------+------------+---------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------------+ | 1 | 0.00015600 | select @@version_comment limit 1 | | 2 | 0.00056675 | show variables like '%profiling%' | | 3 | 0.00007050 | select * from b limit 10000 | | 4 | 0.00014075 | SELECT DATABASE() | | 5 | 0.00592575 | select * from b limit 10000 | | 6 | 0.06692075 | select * from b group by id%10 limit 10000 | | 7 | 0.06478225 | select * from b group by id%10 limit 100000 | +----------+------------+---------------------------------------------+
查看表中 具体记录的详细执行信息
show profile [参数[,参数...]] for query [上面的Query_ID]
mysql> show profile cpu,block io for query 7; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000060 | 0.000000 | 0.000000 | NULL | NULL | | checking permissions | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | | Opening tables | 0.000026 | 0.000000 | 0.000000 | NULL | NULL | | System lock | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | | init | 0.000013 | 0.000000 | 0.000000 | NULL | NULL | | optimizing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | statistics | 0.000014 | 0.000000 | 0.000000 | NULL | NULL | | preparing | 0.000005 | 0.000000 | 0.000000 | NULL | NULL | | Creating tmp table | 0.000288 | 0.000000 | 0.000000 | NULL | NULL | | executing | 0.000003 | 0.000000 | 0.000000 | NULL | NULL | | Copying to tmp table | 0.064244 | 0.062500 | 0.000000 | NULL | NULL | | Sorting result | 0.000021 | 0.000000 | 0.000000 | NULL | NULL | | Sending data | 0.000011 | 0.000000 | 0.000000 | NULL | NULL | | end | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | | removing tmp table | 0.000006 | 0.000000 | 0.000000 | NULL | NULL | | end | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | | query end | 0.000002 | 0.000000 | 0.000000 | NULL | NULL | | closing tables | 0.000004 | 0.000000 | 0.000000 | NULL | NULL | | freeing items | 0.000046 | 0.000000 | 0.000000 | NULL | NULL | | logging slow query | 0.000023 | 0.000000 | 0.000000 | NULL | NULL | | cleaning up | 0.000001 | 0.000000 | 0.000000 | NULL | NULL | +----------------------+----------+----------+------------+--------------+---------------+
可选参数：
- ALL 显示所有信息
- BLOCK IO 块设备IO输入输出次数
- CONTEXT SWITCHES 上下文切换的相关开销
- CPU 用户和系统的CPU使用情况
- IPC 发送和接收消息的相关消耗
- MEMEORY 内存相关消耗
- PAGE FAULTS 主要和次要页面故障的开销
- SOURCE source_function，source_file 等相关开销
- SWAPS 交换次数开销
表中遇到Status 需要注意执行时间
- converting HEAP to MySIAM 数据过大MyISAM内存装不下，向磁盘上搬运
- Creating tmp table 临时表创建
- Copying to tmp table on disk 复制临时表到磁盘
- locked 锁。阻塞
