TiDB程序&手动执行SQL 执行计划不一致

jiaxin · 2024 年2 月 26 日 03:02

问题

相同SQL程序查询1天内数据慢，手动查询1天内数据快。最近上线的某个偏AP类业务从MySQL迁移到TiDB，应用程序SQL无改动

版本

TiDB版本6.1.5

Mybatis prepare预编译形式发送的SQL

jdbc版本

jdbc:mysql://%s:%s/%s?characterEncoding=UTF-8&rewriteBatchedStatements=true&allowMultiQueries=true&connectTimeout=2000&socketTimeout=600000

表结构索引

#SQL语句
select xxxx FROM
  xxxxx_004 l
WHERE
  l.mart_code = 'xxxx'
  AND l.yn = 1
  AND l.supplier_code = ?
  AND l.biz_date >= ?
  AND l.biz_date <= ?
ORDER BY
  created DESC,
  id DESC
LIMIT
  40 [arguments: ("xxxx", "2024-01-18 00:00:00", "2024-01-18 00:00:00")];

#表结构
CREATE TABLE `xxxxxx_004` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mart_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `ledger_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `busi_no` varchar(650) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT xxxx,
  `supplier_code` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT xxxx,
  `supplier_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `contract_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `contract_type` tinyint(4) DEFAULT NULL COMMENT xxxx,
  `direct_source_no` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT xxxx,
  `exec_detail_uk` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT xxxx,
  `schedule_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `profit` tinyint(4) DEFAULT NULL COMMENT xxxx,
  `discount_way` tinyint(4) DEFAULT NULL COMMENT xxxx,
  `sku_id` bigint(20) DEFAULT NULL,
  `goods_code` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `goods_name` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `shop_code` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `shop_type` tinyint(4) DEFAULT NULL COMMENT xxxx,
  `store_id` bigint(20) DEFAULT NULL COMMENT xxxx,
  `shop_name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `rebate_type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `contract_state` tinyint(4) DEFAULT NULL COMMENT xxxx,
  `create_type` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantity` decimal(20,5) DEFAULT NULL COMMENT xxxx,
  `channel_type` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT xxxx,
  `ledger_amount_taxed` decimal(20,5) DEFAULT NULL COMMENT xxxx,
  `ledger_amount_untaxed` decimal(20,5) DEFAULT NULL COMMENT xxxx,
  `ledger_amount_attached` decimal(20,5) DEFAULT NULL COMMENT xxxx,
  `amount_taxed` decimal(20,5) DEFAULT NULL COMMENT xxxx,
  `amount_untaxed` decimal(20,5) DEFAULT NULL COMMENT xxxx,
  `goods_tax_code` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT xxxx,
  `goods_tax_ratio` decimal(20,4) DEFAULT NULL COMMENT xxxx,
  `tax_ratio` decimal(20,8) DEFAULT NULL COMMENT xxxx,
  `collect_date` date DEFAULT NULL COMMENT xxxx,
  `mode` tinyint(4) DEFAULT NULL COMMENT xxxx,
  `bill_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `order_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `biz_start_date` date DEFAULT NULL,
  `biz_end_date` date DEFAULT NULL COMMENT xxxx,
  `biz_date` date DEFAULT NULL COMMENT xxxx,
  `contract_day` date DEFAULT NULL COMMENT xxxx,
  `category_mi` varchar(32) COLLATE          DEFAULT '',
  `category_sm` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `category_la` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `unit` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT xxxx,
  `created_user` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `extend_filed` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT xxxx,
  `remarks` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `yn` tinyint(4) NOT NULL DEFAULT '1')


#表索引
PRIMARY (id)
 uk_ledgerno (ledger_no,mart_code,yn)
 idx_billno (bill_no)
 idx_orderno (order_no)
 idx_collectdate_billno_orderno (collect_date,bill_no,order_no,supplier_code,contract_no,rebate_type,contract_type,profit)
 idx_directsourceno_rebatetype_busino (direct_source_no,rebate_type,busi_no,yn)
 idx_suppliercode_busino (supplier_code,busi_no)
 idx_suppliercode_contractno_bizdate (supplier_code,contract_no,biz_date)
 idx_suppliercode_bizdate (supplier_code,biz_date)
 idx_suppliercode_shopcode_bizdate (supplier_code,shop_code,biz_date)
 idx_bizdate (biz_date)
 idx_contractno (contract_no)
 idx_busino (busi_no)
 idx_suppliercode_bizstartdate (supplier_code,biz_start_date,biz_end_date)
 idx_created_suppliercode_rebatetype (created,supplier_code,rebate_type)
 idx_modified_created_mart (modified,created,mart_code)

表现

业务查询一天内数据页面超时

业务查询多天内数据页面反而快（走到了正确联合索引）

排查分析

TiDB dashboard中有几个3分多钟慢查询

表统计信息健康度为：99良好

TiDB集群自身无压力

页面程序查询一天内数据和人工手动执行走到不同索引（相同SQL）

程序执行（TiDB）	人工手动执行（TiDB）	人工手动执行（MySQL）
查询条件：WHERE l.mart_code = ‘xxxx’ AND l.yn = 1 AND l.supplier_code = ? AND l.biz_date >= ? AND l.biz_date <= ? ORDER BY created DESC, id DESC LIMIT 40 [arguments: (“xxxx”, “2024-01-18 00:00:00”, “2024-01-18 00:00:00”)];
走索引:idx_suppliercode_bizstartdate(supplier_code, biz_start_date, biz_end_date)，相当于只走到了一个字段条件supplier_code索引过滤，另一个条件biz_date没走到索引过滤 → 耗时3.5分钟(不符合预期)	走索引:index:idx_suppliercode_bizdate(supplier_code, biz_date) → 耗时183ms(符合预期)	走索引：idx_suppliercode_bizdate(supplier_code, biz_date) → 耗时54ms(符合预期)

截图信息：
程序执行（TiDB）

人工手动执行（TiDB）

人工手动执行（MySQL）

临时解决

程序临时增加新查询条件（SQL走到联合索引）
研发显示在代码SQL中增加强制索引force index（SQL类型多改造难度大）
TiDB层面创建执行计划绑定（SQL行数多需要参数化不好创建）

如
CREATE BINDING FOR SELECT * FROM t WHERE a > 1 USING SELECT * FROM t use index(idx) WHERE b > 2;

已解决

关闭计算节点执行计划缓存后，业务应用研发去掉代码强制索引，页面报表查询1天内数据变快（走到了正确联合索引）

# 3个计算节点分别执行
ADMIN FLUSH INSTANCE PLAN_CACHE;
# 全局执行一次
set global tidb_enable_prepared_plan_cache=false;

Kongdom · 2024 年2 月 26 日 03:32

手动执行sql是完全从慢查询页面中复制出来的么？遇到过几次是where条件类型转换导致的慢查询，但是手工执行给参数的时候给的是正确的类型。

wangccsy · 2024 年2 月 26 日 05:13

这个个人感觉应该是可能的，如果你使用了ORM工具更是可能。因为ORM本身（或者JDBC的实现）会进行一些个SQL的优化。

jiaxin · 2024 年2 月 26 日 05:40

是dashboard里慢SQL，我之前打开tidb的general_log看记录的和dashboard里一样

jiaxin · 2024 年2 月 26 日 05:42

我之前打开tidb的general_log查过，没发现有set会话变量的设置（之前怀疑有，也可能tidb general_log没输出set会话变量语句）

Kongdom · 2024 年2 月 26 日 05:46

程序里这样处理后，程序执行的sql计划和手工执行的sql计划就一样了？

WinterLiu · 2024 年2 月 26 日 05:50

用新不用旧，升级tidb可能会解决问题

jiaxin · 2024 年2 月 26 日 06:03

是的了

jiaxin · 2024 年2 月 26 日 06:04

Kongdom · 2024 年2 月 26 日 06:25

使用force index后，可以看一下两种情况后面这一列的内容一样么？

小龙虾爱大龙虾 · 2024 年2 月 26 日 08:58

猜测是PLAN_CACHE的问题，你的范围传值l.biz_date >=和l.biz_date俩是相等的，导致执行计划不能走这个联合索引的范围扫描，建议：
mybatis框架应该有个属性，应该是设置statementType为STATEMENT，让这个SQL不走prepare sataement接口，测试下

有猫万事足 · 2024 年2 月 26 日 09:11

楼上这个plan cache的猜测感觉挺靠谱的。

毕竟如果不是plan cache，想不出为啥同一个sql，相同的条件，手动和程序执行计划会不一样。

https://docs.pingcap.com/zh/tidb/stable/sql-prepared-plan-cache#手动清空计划缓存

可以考虑考虑把某个tidb实例上的plan cache清掉，看看是否会都变慢，或者都变快。

Trouble · 2024 年2 月 27 日 02:37

检查下jdbc连接参数useServerPrepStmts，两部分是否一致

redgame · 2024 年2 月 27 日 05:59

这让人费解呀。。。完全相同不会走出这种

changpeng75 · 2024 年2 月 27 日 06:02

感觉索引的规划有问题，索引的数量有点多。
复合索引可以尝试把日期字段放到前面，把supplier_code放到后面。

jiaxin · 2024 年2 月 28 日 06:00

还真可能和plan cache有关，我验证下，之前另一套高TPS的TiDB集群计算节点内存一直涨，关闭执行计划缓存后内存就正常了

jiaxin · 2024 年2 月 28 日 07:58

目前问题已解决，关掉计算节点执行计划缓存后，页面报表查询响应快，符合预期（走到了正确联合索引）

system · 2024 年4 月 28 日 07:58

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