【TiDB 使用环境】生产环境 /测试/ Poc
SELECT
puOder.id,
created_by,
created_time,
updated_by,
updated_time,
auditor,
audit_time,
.......
FROM
AAA puOder
INNER JOIN (
SELECT
DISTINCT o.id,
i.sku_id,
i.id order_item_id
FROM
pu_order o
LEFT JOIN AAA_item i ON o.id = i.pu_order_id
LEFT JOIN AAA_task t ON o.process_instance_id = t.process_instance_id
LEFT JOIN DB1.BBB_view bov ON o.created_org_id = bov.org_id
WHERE
o.is_deleted = 0
AND i.is_deleted = 0
AND (
bov.tree_path LIKE CONCAT('/1957734668423237632/', '%')
)
AND o.order_mode = '10081001'
) tmp ON puOder.id = tmp.id
ORDER BY
order_time DESC
LIMIT
10;
从执行计划来看这两项耗时严重
IndexHashJoin_40 time:3.81s, loops:17, inner:{total:12.8s, concurrency:5, task:9, construct:8.61ms, fetch:12.8s, build:1.72ms, join:42.9ms}
└─TableReader_35(Probe) time:12.8s, loops:25, cop_task: {num: 35, max: 551.9ms, min: 2.08ms, avg: 364.9ms, p95: 519.3ms, max_proc_keys: 2016, p95_proc_keys: 1376, tot_proc: 341.9ms, tot_wait: 77.5ms, copr_cache_hit_ratio: 0.00, build_task_duration: 941.9µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:35, total_time:12.8s}}
└─TableRangeScan_34 tikv_task:{proc max:544ms, min:0s, avg: 359.1ms, p80:502ms, p95:511ms, iters:127, tasks:35}, scan_detail: {total_process_keys: 15891, total_process_keys_size: 8437531, total_keys: 15913, get_snapshot_time: 354.2µs, rocksdb: {block: {cache_hit_count: 81141}}}, time_detail: {total_process_time: 341.9ms, total_suspend_time: 12.3s, total_wait_time: 77.5ms, total_kv_read_wall_time: 12.6s, tikv_wall_time: 12.7s}
将tidb_replica_read设置为leader-and-follower后缓解了。但是还是会时不时出现1.5s左右的慢SQL。执行计划耗时还是上面两项。
有没有优化建议？
