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

约定不等于承诺〃 2022-09-21 15:25 120阅读 0赞

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