O que é Limit Offset pushdown e quais são seus limites - PolarDB: Central de Documentação do Alibaba Cloud

Consultas paginadas perdem desempenho à medida que o número da página aumenta, pois o modelo de execução padrão do MySQL transmite todas as linhas candidatas para a camada SQL, que as descarta imediatamente com base no valor de OFFSET. O PolarDB for MySQL reduz essa sobrecarga ao delegar a avaliação de LIMIT OFFSET para a camada do mecanismo de armazenamento, filtrando as linhas desnecessárias antes da transmissão.

Como funciona

No MySQL padrão, a cláusula LIMIT é avaliada na camada SQL. O mecanismo de armazenamento envia todas as linhas candidatas para essa camada, que ignora linhas conforme o valor de OFFSET e retorna o conjunto de resultados final. Em consultas paginadas com valores altos de OFFSET, o mecanismo de armazenamento transmite um grande volume de linhas descartadas imediatamente, e o custo cresce linearmente conforme os números das páginas aumentam.

Com o LIMIT OFFSET pushdown ativado, o PolarDB filtra as linhas na camada do mecanismo de armazenamento antes que elas cheguem à camada SQL. Esse recurso atua em dois cenários principais:

Ausência de condições WHERE na camada SQL: Quando os predicados são totalmente delegados por meio do recurso de pushdown de predicados detach_range_condition, não resta nenhuma filtragem na camada SQL. Assim, o LIMIT OFFSET pode ser delegado diretamente.
Acesso por índice secundário com buscas na tabela: Se uma consulta utilizar um índice secundário e também precisar de colunas da tabela primária, o LIMIT OFFSET pushdown evitará buscas desnecessárias na tabela dentro do mecanismo de armazenamento, impedindo a recuperação de linhas que seriam descartadas de qualquer forma.

Pré-requisitos

Antes de ativar este recurso, verifique se o seu cluster PolarDB for MySQL atende aos seguintes requisitos de versão:

Versão 8.0, revisão 8.0.1.1.16 ou posterior
Versão 8.0, revisão 8.0.2.2.0 ou posterior

Para verificar a versão do seu cluster, consulte Consultar a versão do mecanismo.

Limitações

O LIMIT OFFSET pushdown aplica-se apenas quando o valor de OFFSET é superior a 512. Para valores pequenos de OFFSET, a quantidade de linhas ignoradas é baixa o suficiente para que a sobrecarga de coordenação do pushdown supere o benefício em relação ao caminho de execução padrão.

Para remover essa restrição, defina ignore_polar_optimizer_rule como ON. Para obter instruções, consulte Especificar parâmetros de cluster e nó.

Parâmetro	Nível	Padrão	Descrição
`ignore_polar_optimizer_rule`	Global e sessão	OFF	Controla a restrição de limiar do OFFSET. Defina como `ON` para desativar o limiar e permitir o pushdown para qualquer valor de OFFSET.

Ativar ou desativar o LIMIT OFFSET pushdown

O LIMIT OFFSET pushdown vem ativado por padrão. Utilize o parâmetro loose_optimizer_switch para alterar seu estado. Para instruções sobre como modificar parâmetros, consulte Especificar parâmetros de cluster e nó.

Parâmetro	Nível	Variável	Padrão	Descrição
`loose_optimizer_switch`	Global e sessão	`limit_offset_pushdown`	ON	Ativa ou desativa o LIMIT OFFSET pushdown.
`loose_optimizer_switch`	Global e sessão	`detach_range_condition`	ON	Ativa ou desativa o pushdown de predicados. O LIMIT OFFSET pushdown depende deste recurso quando há condições WHERE.

Verificar se o pushdown está ativo

Execute EXPLAIN na sua consulta e verifique o campo Extra. Quando o LIMIT OFFSET pushdown está ativo, a saída inclui Using limit-offset pushdown. Se essa string estiver ausente, a consulta não atendeu às condições para pushdown. Consulte Quando o pushdown não se aplica para conhecer os motivos mais comuns.

Os exemplos a seguir utilizam o esquema TPC-H.

Sem condições de predicado (Q1)

Varredura completa da tabela sem cláusula WHERE. A tabela primária é acessada diretamente. Como não resta nenhuma filtragem na camada SQL, o LIMIT OFFSET é delegado.

EXPLAIN
SELECT *
FROM lineitem
LIMIT 10000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 59440464
     filtered: 100.00
        Extra: Using limit-offset pushdown

O valor Extra: Using limit-offset pushdown confirma que a filtragem de linhas ocorre na camada do mecanismo de armazenamento, e não na camada SQL.

Condições de intervalo na chave primária (Q2)

Quando as condições WHERE são baseadas na chave primária, o recurso de pushdown de predicados (detach_range_condition) as remove da camada SQL, permitindo a delegação do LIMIT OFFSET.

EXPLAIN SELECT * FROM lineitem WHERE l_orderkey > 10 AND l_orderkey < 60000000 LIMIT 10000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: PRIMARY,i_l_orderkey,i_l_orderkey_quantity
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 29720232
     filtered: 100.00
        Extra: Using limit-offset pushdown

Índice secundário com buscas na tabela (Q3)

Ao utilizar um índice secundário em uma consulta que requer colunas da tabela primária, o LIMIT OFFSET pushdown evita buscas desnecessárias na tabela primária para linhas fora da janela de resultados.

EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
          key: i_l_suppkey_partkey
      key_len: 5
          ref: NULL
         rows: 11123302
     filtered: 100.00
        Extra: Using limit-offset pushdown

ORDER BY com índice

Se uma cláusula ORDER BY for satisfeita por um índice, os predicados são removidos na camada SQL e o LIMIT OFFSET é delegado.

EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: range
possible_keys: i_l_partkey,i_l_suppkey_partkey
          key: i_l_suppkey_partkey
      key_len: 5
          ref: NULL
         rows: 11123302
     filtered: 100.00
        Extra: Using limit-offset pushdown

Quando o pushdown não se aplica

O LIMIT OFFSET pushdown exige que nenhum trabalho de filtragem reste na camada SQL. Caso Extra não exiba Using limit-offset pushdown, verifique se alguma das condições abaixo se aplica:

Condição	Motivo para o pushdown ser ignorado
Valor de OFFSET igual ou inferior a 512	Ignorar poucas linhas é mais eficiente sem a sobrecarga do pushdown. Defina `ignore_polar_optimizer_rule` como `ON` para substituir esse comportamento.
As condições WHERE não podem ser totalmente delegadas ao mecanismo de armazenamento	Filtragens remanescentes na camada SQL impedem a delegação do LIMIT OFFSET. Isso ocorre quando a consulta usa filesort ou um ORDER BY não indexado.

Melhorias de desempenho

A figura a seguir mostra as melhorias no tempo de consulta medidas com um conjunto de dados TPC-H no fator de escala 10, comparando Q1, Q2 e Q3 com e sem o LIMIT OFFSET pushdown ativado.

Performance improvements