SQL索引选择有误SPM难以固定

FaNLoL · 2021 年5 月 31 日 10:20

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

【TiDB 版本】5.0.1

【问题描述】

业务SQL

SELECT
  id
FROM
  T1
WHERE
  (
    c1 = ?
    AND c2 IN (
    ?,
    ?,
    ?
    )
    AND c3 IN (?,?,?)
    AND c4 >= '2021-05-28 08:00:00'
    AND c4 <= '2021-05-29 07:59:59'
  )
ORDER BY
  c4 DESC,
  id DESC
LIMIT
  0, 100

c2和c3 IN的值, 数量是不固定的

select count(*) from db.T1;
+------------+
| count(*)   |
+------------+
| 2612900106 |
+------------+

select c2,count(*) from T1 group by c2 order by 2 desc limit 10;
+--------------------+-----------+
| c2                 | count(*)  |
+--------------------+-----------+
| 427945637011525655 | 631061462 |
| 427945637011525648 | 630671799 |
| 427945637028302855 | 340563208 |
| 427945637028302865 | 334951666 |
| 427945637032497158 | 130965452 |
| 437729683774115860 |  34841363 |
| 469583111051591695 |  34666223 |
| 427945637024108554 |  31489853 |
| 437729691776847902 |  19915815 |
| 427945637011525653 |  18927616 |
+--------------------+-----------+

select c3,count(*) from T1 group by c3 order by 2 desc limit 10;
+-------+------------+
| c3    | count(*)   |
+-------+------------+
| 11001 | 1255337216 |
| 20001 | 1253381078 |
| 10004 |   36179832 |
| 11101 |   33025270 |
| 20101 |   32935342 |
| 10001 |    1789644 |
| 14001 |      62054 |
| 10002 |      45632 |
| 13001 |      40920 |
| 16009 |      25936 |
+-------+------------+

select c4,count(*) from T1 group by c4 order by 2 desc limit 10;
+---------------------+----------+
| c4                  | count(*) |
+---------------------+----------+
| 2021-03-11 10:57:01 |     1150 |
| 2021-03-05 09:21:54 |     1138 |
| 2021-04-23 11:08:10 |     1129 |
| 2021-05-17 18:07:23 |     1128 |
| 2021-05-11 13:08:39 |     1121 |
| 2021-03-08 10:53:03 |     1120 |
| 2021-03-18 09:25:32 |     1115 |
| 2021-03-07 10:52:35 |     1113 |
| 2021-03-08 10:53:08 |     1113 |
| 2021-03-08 10:53:56 |     1112 |
+---------------------+----------+

表结构

show create table T1\G
*************************** 1. row ***************************
       Table: T1
Create Table: CREATE TABLE `T1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `xx1` varchar(64) NOT NULL DEFAULT '' COMMENT '交易号',
  `xx2` varchar(64) NOT NULL DEFAULT '' COMMENT '交易场景',
  `c3` int(11) NOT NULL DEFAULT '0' COMMENT '交易类型',
  `xx4` varchar(64) NOT NULL DEFAULT '' COMMENT '流水号',
  `xx5` varchar(64) NOT NULL DEFAULT '' COMMENT '业务流水号',
  `xx6` varchar(64) NOT NULL DEFAULT '' COMMENT '外部订单号',
  `c2` varchar(64) NOT NULL DEFAULT '' COMMENT '交易账号',
  `xx7` bigint(20) NOT NULL DEFAULT '0' COMMENT '交易金额',
  `xx8` bigint(20) NOT NULL DEFAULT '0' COMMENT '交易后余额',
  `xx9` varchar(128) NOT NULL DEFAULT '' COMMENT '交易备注',
  `xx10` varchar(128) NOT NULL DEFAULT '' COMMENT '对手方姓名',
  `xx11` varchar(128) NOT NULL DEFAULT '' COMMENT '对手方账号',
  `xx12` varchar(32) NOT NULL DEFAULT '' COMMENT '交易渠道',
  `xx13` varchar(64) NOT NULL DEFAULT '' COMMENT '交易渠道',
  `xx14` varchar(128) NOT NULL DEFAULT '' COMMENT '渠道流水号',
  `xx15` varchar(32) NOT NULL DEFAULT '' COMMENT '币种',
  `xx16` bigint(20) NOT NULL DEFAULT '0' COMMENT '汇率，扩大1e6',
  `c1` tinyint(4) NOT NULL DEFAULT '0' COMMENT '记账状态',
  `xx17` date DEFAULT NULL COMMENT '记账日期',
  `xx18` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `c4` datetime DEFAULT NULL COMMENT '入账时间',
  `xx19` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否同步流水 0:新账务流水 1:v2同步流水',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `uniq_idx_ref` (`xx4`),
  KEY `idx_1` (`xx5`),
  KEY `idx_2` (`xx6`),
  KEY `idx_3` (`c2`),
  KEY `idx_4` (`c3`),
  KEY `idx_5` (`c1`),
  KEY `idx_6` (`xx17`),
  KEY `idx_7` (`xx18`),
  KEY `idx_8` (`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2613372263 COMMENT='流水表'
