MYSQL查询优化:profile功能
来源:互联网 发布:菜鸟网络嘉定 编辑:程序博客网 时间:2024/04/28 02:54
这里还需要注意一点就是,需要安装profile模块才能实现。
1、不过版本要在5.0.37之后。(SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37.)
SELECT @@profiling;
来查看是否已经启用profile,如果profilng值为0,可以通过
SET profiling = 1;
来启用。启用profiling之后,我们执行一条查询语句,比如:
select count(*) from roi_summary;
然后show profiles查看如下:
+----------------+------------+----------------------------------+| Query_ID | Duration | Query |+----------------+------------+----------------------------------+| 1 | 0.00021500 | select @@profiling || 2 | 0.05522700 | select count(*) from roi_summary |+----------------+------------+----------------------------------+
2 rows in set (0.00 sec)
其中ID为5的语句是刚执行的查询语句
2、变量profiling是用户变量,每次都得重新启用。
以下是我做的一些实验。数据很明显,就不多解释了。
mysql> use testDatabase changedmysql> set profiling=1;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+----------------+| Tables_in_test |+----------------+| bag_item | | bag_user | | score | | t | +----------------+4 rows in set (0.03 sec)mysql> select count(*) from t;+----------+| count(*) |+----------+| 2097152 | +----------+1 row in set (0.74 sec)mysql> show profiles;+----------+------------+------------------------+| Query_ID | Duration | Query |+----------+------------+------------------------+| 1 | 0.02717000 | show tables | | 2 | 0.74770100 | select count(*) from t | +----------+------------+------------------------+2 rows in set (0.00 sec)mysql> show profile for query 2;+--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| (initialization) | 0.000004 | | checking query cache for query | 0.000044 | | Opening tables | 0.000012 | | System lock | 0.000017 | | Table lock | 0.00003 | | init | 0.000013 | | optimizing | 0.000008 | | statistics | 0.000013 | | preparing | 0.000011 | | executing | 0.000006 | | Sending data | 0.747313 | | end | 0.000014 | | query end | 0.000006 | | storing result in query cache | 0.000006 | | freeing items | 0.000012 | | closing tables | 0.000009 | | logging slow query | 0.000183 | +--------------------------------+----------+17 rows in set (0.00 sec)mysql> show profile block io,cpu for query 2;+--------------------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+--------------------------------+----------+----------+------------+--------------+---------------+| (initialization) | 0.000004 | 0 | 0 | 0 | 0 | | checking query cache for query | 0.000044 | 0 | 0 | 0 | 0 | | Opening tables | 0.000012 | 0 | 0 | 0 | 0 | | System lock | 0.000017 | 0 | 0 | 0 | 0 | | Table lock | 0.00003 | 0 | 0 | 0 | 0 | | init | 0.000013 | 0 | 0 | 0 | 0 | | optimizing | 0.000008 | 0 | 0 | 0 | 0 | | statistics | 0.000013 | 0 | 0 | 0 | 0 | | preparing | 0.000011 | 0 | 0 | 0 | 0 | | executing | 0.000006 | 0 | 0 | 0 | 0 | | Sending data | 0.747313 | 0.746887 | 0 | 0 | 0 | | end | 0.000014 | 0 | 0 | 0 | 0 | | query end | 0.000006 | 0 | 0 | 0 | 0 | | storing result in query cache | 0.000006 | 0 | 0 | 0 | 0 | | freeing items | 0.000012 | 0 | 0 | 0 | 0 | | closing tables | 0.000009 | 0 | 0 | 0 | 0 | | logging slow query | 0.000183 | 0 | 0 | 0 | 0 | +--------------------------------+----------+----------+------------+--------------+---------------+17 rows in set (0.00 sec)mysql> insert into t(username) select username from t;Query OK, 2097152 rows affected (34.17 sec)Records: 2097152 Duplicates: 0 Warnings: 0mysql> show profiles;+----------+-------------+------------------------------------------------+| Query_ID | Duration | Query |+----------+-------------+------------------------------------------------+| 1 | 0.02717000 | show tables | | 2 | 0.74770100 | select count(*) from t | | 3 | 0.00004200 | show prifile for query 2 | | 4 | 34.30410100 | insert into t(username) select username from t | +----------+-------------+------------------------------------------------+4 rows in set (0.00 sec)mysql> show profile cpu,block io,memory,swaps for query 4;mysql> select count(*) from t;+----------+| count(*) |+----------+| 4194304 | +----------+1 row in set (1.51 sec)mysql> show profiles;+----------+-------------+------------------------------------------------+| Query_ID | Duration | Query |+----------+-------------+------------------------------------------------+| 1 | 0.02717000 | show tables | | 2 | 0.74770100 | select count(*) from t | | 3 | 0.00004200 | show prifile for query 2 | | 4 | 34.30410100 | insert into t(username) select username from t | | 5 | 1.50563800 | select count(*) from t | +----------+-------------+------------------------------------------------+5 rows in set (0.00 sec)mysql> show profile cpu,block io,memory,swaps,context switches,source for query 5;……mysql> update t set username = 'waill';Query OK, 4194304 rows affected (44.82 sec)Rows matched: 4194304 Changed: 4194304 Warnings: 0mysql> show profiles;+----------+-------------+------------------------------------------------+| Query_ID | Duration | Query |+----------+-------------+------------------------------------------------+| 1 | 0.02717000 | show tables | | 2 | 0.74770100 | select count(*) from t | | 3 | 0.00004200 | show prifile for query 2 | | 4 | 34.30410100 | insert into t(username) select username from t | | 5 | 1.50563800 | select count(*) from t | | 6 | 44.82054700 | update t set username = 'waill' | +----------+-------------+------------------------------------------------+6 rows in set (0.00 sec)mysql> show profile cpu,block io,memory,swaps,context switches,source for query 6;
转自:http://hi.baidu.com/chssheng2007/blog/item/a97888b3e8ac0ebfd9335aae.html
SHOW PROFILE语法:
SHOW PROFILE [type
[,type
] ... ] [FOR QUERYn
] [LIMITrow_count
[OFFSEToffset
]]type
: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS
摘自:http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
0 0
- MYSQL查询优化:profile功能
- MYSQL查询优化:profile功能
- MYSQL查询优化:profile功能
- MYSQL查询优化:profile功能
- MYSQL查询优化:profile功能
- MYSQL查询优化:profile功能
- MySQL查询优化之profile
- mysql profile explain slow_query_log分析优化查询
- 慢查询、explain profile,mysql优化
- 利用 index、explain和profile优化mysql数据库查询小结
- mysql profile功能
- mysql profile功能
- mysql profile功能
- Mysql profile分析查询
- mysql 优化之 profile
- Mysql 优化 Profile命令
- 优化mysql实验(explain;索引)+利用 index、explain和profile优化mysql数据库查询小结
- mysql 5.0中的profile功能
- android应用中去掉标题栏的方法
- SQLite 清空表并将自增列归零
- iOS_33_音乐播放(后台播放+锁屏歌词)
- 如何赶超竞争对手排名
- 像黑客一样使用Linux
- MYSQL查询优化:profile功能
- sql优化 方法
- dlopen参数总结
- xml简介
- freemarker 标签使用 (实例)
- 那些牛叉无比的评审风格,你,属于哪一种?
- 使用gcd_wait
- Flex的mxml文件中ASCII码的使用
- php笔记