PolarDB:Transformação de expressões OR/IN em UNION ALL

Como funciona

Uma única varredura de índice pode usar condições AND para filtrar colunas indexadas, mas não condições OR que abrangem várias tabelas. Quando uma condição OR envolve colunas de duas ou mais tabelas, o otimizador do MySQL a trata como filtro pós-junção e recorre à varredura completa da tabela com hash join.

Por exemplo, a consulta a seguir não consegue usar os índices em t1.b ou t3.c1:

-- Before optimization: full table scan, high execution time
EXPLAIN ANALYZE SELECT * FROM t1, t3 WHERE t3.c1 > 98 OR t1.b <= 0;

-> Filter: ((t3.c1 > 98) or (t1.b <= 0)) ... (actual time=115.259..5416.434 ...)
    -> Inner hash join ...
        -> Table scan on t3 ...
        -> Hash
          -> Table scan on t1 ...

Essa consulta com OR equivale logicamente a duas consultas separadas mescladas com UNION ALL. Após a reescrita, cada ramificação pode usar seu respectivo índice:

-- Manually rewritten to UNION ALL: indexes used, execution time drops significantly
EXPLAIN ANALYZE
SELECT * FROM t1, t3 WHERE t1.b <= 0
UNION ALL
SELECT * FROM t1, t3 WHERE t3.c1 > 98 AND (t1.b > 0 OR (t1.b <= 0) IS NULL);

-> Append (actual time=58.272..302.546 ...)
    ...
    -> Index range scan on t3 using idx_c1 ...

O PolarDB automatiza essa reescrita. Durante a geração do plano, o otimizador avalia se transformar uma expressão OR em uma estrutura UNION ALL reduziria o custo da consulta. O sistema compara as estimativas de custo e executa o plano mais eficiente. Não é necessário modificar o SQL.

Aplicabilidade

• Série do produto: Cluster Edition, Standard Edition

• Versão do mecanismo: MySQL 8.0.2, versão de revisão 8.0.2.2.32 ou posterior

Este recurso está em lançamento canário. A ativação é padrão em nós somente leitura (RO), mas requer configurações adicionais em nós de leitura e gravação (RW). Para usar este recurso em nós RW, envie um ticket.

Ativar e configurar a otimização de reescrita de consultas

Controle o comportamento da otimização com os parâmetros a seguir.

Os nomes dos parâmetros diferem entre o console do PolarDB e a sessão de banco de dados:

• Console do PolarDB: Os parâmetros usam o prefixo loose_ para manter compatibilidade com arquivos de configuração do MySQL. Localize e modifique os parâmetros com o prefixo loose_.

• Sessão de banco de dados (linha de comando ou cliente): Omita o prefixo loose_ ao usar o comando SET.

Mantenha o loose_cbqt_cost_threshold no valor padrão. Definir esse parâmetro como 0 faz o otimizador tentar reescrever todas as consultas elegíveis. Isso aumenta o tempo de otimização para consultas simples e pode afetar o desempenho da aplicação.

Limitações

Este recurso aplica-se somente quando todas as condições a seguir são atendidas.

Limites gerais (aplicam-se a todos os tipos de transformação):

• A quantidade de itens na cláusula OR ou na lista IN não pode exceder 10.

• O bloco de consulta não pode conter subconsultas, cláusulas GROUP BY, funções de janela, cláusulas DISTINCT ou funções de agregação.

Transformação geral UNION ALL (JOINs de múltiplas tabelas):

• Cláusula OR:

  • A condição OR deve envolver duas ou mais tabelas.

  • Cada cláusula OR deve usar o padrão field=const ou permitir uso eficaz de índice.

    • field=const: field é uma coluna da tabela; const é um valor constante.

    • Uso eficaz de índice: por exemplo, em t1.f1=t2.f2, f1 deve ser prefixo de um índice em t1 e f2 deve ser prefixo de um índice em t2.

• Lista IN: Não há transformação em UNION ALL porque o método de acesso range é mais eficiente.

Transformação Top-K (consultas de tabela única ORDER BY ... LIMIT):

• Cláusula OR: Todas as condições OR devem aplicar-se à mesma coluna. Essa coluna e a coluna ORDER BY devem ser prefixos do mesmo índice. Por exemplo, com o índice (c2, c3), a consulta deve ser WHERE c2 = ... OR c2 = ... ORDER BY c3.

• Lista IN: A coluna na lista IN e a coluna ORDER BY devem ser prefixos do mesmo índice.

Exemplo: Verificar o efeito da otimização

Preparação de dados

