本文介绍了MySQL性能分析的各种方法，包括使用SHOW PROFILE、SHOW STATUS、SHOW GLOBAL STATUS、SHOW PROCESSLIST以及USER_STATISTICS表。强调了SHOW STATUS和SHOW PROCESSLIST在诊断问题时的效率，并提到了监控工具如percona Toolkit的pt-stalk和oprofile的重要性。同时，文章指出临时表的使用和索引优化是影响性能的关键因素。
单条查询剖析
使用SHOW PROFILE
首先需要开启，默认是禁用的。
mysql> set profiling =1;
-- 创建一条查询语句
mysql> select * from table_name;
-- 使用show profiles 查看执行时间及查询sql;
-- 注意 这条sql 必须是profiling 开启后的查询才能出现。
mysql> show profiles;
-> //
+----------+------------+--------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------+
| 1 | 0.00241275 | select * from table_name |
+----------+------------+--------------------------+
1 row in set, 1 warning (0.00 sec)
-- 详细的执行时间
mysql> show profile for query 1;
-> //
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000060 |
| Executing hook on transaction | 0.000004 |
| starting | 0.000007 |
| checking permissions | 0.000005 |
| Opening tables | 0.000041 |
| init | 0.000004 |
| System lock | 0.000009 |
| optimizing | 0.000003 |
| statistics | 0.000019 |
| preparing | 0.000015 |
| executing | 0.002076 |
| end | 0.000003 |
| query end | 0.000003 |
| waiting for handler commit | 0.000008 |
| closing tables | 0.000006 |
| freeing items | 0.000140 |
| cleaning up | 0.000010 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
使用 INFORMATION_SCHEMA.PROFILING 表
上面的profiling 也是直接使用INFORMATION_SCHEMA.PROFILING表查询的。 这种是自定义查询。
mysql> set @query_id=1;
-> 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;
-> //
Query OK, 0 rows affected (0.00 sec)
+--------------------------------+----------+-------+-------+--------------+
| state | total_r | pct_r | calls | r/call |
+--------------------------------+----------+-------+-------+--------------+
| executing | 0.002076 | 86.03 | 1 | 0.0020760000 |
| freeing items | 0.000140 | 5.80 | 1 | 0.0001400000 |
| starting | 0.000067 | 2.78 | 2 | 0.0000335000 |
| Opening tables | 0.000041 | 1.70 | 1 | 0.0000410000 |
| statistics | 0.000019 | 0.79 | 1 | 0.0000190000 |
| preparing | 0.000015 | 0.62 | 1 | 0.0000150000 |
| cleaning up | 0.000010 | 0.41 | 1 | 0.0000100000 |
| System lock | 0.000009 | 0.37 | 1 | 0.0000090000 |
| waiting for handler commit | 0.000008 | 0.33 | 1 | 0.0000080000 |
| closing tables | 0.000006 | 0.25 | 1 | 0.0000060000 |
| checking permissions | 0.000005 | 0.21 | 1 | 0.0000050000 |
| Executing hook on transaction | 0.000004 | 0.17 | 1 | 0.0000040000 |
| init | 0.000004 | 0.17 | 1 | 0.0000040000 |
| optimizing | 0.000003 | 0.12 | 1 | 0.0000030000 |
| end | 0.000003 | 0.12 | 1 | 0.0000030000 |
| query end | 0.000003 | 0.12 | 1 | 0.0000030000 |
+--------------------------------+----------+-------+-------+--------------+
16 rows in set, 2 warnings (0.00 sec)
以上可以看出我的sql 主要消耗在 executing 上。针对我这个sql 可以考虑加索引。但是如果本身执行速度就已经很快的情况下去优化是没必要的。
使用show status
show status 命令会返回计数器的情况。既有服务器级别的全局计数器，也有基于某个连接的会话级别的计数器。
show status 大部分情况下都是一个计数器，可以显示某次读索引的频繁程度。但无法给出具体的消耗时间。
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table_name;
5999 rows in set (0.00 sec)
mysql> show status where Variable_name LIKE 'Handler%' or Variable_name LIKE 'Created%';
-> //
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 0 |
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 6000 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
21 rows in set (0.00 sec)
Created_tmp_disk_tables: 磁盘临时表
Created_tmp_tables ： 临时表
注： 有些版本 show status 本身也会创建一个临时表，所以有些版本的临时表应该减一。EXPLAIN 是估计信息，show status 是实际测量的结果。EXPLAIN 查看时也可以获取大量信息，但当EXPLAIN 无法查出具体使用的临时是否是磁盘表时，可以使用show status。
SHOW GLOBAL STATUS
不需要权限，可以直接使用。对服务器影响小。使用对Threads_running，Threads_connected, Questions, Queries.
SHOW PROCESSLIST
来观察大量线程是否处于不正常的状态或者其他不正常的特征。可能会出现数据尖峰，而尖峰会成为不错的指标。
使用USER_STATISTICS 表
引用的额外级别统计 INFORMATION_SCHEMA 表。
SHOW TABLES FROM INFORMATION_SCHEMA LIKE '%_STATISTICS';
+---------------------------------------------+
| Tables_in_information_schema (%_STATISTICS) |
+---------------------------------------------+
| COLUMN_STATISTICS |
+---------------------------------------------+
- 可以查看出使用最多和最少的索引，通过读取数或者更新数排序。
- 查看从未使用的索引
gunplot 或R 可以把输出结果绘制成图表展示。诊断问题最好是使用SHOW STATUS 和 SHOW PROCESSLIST 这两种方式的开销最低。而图异常可以说明查询在某个地方排队，或查询量突然上升。
监控工具
percona Toolkit 中的 pt-stalk 可以监控高并发线程的情况下查看是否有堆积。监控数据的。
oprofile : 服务器内部诊断工具。但最好不要在生产环境中使用。
tcpdump: 剖析查询，mysql 无法方便的打开和关闭慢查询，此时需要通过tcp 流量模拟。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
