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

[Mingdr (会飞的猫)]

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

• 【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秒多

[GangShen]

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

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

[Mingdr (会飞的猫)]

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

[GangShen]

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

三篇文章了解 TiDB 技术内幕 - 说计算

[Mingdr (会飞的猫)]

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

[QBin (Bin)]

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

[Mingdr (会飞的猫)]

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)
)

[QBin (Bin)]

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

[Mingdr (会飞的猫)]

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

[GangShen]

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

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

[Mingdr (会飞的猫)]

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

但是显示结果的时间为

是navicat的原因还是什么呢

[GangShen]

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

[Mingdr (会飞的猫)]

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

[GangShen]

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

[Mingdr (会飞的猫)]

好的 谢谢

[Mingdr (会飞的猫)]

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

[QBin (Bin)]

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

[Mingdr (会飞的猫)]

ok~

[GangShen]