欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

MySQL性能优化之单条SQL性能剖析

程序员文章站 2022-03-23 19:35:56
上面一篇文章已经将慢查询语句记录到日志中，接着我们就要对单条SQL查询进行性能分析，了解它慢在何处，才能对症下药进行性能优化。 show profile show profil...

上面一篇文章已经将慢查询语句记录到日志中，接着我们就要对单条SQL查询进行性能分析，了解它慢在何处，才能对症下药进行性能优化。

show profile

show profile命令是MySQL5.1之后引入的，由开源社区的Jeremy Cole贡献。

1. 开启profiling

profiling系统变量是用来支持访问剖析信息的，profiling默认是关闭的，我们可以用set profiling=1命令开启profile。

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

查看系统变量除了使用show variables命令外，还可以使用select @@语句，其中@@用来访问系统变量，一个@用来访问用户自定义变量。设置变量值可以使用set命令，命令语法如下：

SET variable_assignment [, variable_assignment] …

variable_assignment:
user_var_name = expr
| param_name = expr
| local_var_name = expr
| [GLOBAL | SESSION]
system_var_name = expr
| [@@global. | @@session. | @@]
system_var_name = expr

set命令更多详细内容可以参考MySQL官方手册

2. show profiles

show profiles命令用来显示当前会话最近执行语句的耗时情况。

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show profiles;
Empty set, 1 warning (0.00 sec)

