TIDB查询优化

以下慢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

执行计划

  • 关键词

您好: 从你返回的执行计划看,走了索引,HQ_KDT_ID和daily,也是在tikv聚合后返回给tidb,感觉执行计划没有什么问题.
麻烦您反馈下建表语句包括索引创建。 反馈下slow-query,tidb中应该有这个日志.(tidb.log日志) 另外麻烦问下,这种sql基本都是在12s吗? 它的执行时间是全天都会执行,还是业务高峰期,在执行的时候,资源上是否有过大的cpu,IO,等?

您好,现在是验证阶段还没跑业务,这种查询都是要12s,只跑这一个SQL,集群资源都很充足,想问下是否有调优方式,还是这个就是TIDB的最佳性能了。建表语句如下

CREATE TABLE `retail_sku_uniservice_version3` (
  `daily` bigint(20) DEFAULT NULL,
  `weekly` bigint(20) DEFAULT NULL,
  `monthly` bigint(20) DEFAULT NULL,
  `quaterly` bigint(20) DEFAULT NULL,
  `hq_kdt_id` bigint(20) DEFAULT NULL,
  `kdt_id` bigint(20) DEFAULT NULL,
  `channel` varchar(16) DEFAULT NULL,
  `sku_id` bigint(20) DEFAULT NULL,
  `sku_class` bigint(20) DEFAULT NULL,
  `ext_type` bigint(20) DEFAULT NULL,
  `ext_enum` bigint(20) DEFAULT NULL,
  `sold_sku_num` bigint(20) DEFAULT NULL,
  `paid_order_amt` bigint(20) DEFAULT NULL,
  `discount_amt` bigint(20) DEFAULT NULL,
  `refund_num` bigint(20) DEFAULT NULL,
  `refund_amt` bigint(20) DEFAULT NULL,
  `refund_items_amt` bigint(20) DEFAULT NULL,
  `net_sales` bigint(20) DEFAULT NULL,
  `estimate_inv_cost` bigint(20) DEFAULT NULL,
  `estimate_gross_profit` bigint(20) DEFAULT NULL
  `begin_inv_cost` bigint(20) DEFAULT NULL
  `begin_inv_num` bigint(20) DEFAULT NULL
  `end_inv_cost` bigint(20) DEFAULT NULL
  `end_inv_num` bigint(20) DEFAULT NULL
  `purchase_in_cost` bigint(20) DEFAULT NULL
  `purchase_in_num` bigint(20) DEFAULT NULL
  `stock_allot_in_cost` bigint(20) DEFAULT NULL
  `stock_allot_in_num` bigint(20) DEFAULT NULL
  `sales_return_num` bigint(20) DEFAULT NULL
  `sales_return_amt` bigint(20) DEFAULT NULL
  `sales_return_tax_excluded_amt` bigint(20) DEFAULT NULL
  `sales_return_cost` bigint(20) DEFAULT NULL
  `sales_return_tax_excluded_cost` bigint(20) DEFAULT NULL
  `stock_check_in_cost` bigint(20) DEFAULT NULL
  `stock_check_in_num` bigint(20) DEFAULT NULL
  `stock_check_out_cost` bigint(20) DEFAULT NULL
  `stock_check_out_num` bigint(20) DEFAULT NULL
  `sales_out_num` bigint(20) DEFAULT NULL
  `sales_out_amt` bigint(20) DEFAULT NULL
  `sales_out_tax_excluded_amt` bigint(20) DEFAULT NULL
  `sales_out_cost` bigint(20) DEFAULT NULL
  `sales_out_tax_excluded_cost` bigint(20) DEFAULT NULL
  `stock_allot_out_cost` bigint(20) DEFAULT NULL
  `stock_allot_out_num` bigint(20) DEFAULT NULL
  `delivery_in_num` bigint(20) DEFAULT NULL
  `delivery_in_cost` bigint(20) DEFAULT NULL
  `delivery_out_num` bigint(20) DEFAULT NULL
  `delivery_out_cost` bigint(20) DEFAULT NULL
  `other_out_loss_cost` bigint(20) DEFAULT NULL
  `other_out_loss_num` bigint(20) DEFAULT NULL
  `other_out_use_cost` bigint(20) DEFAULT NULL
  `other_out_use_num` bigint(20) DEFAULT NULL
  `other_out_other_cost` bigint(20) DEFAULT NULL
  `other_out_other_num` bigint(20) DEFAULT NULL
  `cost_change_cost` bigint(20) DEFAULT NULL
  `logistics_delivery_order_num` bigint(20) DEFAULT NULL
  `logistics_delivery_goods_num` bigint(20) DEFAULT NULL
  `logistics_delivery_order_amt` bigint(20) DEFAULT NULL
  `self_pick_up_order_num` bigint(20) DEFAULT NULL
  `self_pick_up_goods_num` bigint(20) DEFAULT NULL
  `self_pick_up_order_amt` bigint(20) DEFAULT NULL
  `purchase_in_tax` bigint(20) DEFAULT NULL
  `purchase_in_without_tax_amt` bigint(20) DEFAULT NULL
  `purchase_out_num` bigint(20) DEFAULT NULL
  `purchase_out_amt` bigint(20) DEFAULT NULL
  `purchase_out_tax` bigint(20) DEFAULT NULL
  `purchase_out_without_tax_amt` bigint(20) DEFAULT NULL
  `net_purchase_num` bigint(20) DEFAULT NULL
  `net_purchase_amt` bigint(20) DEFAULT NULL
  `sales_inv_amt` bigint(20) DEFAULT NULL
  `sales_inv_cost` bigint(20) DEFAULT NULL
  `stock_check_net_num` bigint(20) DEFAULT NULL
  `stock_check_net_amt` bigint(20) DEFAULT NULL
  `vendor_purchase_in_num` bigint(20) DEFAULT NULL
  `vendor_purchase_in_amt` bigint(20) DEFAULT NULL
  `vendor_purchase_in_tax` bigint(20) DEFAULT NULL
  `vendor_purchase_in_without_tax_amt` bigint(20) DEFAULT NULL
  `vendor_purchase_out_num` bigint(20) DEFAULT NULL
  `vendor_purchase_out_amt` bigint(20) DEFAULT NULL
  `vendor_purchase_out_tax` bigint(20) DEFAULT NULL
  `vendor_purchase_out_without_tax_amt` bigint(20) DEFAULT NULL
  `vendor_net_purchase_num` bigint(20) DEFAULT NULL
  `vendor_net_purchase_amt` bigint(20) DEFAULT NULL
  `stock_check_num` bigint(20) DEFAULT NULL
  `stock_check_cost` bigint(20) DEFAULT NULL
  `avg_inv_cost` bigint(20) DEFAULT NULL
  `stock_turnover_rate` bigint(20) DEFAULT NULL
  `stock_turnover_day` bigint(20) DEFAULT NULL
  `sales_rate` bigint(20) DEFAULT NULL
  `sku_gross_margin` bigint(20) DEFAULT NULL
  `sku_gross_margin_rate` bigint(20) DEFAULT NULL
  KEY `idx_daily` (`hq_kdt_id`,`daily`),
  KEY `idx_weekly` (`hq_kdt_id`,`weekly`),
  KEY `idx_monthly` (`hq_kdt_id`,`monthly`),
  KEY `idx_quaterly` (`hq_kdt_id`,`quaterly`),
  KEY `stock_check_net_num` (`stock_check_net_num`)
) shard_row_id_bits = 5 pre_split_regions=4;

