MySQL：内联视图与标量子查询

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情况下，标量子查询还是很有用处的。