mysql> show databases;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| db_cnilink           |
| db_internation_trade |
| db_oa                |
| db_privilege         |
| db_spring_jpa        |
| db_tao_market        |
| db_zheng             |
| mysql                |
| performance_schema   |
| sys                  |
| test                 |
+----------------------+
12 rows in set (0.01 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dept           |
| emp            |
+----------------+
7 rows in set (0.00 sec)

mysql> select * from emp;
+-------+-------+------------+------+---------------------+------+------+--------+
| empno | ename | job        | mgr  | hireDate            | sal  | comm | deptno |
+-------+-------+------------+------+---------------------+------+------+--------+
|     1 | 张三  | 系统架构师 | NULL | 2015-01-07 18:30:25 |   20 |   20 |      1 |
|     2 | 李四  | 扫地员工   | NULL | 2015-01-07 18:30:25 |   20 |   20 |      1 |
|     3 | SMITH | 软件设计师 |    1 | 2017-11-15 16:12:00 |   10 |   10 |      2 |
|     4 | SMITH | 软件设计师 |    1 | 2017-11-15 16:24:25 |   10 |   10 |      2 |
+-------+-------+------------+------+---------------------+------+------+--------+
4 rows in set (0.01 sec)

mysql> show profiles;
+----------+------------+-------------------+
| Query_ID | Duration   | Query             |
+----------+------------+-------------------+
|        1 | 0.00629075 | show databases    |
|        2 | 0.00312050 | SELECT DATABASE() |
|        3 | 0.00216050 | show tables       |
|        4 | 0.01549250 | select * from emp |
+----------+------------+-------------------+
4 rows in set, 1 warning (0.00 sec)

可以看到show profiles展示了最近执行的四条查询语句。show profiles默认保存15条性能剖析信息，可以通过profiling_history_size系统变量来修改这个值，注意这个变量最大值为100。

3. show profile

我们要看到某一条查询的详细性能剖析信息，需要用到show profile命令。

mysql> show profiles;
+----------+------------+-------------------+
| Query_ID | Duration   | Query             |
+----------+------------+-------------------+
|        1 | 0.00629075 | show databases    |
|        2 | 0.00312050 | SELECT DATABASE() |
|        3 | 0.00216050 | show tables       |
|        4 | 0.01549250 | select * from emp |
+----------+------------+-------------------+
4 rows in set, 1 warning (0.00 sec)

# show profile命令默认显示最近一条查询的剖析信息
mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.002576 |
| checking permissions | 0.000011 |
| Opening tables       | 0.008276 |
| init                 | 0.000052 |
| System lock          | 0.000026 |
| optimizing           | 0.000008 |
| statistics           | 0.000026 |
| preparing            | 0.000021 |
| executing            | 0.000002 |
| Sending data         | 0.000816 |
| end                  | 0.000007 |
| query end            | 0.000007 |
| closing tables       | 0.000009 |
| freeing items        | 0.000079 |
| logging slow query   | 0.000007 |
| Opening tables       | 0.000163 |
| System lock          | 0.003389 |
| cleaning up          | 0.000019 |
+----------------------+----------+
18 rows in set, 1 warning (0.00 sec)

# 要获取更早的查询剖析信息要用for query字句
# for query字句后面跟QUERY_ID
mysql> show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.001741 |
| checking permissions | 0.000010 |
| checking permissions | 0.000002 |
| Opening tables       | 0.000040 |
| init                 | 0.000009 |
| System lock          | 0.000005 |
| optimizing           | 0.000006 |
| statistics           | 0.000012 |
| preparing            | 0.000010 |
| executing            | 0.000005 |
| checking permissions | 0.000220 |
| Sending data         | 0.000016 |
| end                  | 0.000003 |
| query end            | 0.000004 |
| closing tables       | 0.000002 |
| removing tmp table   | 0.000004 |
| closing tables       | 0.000003 |
| freeing items        | 0.000055 |
| cleaning up          | 0.000016 |
+----------------------+----------+
19 rows in set, 1 warning (0.00 sec)

从show profile可以清楚地看到查询语句每个阶段的耗时情况。

关于show profile命令的更多语法查看官方手册：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

4. 直接查询INFORMATION_SCHEMA.PROFILING表

show profile命令列出来的信息的确很详细，但是我们无法快速的确定哪个步骤花费的时间最多，因为输出的顺序是按照语句执行的顺序排列的。但是我们实际更关心的是哪些部分开销较大，但是很不幸show profile命令暂时不支持order by这样的排序功能。实际上profile的性能剖析信息都存在information_schema数据库的profiling表中。

mysql> set @query_id=4;
Query OK, 0 rows affected (0.00 sec)

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       |
+----------------------+----------+-------+-------+--------------+
| Opening tables       | 0.008439 | 54.47 |     2 | 0.0042195000 |
| System lock          | 0.003415 | 22.04 |     2 | 0.0017075000 |
| starting             | 0.002576 | 16.63 |     1 | 0.0025760000 |
| Sending data         | 0.000816 |  5.27 |     1 | 0.0008160000 |
| freeing items        | 0.000079 |  0.51 |     1 | 0.0000790000 |
| init                 | 0.000052 |  0.34 |     1 | 0.0000520000 |
| statistics           | 0.000026 |  0.17 |     1 | 0.0000260000 |
| preparing            | 0.000021 |  0.14 |     1 | 0.0000210000 |
| cleaning up          | 0.000019 |  0.12 |     1 | 0.0000190000 |
| checking permissions | 0.000011 |  0.07 |     1 | 0.0000110000 |
| closing tables       | 0.000009 |  0.06 |     1 | 0.0000090000 |
| optimizing           | 0.000008 |  0.05 |     1 | 0.0000080000 |
| logging slow query   | 0.000007 |  0.05 |     1 | 0.0000070000 |
| query end            | 0.000007 |  0.05 |     1 | 0.0000070000 |
| end                  | 0.000007 |  0.05 |     1 | 0.0000070000 |
| executing            | 0.000002 |  0.01 |     1 | 0.0000020000 |
+----------------------+----------+-------+-------+--------------+
16 rows in set, 17 warnings (0.01 sec)

上面的查询语句参考自《高性能MySQL》第三版3.3节。

通过这个查询可以清楚地看到最耗时的部分。

通过看官方手册对explain的解释发现，describe(可简写成desc)和explain可以混着用(MySQL解析器将它们视为同义词)，只是大多数情况下更喜欢用desc来查看表结构(事实上是对show columns命令的简化，主要为了兼容Oracle数据库)，使用explain来查看执行计划(即解析MySQL是如何执行查询语句的)。

使用explain获取执行计划

我们可以使用explain来获取select,delete,insert,replace,update这几个语句经过MySQL优化器优化后的执行计划。

mysql> explain select * from test.emp \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql里面默认以;作为一条完整语句的终结符，但是可以通过delimiter(缩写\d)命令进行修改，可以使用go(缩写\g)命令将SQL语句发送到服务器，使用ego(缩写\G)命令可以让返回的查询结果垂直显示。

下表是上面explain命令返回结果各列的意义(其中json name是以explain format=json执行计划分析返回的结果)：

column json name meaning
id select_id 该SELECT查询的标识符
select_type 没有 该SELECT查询的类型，常见的取值有SIMPLE,PRIMARY,UNION,SUBQUERY,DERIVED(from子句中的子查询)等
table table_name 查询用到了哪些表
partitions partitions 匹配的分区（分区表）
type access_type 连接join类型(从好到坏)：system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range -> index -> ALL。详细请参考官方手册。
possible_keys possible_keys 可能选用的索引(可能有多个)，可以在查询中用 FORCE INDEX，USE INDEX或IGNORE INDEX来强制使用(或不适用)某个特定的索引。
key key 实际选择选用的索引，大多数情况下和possible_keys值相同。如果为NULL，说明语句没有用到索引。
key_len key_length 使用索引的长度，这个值可以确定MySQL实际使用了索引的哪些部分(复合索引经常出现只用到索引的前几列的情况)。
ref ref 与索引进行比较的列(哪些列用到了索引)
rows rows 要扫描的行的估计值(对于InnoDB，这个数字是估计值)
filtered filtered 表条件过滤的行的百分比(rows * filtered%与前一张表进行join连接的行数)
Extra 没有 附加信息，常见的附加信息如：Using index,Using filesort,Using join buffer,Using temporary,Using where。更多附加信息参考官方手册

关于explain这部分内容就不赘述了，官方手册给了详细的解释和例子。

上一篇： C++文件流状态的判别与定位

下一篇： mysql数据库备份脚本的代码实例

推荐阅读