range分区表order by 索引列limit 1 长时间未返回

Bug 反馈
清晰准确地描述您发现的问题,提供任何可能复现问题的步骤有助于研发同学及时处理问题
【 TiDB 版本】
Server version: 5.7.25-TiDB-v6.1.0

【 Bug 的影响】
order by 索引列长时间未返回结果

【可能的问题复现步骤】
背景说明:该表总行数有130亿行所有,按天分区,每个分区约1亿行左右。

  1. 分区表
    主键索引: PRIMARY KEY (dt,doc_id) /*T![clustered_index] NONCLUSTERED */,
    PRIMARY KEY (dt,doc_id) /*T![clustered_index] NONCLUSTERED */,
    KEY updatetime (updatetime),
    KEY newdate (newdate)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![placement] PLACEMENT POLICY=storeonssd */
    PARTITION BY RANGE (UNIX_TIMESTAMP(dt))
    (PARTITION p20210601 VALUES LESS THAN (1622563200),
    …)

  2. explain
    所执行的SQL: select * from logoutrole order by dt desc limit 1 ;

  3. 执行,长时间未返回


    3.查看explain计划

    4.执行explain analyze 以及重跑变快

  4. 使用完整的主键索引order by ,也长时间未出结果

【看到的非预期行为】
长时间未返回结果,且执行计划有TableFullScan_12 算子出现

【期望看到的行为】
执行计划不需要TableFullScan,且可以很快返回查询内容

【相关组件及具体版本】
tidb
v6.1.0

【其他背景信息或者截图】
如集群拓扑,系统和内核版本,应用 app 信息等;如果问题跟 SQL 有关,请提供 SQL 语句和相关表的 Schema 信息;如果节点日志存在关键报错,请提供相关节点的日志内容或文件;如果一些业务敏感信息不便提供,请留下联系方式,我们与您私下沟通。

/*+USE_INDEX(logoutrole PRIMARY)*/是否有效?mysql客户端要加--comments

一样的执行计划。登录时已加 --comments

我看第二个force index生效了,也还是很慢吗

是的,跑了15min以上都还没出结果

表很大吗? 收集下统计信息试试

表很大,约140亿行

analyze执行命令也要很久

看着更像是 TiDB 对于分区表下 order by idx_col limit m, n 执行计划太差的问题。

当前的执行计划,是对每个分区计算出 topN 后,把各个分区的数据汇总计算 topN,得到的结果应当是正确的,但是没有用好索引的本身有序性。

因为当前排序的列是主键列 dt,理想的执行计划应该是按照这样的逻辑执行:

  1. 分区键是 dt,那么各个分区之间,基于 dt 有序,分区 p1 内的 dt 值都小于分区 p2 的 dt 值
  2. 分区内 dt 是索引(主键索引),那么按照 dt 的索引,分区内 dt 是有序的

基于以上的有序性,按照顺序扫描分区表,对于分区表内部,使用 dt 的索引就可以得到 order by dt limit m, n 的结果了,也就是跟使用非分区表类似的效率,使用 limit 算子就可以了。

对于当前的结果,因为不清楚这里 dt 的类型定义,有没有这种可能:
UNIX_TIMESTAMP(dt) 的影响,就是说对 dt 列的值进行 UNIX_TIMESTAMP(dt) 后,原来 dt 值的顺序跟 UNIX_TIMESTAMP(dt) 的顺序不一致,导致可能要扫描所有的分区。
这种可能性较小,而且即使是这种影响,也不需要对分区表内执行全表扫,因为分区表内 dt 索引仍然有序,对于这样的情况,这种执行计划就行了:

  1. 对所有分区表的结果执行 topN
  2. 对于分区表内部执行 limit

所以 TiDB 对于 range 分区,这种情况,生成的执行计划不够优秀感觉有很大关系。

基于当前的执行计划,耗时较久就要看下机器性能以及扫表相关相关的参数了,毕竟表数据量大,如果磁盘性能不佳,扫描并发参数较低,130亿的数据,耗时较久很有可能的。

1赞

如果有tiflash,应该就秒出:smile:

set session tidb_partition_prune_mode=‘dynamic’ 这样跑下试试,虽然你的SQL涉及不到裁剪的问题,但以前确实有因为不能动态裁剪导致用不了索引

这个集群一直都是dynamic的了,

mysql> show variables like ‘tidb_partition_prune_mode’ ;
±--------------------------±--------+
| Variable_name | Value |
±--------------------------±--------+
| tidb_partition_prune_mode | dynamic |
±--------------------------±--------+
1 row in set (0.01 sec)

mysql>