1 row in set (0.00 sec)

Healthy

+----------+------------+----------------+---------+
| Db_name  | Table_name | Partition_name | Healthy |
+----------+------------+----------------+---------+
| db | T1       |                |      99 |
+----------+------------+----------------+---------+

业务SQL是变化的, 也就是说c1,c2,c3,c4是无法确定值(和数量的)
从上面的sql可以看出,c2,c3数据倾斜比较严重. 在MySQL中, 总是能选择正确的索引, 走c2或c3或c4
在TIDB中, 大部分SQL是选错索引的, 如果选对了0.02s查出结果, 选错可能查2小时

本来想使用SPM固定执行计划, 但是发现也不合适, 因为不同的sql走c2,c3,c4都是用可能的, use_index(T1, idx_3, idx_4, idx_8) 就没意义了
同时我还发现有时候无法匹配到创建的binding

举个例子 select * from t where c1 in (1,2,3)
我创建binding
create global binding for select * from t where c1 in (1,2,3) using select /*+ xxxx */ * from t where c1 in (1,2,3)

那么sql select * from t where c1 in (8,9,19,11,13)能匹配到这个binding吗, 就是说in里面值变了, in的值的数量也变了, 我实际使用看起来是匹配不上的, 那这样就完全没法搞了

这套TIDB现在是作为线上MySQL的从库, 通过DM同步数据, 核心交易还在MySQL跑, MySQL保留3个月数据, TIDB有全量数据(DM过滤delete event), 结果现在发现放在TIDB上的查询速度也不尽如人意,
可能有人说T1索引不好, 都是单列索引, 确实, 不过没法改了, 因为线上MySQL跑的很好, 不可能为了TIDB再去动索引.

现在有啥解决办法吗?

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

yilong · 2021 年5 月 31 日 13:05

请问 c1,c2,c3,c4是无法确定值(和数量的) 这里完全是无规律的吗？还是说 c2，c3 top 10 的值可以占到查询的 80% —90%？

FaNLoL · 2021 年6 月 1 日 02:06

c1c4 有规律, c2,c3就没规律了, 不符合"top 10 的值可以占到查询的 80% —90%" , 即便能占到80%-90%, 剩余20-10%的查询也不能接受超时(接口3s超时)

yilong · 2021 年6 月 1 日 03:34

我们在分析下是否有合适的方法，会尽快答复，多谢。

qizheng · 2021 年6 月 1 日 03:38

如果下游 TiDB 将单列索引改成组合索引，业务需要进行额外的改造么

FaNLoL · 2021 年6 月 1 日 03:42

只要下游TIDB的修改不会在之后因上游MySQL 做DDL导致DM同步异常就不需要改造. 不过感觉索引改造意义不大呢? 改成c2,c3,c4组合索引? 走index skip scan?

yilong · 2021 年6 月 1 日 06:08

具体哪些sql有问题，上传下 explain analyze sql 的结果
show global bindings 看下哪个绑定执行计划有问题
如果方便的话，导出下统计信息
https://docs.pingcap.com/zh/tidb/stable/statistics#统计信息的导入导出