麻烦反馈下统计信息,谢谢.

表元数据:

列元数据: 列元数据.txt (165.6 KB) 直方图桶的元数据: 导出的统计信息: 文件大小超出限制,百度网盘链接: 链接:https://pan.baidu.com/s/1_SJuXVFGvNtz2Xg_TsKMDw 密码:7g97 感谢~

你好: 1. 先执行sql,然后立即执行此命令,取出profile文件,谢谢. curl -G “172.16.5.89:10680/debug/pprof/profile?seconds=100” > /tmp/cpu.profile 请取一下profile文件

    2.  尝试调大以下参数,查看是否有用:
          https://pingcap.com/docs-cn/v2.1/reference/configuration/tidb-server/tidb-specific-variables/#tidb_hashagg_partial_concurrency
         tidb_hashagg_partial_concurrency和tidb_hashagg_final_concurrency

你好:

1:profile 文件:cpu.profile (26.8 KB)

2:我在client session中 执行了以下两个语句,尝试过10/20两个值,查询速度还是差不多。

set tidb_hashagg_partial_concurrency=10;
set tidb_hashagg_final_concurrency=10;

你好,在 cpu.profile 文件里面没有抓到相应的语句 。 麻烦在 “172.16.5.89:10680/debug/pprof/profile?seconds=100” > /tmp/cpu.profile 的过程中手动运行一下问题语句。重新传一份新的 profile 文件。

你好: 抱歉,这里我没有写清楚,请把ip和端口换为你自己的ip和端口,端口是inventory中配置的tidb_status_port,谢谢.

你好,昨天我的操作是: “先执行sql,然后立即执行此命令,取出profile文件”

现在改为: 在获取profile的过程中执行SQL,新的profile文件: cpu.profile (27.6 KB)

你好:已重新抓取profile,如上

你好: 这个问题已经请研发同事帮忙查看,他们在profile中没有发现查询的sql. 研发同事也确认执行计划是没有问题的. 还要麻烦您多抓取几次profile,谢谢. 先执行此语句,再执行sql(减少profile抓取时间,保证sql在这个时间完成即可,麻烦多抓取几个profile吧) curl -G “xxx.xx.x.xx:xxx/debug/pprof/profile?seconds=20” > /tmp/cpu1.profile —>替换为你的ip和端口

你好,按照20s 和 25s 和 30s 抓了三个profile

cpu-20.profile (3.8 KB)

cpu-25.profile (4.3 KB)

cpu-30.profile (21.3 KB)

好的,已经提交研发帮忙查看,谢谢

您好: 研发同事帮忙查看后,还是没有这个sql的信息. 都是GC 或者统计信息. 研发建议:用户抓的时候,用个 shell 脚本循环跑 用户的查询

您好,这一次抓到的比较大,看看有没有,麻烦了

cpu-25-new.profile (66.8 KB)

cpu-20-new.profile (49.2 KB)

@nicksoul 可以用 explain analyze select … 看下执行的结果吗?

@nicksoul BTW, 这个查询在相同数据量下, mysql 上要跑多久,有测过吗?

没测过,因为想着不可能用MySQL

执行计划