查询计划中索引选择的问题

EricSong · 2024 年5 月 16 日 14:19

【TiDB 使用环境】测试
【TiDB 版本】v6.5.0
【复现路径】
创建表如下

CREATE TABLE `ls` (
  `timestamp` timestamp(3) NULL DEFAULT NULL,
  `organization_id` varchar(61) NOT NULL,
  `cluster_name` varchar(100) DEFAULT NULL,
  `cluster_id` varchar(61) NOT NULL,
  `host_name` varchar(255) DEFAULT NULL,
  `host_ip` varchar(20) DEFAULT NULL,
   ...
  `service_state` float DEFAULT NULL,
  UNIQUE KEY `timestamp` (`timestamp`,`organization_id`,`cluster_id`,`host_name`,`host_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

表中有数据5G，共3千多万条数据，timestamp时间跨度1年左右

【遇到的问题：问题现象及影响】
只要查询范围大于5天就会开始扫描全表，小于5天才会走索引

小于5天
explain SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-04-27 01:32:47" and ls.timestamp < "2024-04-30 01:32:47"
id                         |estRows  |task     |access object                                                                        |operator info                                                            |
---------------------------+---------+---------+-------------------------------------------------------------------------------------+-------------------------------------------------------------------------+
Projection_4               |362228.19|root     |                                                                                     |ls.service_state, ls.timestamp     |
└─IndexLookUp_10           |362228.19|root     |                                                                                     |                                                                         |
  ├─IndexRangeScan_8(Build)|362228.19|cop[tikv]|table:ls, index:timestamp(timestamp, organization_id, cluster_id, host_name, host_ip)|range:(2024-04-27 01:32:47.000,2024-04-30 01:32:47.000), keep order:false|
  └─TableRowIDScan_9(Probe)|362228.19|cop[tikv]|table:ls                                                                             |keep order:false                                                         |
---
大于5天
explain SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-04-25 01:32:47" and ls.timestamp < "2024-04-30 01:32:47"
id                   |estRows    |task     |access object|operator info                                                                                                                   |
---------------------+-----------+---------+-------------+--------------------------------------------------------------------------------------------------------------------------------+
Projection_4         |629602.92  |root     |             |ls.service_state, ls.timestamp                                                            |
└─TableReader_7      |629602.92  |root     |             |data:Selection_6                                                                                                                |
  └─Selection_6      |629602.92  |cop[tikv]|             |gt(ls.timestamp, 2024-04-25 01:32:47.000000), lt(ls.timestamp, 2024-04-30 01:32:47.000000)|
    └─TableFullScan_5|34892667.00|cop[tikv]|table:ls     |keep order:false                                                                                                                |

表的健康度是100
我理解只要走索引和回表的行数加起来不超过总行数，那理论上走索引的效率就是高的，为什么5天后就开始不走索引了？是有什么优化机制吗？

Kongdom · 2024 年5 月 16 日 14:59

执行explain analyze select试试看呢？

zhaokede · 2024 年5 月 16 日 16:03

换个时间间隔看一下执行计划，是不是这一段时间内的数据量特大，所以才走全表扫描

YuchongXU · 2024 年5 月 16 日 23:32

多测试几个时间，看一下执行计划

TIDB-Learner · 2024 年5 月 17 日 00:21

连续时间使用between and 试试呢？

lemonade010 · 2024 年5 月 17 日 00:39

我也遇到这样一个问题，使用>=,和between 效果是一样的，不清楚具体的cost条件是什么导致走全表扫描

tidb菜鸟一只 · 2024 年5 月 17 日 00:43

你explain analyze 执行下看看，可能是explain执行计划有偏差，3000W数据过滤60W+就全表扫不太对

yytest · 2024 年5 月 17 日 00:45

设置一下hint，指定一下可以吗？

DBAER · 2024 年5 月 17 日 00:56

强制走索引，对比一下行数，

友利奈绪 · 2024 年5 月 17 日 01:20

tidb可以强制走某个索引对比一下

EricSong · 2024 年5 月 17 日 01:42

执行EXPLAIN ANALYZE如下，看起来和EXPLAIN差不多

explain analyze SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-04-27 01:32:47" and ls.timestamp < "2024-04-30 01:32:47"
id                         |estRows  |actRows|task     |access object                                                                        |execution info                                                                                                                                                                                                                                                 |operator info                                                            |memory  |disk|
---------------------------+---------+-------+---------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+--------+----+
Projection_4               |362228.19|381485 |root     |                                                                                     |time:367.7ms, loops:374, Concurrency:5                                                                                                                                                                                                                         |ls.service_state, ls.timestamp     |137.3 KB|N/A |
└─IndexLookUp_10           |362228.19|381485 |root     |                                                                                     |time:366.7ms, loops:374, index_task: {total_time: 334.7ms, fetch_handle: 334.4ms, build: 63µs, wait: 313.3µs}, table_task: {total_time: 554.5ms, num: 23, concurrency: 5}                                                                                      |                                                                         |3.30 MB |N/A |
  ├─IndexRangeScan_8(Build)|362228.19|381485 |cop[tikv]|table:ls, index:timestamp(timestamp, organization_id, cluster_id, host_name, host_ip)|time:289.3ms, loops:379, cop_task: {num: 24, max: 75.2ms, min: 1.64ms, avg: 22.5ms, p95: 61.3ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 487ms, tot_wait: 1ms, rpc_num: 24, rpc_time: 540.6ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 1|range:(2024-04-27 01:32:47.000,2024-04-30 01:32:47.000), keep order:false|N/A     |N/A |
  └─TableRowIDScan_9(Probe)|362228.19|381485 |cop[tikv]|table:ls                                                                             |time:367.3ms, loops:413, cop_task: {num: 37, max: 29.8ms, min: 2.1ms, avg: 12.1ms, p95: 23.8ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 372ms, tot_wait: 1ms, rpc_num: 37, rpc_time: 447.8ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15|keep order:false                                                         |N/A     |N/A |
--------------------------------------------------------------------------------
explain analyze SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-04-25 01:32:47" and ls.timestamp < "2024-04-30 01:32:47"
id                   |estRows    |actRows |task     |access object|execution info                                                                                                                                                                                                                                                 |operator info                                                                                                                   |memory  |disk|
---------------------+-----------+--------+---------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------+--------+----+
Projection_4         |629602.92  |640142  |root     |             |time:1.94s, loops:630, Concurrency:5                                                                                                                                                                                                                           |ls.service_state, ls.timestamp                                                            |129.2 KB|N/A |
└─TableReader_7      |629602.92  |640142  |root     |             |time:1.94s, loops:630, cop_task: {num: 97, max: 521.9ms, min: 868.1µs, avg: 271.6ms, p95: 477.4ms, max_proc_keys: 554792, p95_proc_keys: 521241, tot_proc: 25.8s, tot_wait: 342ms, rpc_num: 97, rpc_time: 26.3s, copr_cache_hit_ratio: 0.00, distsql_concurrenc|data:Selection_6                                                                                                                |1.15 MB |N/A |
  └─Selection_6      |629602.92  |640142  |cop[tikv]|             |tikv_task:{proc max:505ms, min:0s, avg: 266.2ms, p80:379ms, p95:464ms, iters:34498, tasks:97}, scan_detail: {total_process_keys: 34892667, total_process_keys_size: 7457693379, total_keys: 34892764, get_snapshot_time: 2.78ms, rocksdb: {key_skipped_count: 3|gt(ls.timestamp, 2024-04-25 01:32:47.000000), lt(ls.timestamp, 2024-04-30 01:32:47.000000)|N/A     |N/A |
    └─TableFullScan_5|34892667.00|34892667|cop[tikv]|table:ls     |tikv_task:{proc max:408ms, min:0s, avg: 192.7ms, p80:276ms, p95:348ms, iters:34498, tasks:97}                                                                                                                                                                  |keep order:false                                                                                                                |N/A     |N/A |

EricSong · 2024 年5 月 17 日 01:44

指定OptimizeHint是可以强制使用索引的，但是我比较好奇的是为什么在索引行数+回写行数<全表扫描行数时仍然走了全表扫描
我查到了cost-model的一些信息，但还是不理解为什么索引行数+回写行数的代价会大于全表扫描

EricSong · 2024 年5 月 17 日 01:46

这是强制走索引的结果，看起来强制走索引的总扫描行数还是更小的，但不知道为什么就是不自动走索引

explain analyze SELECT /*+ USE_INDEX(ls, timestamp) */ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-04-25 01:32:47" and ls.timestamp < "2024-04-30 01:32:47"
id                         |estRows  |actRows|task     |access object                                                                        |execution info                                                                                                                                                                                                                                                 |operator info                                                            |memory  |disk|
---------------------------+---------+-------+---------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+--------+----+
Projection_4               |629602.92|640142 |root     |                                                                                     |time:495.7ms, loops:627, Concurrency:5                                                                                                                                                                                                                         |ls.service_state, ls.timestamp     |137.4 KB|N/A |
└─IndexLookUp_7            |629602.92|640142 |root     |                                                                                     |time:494.4ms, loops:627, index_task: {total_time: 472.1ms, fetch_handle: 435.6ms, build: 82.7µs, wait: 36.4ms}, table_task: {total_time: 901.1ms, num: 35, concurrency: 5}                                                                                     |                                                                         |3.33 MB |N/A |
  ├─IndexRangeScan_5(Build)|629602.92|640142 |cop[tikv]|table:ls, index:timestamp(timestamp, organization_id, cluster_id, host_name, host_ip)|time:366.3ms, loops:631, cop_task: {num: 37, max: 77.8ms, min: 394.2µs, avg: 14.5ms, p95: 67.9ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 481ms, rpc_num: 37, rpc_time: 536.5ms, copr_cache_hit_ratio: 0.27, distsql_concurrency: 15}, tikv_task:|range:(2024-04-25 01:32:47.000,2024-04-30 01:32:47.000), keep order:false|N/A     |N/A |
  └─TableRowIDScan_6(Probe)|629602.92|640142 |cop[tikv]|table:ls                                                                             |time:631.5ms, loops:679, cop_task: {num: 46, max: 30ms, min: 1.58ms, avg: 15ms, p95: 27.6ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 584ms, tot_wait: 1ms, rpc_num: 46, rpc_time: 688.3ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, |keep order:false                                                         |N/A     |N/A |
------------------------------------------
explain analyze SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-04-25 01:32:47" and ls.timestamp < "2024-04-30 01:32:47"
id                   |estRows    |actRows |task     |access object|execution info                                                                                                                                                                                                                                                 |operator info                                                                                                                   |memory  |disk|
---------------------+-----------+--------+---------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------+--------+----+
Projection_4         |629602.92  |640142  |root     |             |time:814.8ms, loops:630, Concurrency:5                                                                                                                                                                                                                         |ls.service_state, ls.timestamp                                                            |128.9 KB|N/A |
└─TableReader_7      |629602.92  |640142  |root     |             |time:812.6ms, loops:630, cop_task: {num: 97, max: 556.6ms, min: 772µs, avg: 88.5ms, p95: 471.2ms, max_proc_keys: 554792, p95_proc_keys: 485769, tot_proc: 8.36s, tot_wait: 40ms, rpc_num: 97, rpc_time: 8.58s, copr_cache_hit_ratio: 0.52, distsql_concurrency:|data:Selection_6                                                                                                                |1.15 MB |N/A |
  └─Selection_6      |629602.92  |640142  |cop[tikv]|             |tikv_task:{proc max:555ms, min:1ms, avg: 280.4ms, p80:411ms, p95:478ms, iters:34498, tasks:97}, scan_detail: {total_process_keys: 9781457, total_process_keys_size: 2115995946, total_keys: 9781504, get_snapshot_time: 62.7ms, rocksdb: {key_skipped_count: 97|gt(ls.timestamp, 2024-04-25 01:32:47.000000), lt(ls.timestamp, 2024-04-30 01:32:47.000000)|N/A     |N/A |
    └─TableFullScan_5|34892667.00|34892667|cop[tikv]|table:ls     |tikv_task:{proc max:449ms, min:1ms, avg: 205.4ms, p80:305ms, p95:370ms, iters:34498, tasks:97}                                                                                                                                                                 |keep order:false                                                                                                                |N/A     |N/A |

EricSong · 2024 年5 月 17 日 02:09

看起来和时间段内的数据确实有关，但是我不理解为什么会这样，难道即便索引+回写的行数小于全表扫描行数一个数量级，实际耗时也是前者更高吗？

explain analyze SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-03-25 01:32:47" and ls.timestamp < "2024-03-30 01:32:47"
id                   |estRows    |actRows |task     |access object|execution info                                                                                                                                                                                                                                                 |operator info                                                                                                                   |memory  |disk|
---------------------+-----------+--------+---------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------+--------+----+
Projection_4         |580657.83  |578325  |root     |             |time:2.41s, loops:570, Concurrency:5                                                                                                                                                                                                                           |ls.service_state, ls.timestamp                                                            |129.2 KB|N/A |
└─TableReader_7      |580657.83  |578325  |root     |             |time:2.41s, loops:570, cop_task: {num: 95, max: 829.4ms, min: 1.17ms, avg: 344.9ms, p95: 689.8ms, max_proc_keys: 554792, p95_proc_keys: 521241, tot_proc: 32.5s, tot_wait: 43ms, rpc_num: 95, rpc_time: 32.8s, copr_cache_hit_ratio: 0.00, distsql_concurrency:|data:Selection_6                                                                                                                |1.08 MB |N/A |
  └─Selection_6      |580657.83  |578325  |cop[tikv]|             |tikv_task:{proc max:829ms, min:0s, avg: 342.5ms, p80:502ms, p95:687ms, iters:34490, tasks:95}, scan_detail: {total_process_keys: 34892667, total_process_keys_size: 7457693379, total_keys: 34892762, get_snapshot_time: 2.92ms, rocksdb: {key_skipped_count: 3|gt(ls.timestamp, 2024-03-25 01:32:47.000000), lt(ls.timestamp, 2024-03-30 01:32:47.000000)|N/A     |N/A |
    └─TableFullScan_5|34892667.00|34892667|cop[tikv]|table:ls     |tikv_task:{proc max:727ms, min:0s, avg: 265.5ms, p80:394ms, p95:558ms, iters:34490, tasks:95}                                                                                                                                                                  |keep order:false                                                                                                                |N/A     |N/A |
-----
explain analyze SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-03-26 01:32:47" and ls.timestamp < "2024-03-30 01:32:47"
id                         |estRows  |actRows|task     |access object                                                                        |execution info                                                                                                                                                                                                                                                 |operator info                                                            |memory  |disk|
---------------------------+---------+-------+---------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+--------+----+
Projection_4               |477004.35|469005 |root     |                                                                                     |time:498ms, loops:460, Concurrency:5                                                                                                                                                                                                                           |ls.service_state, ls.timestamp     |140.2 KB|N/A |
└─IndexLookUp_10           |477004.35|469005 |root     |                                                                                     |time:497.2ms, loops:460, index_task: {total_time: 471.3ms, fetch_handle: 470.9ms, build: 79.9µs, wait: 335.1µs}, table_task: {total_time: 594.9ms, num: 27, concurrency: 5}                                                                                    |                                                                         |2.64 MB |N/A |
  ├─IndexRangeScan_8(Build)|477004.35|469005 |cop[tikv]|table:ls, index:timestamp(timestamp, organization_id, cluster_id, host_name, host_ip)|time:420.1ms, loops:464, cop_task: {num: 33, max: 77.7ms, min: 1.44ms, avg: 20.7ms, p95: 69.8ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 614ms, rpc_num: 33, rpc_time: 681.1ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{|range:(2024-03-26 01:32:47.000,2024-03-30 01:32:47.000), keep order:false|N/A     |N/A |
  └─TableRowIDScan_9(Probe)|477004.35|469005 |cop[tikv]|table:ls                                                                             |time:395.5ms, loops:499, cop_task: {num: 37, max: 21ms, min: 1.19ms, avg: 11.9ms, p95: 20.3ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 363ms, tot_wait: 1ms, rpc_num: 37, rpc_time: 439.2ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}|keep order:false                                                         |N/A     |N/A |
-------
explain analyze SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2023-12-25 01:32:47" and ls.timestamp < "2023-12-30 01:32:47"

id                   |estRows    |actRows |task     |access object|execution info                                                                                                                                                                                                                                                 |operator info                                                                                                                   |memory  |disk|
---------------------+-----------+--------+---------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------+--------+----+
Projection_4         |633659.55  |665571  |root     |             |time:2.11s, loops:655, Concurrency:5                                                                                                                                                                                                                           |ls.service_state, ls.timestamp                                                            |130.8 KB|N/A |
└─TableReader_7      |633659.55  |665571  |root     |             |time:2.11s, loops:655, cop_task: {num: 103, max: 509.2ms, min: 1.46ms, avg: 273.1ms, p95: 489.4ms, max_proc_keys: 554792, p95_proc_keys: 521184, tot_proc: 27.6s, tot_wait: 337ms, rpc_num: 103, rpc_time: 28.1s, copr_cache_hit_ratio: 0.00, distsql_concurren|data:Selection_6                                                                                                                |1.15 MB |N/A |
  └─Selection_6      |633659.55  |665571  |cop[tikv]|             |tikv_task:{proc max:494ms, min:0s, avg: 267.9ms, p80:427ms, p95:479ms, iters:34520, tasks:103}, scan_detail: {total_process_keys: 34892667, total_process_keys_size: 7457693379, total_keys: 34892770, get_snapshot_time: 141.2ms, rocksdb: {key_skipped_count:|gt(ls.timestamp, 2023-12-25 01:32:47.000000), lt(ls.timestamp, 2023-12-30 01:32:47.000000)|N/A     |N/A |
    └─TableFullScan_5|34892667.00|34892667|cop[tikv]|table:ls     |tikv_task:{proc max:408ms, min:0s, avg: 196.2ms, p80:322ms, p95:369ms, iters:34520, tasks:103}                                                                                                                                                                 |keep order:false                                                                                                                |N/A     |N/A |
------
explain analyze SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2023-11-25 01:32:47" and ls.timestamp < "2023-11-30 01:32:47"
id                         |estRows  |actRows|task     |access object                                                                        |execution info                                                                                                                                                                                                                                                 |operator info                                                            |memory  |disk|
---------------------------+---------+-------+---------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+--------+----+
Projection_4               |206297.88|271519 |root     |                                                                                     |time:62.5ms, loops:267, Concurrency:5                                                                                                                                                                                                                          |ls.service_state, ls.timestamp     |137.3 KB|N/A |
└─IndexLookUp_10           |206297.88|271519 |root     |                                                                                     |time:62.1ms, loops:267, index_task: {total_time: 50.8ms, fetch_handle: 20.9ms, build: 23µs, wait: 29.9ms}, table_task: {total_time: 246.8ms, num: 17, concurrency: 5}                                                                                          |                                                                         |3.93 MB |N/A |
  ├─IndexRangeScan_8(Build)|206297.88|271519 |cop[tikv]|table:ls, index:timestamp(timestamp, organization_id, cluster_id, host_name, host_ip)|time:8.8ms, loops:272, cop_task: {num: 21, max: 2.54ms, min: 382.8µs, avg: 1.07ms, p95: 2.13ms, max_proc_keys: 992, p95_proc_keys: 992, tot_proc: 1ms, rpc_num: 21, rpc_time: 22.1ms, copr_cache_hit_ratio: 0.71, distsql_concurrency: 15}, tikv_task:{proc max|range:(2023-11-25 01:32:47.000,2023-11-30 01:32:47.000), keep order:false|N/A     |N/A |
  └─TableRowIDScan_9(Probe)|206297.88|271519 |cop[tikv]|table:ls                                                                             |time:163.2ms, loops:294, cop_task: {num: 27, max: 30.7ms, min: 562.8µs, avg: 7.66ms, p95: 25.7ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 161ms, rpc_num: 27, rpc_time: 206.1ms, copr_cache_hit_ratio: 0.22, distsql_concurrency: 15}, tikv_task:|keep order:false                                                         |N/A     |N/A |

xfworld · 2024 年5 月 17 日 03:30

这个表的数据变动是不是特别大？
我观察到，扫描全表，就会扫描的key，和总key数相差不大了…
total_process_keys: 34892667, total_keys: 34892770,

看起来 timestamp 是热点索引… 没启到效果
可能你要换种方式来建索引… 要尝试一下

zhaokede · 2024 年5 月 17 日 04:30

业务上，能不能换一个效率更高的索引或新建联合索引

EricSong · 2024 年5 月 17 日 05:47

但我奇怪的是如果强制使用索引实际上可以得到一个不错的结果
这是强制走索引的结果，看起来强制走索引的总扫描行数还是更小的，但不知道为什么就是不自动走索引，可能是索引没有只建在timestamp上或者其他原因，TiDB做了代价评估后取消了，但是我还是不理解差一个甚至两个数量级下扫描全表还能是更优吗？

explain analyze SELECT /*+ USE_INDEX(ls, timestamp) */ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-04-25 01:32:47" and ls.timestamp < "2024-04-30 01:32:47"
id                         |estRows  |actRows|task     |access object                                                                        |execution info                                                                                                                                                                                                                                                 |operator info                                                            |memory  |disk|
---------------------------+---------+-------+---------+-------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+--------+----+
Projection_4               |629602.92|640142 |root     |                                                                                     |time:495.7ms, loops:627, Concurrency:5                                                                                                                                                                                                                         |ls.service_state, ls.timestamp     |137.4 KB|N/A |
└─IndexLookUp_7            |629602.92|640142 |root     |                                                                                     |time:494.4ms, loops:627, index_task: {total_time: 472.1ms, fetch_handle: 435.6ms, build: 82.7µs, wait: 36.4ms}, table_task: {total_time: 901.1ms, num: 35, concurrency: 5}                                                                                     |                                                                         |3.33 MB |N/A |
  ├─IndexRangeScan_5(Build)|629602.92|640142 |cop[tikv]|table:ls, index:timestamp(timestamp, organization_id, cluster_id, host_name, host_ip)|time:366.3ms, loops:631, cop_task: {num: 37, max: 77.8ms, min: 394.2µs, avg: 14.5ms, p95: 67.9ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 481ms, rpc_num: 37, rpc_time: 536.5ms, copr_cache_hit_ratio: 0.27, distsql_concurrency: 15}, tikv_task:|range:(2024-04-25 01:32:47.000,2024-04-30 01:32:47.000), keep order:false|N/A     |N/A |
  └─TableRowIDScan_6(Probe)|629602.92|640142 |cop[tikv]|table:ls                                                                             |time:631.5ms, loops:679, cop_task: {num: 46, max: 30ms, min: 1.58ms, avg: 15ms, p95: 27.6ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 584ms, tot_wait: 1ms, rpc_num: 46, rpc_time: 688.3ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, |keep order:false                                                         |N/A     |N/A |
------------------------------------------
explain analyze SELECT ls.service_state, ls.timestamp FROM ls WHERE ls.timestamp > "2024-04-25 01:32:47" and ls.timestamp < "2024-04-30 01:32:47"
id                   |estRows    |actRows |task     |access object|execution info                                                                                                                                                                                                                                                 |operator info                                                                                                                   |memory  |disk|
---------------------+-----------+--------+---------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------+--------+----+
Projection_4         |629602.92  |640142  |root     |             |time:814.8ms, loops:630, Concurrency:5                                                                                                                                                                                                                         |ls.service_state, ls.timestamp                                                            |128.9 KB|N/A |
└─TableReader_7      |629602.92  |640142  |root     |             |time:812.6ms, loops:630, cop_task: {num: 97, max: 556.6ms, min: 772µs, avg: 88.5ms, p95: 471.2ms, max_proc_keys: 554792, p95_proc_keys: 485769, tot_proc: 8.36s, tot_wait: 40ms, rpc_num: 97, rpc_time: 8.58s, copr_cache_hit_ratio: 0.52, distsql_concurrency:|data:Selection_6                                                                                                                |1.15 MB |N/A |
  └─Selection_6      |629602.92  |640142  |cop[tikv]|             |tikv_task:{proc max:555ms, min:1ms, avg: 280.4ms, p80:411ms, p95:478ms, iters:34498, tasks:97}, scan_detail: {total_process_keys: 9781457, total_process_keys_size: 2115995946, total_keys: 9781504, get_snapshot_time: 62.7ms, rocksdb: {key_skipped_count: 97|gt(ls.timestamp, 2024-04-25 01:32:47.000000), lt(ls.timestamp, 2024-04-30 01:32:47.000000)|N/A     |N/A |
    └─TableFullScan_5|34892667.00|34892667|cop[tikv]|table:ls     |tikv_task:{proc max:449ms, min:1ms, avg: 205.4ms, p80:305ms, p95:370ms, iters:34498, tasks:97}                                                                                                                                                                 |keep order:false                                                                                                                |N/A     |N/A |

EricSong · 2024 年5 月 17 日 05:48

这个是可以的，但是我不清楚现在走索引明显更优的情况下为什么不走索引，而且健康度也是100

不想干活 · 2024 年5 月 17 日 06:01

是不是有大量数据正在处理引起的呢

xfworld · 2024 年5 月 17 日 06:01

有机会升级下版本，升到 6.5.9 在试试