MySQL使用profile分析SQL执行状态
来源:互联网 发布:全知视角和有限视角 编辑:程序博客网 时间:2024/05/17 04:53
打开profile
- mysql> select @@have_profiling;
- +------------------+
- | @@have_profiling |
- +------------------+
- | YES |
- +------------------+
- 1 row in set (0.00 sec)
- mysql> select @@profiling;
- +-------------+
- | @@profiling |
- +-------------+
- | 0 |
- +-------------+
- 1 row in set (0.00 sec)
- mysql> set session profiling=1;
- Query OK, 0 rows affected (0.00 sec)
- mysql> select @@profiling;
- +-------------+
- | @@profiling |
- +-------------+
- | 1 |
- +-------------+
- 1 row in set (0.00 sec)
使用profile分析SQL,可以看到执行两次后,Send data和sending cached result to clien执行效率的变化
- mysql> select count(*) from sakila.payment;
- +----------+
- | count(*) |
- +----------+
- | 16049 |
- +----------+
- 1 row in set (0.03 sec)
- mysql> show profiles;
- +----------+------------+-------------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+-------------------------------------+
- | 1 | 0.00020400 | select @@profiling |
- | 2 | 0.00008900 | select count(*) from payment |
- | 3 | 0.00006800 | show databaes |
- | 4 | 0.02102800 | show databases |
- | 5 | 0.02847600 | select count(*) from sakila.payment |
- +----------+------------+-------------------------------------+
- 5 rows in set (0.00 sec)
- mysql> show profile for query 5;
- +--------------------------------+----------+
- | Status | Duration |
- +--------------------------------+----------+
- | starting | 0.000030 |
- | Waiting for query cache lock | 0.000005 |
- | checking query cache for query | 0.000043 |
- | checking permissions | 0.000007 |
- | Opening tables | 0.000027 |
- | System lock | 0.000010 |
- | Waiting for query cache lock | 0.000010 |
- | init | 0.000000 |
- | optimizing | 0.023255 |
- | statistics | 0.000118 |
- | preparing | 0.000041 |
- | executing | 0.000033 |
- | Sending data | 0.003833 |
- | end | 0.000054 |
- | query end | 0.000045 |
- | closing tables | 0.000045 |
- | freeing items | 0.000072 |
- | Waiting for query cache lock | 0.000033 |
- | freeing items | 0.000785 |
- | Waiting for query cache lock | 0.000016 |
- | freeing items | 0.000002 |
- | storing result in query cache | 0.000005 |
- | logging slow query | 0.000003 |
- | cleaning up | 0.000004 |
- +--------------------------------+----------+
- 24 rows in set (0.00 sec)
- mysql> select count(*) from sakila.payment;
- +----------+
- | count(*) |
- +----------+
- | 16049 |
- +----------+
- 1 row in set (0.00 sec)
- mysql> show profiles;
- +----------+------------+-------------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+-------------------------------------+
- | 1 | 0.00020400 | select @@profiling |
- | 2 | 0.00008900 | select count(*) from payment |
- | 3 | 0.00006800 | show databaes |
- | 4 | 0.02102800 | show databases |
- | 5 | 0.02847600 | select count(*) from sakila.payment |
- | 6 | 0.00006900 | select count(*) from sakila.payment |
- +----------+------------+-------------------------------------+
- 6 rows in set (0.00 sec)
- mysql> show profile for query 6;
- +--------------------------------+----------+
- | Status | Duration |
- +--------------------------------+----------+
- | starting | 0.000029 |
- | Waiting for query cache lock | 0.000004 |
- | checking query cache for query | 0.000007 |
- | checking privileges on cached | 0.000004 |
- | checking permissions | 0.000008 |
- | sending cached result to clien | 0.000012 |
- | logging slow query | 0.000002 |
- | cleaning up | 0.000003 |
- +--------------------------------+----------+
- 8 rows in set (0.00 sec)
sending data比较耗费cpu
- mysql> select min(seq) seq,state,count(*) numb_ops,
- -> round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur,
- -> round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu
- -> from information_schema.profiling
- -> where query_id = 5
- -> group by state
- -> order by seq;
- +------+--------------------------------+----------+---------+---------+---------+---------+
- | seq | state | numb_ops | sum_dur | avg_dur | sum_cpu | avg_cpu |
- +------+--------------------------------+----------+---------+---------+---------+---------+
- | 2 | starting | 1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |
- | 3 | Waiting for query cache lock | 4 | 0.00006 | 0.00002 | 0.00100 | 0.00025 |
- | 4 | checking query cache for query | 1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |
- | 5 | checking permissions | 1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
- | 6 | Opening tables | 1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |
- | 7 | System lock | 1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
- | 9 | init | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
- | 10 | optimizing | 1 | 0.02326 | 0.02326 | 0.00000 | 0.00000 |
- | 11 | statistics | 1 | 0.00012 | 0.00012 | 0.00000 | 0.00000 |
- | 12 | preparing | 1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |
- | 13 | executing | 1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |
- | 14 | Sending data | 1 | 0.00383 | 0.00383 | 0.00300 | 0.00300 |
- | 15 | end | 1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |
- | 16 | query end | 1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |
- | 17 | closing tables | 1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |
- | 18 | freeing items | 3 | 0.00086 | 0.00029 | 0.00000 | 0.00000 |
- | 23 | storing result in query cache | 1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
- | 24 | logging slow query | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
- | 25 | cleaning up | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
- +------+--------------------------------+----------+---------+---------+---------+---------+
- 19 rows in set (0.00 sec)
- mysql> show profile cpu for query 5;
- +--------------------------------+----------+----------+------------+
- | Status | Duration | CPU_user | CPU_system |
- +--------------------------------+----------+----------+------------+
- | starting | 0.000030 | 0.000000 | 0.000000 |
- | Waiting for query cache lock | 0.000005 | 0.000000 | 0.000000 |
- | checking query cache for query | 0.000043 | 0.000000 | 0.000000 |
- | checking permissions | 0.000007 | 0.000000 | 0.000000 |
- | Opening tables | 0.000027 | 0.000000 | 0.000000 |
- | System lock | 0.000010 | 0.000000 | 0.000000 |
- | Waiting for query cache lock | 0.000010 | 0.001000 | 0.000000 |
- | init | 0.000000 | 0.000000 | 0.000000 |
- | optimizing | 0.023255 | 0.000000 | 0.000000 |
- | statistics | 0.000118 | 0.000000 | 0.000000 |
- | preparing | 0.000041 | 0.000000 | 0.000000 |
- | executing | 0.000033 | 0.000000 | 0.000000 |
- | Sending data | 0.003833 | 0.002999 | 0.001000 |
- | end | 0.000054 | 0.000000 | 0.000000 |
- | query end | 0.000045 | 0.000000 | 0.000000 |
- | closing tables | 0.000045 | 0.000000 | 0.000000 |
- | freeing items | 0.000072 | 0.000000 | 0.000000 |
- | Waiting for query cache lock | 0.000033 | 0.000000 | 0.000000 |
- | freeing items | 0.000785 | 0.000000 | 0.000000 |
- | Waiting for query cache lock | 0.000016 | 0.000000 | 0.000000 |
- | freeing items | 0.000002 | 0.000000 | 0.000000 |
- | storing result in query cache | 0.000005 | 0.000000 | 0.000000 |
- | logging slow query | 0.000003 | 0.000000 | 0.000000 |
- | cleaning up | 0.000004 | 0.000000 | 0.000000 |
- +--------------------------------+----------+----------+------------+
- 24 rows in set (0.00 sec)
- mysql> show profile block io for query 5;
- +--------------------------------+----------+--------------+---------------+
- | Status | Duration | Block_ops_in | Block_ops_out |
- +--------------------------------+----------+--------------+---------------+
- | starting | 0.000030 | 0 | 0 |
- | Waiting for query cache lock | 0.000005 | 0 | 0 |
- | checking query cache for query | 0.000043 | 0 | 0 |
- | checking permissions | 0.000007 | 0 | 0 |
- | Opening tables | 0.000027 | 0 | 0 |
- | System lock | 0.000010 | 0 | 0 |
- | Waiting for query cache lock | 0.000010 | 0 | 0 |
- | init | 0.000000 | 0 | 0 |
- | optimizing | 0.023255 | 0 | 0 |
- | statistics | 0.000118 | 0 | 0 |
- | preparing | 0.000041 | 0 | 0 |
- | executing | 0.000033 | 0 | 0 |
- | Sending data | 0.003833 | 0 | 0 |
- | end | 0.000054 | 0 | 0 |
- | query end | 0.000045 | 0 | 0 |
- | closing tables | 0.000045 | 0 | 0 |
- | freeing items | 0.000072 | 0 | 0 |
- | Waiting for query cache lock | 0.000033 | 0 | 0 |
- | freeing items | 0.000785 | 0 | 0 |
- | Waiting for query cache lock | 0.000016 | 0 | 0 |
- | freeing items | 0.000002 | 0 | 0 |
- | storing result in query cache | 0.000005 | 0 | 0 |
- | logging slow query | 0.000003 | 0 | 0 |
- | cleaning up | 0.000004 | 0 | 0 |
- +--------------------------------+----------+--------------+---------------+
- 24 rows in set (0.00 sec)
再看同结构数据的MyISAM表
- mysql> create table payment_myisam like payment;
- Query OK, 0 rows affected (0.03 sec)
- mysql> alter table payment_myisam engine=myisam;
- Query OK, 0 rows affected (0.03 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> show create table payment_myisam\G
- *************************** 1. row ***************************
- Table: payment_myisam
- Create Table: CREATE TABLE `payment_myisam` (
- `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
- `customer_id` smallint(5) unsigned NOT NULL,
- `staff_id` tinyint(3) unsigned NOT NULL,
- `rental_id` int(11) DEFAULT NULL,
- `amount` decimal(5,2) NOT NULL,
- `payment_date` datetime NOT NULL,
- `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
- PRIMARY KEY (`payment_id`),
- KEY `idx_fk_staff_id` (`staff_id`),
- KEY `idx_fk_customer_id` (`customer_id`),
- KEY `fk_payment_rental` (`rental_id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
- mysql> insert into payment_myisam select * from payment;
- Query OK, 16049 rows affected (0.10 sec)
- Records: 16049 Duplicates: 0 Warnings: 0
- mysql> select count(*) from payment_myisam;
- +----------+
- | count(*) |
- +----------+
- | 16049 |
- +----------+
- 1 row in set (0.01 sec)
- mysql> show profiles;
- | 23 | 0.09449600 | insert into payment_myisam select * from payment |
- | 24 | 0.00023500 | select count(*) from payment_myisam |
- 15 rows in set (0.00 sec)
- mysql> show profile for query 24;
- +--------------------------------+----------+
- | Status | Duration |
- +--------------------------------+----------+
- | starting | 0.000026 |
- | Waiting for query cache lock | 0.000003 |
- | checking query cache for query | 0.000037 |
- | checking permissions | 0.000006 |
- | Opening tables | 0.000015 |
- | System lock | 0.000009 |
- | Waiting for query cache lock | 0.000019 |
- | init | 0.000011 |
- | optimizing | 0.000006 |
- | executing | 0.000006 |
- | end | 0.000000 |
- | query end | 0.000001 |
- | closing tables | 0.000008 |
- | freeing items | 0.000019 |
- | Waiting for query cache lock | 0.000008 |
- | freeing items | 0.000013 |
- | Waiting for query cache lock | 0.000008 |
- | freeing items | 0.000008 |
- | storing result in query cache | 0.000015 |
- | logging slow query | 0.000008 |
- | cleaning up | 0.000009 |
- +--------------------------------+----------+
可以看到在count操作MyISAM表时没有sending data这个状态,和Innodb不同,MyISAM不需要访问表数据,所以count操作比Innodb快。
原文链接:http://blog.csdn.net/staricqxyz/article/details/18943001
阅读全文
0 0
- MySQL使用profile分析SQL执行状态
- MySQL使用profile分析SQL执行状态
- 查看mysql正在执行的SQL语句,使用profile分析SQL执行状态
- MySql 使用SHOW PROFILE 进行SQL 分析
- MySql 使用SHOW PROFILE 进行SQL 分析
- MySQL中使用explain和profile分析SQL执行计划和性能
- MySQL执行状态分析
- MySQL执行状态分析
- MySQL执行状态分析
- 使用oracle sql profile固定执行计划
- MySQL性能分析工具profile使用教程
- mysql使用profile分析语句性能消耗
- MySQL性能分析工具profile使用教程
- 使用SQL Profile及SQL Tuning Advisor固定执行计划
- 使用SQL Profile稳定SQL语句的执行计划
- MySQL 使用profile分析慢sql,group left join效率高于子查询
- Mysql 分析sql 执行效率
- mysql 执行状态分析 show processlist
- shiro--简单入门
- 多线程-线程池的概述和使用
- 人工智能:是“哆啦A梦”还是“终结者”
- pat1021-1030
- ROS indigo+Ubuntu14.04
- MySQL使用profile分析SQL执行状态
- C语言inline函数
- HTML中关于justify(text-align)属性无效果解决讲解
- 整数数组中找出只出现了一次的数
- angularjs中方法执行的一个小tip
- 理解null值和可空类型
- js 中offsetTop、offsetLeft、offsetWidth、offsetHeight详解
- caffe深度学习网络(.prototxt)在线可视化工具:Netscope Editor
- 设计模式--工厂模式