通过profile分析一条sql 查看sql性能
1:开启
SET profiling = 1;
2 : 编写sql
SELECT * FROM sjt_doctor WHERE mobile =11111111111;
3:查看sql的语句分析
SHOW PROFILES;
4:查看sql1的具体分析
模板:SHOW PROFILE (type)FOR QUERY id;
SHOW PROFILE ALL FOR QUERY 142;
SHOW PROFILE CPU,BLOCK IO FOR QUERY 163;
------------------------
SET profiling = 1;
SELECT * FROM sjt_doctor WHERE mobile =11111111111;
SHOW PROFILES;
-- show profile all for query 163;
SHOW PROFILE CPU,BLOCK IO FOR QUERY 163;
上图中横向栏意义
+----------------------+----------+----------+------------+
"Status": "query end", 状态
"Duration": "1.751142", 持续时间
"CPU_user": "0.008999", cpu用户
"CPU_system": "0.003999", cpu系统
"Block_ops_in": "8", 阻塞的输入操作
"Block_ops_out": "32", 阻塞的输出操作
+----------------------+----------+----------+------------+
上图中纵向栏意义
+----------------------+----------+----------+------------+
starting:开始
checking permissions:检查权限
Opening tables:打开表
init : 初始化
System lock :系统锁
optimizing : 优化
statistics : 统计
preparing :准备
executing :执行
Sending data :发送数据
Sorting result :排序
end :结束
query end :查询 结束
closing tables : 关闭表 /去除TMP 表
freeing items : 释放物品
cleaning up :清理
+----------------------+----------+----------+------------+
另外附上profiling 的学习质料
profiling 基础
http://www.tuicool.com/articles/ZFrUzia
http://www.xaprb.com/blog/2006/10/12/how-to-profile-a-query-in-mysql/
http://www.xaprb.com/blog/2006/10/15/a-case-study-in-profiling-queries-in-mysql/
http://www.xaprb.com/blog/2006/10/17/mysql-profiling-case-study-part-2/
profiling 进阶
http://www.mysqlperformanceblog.com/2009/01/19/profiling-mysql-stored-routines/
http://www.mysqlperformanceblog.com/2008/05/18/wanted-better-memory-profiling-for-mysql/maatkit–perldoc mk-query-profiler
profiling高级
http://dev.mysql.com/tech-resources/articles/pro-mysql-ch6.html
http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html
profiling骨灰级
http://www.scribd.com/doc/2669413/DTrace-and-MySQL-Presentation
http://forge.mysql.com/wiki/Using_DTrace_with_MySQL
http://wikis.sun.com/display/BluePrints/Optimizing+MySQL+Database+Application+Performan
http://blog.csdn.net/lihuayong/article/details/42044593
————————————————