AnalyticDB:Cache de paginação (otimização de desempenho para consultas paginadas profundas)

Configure um banco de dados de cache

Antes de usar o recurso de cache de paginação, especifique um banco de dados para armazenar as tabelas de cache temporárias das consultas paginadas. Caso nenhum banco de dados seja especificado, as tabelas de cache temporárias serão armazenadas no banco de dados interno conectado. Ao ativar o recurso de cache de paginação, o sistema cria automaticamente uma tabela de cache temporária.

Por exemplo, defina o banco de dados paging_cache como banco de dados de cache. Outros bancos de dados também podem ser especificados.

SET ADB_CONFIG PAGING_CACHE_SCHEMA=paging_cache;

Ative o recurso de cache de paginação para consultas paginadas

Quando várias consultas paginadas compartilham o mesmo padrão SQL, adicione uma dica às instruções SQL para melhorar o desempenho. Na primeira execução de uma consulta paginada contendo uma dica, o sistema cria uma tabela de cache temporária para armazenar os resultados. Nas consultas subsequentes com o mesmo padrão SQL, adicione a mesma dica às instruções. Assim, o sistema lê os dados da tabela de cache temporária sem acessar o banco de dados repetidamente.

Consultar informações sobre tabelas de cache

Consulte informações sobre todas as tabelas de cache de consultas paginadas no cluster atual, incluindo ID de paginação, tamanho do cache e status do cache.

SELECT * FROM INFORMATION_SCHEMA.KEPLER_PAGING_CACHE_STATUS_MERGED;

Especifique o número máximo de tabelas de cache

Defina o número máximo de tabelas de cache permitidas no cluster atual. Valor padrão: 32.

SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=32;

Se você tentar criar uma tabela de cache quando o total de tabelas exceder o limite, ocorrerá um erro. Mensagem de erro de exemplo:

Paging cache count exceeds the limit. Please clean up unused caches or increase the related parameter using SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=xxx.

Limpe os dados de cache desnecessários ou aumente o número máximo de tabelas de cache conforme indicado na mensagem de erro.

Especifique um período de validade para uma tabela de cache

Configure um período de validade para a tabela de cache, em segundos. Após o término desse período, a tabela torna-se inválida. Quando consultas paginadas subsequentes compartilharem o mesmo padrão SQL, o sistema acessará o banco de dados e atualizará a tabela de cache. Geralmente, esse recurso é utilizado em cenários de controle de concorrência de relatórios.

Por exemplo, utilize a dica /*paging_cache_enabled=true, paging_cache_validity_interval=300*/ para manter a tabela de cache válida por 300 segundos após a criação. Instrução de exemplo:

/*paging_cache_enabled=true, paging_cache_validity_interval=300*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

Limpar os dados de cache de consultas paginadas

Ao utilizar o recurso de cache de paginação, os resultados das consultas são armazenados no espaço de armazenamento quente do AnalyticDB for MySQL. Se determinados dados em cache não forem mais necessários, limpe-os para liberar espaço de armazenamento.

Limpeza manual

• Dados de cache de consultas paginadas especificados por padrão SQL

  Utilize a dica /*paging_cache_enabled=true, invalidate_paging_cache=true*/ para limpar os dados de cache associados a um padrão SQL específico.

  Instrução de exemplo:

  /*paging_cache_enabled=true,invalidate_paging_cache=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

• Dados de cache de consultas paginadas especificados pelo parâmetro paging_id

  Limpe os dados de cache das consultas paginadas identificadas pelo parâmetro paging_id.

  Instrução de exemplo:

  CLEAN_PAGING_CACHE paging123;

  Nota

  Para obter informações sobre como recuperar o valor do parâmetro paging_id, consulte a seção "Consultar informações sobre tabelas de cache" deste tópico.

Limpeza automática

Defina um tempo de expiração do cache para limpar automaticamente os dados de consultas paginadas não acessados dentro do intervalo especificado. Valor padrão: 3600. Unidade: segundos. Por padrão, dados não acessados em 1 hora são removidos automaticamente.

SET ADB_CONFIG PAGING_CACHE_EXPIRATION_TIME=3600;

Alternativa: Paginação por conjunto de chaves

Se o seu cenário de negócio suportar navegação baseada em cursor (por exemplo, navegar para a próxima página ou página anterior sem pular para uma página arbitrária), utilize a paginação por conjunto de chaves como alternativa à paginação tradicional baseada em OFFSET. Em vez de usar OFFSET para ignorar linhas, a paginação por conjunto de chaves emprega uma cláusula WHERE para filtrar linhas com base no último valor recuperado. Isso evita a sobrecarga de ordenação global e transferência de dados entre nós em páginas profundas.

Por exemplo, suponha que a coluna id seja a chave primária e o último valor de id na página atual seja 1000. Use a seguinte instrução SQL para consultar a próxima página:

-- Traditional deep pagination (slow)
SELECT * FROM t_order ORDER BY id LIMIT 1000, 100;

-- Keyset pagination (fast, recommended)
SELECT * FROM t_order WHERE id > 1000 ORDER BY id LIMIT 100;

O método de paginação por conjunto de chaves recupera apenas o próximo lote de linhas diretamente do índice, sem ordenar ou ignorar um grande número de registros. Isso elimina a degradação de desempenho causada pela paginação profunda, mantendo o desempenho da consulta constante independentemente da profundidade da paginação.

Erros comuns e solução de problemas

Falha na preparação do cache de paginação e cache indisponível

Mensagem de erro:

Paging cache prepare failed, and cache is not available. Please use /*paging_cache_enabled=true,invalidate_paging_cache=true*/ to clean the unavailable cache or set a specific pagingId with /*paging_id=xxx*/ to gen a new cache. Note that the old and new cache data may be inconsistent.

Causa: Ao usar o recurso de cache de paginação para consultar dados, podem ocorrer exceções, como reinicialização ou dimensionamento de nós. Se uma consulta paginada atingir uma tabela de cache cuja criação falhou, o servidor não acessará o banco de dados nem recriará automaticamente a tabela de cache, lançando um erro.

Solução: Para garantir a consistência dos dados, realize as seguintes operações: Em cenários de exportação de dados, recomenda-se limpar os dados exportados e os dados de cache indisponíveis e, em seguida, reiniciar a consulta paginada para recriar a tabela de cache. Em outros cenários, limpe os dados de cache indisponíveis ou especifique um novo valor para o parâmetro paging_id e reinicie a consulta paginada para recriar a tabela de cache.

-- General paged query without using the paging cache feature
SELECT * FROM lineitem ORDER BY l_orderkey,l_linenumber LIMIT 0,100000;

-- Paged query by using the paging cache feature (ORDER BY eliminated in the data export scenario)
/*paging_cache_enabled=true*/ SELECT * FROM lineitem LIMIT 0,100000;