v7.5.x索引选择

🪐 TiDB 技术问题
1

【TiDB 使用环境】生产环境
【TiDB 版本】v7.5.7
【遇到的问题：问题现象及影响】
为啥下面的SQL会选择索引B，而不是索引A？
索引A：seller_id、sub_order_status、biz_type、create_time_us
索引B：seller_id、create_time_us

`seller_id` = 1764332565
  AND `spu_id` = 4399003
  AND `sub_order_status` IN (
    10,
    1000,
    1100,
    1200,
    2000,
    2003,
    2004,
    2005,
    2006,
    2007,
    2010,
    2015,
    3000,
    3010,
    3020,
    3030,
    3035,
    3040,
    4000,
    7000,
    7500,
    8000,
    8010,
    3029,
    3031,
    3036,
    3032,
    3033,
    3034,
    8011,
    8012,
    8013,
    8014
  )
  AND `biz_type` IN ('26', '39', '1000', '36', '31')
  AND `create_time_us` >= '2023-03-29 00:00:00'
  AND `create_time_us` <= '2023-05-16 23:40:52.928001'
  AND (
    `sub_order_status` != 1000
    OR `biz_type` != 21
  )
ORDER BY
  `create_time_us` DESC
LIMIT
  0, 50;
2

你的这个SQL返回的数据应该比较多。排序耗时比较长，所以选择了B减少排序。。如果返回的数据比较少，那么排序耗时，就会短。

3

sub_order_status != 1000
OR biz_type != 21

这个看起来会导致 A 索引失效，就算走了应该也是走 seller_id 列过滤。

你可以用 hint 走 A 索引后，看看执行计划里面的过滤列覆盖度。

4

去掉这个查询条件，也会走索引B，而不是索引A

5

可以加hint让他走固定的索引，然后对比看看。
我猜测是sub_order_status跟biz_type的唯一度太低，且使用了不等于

6

去掉条件肯定不走啊，中间缺两列为啥要走 A。

7

去掉后的查询条件

`seller_id` = 1764332565
  AND `spu_id` = 4399003
  AND `sub_order_status` IN (
    10,
    1000,
    1100,
    1200,
    2000,
    2003,
    2004,
    2005,
    2006,
    2007,
    2010,
    2015,
    3000,
    3010,
    3020,
    3030,
    3035,
    3040,
    4000,
    7000,
    7500,
    8000,
    8010,
    3029,
    3031,
    3036,
    3032,
    3033,
    3034,
    8011,
    8012,
    8013,
    8014
  )
  AND `biz_type` IN ('26', '39', '1000', '36', '31')
  AND `create_time_us` >= '2023-03-29 00:00:00'
  AND `create_time_us` <= '2023-05-16 23:40:52.928001'
ORDER BY
  `create_time_us` DESC
LIMIT
  0, 50;
8

用 hint 控制走的索引，看下预估和 cost。
感觉是 order by 排序 + limit 比较小，导致优化器认为排序加分页 top 优化会更快导致走 B。

9

而且对比两个索引 hint 确实发现性能差距很大？别迷信“字段多的复合索引更好”哦。。。。

10

:thinking:执行的是explain analyze吗？早上看到一个执行explain的，执行explain不一定准确。

11

强制索引A的explain analyze执行计划

