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

sql如下SELECTDISTINCTo.orders_id,o.oa_order_id,os.orders_status_name,o.order_type,o.date_purchasedASadd_date,dop.resource,dop.country_codeFROMdm_order

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，执行时间为将近20smysql> 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

| | 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

|

+----+-------------+------------+-------+----------------------------------+----------------------------+---------+-----------------+------+---------------------------------+

本文原创发布php中文网，转载请注明出处，感谢您的尊重！