MySQL:内联视图与标量子查询 的问题
SELECT
o.order_id,
i.order_item_id,
i.channel,
o.use_online_payed+o.used_balance+o.use_offline_payed+o.need_pay AS total_pay,
stock.tax_amount_money*i.quantity AS tax_amount_money,
FROM_UNIXTIME(o.create_time,'%H') AS create_time,
FROM_UNIXTIME(o.check_time,'%H') AS check_time
FROM
(SELECT product_spec_id,
SUM(tax_stock_price*(good_num+bad_num))/(SUM(good_num)+SUM(bad_num)) AS tax_amount_money
FROM `c`
GROUP BY product_spec_id) AS stock,
a AS o,
b AS i
WHERE o.order_id = i.order_id
AND i.product_spec_id = stock.product_spec_id
AND o.check_time>0
AND o.status IN (1,2,3,4)
AND o.sub_status NOT IN (9,11)
AND o.rejection_status != 3
AND o.parent_order_sn = 0
AND o.check_time BETWEEN 1461427200 AND 1461513599\G
+----+-------------+-----------------------+--------+----------------- ---------------------+-----------------+---------+-----------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------+--------+-- ------------------------------------+-----------------+---------+-----------------------+--------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 324542 | |
| 1 | PRIMARY | i | ref | order_id,order_id_2,product_spec_id | product_spec_id | 4 | stock.product_spec_id | 2 | Using where |
| 1 | PRIMARY | o | eq_ref | PRIMARY,i_gt_orders_check_time | PRIMARY | 4 | shop_zp.i.order_id | 1 | Using where |
| 2 | DERIVED | c | index | NULL | psi | 4 | NULL | 342212 | |
+----+-------------+-----------------------+--------+---------------------------------------+-----------------+---------+-----------------------+--------+-------------+
执行时间4s
不使用from+内联视图方式,改为标量子查询
SELECT
o.order_id,
i.order_item_id,
i.channel,
o.use_online_payed+o.used_balance+o.use_offline_payed+o.need_pay AS total_pay,
(SELECT
SUM(tax_stock_price*(good_num+bad_num))/(SUM(good_num)+SUM(bad_num)) AS tax_amount_money
FROM `c`
WHERE product_spec_id=i.product_spec_id
GROUP BY product_spec_id)*i.quantity AS tax_amount_money,
FROM_UNIXTIME(o.create_time,'%H') AS create_time,
FROM_UNIXTIME(o.check_time,'%H') AS check_time
FROM
a AS o,
b AS i
WHERE o.order_id = i.order_id
AND o.check_time>0
AND o.status IN (1,2,3,4)
AND o.sub_status NOT IN (9,11)
AND o.rejection_status != 3
AND o.parent_order_sn = 0
AND o.check_time BETWEEN 1461427200 AND 1461513599
+----+--------------------+-----------------------+-------+------------------------------------+------------------------+---------+---------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------+-------+------------------------------------+------------------------+---------+---------------------------+------+-------------+
| 1 | PRIMARY | o | range | PRIMARY,i_gt_orders_check_time | i_gt_orders_check_time | 4 | NULL | 385 | Using where |
| 1 | PRIMARY | i | ref | order_id,order_id_2 | order_id | 4 | shop_zp.o.order_id | 1 | |
| 2 | DEPENDENT SUBQUERY | c | ref | psi | psi | 4 | shop_zp.i.product_spec_id | 1 | Using where |
+----+--------------------+-----------------------+-------+------------------------------------+------------------------+---------+---------------------------+------+-------------+
改写后0.02S
可见,在MySQL中,没有hash情况下,标量子查询还是很有用处的。