圆括号导致执行计划非最优问题

wzf0072 · 2024 年5 月 8 日 02:04

绑定执行，没有起效。

aytrack · 2024 年5 月 8 日 06:39

除了上面新建的 binding，有没有其他相关 binding 创建呢。根据目前的信息，怀疑之前本来就已经有其它 binding 了，加了括号的影响是让它匹配不上 binding 导致的

kkpeter · 2024 年5 月 8 日 09:21

坐等结果

TiDBer_RjzUpGDL · 2024 年5 月 8 日 09:22

mark 一下

wzf0072 · 2024 年5 月 10 日 00:15

新建的binding应该会覆盖原有的binding吧。

aytrack · 2024 年5 月 10 日 02:56

嗯，会覆盖
可以提供下 play replayer dump 的完整信息么

wzf0072 · 2024 年5 月 10 日 06:57

replayer_YRLLibKFm6S_UULFCehV2Q==_1712113325591413657.zip|attachment (7.1 MB)

replayer 信息

aytrack · 2024 年5 月 10 日 09:11

当 Global binding 为空的情况下，可以看到使用括号的时候还是走 tiflash

通过导处的 global binding，可以看出这里有一些自动捕获的 global binding 是没有走 tiflash 的

你可以使用

select @@last_plan_from_binding

来看看你的执行计划是否来自于 binding

wzf0072 · 2024 年5 月 11 日 02:16

Navicat执行完sql后执行 select @@last_plan_from_binding
返回0

在命令行执行返回1

aytrack · 2024 年5 月 11 日 03:58

请问 navicat 上的查询也是走 tikv 么。麻烦把这条 global binding 删掉试试呢。

select count ( ? ) from ( select `tsd` . `id` from ( ( `report_database_pro` . `tms_schedulingbill_detail` as `tsd` join `report_database_pro` . `tms_schedulingbill` as `s` on `tsd` . `scheduling_billcode` = `s` . `scheduling_billcode` ) join `report_database_pro` . `tms_bms_detail_expense` as `bcrd` on ( ( `tsd` . `operation_billcode` = `bcrd` . `operation_billcode` ) and ( `tsd` . `scheduling_billcode` = `bcrd` . `scheduling_billcode` ) and `bcrd` . `is_deleted` = ? ) ) left join `report_database_pro` . `mdc_customer_data` as `m` on `tsd` . `shipper_id` = `m` . `customer_data_id` where ( `s` . `departure_company` = ? and `tsd` . `is_deleted` = ? and `bcrd` . `billing_time` >= ? and `bcrd` . `billing_time` <= ? and `bcrd` . `billing_id` = ? and `s` . `is_deleted` = ? ) ) as `total`	SELECT /*+ hash_agg(@`sel_1`), inl_hash_join(@`sel_2` `report_database_pro`.`s`), inl_join(@`sel_2` `report_database_pro`.`m`), inl_hash_join(@`sel_2` `report_database_pro`.`tsd`), use_index(@`sel_2` `report_database_pro`.`bcrd` `idx_billing_time`), use_index(@`sel_2` `report_database_pro`.`tsd` `idx_tms_schedulingbill_detail_operation_billcode`), use_index(@`sel_2` `report_database_pro`.`m` `customerdataid`), use_index(@`sel_2` `report_database_pro`.`s` `idx_tms_schedulingbill_scheduling_billcode`)*/ count(0) FROM (SELECT `tsd`.`id` FROM ((`report_database_pro`.`tms_schedulingbill_detail` AS `tsd` JOIN `report_database_pro`.`tms_schedulingbill` AS `s` ON `tsd`.`scheduling_billcode` = `s`.`scheduling_billcode`) JOIN `report_database_pro`.`tms_bms_detail_expense` AS `bcrd` ON ((`tsd`.`operation_billcode` = `bcrd`.`operation_billcode`) AND (`tsd`.`scheduling_billcode` = `bcrd`.`scheduling_billcode`) AND `bcrd`.`is_deleted` = 0)) LEFT JOIN `report_database_pro`.`mdc_customer_data` AS `m` ON `tsd`.`shipper_id` = `m`.`customer_data_id` WHERE (`s`.`departure_company` = 115 AND `tsd`.`is_deleted` = 0 AND `bcrd`.`billing_time` >= '2023-11-23 00:00:00.0' AND `bcrd`.`billing_time` <= '2023-11-24 00:00:00.0' AND `bcrd`.`billing_id` = '135968' AND `s`.`is_deleted` = 0)) AS `total`	report_database_pro	enabled	2023-11-23 17:01:02.443	2023-11-23 17:01:02.443	utf8mb4	utf8mb4_bin	capture	ac3a0c9e329ea37d0bb8565dc1b860ae4e46e4f423e7805e4f51664b0de059af

wzf0072 · 2024 年5 月 11 日 05:30

Navicat上查询也是走tikv.

DROP GLOBAL BINDING FOR

SELECT
count( ? )
FROM
(
SELECT
tsd.id
FROM
(
( report_database_pro.tms_schedulingbill_detail AS tsd JOIN report_database_pro.tms_schedulingbill AS s ON tsd.scheduling_billcode = s.scheduling_billcode )
JOIN report_database_pro.tms_bms_detail_expense AS bcrd ON ( ( tsd.operation_billcode = bcrd.operation_billcode ) AND ( tsd.scheduling_billcode = bcrd.scheduling_billcode ) AND bcrd.is_deleted = ? )
)
LEFT JOIN report_database_pro.mdc_customer_data AS m ON tsd.shipper_id = m.customer_data_id
WHERE
(
s.departure_company = ?
AND tsd.is_deleted = ?
AND bcrd.billing_time >= ?
AND bcrd.billing_time <= ?
AND bcrd.billing_id = ?
AND s.is_deleted = ?
)
) AS total
;

DROP GLOBAL BINDING FOR
SELECT
/+ hash_agg(@sel_1), inl_hash_join(@sel_2 report_database_pro.s), inl_join(@sel_2 report_database_pro.m), inl_hash_join(@sel_2 report_database_pro.tsd), use_index(@sel_2 report_database_pro.bcrd idx_billing_time), use_index(@sel_2 report_database_pro.tsd idx_tms_schedulingbill_detail_operation_billcode), use_index(@sel_2 report_database_pro.m customerdataid), use_index(@sel_2 report_database_pro.s idx_tms_schedulingbill_scheduling_billcode)/
count( 0 )
FROM
(
SELECT
tsd.id
FROM
((
report_database_pro.tms_schedulingbill_detail AS tsd
JOIN report_database_pro.tms_schedulingbill AS s ON tsd.scheduling_billcode = s.scheduling_billcode
)
JOIN report_database_pro.tms_bms_detail_expense AS bcrd ON (( tsd.operation_billcode = bcrd.operation_billcode ) AND ( tsd.scheduling_billcode = bcrd.scheduling_billcode ) AND bcrd.is_deleted = 0 ))
LEFT JOIN report_database_pro.mdc_customer_data AS m ON tsd.shipper_id = m.customer_data_id
WHERE
(
s.departure_company = 115
AND tsd.is_deleted = 0
AND bcrd.billing_time >= ‘2023-11-23 00:00:00.0’
AND bcrd.billing_time <= ‘2023-11-24 00:00:00.0’
AND bcrd.billing_id = ‘135968’
AND s.is_deleted = 0
)) AS total

删除这两个binding后，Navicat 和命令行都走tiflash了。

请问如何自行定位错误的binding呢？

wzf0072 · 2024 年5 月 11 日 06:05

在一望无际的binding大海中，如何捞到这个“sql_digest ”的呢？ (SQL 执行后，如何查找它的sql_digest )
select * from mysql.bind_info where sql_digest = ‘ac3a0c9e329ea37d0bb8565dc1b860ae4e46e4f423e7805e4f51664b0de059af’ ;
删除掉原来的binding后，已经生成新的binding.

SELECT /+ hash_agg(@sel_1), read_from_storage(@sel_1 tiflash[report_database_pro.s])/ count(0) FROM (SELECT tsd.id FROM ((report_database_pro.tms_schedulingbill_detail AS tsd JOIN report_database_pro.tms_schedulingbill AS s ON tsd.scheduling_billcode = s.scheduling_billcode) JOIN report_database_pro.tms_bms_detail_expense AS bcrd ON ((tsd.operation_billcode = bcrd.operation_billcode) AND (tsd.scheduling_billcode = bcrd.scheduling_billcode) AND bcrd.is_deleted = 0)) LEFT JOIN report_database_pro.mdc_customer_data AS m ON tsd.shipper_id = m.customer_data_id WHERE (s.departure_company = 115 AND tsd.is_deleted = 0 AND bcrd.billing_time >= ‘2024-03-25 00:00:00.0’ AND bcrd.billing_time <= ‘2024-04-01 00:00:00.0’ AND bcrd.billing_id = ‘135968’ AND s.is_deleted = 0)) AS total

aytrack · 2024 年5 月 11 日 06:08

这是因为你开启了自动捕获功能，可以通过添加黑名单的方式过滤是否捕获新的 binding https://docs.pingcap.com/zh/tidb/v6.5/sql-plan-management#自动捕获绑定-baseline-capturing

system · 2024 年7 月 10 日 06:08

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。