tidb产生过多执行计划

h5n1 · 2023 年10 月 13 日 05:05

v7.4.0

tiup bench tpcc(500 warehouse) 初始化数据期间，发现dashbaord有查询慢SQL，里面有500多个执行计划。

select count ( ? ) from ( select `c` . `c_id` , `c` . `c_d_id` , `c` . `c_w_id` , `c` . `c_balance` `c1` , ( select sum ( `ol_amount` ) from `orders` , `order_line` where `ol_w_id` = `o_w_id` and `ol_d_id` = `o_d_id` and `ol_o_id` = `o_id` and `ol_delivery_d` is not ? and `o_w_id` = ? and `o_d_id` = `c` . `c_d_id` and `o_c_id` = `c` . `c_id` ) `sm` , ( select sum ( `h_amount` ) from history where `h_c_w_id` = ? and `h_c_d_id` = `c` . `c_d_id` and `h_c_id` = `c` . `c_id` ) `smh` from `customer` `c` where `c` . `c_w_id` = ? ) `t` where `c1` <> `sm` - `smh`

King-Dylan · 2023 年10 月 13 日 06:40

有对比这些产生不同 plan 的 SQL text 有什么差异吗，可以提供任意两个的 plan text。

h5n1 · 2023 年10 月 13 日 06:46

SQL1：
select count ( ? ) from ( select c . c_id , c . c_d_id , c . c_w_id , c . c_balance c1 , ( select sum ( ol_amount ) from orders , order_line where ol_w_id = o_w_id and ol_d_id = o_d_id and ol_o_id = o_id and ol_delivery_d is not ? and o_w_id = ? and o_d_id = c . c_d_id and o_c_id = c . c_id ) sm , ( select sum ( h_amount ) from history where h_c_w_id = ? and h_c_d_id = c . c_d_id and h_c_id = c . c_id ) smh from customer c where c . c_w_id = ? ) t where c1 <> sm - smh

SQL2：
select count ( ? ) from ( select c . c_id , c . c_d_id , c . c_balance c1 , c_ytd_payment , ( select sum ( ol_amount ) from orders , order_line where ol_w_id = o_w_id and ol_d_id = o_d_id and ol_o_id = o_id and ol_delivery_d is not ? and o_w_id = ? and o_d_id = c . c_d_id and o_c_id = c . c_id ) sm from customer c where c . c_w_id = ? ) t1 where c1 + c_ytd_payment <> sm

我又重新初始化1000个warehous ,2个SQL的执行计划

King-Dylan · 2023 年10 月 13 日 07:15

可以从 slow query log 中取两条完整的对于这个 sql 的内容吗？
select count ( ? ) from ( select c . c_id , c . c_d_id , c . c_w_id , c . c_balance c1 , ( select sum ( ol_amount ) from orders , order_line where ol_w_id = o_w_id and ol_d_id = o_d_id and ol_o_id = o_id and ol_delivery_d is not ? and o_w_id = ? and o_d_id = c . c_d_id and o_c_id = c . c_id ) sm , ( select sum ( h_amount ) from history where h_c_w_id = ? and h_c_d_id = c . c_d_id and h_c_id = c . c_id ) smh from customer c where c . c_w_id = ? ) t where c1 <> sm - smh