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., 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 | | --设置保留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 ;
+--------------------+----------+
| 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 |
+--------------------+----------+
rows in set (0.00 sec)
其他的一些具体的参数：
mysql> SHOW PROFILE BLOCK IO, CPU FOR QUERY ;
+--------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000078 | 0.000000 | 0.000000 | | |
| Opening tables | 0.000031 | 0.000000 | 0.000000 | | |
| System lock | 0.000015 | 0.000000 | 0.000000 | | |
| Table lock | 0.000010 | 0.000000 | 0.000000 | | |
| init | 0.000012 | 0.000000 | 0.000000 | | |
| optimizing | 0.000019 | 0.000000 | 0.000000 | | |
| executing | 0.000022 | 0.000000 | 0.000000 | | |
| end | 0.000006 | 0.000000 | 0.000000 | | |
| query end | 0.000003 | 0.000000 | 0.000000 | | |
| freeing items | 0.000014 | 0.000000 | 0.000000 | | |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | | |
| cleaning up | 0.000004 | 0.000000 | 0.000000 | | |
+--------------------+----------+----------+------------+--------------+---------------+
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 | | 0.0000780000 |
| Opening tables | 0.000031 | 14.29 | | 0.0000310000 |
| executing | 0.000022 | 10.14 | | 0.0000220000 |
| optimizing | 0.000019 | 8.76 | | 0.0000190000 |
| System lock | 0.000015 | 6.91 | | 0.0000150000 |
| freeing items | 0.000014 | 6.45 | | 0.0000140000 |
| init | 0.000012 | 5.53 | | 0.0000120000 |
| Table lock | 0.000010 | 4.61 | | 0.0000100000 |
| end | 0.000006 | 2.76 | | 0.0000060000 |
| cleaning up | 0.000004 | 1.84 | | 0.0000040000 |
| query end | 0.000003 | 1.38 | | 0.0000030000 |
| logging slow query | 0.000003 | 1.38 | | 0.0000030000 |
+--------------------+----------+-------+-------+--------------+
rows in set (0.03 sec)
开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表
如下面的查询，部分信息省略
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING LIMIT ,\G;
*************************** . row ***************************
QUERY_ID:
SEQ:
STATE: System lock
DURATION: 0.000004
CPU_USER: 0.000000
CPU_SYSTEM: 0.000000
CONTEXT_VOLUNTARY:
CONTEXT_INVOLUNTARY:
BLOCK_OPS_IN:
BLOCK_OPS_OUT:
MESSAGES_SENT:
MESSAGES_RECEIVED:
PAGE_FAULTS_MAJOR:
PAGE_FAULTS_MINOR:
SWAPS:
SOURCE_FUNCTION: unknown function
SOURCE_FILE: lock.cc
SOURCE_LINE:
停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭
mysql> set profiling=off;
Query OK, 0 rows
affected, 1 warning (0.00 sec)
现在我也只是具体用了这么点，在具体生产环境里，怎么利用它来优化MySQL（SQL执行）、找出具体的SQL优化方案，有待进一步学习。
最新文章
- 使用if else if else 统计计算
- Redhat 使用中文安装后更换为英文的设定
- My implementation of AVL tree
- Pair Project: Elevator Scheduler [电梯调度算法的实现和测试][关于电梯调度算法的附加思考]：刘耀先-11061183,罗凡-11061174
- jQuery选择器种类整理
- C++ Primer 随笔 Chapter 9 顺序容器
- 基于网络的服装定制MTM系统研究 - 硕士论文 - 道客巴巴
- SQL Server索引 - 非聚集索引 <第七篇>
- 由 “无法使用从远程表选择的 lob 定位符” 错误而引导出来的一系列问题解决方案
- To Fill or Not to Fill (贪心)
- 微信支付 chooseWXPay:fail
- K-means聚类 的 Python 实现
- 一种简单的 rem 单位基准设置
- [物理学与PDEs]第3章第2节 磁流体力学方程组 2.1 考虑到导电媒质 (等离子体) 的运动对 Maxwell 方程组的修正
- numpy 库使用
- struts2 升级至2.3.32时访问页面报错 File "/struts-tags" not found
- 【C++ Primer 第13章】3. 交换操作
- Linux基础命令---添加组groupadd、删除组groupdel
- 非接触IC卡中typeA卡和typeB卡的区别--总结，二者的调制方式和编码方式不同
- 使用 requests 配置代理服务