巧用臨時表將大結果集轉換為小結果集驅動查詢薦

justforqdj發表於2014-02-24

sql如下

SELECT DISTINCT o.orders_id, o.oa_order_id,os.orders_status_name, o.order_type,
    o.date_purchased AS add_date,dop.resource, dop.country_code
FROM dm_order_products AS dop
LEFT JOIN orders AS o ON o.orders_id=dop.orders_id
LEFT JOIN orders_total AS ot ON ot.orders_id=o.orders_id AND ot.class=`ot_total`
LEFT JOIN orders_status AS os ON os.orders_status_id=o.orders_status
WHERE  o.date_purchased >= `2014-01-31 10:00:00` AND o.date_purchased <= `2014-02-24 09:59:59`
ORDER BY o.orders_id DESC LIMIT 0, 20;

因為需要在大結果集中order by 去重，再顯示20條.

表特性是orders(o)表對dm_order_products(dop)表為一對多關係,而取出來的dop.country_code為一個訂單號對應唯一值，由於表結構設計問題，每次查詢該country_code都需要去dop查詢。所以，每次查詢都放大結果集，然後再去重，得到所要的結果集合。

explain

+----+-------------+-------+-------+----------------------------------+----------------------------+---------+-------------------------------+-------+----------------------------------------------+
| id | select_type | table | type  | possible_keys                    | key                        | key_len | ref                           | rows  | Extra                                        |
+----+-------------+-------+-------+----------------------------------+----------------------------+---------+-------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | o     | range | PRIMARY,date_purchased           | date_purchased             | 9       | NULL                          | 952922 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ot    | ref   | idx_orders_total_orders_id,class | idx_orders_total_orders_id | 4       | banggood_work.o.orders_id     |     3 |                                              |
|  1 | SIMPLE      | os    | ref   | PRIMARY                          | PRIMARY                    | 4       | banggood_work.o.orders_status |     1 |                                              |
|  1 | SIMPLE      | dop   | ref   | orders_id                        | orders_id                  | 4       | banggood_work.o.orders_id     |     2 |                                              |
+----+-------------+-------+-------+----------------------------------+----------------------------+---------+-------------------------------+-------+----------------------------------------------+

索引情況使用正常，但是發現需要掃描一個大結果集.

profiling，執行時間為將近20s

mysql> show profile cpu,block io for query 1;
+--------------------------------+-----------+----------+------------+--------------+---------------+
| Status                         | Duration  | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+-----------+----------+------------+--------------+---------------+
| starting                       |  0.000025 | 0.000000 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   |  0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| checking query cache for query |  0.000080 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions           |  0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions           |  0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions           |  0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions           |  0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables                 |  0.000034 | 0.000000 |   0.000000 |            0 |             0 |
| System lock                    |  0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   |  0.000024 | 0.000000 |   0.000000 |            0 |             0 |
| init                           |  0.000046 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing                     |  0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| statistics                     |  0.000193 | 0.000000 |   0.000000 |            0 |             0 |
| preparing                      |  0.000054 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table             |  0.000031 | 0.000000 |   0.000000 |            0 |             0 |
| executing                      |  0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| Copying to tmp table           | 12.491533 | 3.039538 |   3.107527 |        11896 |           824 |
| Sorting result                 |  0.030709 | 0.034995 |   0.004000 |           16 |           496 |
| Sending data                   |  0.000048 | 0.000000 |   0.000000 |            0 |             0 |
| end                            |  0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table             |  0.010108 | 0.000000 |   0.010998 |            8 |            32 |
| end                            |  0.000013 | 0.000000 |   0.000000 |            0 |             0 |
| query end                      |  0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables                 |  0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items                  |  0.000338 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query             |  0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query             |  0.000033 | 0.000000 |   0.000000 |            0 |             8 |
| cleaning up                    |  0.000006 | 0.000000 |   0.000000 |            0 |             0 |

可以看到Copying to tmp table 佔了大部分的cpu時間和io,最後sorting result佔比重不大。

我們可以上面描述的結合特性，是否能夠去掉Copying to tmp table 選項！因為是根據orders_id排序，取出最新的20條資料，如果我們在orders表中先把20條資料取出來，再和對應的表連線，這樣一來，就將整個大結果Copying to tmp table 再排序這一步去掉！

看sql語句如下

SELECT DISTINCT  o.orders_id, o.oa_order_id,os.orders_status_name, o.order_type,
    o.date_purchased AS add_date,dop.resource, dop.country_code
FROM
(
SELECT *
FROM   orders AS o
WHERE  o.date_purchased >= `2014-01-31 10:00:00` AND o.date_purchased <= `2014-02-24 09:59:59`
ORDER BY o.orders_id DESC LIMIT 0, 20
) o
LEFT JOIN  dm_order_products AS dop ON o.orders_id=dop.orders_id
LEFT JOIN orders_total AS ot ON ot.orders_id=o.orders_id AND ot.class=`ot_total`
LEFT JOIN orders_status AS os ON os.orders_status_id=o.orders_status
ORDER BY o.orders_id DESC LIMIT 0, 20;
+----+-------------+------------+-------+----------------------------------+----------------------------+---------+-----------------+------+---------------------------------+
| id | select_type | table      | type  | possible_keys                    | key                        | key_len | ref             | rows | Extra                           |
+----+-------------+------------+-------+----------------------------------+----------------------------+---------+-----------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL                             | NULL                       | NULL    | NULL            |   20 | Using temporary; Using filesort |
|  1 | PRIMARY     | dop        | ref   | orders_id                        | orders_id                  | 4       | o.orders_id     |    2 |                                 |
|  1 | PRIMARY     | ot         | ref   | idx_orders_total_orders_id,class | idx_orders_total_orders_id | 4       | o.orders_id     |    3 |                                 |
|  1 | PRIMARY     | os         | ref   | PRIMARY                          | PRIMARY                    | 4       | o.orders_status |    1 |                                 |
|  2 | DERIVED     | o          | index | date_purchased                   | PRIMARY                    | 4       | NULL            |  330 | Using where                     |
+----+-------------+------------+-------+----------------------------------+----------------------------+---------+-----------------+------+---------------------------------+

