Mysql Explain 查看 filtered 优化sql

原创
2019/10/12 10:16
阅读数 1.4W

sql

 SELECT mt.* ,ds.ps_count FROM
        (select
         
        do.id,
        do.logistics_company_id,
        do.order_no,
        do.lable_info,
        do.ext_order_no,
        do.ext_order_no2,
        do.ext_po_no,
        do.owner_id,
        do.owner_name,
        do.shop_id,
        do.warehouse,
        do.warehouse_name,
        do.delivery_type,
        do.order_type,
        do.rowstate,
        do.receiver,
        do.province,
        do.city,
        do.country,
        do.address,
        do.mobile_no,
        do.merge_info,
        do.invoice_title,
        do.product_weight,
        do.expr_company,
        do.expr_bill_no,
        do.order_date,
        do.paid_date,
        do.picked_date,
        do.shipped_date,
        TIMESTAMPDIFF(HOUR ,nvl(do.paid_date,now()),nvl( do.shipped_date,now())) as deal_hours,
        do.buyer_note,
        do.seller_note,
        do.note_text,
        do.buyer_id,
        do.id_card_no,
        do.sender,
        do.sender_mobile,
        do.sender_province,
        do.sender_city,
        do.sender_country,
        do.sender_addr,
        do.pick_list_no,
        do.expr_printed,
        do.bill_printed,
        do.pick_type,
        do.part_count,
        do.part_list,
        do.ean_no_list,
        do.part_no_list,
        do.shortage_flag,
        do.pkg_weight,
        do.sync_flag,
        do.create_order_type,
        do.error_msg,
        do.created_date,
        do.created_user,
        do.updated_date,
        do.updated_user,
        do.cut_reason,
        do.goods_count,
        do.print_no,
        do.box_no,
        do.source_platform_code,
        do.ext_order_id
     
        from delivery_order do -- force index(delivery_order_6ix)
        where 1=1 AND do.logistics_company_id = 10000
        and do.owner_id  
         
            in
             (  
                'BGSS'
             , 
                'DBQ'
             , 
                'BSA'
             , 
                'ABJ'
             , 
                'CAT'
             , 
                'DBK'
             , 
                'TS'
             , 
                'JCX'
             , 
                'WCX'
             , 
                'FY'
             , 
                'WHN'
             , 
                'DDG'
             , 
                'YM'
             , 
                'PTJ'
             , 
                'MG'
             , 
                'WYM'
             , 
                'TJ'
             , 
                'TSMJ'
             , 
                'CXCW'
             , 
                'MYCW'
             , 
                'BK'
             , 
                'CXJD'
             , 
                'CXTM'
             , 
                'QCX'
             , 
                'HS'
             ) 
         
         
     
        and do.warehouse  
         
            in
             (  
                'WHC'
             , 
                'QHDC'
             , 
                'JKXS'
             , 
                'JSC'
             , 
                'WHCPC'
             , 
                'CXB'
             , 
                'WHN'
             ) 
         
            AND do.rowstate IN
             (  
                2
             ) 
                AND do.owner_id IN ('BGSS')
 
        )AS mt
        LEFT JOIN
        (SELECT do2.id,do2.part_list,ps.ps_count from delivery_order as do2
		        LEFT JOIN
		        (SELECT
				        part_list,
				        (CASE
				        WHEN (COUNT(*) > 0) THEN COUNT(*)
				        ELSE 0
				        END) AS ps_count FROM delivery_order WHERE rowstate = 2 GROUP BY part_list) AS ps
				        ON do2.part_list = ps.part_list  and  do2.rowstate = 2 
		   ) as ds
        ON mt.id = ds.id
        WHERE 1=1
        order by rowstate ASC ,created_date DESC;

 执行总用时 4秒多, 一个select count 一个 select ,造成一面刷新需要8秒多

使用explain 发现 filtered 为0.1, 总数据量是 60多w

考虑到页面加载的时候,常用查询字段,增加联合索引

logistics_company_id, owner_id, warehouse, rowstate

只需要 0.8秒多

filtered 为 100 ,快了不是一个deng'ji

 

  继续优化 子查询里面也添加同样的查询语句

SELECT mt.* ,ds.ps_count FROM (select do.id, do.logistics_company_id, do.order_no, do.lable_info, do.ext_order_no, do.ext_order_no2, do.ext_po_no, 
	do.owner_id, do.owner_name, do.shop_id, do.warehouse, do.warehouse_name, do.delivery_type, do.order_type, do.rowstate, 
	do.receiver, do.province, do.city, do.country, do.address, do.mobile_no, do.merge_info, do.invoice_title,
	do.product_weight, do.expr_company, do.expr_bill_no, do.order_date, do.paid_date, do.picked_date, do.shipped_date, 
	TIMESTAMPDIFF(HOUR ,nvl(do.paid_date,now()),nvl( do.shipped_date,now())) as deal_hours, 
	do.buyer_note, do.seller_note, do.note_text, do.buyer_id, do.id_card_no, do.sender, do.sender_mobile, do.sender_province, 
	do.sender_city, do.sender_country, do.sender_addr, do.pick_list_no, do.expr_printed, do.bill_printed, do.pick_type, do.part_count, 
	do.part_list, do.ean_no_list, do.part_no_list, do.shortage_flag, do.pkg_weight, do.sync_flag, do.create_order_type, do.error_msg, 
	do.created_date, do.created_user, do.updated_date, do.updated_user, do.cut_reason, do.goods_count, do.print_no, do.box_no, do.source_platform_code, 
	do.ext_order_id, do.activity_id from delivery_order do 
where 1=1 AND do.logistics_company_id = 10000 and do.owner_id in ( 'BGSS' , 'DBQ' , 'BSA' , 'ABJ' , 'CAT' , 'DBK' , 'TS' , 'JCX' , 'WCX' , 'FY' , 'WHN' , 'DDG' , 'YM' , 'PTJ' ,
 'MG' , 'WYM' , 'TJ', 'TSMJ' , 'CXCW' , 'MYCW' , 'BK' , 'CXJD' , 'CXTM' , 'QCX' , 'HS' , 'HYB', 'WHMGB2B' ) 
 and do.warehouse in ( 'WHC' , 'QHDC' , 'JKXS' , 'JSC' , 'WHCPC' , 'CXB' , 'WHN' ) 
 AND  do.owner_id IN ('BGSS') 
AND   do.warehouse IN ('WHC')
 AND do.rowstate IN ( 2 , 5 , 6 ) )AS mt LEFT JOIN 
		(SELECT do2.id,do2.part_list,ps.ps_count 
			from delivery_order as do2
 			LEFT JOIN (SELECT part_list,(CASE WHEN (COUNT(*) > 0) THEN COUNT(*) ELSE 0 END) AS ps_count 
			FROM delivery_order WHERE rowstate = 2 AND logistics_company_id = 10000 AND owner_id IN ('BGSS') AND warehouse IN ('WHC') AND rowstate IN ( 2 , 5 , 6 ) GROUP BY part_list) AS ps 
ON do2.part_list = ps.part_list and do2.rowstate = 2 ) as ds ON mt.id = ds.id WHERE 1=1 order by rowstate ASC ,created_date DESC LIMIT 0,30;


explain 一下

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部