程序師世界是廣大編程愛好者互助、分享、學習的平台，程序師世界有你更精彩！
設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> Mysql教程：性能跟蹤語句

Mysql教程：性能跟蹤語句

編輯：關於MYSQL數據庫
要注意兩點。
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)
  1. 上一頁:
  2. 下一頁:
關於MYSQL數據庫
mysqldumpslow用法示例(慢查詢)

mysqldumpslow是mysql自帶的用來分析慢查詢的
MySQL Memory 存儲引擎淺析（1）

需求源自項目中的MemCache需求，開始想用MemCac
MySQL 數據庫簡單查詢的方法

 　　建立如此結構的數據表，並插入110萬條隨機記
MySQL結合使用數據庫分析工具SchemaSpy的方法

 近來，我和一幫老客戶一起工作，他們讓花時間給他們
101個MySQL優化技巧和提示

MySQL是一個功能強大的開源數據庫。隨著越來越多的數據庫驅
Linux系統下Mysql使用簡單教程（一）

如果你會查詢這些相關的問題，說明你是一個正在或者准備從事IT
相關文章
閱讀排行榜
mysql delete limit 使用方法詳解 mysql索引詳解,摘自《MySQL 5權威指南》 mysql data文件夾位置查找 MySQL 數據庫中的安全解決方案(3) MySQL升級：從4.1到5.0 mysql 查看連接狀態 sql多表聯查實例 基於MySql的擴展功能生成全局ID mysql常用命令大全 mysql常用命令總結 將csv文件導入到mysql數據庫 淺談mysql的中文亂碼問題
熱門圖文
sql server中datetime字段去除時間的語句 Oracle初學者需要知道的常用數據表如EMP、DEPT、SALGRADE等是如何操作的？圖解操作 存儲 Linux下安裝mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz Oracle系統優化--應用程序性能的優化 數據庫常見故障及恢復原理 Oracle學習經驗談 總結SQL查詢慢的50個原因
欄目導航
MYSQL入門知識SQL基礎知識關於MYSQL數據庫Mariadb數據庫MySQL綜合教程
Copyright © 程式師世界 All Rights Reserved