Comment réécrire les expressions SQL OR/IN en UNION ALL - PolarDB

PolarDB for MySQL réécrit automatiquement les expressions OR/IN éligibles sous une structure UNION ALL. Cette optimisation permet aux requêtes d'exploiter efficacement les index sans aucune modification de votre code SQL.

Fonctionnement

Une analyse d'index unique peut exploiter des conditions AND pour filtrer sur des colonnes indexées, mais pas les conditions OR qui s'étendent sur plusieurs tables. Lorsqu'une condition OR implique des colonnes de deux tables ou plus, l'optimiseur MySQL la traite comme un filtre post-jointure et se rabat sur une analyse complète de table avec une jointure par hachage.

Par exemple, la requête suivante ne peut pas utiliser les index sur t1.b ni sur 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 ...

Cette requête avec OR est logiquement équivalente à deux requêtes distinctes fusionnées via UNION ALL. Une fois réécrite, chaque branche peut utiliser son propre index :

-- 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 ...

PolarDB automatise cette réécriture. Lors de la génération du plan, l'optimiseur évalue si la transformation d'une expression OR en structure UNION ALL réduit le coût de la requête. Il compare les deux estimations de coût et exécute le plan le moins coûteux. Aucune modification SQL n'est nécessaire.

Applicabilité

Séries de produits : Cluster Edition, Standard Edition
Version du moteur : MySQL 8.0.2, version de révision 8.0.2.2.32 ou ultérieure

Cette fonctionnalité est en cours de déploiement progressif. Elle est activée par défaut sur les nœuds en lecture seule (RO) et nécessite une configuration supplémentaire sur les nœuds en lecture-écriture (RW). Pour utiliser cette fonctionnalité sur des nœuds RW, soumettez un ticket.

Activer et configurer l'optimisation de réécriture de requêtes

Contrôlez le comportement d'optimisation à l'aide des paramètres suivants.

Les noms des paramètres diffèrent entre la console PolarDB et une session de base de données :

Console PolarDB : Les paramètres utilisent le préfixe loose_ pour assurer la compatibilité avec les fichiers de configuration MySQL. Recherchez et modifiez les paramètres portant le préfixe loose_.
Session de base de données (ligne de commande ou client) : Supprimez le préfixe loose_ lors de l'utilisation de la commande SET.

Paramètre	Niveau	Description
`loose_polar_optimizer_switch`	Global/Session	Commutateur principal pour l'expansion OR. Définissez `or_expansion=on` pour activer (par défaut) ou `or_expansion=off` pour désactiver.
`loose_cbqt_cost_threshold`	Global/Session	Seuil de coût déclenchant la réécriture. L'optimiseur tente une réécriture uniquement lorsque le coût estimé de la requête d'origine (visible dans `EXPLAIN`) dépasse cette valeur. Plage de valeurs : 0–18 446 744 073 709 551 615. Valeur par défaut : 100000.

Conservez loose_cbqt_cost_threshold à sa valeur par défaut. Le définir à 0 force l'optimiseur à tenter de réécrire toutes les requêtes éligibles, ce qui augmente le temps d'optimisation pour les requêtes simples et peut affecter les performances métier.

Limites

Cette fonctionnalité s'applique uniquement lorsque toutes les conditions suivantes sont remplies.

Limites générales (s'appliquent à tous les types de transformation) :

Le nombre d'éléments dans la clause OR ou la liste IN ne doit pas dépasser 10.
Le bloc de requête ne doit contenir ni sous-requêtes, ni clauses GROUP BY, ni fonctions fenêtrées, ni clauses DISTINCT, ni fonctions d'agrégation.

Transformation UNION ALL générale (jointures multi-tables) :

Clause OR :
- La condition OR doit impliquer au moins deux tables.
- Chaque clause OR doit suivre le modèle field=const ou pouvoir utiliser un index efficacement.
  - field=const : field correspond à une colonne de table ; const est une valeur constante.
  - Utilisation efficace de l'index : par exemple, dans t1.f1=t2.f2, f1 doit être un préfixe d'un index sur t1 et f2 doit être un préfixe d'un index sur t2.
Liste IN : Non transformée en UNION ALL car la méthode d'accès range est plus optimale.

Transformation Top-K (requêtes ORDER BY ... LIMIT sur table unique) :

Clause OR : Toutes les conditions OR doivent s'appliquer à la même colonne. Cette colonne ainsi que la colonne ORDER BY doivent toutes deux constituer un préfixe du même index. Par exemple, avec l'index (c2, c3), la requête doit être WHERE c2 = ... OR c2 = ... ORDER BY c3.
Liste IN : La colonne de la liste IN et la colonne ORDER BY doivent toutes deux constituer un préfixe du même index.

Exemple : Vérifier l'effet de l'optimisation

Préparation des données

-- 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;

Scénario 1 : Optimiser une requête JOIN multi-tables

Ce scénario illustre comment l'optimiseur réécrit une condition OR couvrant deux tables afin d'exploiter les index.

Sans optimisation : Le plan d'exécution se rabat sur une jointure par hachage avec des analyses complètes de table sur t1 et t3. Ni idx_a sur t1.a ni idx_c1 sur t3.c1 n'est utilisé.

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) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

