连表查询后.多增加一个字段都会导致一个莫名的几千万数据扫描

johnwa-CD · 2022 年7 月 13 日 01:53

【 TiDB 使用环境】生产环境【 TiDB 版本】5.0.6 【遇到的问题】SQL性能问题【复现路径】做过哪些操作出现的问题`
【问题现象及影响】
连表操作,很常规的一个语句

CREATE TABLE FbAdAsset (
Id bigint(20) NOT NULL AUTO_INCREMENT,
date_start datetime NOT NULL,
date_stop datetime NOT NULL,
AssetId varchar(128) NOT NULL,
FbAccount varchar(128) NOT NULL,
AdCampId varchar(128) NOT NULL,
AssetUrl varchar(512) NOT NULL,
AssetThumbUrl varchar(512) DEFAULT NULL,
AssetTitle varchar(512) NOT NULL,
Amount decimal(10,2) DEFAULT NULL,
PayNum int(11) NOT NULL,
Installs int(11) DEFAULT NULL,
Clicks int(11) DEFAULT NULL,
Impressions int(11) DEFAULT NULL,
Spend decimal(10,2) DEFAULT NULL,
Reach int(11) DEFAULT NULL,
Roas decimal(10,2) NOT NULL,
IsVideo int(11) NOT NULL,
UpdateTime datetime NOT NULL,
CreateTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (Id) /*T![clustered_index] NONCLUSTERED */,
KEY date_start (date_start,date_stop),
KEY assetid (AssetId),
KEY date_start_2 (date_start,AssetId),
KEY index_FbAccount (FbAccount),
KEY index_AssetTitle_IsVideo (AssetTitle,IsVideo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=370170001

CREATE TABLE FbAccount (
Id int(11) NOT NULL AUTO_INCREMENT,
Account varchar(128) NOT NULL,
Secret varchar(128) DEFAULT NULL,
PageId varchar(128) DEFAULT NULL,
AppId varchar(128) DEFAULT NULL,
AppUrl varchar(500) DEFAULT NULL,
CreatTime datetime(3) NOT NULL,
ProductId int(11) NOT NULL,
ProductName varchar(128) NOT NULL,
Mt int(11) DEFAULT NULL,
Token varchar(500) DEFAULT NULL,
InsId varchar(500) DEFAULT NULL,
Status int(11) NOT NULL,
AutoFillAd int(11) NOT NULL,
UpdateStatus int(11) NOT NULL,
Chl varchar(128) DEFAULT NULL,
Core int(11) NOT NULL,
FbAdRuleId int(11) NOT NULL,
AdAutoActive int(11) NOT NULL,
StatusChangeTime datetime(3) NOT NULL,
FbAccountType int(11) NOT NULL,
RowVersion bigint(20) DEFAULT NULL,
SpendCap bigint(20) DEFAULT ‘0’,
AmountSpent bigint(20) DEFAULT ‘0’,
PutProductId int(11) NOT NULL DEFAULT ‘0’,
CurrentLanguage2 int(11) DEFAULT NULL,
AccountAdType int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (Id) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY FbAccount_Account_IDX (Account)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=3810145

查询语句:
EXPLAIN

select ad.*,act.ProductId,act.Mt,act.Core,act.CurrentLanguage2 from FbAdAsset ad
INNER JOIN FbAccount act ON ad.FbAccount = act.Account
where date_start >= ‘2022-06-30’ AND date_start <= ‘2022-07-07’
AND Core = 1
AND ProductId = 3366 AND Mt = 1
limit 100

执行计划

Limit_15	0.67	root		offset:0, count:100
└─Projection_17	0.67	root		db1.fbadasset.id, db1.fbadasset.date_start, db1.fbadasset.date_stop, db1.fbadasset.assetid, db1.fbadasset.fbaccount, db1.fbadasset.adcampid, db1.fbadasset.asseturl, db1.fbadasset.assetthumburl, db1.fbadasset.assettitle, db1.fbadasset.amount, db1.fbadasset.paynum, db1.fbadasset.installs, db1.fbadasset.clicks, db1.fbadasset.impressions, db1.fbadasset.spend, db1.fbadasset.reach, db1.fbadasset.roas, db1.fbadasset.isvideo, db1.fbadasset.updatetime, db1.fbadasset.createtime, db1.fbaccount.productid, db1.fbaccount.mt, db1.fbaccount.core, db1.fbaccount.currentlanguage2
└─IndexHashJoin_26	0.67	root		inner join, inner:IndexLookUp_23, outer key:db1.fbaccount.account, inner key:db1.fbadasset.fbaccount, equal cond:eq(db1.fbaccount.account, db1.fbadasset.fbaccount)
├─TableReader_73(Build)	0.00	root		data:Selection_72
│ └─Selection_72	0.00	cop[tiflash]		eq(db1.fbaccount.core, 1), eq(db1.fbaccount.mt, 1), eq(db1.fbaccount.productid, 3366)
│ └─TableFullScan_71	958.00	cop[tiflash]	table:act	keep order:false, stats:pseudo
└─IndexLookUp_23(Probe)	701967.61	root
├─IndexRangeScan_20(Build)	37026963.56	cop[tikv]	table:ad, index:index_FbAccount(FbAccount)	range: decided by [eq(db1.fbadasset.fbaccount, db1.fbaccount.account)], keep order:false
└─Selection_22(Probe)	701967.61	cop[tikv]		ge(db1.fbadasset.date_start, 2022-06-30 00:00:00.000000), le(db1.fbadasset.date_start, 2022-07-07 00:00:00.000000)
└─TableRowIDScan_21	37026963.56	cop[tikv]	table:ad	keep order:false

但是只有使用一个条件的时候,就没有这个 range: decided by [eq(db1.fbadasset.fbaccount, db1.fbaccount.account)], keep order:false 这个看到estRows 返回了 3000多万行

------语句:
EXPLAIN

select ad.*,act.ProductId,act.Mt,act.Core,act.CurrentLanguage2 from FbAdAsset ad
INNER JOIN FbAccount act ON ad.FbAccount = act.Account
where date_start >= ‘2022-06-30’ AND date_start <= ‘2022-07-07’
AND Core = 1

执行计划:

Projection_10	672484.97	root		db1.fbadasset.id, db1.fbadasset.date_start, db1.fbadasset.date_stop, db1.fbadasset.assetid, db1.fbadasset.fbaccount, db1.fbadasset.adcampid, db1.fbadasset.asseturl, db1.fbadasset.assetthumburl, db1.fbadasset.assettitle, db1.fbadasset.amount, db1.fbadasset.paynum, db1.fbadasset.installs, db1.fbadasset.clicks, db1.fbadasset.impressions, db1.fbadasset.spend, db1.fbadasset.reach, db1.fbadasset.roas, db1.fbadasset.isvideo, db1.fbadasset.updatetime, db1.fbadasset.createtime, db1.fbaccount.productid, db1.fbaccount.mt, db1.fbaccount.core, db1.fbaccount.currentlanguage2
└─HashJoin_42	672484.97	root		inner join, equal:[eq(db1.fbaccount.account, db1.fbadasset.fbaccount)]
├─TableReader_66(Build)	0.96	root		data:Selection_65
│ └─Selection_65	0.96	cop[tiflash]		eq(db1.fbaccount.core, 1)
│ └─TableFullScan_64	958.00	cop[tiflash]	table:act	keep order:false, stats:pseudo
└─IndexLookUp_78(Probe)	3500236.02	root
├─IndexRangeScan_76(Build)	3500236.02	cop[tikv]	table:ad, index:date_start_2(date_start, AssetId)	range:[2022-06-30 00:00:00,2022-07-07 00:00:00], keep order:false
└─TableRowIDScan_77(Probe)	3500236.02	cop[tikv]	table:ad	keep order:false

感觉只是一个常规的join表操作查询.怎么会突然变成这样,查询不出来
FbAdAsset 总数据大概是 1.7亿 FbAccount 大概是958行
【附件】

请提供各个组件的 version 信息，如 cdc/tikv，可通过执行 cdc version/tikv-server --version 获取。

ddhe9527 · 2022 年7 月 13 日 02:09

两个SQL里面act表和ad表的JOIN方式不一样。第一个SQL(带limit 100那个)走的是IndexHashJoin，ad表使用了关联字段FbAccount上的索引index_FbAccount。第二个SQL走的是HashJoin，ad表走的是date_start字段上的索引date_start_2。看起来index_FbAccount这个索引的筛选能力很差，所以出现了estRows 很大的情况，可以给第一个SQL加个hint试试

/*+USE_INDEX(ad date_start_2)*/

johnwa-CD · 2022 年7 月 13 日 02:33

非常感谢,确实解决了.我有点疑惑的是 . 我只是增加了一个查询条件.
他这个索引使用感觉就未能按预期执行了
就一个是查询条件是 Mt = 1 另外一个是 Mt=1 AND ProductId=3366

这两个条件,会有不同的索引使用.我比较疑惑
上面的这个 Limit 并不是关键,是我截取的时候少加了

ddhe9527 · 2022 年7 月 13 日 02:50

我咋感觉和LIMIT有关系呢，可以测一下第二个SQL把limit 100加上，看优化器选择哪种JOIN方式。带LIMIT的话CBO优化器应该更倾向于使用IndexJoin或者IndexHashJoin，而不是HashJoin，这也导致了被驱动表在选择索引的时候有差异，因为HashJoin的两张表，在关联字段上是不会使用索引的

johnwa-CD · 2022 年7 月 13 日 03:00

johnwa-CD · 2022 年7 月 13 日 03:01

老师,我上面这两个查询语句的截图
我都不用limit ,差别也是 where 后面是 1条件还是 >1 条件

johnwa-CD · 2022 年7 月 13 日 03:03

另外,我还有一个疑惑,为什么这种查询,不会走 TiFlash呢
我试了加上各种参数,怎么才能让他走 TiFlash查询
FbAdAsset 我也是有添加到 TiFlash 中的

set @@session.tidb_allow_batch_cop=1;
set @@session.tidb_opt_broadcast_join=1;
加进去也没用.我是 5.0.6

johnwa-CD · 2022 年7 月 13 日 03:06

johnwa-CD · 2022 年7 月 13 日 03:06

对于tiflash的使用时机,也不是太能理解

ddhe9527 · 2022 年7 月 13 日 03:09

从图上看，优化器是觉得加上这两个条件后，act表的规模已经足够小了，TableReader72那里几乎为0，所以act表作为驱动表走NL会很快，从而导致被驱动表走了关联字段上的索引

johnwa-CD · 2022 年7 月 13 日 03:18

有点颠覆我对数据库的认知.
因为我以前多数都是在 SQLServer上做这种,迁移到TiDB 后有一些语句的行为真的很难理解
在sqlserver里面这种无论如何,优化器都应该不会选择错误的索引
因为这种查询语句,实在太常见了 .
大表关联小的字典表.然后多个条件过滤

ddhe9527 · 2022 年7 月 13 日 03:29

TiFlash上没有索引(不考虑Dleta Tree内部Pack级的Min-Max索引)，所以如果优化器觉得要走全表扫描，并且有TiFlash副本可用，那是可能选择使用它的。如果优化器决定走索引，那肯定就是TiKV。你这两个SQL最大的区别就是时间范围大小的区别，limit也有一定影响

SQL Server也有走错索引的时候，Oracle也有。TiDB的优化器还在打磨

johnwa-CD · 2022 年7 月 13 日 04:20

嗯.还是感谢老师的帮助解决了上面的问题,这种就是说.相当于强制走指定索引查询对吧

ddhe9527 · 2022 年7 月 13 日 04:40

是的，用hint让SQL使用指定的索引，还有很多其它hint，可参考如下文档
https://docs.pingcap.com/zh/tidb/stable/optimizer-hints#optimizer-hints

cs58_dba · 2022 年7 月 15 日 02:37

最近刚学了PCTP的内容，视频中的教学内容可供参考：

方案一: 及时收集统计信息
- 考虑使用 analyze table 来手动收集统计信息，或者结合 cron job 的方式。
- 调整 tidb_auto_analyze_ratio、tidb_auto_analyze_start_time 和 tidb_auto_analyze_end_time 参数提高收集的频次，扩大收集的窗口时间。
方案二: 更改执行计划
- 使用 hint 或者 use index 语句固化执行计划。
- 使用 sql hint 的方式更改执行计划。

system · 2022 年10 月 31 日 19:19

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