Mysql-报表查询优化提升10倍-
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Mysql 报表查询分享(提升10倍)
技术部-陈远
2015年5月14日
优化前需要330秒，优化后只需要25秒
优化前：
优化前需要330秒，优化后只需要25秒
优化后：
Βιβλιοθήκη Baidu
菜品销售汇总查询优化 • 由于这个查询非常的大，实际上是两个查 询union，两个查询相似，实际我们分析的 时候只需要抽取其中一个子查询即可：
SELECT DishId,BigTypeName,SmallTypeName,DishSerial,DishName,DishPortionsName, DishPrice,SUM(SalesNum) AS SalesNum,SUM(DishSalesAmount+CookingPrice) AS SalesAmount, SUM(CookingPrice) AS CookingPrice, SUM(DiscountAmount) AS DiscountAmount,DishPortionsId, SUM(SingleCount) AS SingleCount, SUM(packageCount) AS packageCount FROM ( SELECT * FROM ( SELECT a.`dish_id` AS DishId,d.`dish_type_name` AS BigTypeName,c.`dish_type_name` AS SmallTypeName, a.dish_serial AS DishSerial,CASE WHEN b.is_temporary =1 THEN CONCAT('(临)',a.dish_name) ELSE a.dish_name END AS DishName,f.`dish_portions_name` AS DishPortionsName, a.`dish_price` AS DishPrice,(a.`dish_num`-a.dish_return_count) AS SalesNum,(IF(a.is_free_dish=1,0,a.dish_price) * (a.`dish_num`-a.dish_return_count)) AS DishSalesAmount,IF(a.is_free_dish=1,0,a.`actual_cooking_price`) AS CookingPrice, (IF(a.is_free_dish=1,0,((a.dish_price) * (a.`dish_num`-a.dish_return_count) + a.`actual_cooking_price`)*a.discount/100)) AS DiscountAmount,a.dish_portions_id AS DishPortionsId, (CASE WHEN a.is_package_dish=0 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS SingleCount, (CASE WHEN a.is_package_dish=1 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS packageCount FROM order_dish a JOIN `dish` b ON b.`dish_id` = a.`dish_id` JOIN `dish_type` c ON c.`dish_type_id` = b.`dish_type_id` JOIN `dish_type` d ON d.`dish_type_id` = c.`parent_id` JOIN `dish_portions` f ON f.`dish_portions_id` = a.`dish_portions_id` JOIN dining_order g ON g.order_id = a.dining_order_id WHERE a.status_code = 1 AND c.status_code = 1 AND IFNULL(a.`return_order_dish_id`,'') = '' AND g.order_status=99 AND ((a.is_package_dish=0) OR ((a.is_package_dish=1) AND a.belong_order_dish_id>'')) AND a.`dish_num`<>a.dish_return_count AND a.`business_date` BETWEEN '2015-02-20' AND '2015-03-20 23:59:59' ) a UNION ALL SELECT * FROM ( SELECT a.`dish_id` AS DishId,d.`dish_type_name` AS BigTypeName,c.`dish_type_name` AS SmallTypeName, a.dish_serial AS DishSerial,CASE WHEN b.is_temporary =1 THEN CONCAT('(临)',a.dish_name) ELSE a.dish_name END AS DishName,f.`dish_portions_name` AS DishPortionsName, a.`dish_price` AS DishPrice,(a.`dish_num`-a.dish_return_count) AS SalesNum,(IF(a.is_free_dish=1,0,a.dish_price) * (a.`dish_num`-a.dish_return_count)) AS DishSalesAmount,IF(a.is_free_dish=1,0,a.`actual_cooking_price`) AS CookingPrice, (IF(a.is_free_dish=1,0,((a.dish_price) * (a.`dish_num`-a.dish_return_count) + a.`actual_cooking_price`)*a.discount/100)) AS DiscountAmount,a.dish_portions_id AS DishPortionsId, (CASE WHEN a.is_package_dish=0 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS SingleCount, (CASE WHEN a.is_package_dish=1 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS packageCount FROM order_dish_history a USE INDEX (idx_order_dish_history_business_date) JOIN `dish` b ON b.`dish_id` = a.`dish_id` JOIN `dish_type` c ON c.`dish_type_id` = b.`dish_type_id` JOIN `dish_type` d ON d.`dish_type_id` = c.`parent_id` JOIN `dish_portions` f ON f.`dish_portions_id` = a.`dish_portions_id` JOIN dining_order_history g ON g.order_id = a.dining_order_id WHERE a.status_code = 1 AND c.status_code = 1 AND IFNULL(a.`return_order_dish_id`,'') = '' AND g.order_status=99 AND ((a.is_package_dish=0) OR ((a.is_package_dish=1) AND a.belong_order_dish_id>'')) AND a.`dish_num`<>a.dish_return_count AND a.`business_date` BETWEEN '2015-02-20' AND '2015-03-20 23:59:59' ) a )a GROUP BY DishId,a.DishPortionsId,DishPrice ;
SELECT a.`dish_id` AS DishId,d.`dish_type_name` AS BigTypeName,c.`dish_type_name` AS SmallTypeName, a.dish_serial AS DishSerial,CASE WHEN b.is_temporary =1 THEN CONCAT('(临)',a.dish_name) ELSE a.dish_name END AS DishName,f.`dish_portions_name` AS DishPortionsName, a.`dish_price` AS DishPrice,(a.`dish_num`-a.dish_return_count) AS SalesNum,(IF(a.is_free_dish=1,0,a.dish_price) * (a.`dish_num`-a.dish_return_count)) AS DishSalesAmount,IF(a.is_free_dish=1,0,a.`actual_cooking_price`) AS CookingPrice, (IF(a.is_free_dish=1,0,((a.dish_price) * (a.`dish_num`-a.dish_return_count) + a.`actual_cooking_price`)*a.discount/100)) AS DiscountAmount,a.dish_portions_id AS DishPortionsId, (CASE WHEN a.is_package_dish=0 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS SingleCount, (CASE WHEN a.is_package_dish=1 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS packageCount FROM order_dish_history a JOIN `dish` b ON b.`dish_id` = a.`dish_id` JOIN `dish_type` c ON c.`dish_type_id` = b.`dish_type_id` JOIN `dish_type` d ON d.`dish_type_id` = c.`parent_id` JOIN `dish_portions` f ON f.`dish_portions_id` = a.`dish_portions_id` JOIN dining_order_history g ON g.order_id = a.dining_order_id WHERE a.status_code = 1 AND c.status_code = 1 AND IFNULL(a.`return_order_dish_id`,'') = '' AND g.order_status=99 AND ((a.is_package_dish=0) OR ((a.is_package_dish=1) AND a.belong_order_dish_id>'')) AND a.`dish_num`<>a.dish_return_count AND a.`business_date` BETWEEN '2015-02-20' AND '2015-03-20 23:59:59'
技术部-陈远
2015年5月14日
优化前需要330秒，优化后只需要25秒
优化前：
优化前需要330秒，优化后只需要25秒
优化后：
Βιβλιοθήκη Baidu
菜品销售汇总查询优化 • 由于这个查询非常的大，实际上是两个查 询union，两个查询相似，实际我们分析的 时候只需要抽取其中一个子查询即可：
SELECT DishId,BigTypeName,SmallTypeName,DishSerial,DishName,DishPortionsName, DishPrice,SUM(SalesNum) AS SalesNum,SUM(DishSalesAmount+CookingPrice) AS SalesAmount, SUM(CookingPrice) AS CookingPrice, SUM(DiscountAmount) AS DiscountAmount,DishPortionsId, SUM(SingleCount) AS SingleCount, SUM(packageCount) AS packageCount FROM ( SELECT * FROM ( SELECT a.`dish_id` AS DishId,d.`dish_type_name` AS BigTypeName,c.`dish_type_name` AS SmallTypeName, a.dish_serial AS DishSerial,CASE WHEN b.is_temporary =1 THEN CONCAT('(临)',a.dish_name) ELSE a.dish_name END AS DishName,f.`dish_portions_name` AS DishPortionsName, a.`dish_price` AS DishPrice,(a.`dish_num`-a.dish_return_count) AS SalesNum,(IF(a.is_free_dish=1,0,a.dish_price) * (a.`dish_num`-a.dish_return_count)) AS DishSalesAmount,IF(a.is_free_dish=1,0,a.`actual_cooking_price`) AS CookingPrice, (IF(a.is_free_dish=1,0,((a.dish_price) * (a.`dish_num`-a.dish_return_count) + a.`actual_cooking_price`)*a.discount/100)) AS DiscountAmount,a.dish_portions_id AS DishPortionsId, (CASE WHEN a.is_package_dish=0 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS SingleCount, (CASE WHEN a.is_package_dish=1 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS packageCount FROM order_dish a JOIN `dish` b ON b.`dish_id` = a.`dish_id` JOIN `dish_type` c ON c.`dish_type_id` = b.`dish_type_id` JOIN `dish_type` d ON d.`dish_type_id` = c.`parent_id` JOIN `dish_portions` f ON f.`dish_portions_id` = a.`dish_portions_id` JOIN dining_order g ON g.order_id = a.dining_order_id WHERE a.status_code = 1 AND c.status_code = 1 AND IFNULL(a.`return_order_dish_id`,'') = '' AND g.order_status=99 AND ((a.is_package_dish=0) OR ((a.is_package_dish=1) AND a.belong_order_dish_id>'')) AND a.`dish_num`<>a.dish_return_count AND a.`business_date` BETWEEN '2015-02-20' AND '2015-03-20 23:59:59' ) a UNION ALL SELECT * FROM ( SELECT a.`dish_id` AS DishId,d.`dish_type_name` AS BigTypeName,c.`dish_type_name` AS SmallTypeName, a.dish_serial AS DishSerial,CASE WHEN b.is_temporary =1 THEN CONCAT('(临)',a.dish_name) ELSE a.dish_name END AS DishName,f.`dish_portions_name` AS DishPortionsName, a.`dish_price` AS DishPrice,(a.`dish_num`-a.dish_return_count) AS SalesNum,(IF(a.is_free_dish=1,0,a.dish_price) * (a.`dish_num`-a.dish_return_count)) AS DishSalesAmount,IF(a.is_free_dish=1,0,a.`actual_cooking_price`) AS CookingPrice, (IF(a.is_free_dish=1,0,((a.dish_price) * (a.`dish_num`-a.dish_return_count) + a.`actual_cooking_price`)*a.discount/100)) AS DiscountAmount,a.dish_portions_id AS DishPortionsId, (CASE WHEN a.is_package_dish=0 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS SingleCount, (CASE WHEN a.is_package_dish=1 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS packageCount FROM order_dish_history a USE INDEX (idx_order_dish_history_business_date) JOIN `dish` b ON b.`dish_id` = a.`dish_id` JOIN `dish_type` c ON c.`dish_type_id` = b.`dish_type_id` JOIN `dish_type` d ON d.`dish_type_id` = c.`parent_id` JOIN `dish_portions` f ON f.`dish_portions_id` = a.`dish_portions_id` JOIN dining_order_history g ON g.order_id = a.dining_order_id WHERE a.status_code = 1 AND c.status_code = 1 AND IFNULL(a.`return_order_dish_id`,'') = '' AND g.order_status=99 AND ((a.is_package_dish=0) OR ((a.is_package_dish=1) AND a.belong_order_dish_id>'')) AND a.`dish_num`<>a.dish_return_count AND a.`business_date` BETWEEN '2015-02-20' AND '2015-03-20 23:59:59' ) a )a GROUP BY DishId,a.DishPortionsId,DishPrice ;
SELECT a.`dish_id` AS DishId,d.`dish_type_name` AS BigTypeName,c.`dish_type_name` AS SmallTypeName, a.dish_serial AS DishSerial,CASE WHEN b.is_temporary =1 THEN CONCAT('(临)',a.dish_name) ELSE a.dish_name END AS DishName,f.`dish_portions_name` AS DishPortionsName, a.`dish_price` AS DishPrice,(a.`dish_num`-a.dish_return_count) AS SalesNum,(IF(a.is_free_dish=1,0,a.dish_price) * (a.`dish_num`-a.dish_return_count)) AS DishSalesAmount,IF(a.is_free_dish=1,0,a.`actual_cooking_price`) AS CookingPrice, (IF(a.is_free_dish=1,0,((a.dish_price) * (a.`dish_num`-a.dish_return_count) + a.`actual_cooking_price`)*a.discount/100)) AS DiscountAmount,a.dish_portions_id AS DishPortionsId, (CASE WHEN a.is_package_dish=0 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS SingleCount, (CASE WHEN a.is_package_dish=1 THEN a.`dish_num`-a.dish_return_count ELSE 0 END ) AS packageCount FROM order_dish_history a JOIN `dish` b ON b.`dish_id` = a.`dish_id` JOIN `dish_type` c ON c.`dish_type_id` = b.`dish_type_id` JOIN `dish_type` d ON d.`dish_type_id` = c.`parent_id` JOIN `dish_portions` f ON f.`dish_portions_id` = a.`dish_portions_id` JOIN dining_order_history g ON g.order_id = a.dining_order_id WHERE a.status_code = 1 AND c.status_code = 1 AND IFNULL(a.`return_order_dish_id`,'') = '' AND g.order_status=99 AND ((a.is_package_dish=0) OR ((a.is_package_dish=1) AND a.belong_order_dish_id>'')) AND a.`dish_num`<>a.dish_return_count AND a.`business_date` BETWEEN '2015-02-20' AND '2015-03-20 23:59:59'