FaNLoL · 2021 年6 月 1 日 07:10

感谢, 暂时无法提供了, 因为我目前通过global binding算是稳定住了线上吧(不过不敢保证以后没有问题), 所以现在有binding的情况找了几个sql执行计划都是对的, 现在线上在跑, 我也不敢删binding去做测试. 等有机会我再提供吧

14:38:04 [db]> show global bindings\G
*************************** 1. row ***************************
Original_sql: select count ( ? ) as `num` , `ifnull` ( sum ( `amount` ) , ? ) as `sum_amount` from `db` . `T1` where ( `c1` = ? and `c2` in ( ... ) and `c3` in ( ... ) and `c4` >= ? and `c4` <= ? )
    Bind_sql: SELECT /*+ USE_INDEX(`T1` `id3`, `idx4`)*/ COUNT(1) AS `num`,IFNULL(SUM(`amount`), 0) AS `sum_amount` FROM `db`.`T1` WHERE (`c1` = 1 AND `c2` IN ('537575323462586369','537678695138119693','537828424752869376','537819286719221762','537836464424140806','537836464424140808','537837753023377408','537836464424140810','537836464424140812','537837753023377410','537836464424140814','537842880232861698','537842880232861710','537849368728879104','537854992422322176','537854992422322186','537849368728879114','538026973843214342','538026973843214344','538049076822855686','538100342286499852','538100342286499854','538100342957584386','538100342957584388','538121636163928064','538100342957584390','538100342957584392','538121636163928066','538100342957584394','538121636163928068','538100342957584396','538129792327790602','538132036779552768','538129792327790604','538129792327790606','538132036779552772','538132036779552774','538158899547648000','538132036779552776','538158899547648002','538158899547648010','538158899547648012','538132036779552781','538132036779552783','538158899547648014','538179862163148802','538186833616486400','538186833616486406','538179862163148804','538179862163148806','538186833616486408','538186833616486410','538179862163148808','538186833616486412','538179862163148810','538179862163148812','538770198568677382') AND `c3` IN (11001,11002,11003) AND `c4` >= '2021-05-28 00:00:00' AND `c4` <= '2021-05-28 23:59:59')
  Default_db: db
      Status: using
 Create_time: 2021-05-31 15:10:30.534
 Update_time: 2021-05-31 15:10:30.534
     Charset: utf8
   Collation: utf8_general_ci
      Source: manual
