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 因为需要在大结果集中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 | 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|+—-+————-+————+——-+———————————-+—————————-+———+—————–+——+———————————+
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至253000106@qq.com举报，一经查实，本站将立刻删除。
发布者：PHP中文网，转转请注明出处：https://www.chuangxiangniao.com/p/1868738.html
