【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】
我有个执行计划，用到了order by 主键 limit 10000；但是主键因为超过了 bigint类型的大小 使用的 varchar，所以在算子上 keep order:false 。这个应该怎么调合适呢
发个完整的执行计划？
如果您的查询中使用了ORDER BY主键LIMIT 10000 ，但是主键是varchar类型，您可以考虑修改查询策略，比如使用WHERE子句来限制结果集，减少排序的数据量，或者改变排序的字段，以利用现有的索引
这个要看具体的执行计划
原则上，应该limit有一个临界点值，超过每个值之后，执行计划可能会有偏差，比如 limit 100走索引，limit 101走全表扫描啥的，具体需要测试临界值是多少
应该下推将查询计划树中的 TopN 计算尽可能下推到距离数据源最近的地方，以尽早完成数据的过滤，进而显著地减少数据传输或计算的开销，也就是需要SQL优化一下
有个文档可以参考一下逻辑优化 - TopN 和 Limit 下推 - 《TiDB v6.5 中文文档》 - 书栈网 · BookStack
详细执行计划贴上
keep order:false不代表limit没有下推啊，只是你的limit10000会下推到所有tikv，假如你有3个tikv节点，最终会汇总30000条数据到tidb-server，由于你在tidb-server中会从这3万条数据中重新排序过滤出前10000条，所以tikv是没有必要排序的，所以就会出现keep order:false。
查询大概是 select table where order by 主键 limit 10000
+------------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_7 | 10000.00 | 10000 | root | | time:4.91s, loops:11, RU:5422.964476, Concurrency:8 | test_data.testtable.id_, test_data.testtable.ref_id_, test_data.testtable.parent_id_, test_data.testtable.update_version_, test_data.testtable.f_order_key, test_data.testtable.f_source_system, test_data.testtable.f_source_system_name, test_data.testtable.f_business_type, test_data.testtable.f_business_type_name, test_data.testtable.f_order_id, test_data.testtable.f_service_date, test_data.testtable.f_process_flag, test_data.testtable.f_error_message, test_data.testtable.inst_id_, test_data.testtable.inst_status_, test_data.testtable.f_create_by_name, test_data.testtable.f_consume_cnt, test_data.testtable.tenant_id_, test_data.testtable.create_time_, test_data.testtable.update_time_, test_data.testtable.create_by_, test_data.testtable.update_by_, test_data.testtable.create_dep_id_ | 2.77 MB | N/A |
| └─TopN_9 | 10000.00 | 10000 | root | | time:4.91s, loops:11 | test_data.testtable.id_, offset:0, count:10000 | 10.0 MB | N/A |
| └─IndexLookUp_23 | 10000.00 | 62048 | root | | time:4.81s, loops:62, index_task: {total_time: 2.02s, fetch_handle: 2.02s, build: 17.7µs, wait: 52.5µs}, table_task: {total_time: 4.48s, num: 6, concurrency: 8}, next: {wait_index: 704.6ms, wait_table_lookup_build: 26.2ms, wait_table_lookup_resp: 4.04s} | | 16.1 MB | N/A |
| ├─TopN_22(Build) | 10000.00 | 62048 | cop[tikv] | | time:1.97s, loops:46, cop_task: {num: 29, max: 1.06s, min: 5.37ms, avg: 311.1ms, p95: 880.9ms, max_proc_keys: 417104, p95_proc_keys: 137522, tot_proc: 8.78s, tot_wait: 5.88ms, rpc_num: 30, rpc_time: 9.61s, copr_cache_hit_ratio: 0.00, build_task_duration: 100.3µs, max_distsql_concurrency: 15}, ResolveLock:{num_rpc:1, total_time:1.37ms}, tikv_task:{proc max:1.04s, min:1ms, avg: 303.1ms, p80:486ms, p95:877ms, iters:650, tasks:29}, scan_detail: {total_process_keys: 596674, total_process_keys_size: 66230814, total_keys: 11049078, get_snapshot_time: 567.6µs, rocksdb: {delete_skipped_count: 55665, key_skipped_count: 12334000, block: {cache_hit_count: 22999, read_count: 54, read_byte: 854.9 KB, read_time: 677.1µs}}} | test_data.testtable.id_, offset:0, count:10000 | N/A | N/A |
| │ └─Selection_21 | 5959245.93 | 596674 | cop[tikv] | | tikv_task:{proc max:1.04s, min:1ms, avg: 295.8ms, p80:486ms, p95:787ms, iters:650, tasks:29} | gt(test_data.testtable.id_, "1859373009586388993") | N/A | N/A |
| │ └─IndexRangeScan_19 | 6112639.72 | 596674 | cop[tikv] | table:testtable, index:source_date_flag_IDX(F_SOURCE_SYSTEM, F_SERVICE_DATE, F_PROCESS_FLAG) | tikv_task:{proc max:1.04s, min:1ms, avg: 291.2ms, p80:486ms, p95:737ms, iters:650, tasks:29} | range:["Move" 2024-11-20 "N","Move" 2024-11-20 "N"], keep order:false | N/A | N/A |
| └─TableRowIDScan_20(Probe) | 10000.00 | 62048 | cop[tikv] | table:testtable | time:4.44s, loops:69, cop_task: {num: 8, max: 2.75s, min: 53.9ms, avg: 613.5ms, p95: 2.75s, max_proc_keys: 25000, p95_proc_keys: 25000, tot_proc: 3.04s, tot_wait: 2.61ms, rpc_num: 8, rpc_time: 4.91s, copr_cache_hit_ratio: 0.00, build_task_duration: 4.59ms, max_distsql_concurrency: 2}, tikv_task:{proc max:2.62s, min:45ms, avg: 573.9ms, p80:583ms, p95:2.62s, iters:97, tasks:8}, scan_detail: {total_process_keys: 62048, total_process_keys_size: 16653986, total_keys: 132067, get_snapshot_time: 2.03ms, rocksdb: {delete_skipped_count: 7617, key_skipped_count: 79945, block: {cache_hit_count: 744528, read_count: 89, read_byte: 1.37 MB, read_time: 1.75ms}}} | keep order:false | N/A | N/A |
+------------------------------------+------------+---------+-----------+---------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
order by 的字段不在使用的索引里，所以 需要回表再排序 TOPN。所以 indexrangescan 不能利用索引有序性 ，keep order:false。
你这个应该是非聚簇表，这个索引后边要➕上主键列
F_SOURCE_SYSTEM, F_SERVICE_DATE, F_PROCESS_FLAG, 主键 上建立复合索引
索引加强
你这是非聚簇表吧，如果是聚簇表，应该直接走tablerangescan了，不需要走索引再回表了
CLUSTERED 是聚簇表， 不过加上复合索引确实好了
主键是varchar类型 条件里也有主键 > ***的条件
order by 主键 然后选择的是另一个联合索引。
/*T![clustered_index] CLUSTERED */
聚簇表的 不过索引没有选择主键索引
提问题建议把该贴的贴上，缺的东西太多就得猜，你可以把关键字段脱敏 
哦，哪一样的意思，如果你是聚簇表，并且key用的是主键的聚簇索引，那不需要回表，你这种得回表的，如果优化就像上面龙虾说的那样，直接把order by的字段都放到索引里，直接根据索引排序就可以了。
添加索引
试试覆盖索引。
此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。