+---------------------------------+-----------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                              | estRows   | actRows | task      | access object                                                                                                       | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | memory   | disk |
+---------------------------------+-----------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_7                    | 48.14     | 50      | root      |                                                                                                                     | time:36.9ms, loops:2, RU:114.508575, 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 | 348.9 KB | N/A  |
| └─TopN_9                        | 48.14     | 50      | root      |                                                                                                                     | time:36.8ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:50                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | 357.2 KB | N/A  |
|   └─IndexLookUp_18              | 48.14     | 138     | root      |                                                                                                                     | time:36.1ms, loops:3, index_task: {total_time: 4.36ms, fetch_handle: 4.36ms, build: 2.6µs, wait: 6.44µs}, table_task: {total_time: 107.5ms, num: 4, concurrency: 5}, next: {wait_index: 3.91ms, wait_table_lookup_build: 8.32ms, wait_table_lookup_resp: 23.1ms}                                                                                                                                                                                                                                                                                                                                                                                                                                                                |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 1.04 MB  | N/A  |
|     ├─Selection_15(Build)       | 125746.33 | 764     | cop[tikv] |                                                                                                                     | time:4.32ms, loops:8, cop_task: {num: 4, max: 4.31ms, min: 2.78ms, avg: 3.41ms, p95: 4.31ms, max_proc_keys: 451, p95_proc_keys: 451, tot_proc: 6.1ms, tot_wait: 1.34ms, copr_cache_hit_ratio: 0.00, build_task_duration: 947.2µs, max_distsql_concurrency: 4}, rpc_info:{Cop:{num_rpc:4, total_time:13.6ms}}, tikv_task:{proc max:2ms, min:1ms, avg: 1.5ms, p80:2ms, p95:2ms, iters:11, tasks:4}, scan_detail: {total_process_keys: 764, total_process_keys_size: 62648, total_keys: 896, get_snapshot_time: 1.19ms, rocksdb: {key_skipped_count: 764, block: {cache_hit_count: 1236, read_count: 14, read_byte: 238.5 KB, read_time: 498.9µs}}}                                                                                | or(ne(dw_trade_order_seller_db.trade_order_seller.sub_order_status, 1000), ne(cast(dw_trade_order_seller_db.trade_order_seller.biz_type, double BINARY), 21))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A      | N/A  |
|     │ └─IndexRangeScan_13       | 125746.33 | 764     | cop[tikv] | table:trade_order_seller, index:idx_selid_status_type_ctimes(seller_id, sub_order_status, biz_type, create_time_us) | tikv_task:{proc max:2ms, min:1ms, avg: 1.5ms, p80:2ms, p95:2ms, iters:11, tasks:4}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | range:[194332555 10 "100" 2023-09-15 00:00:00.000000,194332555 10 "100" 2023-09-15 21:16:03.069001), [194332555 10 "13" 2023-09-15 00:00:00.000000,194332555 10 "13" 2023-09-15 21:16:03.069001), [194332555 10 "21" 2023-09-15 00:00:00.000000,194332555 10 "21" 2023-09-15 21:16:03.069001), [194332555 10 "6" 2023-09-15 00:00:00.000000,194332555 10 "6" 2023-09-15 21:16:03.069001), [194332555 1000 "100" 2023-09-15 00:00:00.000000,194332555 1000 "100" 2023-09-15 21:16:03.069001), [194332555 1000 "13" 2023-09-15 00:00:00.000000,194332555 1000 "13" 2023-09-15 21:16:03.069001), [194332555 1000 "21" 2023-09-15 00:00:00.000000,194332555 1000 "21" 2023-09-15 21:16:03.069001), [194332555 1000 "6" 2023-09-15 00:00:00.000000,194332555 1000 "6" 2023-09-15 21:16:03.069001), [194332555 1100 "100" 2023-09-15 00:00:00.000000,194332555 1100 "100" 2023-09-15 21:16:03.069001), [194332555 1100 "13" 2023-09-15 00:00:00.000000,194332555 1100 "13" 2023-09-15 21:16:03.069001), [194332555 1100 "21" 2023-09-15 00:00:00.000000,194332555 1100 "21" 2023-09-15 21:16:03.069001), [194332555 1100 "6" 2023-09-15 00:00:00.000000,194332555 1100 "6" 2023-09-15 21:16:03.069001), [194332555 1200 "100" 2023-09-15 00:00:00.000000,194332555 1200 "100" 2023-09-15 21:16:03.069001), [194332555 1200 "13" 2023-09-15 00:00:00.000000,194332555 1200 "13" 2023-09-15 21:16:03.069001), [194332555 1200 "21" 2023-09-15 00:00:00.000000,194332555 1200 "21" 2023-09-15 21:16:03.069001), [194332555 1200 "6" 2023-09-15 00:00:00.000000,194332555 1200 "6" 2023-09-15 21:16:03.069001), [194332555 2000 "100" 2023-09-15 00:00:00.000000,194332555 2000 "100" 2023-09-15 21:16:03.069001), [194332555 2000 "13" 2023-09-15 00:00:00.000000,194332555 2000 "13" 2023-09-15 21:16:03.069001), [194332555 2000 "21" 2023-09-15 00:00:00.000000,194332555 2000 "21" 2023-09-15 21:16:03.069001), [194332555 2000 "6" 2023-09-15 00:00:00.000000,194332555 2000 "6" 2023-09-15 21:16:03.069001), [194332555 2003 "100" 2023-09-15 00:00:00.000000,194332555 2003 "100" 2023-09-15 21:16:03.069001), [194332555 2003 "13" 2023-09-15 00:00:00.000000,194332555 2003 "13" 2023-09-15 21:16:03.069001), [194332555 2003 "21" 2023-09-15 00:00:00.000000,194332555 2003 "21" 2023-09-15 21:16:03.069001), [194332555 2003 "6" 2023-09-15 00:00:00.000000,194332555 2003 "6" 2023-09-15 21:16:03.069001), [194332555 2004 "100" 2023-09-15 00:00:00.000000,194332555 2004 "100" 2023-09-15 21:16:03.069001), [194332555 2004 "13" 2023-09-15 00:00:00.000000,194332555 2004 "13" 2023-09-15 21:16:03.069001), [194332555 2004 "21" 2023-09-15 00:00:00.000000,194332555 2004 "21" 2023-09-15 21:16:03.069001), [194332555 2004 "6" 2023-09-15 00:00:00.000000,194332555 2004 "6" 2023-09-15 21:16:03.069001), [194332555 2005 "100" 2023-09-15 00:00:00.000000,194332555 2005 "100" 2023-09-15 21:16:03.069001), [194332555 2005 "13" 2023-09-15 00:00:00.000000,194332555 2005 "13" 2023-09-15 21:16:03.069001), [194332555 2005 "21" 2023-09-15 00:00:00.000000,194332555 2005 "21" 2023-09-15 21:16:03.069001), [194332555 2005 "6" 2023-09-15 00:00:00.000000,194332555 2005 "6" 2023-09-15 21:16:03.069001), [194332555 2006 "100" 2023-09-15 00:00:00.000000,194332555 2006 "100" 2023-09-15 21:16:03.069001), [194332555 2006 "13" 2023-09-15 00:00:00.000000,194332555 2006 "13" 2023-09-15 21:16:03.069001), [194332555 2006 "21" 2023-09-15 00:00:00.000000,194332555 2006 "21" 2023-09-15 21:16:03.069001), [194332555 2006 "6" 2023-09-15 00:00:00.000000,194332555 2006 "6" 2023-09-15 21:16:03.069001), [194332555 2007 "100" 2023-09-15 00:00:00.000000,194332555 2007 "100" 2023-09-15 21:16:03.069001), [194332555 2007 "13" 2023-09-15 00:00:00.000000,194332555 2007 "13" 2023-09-15 21:16:03.069001), [194332555 2007 "21" 2023-09-15 00:00:00.000000,194332555 2007 "21" 2023-09-15 21:16:03.069001), [194332555 2007 "6" 2023-09-15 00:00:00.000000,194332555 2007 "6" 2023-09-15 21:16:03.069001), [194332555 2010 "100" 2023-09-15 00:00:00.000000,194332555 2010 "100" 2023-09-15 21:16:03.069001), [194332555 2010 "13" 2023-09-15 00:00:00.000000,194332555 2010 "13" 2023-09-15 21:16:03.069001), [194332555 2010 "21" 2023-09-15 00:00:00.000000,194332555 2010 "21" 2023-09-15 21:16:03.069001), [194332555 2010 "6" 2023-09-15 00:00:00.000000,194332555 2010 "6" 2023-09-15 21:16:03.069001), [194332555 2015 "100" 2023-09-15 00:00:00.000000,194332555 2015 "100" 2023-09-15 21:16:03.069001), [194332555 2015 "13" 2023-09-15 00:00:00.000000,194332555 2015 "13" 2023-09-15 21:16:03.069001), [194332555 2015 "21" 2023-09-15 00:00:00.000000,194332555 2015 "21" 2023-09-15 21:16:03.069001), [194332555 2015 "6" 2023-09-15 00:00:00.000000,194332555 2015 "6" 2023-09-15 21:16:03.069001), [194332555 3000 "100" 2023-09-15 00:00:00.000000,194332555 3000 "100" 2023-09-15 21:16:03.069001), [194332555 3000 "13" 2023-09-15 00:00:00.000000,194332555 3000 "13" 2023-09-15 21:16:03.069001), [194332555 3000 "21" 2023-09-15 00:00:00.000000,194332555 3000 "21" 2023-09-15 21:16:03.069001), [194332555 3000 "6" 2023-09-15 00:00:00.000000,194332555 3000 "6" 2023-09-15 21:16:03.069001), [194332555 3010 "100" 2023-09-15 00:00:00.000000,194332555 3010 "100" 2023-09-15 21:16:03.069001), [194332555 3010 "13" 2023-09-15 00:00:00.000000,194332555 3010 "13" 2023-09-15 21:16:03.069001), [194332555 3010 "21" 2023-09-15 00:00:00.000000,194332555 3010 "21" 2023-09-15 21:16:03.069001), [194332555 3010 "6" 2023-09-15 00:00:00.000000,194332555 3010 "6" 2023-09-15 21:16:03.069001), [194332555 3020 "100" 2023-09-15 00:00:00.000000,194332555 3020 "100" 2023-09-15 21:16:03.069001), [194332555 3020 "13" 2023-09-15 00:00:00.000000,194332555 3020 "13" 2023-09-15 21:16:03.069001), [194332555 3020 "21" 2023-09-15 00:00:00.000000,194332555 3020 "21" 2023-09-15 21:16:03.069001), [194332555 3020 "6" 2023-09-15 00:00:00.000000,194332555 3020 "6" 2023-09-15 21:16:03.069001), [194332555 3029 "100" 2023-09-15 00:00:00.000000,194332555 3029 "100" 2023-09-15 21:16:03.069001), [194332555 3029 "13" 2023-09-15 00:00:00.000000,194332555 3029 "13" 2023-09-15 21:16:03.069001), [194332555 3029 "21" 2023-09-15 00:00:00.000000,194332555 3029 "21" 2023-09-15 21:16:03.069001), [194332555 3029 "6" 2023-09-15 00:00:00.000000,194332555 3029 "6" 2023-09-15 21:16:03.069001), [194332555 3030 "100" 2023-09-15 00:00:00.000000,194332555 3030 "100" 2023-09-15 21:16:03.069001), [194332555 3030 "13" 2023-09-15 00:00:00.000000,194332555 3030 "13" 2023-09-15 21:16:03.069001), [194332555 3030 "21" 2023-09-15 00:00:00.000000,194332555 3030 "21" 2023-09-15 21:16:03.069001), [194332555 3030 "6" 2023-09-15 00:00:00.000000,194332555 3030 "6" 2023-09-15 21:16:03.069001), [194332555 3031 "100" 2023-09-15 00:00:00.000000,194332555 3031 "100" 2023-09-15 21:16:03.069001), [194332555 3031 "13" 2023-09-15 00:00:00.000000,194332555 3031 "13" 2023-09-15 21:16:03.069001), [194332555 3031 "21" 2023-09-15 00:00:00.000000,194332555 3031 "21" 2023-09-15 21:16:03.069001), [194332555 3031 "6" 2023-09-15 00:00:00.000000,194332555 3031 "6" 2023-09-15 21:16:03.069001), [194332555 3032 "100" 2023-09-15 00:00:00.000000,194332555 3032 "100" 2023-09-15 21:16:03.069001), [194332555 3032 "13" 2023-09-15 00:00:00.000000,194332555 3032 "13" 2023-09-15 21:16:03.069001), [194332555 3032 "21" 2023-09-15 00:00:00.000000,194332555 3032 "21" 2023-09-15 21:16:03.069001), [194332555 3032 "6" 2023-09-15 00:00:00.000000,194332555 3032 "6" 2023-09-15 21:16:03.069001), [194332555 3033 "100" 2023-09-15 00:00:00.000000,194332555 3033 "100" 2023-09-15 21:16:03.069001), [194332555 3033 "13" 2023-09-15 00:00:00.000000,194332555 3033 "13" 2023-09-15 21:16:03.069001), [194332555 3033 "21" 2023-09-15 00:00:00.000000,194332555 3033 "21" 2023-09-15 21:16:03.069001), [194332555 3033 "6" 2023-09-15 00:00:00.000000,194332555 3033 "6" 2023-09-15 21:16:03.069001), [194332555 3034 "100" 2023-09-15 00:00:00.000000,194332555 3034 "100" 2023-09-15 21:16:03.069001), [194332555 3034 "13" 2023-09-15 00:00:00.000000,194332555 3034 "13" 2023-09-15 21:16:03.069001), [194332555 3034 "21" 2023-09-15 00:00:00.000000,194332555 3034 "21" 2023-09-15 21:16:03.069001), [194332555 3034 "6" 2023-09-15 00:00:00.000000,194332555 3034 "6" 2023-09-15 21:16:03.069001), [194332555 3035 "100" 2023-09-15 00:00:00.000000,194332555 3035 "100" 2023-09-15 21:16:03.069001), [194332555 3035 "13" 2023-09-15 00:00:00.000000,194332555 3035 "13" 2023-09-15 21:16:03.069001), [194332555 3035 "21" 2023-09-15 00:00:00.000000,194332555 3035 "21" 2023-09-15 21:16:03.069001), [194332555 3035 "6" 2023-09-15 00:00:00.000000,194332555 3035 "6" 2023-09-15 21:16:03.069001), [194332555 3036 "100" 2023-09-15 00:00:00.000000,194332555 3036 "100" 2023-09-15 21:16:03.069001), [194332555 3036 "13" 2023-09-15 00:00:00.000000,194332555 3036 "13" 2023-09-15 21:16:03.069001), [194332555 3036 "21" 2023-09-15 00:00:00.000000,194332555 3036 "21" 2023-09-15 21:16:03.069001), [194332555 3036 "6" 2023-09-15 00:00:00.000000,194332555 3036 "6" 2023-09-15 21:16:03.069001), [194332555 3040 "100" 2023-09-15 00:00:00.000000,194332555 3040 "100" 2023-09-15 21:16:03.069001), [194332555 3040 "13" 2023-09-15 00:00:00.000000,194332555 3040 "13" 2023-09-15 21:16:03.069001), [194332555 3040 "21" 2023-09-15 00:00:00.000000,194332555 3040 "21" 2023-09-15 21:16:03.069001), [194332555 3040 "6" 2023-09-15 00:00:00.000000,194332555 3040 "6" 2023-09-15 21:16:03.069001), [194332555 4000 "100" 2023-09-15 00:00:00.000000,194332555 4000 "100" 2023-09-15 21:16:03.069001), [194332555 4000 "13" 2023-09-15 00:00:00.000000,194332555 4000 "13" 2023-09-15 21:16:03.069001), [194332555 4000 "21" 2023-09-15 00:00:00.000000,194332555 4000 "21" 2023-09-15 21:16:03.069001), [194332555 4000 "6" 2023-09-15 00:00:00.000000,194332555 4000 "6" 2023-09-15 21:16:03.069001), [194332555 7000 "100" 2023-09-15 00:00:00.000000,194332555 7000 "100" 2023-09-15 21:16:03.069001), [194332555 7000 "13" 2023-09-15 00:00:00.000000,194332555 7000 "13" 2023-09-15 21:16:03.069001), [194332555 7000 "21" 2023-09-15 00:00:00.000000,194332555 7000 "21" 2023-09-15 21:16:03.069001), [194332555 7000 "6" 2023-09-15 00:00:00.000000,194332555 7000 "6" 2023-09-15 21:16:03.069001), [194332555 7500 "100" 2023-09-15 00:00:00.000000,194332555 7500 "100" 2023-09-15 21:16:03.069001), [194332555 7500 "13" 2023-09-15 00:00:00.000000,194332555 7500 "13" 2023-09-15 21:16:03.069001), [194332555 7500 "21" 2023-09-15 00:00:00.000000,194332555 7500 "21" 2023-09-15 21:16:03.069001), [194332555 7500 "6" 2023-09-15 00:00:00.000000,194332555 7500 "6" 2023-09-15 21:16:03.069001), [194332555 8000 "100" 2023-09-15 00:00:00.000000,194332555 8000 "100" 2023-09-15 21:16:03.069001), [194332555 8000 "13" 2023-09-15 00:00:00.000000,194332555 8000 "13" 2023-09-15 21:16:03.069001), [194332555 8000 "21" 2023-09-15 00:00:00.000000,194332555 8000 "21" 2023-09-15 21:16:03.069001), [194332555 8000 "6" 2023-09-15 00:00:00.000000,194332555 8000 "6" 2023-09-15 21:16:03.069001), [194332555 8010 "100" 2023-09-15 00:00:00.000000,194332555 8010 "100" 2023-09-15 21:16:03.069001), [194332555 8010 "13" 2023-09-15 00:00:00.000000,194332555 8010 "13" 2023-09-15 21:16:03.069001), [194332555 8010 "21" 2023-09-15 00:00:00.000000,194332555 8010 "21" 2023-09-15 21:16:03.069001), [194332555 8010 "6" 2023-09-15 00:00:00.000000,194332555 8010 "6" 2023-09-15 21:16:03.069001), [194332555 8011 "100" 2023-09-15 00:00:00.000000,194332555 8011 "100" 2023-09-15 21:16:03.069001), [194332555 8011 "13" 2023-09-15 00:00:00.000000,194332555 8011 "13" 2023-09-15 21:16:03.069001), [194332555 8011 "21" 2023-09-15 00:00:00.000000,194332555 8011 "21" 2023-09-15 21:16:03.069001), [194332555 8011 "6" 2023-09-15 00:00:00.000000,194332555 8011 "6" 2023-09-15 21:16:03.069001), [194332555 8012 "100" 2023-09-15 00:00:00.000000,194332555 8012 "100" 2023-09-15 21:16:03.069001), [194332555 8012 "13" 2023-09-15 00:00:00.000000,194332555 8012 "13" 2023-09-15 21:16:03.069001), [194332555 8012 "21" 2023-09-15 00:00:00.000000,194332555 8012 "21" 2023-09-15 21:16:03.069001), [194332555 8012 "6" 2023-09-15 00:00:00.000000,194332555 8012 "6" 2023-09-15 21:16:03.069001), [194332555 8013 "100" 2023-09-15 00:00:00.000000,194332555 8013 "100" 2023-09-15 21:16:03.069001), [194332555 8013 "13" 2023-09-15 00:00:00.000000,194332555 8013 "13" 2023-09-15 21:16:03.069001), [194332555 8013 "21" 2023-09-15 00:00:00.000000,194332555 8013 "21" 2023-09-15 21:16:03.069001), [194332555 8013 "6" 2023-09-15 00:00:00.000000,194332555 8013 "6" 2023-09-15 21:16:03.069001), [194332555 8014 "100" 2023-09-15 00:00:00.000000,194332555 8014 "100" 2023-09-15 21:16:03.069001), [194332555 8014 "13" 2023-09-15 00:00:00.000000,194332555 8014 "13" 2023-09-15 21:16:03.069001), [194332555 8014 "21" 2023-09-15 00:00:00.000000,194332555 8014 "21" 2023-09-15 21:16:03.069001), [194332555 8014 "6" 2023-09-15 00:00:00.000000,194332555 8014 "6" 2023-09-15 21:16:03.069001), keep order:false | N/A      | N/A  |
|     └─TopN_17(Probe)            | 48.14     | 138     | cop[tikv] |                                                                                                                     | time:74.5ms, loops:8, cop_task: {num: 224, max: 22.9ms, min: 0s, avg: 2.03ms, p95: 7.5ms, max_proc_keys: 16, p95_proc_keys: 9, tot_proc: 501.3ms, tot_wait: 87.1ms, copr_cache_hit_ratio: 0.03, build_task_duration: 32.4ms, max_distsql_concurrency: 1, max_extra_concurrency: 10, store_batch_num: 139}, rpc_info:{Cop:{num_rpc:85, total_time:452.9ms}}, tikv_task:{proc max:20ms, min:0s, avg: 2.71ms, p80:4ms, p95:9ms, iters:224, tasks:224}, scan_detail: {total_process_keys: 712, total_process_keys_size: 4005020, total_keys: 975, get_snapshot_time: 64.1ms, rocksdb: {delete_skipped_count: 362, key_skipped_count: 265, block: {cache_hit_count: 8157, read_count: 439, read_byte: 6.68 MB, read_time: 168.4ms}}} | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:50                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | N/A      | N/A  |
|       └─Selection_16            | 48.14     | 138     | cop[tikv] |                                                                                                                     | tikv_task:{proc max:20ms, min:0s, avg: 2.53ms, p80:3ms, p95:9ms, iters:224, tasks:224}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | eq(dw_trade_order_seller_db.trade_order_seller.spu_id, 2336463), ne(bitand(dw_trade_order_seller_db.trade_order_seller.flag, 262144), 262144)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A      | N/A  |
|         └─TableRowIDScan_14     | 125746.33 | 764     | cop[tikv] | table:trade_order_seller                                                                                            | tikv_task:{proc max:20ms, min:0s, avg: 2.51ms, p80:3ms, p95:9ms, iters:224, tasks:224}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A      | N/A  |
+---------------------------------+-----------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
8 rows in set (0.04 sec)
12

