very slow query of segments metedata

[lukoou3]

Please provide a detailed title (e.g. "Broker crashes when using TopN query with Bound filter" instead of just "Broker crashes").

use mysql.

Affected Version

26.0.0.

Description

T have exactly the same issue whth #9899 when segments count > 100, 000, . SQL SELECT payloadFROM druid_segments WHERE used=true query vrey slow.

update segments metedata very slow,

I found that the DataSourcesSnapshot.fromUsedSegments method is slower

log:

tail -f coordinator-overlord.log  grep "SqlSegmentsMetadataManager - Polled and found"
2024-05-06T03:12:25,822 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,521 segments in the database
2024-05-06T03:15:59,718 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,522 segments in the database
2024-05-06T03:19:41,505 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,543 segments in the database
2024-05-06T03:23:29,951 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,557 segments in the database
2024-05-06T03:27:21,508 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,561 segments in the database
2024-05-06T03:31:11,409 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,621 segments in the database
2024-05-06T03:34:48,165 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,622 segments in the database
2024-05-06T03:38:22,952 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,648 segments in the database
2024-05-06T03:42:02,943 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,648 segments in the database
2024-05-06T03:45:43,469 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,653 segments in the database
2024-05-06T03:49:20,109 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,655 segments in the database
2024-05-06T03:53:06,230 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,668 segments in the database
2024-05-06T03:56:52,150 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,700 segments in the database
2024-05-06T04:00:41,506 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 145,752 segments in the database
2024-05-06T04:04:23,593 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 146,157 segments in the database
2024-05-06T04:08:30,852 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 146,328 segments in the database
2024-05-06T04:12:10,367 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 146,332 segments in the database
2024-05-06T04:15:48,570 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 146,334 segments in the database
2024-05-06T04:19:27,530 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 146,355 segments in the database
2024-05-06T04:23:10,528 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 146,368 segments in the database
2024-05-06T04:26:58,152 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 146,372 segments in the database
2024-05-06T04:30:32,644 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 146,432 segments in the database
2024-05-06T04:34:05,419 INFO [org.apache.druid.metadata.SqlSegmentsMetadataManager-Exec--0] org.apache.druid.metadata.SqlSegmentsMetadataManager - Polled and found 146,434 segments in the database