巧用临时表将大结果集转换为小结果集驱动查询

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导临时表，重新排序后，再返回！

©著作权归作者所有：来自51CTO博客作者justforqdj的原创作品，如需转载，请注明出处，否则将追究法律责任

mysql临时表驱动查询mysql sql调优记录