- 【TiDB 版本】：v4.0.0-rc
- 【问题描述】：
生产环境（小型物联网系统）业务大表，已做分区。数据量4kw，有个别表1亿数据量，今天突然查询特别缓慢。
大表有并发插入（tps大概200左右）。
kv节点数：3
kv配置： 16G内存，8核。
临时修改了点代码，3个节点的内存同时扩充到32G。kV部分截图如下，我想问本质问题是什么？下次怎么避免。
你好，
当时执行 sql 可以上传下，并上传下 explain 和 explain analyze 结果
logs (2).tar (4.5 MB)
**(部分节点日志，已上传，2kv,1tidb日志)**由于查询慢时，kv节点的cpu都已打满，大量sql都慢，只选几条有代表性的说明下。现在已经不慢了。但是原因未找到。
一、查询缓慢，小表，t_eqp_truck_box_state：记录400多条（update并发操作此表，）；t_eqp_truck_box ，t_eqp_truck :数据量均不超过2000。
sql:
SELECT
Distinct t.truck_id,
t.truck_box_id,
COALESCE(t.is_online, false) AS online,
t.lat,
t.lng,
t2.plate_number,
COALESCE(t.is_acc_on, false) AS acc_on,
COALESCE(t.is_alarm, false) AS alarm,
COALESCE(t.is_body_working, false) AS body_working,
COALESCE(t.is_stop, false) AS stop,
t2.model
FROM
t_eqp_truck_box_state t
INNER JOIN t_eqp_truck_box t3 ON t.truck_box_id = t3.id
and t3.is_link = true
INNER JOIN t_eqp_truck t2 ON t.truck_id = t2.id
WHERE
(
t.ent_id = 35
AND t.lat IS NOT NULL
AND t.lng IS NOT NULL
AND t2.dep_id IN (0, 191, 187, 186, 175)
)
explain :
|id|estRows|task|access object|operator info|
|---|---|---|---|---|
|HashAgg_14|1.00|root||group by:Column#72, Column#73, Column#74, Column#75, Column#76, coollu.t_eqp_truck.model, coollu.t_eqp_truck.plate_number, coollu.t_eqp_truck_box_state.lat, coollu.t_eqp_truck_box_state.lng, coollu.t_eqp_truck_box_state.truck_box_id, coollu.t_eqp_truck_box_state.truck_id, funcs:firstrow(coollu.t_eqp_truck_box_state.truck_id)->coollu.t_eqp_truck_box_state.truck_id, funcs:firstrow(coollu.t_eqp_truck_box_state.truck_box_id)->coollu.t_eqp_truck_box_state.truck_box_id, funcs:firstrow(Column#72)->Column#72, funcs:firstrow(coollu.t_eqp_truck_box_state.lat)->coollu.t_eqp_truck_box_state.lat, funcs:firstrow(coollu.t_eqp_truck_box_state.lng)->coollu.t_eqp_truck_box_state.lng, funcs:firstrow(coollu.t_eqp_truck.plate_number)->coollu.t_eqp_truck.plate_number, funcs:firstrow(Column#73)->Column#73, funcs:firstrow(Column#74)->Column#74, funcs:firstrow(Column#75)->Column#75, funcs:firstrow(Column#76)->Column#76, funcs:firstrow(coollu.t_eqp_truck.model)->coollu.t_eqp_truck.model|
|└─Projection_15|0.30|root||coollu.t_eqp_truck_box_state.truck_id, coollu.t_eqp_truck_box_state.truck_box_id, coalesce(coollu.t_eqp_truck_box_state.is_online, 0)->Column#72, coollu.t_eqp_truck_box_state.lat, coollu.t_eqp_truck_box_state.lng, coollu.t_eqp_truck.plate_number, coalesce(coollu.t_eqp_truck_box_state.is_acc_on, 0)->Column#73, coalesce(coollu.t_eqp_truck_box_state.is_alarm, 0)->Column#74, coalesce(coollu.t_eqp_truck_box_state.is_body_working, 0)->Column#75, coalesce(coollu.t_eqp_truck_box_state.is_stop, 0)->Column#76, coollu.t_eqp_truck.model|
| └─IndexMergeJoin_30|0.30|root||inner join, inner:Projection_28, outer key:coollu.t_eqp_truck_box_state.truck_id, inner key:coollu.t_eqp_truck.id|
| ├─IndexMergeJoin_89(Build)|0.24|root||inner join, inner:Projection_87, outer key:coollu.t_eqp_truck_box.id, inner key:coollu.t_eqp_truck_box_state.truck_box_id|
| │ ├─IndexLookUp_121(Build)|0.52|root|||
| │ │ ├─IndexRangeScan_118(Build)|0.52|cop[tikv]|table:t3, index:idx_t_eqp_truck_box_is_link(is_link)|range:[1,1], keep order:false, stats:pseudo|
| │ │ └─Selection_120(Probe)|0.52|cop[tikv]||not(isnull(coollu.t_eqp_truck_box.id))|
| │ │ └─TableRowIDScan_119|0.52|cop[tikv]|table:t3|keep order:false, stats:pseudo|
| │ └─Projection_87(Probe)|0.45|root||coollu.t_eqp_truck_box_state.ent_id, coollu.t_eqp_truck_box_state.truck_box_id, coollu.t_eqp_truck_box_state.truck_id, coollu.t_eqp_truck_box_state.lat, coollu.t_eqp_truck_box_state.lng, coollu.t_eqp_truck_box_state.is_online, coollu.t_eqp_truck_box_state.is_acc_on, coollu.t_eqp_truck_box_state.is_body_working, coollu.t_eqp_truck_box_state.is_alarm, coollu.t_eqp_truck_box_state.is_stop|
| │ └─IndexLookUp_86|0.45|root|||
| │ ├─IndexRangeScan_83(Build)|1.00|cop[tikv]|table:t, index:uk_eqp_truck_box_state_truck_box_id(truck_box_id)|range: decided by [eq(coollu.t_eqp_truck_box_state.truck_box_id, coollu.t_eqp_truck_box.id)], keep order:true, stats:pseudo|
| │ └─Selection_85(Probe)|0.45|cop[tikv]||eq(coollu.t_eqp_truck_box_state.ent_id, 35), not(isnull(coollu.t_eqp_truck_box_state.lat)), not(isnull(coollu.t_eqp_truck_box_state.lng)), not(isnull(coollu.t_eqp_truck_box_state.truck_id))|
| │ └─TableRowIDScan_84|1.00|cop[tikv]|table:t|keep order:false, stats:pseudo|
| └─Projection_28(Probe)|1.00|root||coollu.t_eqp_truck.id, coollu.t_eqp_truck.plate_number, coollu.t_eqp_truck.dep_id, coollu.t_eqp_truck.model|
| └─IndexLookUp_27|1.00|root|||
| ├─Selection_25(Build)|1.00|cop[tikv]||not(isnull(coollu.t_eqp_truck.id))|
| │ └─IndexRangeScan_23|1.00|cop[tikv]|table:t2, index:id(id)|range: decided by [eq(coollu.t_eqp_truck.id, coollu.t_eqp_truck_box_state.truck_id)], keep order:true, stats:pseudo|
| └─Selection_26(Probe)|1.00|cop[tikv]||in(coollu.t_eqp_truck.dep_id, 0, 191, 187, 186, 175)|
| └─TableRowIDScan_24|1.00|cop[tikv]|table:t2|keep order:false, stats:pseudo|
explain analyze:
|id|estRows|actRows|task|access object|execution info|operator info|memory|disk|
|---|---|---|---|---|---|---|---|---|
|HashAgg_14|1.00|16|root||time:157.914312ms, loops:5, PartialConcurrency:4, FinalConcurrency:4|group by:Column#72, Column#73, Column#74, Column#75, Column#76, coollu.t_eqp_truck.model, coollu.t_eqp_truck.plate_number, coollu.t_eqp_truck_box_state.lat, coollu.t_eqp_truck_box_state.lng, coollu.t_eqp_truck_box_state.truck_box_id, coollu.t_eqp_truck_box_state.truck_id, funcs:firstrow(coollu.t_eqp_truck_box_state.truck_id)->coollu.t_eqp_truck_box_state.truck_id, funcs:firstrow(coollu.t_eqp_truck_box_state.truck_box_id)->coollu.t_eqp_truck_box_state.truck_box_id, funcs:firstrow(Column#72)->Column#72, funcs:firstrow(coollu.t_eqp_truck_box_state.lat)->coollu.t_eqp_truck_box_state.lat, funcs:firstrow(coollu.t_eqp_truck_box_state.lng)->coollu.t_eqp_truck_box_state.lng, funcs:firstrow(coollu.t_eqp_truck.plate_number)->coollu.t_eqp_truck.plate_number, funcs:firstrow(Column#73)->Column#73, funcs:firstrow(Column#74)->Column#74, funcs:firstrow(Column#75)->Column#75, funcs:firstrow(Column#76)->Column#76, funcs:firstrow(coollu.t_eqp_truck.model)->coollu.t_eqp_truck.model|88.8828125 KB|N/A|
|└─Projection_15|0.30|16|root||time:132.150827ms, loops:2, Concurrency:OFF|coollu.t_eqp_truck_box_state.truck_id, coollu.t_eqp_truck_box_state.truck_box_id, coalesce(coollu.t_eqp_truck_box_state.is_online, 0)->Column#72, coollu.t_eqp_truck_box_state.lat, coollu.t_eqp_truck_box_state.lng, coollu.t_eqp_truck.plate_number, coalesce(coollu.t_eqp_truck_box_state.is_acc_on, 0)->Column#73, coalesce(coollu.t_eqp_truck_box_state.is_alarm, 0)->Column#74, coalesce(coollu.t_eqp_truck_box_state.is_body_working, 0)->Column#75, coalesce(coollu.t_eqp_truck_box_state.is_stop, 0)->Column#76, coollu.t_eqp_truck.model|98.5703125 KB|N/A|
| └─IndexMergeJoin_30|0.30|16|root||time:132.042762ms, loops:2, Concurrency:4|inner join, inner:Projection_28, outer key:coollu.t_eqp_truck_box_state.truck_id, inner key:coollu.t_eqp_truck.id|74.125 KB|N/A|
| ├─IndexMergeJoin_89(Build)|0.24|16|root||time:102.137343ms, loops:3, Concurrency:4|inner join, inner:Projection_87, outer key:coollu.t_eqp_truck_box.id, inner key:coollu.t_eqp_truck_box_state.truck_box_id|8.779296875 KB|N/A|
| │ ├─IndexLookUp_121(Build)|0.52|446|root||time:20.89547ms, loops:2, rpc num: 1, rpc time:1.142248ms, proc keys:448||24.8125 KB|N/A|
| │ │ ├─IndexRangeScan_118(Build)|0.52|446|cop[tikv]|table:t3, index:idx_t_eqp_truck_box_is_link(is_link)|time:0s, loops:4|range:[1,1], keep order:false, stats:pseudo|N/A|N/A|
| │ │ └─Selection_120(Probe)|0.52|446|cop[tikv]||time:10ms, loops:4|not(isnull(coollu.t_eqp_truck_box.id))|N/A|N/A|
| │ │ └─TableRowIDScan_119|0.52|446|cop[tikv]|table:t3|time:10ms, loops:4|keep order:false, stats:pseudo|N/A|N/A|
| │ └─Projection_87(Probe)|0.45|16|root||time:70.909398ms, loops:2, Concurrency:OFF|coollu.t_eqp_truck_box_state.ent_id, coollu.t_eqp_truck_box_state.truck_box_id, coollu.t_eqp_truck_box_state.truck_id, coollu.t_eqp_truck_box_state.lat, coollu.t_eqp_truck_box_state.lng, coollu.t_eqp_truck_box_state.is_online, coollu.t_eqp_truck_box_state.is_acc_on, coollu.t_eqp_truck_box_state.is_body_working, coollu.t_eqp_truck_box_state.is_alarm, coollu.t_eqp_truck_box_state.is_stop|3.99609375 KB|N/A|
| │ └─IndexLookUp_86|0.45|16|root||time:70.891912ms, loops:2, rpc num: 1, rpc time:6.457469ms, proc keys:407||18.41796875 KB|N/A|
| │ ├─IndexRangeScan_83(Build)|1.00|407|cop[tikv]|table:t, index:uk_eqp_truck_box_state_truck_box_id(truck_box_id)|time:3ms, loops:4|range: decided by [eq(coollu.t_eqp_truck_box_state.truck_box_id, coollu.t_eqp_truck_box.id)], keep order:true, stats:pseudo|N/A|N/A|
| │ └─Selection_85(Probe)|0.45|16|cop[tikv]||time:11ms, loops:4|eq(coollu.t_eqp_truck_box_state.ent_id, 35), not(isnull(coollu.t_eqp_truck_box_state.lat)), not(isnull(coollu.t_eqp_truck_box_state.lng)), not(isnull(coollu.t_eqp_truck_box_state.truck_id))|N/A|N/A|
| │ └─TableRowIDScan_84|1.00|407|cop[tikv]|table:t|time:11ms, loops:4|keep order:false, stats:pseudo|N/A|N/A|
| └─Projection_28(Probe)|1.00|16|root||time:29.393782ms, loops:2, Concurrency:OFF|coollu.t_eqp_truck.id, coollu.t_eqp_truck.plate_number, coollu.t_eqp_truck.dep_id, coollu.t_eqp_truck.model|2.05859375 KB|N/A|
| └─IndexLookUp_27|1.00|16|root||time:28.693895ms, loops:2, rpc num: 1, rpc time:2.152884ms, proc keys:16||10.37109375 KB|N/A|
| ├─Selection_25(Build)|1.00|16|cop[tikv]||time:0s, loops:1|not(isnull(coollu.t_eqp_truck.id))|N/A|N/A|
| │ └─IndexRangeScan_23|1.00|16|cop[tikv]|table:t2, index:id(id)|time:0s, loops:1|range: decided by [eq(coollu.t_eqp_truck.id, coollu.t_eqp_truck_box_state.truck_id)], keep order:true, stats:pseudo|N/A|N/A|
| └─Selection_26(Probe)|1.00|16|cop[tikv]||time:0s, loops:1|in(coollu.t_eqp_truck.dep_id, 0, 191, 187, 186, 175)|N/A|N/A|
| └─TableRowIDScan_24|1.00|16|cop[tikv]|table:t2|time:0s, loops:1|keep order:false, stats:pseudo|N/A|N/A|
-------------------------------------------------------------------------------------------------------------------------------------------------
二、大表单表查询缓慢： t_eqp_truck_location_sub （4kw数据）
sql语句：
SELECT
t.main_id,
t.gmt_location,
t.truck_id,
t.truck_box_id,
t.addr,
t.is_stop AS stop,
t.stop_time,
t.is_bodywork AS bodywork,
t.bodywork_time,
t.is_alarm AS alarm,
t.alarm_time,
t.info,
t.is_acc_on AS acc_on,
t.speed,
t.mileage,
t.id,
t.ent_id,
t.gmt_create,
t.gmt_modified,
t.creator,
t.modifier
FROM
t_eqp_truck_location_sub t
WHERE
(
t.truck_id = 245
AND t.truck_box_id = 538
AND t.gmt_location >= ‘2020-07-06 14:59:59.958’
AND t.gmt_location <= ‘2020-07-07 14:59:59.958’
AND t.ent_id = 5
AND 1 = 1
)
ORDER BY
t.gmt_location DESC
LIMIT
1
explain
|id|estRows|task|access object|operator info|
|---|---|---|---|---|
|Projection_7|1.00|root||coollu.t_eqp_truck_location_sub.main_id, coollu.t_eqp_truck_location_sub.gmt_location, coollu.t_eqp_truck_location_sub.truck_id, coollu.t_eqp_truck_location_sub.truck_box_id, coollu.t_eqp_truck_location_sub.addr, coollu.t_eqp_truck_location_sub.is_stop, coollu.t_eqp_truck_location_sub.stop_time, coollu.t_eqp_truck_location_sub.is_bodywork, coollu.t_eqp_truck_location_sub.bodywork_time, coollu.t_eqp_truck_location_sub.is_alarm, coollu.t_eqp_truck_location_sub.alarm_time, coollu.t_eqp_truck_location_sub.info, coollu.t_eqp_truck_location_sub.is_acc_on, coollu.t_eqp_truck_location_sub.speed, coollu.t_eqp_truck_location_sub.mileage, coollu.t_eqp_truck_location_sub.id, coollu.t_eqp_truck_location_sub.ent_id, coollu.t_eqp_truck_location_sub.gmt_create, coollu.t_eqp_truck_location_sub.gmt_modified, coollu.t_eqp_truck_location_sub.creator, coollu.t_eqp_truck_location_sub.modifier|
|└─Limit_12|1.00|root||offset:0, count:1|
| └─Projection_41|1.00|root||coollu.t_eqp_truck_location_sub.id, coollu.t_eqp_truck_location_sub.gmt_create, coollu.t_eqp_truck_location_sub.gmt_modified, coollu.t_eqp_truck_location_sub.ent_id, coollu.t_eqp_truck_location_sub.main_id, coollu.t_eqp_truck_location_sub.gmt_location, coollu.t_eqp_truck_location_sub.truck_id, coollu.t_eqp_truck_location_sub.truck_box_id, coollu.t_eqp_truck_location_sub.addr, coollu.t_eqp_truck_location_sub.is_stop, coollu.t_eqp_truck_location_sub.stop_time, coollu.t_eqp_truck_location_sub.is_bodywork, coollu.t_eqp_truck_location_sub.bodywork_time, coollu.t_eqp_truck_location_sub.is_alarm, coollu.t_eqp_truck_location_sub.alarm_time, coollu.t_eqp_truck_location_sub.info, coollu.t_eqp_truck_location_sub.is_acc_on, coollu.t_eqp_truck_location_sub.speed, coollu.t_eqp_truck_location_sub.mileage, coollu.t_eqp_truck_location_sub.creator, coollu.t_eqp_truck_location_sub.modifier|
| └─IndexLookUp_40|1.00|root|||
| ├─IndexRangeScan_37(Build)|3.27|cop[tikv]|table:t, index:idx_tk_loc_sub_tkbox_gmt(truck_id, truck_box_id, gmt_location)|range:[245 538 2020-07-06 15:00:00,245 538 2020-07-07 15:00:00), keep order:true, desc|
| └─Selection_39(Probe)|1.00|cop[tikv]||eq(coollu.t_eqp_truck_location_sub.ent_id, 5)|
| └─TableRowIDScan_38|3.27|cop[tikv]|table:t|keep order:false|
explain analyze
|id|estRows|actRows|task|access object|execution info|operator info|memory|disk|
|---|---|---|---|---|---|---|---|---|
|Projection_7|1.00|1|root||time:8.64011ms, loops:2, Concurrency:OFF|coollu.t_eqp_truck_location_sub.main_id, coollu.t_eqp_truck_location_sub.gmt_location, coollu.t_eqp_truck_location_sub.truck_id, coollu.t_eqp_truck_location_sub.truck_box_id, coollu.t_eqp_truck_location_sub.addr, coollu.t_eqp_truck_location_sub.is_stop, coollu.t_eqp_truck_location_sub.stop_time, coollu.t_eqp_truck_location_sub.is_bodywork, coollu.t_eqp_truck_location_sub.bodywork_time, coollu.t_eqp_truck_location_sub.is_alarm, coollu.t_eqp_truck_location_sub.alarm_time, coollu.t_eqp_truck_location_sub.info, coollu.t_eqp_truck_location_sub.is_acc_on, coollu.t_eqp_truck_location_sub.speed, coollu.t_eqp_truck_location_sub.mileage, coollu.t_eqp_truck_location_sub.id, coollu.t_eqp_truck_location_sub.ent_id, coollu.t_eqp_truck_location_sub.gmt_create, coollu.t_eqp_truck_location_sub.gmt_modified, coollu.t_eqp_truck_location_sub.creator, coollu.t_eqp_truck_location_sub.modifier|8.11328125 KB|N/A|
|└─Limit_12|1.00|1|root||time:8.617409ms, loops:2|offset:0, count:1|N/A|N/A|
| └─Projection_41|1.00|1|root||time:8.614832ms, loops:1, Concurrency:OFF|coollu.t_eqp_truck_location_sub.id, coollu.t_eqp_truck_location_sub.gmt_create, coollu.t_eqp_truck_location_sub.gmt_modified, coollu.t_eqp_truck_location_sub.ent_id, coollu.t_eqp_truck_location_sub.main_id, coollu.t_eqp_truck_location_sub.gmt_location, coollu.t_eqp_truck_location_sub.truck_id, coollu.t_eqp_truck_location_sub.truck_box_id, coollu.t_eqp_truck_location_sub.addr, coollu.t_eqp_truck_location_sub.is_stop, coollu.t_eqp_truck_location_sub.stop_time, coollu.t_eqp_truck_location_sub.is_bodywork, coollu.t_eqp_truck_location_sub.bodywork_time, coollu.t_eqp_truck_location_sub.is_alarm, coollu.t_eqp_truck_location_sub.alarm_time, coollu.t_eqp_truck_location_sub.info, coollu.t_eqp_truck_location_sub.is_acc_on, coollu.t_eqp_truck_location_sub.speed, coollu.t_eqp_truck_location_sub.mileage, coollu.t_eqp_truck_location_sub.creator, coollu.t_eqp_truck_location_sub.modifier|8.4765625 KB|N/A|
| └─IndexLookUp_40|1.00|1|root||time:8.589859ms, loops:1, rpc num: 1, rpc time:4.523009ms, proc keys:4220||657.5546875 KB|N/A|
| ├─IndexRangeScan_37(Build)|3.27|4220|cop[tikv]|table:t, index:idx_tk_loc_sub_tkbox_gmt(truck_id, truck_box_id, gmt_location)|time:3ms, loops:9|range:[245 538 2020-07-06 15:00:00,245 538 2020-07-07 15:00:00), keep order:true, desc|N/A|N/A|
| └─Selection_39(Probe)|1.00|3040|cop[tikv]||proc max:9ms, min:1ms, p80:6ms, p95:9ms, iters:28, tasks:7|eq(coollu.t_eqp_truck_location_sub.ent_id, 5)|N/A|N/A|
| └─TableRowIDScan_38|3.27|3040|cop[tikv]|table:t|proc max:9ms, min:1ms, p80:6ms, p95:9ms, iters:28, tasks:7|keep order:false|N/A|N/A|
--------------------------------------------------------------------------------------------------------------------------------------------------
三、大表和大表关联查询（2张表）：
t_eqp_truck_location ：记录条数：8900w，有分区
CREATE TABLE t_eqp_truck_location
(
id bigint AUTO_INCREMENT,
gmt_create timestamp ,
truck_id integer NOT NULL ,
truck_box_id integer NOT NULL ,
lat double ,
lng double ,
gmt_location datetime comment ‘yyyy-MM-dd HH:mm:ss.’,
speed double ,
is_located boolean ,
is_fill boolean ,
creator varchar(20),
modifier varchar(20),
CONSTRAINT pk_t_eqp_truck_location_id_location PRIMARY KEY (id,gmt_location)
)PARTITION BY RANGE ( to_days(gmt_location) ) (
PARTITION p0 VALUES LESS THAN (to_days(‘2017-01-01’)),
PARTITION p2017 VALUES LESS THAN (to_days(‘2018-01-01’)),
PARTITION p2018 VALUES LESS THAN (to_days(‘2019-01-01’)),
PARTITION p2019 VALUES LESS THAN (to_days(‘2020-01-01’)),
PARTITION p2020 VALUES LESS THAN (to_days(‘2021-01-01’)),
PARTITION p2021 VALUES LESS THAN (to_days(‘2022-01-01’)),
PARTITION p2022 VALUES LESS THAN (to_days(‘2023-01-01’)),
PARTITION p2023 VALUES LESS THAN (to_days(‘2024-01-01’)),
PARTITION p2024 VALUES LESS THAN (to_days(‘2025-01-01’)),
PARTITION p2025 VALUES LESS THAN (to_days(‘2026-01-01’)),
PARTITION p2026 VALUES LESS THAN (to_days(‘2027-01-01’)),
PARTITION p2027 VALUES LESS THAN (to_days(‘2028-01-01’)),
PARTITION pMax VALUES LESS THAN MAXVALUE
);
CREATE INDEX idx_t_eqp_truck_location_gmt_location ON t_eqp_truck_location(gmt_location);
CREATE INDEX idx_t_eqp_truck_location_truck ON t_eqp_truck_location(truck_id,truck_box_id);
CREATE INDEX idx_t_eqp_truck_location_located ON t_eqp_truck_location(is_located);
CREATE INDEX idx_t_eqp_truck_location_truckbox_located ON t_eqp_truck_location(truck_box_id,is_located);
CREATE INDEX idx_t_eqp_truck_location_tk_located_gmt ON t_eqp_truck_location(truck_id,truck_box_id,is_located,gmt_location);
（重复加多了一些索引，感觉有时对一些条件组合查询速度有帮助，不知道是否符合tidb特性？）
- t_eqp_truck_location_sub: 记录条数：4000多W，无分区
CREATE TABLE t_eqp_truck_location_sub
(
id bigint PRIMARY KEY AUTO_INCREMENT,
gmt_create timestamp,
gmt_modified timestamp,
ent_id integer,
main_id bigint NOT NULL ,
gmt_location timestamp ,
truck_id integer NOT NULL ,
truck_box_id integer NOT NULL ,
addr varchar(200),
is_stop boolean ,
stop_time integer ,
is_bodywork boolean ,
bodywork_time integer,
is_alarm boolean ,
alarm_time integer ,
info json,
is_acc_on boolean ,
speed double ,
mileage double ,
creator varchar(20),
modifier varchar(20)
) ;
CREATE INDEX idx_tk_loc_sub_main_ent_id ON t_eqp_truck_location_sub(main_id,ent_id);
CREATE INDEX idx_tk_loc_sub_tkbox_gmt ON t_eqp_truck_location_sub(truck_id,truck_box_id,gmt_location);
CREATE INDEX idx_tk_loc_sub_gmt_location ON t_eqp_truck_location_sub(gmt_location);
CREATE INDEX idx_tk_loc_sub_ent_id ON t_eqp_truck_location_sub(ent_id);
SELECT
t.id,
t.gmt_create,
t.truck_id,
t.truck_box_id,
t.lat,
t.lng,
t.gmt_location,
t.speed,
t.is_located AS located,
t.is_fill AS fill,
s.addr,
s.is_stop AS stop,
s.stop_time,
s.is_bodywork AS bodywork,
s.bodywork_time,
s.is_alarm AS alarm,
s.alarm_time,
s.info
FROM
t_eqp_truck_location t
LEFT OUTER JOIN t_eqp_truck_location_sub s ON s.main_id = t.id
WHERE
(
t.truck_box_id = 797
AND t.is_located = 1
AND t.gmt_location >= '2020-07-06 00:00:00'
AND t.gmt_location <= '2020-07-06 23:59:59'
AND 1 = 1
)
ORDER BY
t.gmt_location DESC
explain:
id estRows task access object operator info
Sort_8 3323.82 root coollu.t_eqp_truck_location.gmt_location:desc
└─IndexMergeJoin_21 3323.82 root left outer join, inner:Projection_19, outer key:coollu.t_eqp_truck_location.id, inner key:coollu.t_eqp_truck_location_sub.main_id
├─IndexLookUp_46(Build) 3319.38 root
│ ├─IndexRangeScan_43(Build) 51600.87 cop[tikv] table:t, partition:p2020, index:idx_t_eqp_truck_location_truckbox_located(truck_box_id, is_located) range:[797 1,797 1], keep order:false
│ └─Selection_45(Probe) 3319.38 cop[tikv] ge(coollu.t_eqp_truck_location.gmt_location, 2020-07-06 00:00:00.000000), le(coollu.t_eqp_truck_location.gmt_location, 2020-07-06 23:59:59.000000)
│ └─TableRowIDScan_44 51600.87 cop[tikv] table:t, partition:p2020 keep order:false
└─Projection_19(Probe) 1.00 root coollu.t_eqp_truck_location_sub.main_id, coollu.t_eqp_truck_location_sub.addr, coollu.t_eqp_truck_location_sub.is_stop, coollu.t_eqp_truck_location_sub.stop_time, coollu.t_eqp_truck_location_sub.is_bodywork, coollu.t_eqp_truck_location_sub.bodywork_time, coollu.t_eqp_truck_location_sub.is_alarm, coollu.t_eqp_truck_location_sub.alarm_time, coollu.t_eqp_truck_location_sub.info
└─IndexLookUp_18 1.00 root
├─IndexRangeScan_16(Build) 1.00 cop[tikv] table:s, index:idx_tk_loc_sub_main_ent_id(main_id, ent_id) range: decided by [eq(coollu.t_eqp_truck_location_sub.main_id, coollu.t_eqp_truck_location.id)], keep order:true
└─TableRowIDScan_17(Probe) 1.00 cop[tikv] table:s keep order:false
explain analyze:
id estRows actRows task access object execution info operator info memory disk
Sort_8 3324.15 0 root time:939.635948ms, loops:1 coollu.t_eqp_truck_location.gmt_location:desc 0 Bytes 0 Bytes
└─IndexMergeJoin_21 3324.15 0 root time:939.591708ms, loops:1, Concurrency:4 left outer join, inner:Projection_19, outer key:coollu.t_eqp_truck_location.id, inner key:coollu.t_eqp_truck_location_sub.main_id 0 Bytes N/A
├─IndexLookUp_46(Build) 3319.72 0 root time:939.656378ms, loops:1, rpc num: 1, rpc time:75.267564ms, proc keys:84138 1.418212890625 MB N/A
│ ├─IndexRangeScan_43(Build) 51606.04 84138 cop[tikv] table:t, partition:p2020, index:idx_t_eqp_truck_location_truckbox_located(truck_box_id, is_located) time:38ms, loops:87 range:[797 1,797 1], keep order:false N/A N/A
│ └─Selection_45(Probe) 3319.72 0 cop[tikv] proc max:249ms, min:0s, p80:14ms, p95:102ms, iters:588, tasks:134 ge(coollu.t_eqp_truck_location.gmt_location, 2020-07-06 00:00:00.000000), le(coollu.t_eqp_truck_location.gmt_location, 2020-07-06 23:59:59.000000) N/A N/A
│ └─TableRowIDScan_44 51606.04 84138 cop[tikv] table:t, partition:p2020 proc max:249ms, min:0s, p80:14ms, p95:102ms, iters:588, tasks:134 keep order:false N/A N/A
└─Projection_19(Probe) 1.00 root time:0ns, loops:0 coollu.t_eqp_truck_location_sub.main_id, coollu.t_eqp_truck_location_sub.addr, coollu.t_eqp_truck_location_sub.is_stop, coollu.t_eqp_truck_location_sub.stop_time, coollu.t_eqp_truck_location_sub.is_bodywork, coollu.t_eqp_truck_location_sub.bodywork_time, coollu.t_eqp_truck_location_sub.is_alarm, coollu.t_eqp_truck_location_sub.alarm_time, coollu.t_eqp_truck_location_sub.info N/A N/A
└─IndexLookUp_18 1.00 root time:0ns, loops:0 N/A N/A
├─IndexRangeScan_16(Build) 1.00 cop[tikv] table:s, index:idx_tk_loc_sub_main_ent_id(main_id, ent_id) time:0ns, loops:0 range: decided by [eq(coollu.t_eqp_truck_location_sub.main_id, coollu.t_eqp_truck_location.id)], keep order:true N/A N/A
└─TableRowIDScan_17(Probe) 1.00 cop[tikv] table:s time:0ns, loops:0 keep order:false N/A N/A你好，你的问题已收到，正在分析，如有进展会及时更新回帖。
这边可否提供下 tikv-detail 和 overview 监控的完整截图，感谢
ok，感谢反馈，这边可能需要一个完整的监控 pdf 方便看到更多的监控项一边排查。辛苦按照以下方式进行获取。
打开 grafana 监控，先按 d 再按 shift+e 可以打开所有监控项。
(1)、chrome 安装这个插件https://chrome.google.com/webstore/detail/full-page-screen-capture/fdpohaocaechififmbbbbbknoalclacl
(2)、鼠标焦点置于 Dashboard 上，按 ?可显示所有快捷键，先按 d 再按 E 可将所有 Rows 的 Panels 打开，需等待一段时间待页面加载完成。
(3)、使用这个 full-page-screen-capture 插件进行截屏保存
1 个赞
pdf比较大，上传有大小限制
可以提供百度网盘的方式，或者将 grafana 监控临时地址发给我们。
麻烦看下当时的慢日志中的相关记录
3.0.5 及以上版本 TiDB 慢日志中会记录 tidb_decode_plan(xxxx) 这个是当时 SQL 执行时的执行计划，可以将这一部分复制到 tidb 中进行查询看到当时的具体执行计划，select tidb_decode_plan(xxx)
将当时的执行计划和正常时候手动执行的执行计划进行对比，看下当时是否有 SQL 执行计划走错的情况
https://docs.pingcap.com/zh/tidb/stable/troubleshoot-cpu-issues#tidb-执行计划不对导致延迟增高
hi，
请问问题是否已经解决了呢？
恩，多谢，已解决。定期对相关表执行analyze table。
OK，现象为查询变慢的原因就是执行力话走错了，感谢反馈，方式设置的 auto analyze 设置为多少呢
之前是手动触发的，自动设置的0.2（按文档建议），正好问下，具体有没自动执行analyze table操作，需要哪个日志里查看？
可以在 tidb log 中查看到
没手动 analyze table 过的表 ，auto analyze 的条件是 1min
没有修改过该表，
已经 analyze 过的表 auto analyze 的条件是看 tbl.ModifyCount/tbl.Count > autoAnalyzeRatio（SHOW STATS_META where db_name = ‘test’ and table_name = ‘sbtest1’;）
此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。