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

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_codeFROM dm_order_products AS dopLEFT JOIN orders AS o ON o.orders_id=dop.orders_idLEFT 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_statusWHERE 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_codeFROM(SELECT * FROM orders AS oWHERE 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) oLEFT JOIN dm_order_products AS dop ON o.orders_id=dop.orders_idLEFT 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_statusORDER 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|+—-+————-+————+——-+———————————-+—————————-+———+—————–+——+———————————+

喜欢 (0)赏

[搞代码]

分享 (0)