Avec optimisation : Le plan d'exécution bascule vers UNION ALL. Chaque branche utilise son index respectif (idx_a sur t1.a et idx_c1 sur t3.c1), produisant le même résultat qu'une réécriture manuelle en 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) |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------------------------+

La mention MRR (Multi-Range Read) dans la sortie indique une optimisation MySQL qui regroupe les lectures disque aléatoires pour améliorer l'efficacité des E/S.

Scénario 2 : Optimiser une requête Top-K (clause OR)

Ce scénario montre comment l'optimiseur réécrit une condition OR en UNION ALL et propage la clause LIMIT vers chaque branche, éliminant ainsi un tri global coûteux.

Sans optimisation : Le plan utilise une analyse de plage d'index pour récupérer toutes les lignes correspondant à c2=2 ou c2=0, puis trie l'ensemble des lignes avant d'appliquer la limite. Le temps d'exécution avoisine 200 millisecondes.

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)

Avec optimisation : Le plan est réécrit en UNION ALL. Il applique LIMIT 5 séparément à chaque branche (c2=2 et c2=0) via une recherche par index, puis fusionne les deux jeux de résultats de 5 lignes. Aucun tri global n'est requis. Le temps d'exécution chute à environ 1 milliseconde.

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)

Scénario 3 : Optimiser une requête Top-K (liste IN)

Une liste IN est logiquement équivalente à une clause OR et bénéficie de la même optimisation Top-K.

Sans optimisation : Le plan effectue une analyse de plage d'index sur toutes les lignes correspondant à c2 IN (2, 0), puis procède au tri avant d'appliquer la limite. Le temps d'exécution est d'environ 200 millisecondes.

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)

Avec optimisation : Le plan applique LIMIT 5 par branche et fusionne les résultats, supprimant le tri global. Le temps d'exécution tombe à près de 0 milliseconde.

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)

Utiliser des hints pour un contrôle manuel

L'optimiseur applique l'expansion OR automatiquement lorsque le coût estimé de la requête dépasse cbqt_cost_threshold. Utilisez des hints d'optimiseur pour outrepasser ce comportement sur une requête spécifique sans modifier les paramètres globaux ou de session.

Les hints suivants sont disponibles :

Hint	Effet
`NO_OR_EXPAND(@QB_NAME)`	Désactive de force l'expansion OR pour le bloc de requête spécifié
`OR_EXPAND(@QB_NAME)`	Active de force l'expansion OR pour le bloc de requête spécifié
`OR_EXPAND(@QB_NAME idx)`	Force l'expansion uniquement de l'expression `OR` située à la position `idx` (base 0) dans la clause `WHERE`

Désactiver l'expansion OR pour un bloc de requête :

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) |
+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-----------------------------+

Activer l'expansion OR pour un bloc de requête :

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 |
+----+--------------------+-------+------------+------+---------------+--------+---------+-------+------+----------+------------------------------------+

Étendre une seule expression OR lorsqu'une clause WHERE en contient plusieurs :

Utilisez OR_EXPAND(@QB_NAME idx) lorsque vous souhaitez étendre une seule expression. Le paramètre idx correspond à la position (base 0) de l'expression cible dans la clause WHERE. Dans l'exemple suivant, OR_EXPAND(@subq1 3) étend l'expression située à la position 3, soit (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)