v7.5.7版本tidb_opt_ordering_index_selectivity_threshold导致同一SQL性能差异较大

[Hacker_Yv76YjBL]

【TiDB 使用环境】生产环境
【TiDB 版本】v7.5.7
【遇到的问题：问题现象及影响】
集群默认将tidb_opt_ordering_index_selectivity_threshold调整为1，导致这个SQL执行耗时较长。暂时无法将该参数调整为0，因为会导致更多的SQL变慢。
如果将 AND sub_order_status > 0去掉，也可以走正确的执行计划。
是否有其他方式让该SQL的执行计划走limit的方式，而不是topN。
SQL语句

SELECT
  id,
  order_no,
  sub_order_no,
  biz_type,
  biz_channel,
  biz_code,
  biz_id,
  abs(sub_order_status) AS sub_order_status,
  buyer_id,
  buyer_name,
  seller_id,
  seller_name,
  pay_amount,
  discount_amount,
  inventory_id,
  spu_id,
  sku_id,
  sku_title,
  sku_price,
  sku_count,
  buyer_note,
  deposit_amount,
  poundage_amount,
  poundage_info,
  item_info,
  close_type,
  close_time,
  feature,
  is_del,
  create_time,
  modify_time,
  product_amount,
  freight_amount,
  sku_logo,
  seller_note,
  seller_address_info,
  seller_address_back,
  merchant_info,
  repository_address,
  delivery_deadline,
  tab_tag,
  flag,
  relation_info,
  create_time_us,
  seller_feature,
  seller_type,
  delivery_start_time,
  delivery_no,
  warehouse_code,
  warehouse_address_id,
  down_payment_time,
  balance_payment_time,
  merchant_accept,
  delivery_mode,
  sub_is_del,
  order_status,
  order_deliver_status,
  order_product_amount,
  order_freight_amount,
  order_pay_amount,
  order_discount_amount,
  buyer_address_info,
  order_platform,
  order_source_name,
  order_feature,
  order_flag,
  order_relation_info,
  order_is_del,
  oversea_tax,
  platform_received_time,
  trade_close_reason_id,
  seller_bidding_no,
  sub_order_modify_time,
  main_order_modify_time,
  logistics_delivery_deadline,
  virtual_delivery_type,
  sale_type,
  seller_earliest_logistic_delivery_time,
  seller_earliest_logistic_collect_time,
  seller_earliest_logistic_no,
  seller_earliest_logistic_modify_time,
  seller_earliest_logistic_express_no,
  seller_earliest_logistic_express_status,
  receive_address_confirmed
FROM
  trade_order_seller
WHERE
  seller_id = 1672640944
  AND sub_order_status > 0
ORDER BY
  create_time_us DESC
LIMIT
  0, 4;

set session tidb_opt_ordering_index_selectivity_threshold=0;
SQL执行耗时：0.33秒
执行计划：

