内容简介：MySQL优化之profile
分析 SQL 执行带来的开销是优化SQL的重要手段。在 MySQL 数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。
注意：MySQL5.0.37版本以上支持PROFILING调试功能，但是因为它需要调用系统的getrusage函数（用于获取进程资源使用量），所以只是在Linux/Unix类平台上才能使用，并且PROFILING是针对处理进程(process)而不是线程(thread)的；服务器上的其他应用，比如说服务器负载不同，MySQL的响应速度、IO效率等可能不同，从而导致PROFILING结果不同（即便是对同一条SQL语句），因此，这个 工具 适合开发过程中的调试，如果要在生产环境中调试使用，则要注意它的局限性。
1、获取你的MySQL服务器版本
[shusheng@localhost ~]$ mysql --version mysql Ver 14.14 Distrib 5.1.60, for redhat-linux-gnu (i386) using readline 5.1
2、查看profiling系统变量
mysql> show variables like '%profil%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | have_profiling | YES | --只读变量，用于控制是否由系统变量开启或禁用profiling（我还不确定这项是否只出现特定版本或者需要设置） | profiling | OFF | --开启SQL语句剖析功能（开启之后应为ON） | profiling_history_size | 15 | --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling +------------------------+-------+
3、来查看是否已经启用profile
mysql> select @@PROFILING;
+-------------+
| @@PROFILING |
+-------------+
| 0 |
+-------------+
1 row in set (0.03 sec)
如果profiling值为0，可以通过
SET profiling = 1;
来启用。启用profiling之后，我们执行一条查询语句，比如：
mysql> SELECT * FROM mysql.user;
然后show profiles查看如下：
mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------+
| 1 | 0.00015400 | select @@PROFILING |
| 2 | 0.00160000 | SELECT * FROM mysql.user |
+----------+------------+--------------------------------+
2 rows in set (0.00 sec)
其中ID为2的语句是刚执行的查询语句，profiles文件只会记录当前session并且profile打开之后的SQL语句。
4、获取profile的帮助（常见的参数选项）；
mysql> help profile; Name: 'SHOW PROFILE' Description: Syntax: SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: | ALL --显示所有的开销信息 | BLOCK IO --显示块IO相关开销 | CONTEXT SWITCHES --上下文切换相关开销 | CPU --显示CPU相关开销信息 | IPC --显示发送和接收相关开销信息 | MEMORY --显示内存相关开销信息 | PAGE FAULTS --显示页面错误相关开销信息 | SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息 | SWAPS --显示交换次数相关开销的信息
注意：上面的的命令help命令，在你的系统里显示出来的结果不一定和上面相同，可能是版本的原因，不必深究。
5、利用profile获取SQL语句的开销信息
注意：
（1）show profile之类的语句不会被profiling，即自身不会产生Profiling
（2）变量profiling是用户变量，每次都得重新启用（session退出之后,profiling会被自动关闭 ）。
下面是profile的常用功能：
登陆到数据库（schema）
mysql> use test Database changed
开启profile mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec)
现在开始执行需要测试的SQL语句，MySQL数据库将会记录想关的调试信息
mysql> SHOW TABLES;
+----------------------+
| Tables_in_test |
+----------------------+
| account |
| branch |
+----------------------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM account;
+----------+
| COUNT(*) |
+----------+
| 24 |
+----------+
1 row in set (0.00 sec)
显示当前所有已经记录的PROFILES：
mysql> SHOW PROFILES;
+----------+------------+------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------+
| 1 | 0.00100800 | SHOW TABLES |
| 2 | 0.00021700 | SELECT COUNT(*) FROM account |
+----------+------------+------------------------------+
2 rows in set (0.00 sec)
获取指定查询的开销，对应上面profiles中的Query_ID：
mysql> SHOW PROFILE FOR QUERY 2; +--------------------+----------+ | Status | Duration | +--------------------+----------+ | starting | 0.000078 | | Opening tables | 0.000031 | | System lock | 0.000015 | | Table lock | 0.000010 | | init | 0.000012 | | optimizing | 0.000019 | | executing | 0.000022 | | end | 0.000006 | | query end | 0.000003 | | freeing items | 0.000014 | | logging slow query | 0.000003 | | cleaning up | 0.000004 | +--------------------+----------+ 12 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 | +--------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000078 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | end | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | +--------------------+----------+----------+------------+--------------+---------------+ 12 rows in set (0.00 sec)
下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列
mysql> set @query_id=2;
mysql> SELECT STATE, SUM(DURATION) AS Total_R,
-> ROUND(
-> 100 * SUM(DURATION) /
-> (SELECT SUM(DURATION)
-> FROM INFORMATION_SCHEMA.PROFILING
-> WHERE QUERY_ID = @query_id
-> ), 2) AS Pct_R,
-> COUNT(*) AS Calls,
-> SUM(DURATION) / COUNT(*) AS "R/Call"
-> FROM INFORMATION_SCHEMA.PROFILING
-> WHERE QUERY_ID = @query_id
-> GROUP BY STATE
-> ORDER BY Total_R DESC;
结果如下：
+--------------------+----------+-------+-------+--------------+ | STATE | Total_R | Pct_R | Calls | R/Call | +--------------------+----------+-------+-------+--------------+ | starting | 0.000078 | 35.94 | 1 | 0.0000780000 | | Opening tables | 0.000031 | 14.29 | 1 | 0.0000310000 | | executing | 0.000022 | 10.14 | 1 | 0.0000220000 | | optimizing | 0.000019 | 8.76 | 1 | 0.0000190000 | | System lock | 0.000015 | 6.91 | 1 | 0.0000150000 | | freeing items | 0.000014 | 6.45 | 1 | 0.0000140000 | | init | 0.000012 | 5.53 | 1 | 0.0000120000 | | Table lock | 0.000010 | 4.61 | 1 | 0.0000100000 | | end | 0.000006 | 2.76 | 1 | 0.0000060000 | | cleaning up | 0.000004 | 1.84 | 1 | 0.0000040000 | | query end | 0.000003 | 1.38 | 1 | 0.0000030000 | | logging slow query | 0.000003 | 1.38 | 1 | 0.0000030000 | +--------------------+----------+-------+-------+--------------+ 12 rows in set (0.03 sec)
开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表
如下面的查询，部分信息省略
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING LIMIT 3,3\G; *************************** 1. row *************************** QUERY_ID: 1 SEQ: 4 STATE: System lock DURATION: 0.000004 CPU_USER: 0.000000 CPU_SYSTEM: 0.000000 CONTEXT_VOLUNTARY: 0 CONTEXT_INVOLUNTARY: 0 BLOCK_OPS_IN: 0 BLOCK_OPS_OUT: 0 MESSAGES_SENT: 0 MESSAGES_RECEIVED: 0 PAGE_FAULTS_MAJOR: 0 PAGE_FAULTS_MINOR: 0 SWAPS: 0 SOURCE_FUNCTION: unknown function SOURCE_FILE: lock.cc SOURCE_LINE: 260
停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭
mysql> set profiling=off;
Query OK, 0 rows affected, 1 warning (0.00 sec)
现在我也只是具体用了这么点，在具体生产环境里，怎么利用它来优化MySQL（SQL执行）、找出具体的SQL优化方案，有待进一步学习。
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Understanding Computation
Tom Stuart / O'Reilly Media / 2013-6-3 / USD 39.99
Finally, you can learn computation theory and programming language design in an engaging, practical way. Understanding Computation explains theoretical computer science in a context you'll recognize, ......一起来看看 《Understanding Computation》 这本书的介绍吧!