*************************** 2. row ***************************
Original_sql: select `id` from `db` . `T1` where ( `c1` = ? and `c2` in ( ... ) and `c3` in ( ? ) and `c4` >= ? and `c4` <= ? ) order by `c4` desc , `id` desc limit ...
    Bind_sql: SELECT /*+ USE_INDEX(`T1` `id3`, `idx4`)*/ `id` FROM `db`.`T1` WHERE (`c1` = 1 AND `c2` IN ('524744873262649350','524744873262649351','524744873262649352','524744873262649355','524744873262649356','524744873262649357','524744873262649358','527165583977287681','527165583977287682','527165583977287691','527165583977287692','527165583977287693','527165583977287694','527231031330672642','527523339804127234','527523339804127245','527523339804127246','527541505372508164','527541505372508165','527588219076792321','527620307947663374','530441560127488001','530140433821929473','531261477290553358','531279080398565381','531553805662212098','531576527062618112','531976329306886145','530140433821929474','532327870802997259','530140433821929475','530140433821929476','532379041311801356','532798402140880897','532798402140880898','532798402140880911','533039270890356746','533039270890356747','533512397780987908','533691765295722511','533520792084856838','533520792084856839','533704061409607680','533520792084856840','533704061409607681','533704061409607682','533520792084856841','533520792084856842','533704061409607683','534072251893469193','534119072820940814','534119072820940815','534122654836248586','534122654836248587','534122654836248588','534197491936051200','534197491936051211','534197491936051212','534197554590560261','534197554590560262','534403836761133071','534422258198818830','534493896877588483','534477266449641477','534513460919189504','534515388759724037','534515388759724038','534582684018589703','534864371612954626','534886886355812361','534886886355812362','534886886355812363','534886886355812366','534886630582964232','534886630582964233','534886630582964234','534886886355812367','534896463428239360','534939570765090817','534939570765090818','534896463428239369','534939570765090819','534896463428239370','534896463428239371','534939570765090820','534896463428239372','534939570765090821','534939570765090822','534896463428239373','534939570765090823','534896463428239374','534939570765090824','535261710244691971','535249761104613387','535249761104613388','535261710244691978','535261710244691979','535261710244691980','535261710244691981','535249761104613389','535261710244691982','535261710244691983','535283930987810816','535284041545465856','535283930987810817','535283930987810818','535284041545465857','535284041545465858','535283930987810819','535283930987810820','535284041545465859','535283930987810821','535284041545465860','535283930987810822','535283930987810823','535284041545465861','535284041545465862','535283930987810824','535284041545465863','535283930987810825','535283930987810826','535284041545465864','535283930987810827','535284041545465865','535283930987810828','535283930987810829','535283930987810830','535299109783916545','535299109783916546','535299109783916549','535299109783916550','535331799484649485','535594485246115842','535600881513582596','535594485246115843','535600881513582597','535600881513582598','535594485246115844','535594485246115845','535594485246115846','535600881513582599','535594485246115847','535594485246115848','535594485246115849','535594485246115850','535600881513582600','535594485246115851','535600881513582601','535594485246115852','535600881513582602','535594485246115855','535634388642222092','535634388642222093','535634388642222094','535726561865805825','535726561865805826','535726561865805827','536230534603386890','536228231980826627','536230534603386891','536228231980826628','536228231980826629','536230534603386892','536230534603386895','536235238448218112','536228231980826636','536235238448218123','536235238448218124','536235238448218125','536228231980826637','536235238448218126','536235238448218127','536238157042073600','536228231980826638','536238157042073601','536238157042073602','536238157042073603','536240045338705921','536240045338705922','536240045338705923','536240045338705924','536240045338705925','536240045338705926','536238157042073606','536240045338705927','536240045338705928','536238157042073607','536240045338705929','536238157042073608','536238157042073609','536238157042073610','536240045338705932','536240045338705933','536238157042073611','536238157042073612','536246909279457292','536246909279457293','536247820642988033','536247820642988034','536247820642988037','536247820642988038','536247820642988039','536253817235230722','536253817235230723','536253817235230724','536260900907167750','536260900907167751','536260900907167752','536260900907167753','536260900907167754','536268883129053195','536268883129053196','536260900907167755','536268883129053197','536260900907167756','536268883129053198','536260900907167757','536260900907167758','536260900907167759','536291446391615488','536268883129053199','536292285332107264','536292285332107265','536292285332107266','536292285332107267','536291446391615489','536291446391615490','536291446391615491','536291446391615492','536292285332107268','536292285332107269','536291446391615493','536292285332107270','536292285332107271','536292285332107272','536292285332107273','536291446391615500','536664542357274632','536671706207531013','536671706207531014','536948282845347845','536993919926317059','536994122880307230','536994122880307231','537067667996065800','537067709888778244','537067709888778251','537068433003560963','537068520215719940','537068789376798724','537068789376798725','537068789376798730','537081801064099851','537081801257037833','537081801257037834','537099920625160198','537099920625160205','537100557152731138','537100695199854595','537100557152731145','537100695199854600','537100695199854605','537100557152731148','537101199032233990','537101449331527683','537101199032233993','537101449331527692','537232828312629257','537232828312629258','537232828312629259','537300875891232770','537301514830528512','537300875891232781','537301514830528517','537304437849374728','537305197425246215','537305197425246216','537307247773011970','537305197425246217','537307247773011971','537307247773011972','537318826329550860','537318826329550861','537318826329550862','537318825385840653','537318826329550863','537318825385840654','537321363262713856','537321363262713857','537321363262713858','537321363262713859','537318825385840655','537322364787019776','537321363262713870','537321363262713871','537322364787019777','537323081018945536','537323081018945537','537322364787019778','537322364787019779','537322364787019784','537322364787019785','537322364787019786','537322364787019787','537323081018945542','537323081018945547','537323081018945548','537322364787019788','537323081018945549','537322364787019789','537325617218109442','537323081018945550','537325617218109443','537325617218109444','537325617218109445','537325868402393089','537325868402393090','537325868402393091','537325868402393096','537325868402393097','537325617218109450','537325617218109451','537325868402393098','537325868402393099','537325868402393100','537327631092199424','537327631092199427','537327516805812229','537327631092199428','537327516805812230','537327631092199429','537327516805812231','537327516805812236','537327516805812237','537333687109341188','537333687788814338','537333687109341191','537333687788814339','537338201988190218','537390651810033678','537401397650440201','537401397650440202','537406455767158791','537406455767158792','537405079943163919','537410250601443328','537410250601443329','537406455767158793','537410250601443330','537406455767158794','537410250601443331','537406455767158795','537406455767158796','537410250601443332','537406455767158797','537410250601443333','537406455767158798','537406455767158799','537413539342635010','537413539342635011','537413836681031680','537413539342635012','537413539342635013','537413539342635014','537413539342635015','537413539342635016','537678695138119695','537678741778776069','537678741778776070','537693325419917322','537693325419917323','537693325419917324','537678741778776071','537693325419917325','537693325419917326','537693325419917327','537697163119996938','537678741778776072','537697163119996939','537697163119996940','537697163119996941','537678741778776073','537697163119996942','537697163119996943','537678741778776074','537700045730918400','538026973851598858','538026973851598859','538026973851598860','538026973851598861','538026973843214346','538026973851598862','538026973843214347','538026973851598863','538026973843214348','538049076822855680','538026973843214349','538026973843214350','538049076822855681','538026973843214351','538049076822855682','538049925200527360','538049925200527361','538049076822855683','538049076822855684','538049925200527362','538049925200527363','538049076822855685','538049925200527364','538049925200527371','538132036779552778','538158899547648004','538132036779552779','538158899547648005','538132036779552780','538158899547648006','538158899547648007','538158899547648008','538158899547648009','538179862163148801','538391285216628740','538391285216628741','538490384708321290','538491647369986052','538491647369986061','538490384708321293','538490384708321294','538491647369986062','538761504254640137','538491647369986063','538763038954668036','538762527283130378','538762527283130379','538762527283130380','538763038954668037','538770198568677384','538804791028011019','538804791028011020','538804791028011021','538804523506913289','538804791028011022','538804523506913290','538804523506913291','538804791028011023','538834084038758400','538834084038758401','538834084038758402','538834084038758403','538834084038758404','538804523506913292','538804523506913293','538834084038758405','538834084038758406','538836274144919556','538834084038758407','538836274144919565','538836631281520640') AND `c3` IN (110001) AND `c4` >= '2021-05-01 00:00:00' AND `c4` <= '2021-05-31 23:59:59') ORDER BY `c4` DESC,`id` DESC LIMIT 0,20
  Default_db: db
      Status: using
 Create_time: 2021-05-31 17:07:55.722
 Update_time: 2021-05-31 17:07:55.722
     Charset: utf8
   Collation: utf8_general_ci
      Source: manual