+------------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                 | estRows | actRows | task      | access object                                                                           | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | memory  | disk |
+------------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_7                       | 4.00    | 4       | root      |                                                                                         | time:18.5ms, loops:2, RU:128.712656, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | dw_trade_order_seller_db.trade_order_seller.id, dw_trade_order_seller_db.trade_order_seller.order_no, dw_trade_order_seller_db.trade_order_seller.sub_order_no, dw_trade_order_seller_db.trade_order_seller.biz_type, dw_trade_order_seller_db.trade_order_seller.biz_channel, dw_trade_order_seller_db.trade_order_seller.biz_code, dw_trade_order_seller_db.trade_order_seller.biz_id, abs(dw_trade_order_seller_db.trade_order_seller.sub_order_status)->Column#85, dw_trade_order_seller_db.trade_order_seller.buyer_id, dw_trade_order_seller_db.trade_order_seller.buyer_name, dw_trade_order_seller_db.trade_order_seller.seller_id, dw_trade_order_seller_db.trade_order_seller.seller_name, dw_trade_order_seller_db.trade_order_seller.pay_amount, dw_trade_order_seller_db.trade_order_seller.discount_amount, dw_trade_order_seller_db.trade_order_seller.inventory_id, dw_trade_order_seller_db.trade_order_seller.spu_id, dw_trade_order_seller_db.trade_order_seller.sku_id, dw_trade_order_seller_db.trade_order_seller.sku_title, dw_trade_order_seller_db.trade_order_seller.sku_price, dw_trade_order_seller_db.trade_order_seller.sku_count, dw_trade_order_seller_db.trade_order_seller.buyer_note, dw_trade_order_seller_db.trade_order_seller.deposit_amount, dw_trade_order_seller_db.trade_order_seller.poundage_amount, dw_trade_order_seller_db.trade_order_seller.poundage_info, dw_trade_order_seller_db.trade_order_seller.item_info, dw_trade_order_seller_db.trade_order_seller.close_type, dw_trade_order_seller_db.trade_order_seller.close_time, dw_trade_order_seller_db.trade_order_seller.feature, dw_trade_order_seller_db.trade_order_seller.is_del, dw_trade_order_seller_db.trade_order_seller.create_time, dw_trade_order_seller_db.trade_order_seller.modify_time, dw_trade_order_seller_db.trade_order_seller.product_amount, dw_trade_order_seller_db.trade_order_seller.freight_amount, dw_trade_order_seller_db.trade_order_seller.sku_logo, dw_trade_order_seller_db.trade_order_seller.seller_note, dw_trade_order_seller_db.trade_order_seller.seller_address_info, dw_trade_order_seller_db.trade_order_seller.seller_address_back, dw_trade_order_seller_db.trade_order_seller.merchant_info, dw_trade_order_seller_db.trade_order_seller.repository_address, dw_trade_order_seller_db.trade_order_seller.delivery_deadline, dw_trade_order_seller_db.trade_order_seller.tab_tag, dw_trade_order_seller_db.trade_order_seller.flag, dw_trade_order_seller_db.trade_order_seller.relation_info, dw_trade_order_seller_db.trade_order_seller.create_time_us, dw_trade_order_seller_db.trade_order_seller.seller_feature, dw_trade_order_seller_db.trade_order_seller.seller_type, dw_trade_order_seller_db.trade_order_seller.delivery_start_time, dw_trade_order_seller_db.trade_order_seller.delivery_no, dw_trade_order_seller_db.trade_order_seller.warehouse_code, dw_trade_order_seller_db.trade_order_seller.warehouse_address_id, dw_trade_order_seller_db.trade_order_seller.down_payment_time, dw_trade_order_seller_db.trade_order_seller.balance_payment_time, dw_trade_order_seller_db.trade_order_seller.merchant_accept, dw_trade_order_seller_db.trade_order_seller.delivery_mode, dw_trade_order_seller_db.trade_order_seller.sub_is_del, dw_trade_order_seller_db.trade_order_seller.order_status, dw_trade_order_seller_db.trade_order_seller.order_deliver_status, dw_trade_order_seller_db.trade_order_seller.order_product_amount, dw_trade_order_seller_db.trade_order_seller.order_freight_amount, dw_trade_order_seller_db.trade_order_seller.order_pay_amount, dw_trade_order_seller_db.trade_order_seller.order_discount_amount, dw_trade_order_seller_db.trade_order_seller.buyer_address_info, dw_trade_order_seller_db.trade_order_seller.order_platform, dw_trade_order_seller_db.trade_order_seller.order_source_name, dw_trade_order_seller_db.trade_order_seller.order_feature, dw_trade_order_seller_db.trade_order_seller.order_flag, dw_trade_order_seller_db.trade_order_seller.order_relation_info, dw_trade_order_seller_db.trade_order_seller.order_is_del, dw_trade_order_seller_db.trade_order_seller.oversea_tax, dw_trade_order_seller_db.trade_order_seller.platform_received_time, dw_trade_order_seller_db.trade_order_seller.trade_close_reason_id, dw_trade_order_seller_db.trade_order_seller.seller_bidding_no, dw_trade_order_seller_db.trade_order_seller.sub_order_modify_time, dw_trade_order_seller_db.trade_order_seller.main_order_modify_time, dw_trade_order_seller_db.trade_order_seller.logistics_delivery_deadline, dw_trade_order_seller_db.trade_order_seller.virtual_delivery_type, dw_trade_order_seller_db.trade_order_seller.sale_type, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_delivery_time, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_collect_time, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_no, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_modify_time, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_express_no, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_express_status, dw_trade_order_seller_db.trade_order_seller.receive_address_confirmed | 65.7 KB | N/A  |
| └─Limit_12                         | 4.00    | 4       | root      |                                                                                         | time:18.4ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | offset:0, count:4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | N/A     | N/A  |
|   └─IndexLookUp_26                 | 4.00    | 4       | root      |                                                                                         | time:18.4ms, loops:1, index_task: {total_time: 18.3ms, fetch_handle: 1.56ms, build: 471.2µs, wait: 16.3ms}, table_task: {total_time: 86.6ms, num: 8, concurrency: 5}, next: {wait_index: 1.48ms, wait_table_lookup_build: 119.7µs, wait_table_lookup_resp: 16.7ms}                                                                                                                                                                                                                                                                                                                                                                                                       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 4.40 MB | N/A  |
|     ├─IndexRangeScan_23(Build)     | 4.01    | 290019  | cop[tikv] | table:trade_order_seller, index:idx_seller_id_create_time_us(seller_id, create_time_us) | time:1.34ms, loops:9, cop_task: {num: 1, max: 599.8µs, proc_keys: 0, tot_proc: 2.23µs, tot_wait: 73.4µs, copr_cache_hit_ratio: 1.00, build_task_duration: 20.3µs, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:1, total_time:580.1µs}}, tikv_task:{time:181ms, loops:288}, scan_detail: {get_snapshot_time: 34.2µs, rocksdb: {block: {}}}                                                                                                                                                                                                                                                                                                                         | range:[1672640944,1672640944], keep order:true, desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | N/A     | N/A  |
|     └─Selection_25(Probe)          | 4.00    | 915     | cop[tikv] |                                                                                         | time:82.8ms, loops:10, cop_task: {num: 173, max: 16.6ms, min: 0s, avg: 1.6ms, p95: 8.99ms, max_proc_keys: 23, p95_proc_keys: 18, tot_proc: 266.7ms, tot_wait: 11.7ms, copr_cache_hit_ratio: 0.05, build_task_duration: 861.8µs, max_distsql_concurrency: 1, max_extra_concurrency: 8, store_batch_num: 91}, rpc_info:{Cop:{num_rpc:82, total_time:275.8ms}}, tikv_task:{proc max:16ms, min:0s, avg: 1.73ms, p80:2ms, p95:11ms, iters:173, tasks:173}, scan_detail: {total_process_keys: 819, total_process_keys_size: 5207009, total_keys: 863, get_snapshot_time: 5.17ms, rocksdb: {delete_skipped_count: 168, key_skipped_count: 50, block: {cache_hit_count: 8132}}}  | gt(dw_trade_order_seller_db.trade_order_seller.sub_order_status, 0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | N/A     | N/A  |
|       └─TableRowIDScan_24          | 4.01    | 915     | cop[tikv] | table:trade_order_seller                                                                | tikv_task:{proc max:16ms, min:0s, avg: 1.72ms, p80:2ms, p95:11ms, iters:173, tasks:173}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A     | N/A  |
+------------------------------------+---------+---------+-----------+-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (0.02 sec)

set session tidb_opt_ordering_index_selectivity_threshold=1;
SQL执行耗时：8.13秒
执行计划：

+------------------------------------+------------+---------+-----------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                                 | estRows    | actRows | task      | access object                                                                             | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | memory   | disk |
+------------------------------------+------------+---------+-----------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_7                       | 4.00       | 4       | root      |                                                                                           | time:3.06s, loops:2, RU:50181.284098, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | dw_trade_order_seller_db.trade_order_seller.id, dw_trade_order_seller_db.trade_order_seller.order_no, dw_trade_order_seller_db.trade_order_seller.sub_order_no, dw_trade_order_seller_db.trade_order_seller.biz_type, dw_trade_order_seller_db.trade_order_seller.biz_channel, dw_trade_order_seller_db.trade_order_seller.biz_code, dw_trade_order_seller_db.trade_order_seller.biz_id, abs(dw_trade_order_seller_db.trade_order_seller.sub_order_status)->Column#85, dw_trade_order_seller_db.trade_order_seller.buyer_id, dw_trade_order_seller_db.trade_order_seller.buyer_name, dw_trade_order_seller_db.trade_order_seller.seller_id, dw_trade_order_seller_db.trade_order_seller.seller_name, dw_trade_order_seller_db.trade_order_seller.pay_amount, dw_trade_order_seller_db.trade_order_seller.discount_amount, dw_trade_order_seller_db.trade_order_seller.inventory_id, dw_trade_order_seller_db.trade_order_seller.spu_id, dw_trade_order_seller_db.trade_order_seller.sku_id, dw_trade_order_seller_db.trade_order_seller.sku_title, dw_trade_order_seller_db.trade_order_seller.sku_price, dw_trade_order_seller_db.trade_order_seller.sku_count, dw_trade_order_seller_db.trade_order_seller.buyer_note, dw_trade_order_seller_db.trade_order_seller.deposit_amount, dw_trade_order_seller_db.trade_order_seller.poundage_amount, dw_trade_order_seller_db.trade_order_seller.poundage_info, dw_trade_order_seller_db.trade_order_seller.item_info, dw_trade_order_seller_db.trade_order_seller.close_type, dw_trade_order_seller_db.trade_order_seller.close_time, dw_trade_order_seller_db.trade_order_seller.feature, dw_trade_order_seller_db.trade_order_seller.is_del, dw_trade_order_seller_db.trade_order_seller.create_time, dw_trade_order_seller_db.trade_order_seller.modify_time, dw_trade_order_seller_db.trade_order_seller.product_amount, dw_trade_order_seller_db.trade_order_seller.freight_amount, dw_trade_order_seller_db.trade_order_seller.sku_logo, dw_trade_order_seller_db.trade_order_seller.seller_note, dw_trade_order_seller_db.trade_order_seller.seller_address_info, dw_trade_order_seller_db.trade_order_seller.seller_address_back, dw_trade_order_seller_db.trade_order_seller.merchant_info, dw_trade_order_seller_db.trade_order_seller.repository_address, dw_trade_order_seller_db.trade_order_seller.delivery_deadline, dw_trade_order_seller_db.trade_order_seller.tab_tag, dw_trade_order_seller_db.trade_order_seller.flag, dw_trade_order_seller_db.trade_order_seller.relation_info, dw_trade_order_seller_db.trade_order_seller.create_time_us, dw_trade_order_seller_db.trade_order_seller.seller_feature, dw_trade_order_seller_db.trade_order_seller.seller_type, dw_trade_order_seller_db.trade_order_seller.delivery_start_time, dw_trade_order_seller_db.trade_order_seller.delivery_no, dw_trade_order_seller_db.trade_order_seller.warehouse_code, dw_trade_order_seller_db.trade_order_seller.warehouse_address_id, dw_trade_order_seller_db.trade_order_seller.down_payment_time, dw_trade_order_seller_db.trade_order_seller.balance_payment_time, dw_trade_order_seller_db.trade_order_seller.merchant_accept, dw_trade_order_seller_db.trade_order_seller.delivery_mode, dw_trade_order_seller_db.trade_order_seller.sub_is_del, dw_trade_order_seller_db.trade_order_seller.order_status, dw_trade_order_seller_db.trade_order_seller.order_deliver_status, dw_trade_order_seller_db.trade_order_seller.order_product_amount, dw_trade_order_seller_db.trade_order_seller.order_freight_amount, dw_trade_order_seller_db.trade_order_seller.order_pay_amount, dw_trade_order_seller_db.trade_order_seller.order_discount_amount, dw_trade_order_seller_db.trade_order_seller.buyer_address_info, dw_trade_order_seller_db.trade_order_seller.order_platform, dw_trade_order_seller_db.trade_order_seller.order_source_name, dw_trade_order_seller_db.trade_order_seller.order_feature, dw_trade_order_seller_db.trade_order_seller.order_flag, dw_trade_order_seller_db.trade_order_seller.order_relation_info, dw_trade_order_seller_db.trade_order_seller.order_is_del, dw_trade_order_seller_db.trade_order_seller.oversea_tax, dw_trade_order_seller_db.trade_order_seller.platform_received_time, dw_trade_order_seller_db.trade_order_seller.trade_close_reason_id, dw_trade_order_seller_db.trade_order_seller.seller_bidding_no, dw_trade_order_seller_db.trade_order_seller.sub_order_modify_time, dw_trade_order_seller_db.trade_order_seller.main_order_modify_time, dw_trade_order_seller_db.trade_order_seller.logistics_delivery_deadline, dw_trade_order_seller_db.trade_order_seller.virtual_delivery_type, dw_trade_order_seller_db.trade_order_seller.sale_type, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_delivery_time, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_collect_time, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_no, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_modify_time, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_express_no, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_express_status, dw_trade_order_seller_db.trade_order_seller.receive_address_confirmed | 65.7 KB  | N/A  |
| └─TopN_9                           | 4.00       | 4       | root      |                                                                                           | time:3.06s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | 1.70 MB  | N/A  |
|   └─IndexLookUp_22                 | 4.00       | 540812  | root      |                                                                                           | time:2.93s, loops:135, index_task: {total_time: 2.82s, fetch_handle: 550.3ms, build: 133.9µs, wait: 2.27s}, table_task: {total_time: 12.6s, num: 91, concurrency: 5}, next: {wait_index: 380.7ms, wait_table_lookup_build: 8.75ms, wait_table_lookup_resp: 1.23s}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 525.3 MB | N/A  |
|     ├─IndexRangeScan_13(Build)     | 1572408.37 | 1590062 | cop[tikv] | table:trade_order_seller, index:seller_id(seller_id, sub_order_status, delivery_deadline) | time:470.2ms, loops:408, cop_task: {num: 3, max: 804.4ms, min: 1.37ms, avg: 318.2ms, p95: 804.4ms, max_proc_keys: 1294047, p95_proc_keys: 1294047, tot_proc: 905.6ms, tot_wait: 1.71ms, copr_cache_hit_ratio: 0.00, build_task_duration: 43µs, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:3, total_time:954.6ms}}, tikv_task:{proc max:744ms, min:1ms, avg: 294.3ms, p80:744ms, p95:744ms, iters:1563, tasks:3}, scan_detail: {total_process_keys: 1590062, total_process_keys_size: 101763968, total_keys: 1590065, get_snapshot_time: 1.58ms, rocksdb: {key_skipped_count: 1590062, block: {cache_hit_count: 2513, read_count: 1, read_byte: 15.6 KB, read_time: 14.2µs}}}                                                                                 | range:(1672640944 0,1672640944 +inf], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | N/A      | N/A  |
|     └─TopN_21(Probe)               | 4.00       | 540812  | cop[tikv] |                                                                                           | time:11s, loops:269, cop_task: {num: 166574, max: 38ms, min: 0s, avg: 919.3µs, p95: 4.45ms, max_proc_keys: 79, p95_proc_keys: 31, tot_proc: 2m33.1s, tot_wait: 3m5.5s, copr_cache_hit_ratio: 0.04, build_task_duration: 830.1ms, max_distsql_concurrency: 15, max_extra_concurrency: 320, store_batch_num: 126820}, rpc_info:{Cop:{num_rpc:39754, total_time:2m32.5s}}, tikv_task:{proc max:0s, min:0s, avg: 2.5ms, p80:1.1ms, p95:6ms, iters:166574, tasks:166574}, scan_detail: {total_process_keys: 1271450, total_process_keys_size: 7497839220, total_keys: 1407877, get_snapshot_time: 47.9s, rocksdb: {delete_skipped_count: 568327, key_skipped_count: 153103, block: {cache_hit_count: 12282230, read_count: 1233, read_byte: 12.3 MB, read_time: 11.1ms}}}  | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | N/A      | N/A  |
|       └─TableRowIDScan_14          | 1572408.37 | 1590062 | cop[tikv] | table:trade_order_seller                                                                  | tikv_task:{proc max:0s, min:0s, avg: 2.37ms, p80:1ms, p95:6ms, iters:166574, tasks:166574}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A      | N/A  |
+------------------------------------+------------+---------+-----------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
6 rows in set (3.07 sec)