默认索引B的explain analyze执行计划

+------------------------------------+----------+---------+-----------+-----------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                                 | estRows  | actRows | task      | access object                                                                           | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | memory   | disk |
+------------------------------------+----------+---------+-----------+-----------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| Projection_7                       | 48.14    | 50      | root      |                                                                                         | time:12.4ms, loops:2, RU:62.327267, 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 | 348.9 KB | N/A  |
| └─Limit_12                         | 48.14    | 50      | root      |                                                                                         | time:12.3ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | offset:0, count:50                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | N/A      | N/A  |
|   └─IndexLookUp_22                 | 48.14    | 50      | root      |                                                                                         | time:12.3ms, loops:1, index_task: {total_time: 2.94ms, fetch_handle: 2.8ms, build: 128.9µs, wait: 6.53µs}, table_task: {total_time: 43.8ms, num: 5, concurrency: 5}, next: {wait_index: 3.21ms, wait_table_lookup_build: 348µs, wait_table_lookup_resp: 8.37ms}                                                                                                                                                                                                                                                                                                                                                                                                                                                            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 705.1 KB | N/A  |
|     ├─IndexRangeScan_19(Build)     | 34248.81 | 1194    | cop[tikv] | table:trade_order_seller, index:idx_seller_id_create_time_us(seller_id, create_time_us) | time:2.74ms, loops:8, cop_task: {num: 1, max: 2.68ms, proc_keys: 1194, tot_proc: 1.65ms, tot_wait: 400.4µs, copr_cache_hit_ratio: 0.00, build_task_duration: 323.5µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:2.66ms}}, tikv_task:{time:1ms, loops:6}, scan_detail: {total_process_keys: 1194, total_process_keys_size: 65670, total_keys: 1195, get_snapshot_time: 362.5µs, rocksdb: {key_skipped_count: 1194, block: {cache_hit_count: 8, read_count: 4, read_byte: 72.8 KB, read_time: 424.6µs}}}                                                                                                                                                                                             | range:[194332555 2023-09-15 00:00:00.000000,194332555 2023-09-15 21:16:03.069001), keep order:true, desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | N/A      | N/A  |
|     └─Selection_21(Probe)          | 48.14    | 131     | cop[tikv] |                                                                                         | time:41.5ms, loops:8, cop_task: {num: 80, max: 8.64ms, min: 0s, avg: 2.13ms, p95: 7.51ms, max_proc_keys: 23, p95_proc_keys: 20, tot_proc: 116.5ms, tot_wait: 29.7ms, copr_cache_hit_ratio: 0.32, build_task_duration: 1.29ms, max_distsql_concurrency: 2, max_extra_concurrency: 8, store_batch_num: 24}, rpc_info:{Cop:{num_rpc:56, total_time:170ms}}, tikv_task:{proc max:21ms, min:0s, avg: 5.24ms, p80:9ms, p95:15ms, iters:81, tasks:80}, scan_detail: {total_process_keys: 407, total_process_keys_size: 2306874, total_keys: 534, get_snapshot_time: 27.1ms, rocksdb: {delete_skipped_count: 231, key_skipped_count: 129, block: {cache_hit_count: 4376, read_count: 184, read_byte: 2.90 MB, read_time: 37.3ms}}} | eq(dw_trade_order_seller_db.trade_order_seller.spu_id, 2336463), in(dw_trade_order_seller_db.trade_order_seller.biz_type, "6", "100", "13", "21"), in(dw_trade_order_seller_db.trade_order_seller.sub_order_status, 10, 1000, 1100, 1200, 2000, 2003, 2004, 2005, 2006, 2007, 2010, 2015, 3000, 3010, 3020, 3030, 3035, 3040, 4000, 7000, 7500, 8000, 8010, 3029, 3031, 3036, 3032, 3033, 3034, 8011, 8012, 8013, 8014), ne(bitand(dw_trade_order_seller_db.trade_order_seller.flag, 262144), 262144), or(ne(dw_trade_order_seller_db.trade_order_seller.sub_order_status, 1000), ne(cast(dw_trade_order_seller_db.trade_order_seller.biz_type, double BINARY), 21))                                                                                                                                                                                                                                                                                                                                                                                             | N/A      | N/A  |
|       └─TableRowIDScan_20          | 34248.81 | 851     | cop[tikv] | table:trade_order_seller                                                                | tikv_task:{proc max:21ms, min:0s, avg: 5.13ms, p80:8ms, p95:15ms, iters:81, tasks:80}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A      | N/A  |
+------------------------------------+----------+---------+-----------+-----------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
6 rows in set (0.02 sec)
13

强制走索引试试

14

30ms 和 16ms 区别差异在这看个啥。

15

这个事情很好理解，给你翻译一下：
索引A：假设满足seller_id = 1764332565 的行数有n行，经过sub_order_status, biz_type筛选后假设有A1行，然后再经过create_time_us筛选最后返回100行。
索引B：假设满足seller_id = 1764332565 的行数有n行，经过create_time_us筛选最后返回B1行，然后经过sub_order_status, biz_type筛选最后返回100行。
如果B1明显小于A1 优化器不就选择了索引B了吗

16

这不是默认的执行效率比强制指定的索引还要高吗？那选idx_seller_id_create_time_us这个索引肯定的啊

17

keep order:true, des 索引B可以避免排序

18

对于你提到的索引 A（seller_id、sub_order_status、biz_type、create_time_us）和索引 B（seller_id、create_time_us）的选择问题，核心原因通常与查询对索引列的利用程度、索引的 “紧凑性” 及优化器对扫描成本的评估有关。