MySQL SQL Tuning:Profile定位单条QUERY性能瓶颈

标签: mysql sql tuning | 发表时间:2013-10-03 10:01 | 作者:linwaterbin
出处:http://blog.csdn.net
当生了病的query来就诊时,如果身为医生的我们"胡庸医乱用虎狼药"
不仅于事无补,还浪费了大量的人力和时间成本,甚至会拖垮服务器
所以,我们在接受优化一条SQL,第一件事情便是要明白query病在哪里?
是IO?是CPU?只有明白瓶颈在哪里,方可对症下药,也才能药到病除
而MySQL QUERY Profiler是一个使用非常方便的QUERY诊断工具,5.0引入。5.1GA版嵌入
这个工具详细呈现了SQL在整个生命周期的每个动作,这和Oracle开启1046事件类似
我们可以很清晰地明白该SQL是在数据存取还是运算(排序或分组等)上花费得多
那么我们就不会很盲目地看到order by就去tuning sort buffer而忽略sorting result时间是如此之少

Profile语法:

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS

注解:
默认输出结果只会展示Status和Duration,我们可以指定type来扩展输出
我比较常用的是外加CPU和BLOCK IO来输出CPU和IO的负载,其实这些已经够了

默认profile是关闭的,通过profiling参数控制,为session级
开启:SET profiling=1
关闭:set profiling=0
查询:select @@profiling 
show profiles保存的query条数由参数profiling_history_size控制,默认是15,超过了会把前面的剔掉

mysql> set profiling=1;

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+

mysql> select * from employees.t order by first_name;

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        1 | 0.21138800 | show create table employees.t                 |
|        2 | 8.21691600 | select * from employees.t order by first_name |
+----------+------------+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000160 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000055 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000033 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000050 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000145 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000118 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| Sorting result       | 2.838465 | 1.396087 |   1.140071 |            0 |             0 |
| Sending data         | 0.928078 | 0.544034 |   0.056003 |            0 |             0 |
| end                  | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 4.449672 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query   | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
17 rows in set (0.00 sec)

相同的信息我们还可以从information_schema里面输出,并且我们还可以对时间进行排序
因为Profile默认是按执行顺序排序的,而实际上我们更关心的是花费了多少时间,这才能方便知道哪些开销较大

mysql> SELECT STATE, FORMAT(DURATION, 6) AS DURATION
    -> FROM INFORMATION_SCHEMA.PROFILING
    -> WHERE QUERY_ID = 2 ORDER BY DURATION DESC;
+----------------------+----------+
| STATE                | DURATION |
+----------------------+----------+
| freeing items        | 4.449672 |
| Sorting result       | 2.838465 |
| Sending data         | 0.928078 |
| starting             | 0.000160 |
| statistics           | 0.000145 |
| preparing            | 0.000118 |
| Opening tables       | 0.000055 |
| init                 | 0.000050 |
| System lock          | 0.000033 |
| end                  | 0.000026 |
| optimizing           | 0.000026 |
| checking permissions | 0.000026 |
| closing tables       | 0.000021 |
| logging slow query   | 0.000014 |
| query end            | 0.000011 |
| executing            | 0.000011 |
| cleaning up          | 0.000005 |
+----------------------+----------+
17 rows in set (0.00 sec)

参考文章:
http://dev.mysql.com/doc/refman/5.5/en/show-profile.html


By 迦夜
2013-10-3
Good Luck

作者:linwaterbin 发表于2013-10-3 2:01:48 原文链接
阅读:227 评论:3 查看评论

相关 [mysql sql tuning] 推荐:

MySQL SQL Tuning:深入理解Order By

- - CSDN博客数据库推荐文章
在MySQL中ORDER BY按先后顺序有2种实现方式,先走索引无排序,如果不行,则用FILESORT. 走索引无排序需要满足2个条件:. ①排序字段和执行计划中所利用INDEX的索引键(或前面几个索引键)完全一致. ②表访问方式为index、ref或range [注释:explain输出中的Type可看出].

使用ORACLE SQL Tuning advisor快速优化低效的SQL语句

- - CSDN博客数据库推荐文章
ORACLE10G以后版本的SQL Tuning advisor可以从以下四个方面给出优化方案.   (1)为统计信息丢失或失效的对象收集统计信息.   (2)考虑优化器的任何数据偏差、复杂谓词或失效的统计信息.   (3)重新构建 SQL 以优化性能.   (4)提出新索引建议. 1、为SQL_id创建一个STA(SQL Tuning advisor) 分析任务(使用SYS用户执行).

MySql动态SQL

- - SQL - 编程语言 - ITeye博客
13.7. 用于预处理语句的SQL语法. MySQL 5.1对服务器一方的预制语句提供支持. 如果您使用合适的客户端编程界面,则这种支持可以发挥在MySQL 4.1中实施的高效客户端/服务器二进制协议的优势. 候选界面包括MySQL C API客户端库(用于C程序)、MySQL Connector/J(用于Java程序)和MySQL Connector/NET.

ORACLE SQL TUNING各种技巧及复杂实例

- - 数据库 - ITeye博客
ORACLE的优化器共有3种:. CHOOSE (选择性). 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须定期更新统计信息,以保证数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关.

mysql记录耗时的sql

- - 数据库 - ITeye博客
mysql记录耗时的sql. mysql可以把耗时的sql或未使用索引的sql都记录在slow log里,供优化分析使用. 1.mysql慢查询日志启用:. mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句,那如何打开mysql的慢查询日志记录呢. 这说明slow log功能没有启用,要启用需要修改mysql的配置文件,在配置文件"[mysqld]"里添加如下俩参数:.

全国省市县 SQL (mysql)

- - 学习笔记
此城市区域内容可以配合ip.taobao.com api 使用.. 有些字段没有添加索引,用的时候根据实际情况做修改. 使用mysql来实现lbs(地理位置服务)功能. 10款对开发者有帮助的Android应用. DeployPHP 系列第 1 部分:优化 PHP 和 Oracle. 在 Oracle 和 PHP 中使用 LOB.

mysql的sql优化(前奏)

- - 数据库 - ITeye博客
要优化mysql首先要知道什么地方需要优化,然后才能针对具体问题进行优化. 什么分库分表,建立索引....摆脱不要那么官方好吗. 1.学会和培养使用mysql的查看命令的使用习惯. 什么你忘记如何创建表的语句了. SHOW CREATE TABLE Name: 'SHOW CREATE TABLE' Description: Syntax: SHOW CREATE TABLE tbl_name Shows the CREATE TABLE statement that creates the named table.

【ActiveMQ Tuning】Prefetch Limit

- - 博客园_首页
   摘要:ActiveMQ优化 客户端优化 预取限制. 原文: http://fusesource.com/docs/broker/5.4/tuning/GenTuning-Consumer-Prefetch.html. Overview:图列4.1阐明了Broker在等待之前发送给客户端消息的反馈的行为.

【ActiveMQ Tuning】Serializing to Disk

- - 博客园_首页
     翻译自: http://fusesource.com/docs/broker/5.4/tuning/PersTuning-SerialToDisk.html.      KahaDB message store:KahaDB 是ActiveMQ Broker 为了高性能而推荐使用的消息存储机制.

Mysql查看sql是否走事务

- - CSDN博客数据库推荐文章
可以看到监控日志是否开启的选项(off是关闭)on是开启. 接下来就可以直接进入shell中根据sql特征来查看我们sql会话信息. 126002192 Query select pk fromT_GANTT_CHART where pk =47 for update

//忽略其他的数据了.