可以發現，結果集瞬間縮小N倍,再檢視profiling，執行時間變成0.01s

+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| checking query cache for query | 0.000148 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions           | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions           | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions           | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions           | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.000052 | 0.000000 |   0.000000 |            0 |             0 |
| System lock                    | 0.001299 | 0.000000 |   0.001000 |            0 |             8 |
| optimizing                     | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |
| statistics                     | 0.000127 | 0.000000 |   0.000000 |            0 |             0 |
| preparing                      | 0.000127 | 0.001000 |   0.000000 |            0 |             0 |
| executing                      | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| Sorting result                 | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data                   | 0.002182 | 0.001000 |   0.001000 |            0 |           784 |
| Waiting for query cache lock   | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data                   | 0.000053 | 0.000000 |   0.000000 |            0 |             0 |
| init                           | 0.000048 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing                     | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| statistics                     | 0.000315 | 0.000000 |   0.000000 |            0 |             0 |
| preparing                      | 0.000025 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table             | 0.000043 | 0.000000 |   0.000000 |            0 |             0 |
| executing                      | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| Copying to tmp table           | 0.003390 | 0.002000 |   0.002000 |            0 |             0 |
| Sorting result                 | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data                   | 0.000058 | 0.000000 |   0.000000 |            0 |             0 |
| end                            | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table             | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |
| end                            | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| query end                      | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table             | 0.000591 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000630 | 0.001000 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| storing result in query cache  | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000056 | 0.000000 |   0.000000 |            0 |             8 |
| cleaning up                    | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+

可以看到Copying to tmp table已經被優化！執行時間提高了幾百倍！

注意：

1. 如果是limit m,n這種語句型別，第一步要想到，是不是可以抽取一部分使用臨時表優化後，再和外面的其他表進行優化

2. distinct和orders by 是天敵。在大結果集的sql語句中，儘量避免直接使用distinct.否則，mysql會將整個結果集copy導臨時表，重新排序後，再返回！

PostgreSQL函式：返回表查詢結果集
2018-11-18
SQL函式
Mybatis 查詢語句結果集總結
2018-08-03
MyBatis
使用ROWNUM將導致查詢結果集的固化
2010-05-17
[ORACLE BUG]查詢結果錯誤--臨時表並行
2013-02-28
Oracle並行
DBeaver 匯出多個查詢結果集
2024-03-06
【Python】轉換mysql 結果集為詞典型別
2013-07-27
PythonMySql型別
iOS FMDB有返回結果集和無返回結果集
2018-12-19
iOS
long查詢結果轉換為varchar2型別
2013-01-25
型別
PB帶引數帶結果集的動態SQL查詢
2020-04-05
SQL
JAVA + Oracle儲存過程返回查詢結果集
2007-08-16
JavaOracle儲存過程
備忘：laravel 對查詢結果集可以迴圈where查詢
2021-12-02
Laravel
Mybatis使用小技巧-自定義結果集
2019-04-17
MyBatis
SQL查詢結果集對注入的影響及利用
2009-10-15
SQL
Mybatis模糊查詢結果為空
2020-11-14
MyBatis
結果集 (ResultSet)全面解析
2020-11-18
使用查詢結果更新表的方法
2014-06-19
iOS 時間轉換 datefromstring結果為nil
2017-12-13
iOS
Oracle查詢結果儲存為XML
2007-06-08
OracleXML
MySQL中如何橫向顯示結果集薦
2008-06-30
MySql
MySQL 將查詢結果匯出到檔案
2017-12-24
MySql
PHP PDO獲取結果集
2015-05-27
PHP
結果集集合操作（待更新）
2011-08-24
查詢結果集很小，但 Created_tmp_disk_tables 不斷增加
2020-05-19
在sqlplus下格式化輸出查詢結果集
2012-06-12
SQL
Oracle 儲存過程返回結果集|轉|
2012-09-10
Oracle儲存過程
Oracle 儲存過程返回結果集 (轉)
2007-08-14
Oracle儲存過程
navicat 匯出查詢結果
2013-11-07
mysql匯出查詢結果
2013-04-28
MySql
Mybatis 強大的結果集對映器resultMap
2020-05-24
MyBatis
Mybatis底層原理學習（三）：查詢結果集的處理原理
2018-03-06
MyBatis
JPA SQL 查詢、結果集對映(@NamedNativeQuery、@ColumnResult註解說明)
2018-06-29
SQL
JAVA資料庫處理(連線,資料查詢,結果集返回)
2013-07-23
Java資料庫
SQL語句將查詢結果插入到另一張表中
2020-12-22
SQL
SQL 把查詢結果當作"表"來使用
2017-03-19
SQL
mysql查詢結果多列拼接查詢
2018-08-17
MySql
Sql_從查詢的結果集中分組後取最後有效的資料成新的結果集小記(待優化)
2018-07-04
SQL優化
MySQL in UnionAll結果集的優化
2015-12-31
MySql優化
Laravel 手動建立分頁返回物件結果集
2019-12-28
Laravel物件

巧用臨時表將大結果集轉換為小結果集驅動查詢薦

相關文章