Seleção lenta em tabela vazia após exclusão

Eu estava enfrentando esse problema em uma instância de produção e tentei uma recriação local em pequena escala e consegui.

Eu tenho uma tabela de teste simples

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Estou usando a versão Mysql 8.0.26com um innodbmecanismo de armazenamento

Preenchi esta tabela com cerca de 4 milhões de linhas aleatórias. Selecionar 5000 linhas desta tabela leva cerca de 50 ms. Em seguida, apaguei todas as linhas usandoDELETE FROM test;

Após excluir todas as linhas, se eu executar SELECT * FROM test LIMIT 5000;essa consulta, ela agora leva mais de 1 minuto para ser executada. Quando a consulta select finalmente termina de ser executada, ela retorna 0 linhas (como esperado).

Antes de prosseguir, quero esclarecer que entendo que executar um optimize tableresolverá o problema, mas estou interessado em entender o que realmente está acontecendo nos bastidores. Aqui estão alguns pensamentos que tive

1. Depois de cerca de uma hora, a consulta se torna instantânea novamente. Isso me leva a acreditar que algum tipo de coleta de lixo ou limpeza está acontecendo por baixo dos panos. Não consigo encontrar nada sobre isso nos documentos.
2. A consulta fica significativamente mais lenta após a exclusão. Como não estou usando nenhuma cláusula não indexada na minha consulta select, ela foi resolvida em cerca de 50 ms antes da exclusão, então, de alguma forma, é 1500x mais lenta após a exclusão.
3. Eu pesquisei o conceito de "registros fantasmas", no entanto, todos os resultados que consigo encontrar estão falando apenas sobre o SQL Server em vez do Mysql, o que me leva a acreditar que o conceito de "registros fantasmas" pode não ser uma coisa no Mysql. Não consigo encontrar nenhuma menção nos documentos do Mysql sobre isso.

Eu tentei o log de consulta lenta e esperava ver um monte de dados escaneados, mesmo que tivessem sido excluídos (registros fantasmas), mas além da duração, não consigo ver nada estranho ou inesperado lá

# Time: 2025-02-11T10:29:24.510606Z
# User@Host: root[root] @  [172.17.0.1]  Id: 20978
# Query_time: 69.680895  Lock_time: 0.000135 Rows_sent: 0  Rows_examined: 0 Thread_id: 20978 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 130 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2025-02-11T10:28:14.829711Z End: 2025-02-11T10:29:24.510606Z
SET timestamp=1739269694;
select * from test
LIMIT 0, 5000;

Alguém pode explicar em detalhes o que exatamente está acontecendo aqui? Idealmente, as duas coisas que eu adoraria saber, se possível, são:

1. O que está realmente acontecendo?
2. É possível ver/diagnosticar esse problema olhando algumas estatísticas internas do Mysql? Algo como o status/information_schema/performance_schema do mecanismo innodb, por exemplo. Basicamente, algo que eu poderia olhar e dizer que uma tabela está tendo esse problema sem saber que uma exclusão acabou de ser executada

Aqui está a explicação do select -SELECT * FROM test LIMIT 5000

Antes de excluir (rápido 50 ms)

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'test', NULL, 'index', NULL, 'index2', '1022', NULL, '6958223', '100.00', 'Using index'

Após a exclusão (lento 69s) - Corri analyze table testpara atualizar as estatísticas antes de executar novamente a seleção e a explicação

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'test', NULL, 'index', NULL, 'index2', '1022', NULL, '1', '100.00', 'Using index'