感谢回复。
赞同是TiDB对于range分区表生成执行计划不够优秀的问题,我直接替换为其他普通索引,如 KEY updatetime (updatetime), 执行计划也需要进行 TableFullScan_12:

这里执行计划,那个 TopN_13 算子如果改为带有 keep order true 的 limit 算子就好了:

  1. limit 走 updatetime 索引 keep order true,扫出来第一个(满足limit m, n)
  2. TopN_7 对各个分区子表(各个分区)返回的 limit 结果,进行一个 topN 计算返回数据

那这个问题就稍微麻烦,如果有这种 order by limit m, n 查询的强需求,还真没有好的查询方法。

或者试着加范围查询,例如 where updatetime > “xx” and updatetime < “xxx” order by updatetime limit m, n,通过指定索引列范围,让它走 indexRangeScan 算子,这样速度可以加快。

但是要依赖能不能合理添加这个 updatetime 范围,如果不能好像就无办法。只能期望对这种查询,TiDB 执行计划优化,选择更优的执行计划,等新功能优化版本。


执行: select *from mars_p1log.logoutrole order by updatetime desc limit 1 \G
耗时:1 row in set (3 hours 16 min 56.07 sec)

如你所言,加了索引列的范围查询后,它不再是TableFulScan而且变为了IndexRangeScan_17,响应时间有所缩短

执行: select *from mars_p1log.logoutrole where updatetime>‘2022-06-30 00:00:00’ order by updatetime desc limit 1 ;
耗时:1 row in set (5 min 11.59 sec)

学到了这招 用来指定范围

但执行计划里仍然要扫描大量的数据,这个问题还是未解决
预期是在很短时间内就有返回,需要和官方反馈下这个issue

  1. 表结构方便发一下吗? 或者设计到的这些列的属性
  2. 这个查看的是 order by desc 取 limit 1。 如果不 desc,取 limit 1 ,也耗时很长吗?
  3. 另外,这个 select * from logoutrole order by dt desc limit 1; dt 是日期还是时间? 想取当前的最大日期吗? 分区表 PARTITION BY RANGE (UNIX_TIMESTAMP( dt )) ,那么根据你们分区的时间规律,找到当前最大的分区 p20220701 是不是肯定有数据的? 直接 select * from p20220701 order by dt desc limit 1 满足要求吗?

这个表一直有数据在写入,目前有180亿行左右。

  1. 相关列的表结构和索引如下:
    CREATE TABLE logoutrole (
    doc_id varchar(255) NOT NULL DEFAULT ‘’,
    dt timestamp NOT NULL ,
    updatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
    PRIMARY KEY (dt,doc_id) /*T![clustered_index] NONCLUSTERED */,
    KEY updatetime (updatetime)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![placement] PLACEMENT POLICY=storeonssd */
    PARTITION BY RANGE (UNIX_TIMESTAMP(dt))
    (PARTITION p20210601 VALUES LESS THAN (1622563200) /*T![placement] PLACEMENT POLICY=storeonhdd */,
    PARTITION p20210602 VALUES LESS THAN (1622649600) /*T![placement] PLACEMENT POLICY=storeonhdd */,

    PARTITION p20210805 VALUES LESS THAN (1628179200) /*T![placement] PLACEMENT POLICY=storeonhdd */,
    PARTITION p20210806 VALUES LESS THAN (1628265600),

    PARTITION p20220810 VALUES LESS THAN (1660147200),
    PARTITION p20220811 VALUES LESS THAN (1660233600),
    PARTITION p20220812 VALUES LESS THAN (1660320000))
    1 row in set (0.00 sec)

  2. 是的,不desc 执行耗时也是很久,原因还是因为执行计划里有 TableFullScan_12

  3. dt是 date time的含义,是时间。
    我们不能确定当前最新的数据是不是已经在p20220701分区里了,用我们的案例来说,我们一次性建立了20210601至20220801的全部分区(以后有个定期任务每天新增一个分区),然后有个补数据的任务在往表写数据,从20210601开始追直到追到当前时间,此时我们想看它已经追到哪个时间点了,就执行了 order by updatetime或dt desc limit 1 这个语句来查看。

所以,就是在执行这个 order by updatetime或dt limit 1时发现执行很慢的问题

您好,麻烦 在执行这个 sql 后搜一下连接到的 tidb-server 日志中有没有 buildCopTasks takes too much time 的关键字,辛苦。

另外贴一下 slow.log 中该 sql 的慢日志 和 tidb_decode 的执行计划,感谢。

mysql> select *from mars_p1log.logoutrole order by updatetime desc limit 1 \G

ERROR 1105 (HY000): other error: Coprocessor task terminated due to exceeding the deadline
mysql>
执行的时候出现了这个报错,我找找tidb日志