*************************** 3. row ***************************
Original_sql: select `id` from `db` . `T1` where ( `c1` = ? and `c2` in ( ... ) and `c3` in ( ... ) and `c4` >= ? and `c4` <= ? ) order by `c4` desc , `id` desc limit ...
    Bind_sql: SELECT /*+ USE_INDEX(`T1` `idx4`, `id3`)*/ `id` FROM `db`.`T1` WHERE (`c1` = 1 AND `c2` IN ('525853901912592392','525868485553737729','525868741431443470','525868485553737731','526081610638999561','526087495339786244','526081610638999563','526137642434220033','526087495339786246','526137642434220035','526137642434220037','526157855485636623','526184463487520778','526184463487520780','526190421022396417','526184463487520782','526257977791918080','526190421022396419','526257977791918082','526190421022396421','526190421022396423','526190421022396425','526190421022396427','526257977791918084','526190421022396429','526257977791918086','526190421022396431','526556472998412298','526554170656870409','526556472998412300','526554170656870411','526554170656870413','526556472998412302','526631532492804096','526796462714048526','526827932518240271','526871171593981961','526886266894073868','526886266894073870','526928734607945728','526938204977283077','526938204977283079','526928734607945730','526938204977283081','526928734607945732','526938204977283083','526938204977283085','526928734607945734','526928734607945736','526928734607945742','526988760324288519','527234268938682368','527238064167239686','527239855164403716','527239855164403718','527239855164403720','527254438843305986','527239855164403722','527239855164403724','527254438843305998','527294607592050696','527311181573042176','527311181573042178','527294607592050702','527376658060066816','527588220876144654','527615702492827658','527643814987354112','527633829649760259','527643814987354114','527643814987354120','527633829649760267','527693354390040584','527693354390040586','528060474986647553','527693354390040588','528060474986647555','527693354390040590','528060474986647557','528060474986647559','528060474986647567','528633260469698564','528633260469698566','528633260469698568','528759588942565377','528759588942565379','528805551996452866','528759588942565389','528805551996452872','528805551996452874','528814147077451783','528818023029587976','528814147077451789','528818023029587978','528814147077451791','529420794531467264','529420794531467266','529420794531467268','529420794531467270','529114963021713419') AND `c3` IN (11001,11002,11003) AND `c4` >= '2021-05-28 08:00:00' AND `c4` <= '2021-05-29 07:59:59') ORDER BY `c4` DESC,`id` DESC LIMIT 0,100
  Default_db: db
      Status: using
 Create_time: 2021-05-31 15:23:44.424
 Update_time: 2021-05-31 15:23:44.424
     Charset: utf8
   Collation: utf8_general_ci
      Source: manual
