为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【TiDB 版本】
V4.0.7
【问题描述】
在查询sql 时,全表扫描可以查询完成,但是加上日期条件以后就报超时ERROR 9002 (HY000): TiKV server timeout,
sql如下,
mysql> select branch_code, count(1) ct from (
-> select t.waybill_no,
-> t.branch_code
-> from (select waybill_no,branch_code from t_wide_order4 where branch_code=‘xxxxxxx’ and order_create_time >= date(‘2021-03-20’) and order_create_time < date(‘2021-03-21’)) t
-> left join (select waybill_no from test_all_operation4) w
-> on t.waybill_no = w.waybill_no
-> ) tab
-> group by branch_code
-> order by branch_code;
ERROR 9002 (HY000): TiKV server timeout
mysql> select branch_code, count(1) ct from (
-> select t.waybill_no,
-> t.branch_code
-> from (select waybill_no,branch_code from t_wide_order4 where branch_code=‘xxxxxxx’) t
-> left join (select waybill_no from test_all_operation4) w
-> on t.waybill_no = w.waybill_no
-> ) tab
-> group by branch_code
-> order by branch_code;
成功返回。
表结构如下
CREATE TABLE t_wide_order4
(
waybill_no
varchar(100) NOT NULL COMMENT ‘单号’,
order_channel_code
varchar(100) DEFAULT NULL,
order_create_time
timestamp NOT NULL,
order_logistics_code
varchar(100) DEFAULT NULL,
order_status
varchar(100) DEFAULT NULL,
dispersion_order_flag
varchar(100) DEFAULT NULL,
order_insert_time
timestamp NULL DEFAULT NULL,
sender_prov_code
varchar(100) DEFAULT NULL,
sender_city_code
varchar(100) DEFAULT NULL,
sender_county_code
varchar(100) DEFAULT NULL,
recipient_prov_code
varchar(100) DEFAULT NULL,
recipient_city_code
varchar(100) DEFAULT NULL,
recipient_county_code
varchar(100) DEFAULT NULL,
curtime
varchar(100) DEFAULT NULL,
branch_code
varchar(100) DEFAULT NULL,
seller_id
varchar(100) DEFAULT NULL,
seller_name
varchar(100) DEFAULT NULL,
customer_code
varchar(100) DEFAULT NULL,
company_code
varchar(100) DEFAULT NULL,
action_type
varchar(100) DEFAULT NULL,
is_aliorder
varchar(100) DEFAULT NULL,
datoubi
varchar(100) DEFAULT NULL,
order_chanel
varchar(100) DEFAULT NULL,
yzd_flag
varchar(100) DEFAULT NULL,
PRIMARY KEY (waybill_no
,order_create_time
),
KEY idx_t_wide_order_01
(waybill_no
)
)
若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。