以下慢SQL是否可以优化,相同场景在Kylin耗时8s, TIDB耗时12s
-
【系统版本 & kernel 版本】centos7.3
-
【TiDB 版本】2.1.17
-
【磁盘型号】SSD
-
【集群节点分布】
节点1(376G,48核):TIDB-Server,PD,2 * TIKV
节点2(376G,48核):TIDB-Server,PD,2 * TIKV
节点3(376G,48核):TIKV * 7
-
【数据量 & region 数量 & 副本数】
15亿, region数量
Leaders Distribution: total leader count: 6634 store: 1, num_leaders: 725, percentage: 10.93% store: 128002, num_leaders: 369, percentage: 5.56% store: 128003, num_leaders: 385, percentage: 5.80% store: 128004, num_leaders: 424, percentage: 6.39% store: 128678, num_leaders: 331, percentage: 4.99% store: 7, num_leaders: 726, percentage: 10.94% store: 128680, num_leaders: 387, percentage: 5.83% store: 9, num_leaders: 626, percentage: 9.44% store: 10, num_leaders: 610, percentage: 9.20% store: 128679, num_leaders: 310, percentage: 4.67% store: 8, num_leaders: 776, percentage: 11.70% store: 128001, num_leaders: 363, percentage: 5.47% store: 4, num_leaders: 602, percentage: 9.07% [RECORD - test.RETAIL_SKU_UNISERVICE_VERSION3] - Peers Distribution: total peers count: 19903 store: 1, num_peers: 2828, percentage: 14.21% store: 128002, num_peers: 478, percentage: 2.40% store: 128003, num_peers: 479, percentage: 2.41% store: 4, num_peers: 2583, percentage: 12.98% store: 128678, num_peers: 423, percentage: 2.13% store: 7, num_peers: 2893, percentage: 14.54% store: 128680, num_peers: 489, percentage: 2.46% store: 9, num_peers: 2575, percentage: 12.94% store: 10, num_peers: 2681, percentage: 13.47% store: 128679, num_peers: 415, percentage: 2.09% store: 8, num_peers: 3080, percentage: 15.48% store: 128001, num_peers: 470, percentage: 2.36% store: 128004, num_peers: 509, percentage: 2.56% [INDEX - idx_daily] - Leaders Distribution: total leader count: 1531 store: 128001, num_leaders: 99, percentage: 6.47% store: 128002, num_leaders: 81, percentage: 5.29% store: 128003, num_leaders: 85, percentage: 5.55% store: 4, num_leaders: 177, percentage: 11.56% store: 128678, num_leaders: 76, percentage: 4.96% store: 7, num_leaders: 152, percentage: 9.93% store: 8, num_leaders: 158, percentage: 10.32% store: 9, num_leaders: 199, percentage: 13.00% store: 10, num_leaders: 150, percentage: 9.80% store: 128679, num_leaders: 69, percentage: 4.51% store: 128680, num_leaders: 112, percentage: 7.32% store: 1, num_leaders: 89, percentage: 5.81% store: 128004, num_leaders: 84, percentage: 5.49% [INDEX - idx_weekly] - Leaders Distribution: total leader count: 1490 store: 1, num_leaders: 83, percentage: 5.57% store: 128002, num_leaders: 74, percentage: 4.97% store: 128003, num_leaders: 87, percentage: 5.84% store: 4, num_leaders: 141, percentage: 9.46% store: 128678, num_leaders: 83, percentage: 5.57% store: 7, num_leaders: 172, percentage: 11.54% store: 128680, num_leaders: 92, percentage: 6.17% store: 9, num_leaders: 135, percentage: 9.06% store: 10, num_leaders: 198, percentage: 13.29% store: 128679, num_leaders: 63, percentage: 4.23% store: 8, num_leaders: 166, percentage: 11.14% store: 128001, num_leaders: 98, percentage: 6.58% store: 128004, num_leaders: 98, percentage: 6.58% [INDEX - idx_monthly] - Leaders Distribution: total leader count: 1490 store: 128001, num_leaders: 80, percentage: 5.37% store: 128002, num_leaders: 72, percentage: 4.83% store: 128003, num_leaders: 82, percentage: 5.50% store: 128004, num_leaders: 86, percentage: 5.77% store: 128678, num_leaders: 86, percentage: 5.77% store: 1, num_leaders: 83, percentage: 5.57% store: 128680, num_leaders: 94, percentage: 6.31% store: 9, num_leaders: 182, percentage: 12.21% store: 10, num_leaders: 169, percentage: 11.34% store: 128679, num_leaders: 69, percentage: 4.63% store: 8, num_leaders: 148, percentage: 9.93% store: 7, num_leaders: 162, percentage: 10.87% store: 4, num_leaders: 177, percentage: 11.88% [INDEX - idx_quaterly] - Leaders Distribution: total leader count: 1422 store: 1, num_leaders: 108, percentage: 7.59% store: 128002, num_leaders: 88, percentage: 6.19% store: 128003, num_leaders: 77, percentage: 5.41% store: 128004, num_leaders: 83, percentage: 5.84% store: 128678, num_leaders: 70, percentage: 4.92% store: 128679, num_leaders: 78, percentage: 5.49% store: 8, num_leaders: 170, percentage: 11.95% store: 9, num_leaders: 147, percentage: 10.34% store: 10, num_leaders: 137, percentage: 9.63% store: 7, num_leaders: 151, percentage: 10.62% store: 128680, num_leaders: 80, percentage: 5.63% store: 128001, num_leaders: 70, percentage: 4.92% store: 4, num_leaders: 163, percentage: 11.46% [INDEX - stock_check_net_num] - Leaders Distribution: total leader count: 1407 store: 128001, num_leaders: 87, percentage: 6.18% store: 128002, num_leaders: 79, percentage: 5.61% store: 128003, num_leaders: 70, percentage: 4.98% store: 128004, num_leaders: 79, percentage: 5.61% store: 128678, num_leaders: 65, percentage: 4.62% store: 128679, num_leaders: 78, percentage: 5.54% store: 128680, num_leaders: 69, percentage: 4.90% store: 9, num_leaders: 268, percentage: 19.05% store: 10, num_leaders: 291, percentage: 20.68% store: 7, num_leaders: 17, percentage: 1.21% store: 8, num_leaders: 17, percentage: 1.21% store: 1, num_leaders: 11, percentage: 0.78% store: 4, num_leaders: 276, percentage: 19.62% [INDEX - idx_daily] - Peers Distribution: total peers count: 4593 store: 128001, num_peers: 124, percentage: 2.70% store: 128002, num_peers: 111, percentage: 2.42% store: 128003, num_peers: 104, percentage: 2.26% store: 4, num_peers: 628, percentage: 13.67% store: 128678, num_peers: 89, percentage: 1.94% store: 7, num_peers: 658, percentage: 14.33% store: 8, num_peers: 582, percentage: 12.67% store: 9, num_peers: 687, percentage: 14.96% store: 10, num_peers: 540, percentage: 11.76% store: 128679, num_peers: 90, percentage: 1.96% store: 128680, num_peers: 131, percentage: 2.85% store: 1, num_peers: 740, percentage: 16.11% store: 128004, num_peers: 109, percentage: 2.37% [INDEX - idx_weekly] - Peers Distribution: total peers count: 4470 store: 1, num_peers: 640, percentage: 14.32% store: 128002, num_peers: 92, percentage: 2.06% store: 128003, num_peers: 102, percentage: 2.28% store: 4, num_peers: 548, percentage: 12.26% store: 128678, num_peers: 108, percentage: 2.42% store: 128679, num_peers: 83, percentage: 1.86% store: 8, num_peers: 642, percentage: 14.36% store: 9, num_peers: 521, percentage: 11.66% store: 10, num_peers: 716, percentage: 16.02% store: 7, num_peers: 668, percentage: 14.94% store: 128680, num_peers: 111, percentage: 2.48% store: 128001, num_peers: 120, percentage: 2.68% store: 128004, num_peers: 119, percentage: 2.66% [INDEX - idx_monthly] - Peers Distribution: total peers count: 4470 store: 1, num_peers: 640, percentage: 14.32% store: 128002, num_peers: 88, percentage: 1.97% store: 128003, num_peers: 106, percentage: 2.37% store: 4, num_peers: 646, percentage: 14.45% store: 128678, num_peers: 108, percentage: 2.42% store: 128679, num_peers: 82, percentage: 1.83% store: 128680, num_peers: 109, percentage: 2.44% store: 9, num_peers: 640, percentage: 14.32% store: 10, num_peers: 602, percentage: 13.47% store: 7, num_peers: 705, percentage: 15.77% store: 8, num_peers: 538, percentage: 12.04% store: 128001, num_peers: 97, percentage: 2.17% store: 128004, num_peers: 109, percentage: 2.44% [INDEX - idx_quaterly] - Peers Distribution: total peers count: 4266 store: 1, num_peers: 632, percentage: 14.81% store: 128002, num_peers: 104, percentage: 2.44% store: 128003, num_peers: 94, percentage: 2.20% store: 128004, num_peers: 105, percentage: 2.46% store: 128678, num_peers: 87, percentage: 2.04% store: 128679, num_peers: 92, percentage: 2.16% store: 8, num_peers: 598, percentage: 14.02% store: 9, num_peers: 604, percentage: 14.16% store: 10, num_peers: 507, percentage: 11.88% store: 7, num_peers: 645, percentage: 15.12% store: 128680, num_peers: 101, percentage: 2.37% store: 128001, num_peers: 83, percentage: 1.95% store: 4, num_peers: 614, percentage: 14.39%
-
【问题描述(我做了什么)】
执行以下SQL 语句,耗时12s:
SELECT
test_tbl.HQ_KDT_ID as test_tbl_HQ_KDT_ID
,test_tbl.KDT_ID as test_tbl_KDT_ID
,test_tbl.SKU_ID as test_tbl_SKU_ID
,SUM(test_tbl.SOLD_SKU_NUM ) as test_tbl_SOLD_SKU_NUM
,SUM(test_tbl.PAID_ORDER_AMT ) as test_tbl_PAID_ORDER_AMT
,SUM(test_tbl.DISCOUNT_AMT ) as test_tbl_DISCOUNT_AMT
,SUM(test_tbl.REFUND_NUM ) as test_tbl_REFUND_NUM
,SUM(test_tbl.REFUND_AMT ) as test_tbl_REFUND_AMT
,SUM(test_tbl.REFUND_ITEMS_AMT ) as test_tbl_REFUND_ITEMS_AMT
,SUM(test_tbl.NET_SALES ) as test_tbl_NET_SALES
,SUM(test_tbl.ESTIMATE_INV_COST ) as test_tbl_ESTIMATE_INV_COST
,SUM(test_tbl.ESTIMATE_GROSS_PROFIT ) as test_tbl_ESTIMATE_GROSS_PROFIT
,SUM(test_tbl.BEGIN_INV_COST ) as test_tbl_BEGIN_INV_COST
,SUM(test_tbl.BEGIN_INV_NUM ) as test_tbl_BEGIN_INV_NUM
,SUM(test_tbl.END_INV_COST ) as test_tbl_END_INV_COST
,SUM(test_tbl.END_INV_NUM ) as test_tbl_END_INV_NUM
,SUM(test_tbl.PURCHASE_IN_COST ) as test_tbl_PURCHASE_IN_COST
,SUM(test_tbl.PURCHASE_IN_NUM ) as test_tbl_PURCHASE_IN_NUM
,SUM(test_tbl.STOCK_ALLOT_IN_COST ) as test_tbl_STOCK_ALLOT_IN_COST
,SUM(test_tbl.STOCK_ALLOT_IN_NUM ) as test_tbl_STOCK_ALLOT_IN_NUM
,SUM(test_tbl.SALES_RETURN_NUM ) as test_tbl_SALES_RETURN_NUM
,SUM(test_tbl.SALES_RETURN_AMT ) as test_tbl_SALES_RETURN_AMT
,SUM(test_tbl.SALES_RETURN_TAX_EXCLUDED_AMT ) as test_tbl_SALES_RETURN_TAX_EXCLUDED_AMT
,SUM(test_tbl.SALES_RETURN_COST ) as test_tbl_SALES_RETURN_COST
,SUM(test_tbl.SALES_RETURN_TAX_EXCLUDED_COST ) as test_tbl_SALES_RETURN_TAX_EXCLUDED_COST
,SUM(test_tbl.STOCK_CHECK_IN_COST ) as test_tbl_STOCK_CHECK_IN_COST
,SUM(test_tbl.STOCK_CHECK_IN_NUM ) as test_tbl_STOCK_CHECK_IN_NUM
,SUM(test_tbl.STOCK_CHECK_OUT_COST ) as test_tbl_STOCK_CHECK_OUT_COST
,SUM(test_tbl.STOCK_CHECK_OUT_NUM ) as test_tbl_STOCK_CHECK_OUT_NUM
,SUM(test_tbl.SALES_OUT_NUM ) as test_tbl_SALES_OUT_NUM
,SUM(test_tbl.SALES_OUT_AMT ) as test_tbl_SALES_OUT_AMT
,SUM(test_tbl.SALES_OUT_TAX_EXCLUDED_AMT ) as test_tbl_SALES_OUT_TAX_EXCLUDED_AMT
,SUM(test_tbl.SALES_OUT_COST ) as test_tbl_SALES_OUT_COST
,SUM(test_tbl.SALES_OUT_TAX_EXCLUDED_COST ) as test_tbl_SALES_OUT_TAX_EXCLUDED_COST
,SUM(test_tbl.STOCK_ALLOT_OUT_COST ) as test_tbl_STOCK_ALLOT_OUT_COST
,SUM(test_tbl.STOCK_ALLOT_OUT_NUM ) as test_tbl_STOCK_ALLOT_OUT_NUM
,SUM(test_tbl.DELIVERY_IN_NUM ) as test_tbl_DELIVERY_IN_NUM
,SUM(test_tbl.DELIVERY_IN_COST ) as test_tbl_DELIVERY_IN_COST
,SUM(test_tbl.DELIVERY_OUT_NUM ) as test_tbl_DELIVERY_OUT_NUM
,SUM(test_tbl.DELIVERY_OUT_COST ) as test_tbl_DELIVERY_OUT_COST
,SUM(test_tbl.OTHER_OUT_LOSS_COST ) as test_tbl_OTHER_OUT_LOSS_COST
,SUM(test_tbl.OTHER_OUT_LOSS_NUM ) as test_tbl_OTHER_OUT_LOSS_NUM
,SUM(test_tbl.OTHER_OUT_USE_COST ) as test_tbl_OTHER_OUT_USE_COST
,SUM(test_tbl.OTHER_OUT_USE_NUM ) as test_tbl_OTHER_OUT_USE_NUM
,SUM(test_tbl.OTHER_OUT_OTHER_COST ) as test_tbl_OTHER_OUT_OTHER_COST
,SUM(test_tbl.OTHER_OUT_OTHER_NUM ) as test_tbl_OTHER_OUT_OTHER_NUM
,SUM(test_tbl.COST_CHANGE_COST ) as test_tbl_COST_CHANGE_COST
,SUM(test_tbl.LOGISTICS_DELIVERY_ORDER_NUM ) as test_tbl_LOGISTICS_DELIVERY_ORDER_NUM
,SUM(test_tbl.LOGISTICS_DELIVERY_GOODS_NUM ) as test_tbl_LOGISTICS_DELIVERY_GOODS_NUM
,SUM(test_tbl.LOGISTICS_DELIVERY_ORDER_AMT ) as test_tbl_LOGISTICS_DELIVERY_ORDER_AMT
,SUM(test_tbl.SELF_PICK_UP_ORDER_NUM ) as test_tbl_SELF_PICK_UP_ORDER_NUM
,SUM(test_tbl.SELF_PICK_UP_GOODS_NUM ) as test_tbl_SELF_PICK_UP_GOODS_NUM
,SUM(test_tbl.SELF_PICK_UP_ORDER_AMT ) as test_tbl_SELF_PICK_UP_ORDER_AMT
,SUM(test_tbl.PURCHASE_IN_TAX ) as test_tbl_PURCHASE_IN_TAX
,SUM(test_tbl.PURCHASE_IN_WITHOUT_TAX_AMT ) as test_tbl_PURCHASE_IN_WITHOUT_TAX_AMT
,SUM(test_tbl.PURCHASE_OUT_NUM ) as test_tbl_PURCHASE_OUT_NUM
,SUM(test_tbl.PURCHASE_OUT_AMT ) as test_tbl_PURCHASE_OUT_AMT
,SUM(test_tbl.PURCHASE_OUT_TAX ) as test_tbl_PURCHASE_OUT_TAX
,SUM(test_tbl.PURCHASE_OUT_WITHOUT_TAX_AMT ) as test_tbl_PURCHASE_OUT_WITHOUT_TAX_AMT
,SUM(test_tbl.NET_PURCHASE_NUM ) as test_tbl_NET_PURCHASE_NUM
,SUM(test_tbl.NET_PURCHASE_AMT ) as test_tbl_NET_PURCHASE_AMT
,SUM(test_tbl.SALES_INV_AMT ) as test_tbl_SALES_INV_AMT
,SUM(test_tbl.SALES_INV_COST ) as test_tbl_SALES_INV_COST
,SUM(test_tbl.STOCK_CHECK_NET_NUM ) as test_tbl_STOCK_CHECK_NET_NUM
,SUM(test_tbl.STOCK_CHECK_NET_AMT ) as test_tbl_STOCK_CHECK_NET_AMT
,SUM(test_tbl.VENDOR_PURCHASE_IN_NUM ) as test_tbl_VENDOR_PURCHASE_IN_NUM
,SUM(test_tbl.VENDOR_PURCHASE_IN_AMT ) as test_tbl_VENDOR_PURCHASE_IN_AMT
,SUM(test_tbl.VENDOR_PURCHASE_IN_TAX ) as test_tbl_VENDOR_PURCHASE_IN_TAX
,SUM(test_tbl.VENDOR_PURCHASE_IN_WITHOUT_TAX_AMT ) as test_tbl_VENDOR_PURCHASE_IN_WITHOUT_TAX_AMT
,SUM(test_tbl.VENDOR_PURCHASE_OUT_NUM ) as test_tbl_VENDOR_PURCHASE_OUT_NUM
,SUM(test_tbl.VENDOR_PURCHASE_OUT_AMT ) as test_tbl_VENDOR_PURCHASE_OUT_AMT
,SUM(test_tbl.VENDOR_PURCHASE_OUT_TAX ) as test_tbl_VENDOR_PURCHASE_OUT_TAX
,SUM(test_tbl.VENDOR_PURCHASE_OUT_WITHOUT_TAX_AMT ) as test_tbl_VENDOR_PURCHASE_OUT_WITHOUT_TAX_AMT
,SUM(test_tbl.VENDOR_NET_PURCHASE_NUM ) as test_tbl_VENDOR_NET_PURCHASE_NUM
,SUM(test_tbl.VENDOR_NET_PURCHASE_AMT ) as test_tbl_VENDOR_NET_PURCHASE_AMT
,SUM(test_tbl.STOCK_CHECK_NUM) as test_tbl_STOCK_CHECK_NUM
,SUM(test_tbl.STOCK_CHECK_COST ) as test_tbl_STOCK_CHECK_COST
,SUM(test_tbl.AVG_INV_COST ) as test_tbl_AVG_INV_COST
,SUM(test_tbl.STOCK_TURNOVER_RATE ) as test_tbl_STOCK_TURNOVER_RATE
,SUM(test_tbl.STOCK_TURNOVER_DAY ) as test_tbl_STOCK_TURNOVER_DAY
,SUM(test_tbl.SALES_RATE ) as test_tbl_SALES_RATE
,SUM(test_tbl.SKU_GROSS_MARGIN ) as test_tbl_SKU_GROSS_MARGIN
,SUM(test_tbl.SKU_GROSS_MARGIN_RATE ) as test_tbl_SKU_GROSS_MARGIN_RATE
FROM test.test_tbl as test_tbl
where
HQ_KDT_ID = xxxxx
AND
DAILY BETWEEN '20191001' AND '20191010'
GROUP BY
HQ_KDT_ID, KDT_ID, SKU_ID
ORDER BY SUM(test_tbl.PAID_ORDER_AMT) DESC
LIMIT 50000, 1000;
耗时12s
执行计划
- 【关键词】