之前在第4天初步介紹了Query Profing,
在第6天介紹了改進的查詢方法,
在第9天的範例中有百分比的計算.
今天將要結合將Query Profing再作進一步改進.
先來產生測試資料,這部份與第5天介紹的類似.
調整記憶體.

SET SESSION max_heap_table_size = 1024 * 1024 * 200;

filler table 與 stored procedure在第5天時都定義過了.
這次只要使用就可以了.
產生 10萬筆的序列.

START TRANSACTION;  
CALL prc_filler(100000);  
Query OK, 1 row affected (1.23 sec)

COMMIT; 

將 profiling 功能啟用

SET profiling = 1;

利用10萬筆序列,來產生100萬筆的測試資料到實體Table.

CREATE TABLE ithelp1011a (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
amount INT UNSIGNED NOT NULL,
price DECIMAL(20, 2) NOT NULL
);


INSERT INTO ithelp1011a(amount, price)
SELECT CEILING(RAND(201310) * 1000) + 100,
       CEILING(RAND(201310) * 1000) + 30.00
  FROM filler
 CROSS JOIN (SELECT id
               FROM filler
              LIMIT 10) a;

Query OK, 1000000 rows affected (14.17 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

這次比上次還快一點,只用了14.17秒,
而且也不是先產生100萬筆的序列,只是利用10萬筆序列,
利用 CROSS JOIN 與 LIMIT的結合,就產生了100萬筆測試資料.
這次改進的查詢方式如下:

SELECT STATE
     , SUM(DURATION) AS TotDur
     , ROUND(
          100 * SUM(DURATION) /
              (SELECT SUM(DURATION) 
                 FROM INFORMATION_SCHEMA.PROFILING
                WHERE QUERY_ID = 2)
          , 2) AS Pct_Dur
     , COUNT(1) AS Calls
     , SUM(CPU_USER) AS CPU_USER
     , SUM(CPU_SYSTEM) AS CPU_SYSTEM
  FROM INFORMATION_SCHEMA.PROFILING
 WHERE QUERY_ID = 2
 GROUP BY STATE
 ORDER BY TotDur DESC;

+------------------------------+-----------+---------+-------+----------+------------+
| STATE                        | TotDur    | Pct_Dur | Calls | CPU_USER | CPU_SYSTEM |
+------------------------------+-----------+---------+-------+----------+------------+
| Sending data                 | 14.038065 |   99.09 |     3 | 4.317344 |   0.175973 |
| query end                    |  0.128927 |    0.91 |     1 | 0.007998 |   0.003000 |
| Opening tables               |  0.000108 |    0.00 |     1 | 0.000000 |   0.000000 |
| starting                     |  0.000080 |    0.00 |     1 | 0.000000 |   0.000000 |
| System lock                  |  0.000033 |    0.00 |     1 | 0.000000 |   0.000000 |
| freeing items                |  0.000030 |    0.00 |     1 | 0.000000 |   0.000000 |
| closing tables               |  0.000026 |    0.00 |     2 | 0.000000 |   0.000000 |
| statistics                   |  0.000012 |    0.00 |     2 | 0.000000 |   0.000000 |
| init                         |  0.000012 |    0.00 |     1 | 0.000000 |   0.000000 |
| preparing                    |  0.000012 |    0.00 |     2 | 0.000000 |   0.000000 |
| checking permissions         |  0.000008 |    0.00 |     3 | 0.000000 |   0.000000 |
| removing tmp table           |  0.000007 |    0.00 |     1 | 0.000000 |   0.000000 |
| Waiting for query cache lock |  0.000007 |    0.00 |     2 | 0.000000 |   0.000000 |
| optimizing                   |  0.000005 |    0.00 |     2 | 0.000000 |   0.000000 |
| end                          |  0.000003 |    0.00 |     2 | 0.000000 |   0.000000 |
| cleaning up                  |  0.000002 |    0.00 |     1 | 0.000000 |   0.000000 |
| logging slow query           |  0.000002 |    0.00 |     2 | 0.000000 |   0.000000 |
| executing                    |  0.000002 |    0.00 |     2 | 0.000000 |   0.000000 |
+------------------------------+-----------+---------+-------+----------+------------+
18 rows in set (0.00 sec)

增加了百分比的部份,就更提高了可讀性,更易於了解情況.