3 rows in set (0.00 sec)

不过有个问题还想确认下
第二个binding Original_sql是

select `id` from `db` . `T1` where ( `c1` = ? and `c2` in ( ... ) and `c3` in ( ? ) and `c4` >= ? and `c4` <= ? ) order by `c4` desc , `id` desc limit ...

第三个binding Original_sql是

select `id` from `db` . `T1` where ( `c1` = ? and `c2` in ( ... ) and `c3` in ( ... ) and `c4` >= ? and `c4` <= ? ) order by `c4` desc , `id` desc limit ...

我注意到这两个sql一个c3 in 多值, 一个c3 in 单值, 所以这里Original_sql 一个为

`c3` in ( ? )

一个为

`c3` in ( ... )

请问 c3 in ( ? ) 是否等于 c3 in ( ... ) ?
就是说以下语句是否能够匹配第二个binding?

select `id` from `db` . `T1` where ( `c1` = 1 and `c2` in ( 1,2,3 ) and `c3` in ( 1,2,3 ) and `c4` >= ''2021-05-28 08:00:00'' and `c4` <= '2021-05-29 07:59:59' ) order by `c4` desc , `id` desc limit 0,20

time-and-fate · 2021 年6 月 1 日 08:16

是的，可以参考 SPM 相关文档，SQL 是在标准化之后进行匹配的，像这样的可以提供参数列表的地方标准化的时候会有特殊处理，单个参数会被标准化成 ? ，而多个参数会被标准化成 ...，因此需要分别创建 binding 来覆盖所有情况。

time-and-fate · 2021 年6 月 1 日 08:18

那还方便提供一下统计信息吗？可以参考相关文档导出。

FaNLoL · 2021 年6 月 1 日 09:49

好的明白了, 这个文档看过, 没有提到这个"坑", 这样的话可能需要建4个binding
c2 单值 c3 单值
c2 单值 c3 多值
c2 多值 c3 单值
c2 多值 c3 多值 …

FaNLoL · 2021 年6 月 1 日 10:07

db_t1.json.zip (1.7 MB)

time-and-fate · 2021 年6 月 1 日 11:26

多谢反馈。
文档的细节我们后面会增加相关说明；提供的统计信息我们也会尽快分析来定位问题根本原因。