Como usar o Hybrid Plan para acelerar consultas de tabelas largas - PolarDB: Central de Documentação do Alibaba Cloud

Consultas em tabelas largas que filtram poucas colunas, mas retornam todas elas, causam amplificação de leitura. O índice columnstore filtra com eficiência, mas buscar cada coluna projetada por meio dele gera alto custo. O índice row store evita essa amplificação, porém perde a velocidade da filtragem colunar. O Hybrid Plan combina ambos os índices em uma única execução de consulta para aproveitar o melhor desempenho de cada abordagem.

Como funciona

O Hybrid Plan divide a execução em duas etapas:

O otimizador usa o In-Memory Column Index (IMCI) para filtrar linhas e recuperar suas chaves primárias.
O sistema usa essas chaves primárias para buscar os dados completos das linhas no índice primário do InnoDB.

Essa abordagem elimina a amplificação de leitura: o IMCI executa a varredura colunar rápida e o InnoDB retorna apenas as linhas correspondentes completas.

Aplicabilidade

Edição do cluster: PolarDB for MySQL Enterprise Edition
Versões compatíveis:
- PolarDB for MySQL 8.0.1, revisão 8.0.1.1.37 ou posterior
- PolarDB for MySQL 8.0.2, revisão 8.0.2.2.23.1 ou posterior

Casos de uso

Consultas em tabelas largas que retornam 15 ou mais colunas.

Limitações

As tabelas que fornecem colunas de saída para a consulta devem ter uma chave primária definida explicitamente.

Nota

Se o Hybrid Plan não conseguir acelerar uma instrução SQL lenta devido a limitações como o tipo de tabela, entre em contato conosco para obter assistência.

Parâmetros

Dois parâmetros controlam o Hybrid Plan. Eles atuam em níveis diferentes e seguem uma ordem clara de prioridade:

imci_enable_hybrid_plan — controla o recurso globalmente. Quando definido como ON, o otimizador avalia cada consulta e aplica automaticamente o Hybrid Plan ao estimar benefício de custo.
imci_optimizer_switch — dica por consulta. Definir force_hybrid_index_search=ON em uma dica força o uso do Hybrid Plan para essa instrução, independentemente da estimativa de custo do otimizador.

Prioridade: A dica por consulta (force_hybrid_index_search=ON) substitui a decisão automática do otimizador. Este só decide quando não há dica presente e imci_enable_hybrid_plan está definido como ON.

Parâmetro	Padrão	Descrição
`imci_enable_hybrid_plan`	ON	Habilita o Hybrid Plan globalmente. Defina como OFF para desativar o recurso.
`imci_optimizer_switch`	`force_hybrid_index_search=OFF`	Substituição por consulta. Configure `force_hybrid_index_search=ON` em uma dica para forçar o Hybrid Plan em uma instrução específica.

Ative e verifique o Hybrid Plan

Esta seção usa SELECT * FROM t1; como exemplo.

Etapa 1: Conecte-se ao cluster

Conecte-se ao cluster do PolarDB. Para mais detalhes, consulte Conectar-se a um cluster de banco de dados.

Etapa 2: Ative o recurso

Defina imci_enable_hybrid_plan como ON. Assim, o otimizador aplicará automaticamente o Hybrid Plan às consultas elegíveis em tabelas largas.

SET imci_enable_hybrid_plan=ON;

Etapa 3: Force uma consulta específica a usar o Hybrid Plan (opcional)

Se o otimizador não selecionar o Hybrid Plan para uma consulta (por exemplo, quando a estimativa de custo favorecer outro plano), adicione uma dica para substituir a decisão:

SELECT /*+ SET_VAR(imci_optimizer_switch='force_hybrid_index_search=ON') */ * FROM t1;

Etapa 4: Verifique se o Hybrid Plan está ativo

Execute EXPLAIN na consulta para confirmar o uso do Hybrid Plan. Quando ativo, o plano de execução inclui o operador HybridIndexSearch.

EXPLAIN SELECT /*+ SET_VAR(imci_optimizer_switch='force_hybrid_index_search=ON') */ * FROM t1;

Saída esperada:

