关于tidb select order by的问题

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

【TiDB 版本】v4.0.9

【问题描述】请教个问题:我在一张200w的数据表里面,按索引字段查询,符合条件的记录2000条左右;
下面这条sql:

select * from test where key1 = ‘xxx’ order by col1,col2 limit 0,10;

order by 带上之后就挺慢的;

按照mysql的做法, 如果设置sort_buffer_size较大,基本内存排序,应该很快。tidb有这方面的优化方式吗?
我看这个参数tidb似乎不支持修改;


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

  1. 查看下统计信息是否都正常
    https://docs.pingcap.com/zh/tidb/stable/statistics#统计信息简介

  2. 统计信息都正常的情况下,麻烦先反馈下 explain analyze select * from test where key1 = ‘xxx’ order by col1,col2 limit 0,10;结果

【TiDB 版本】v5.1.0
【问题描述】 id 字段做二次索引后,统计全量结果返回很快(0.64 sec)
order by 返回耗时(1 min 53.99 sec)
下面是统计和order by 的explain 结果

order by 结果的 explain:
MySQL [test]> explain select * from test where id=‘6a1e949f-2936-489f-91e3-73ef3d2e9dfc’ order by date desc limit 10;
±---------------------------------±----------±----------±---------------------------------------------±--------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±---------------------------------±----------±----------±---------------------------------------------±--------------------------------------------------------------------------------------------------------+
| TopN_9 | 10.00 | root | | test.test.date:desc, offset:0, count:10 |
| └─IndexLookUp_22 | 10.00 | root | | |
| ├─IndexRangeScan_19(Build) | 120713.71 | cop[tikv] | table:test, index:index_id(id) | range:[“6a1e949f-2936-489f-91e3-73ef3d2e9dfc”,“6a1e949f-2936-489f-91e3-73ef3d2e9dfc”], keep order:false |
| └─TopN_21(Probe) | 10.00 | cop[tikv] | | test.test.date:desc, offset:0, count:10 |
| └─TableRowIDScan_20 | 120713.71 | cop[tikv] | table:test | keep order:false |
±---------------------------------±----------±----------±---------------------------------------------±--------------------------------------------------------------------------------------------------------+

统计结果的 explain:
MySQL [test]> explain select count(1) num from test where id=‘6a1e949f-2936-489f-91e3-73ef3d2e9dfc’ ; ±----------------------------±----------±----------±---------------------------------------------±--------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±----------------------------±----------±----------±---------------------------------------------±--------------------------------------------------------------------------------------------------------+
| StreamAgg_17 | 1.00 | root | | funcs:count(Column#35)->Column#33 |
| └─IndexReader_18 | 1.00 | root | | index:StreamAgg_9 |
| └─StreamAgg_9 | 1.00 | cop[tikv] | | funcs:count(1)->Column#35 |
| └─IndexRangeScan_16 | 120713.71 | cop[tikv] | table:test, index:index_id(id) | range:[“6a1e949f-2936-489f-91e3-73ef3d2e9dfc”,“6a1e949f-2936-489f-91e3-73ef3d2e9dfc”], keep order:false |
±----------------------------±----------±----------±---------------------------------------------±--------------------------------------------------------------------------------------------------------+