[tidb菜鸟一只 (小菜一颗)]

@Hacker_Yv76YjBL

tidb_opt_ordering_index_selectivity_threshold

这个值不是非得设置为0或者1，可以设置成0.01或者0.99这种， 该变量表示一个阈值。当存在索引能满足过滤条件，且其选择率估算值低于该阈值时，优化器会避免选择用于满足 ORDER BY 和 LIMIT 的索引，而优先选择用于满足过滤条件的索引。 当设为 1 时，优化器总是优先选择满足过滤条件的索引，避免选择满足 ORDER BY 和 LIMIT 的索引。也就是说越大越偏向选择满足过滤条件的索引。
不过你的执行计划看着统计信息都不准啊，estRows和actRows的偏差为什么都这么大。。。。

[WalterWj (王军 - PingCAP)]

@Hacker_Yv76YjBL

LIMIT
  0, 4;

预估 4 应该是 limit 4 导致的。应该和统计信息无关。

实际扫那么多数据，应该是索引过滤出来的数据，还有其他非索引字段过滤条件，很多不符合，因此在不断地排序往下找。所以就慢了。

如果很快能找到，实际上第二个执行计划是快的，麻烦的是他往下找的深度太深了就会很慢了。

[Hacker_Yv76YjBL]

16:58:07 [10.221.202.67] {root} (dw_trade_order_seller_db) > explain analyze SELECT /*+ USE_INDEX(trade_order_seller idx_seller_id_create_time_us)*/
    ->   id,
    ->   order_no,
    ->   sub_order_no,
    ->   biz_type,
    ->   biz_channel,
    ->   biz_code,
    ->   biz_id,
    ->   abs(sub_order_status) AS sub_order_status,
    ->   buyer_id,
    ->   buyer_name,
    ->   seller_id,
    ->   seller_name,
    ->   pay_amount,
    ->   discount_amount,
    ->   inventory_id,
    ->   spu_id,
    ->   sku_id,
    ->   sku_title,
    ->   sku_price,
    ->   sku_count,
    ->   buyer_note,
    ->   deposit_amount,
    ->   poundage_amount,
    ->   poundage_info,
    ->   item_info,
    ->   close_type,
    ->   close_time,
    ->   feature,
    ->   is_del,
    ->   create_time,
    ->   modify_time,
    ->   product_amount,
    ->   freight_amount,
    ->   sku_logo,
    ->   seller_note,
    ->   seller_address_info,
    ->   seller_address_back,
    ->   merchant_info,
    ->   repository_address,
    ->   delivery_deadline,
    ->   tab_tag,
    ->   flag,
    ->   relation_info,
    ->   create_time_us,
    ->   seller_feature,
    ->   seller_type,
    ->   delivery_start_time,
    ->   delivery_no,
    ->   warehouse_code,
    ->   warehouse_address_id,
    ->   down_payment_time,
    ->   balance_payment_time,
    ->   merchant_accept,
    ->   delivery_mode,
    ->   sub_is_del,
    ->   order_status,
    ->   order_deliver_status,
    ->   order_product_amount,
    ->   order_freight_amount,
    ->   order_pay_amount,
    ->   order_discount_amount,
    ->   buyer_address_info,
    ->   order_platform,
    ->   order_source_name,
    ->   order_feature,
    ->   order_flag,
    ->   order_relation_info,
    ->   order_is_del,
    ->   oversea_tax,
    ->   platform_received_time,
    ->   trade_close_reason_id,
    ->   seller_bidding_no,
    ->   sub_order_modify_time,
    ->   main_order_modify_time,
    ->   logistics_delivery_deadline,
    ->   virtual_delivery_type,
    ->   sale_type,
    ->   seller_earliest_logistic_delivery_time,
    ->   seller_earliest_logistic_collect_time,
    ->   seller_earliest_logistic_no,
    ->   seller_earliest_logistic_modify_time,
    ->   seller_earliest_logistic_express_no,
    ->   seller_earliest_logistic_express_status,
    ->   receive_address_confirmed
    -> FROM
    ->   trade_order_seller
    -> WHERE
    ->   seller_id = 1759982590
    ->   AND sub_order_status > 0
    -> ORDER BY
    ->   create_time_us DESC
    -> LIMIT
    ->   0, 4;
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                                 | estRows    | actRows | task      | access object                                                                           | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | memory   | disk |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_7                       | 4.00       | 4       | root      |                                                                                         | time:2.82s, loops:2, RU:49978.065809, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | dw_trade_order_seller_db.trade_order_seller.id, dw_trade_order_seller_db.trade_order_seller.order_no, dw_trade_order_seller_db.trade_order_seller.sub_order_no, dw_trade_order_seller_db.trade_order_seller.biz_type, dw_trade_order_seller_db.trade_order_seller.biz_channel, dw_trade_order_seller_db.trade_order_seller.biz_code, dw_trade_order_seller_db.trade_order_seller.biz_id, abs(dw_trade_order_seller_db.trade_order_seller.sub_order_status)->Column#85, dw_trade_order_seller_db.trade_order_seller.buyer_id, dw_trade_order_seller_db.trade_order_seller.buyer_name, dw_trade_order_seller_db.trade_order_seller.seller_id, dw_trade_order_seller_db.trade_order_seller.seller_name, dw_trade_order_seller_db.trade_order_seller.pay_amount, dw_trade_order_seller_db.trade_order_seller.discount_amount, dw_trade_order_seller_db.trade_order_seller.inventory_id, dw_trade_order_seller_db.trade_order_seller.spu_id, dw_trade_order_seller_db.trade_order_seller.sku_id, dw_trade_order_seller_db.trade_order_seller.sku_title, dw_trade_order_seller_db.trade_order_seller.sku_price, dw_trade_order_seller_db.trade_order_seller.sku_count, dw_trade_order_seller_db.trade_order_seller.buyer_note, dw_trade_order_seller_db.trade_order_seller.deposit_amount, dw_trade_order_seller_db.trade_order_seller.poundage_amount, dw_trade_order_seller_db.trade_order_seller.poundage_info, dw_trade_order_seller_db.trade_order_seller.item_info, dw_trade_order_seller_db.trade_order_seller.close_type, dw_trade_order_seller_db.trade_order_seller.close_time, dw_trade_order_seller_db.trade_order_seller.feature, dw_trade_order_seller_db.trade_order_seller.is_del, dw_trade_order_seller_db.trade_order_seller.create_time, dw_trade_order_seller_db.trade_order_seller.modify_time, dw_trade_order_seller_db.trade_order_seller.product_amount, dw_trade_order_seller_db.trade_order_seller.freight_amount, dw_trade_order_seller_db.trade_order_seller.sku_logo, dw_trade_order_seller_db.trade_order_seller.seller_note, dw_trade_order_seller_db.trade_order_seller.seller_address_info, dw_trade_order_seller_db.trade_order_seller.seller_address_back, dw_trade_order_seller_db.trade_order_seller.merchant_info, dw_trade_order_seller_db.trade_order_seller.repository_address, dw_trade_order_seller_db.trade_order_seller.delivery_deadline, dw_trade_order_seller_db.trade_order_seller.tab_tag, dw_trade_order_seller_db.trade_order_seller.flag, dw_trade_order_seller_db.trade_order_seller.relation_info, dw_trade_order_seller_db.trade_order_seller.create_time_us, dw_trade_order_seller_db.trade_order_seller.seller_feature, dw_trade_order_seller_db.trade_order_seller.seller_type, dw_trade_order_seller_db.trade_order_seller.delivery_start_time, dw_trade_order_seller_db.trade_order_seller.delivery_no, dw_trade_order_seller_db.trade_order_seller.warehouse_code, dw_trade_order_seller_db.trade_order_seller.warehouse_address_id, dw_trade_order_seller_db.trade_order_seller.down_payment_time, dw_trade_order_seller_db.trade_order_seller.balance_payment_time, dw_trade_order_seller_db.trade_order_seller.merchant_accept, dw_trade_order_seller_db.trade_order_seller.delivery_mode, dw_trade_order_seller_db.trade_order_seller.sub_is_del, dw_trade_order_seller_db.trade_order_seller.order_status, dw_trade_order_seller_db.trade_order_seller.order_deliver_status, dw_trade_order_seller_db.trade_order_seller.order_product_amount, dw_trade_order_seller_db.trade_order_seller.order_freight_amount, dw_trade_order_seller_db.trade_order_seller.order_pay_amount, dw_trade_order_seller_db.trade_order_seller.order_discount_amount, dw_trade_order_seller_db.trade_order_seller.buyer_address_info, dw_trade_order_seller_db.trade_order_seller.order_platform, dw_trade_order_seller_db.trade_order_seller.order_source_name, dw_trade_order_seller_db.trade_order_seller.order_feature, dw_trade_order_seller_db.trade_order_seller.order_flag, dw_trade_order_seller_db.trade_order_seller.order_relation_info, dw_trade_order_seller_db.trade_order_seller.order_is_del, dw_trade_order_seller_db.trade_order_seller.oversea_tax, dw_trade_order_seller_db.trade_order_seller.platform_received_time, dw_trade_order_seller_db.trade_order_seller.trade_close_reason_id, dw_trade_order_seller_db.trade_order_seller.seller_bidding_no, dw_trade_order_seller_db.trade_order_seller.sub_order_modify_time, dw_trade_order_seller_db.trade_order_seller.main_order_modify_time, dw_trade_order_seller_db.trade_order_seller.logistics_delivery_deadline, dw_trade_order_seller_db.trade_order_seller.virtual_delivery_type, dw_trade_order_seller_db.trade_order_seller.sale_type, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_delivery_time, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_collect_time, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_no, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_modify_time, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_express_no, dw_trade_order_seller_db.trade_order_seller.seller_earliest_logistic_express_status, dw_trade_order_seller_db.trade_order_seller.receive_address_confirmed | 64.0 KB  | N/A  |
| └─TopN_9                           | 4.00       | 4       | root      |                                                                                         | time:2.82s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | 1.65 MB  | N/A  |
|   └─IndexLookUp_17                 | 4.00       | 459669  | root      |                                                                                         | time:2.7s, loops:115, index_task: {total_time: 2.69s, fetch_handle: 419.2ms, build: 121.5µs, wait: 2.27s}, table_task: {total_time: 12.7s, num: 89, concurrency: 5}, next: {wait_index: 149.4ms, wait_table_lookup_build: 9.33ms, wait_table_lookup_resp: 1.57s}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 546.6 MB | N/A  |
|     ├─IndexRangeScan_13(Build)     | 1599407.00 | 1552483 | cop[tikv] | table:trade_order_seller, index:idx_seller_id_create_time_us(seller_id, create_time_us) | time:347.2ms, loops:399, cop_task: {num: 3, max: 1.29s, min: 45.9ms, avg: 576.5ms, p95: 1.29s, max_proc_keys: 960000, p95_proc_keys: 960000, tot_proc: 1.16s, tot_wait: 567.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 37.6µs, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:3, total_time:1.73s}}, tikv_task:{proc max:1.25s, min:41ms, avg: 553.3ms, p80:1.25s, p95:1.25s, iters:1529, tasks:3}, scan_detail: {total_process_keys: 1552483, total_process_keys_size: 85386565, total_keys: 1552486, get_snapshot_time: 445.1µs, rocksdb: {key_skipped_count: 1552483, block: {cache_hit_count: 24, read_count: 2464, read_byte: 32.5 MB, read_time: 18.8ms}}}                                                                                            | range:[1759982590,1759982590], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | N/A      | N/A  |
|     └─TopN_16(Probe)               | 4.00       | 459669  | cop[tikv] |                                                                                         | time:11.4s, loops:248, cop_task: {num: 123237, max: 95.9ms, min: 0s, avg: 4.91ms, p95: 18.6ms, max_proc_keys: 114, p95_proc_keys: 31, tot_proc: 4m15.9s, tot_wait: 11m31.2s, copr_cache_hit_ratio: 0.07, build_task_duration: 553.6ms, max_distsql_concurrency: 15, max_extra_concurrency: 320, store_batch_num: 91623}, rpc_info:{Cop:{num_rpc:31614, total_time:10m5.1s}}, tikv_task:{proc max:0s, min:0s, avg: 13.6ms, p80:21ms, p95:46ms, iters:123237, tasks:123237}, scan_detail: {total_process_keys: 1177706, total_process_keys_size: 6763520793, total_keys: 1283655, get_snapshot_time: 4.91s, rocksdb: {delete_skipped_count: 769471, key_skipped_count: 129519, block: {cache_hit_count: 10773550, read_count: 909716, read_byte: 12.3 GB, read_time: 9.29s}}} | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:4                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | N/A      | N/A  |
|       └─Selection_15               | 1595197.23 | 1552483 | cop[tikv] |                                                                                         | tikv_task:{proc max:0s, min:0s, avg: 13.4ms, p80:25ms, p95:53ms, iters:123237, tasks:123237}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | gt(dw_trade_order_seller_db.trade_order_seller.sub_order_status, 0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | N/A      | N/A  |
|         └─TableRowIDScan_14        | 1599407.00 | 1552483 | cop[tikv] | table:trade_order_seller                                                                | tikv_task:{proc max:0s, min:0s, avg: 13.4ms, p80:19ms, p95:42ms, iters:123237, tasks:123237}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A      | N/A  |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
7 rows in set (2.85 sec)

[WalterWj (王军 - PingCAP)]

可以先加个 set var hint 解决：

 /*+ SET_VAR(tidb_opt_ordering_index_selectivity_threshold=0),use_index(trade_order_seller,idx_seller_id_create_time_us) */

[WalterWj (王军 - PingCAP)]

拿下这两个 SQL 结果提供下：

explain analyze SELECT /*+ use_index(trade_order_seller, idx_seller_id_create_time_us) */
  id,
  order_no,
  sub_order_no,
  biz_type,
  biz_channel,
  biz_code,
  biz_id,
  abs(sub_order_status) AS sub_order_status,
  buyer_id,
  buyer_name,
  seller_id,
  seller_name,
  pay_amount,
  discount_amount,
  inventory_id,
  spu_id,
  sku_id,
  sku_title,
  sku_price,
  sku_count,
  buyer_note,
  deposit_amount,
  poundage_amount,
  poundage_info,
  item_info,
  close_type,
  close_time,
  feature,
  is_del,
  create_time,
  modify_time,
  product_amount,
  freight_amount,
  sku_logo,
  seller_note,
  seller_address_info,
  seller_address_back,
  merchant_info,
  repository_address,
  delivery_deadline,
  tab_tag,
  flag,
  relation_info,
  create_time_us,
  seller_feature,
  seller_type,
  delivery_start_time,
  delivery_no,
  warehouse_code,
  warehouse_address_id,
  down_payment_time,
  balance_payment_time,
  merchant_accept,
  delivery_mode,
  sub_is_del,
  order_status,
  order_deliver_status,
  order_product_amount,
  order_freight_amount,
  order_pay_amount,
  order_discount_amount,
  buyer_address_info,
  order_platform,
  order_source_name,
  order_feature,
  order_flag,
  order_relation_info,
  order_is_del,
  oversea_tax,
  platform_received_time,
  trade_close_reason_id,
  seller_bidding_no,
  sub_order_modify_time,
  main_order_modify_time,
  logistics_delivery_deadline,
  virtual_delivery_type,
  sale_type,
  seller_earliest_logistic_delivery_time,
  seller_earliest_logistic_collect_time,
  seller_earliest_logistic_no,
  seller_earliest_logistic_modify_time,
  seller_earliest_logistic_express_no,
  seller_earliest_logistic_express_status,
  receive_address_confirmed
FROM
  trade_order_seller
WHERE
  seller_id = 1672640944
  AND sub_order_status > 0
ORDER BY
  create_time_us DESC
LIMIT
  0, 4

和

explain analyze SELECT /*+ use_index(trade_order_seller, seller_id) */
  id,
  order_no,
  sub_order_no,
  biz_type,
  biz_channel,
  biz_code,
  biz_id,
  abs(sub_order_status) AS sub_order_status,
  buyer_id,
  buyer_name,
  seller_id,
  seller_name,
  pay_amount,
  discount_amount,
  inventory_id,
  spu_id,
  sku_id,
  sku_title,
  sku_price,
  sku_count,
  buyer_note,
  deposit_amount,
  poundage_amount,
  poundage_info,
  item_info,
  close_type,
  close_time,
  feature,
  is_del,
  create_time,
  modify_time,
  product_amount,
  freight_amount,
  sku_logo,
  seller_note,
  seller_address_info,
  seller_address_back,
  merchant_info,
  repository_address,
  delivery_deadline,
  tab_tag,
  flag,
  relation_info,
  create_time_us,
  seller_feature,
  seller_type,
  delivery_start_time,
  delivery_no,
  warehouse_code,
  warehouse_address_id,
  down_payment_time,
  balance_payment_time,
  merchant_accept,
  delivery_mode,
  sub_is_del,
  order_status,
  order_deliver_status,
  order_product_amount,
  order_freight_amount,
  order_pay_amount,
  order_discount_amount,
  buyer_address_info,
  order_platform,
  order_source_name,
  order_feature,
  order_flag,
  order_relation_info,
  order_is_del,
  oversea_tax,
  platform_received_time,
  trade_close_reason_id,
  seller_bidding_no,
  sub_order_modify_time,
  main_order_modify_time,
  logistics_delivery_deadline,
  virtual_delivery_type,
  sale_type,
  seller_earliest_logistic_delivery_time,
  seller_earliest_logistic_collect_time,
  seller_earliest_logistic_no,
  seller_earliest_logistic_modify_time,
  seller_earliest_logistic_express_no,
  seller_earliest_logistic_express_status,
  receive_address_confirmed
FROM
  trade_order_seller
WHERE
  seller_id = 1672640944
  AND sub_order_status > 0
ORDER BY
  create_time_us DESC
LIMIT
  0, 4

[tidb菜鸟一只 (小菜一颗)]

哦，我没看到他limit是4，我以为是个100之类的数，预计扫出4条数据呢。。。
那limit条数就4条的话，其实走时间索引没啥问题，排序出来取最前面几行数据，如果limit值大一点，就难说哪个执行计划快了

[独善其身 (Ti D Ber Bi Rqfz5 K)]

感觉回表特别多导致的吧

[Royce1220 (Ti D Ber Kwxb3 N7 I)]

最后定位到问题了吗