高性能Mysql-性能剖析

性能优化的两个原则:
1. 我们通过任务和时间而不是资源来衡量性能。
2. 无法测量就无法优化。

完成一项任务所需要的时间分为两部分:执行时间和等待时间。如果优化任务执行时间,最好的办法是通过测量不同子任务花费的时间。对于等待时间相对复杂,可能是系统间接影响。

下面我们主要借助一些工具分析单条查询的效率。

剖析单条查询

使用SHOW PROFILE

开启

profiling参数可以启用SQL剖析。该参数开启后,后续执行的SQL语句都将记录其资源开销,诸如IO,上下文切换,CPU,Memory等等。
查询是否开启,值为1,则开启:

mysql root@192.168.99.100:afbet> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0           |
+-------------+
1 row in set
Time: 0.008s

可以通过set profiling=1;set profiling=0;开启和关闭,注意开启后损耗性能,测试后一定得关闭。如果是session级别,退出session后,会自动关闭。

也可以通过下列命令查询:

mysql root@192.168.99.100:afbet> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set
Time: 0.007s

已经开启,记录数15

当然,如果忘记具体命令使用,可以通过万能的help帮助:help profile

使用

开启后,执行需要分析的指令如,然后show profils:

> show PROFILES
+----------+------------+---------------------------------------+
| Query_ID | Duration   | Query                                 |
+----------+------------+---------------------------------------+
| 1        | 0.00012325 | SHOW WARNINGS                         |
| 2        | 0.0030495  | select count(1) from t_realsports_bet |
+----------+------------+---------------------------------------+

第二条指令就是我们查询指令,然后看详细信息:

> show PROFILE for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000068 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000017 |
| init                 | 0.000013 |
| System lock          | 0.000009 |
| optimizing           | 0.000006 |
| statistics           | 0.000012 |
| preparing            | 0.000011 |
| executing            | 0.000004 |
| Sending data         | 0.002694 |
| end                  | 0.000011 |
| query end            | 0.000007 |
| closing tables       | 0.000009 |
| freeing items        | 0.000159 |
| cleaning up          | 0.000023 |
+----------------------+----------+

Druation表示持续时间,作为默认输出,可以使用show profile all for query 2获取其他开销细节。profile相关细节可以更灵活地从INFORMATION_SCHEMA中对应的表查询。

>set @query_id=2;
> 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       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.002498 | 87.31 | 1     | 0.0024980000 |
| freeing items        | 0.000139 | 4.86  | 1     | 0.0001390000 |
| starting             | 0.000068 | 2.38  | 1     | 0.0000680000 |
| init                 | 0.000044 | 1.54  | 1     | 0.0000440000 |
| cleaning up          | 0.000021 | 0.73  | 1     | 0.0000210000 |
| Opening tables       | 0.000016 | 0.56  | 1     | 0.0000160000 |
| statistics           | 0.000011 | 0.38  | 1     | 0.0000110000 |
| end                  | 0.000011 | 0.38  | 1     | 0.0000110000 |
| preparing            | 0.000009 | 0.31  | 1     | 0.0000090000 |
| System lock          | 0.000009 | 0.31  | 1     | 0.0000090000 |
| closing tables       | 0.000009 | 0.31  | 1     | 0.0000090000 |
| checking permissions | 0.000009 | 0.31  | 1     | 0.0000090000 |
| optimizing           | 0.000007 | 0.24  | 1     | 0.0000070000 |
| query end            | 0.000006 | 0.21  | 1     | 0.0000060000 |
| executing            | 0.000004 | 0.14  | 1     | 0.0000040000 |
+----------------------+----------+-------+-------+--------------+

上面的统计sql来源于《高性能mysql》,可以使用group by,更灵活。

使用SHOW STATUS

SHOW STATUS无法基于时间统计,但可以观察哪些操作代价高,哪些消耗时间较多。如句柄计数器(handle counter)

示例

> FLUSH STATUS;
> select * from t_test_staus where status=0;
> 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         | 2     |
| 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      | 14551 |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 16    |
+----------------------------+-------+
21 rows in set (0.00 sec)

从上述结果可以看到,使用了2个临时表,并没有创建磁盘表。有14551个读操作没有用到索引(Handler_read_rnd_next)。Handler_read_key 值大,说明索引使用高。其他详细说明可以参阅官方文档。

使用EXPLAIN

> explain SELECT t2.id FROM `t_realsports_subbet_selection` t1, `t_realsports_subbet` t2 WHERE t1.selection_id ='1711211453551000003795' AND t1.subBet_id = t2.id AND t2.status = 0
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows  | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+--------------------------+
| 1  | SIMPLE      | t2    | ALL    | PRIMARY       | <null>  | <null>  | <null>            | 40365 | Using where              |
| 1  | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 304     | test.t2.id,const | 1     | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+--------------------------+
2 rows in set
Time: 0.011s
  • id:每个被独立执行的操作的标识,表示对象被操作的顺序;ID值大,先被执行;如果相同,执行顺序一般从上到下;
  • select_type:数据库引擎将SQL拆分成若干部分的子查询/子操作,每个查询select子句中的查询类型;
  • table: 本次子查询所查询的目标数据表;
  • type:子查询类型,非常重要的性能衡量点。这个字段项可能显示的值包括:“ALL->index->range->ref->eq_ref->const | system->NULL”这些值所表示的查询性能,从左至右依次增加;
  • possible_keys:本次子查询可能使用的索引。如果查询所使用的检索条件可能涉及到多个索引,这里将会列出这些所有的可能性;
  • key: 本次子查询最终被选定的执行索引
  • key_len: 被选定的索引键的长度;
  • ref:表示本次子查询参照的参照条件/参照数据表,参照条件/参照数据表,这个字段的值还可能是一个常量;
  • rows: 执行根据目前数据表的实际情况预估的,完成这个子查询需要扫描的数据行数;
  • Extra:包含不适合在其他列中显示但十分重要的额外信息
    详细说明参考MySQL系列—EXPLAIN 介绍

使用EXPLAIN的局限在于,它只是预估结果,不能使用cache,不能分析insert语句。


参考资料:
[1]. 高性能mysql
[2]. http://blog.csdn.net/u012758088/article/details/77151548

CONTENTS