+----+-------------------------+------+---------------------------------------------------------------+
| ID | Operator                | Name | Extra Info                                                    |
+----+-------------------------+------+---------------------------------------------------------------+
|  1 | Select Statement        |      | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
|  2 | └─HybridIndexSearch     |      | Used table: t1                                                |
|  3 |   └─Compute Scalar      |      |                                                               |
|  4 |     └─Table Scan        | t1   |                                                               |
+----+-------------------------+------+---------------------------------------------------------------+
4 rows in set (0.02 sec)

O operador HybridIndexSearch na linha 2 confirma que o Hybrid Plan está ativo. A linha 1 mostra o contexto de execução do IMCI; a linha 4 confirma a varredura da tabela InnoDB para recuperação completa das linhas.

Teste de desempenho

O teste a seguir usa o conjunto de dados OnTime do ClickHouse. A tabela OnTime tem 109 colunas, sendo representativa como tabela larga. O esquema da tabela foi modificado para incluir uma chave primária explícita. O esquema modificado é o seguinte:

CREATE TABLE `ontime` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Year` year(4) DEFAULT NULL,
  `Quarter` tinyint(4) DEFAULT NULL,
  `Month` tinyint(4) DEFAULT NULL,
  `DayofMonth` tinyint(4) DEFAULT NULL,
  `DayOfWeek` tinyint(4) DEFAULT NULL,
  `FlightDate` date DEFAULT NULL,
  `UniqueCarrier` char(7) DEFAULT NULL,
  `AirlineID` int(11) DEFAULT NULL,
  `Carrier` char(2) DEFAULT NULL,
  `TailNum` varchar(50) DEFAULT NULL,
  `FlightNum` varchar(10) DEFAULT NULL,
  `OriginAirportID` int(11) DEFAULT NULL,
  `OriginAirportSeqID` int(11) DEFAULT NULL,
  `OriginCityMarketID` int(11) DEFAULT NULL,
  `Origin` char(5) DEFAULT NULL,
  `OriginCityName` varchar(100) DEFAULT NULL,
  `OriginState` char(2) DEFAULT NULL,
  `OriginStateFips` varchar(10) DEFAULT NULL,
  `OriginStateName` varchar(100) DEFAULT NULL,
  `OriginWac` int(11) DEFAULT NULL,
  `DestAirportID` int(11) DEFAULT NULL,
  `DestAirportSeqID` int(11) DEFAULT NULL,
  `DestCityMarketID` int(11) DEFAULT NULL,
  `Dest` char(5) DEFAULT NULL,
  `DestCityName` varchar(100) DEFAULT NULL,
  `DestState` char(2) DEFAULT NULL,
  `DestStateFips` varchar(10) DEFAULT NULL,
  `DestStateName` varchar(100) DEFAULT NULL,
  `DestWac` int(11) DEFAULT NULL,
  `CRSDepTime` int(11) DEFAULT NULL,
  `DepTime` int(11) DEFAULT NULL,
  `DepDelay` int(11) DEFAULT NULL,
  `DepDelayMinutes` int(11) DEFAULT NULL,
  `DepDel15` int(11) DEFAULT NULL,
  `DepartureDelayGroups` int(11) DEFAULT NULL,
  `DepTimeBlk` varchar(20) DEFAULT NULL,
  `TaxiOut` int(11) DEFAULT NULL,
  `WheelsOff` int(11) DEFAULT NULL,
  `WheelsOn` int(11) DEFAULT NULL,
  `TaxiIn` int(11) DEFAULT NULL,
  `CRSArrTime` int(11) DEFAULT NULL,
  `ArrTime` int(11) DEFAULT NULL,
  `ArrDelay` int(11) DEFAULT NULL,
  `ArrDelayMinutes` int(11) DEFAULT NULL,
  `ArrDel15` int(11) DEFAULT NULL,
  `ArrivalDelayGroups` int(11) DEFAULT NULL,
  `ArrTimeBlk` varchar(20) DEFAULT NULL,
  `Cancelled` tinyint(4) DEFAULT NULL,
  `CancellationCode` char(1) DEFAULT NULL,
  `Diverted` tinyint(4) DEFAULT NULL,
  `CRSElapsedTime` int(11) DEFAULT NULL,
  `ActualElapsedTime` int(11) DEFAULT NULL,
  `AirTime` int(11) DEFAULT NULL,
  `Flights` int(11) DEFAULT NULL,
  `Distance` int(11) DEFAULT NULL,
  `DistanceGroup` tinyint(4) DEFAULT NULL,
  `CarrierDelay` int(11) DEFAULT NULL,
  `WeatherDelay` int(11) DEFAULT NULL,
  `NASDelay` int(11) DEFAULT NULL,
  `SecurityDelay` int(11) DEFAULT NULL,
  `LateAircraftDelay` int(11) DEFAULT NULL,
  `FirstDepTime` varchar(10) DEFAULT NULL,
  `TotalAddGTime` varchar(10) DEFAULT NULL,
  `LongestAddGTime` varchar(10) DEFAULT NULL,
  `DivAirportLandings` varchar(10) DEFAULT NULL,
  `DivReachedDest` varchar(10) DEFAULT NULL,
  `DivActualElapsedTime` varchar(10) DEFAULT NULL,
  `DivArrDelay` varchar(10) DEFAULT NULL,
  `DivDistance` varchar(10) DEFAULT NULL,
  `Div1Airport` varchar(10) DEFAULT NULL,
  `Div1AirportID` int(11) DEFAULT NULL,
  `Div1AirportSeqID` int(11) DEFAULT NULL,
  `Div1WheelsOn` varchar(10) DEFAULT NULL,
  `Div1TotalGTime` varchar(10) DEFAULT NULL,
  `Div1LongestGTime` varchar(10) DEFAULT NULL,
  `Div1WheelsOff` varchar(10) DEFAULT NULL,
  `Div1TailNum` varchar(10) DEFAULT NULL,
  `Div2Airport` varchar(10) DEFAULT NULL,
  `Div2AirportID` int(11) DEFAULT NULL,
  `Div2AirportSeqID` int(11) DEFAULT NULL,
  `Div2WheelsOn` varchar(10) DEFAULT NULL,
  `Div2TotalGTime` varchar(10) DEFAULT NULL,
  `Div2LongestGTime` varchar(10) DEFAULT NULL,
  `Div2WheelsOff` varchar(10) DEFAULT NULL,
  `Div2TailNum` varchar(10) DEFAULT NULL,
  `Div3Airport` varchar(10) DEFAULT NULL,
  `Div3AirportID` int(11) DEFAULT NULL,
  `Div3AirportSeqID` int(11) DEFAULT NULL,
  `Div3WheelsOn` varchar(10) DEFAULT NULL,
  `Div3TotalGTime` varchar(10) DEFAULT NULL,
  `Div3LongestGTime` varchar(10) DEFAULT NULL,
  `Div3WheelsOff` varchar(10) DEFAULT NULL,
  `Div3TailNum` varchar(10) DEFAULT NULL,
  `Div4Airport` varchar(10) DEFAULT NULL,
  `Div4AirportID` int(11) DEFAULT NULL,
  `Div4AirportSeqID` int(11) DEFAULT NULL,
  `Div4WheelsOn` varchar(10) DEFAULT NULL,
  `Div4TotalGTime` varchar(10) DEFAULT NULL,
  `Div4LongestGTime` varchar(10) DEFAULT NULL,
  `Div4WheelsOff` varchar(10) DEFAULT NULL,
  `Div4TailNum` varchar(10) DEFAULT NULL,
  `Div5Airport` varchar(10) DEFAULT NULL,
  `Div5AirportID` int(11) DEFAULT NULL,
  `Div5AirportSeqID` int(11) DEFAULT NULL,
  `Div5WheelsOn` varchar(10) DEFAULT NULL,
  `Div5TotalGTime` varchar(10) DEFAULT NULL,
  `Div5LongestGTime` varchar(10) DEFAULT NULL,
  `Div5WheelsOff` varchar(10) DEFAULT NULL,
  `Div5TailNum` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=58592318 DEFAULT CHARSET=latin1 COMMENT='columnar=1'

Consulta de teste:

SELECT * FROM ontime ORDER BY ArrTime LIMIT 1000;

Todas as consultas foram executadas a partir de um início a frio.

Estratégia de execução	Tempo de consulta
Apenas row store	232,48 segundos
Apenas índice columnstore (IMCI)	2,56 segundos
Hybrid Plan	0,33 segundos

Para essa carga de trabalho, o Hybrid Plan é 7,8 vezes mais rápido que o uso exclusivo de columnstore e 704 vezes mais rápido que o uso exclusivo de row store.