iT邦幫忙

DAY 4
7

MySQL那些事兒系列 第 4

SQL Query 執行的細節

我們在開發系統時,對SQL Query 總是希望能夠有效率.
了解一道SQL Query執行的細節,有助於提昇效率.
以之前介紹Sakila範例資料庫為例,我們先建立一個Saikla的user,
對Sakila有相關權限.然後登入.
首先要把這次登入的Session啟用Profile功能.

sakila@[sakila]>SET profiling=1;
Query OK, 0 rows affected (0.00 sec)

查詢 city 這個Table.

sakila@[sakila]>SELECT * 
                  FROM city;

檢查PROFILE 裡的紀錄
sakila@[sakila]>SHOW PROFILES;
+----------+------------+-----------------------+
| Query_ID | Duration   | Query                 |
+----------+------------+-----------------------+
|        1 | 0.00086850 | SELECT * 
  FROM city |
+----------+------------+-----------------------+
1 row in set (0.00 sec)

得知 Query_ID 是 1
查看相關細節.

sakila@[sakila]>SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000015 |
| Waiting for query cache lock   | 0.000003 |
| checking query cache for query | 0.000024 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000022 |
| System lock                    | 0.000006 |
| Waiting for query cache lock   | 0.000016 |
| init                           | 0.000011 |
| optimizing                     | 0.000003 |
| statistics                     | 0.000007 |
| preparing                      | 0.000005 |
| executing                      | 0.000002 |
| Sending data                   | 0.000488 |
| Waiting for query cache lock   | 0.000002 |
| Sending data                   | 0.000234 |
| end                            | 0.000003 |
| query end                      | 0.000002 |
| closing tables                 | 0.000004 |
| freeing items                  | 0.000004 |
| Waiting for query cache lock   | 0.000001 |
| freeing items                  | 0.000005 |
| Waiting for query cache lock   | 0.000001 |
| freeing items                  | 0.000001 |
| storing result in query cache  | 0.000002 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000001 |
+--------------------------------+----------+
26 rows in set (0.00 sec)

可以看到一道SQL Query 的執行細分為許多步驟,以及各個步驟的執行時間.

也可以更詳細觀察如CPU的使用情形.
sakila@[sakila]>SHOW PROFILE CPU FOR QUERY 1;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000015 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000003 | 0.000000 |   0.000000 |
| checking query cache for query | 0.000024 | 0.000000 |   0.000000 |
| checking permissions           | 0.000006 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000022 | 0.000000 |   0.000000 |
| System lock                    | 0.000006 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000016 | 0.000000 |   0.000000 |
| init                           | 0.000011 | 0.000000 |   0.000000 |
| optimizing                     | 0.000003 | 0.000000 |   0.000000 |
| statistics                     | 0.000007 | 0.000000 |   0.000000 |
| preparing                      | 0.000005 | 0.000000 |   0.000000 |
| executing                      | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000488 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000234 | 0.000000 |   0.000000 |
| end                            | 0.000003 | 0.000000 |   0.000000 |
| query end                      | 0.000002 | 0.000000 |   0.000000 |
| closing tables                 | 0.000004 | 0.000000 |   0.000000 |
| freeing items                  | 0.000004 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000001 | 0.000000 |   0.000000 |
| freeing items                  | 0.000005 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000001 | 0.000000 |   0.000000 |
| freeing items                  | 0.000001 | 0.000000 |   0.000000 |
| storing result in query cache  | 0.000002 | 0.000000 |   0.000000 |
| logging slow query             | 0.000001 | 0.000000 |   0.000000 |
| cleaning up                    | 0.000001 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+
26 rows in set (0.00 sec)

這個Table 很小,CPU耗用的時間很少,所以變成在小數點後面很遠了,顯示出來就看不到了.

我們換查詢一下 VIEW.
登出後將MySQL 重開,再登入,前面步驟省略.

sakila@[sakila]>SHOW PROFILES;
+----------+------------+------------------------------------------+
| Query_ID | Duration   | Query                                    |
+----------+------------+------------------------------------------+
|        1 | 0.04519025 | SELECT * FROM nicer_but_slower_film_list |
+----------+------------+------------------------------------------+

查看細節.

sakila@[sakila]>SHOW PROFILE CPU FOR QUERY 1;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000016 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| checking query cache for query | 0.000024 | 0.000000 |   0.000000 |
| checking permissions           | 0.000006 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000166 | 0.000000 |   0.000000 |
| System lock                    | 0.000012 | 0.000000 |   0.000000 |
| checking permissions           | 0.000002 | 0.000000 |   0.000000 |
| checking permissions           | 0.000001 | 0.000000 |   0.000000 |
| checking permissions           | 0.000001 | 0.000000 |   0.000000 |
| checking permissions           | 0.000001 | 0.000000 |   0.000000 |
| checking permissions           | 0.000202 | 0.000000 |   0.000000 |
| optimizing                     | 0.000019 | 0.000000 |   0.000000 |
| statistics                     | 0.000055 | 0.000000 |   0.000000 |
| preparing                      | 0.000019 | 0.000000 |   0.000000 |
| Creating tmp table             | 0.000132 | 0.000000 |   0.000000 |
| executing                      | 0.000002 | 0.000000 |   0.000000 |
| Copying to tmp table           | 0.026277 | 0.019997 |   0.005999 |
| Sorting result                 | 0.003313 | 0.002000 |   0.001000 |
| Sending data                   | 0.011883 | 0.003999 |   0.007999 |
| removing tmp table             | 0.001509 | 0.000000 |   0.000999 |
| Sending data                   | 0.000005 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000028 | 0.000000 |   0.000000 |
| init                           | 0.000013 | 0.000000 |   0.000000 |
| optimizing                     | 0.000003 | 0.000000 |   0.000000 |
| statistics                     | 0.000007 | 0.000000 |   0.000000 |
| preparing                      | 0.000005 | 0.000000 |   0.000000 |
| executing                      | 0.000001 | 0.000000 |   0.000000 |
| Sending data                   | 0.000100 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000102 | 0.001000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000072 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000001 | 0.000000 |   0.000000 |
| Sending data                   | 0.000092 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000075 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000001 | 0.000000 |   0.000000 |
| Sending data                   | 0.000118 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000106 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000090 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000001 | 0.000000 |   0.000000 |
| Sending data                   | 0.000096 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000070 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000089 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000084 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.000082 | 0.001000 |   0.000000 |
| Waiting for query cache lock   | 0.000001 | 0.000000 |   0.000000 |
| Sending data                   | 0.000043 | 0.000000 |   0.000000 |
| end                            | 0.000003 | 0.000000 |   0.000000 |
| query end                      | 0.000003 | 0.000000 |   0.000000 |
| closing tables                 | 0.000001 | 0.000000 |   0.000000 |
| removing tmp table             | 0.000199 | 0.000000 |   0.000000 |
| closing tables                 | 0.000015 | 0.000000 |   0.000000 |
| freeing items                  | 0.000007 | 0.000000 |   0.000000 |
| removing tmp table             | 0.000004 | 0.000000 |   0.000000 |
| freeing items                  | 0.000005 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000001 | 0.000000 |   0.000000 |
| freeing items                  | 0.000008 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000001 | 0.000000 |   0.000000 |
| freeing items                  | 0.000001 | 0.000000 |   0.000000 |
| storing result in query cache  | 0.000002 | 0.000000 |   0.000000 |
| logging slow query             | 0.000001 | 0.000000 |   0.000000 |
| cleaning up                    | 0.000002 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+
70 rows in set (0.00 sec)

可以發現步驟變多了,既要檢查權限,也要產生暫存表,最後還要清除暫存表等等.


上一篇
MySQL Workbench 的用途
下一篇
測試資料的產生
系列文
MySQL那些事兒30
0
wilson1966
iT邦研究生 5 級 ‧ 2013-10-06 09:57:53

請問如何對MS SQL 也做相同的測試?

wilson1966提到:
請問如何對MS SQL 也做相同的測試?

SQL Server 也是一門很深的學問,待敝人先把MySQL鐵人賽的部份完成.再把對SQL Server一些粗淺的研究心得與大家分享.
感謝您的關注.

我要留言

立即登入留言