-- Create and populate table t1
CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_a` (`a`)
) ENGINE=InnoDB;

INSERT INTO `t1` VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
-- Run repeatedly to increase data volume
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;

-- Create and populate table t3
CREATE TABLE `t3` (
  `c1` int(11) NOT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  KEY `idx_c1`(`c1`),
  KEY `idx_c2_c3` (`c2`,`c3`)
) ENGINE=InnoDB;

INSERT INTO `t3` VALUES (1,0,1,0),(2,0,2,0),(3,0,3,0),(4,0,4,0),(5,0,5,0),(6,0,6,0),(7,0,7,0),(8,0,8,0),(9,0,9,0),(10,0,10,0),(11,0,11,0),(12,0,12,0),(13,0,13,0),(14,0,14,0),(15,0,15,0),(16,0,16,0),(17,0,17,0),(18,0,18,0),(19,0,19,0),(20,0,20,0),(21,0,21,0),(22,0,22,0),(23,0,23,0),(24,0,24,0),(25,1,25,0),(26,1,26,0),(27,1,27,0),(28,1,28,0),(29,1,29,0),(30,1,30,0),(31,1,31,0),(32,1,32,0),(33,1,33,0),(34,1,34,0),(35,1,35,0),(36,1,36,0),(37,1,37,0),(38,1,38,0),(39,1,39,0),(40,1,40,0),(41,1,41,0),(42,1,42,0),(43,1,43,0),(44,1,44,0),(45,1,45,0),(46,1,46,0),(47,1,47,0),(48,1,48,0),(49,1,49,0),(50,1,50,1),(51,1,51,1),(52,1,52,1),(53,1,53,1),(54,1,54,1),(55,1,55,1),(56,1,56,1),(57,1,57,1),(58,1,58,1),(59,1,59,1),(60,1,60,1),(61,1,61,1),(62,1,62,1),(63,1,63,1),(64,1,64,1),(65,1,65,1),(66,1,66,1),(67,1,67,1),(68,1,68,1),(69,1,69,1),(70,1,70,1),(71,1,71,1),(72,1,72,1),(73,1,73,1),(74,1,74,1),(75,2,75,1),(76,2,76,1),(77,2,77,1),(78,2,78,1),(79,2,79,1),(80,2,80,1),(81,2,81,1),(82,2,82,1),(83,2,83,1),(84,2,84,1),(85,2,85,1),(86,2,86,1),(87,2,87,1),(88,2,88,1),(89,2,89,1),(90,2,90,1),(91,2,91,1),(92,2,92,1),(93,2,93,1),(94,2,94,1),(95,2,95,1),(96,2,96,1),(97,2,97,1),(98,2,98,1),(99,2,99,1),(100,2,100,1);

-- Run repeatedly to increase data volume
INSERT INTO t3 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t3;
INSERT INTO t3 SELECT * FROM t3;

-- Analyze the tables
ANALYZE TABLE t1, t3;

Cenário 1: Otimizar uma consulta JOIN de múltiplas tabelas

Este cenário demonstra como o otimizador reescreve uma condição OR que abrange duas tabelas para aproveitar os índices.

Sem otimização: O plano de execução recorre a hash join com varreduras completas nas tabelas t1 e t3. Nem o índice idx_a em t1.a nem o idx_c1 em t3.c1 são utilizados.

SET polar_optimizer_switch='or_expansion=off';
DESC SELECT * FROM t1, t3 WHERE t3.c1 > 98 OR t1.a < 5;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_a         | NULL | NULL    | NULL | 1280 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t3    | NULL       | ALL  | idx_c1        | NULL | NULL    | NULL | 6591 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

Com otimização: O plano de execução muda para UNION ALL. Cada ramificação usa seu respectivo índice (idx_a em t1.a e idx_c1 em t3.c1), produzindo o mesmo resultado de uma reescrita manual com UNION ALL.

SET polar_optimizer_switch='or_expansion=on';
SET cbqt_cost_threshold=1;  -- Lower the threshold to trigger the optimization
DESC SELECT * FROM t1, t3 WHERE t3.c1 > 98 OR t1.a < 5;

+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------------------------+
|  1 | PRIMARY     | t1    | NULL       | range | idx_a         | idx_a  | 5       | NULL |  256 |   100.00 | Using index condition; Using MRR           |
|  1 | PRIMARY     | t3    | NULL       | ALL   | NULL          | NULL   | NULL    | NULL | 6400 |   100.00 | Using join buffer (hash join)              |
|  2 | UNION       | t3    | NULL       | range | idx_c1        | idx_c1 | 4       | NULL |  128 |   100.00 | Using index condition; Using MRR           |
|  2 | UNION       | t1    | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |  640 |    66.67 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------------------------+

A presença de MRR (Multi-Range Read) na saída indica uma otimização do MySQL que agrupa leituras aleatórias de disco para melhorar a eficiência de E/S.

Cenário 2: Otimizar uma consulta Top-K (cláusula OR)

Neste cenário, o otimizador reescreve uma condição OR em UNION ALL e propaga a cláusula LIMIT para cada ramificação, eliminando a ordenação global de grande escala.

Sem otimização: O plano usa varredura de intervalo de índice para recuperar todas as linhas correspondentes a c2=2 ou c2=0. Em seguida, ordena todas as linhas antes de aplicar o limite. O tempo de execução é de cerca de 200 milissegundos.

SET polar_optimizer_switch='or_expansion=off';
DESC ANALYZE SELECT c2 FROM t3 WHERE (c2 = 2 OR c2 = 0) ORDER BY t3.c3 DESC LIMIT 5;

| -> Limit: 5 row(s)  (actual time=193.389..193.393 rows=5 loops=1)
    -> Sort: t3.c3 DESC, limit input to 5 row(s) per chunk  (cost=641.82 rows=3200) (actual time=193.386..193.388 rows=5 loops=1)
        -> Index range scan on t3 using idx_c2_c3, with index condition: ((t3.c2 = 2) or (t3.c2 = 0))  (actual time=0.348..187.455 rows=3200 loops=1)
|
1 row in set (0.20 sec)

Com otimização: O plano é reescrito para UNION ALL. O sistema aplica LIMIT 5 a cada ramificação (c2=2 e c2=0) separadamente via busca por índice e, depois, mescla os dois conjuntos de resultados de 5 linhas. Nenhuma ordenação global é necessária. O tempo de execução cai para cerca de 1 milissegundo.

SET polar_optimizer_switch='or_expansion=on';
SET cbqt_cost_threshold=1;
DESC ANALYZE SELECT c2 FROM t3 WHERE (c2 = 2 OR c2 = 0) ORDER BY t3.c3 DESC LIMIT 5;

| -> Limit: 5 row(s)  (actual time=1.249..1.254 rows=5 loops=1)
    -> Sort: derived_1_2.Name_exp_1 DESC, limit input to 5 row(s) per chunk  (actual time=0.104..0.106 rows=5 loops=1)
        -> Table scan on derived_1_2  (actual time=0.006..0.013 rows=10 loops=1)
            -> Union materialize  (actual time=1.246..1.249 rows=5 loops=1)
                -> Limit: 5 row(s)  (actual time=0.336..0.571 rows=5 loops=1)
                    -> Index lookup on t3 using idx_c2_c3 (c2=2; iterate backwards)  (cost=0.00 rows=5) (actual time=0.333..0.566 rows=5 loops=1)
                -> Limit: 5 row(s)  (actual time=0.215..0.431 rows=5 loops=1)
                    -> Index lookup on t3 using idx_c2_c3 (c2=0; iterate backwards)  (cost=0.00 rows=5) (actual time=0.214..0.427 rows=5 loops=1)
 |
1 row in set (0.01 sec)

Cenário 3: Otimizar uma consulta Top-K (lista IN)

Uma lista IN equivale logicamente a uma cláusula OR e suporta a mesma otimização Top-K.

Sem otimização: O plano utiliza varredura de intervalo de índice em todas as linhas correspondentes a c2 IN (2, 0) e realiza a ordenação antes de aplicar o limite. O tempo de execução gira em torno de 200 milissegundos.

SET polar_optimizer_switch='or_expansion=off';
DESC ANALYZE SELECT c2 FROM t3 WHERE c2 IN (2, 0) ORDER BY t3.c3 DESC LIMIT 5;

| -> Limit: 5 row(s)  (actual time=197.497..197.501 rows=5 loops=1)
    -> Sort: t3.c3 DESC, limit input to 5 row(s) per chunk  (cost=641.82 rows=3200) (actual time=197.494..197.496 rows=5 loops=1)
        -> Index range scan on t3 using idx_c2_c3, with index condition: (t3.c2 in (2,0))  (actual time=0.319..191.560 rows=3200 loops=1)
 |
1 row in set (0.20 sec)

Com otimização: O plano aplica LIMIT 5 por ramificação e mescla os resultados, eliminando a ordenação global. O tempo de execução cai para quase 0 milissegundos.

SET polar_optimizer_switch='or_expansion=on';
SET cbqt_cost_threshold=1;
DESC ANALYZE SELECT c2 FROM t3 WHERE c2 IN (2, 0) ORDER BY t3.c3 DESC LIMIT 5;

| -> Limit: 5 row(s)  (actual time=1.256..1.260 rows=5 loops=1)
    -> Sort: derived_1_2.Name_exp_1 DESC, limit input to 5 row(s) per chunk  (actual time=0.090..0.093 rows=5 loops=1)
        -> Table scan on derived_1_2  (actual time=0.005..0.012 rows=10 loops=1)
            -> Union materialize  (actual time=1.252..1.255 rows=5 loops=1)
                -> Limit: 5 row(s)  (actual time=0.259..0.545 rows=5 loops=1)
                    -> Index lookup on t3 using idx_c2_c3 (c2=2; iterate backwards)  (cost=0.00 rows=5) (actual time=0.256..0.540 rows=5 loops=1)
                -> Limit: 5 row(s)  (actual time=0.237..0.455 rows=5 loops=1)
                    -> Index lookup on t3 using idx_c2_c3 (c2=0; iterate backwards)  (cost=0.00 rows=5) (actual time=0.236..0.451 rows=5 loops=1)
|
1 row in set (0.00 sec)

Usar hints para controle manual

O otimizador aplica a expansão de OR automaticamente quando o custo estimado da consulta excede cbqt_cost_threshold. Use hints do otimizador para substituir esse comportamento em uma consulta específica sem alterar parâmetros globais ou de sessão.

Estão disponíveis os seguintes hints:

Hint	Efeito
NO_OR_EXPAND(@QB_NAME)	Desativa forçadamente a expansão de OR para o bloco de consulta especificado
OR_EXPAND(@QB_NAME)	Ativa forçadamente a expansão de OR para o bloco de consulta especificado
OR_EXPAND(@QB_NAME idx)	Expande forçadamente apenas a expressão OR na posição idx (baseada em zero) da cláusula WHERE

Desativar a expansão de OR para um bloco de consulta:

DESC SELECT /*+ NO_OR_EXPAND(@subq1) */ * FROM t1
WHERE EXISTS (
  SELECT /*+ QB_NAME(subq1) */ 1 FROM t3
  WHERE (t1.a = 1 OR t1.b = 2) AND t3.c1 < 5 AND t1.b = t3.c1
);

+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref        | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_a         | NULL   | NULL    | NULL       |  640 |    19.00 | Using where                 |
|  1 | SIMPLE      | t3    | NULL       | ref  | idx_c1        | idx_c1 | 4       | test2.t1.b |   64 |   100.00 | Using index; FirstMatch(t1) |
+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-----------------------------+

Ativar a expansão de OR para um bloco de consulta:

DESC SELECT /*+ OR_EXPAND(@subq1) */ * FROM t1
WHERE EXISTS (
  SELECT /*+ QB_NAME(subq1) */ 1 FROM t3
  WHERE (t3.c1 = 1 OR t1.b = 2) AND t3.c1 < 5 AND t1.b = t3.c1
);

+----+--------------------+-------+------------+------+---------------+--------+---------+-------+------+----------+------------------------------------+
| id | select_type        | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                              |
+----+--------------------+-------+------------+------+---------------+--------+---------+-------+------+----------+------------------------------------+
|  1 | PRIMARY            | t1    | NULL       | ALL  | NULL          | NULL   | NULL    | NULL  |  640 |   100.00 | Using where                        |
|  2 | DEPENDENT SUBQUERY | t3    | NULL       | ref  | idx_c1        | idx_c1 | 4       | const |   64 |   100.00 | Using where; Using index           |
|  3 | DEPENDENT UNION    | t3    | NULL       | ref  | idx_c1        | idx_c1 | 4       | const |   64 |   100.00 | Using index condition; Using index |
+----+--------------------+-------+------------+------+---------------+--------+---------+-------+------+----------+------------------------------------+

Expandir uma única expressão OR quando uma cláusula WHERE contém várias:

Use OR_EXPAND(@QB_NAME idx) para expandir apenas uma expressão. O parâmetro idx representa a posição baseada em zero da expressão alvo na cláusula WHERE. No exemplo a seguir, OR_EXPAND(@subq1 3) expande a expressão na posição 3, que é (t3.c2 = 1 OR t1.b = 2):

DESC FORMAT=TREE SELECT /*+ OR_EXPAND(@subq1 3) */ * FROM t1
WHERE EXISTS (
  SELECT /*+ QB_NAME(subq1) */ 1 FROM t3
  WHERE (t3.c2 = 999 OR t1.b = 999) AND t3.c1 < 5 AND t1.b = t3.c1 AND (t3.c2 = 1 OR t1.b = 2)
);

| -> Filter: exists(select #2)  (cost=64.75 rows=640)
    -> Table scan on t1  (cost=64.75 rows=640)
    -> Select #2 (subquery in condition; dependent)
        -> Limit: 1 row(s)
            -> Append
                -> Stream results
                    -> Filter: (t3.c2 = 1)  (cost=17.45 rows=32)
                        -> Index lookup on t3 using idx_c1 (c1=t1.b), with index condition: ((t1.b = 999) and (t3.c1 < 5))  (cost=17.45 rows=64)
                -> Stream results
                    -> Filter: (t3.c1 = 2)  (cost=0.51 rows=0)
                        -> Index lookup on t3 using idx_c2_c3 (c2=999), with index condition: ((t1.b = 2) and lnnvl((t3.c2 = 1)))  (cost=0.51 rows=1)