关于使用limit在加条件的情况下比较慢

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:2.1.19,3.0.4,3.0.8我都测试过了,都是一样的。
  • 【问题描述】: 我在查询交易表(每天150w,日期有索引)一天某个商户或者满足某些条件的订单时,使用limit查询,但是无论是否有数据,查询都比较慢。同样的查询在mysql上面,如果满足条件的数据比较多,查询limit 100是很快的,但是在tidb里面,无论有没有数据,都比较慢,如果索引同为b+tree或者类似结构,不应该查询到前100条就结束扫描而不是扫描所有的数据吗

可以看到就算只取前10条都是很慢的,满足该条件的数据是很多的。 而且后台监控有一台kv的coprocesser CPU突然很高。

这是查询全部数据 一共1.5w 不分页 ,也才4秒多

看你查询中有 order by 操作,需要进行排序,对于 MySQL 而言底层是 B+ 树结构是有序的,所以扫描值需要按照顺序扫描就行

tidb 中数据存储是无序的,进行 order by 操作的话需要将数据从 tikv 中加载到 tidb 层进行排序

但是时间列是有索引的,按理说应该是有序的吧,按照时间列取前十,应该不用扫描所有的行数吧

索引在 tikv 中也是可以 key-value 方式存储的,并不是完全有序的,具体可以参考一下这篇文章

这种场景还是很多的,有没有什么解决方案呢

麻烦上传一下完整的 explain analyze 信息(截图里面有些字段信息不全),以及 提供一下相关表的表结构 ( show create table)。

CREATE TABLE ord_trans ( TRANS_ID bigint(18) unsigned NOT NULL AUTO_INCREMENT, CHAN_CODE char(1) COLLATE utf8mb4_general_ci NOT NULL COMMENT ‘支付渠道或者其它支付通道’, SUB_CHAN_CODE char(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘支付渠道(其它支付通道下细分的渠道)’, MERCH_ID int(11) NOT NULL COMMENT ‘赢商通商户ID’, SUB_MERCH_ID int(11) DEFAULT NULL COMMENT ‘赢商通子商户ID’, STORE_ID int(11) NOT NULL COMMENT ‘交易的门店ID’, DEVICE_ID int(11) NOT NULL COMMENT ‘交易的设备、工号ID’, MCH_CHAN_ID int(11) NOT NULL COMMENT ‘发起交易请求的商户支付渠道参数配置ID’, TRANS_TIME datetime DEFAULT NULL COMMENT ‘交易时间,赢商通生成交易单的时间,TRANS_TYPE=P时为支付时间,TRANS_TYPE=R时为退款时间’, TRADE_TIME datetime DEFAULT NULL COMMENT ‘支付渠道完成交易的时间,TRANS_TYPE=P时为支付时间,TRANS_TYPE=R时为退款时间’, TRANS_STATUS char(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘订单状态,S:支付成功,F:交易失败,W:等待支付’, REFUND_STATUS char(1) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘N’ COMMENT ‘退款状态,A:全额退款,P:部分退款,N:未退款’, CHAN_STATUS varchar(15) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘渠道支付状态,状态含义见KEY_TRANS_CHAN_STATUS’, CODE char(6) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘赢商通定义的消息编码’, PRIMARY KEY (TRANS_ID), KEY INDEX_TRANS_TIME (TRANS_TIME), KEY INDEX_TRADE_TIME (TRADE_TIME), KEY INDEX_SUB_OPEN_ID (SUB_OPEN_ID), KEY INDEX_CHAN_TRANS_ID (CHAN_TRANS_ID), KEY INDEX_MERCH_TRADE_TIME (MERCH_ID,TRADE_TIME) )

  1. operator info 的麻烦也提供一下。最好能以文本的形式提供,方便分析。
  2. 请问截图的 explain analyze 是 3.0.8 版本的截图吗?

2 版本是3.0.8

1 explain analyze

TopN_10 0.56 root ystpay.t.trans_time:desc, offset:0, count:10 time:174.84971ms, loops:2, rows:10 110.40625 KB
└─IndexLookUp_36 0.56 root time:172.939113ms, loops:5, rows:2783 1.6243743896484375 MB
├─IndexScan_33 21924.72 cop table:T, index:MERCH_ID, TRADE_TIME, range:[1477,1477], keep order:false time:27ms, loops:64, rows:60816 N/A
└─Selection_35 0.56 cop eq(ystpay.t.chan_code, “A”), eq(ystpay.t.sub_merch_id, 3999), ge(ystpay.t.trans_time, 2020-01-03 00:00:00.000000), le(ystpay.t.trans_time, 2020-01-03 23:59:59.000000) proc max:83ms, min:0s, p80:53ms, p95:68ms, rows:2783, iters:245, tasks:54 N/A
└─TableScan_34 21924.72 cop table:ord_trans, keep order:false proc max:83ms, min:0s, p80:52ms, p95:66ms, rows:60816, iters:245, tasks:54 N/A

这边看起来是走的索引有点问题,导致执行效率比较低

可以尝试 analyze table 重新收集一下统计信息,然后再测试看下

加了组合索引快了些了,但是对于分页查询有些时候还是会比较慢,另外有个问题,在本地执行SQL,显示执行为 image

但是显示结果的时间为

image

是navicat的原因还是什么呢

可以在命令行中执行 sql 看下时间,是在0.1秒左右还是1秒左右,如果是 0.1 秒左右,可能是 navicat 的问题

试了下 ,还真是,谢谢~。 最后有个问题,我们现在读写比例在6:4的样子,怎么设置region的大小合适呢,日增800w左右的数据。

region 大小建议按照默认值即可,如果有遇到性能问题,可以在 TUG 上提帖我们帮助看下

好的 谢谢

请问 raftstore.hibernate-regions ( 实验特性 ) 这个参数在哪儿配置呢,tikv.yml 里面并没有这个参数,版本3.0.8

由于是实验特性的关系,hibernate-regions 这个参数默认是没有暴露出来的。所以需要自己在 raftstore 下面添加

ok~

:+1::+1::+1: