MySQL中找到SQL耗时瓶颈，(利刀SHOW PROFILE)各个击破

MySQL中找到SQL耗时瓶颈，(利刀SHOW PROFILE)各个击破

最近做的数据量上升了，部分表到了上千条的数据，那个速度真是惨不忍睹啊，一个字“慢”！
分析下SQL的问题吧！手动分析，只能看到网上说的那些优化方法。但是瓶颈在那里呢？可以使用explain 的方式解决，但是还是感觉explain 不够详细。

MySQL5.0.37版本以上支持了，profiling ，据说是Jeremy Cole捐献给MySQL社区版本，呵呵。就说说他的使用吧！

profiling 功能可以了解到sql语句消耗资源的更详细的信息。

show profile 的格式如下：

SHOW PROFILE [type [, type] … ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type:
    ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS

该方式默认是关闭的。
可以通过以下语句查看
mysql>select @@profiling;
+————-+
| @@profiling |
+————-+
|           0 |
+————-+
1 row in set (0.00 sec)

打开功能

mysql>set profiling=1;

执行需要测试的sql 语句：

select inet_ntoa(visit_net) visit_net,sum(totalbytes) as totalbytes,sum(inbytes) as inbytes,sum(totalbytes)-sum(inbytes) as outbytes from
      ( select * from businessflow where userid=9 ) aa
group by visit_net order by totalbytes DESC limit 0,3

mysql> show profiles\G;

通过指定的Query_ID 来查询指定的sql语句的执行信息：

mysql> show profile for query 1;

+——————————–+———-+
| Status                         | Duration |
+——————————–+———-+
| starting                       | 0.000021 |
| checking query cache for query | 0.000085 |
| Opening tables                 | 0.000036 |
| System lock                    | 0.000007 |
| Table lock                     | 0.000071 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000013 |
| preparing                      | 0.000013 |
| executing                      | 0.000005 |
| Sending data                   | 0.592819 |
| converting HEAP to MyISAM      | 0.026474 |
| Sending data                   | 0.215715 |
| init                           | 0.000029 |
| storing result in query cache | 0.000012 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000011 |
| preparing                      | 0.000008 |
| Creating tmp table             | 0.000038 |
| executing                      | 0.000004 |
| Copying to tmp table           | 0.235759 |
| converting HEAP to MyISAM      | 1.020230 |
| Copying to tmp table on disk   | 0.166174 |
| Sorting result                 | 0.056304 |
| Sending data                   | 0.000053 |
| end                            | 0.000004 |
| removing tmp table             | 0.003539 |
| end                            | 0.000008 |
| query end                      | 0.000004 |
| freeing items                  | 0.000036 |
| removing tmp table             | 0.004107 |
| closing tables                 | 0.000009 |
| logging slow query             | 0.000004 |
| cleaning up                    | 0.000004 |
+——————————–+———-+
33 rows in set (0.00 sec)
mysql> show profile cpu for query 1;
+——————————–+———-+———-+————+
| Status                         | Duration | CPU_user | CPU_system |
+——————————–+———-+———-+————+
| starting                       | 0.000021 | 0.000000 |   0.000000 |
| checking query cache for query | 0.000085 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000036 | 0.000000 |   0.000000 |
| System lock                    | 0.000007 | 0.000000 |   0.000000 |
| Table lock                     | 0.000071 | 0.000000 |   0.000000 |
| optimizing                     | 0.000012 | 0.000000 |   0.000000 |
| statistics                     | 0.000013 | 0.000000 |   0.000000 |
| preparing                      | 0.000013 | 0.000000 |   0.000000 |
| executing                      | 0.000005 | 0.000000 |   0.000000 |
| Sending data                   | 0.592819 | 0.592037 |   0.000000 |
| converting HEAP to MyISAM      | 0.026474 | 0.000000 |   0.020002 |
| Sending data                   | 0.215715 | 0.212014 |   0.008000 |
| init                           | 0.000029 | 0.000000 |   0.000000 |
| storing result in query cache | 0.000012 | 0.000000 |   0.000000 |
| optimizing                     | 0.000005 | 0.000000 |   0.000000 |
| statistics                     | 0.000011 | 0.000000 |   0.000000 |
| preparing                      | 0.000008 | 0.000000 |   0.000000 |
| Creating tmp table             | 0.000038 | 0.000000 |   0.000000 |
| executing                      | 0.000004 | 0.000000 |   0.000000 |
| Copying to tmp table           | 0.235759 | 0.240015 |   0.000000 |
| converting HEAP to MyISAM      | 1.020230 | 0.988061 |   0.032002 |
| Copying to tmp table on disk   | 0.166174 | 0.160010 |   0.000000 |
| Sorting result                 | 0.056304 | 0.052004 |   0.008001 |
| Sending data                   | 0.000053 | 0.000000 |   0.000000 |
| end                            | 0.000004 | 0.000000 |   0.000000 |
| removing tmp table             | 0.003539 | 0.000000 |   0.000000 |
| end                            | 0.000008 | 0.000000 |   0.000000 |
| query end                      | 0.000004 | 0.000000 |   0.000000 |
| freeing items                  | 0.000036 | 0.000000 |   0.000000 |
| removing tmp table             | 0.004107 | 0.000000 |   0.008000 |
| closing tables                 | 0.000009 | 0.000000 |   0.000000 |
| logging slow query             | 0.000004 | 0.000000 |   0.000000 |
| cleaning up                    | 0.000004 | 0.000000 |   0.000000 |
+——————————–+———-+———-+————+
33 rows in set (0.00 sec)

从上上面可以看到，

| Sending data                   | 0.592819 |
| converting HEAP to MyISAM      | 0.026474 |
| Sending data                   | 0.215715 |

| Copying to tmp table           | 0.235759 |
| converting HEAP to MyISAM      | 1.020230 |
| Copying to tmp table on disk   | 0.166174 |

以上几项 耗时较长，所以考虑把该表转换为MyISAM 以供查询提高速度。并且提高的值

测试完毕以后 ，关闭参数：

mysql> set profiling=0

再次打开程序，发现等待速度降低。问题有所好转。OK！

这里还需要注意一点就是，需要安装profile模块才能实现。

如下内容转自：http://blog.chinaunix.net/u/29134/showart_480834.html

要注意两点。
1、不过版本要在5.0.37之后

手册上介绍：(SHOW PROFILES and SHOW PROFILE were added in MySQL 5.0.37. )
Important
Please note that the SHOW PROFILE and SHOW PROFILES functionality is part of the MySQL 5.0 Community Server only.
2、变量profiling是用户变量，每次都得重新启用。
以下是我做的一些实验。数据很明显，就不多解释了。
mysql> use test
Database changed
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+

| Tables_in_test |
+----------------+

| bag_item |
| bag_user |
| score |
| t |
+----------------+

4 rows in set (0.03 sec)

mysql> select count(*) from t;
+----------+

| count(*) |
+----------+

| 2097152 |
+----------+

1 row in set (0.74 sec)

mysql> show profiles;
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.02717000 | show tables            |
|        2 | 0.74770100 | select count(*) from t |
+----------+------------+------------------------+
2 rows in set (0.00 sec)

mysql> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000004 |
| checking query cache for query | 0.000044 |
| Opening tables                 | 0.000012 |
| System lock                    | 0.000017 |
| Table lock                     | 0.00003  |
| init                           | 0.000013 |
| optimizing                     | 0.000008 |
| statistics                     | 0.000013 |
| preparing                      | 0.000011 |
| executing                      | 0.000006 |
| Sending data                   | 0.747313 |
| end                            | 0.000014 |
| query end                      | 0.000006 |
| storing result in query cache  | 0.000006 |
| freeing items                  | 0.000012 |
| closing tables                 | 0.000009 |
| logging slow query             | 0.000183 |
+--------------------------------+----------+
17 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 |
+--------------------------------+----------+----------+------------+--------------+---------------+
| (initialization)               | 0.000004 | 0        | 0          |            0 |             0 |
| checking query cache for query | 0.000044 | 0        | 0          |            0 |             0 |
| Opening tables                 | 0.000012 | 0        | 0          |            0 |             0 |
| System lock                    | 0.000017 | 0        | 0          |            0 |             0 |
| Table lock                     | 0.00003  | 0        | 0          |            0 |             0 |
| init                           | 0.000013 | 0        | 0          |            0 |             0 |
| optimizing                     | 0.000008 | 0        | 0          |            0 |             0 |
| statistics                     | 0.000013 | 0        | 0          |            0 |             0 |
| preparing                      | 0.000011 | 0        | 0          |            0 |             0 |
| executing                      | 0.000006 | 0        | 0          |            0 |             0 |
| Sending data                   | 0.747313 | 0.746887 | 0          |            0 |             0 |
| end                            | 0.000014 | 0        | 0          |            0 |             0 |
| query end                      | 0.000006 | 0        | 0          |            0 |             0 |
| storing result in query cache  | 0.000006 | 0        | 0          |            0 |             0 |
| freeing items                  | 0.000012 | 0        | 0          |            0 |             0 |
| closing tables                 | 0.000009 | 0        | 0          |            0 |             0 |
| logging slow query             | 0.000183 | 0        | 0          |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set (0.00 sec)

mysql> insert into t(username) select username from t;
Query OK, 2097152 rows affected (34.17 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql> show profiles;
+----------+-------------+------------------------------------------------+
| Query_ID | Duration    | Query                                          |
+----------+-------------+------------------------------------------------+
|        1 |  0.02717000 | show tables                                    |
|        2 |  0.74770100 | select count(*) from t                         |
|        3 |  0.00004200 | show prifile for query 2                       |
|        4 | 34.30410100 | insert into t(username) select username from t |
+----------+-------------+------------------------------------------------+
4 rows in set (0.00 sec)

mysql> show profile cpu,block io,memory,swaps for query 4;
+------------------------------+-----------+-----------+------------+--------------+---------------+-------+
| Status                       | Duration  | CPU_user  | CPU_system | Block_ops_in | Block_ops_out | Swaps |
+------------------------------+-----------+-----------+------------+--------------+---------------+-------+
| (initialization)             | 0.000038  | 0         | 0          |            0 |             0 |     0 |
| checking permissions         | 0.000016  | 0         | 0          |            0 |             0 |     0 |
| Opening tables               | 0.000014  | 0         | 0          |            0 |             0 |     0 |
| System lock                  | 0.000007  | 0         | 0          |            0 |             0 |     0 |
| Table lock                   | 0.000013  | 0         | 0          |            0 |             0 |     0 |
| init                         | 0.000015  | 0         | 0          |            0 |             0 |     0 |
| optimizing                   | 0.000006  | 0         | 0          |            0 |             0 |     0 |
| statistics                   | 0.000012  | 0         | 0          |            0 |             0 |     0 |
| preparing                    | 0.000011  | 0         | 0          |            0 |             0 |     0 |
| Creating tmp table           | 0.000029  | 0         | 0          |            0 |             0 |     0 |
| executing                    | 0.000005  | 0         | 0          |            0 |             0 |     0 |
| Copying to tmp table         | 1.262877  | 1.24981   | 0.012998   |            0 |             0 |     0 |
| converting HEAP to MyISAM    | 0.384814  | 0.187971  | 0.19797    |            0 |             0 |     0 |
| Copying to tmp table on disk | 1.417069  | 1.203817  | 0.191971   |            0 |             0 |     0 |
| Sending data                 | 31.104185 | 13.965877 | 0.843872   |            0 |             0 |     0 |
| end                          | 0.000017  | 0         | 0          |            0 |             0 |     0 |
| removing tmp table           | 0.134872  | 0         | 0.029995   |            0 |             0 |     0 |
| end                          | 0.000026  | 0         | 0          |            0 |             0 |     0 |
| query end                    | 0.000006  | 0         | 0          |            0 |             0 |     0 |
| freeing items                | 0.000011  | 0         | 0          |            0 |             0 |     0 |
| closing tables               | 0.000009  | 0         | 0          |            0 |             0 |     0 |
| logging slow query           | 0.000049  | 0         | 0          |            0 |             0 |     0 |
+------------------------------+-----------+-----------+------------+--------------+---------------+-------+
22 rows in set (0.00 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  4194304 |
+----------+
1 row in set (1.51 sec)

mysql> show profiles;
+----------+-------------+------------------------------------------------+
| Query_ID | Duration    | Query                                          |
+----------+-------------+------------------------------------------------+
|        1 |  0.02717000 | show tables                                    |
|        2 |  0.74770100 | select count(*) from t                         |
|        3 |  0.00004200 | show prifile for query 2                       |
|        4 | 34.30410100 | insert into t(username) select username from t |
|        5 |  1.50563800 | select count(*) from t                         |
+----------+-------------+------------------------------------------------+
5 rows in set (0.00 sec)

mysql> show profile cpu,block io,memory,swaps,context switches,source for query 5;
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+-------+---------------------------+---------------+-------------+
| Status                         | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Swaps | Source_function           | Source_file   | Source_line |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+-------+---------------------------+---------------+-------------+
| (initialization)               | 0.000004 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | send_result_to_client     | sql_cache.cc  |        1099 |
| checking query cache for query | 0.000048 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | open_tables               | sql_base.cc   |        2629 |
| Opening tables                 | 0.000011 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_lock_tables         | lock.cc       |         153 |
| System lock                    | 0.000007 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_lock_tables         | lock.cc       |         162 |
| Table lock                     | 0.000032 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_select              | sql_select.cc |        2242 |
| init                           | 0.000013 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | optimize                  | sql_select.cc |         745 |
| optimizing                     | 0.000008 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | optimize                  | sql_select.cc |         904 |
| statistics                     | 0.000013 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | optimize                  | sql_select.cc |         914 |
| preparing                      | 0.000011 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | exec                      | sql_select.cc |        1565 |
| executing                      | 0.000006 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | exec                      | sql_select.cc |        2083 |
| Sending data                   | 1.505401 | 1.504771 | 0          |                 3 |                   5 |            0 |             0 |     0 | mysql_select              | sql_select.cc |        2287 |
| end                            | 0.000015 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_execute_command     | sql_parse.cc  |        5122 |
| query end                      | 0.000005 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | query_cache_end_of_result | sql_cache.cc  |         729 |
| storing result in query cache  | 0.000007 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_parse               | sql_parse.cc  |        6116 |
| freeing items                  | 0.00001  | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | dispatch_command          | sql_parse.cc  |        2146 |
| closing tables                 | 0.000009 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | log_slow_statement        | sql_parse.cc  |        2204 |
| logging slow query             | 0.000038 | 0        | 0          |                 0 |                   0 |            0 |             0 |     0 | dispatch_command          | sql_parse.cc  |        2169 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+-------+---------------------------+---------------+-------------+
17 rows in set (0.00 sec)
mysql> update t set username = 'waill';
Query OK, 4194304 rows affected (44.82 sec)
Rows matched: 4194304  Changed: 4194304  Warnings: 0

mysql> show profiles;
+----------+-------------+------------------------------------------------+
| Query_ID | Duration    | Query                                          |
+----------+-------------+------------------------------------------------+
|        1 |  0.02717000 | show tables                                    |
|        2 |  0.74770100 | select count(*) from t                         |
|        3 |  0.00004200 | show prifile for query 2                       |
|        4 | 34.30410100 | insert into t(username) select username from t |
|        5 |  1.50563800 | select count(*) from t                         |
|        6 | 44.82054700 | update t set username = 'waill'                |
+----------+-------------+------------------------------------------------+
6 rows in set (0.00 sec)

mysql> show profile cpu,block io,memory,swaps,context switches,source for query 6;
+----------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+-------+-----------------------+---------------+-------------+
| Status               | Duration  | CPU_user  | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Swaps | Source_function       | Source_file   | Source_line |
+----------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+-------+-----------------------+---------------+-------------+
| (initialization)     | 0.000035  | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | check_access          | sql_parse.cc  |        5306 |
| checking permissions | 0.000011  | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | open_tables           | sql_base.cc   |        2629 |
| Opening tables       | 0.000011  | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_lock_tables     | lock.cc       |         153 |
| System lock          | 0.000007  | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_lock_tables     | lock.cc       |         162 |
| Table lock           | 0.000008  | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_update          | sql_update.cc |         167 |
| init                 | 0.000021  | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_update          | sql_update.cc |         429 |
| Updating             | 44.514679 | 27.976747 | 2.818571   |              9949 |                1584 |            0 |             0 |     0 | mysql_update          | sql_update.cc |         560 |
| end                  | 0.305671  | 0.028996  | 0.023997   |                43 |                  18 |            0 |             0 |     0 | mysql_execute_command | sql_parse.cc  |        5122 |
| query end            | 0.000026  | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | mysql_parse           | sql_parse.cc  |        6116 |
| freeing items        | 0.000016  | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | dispatch_command      | sql_parse.cc  |        2146 |
| closing tables       | 0.000012  | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | log_slow_statement    | sql_parse.cc  |        2204 |
| logging slow query   | 0.00005   | 0         | 0          |                 0 |                   0 |            0 |             0 |     0 | dispatch_command      | sql_parse.cc  |        2169 |
+----------------------+-----------+-----------+------------+-------------------+---------------------+--------------+---------------+-------+-----------------------+---------------+-------------+
12 rows in set (0.00 sec)