作者:赵庭洪 | 来源:互联网 | 2018-07-10 07:47
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如下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,执行时间为将近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
|
| 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
|
+----+-------------+------------+-------+----------------------------------+----------------------------+---------+-----------------+------+---------------------------------+