Push down WHERE clauses to IN subqueries - PolarDB - Centre de documentation Alibaba Cloud

Lorsqu'une requête associe une condition WHERE externe à une sous-requête IN utilisant GROUP BY, PolarDB for MySQL peut reporter la condition externe directement dans la sous-requête avant sa matérialisation. Cette optimisation réduit le nombre de lignes matérialisées et améliore les performances des requêtes.

Prérequis

Avant de commencer, assurez-vous que votre cluster exécute l'une des versions de moteur suivantes. Pour vérifier la version, consultez Interroger la version du moteur.

MySQL 8.0.1, version de révision 8.0.1.1.42 ou ultérieure
MySQL 8.0.2, version de révision 8.0.2.19 ou ultérieure

Fonctionnement

Si une requête correspond au modèle suivant, l'optimiseur reporte la condition WHERE externe dans la sous-requête avant la matérialisation :

SELECT ... FROM t
WHERE <condition on t.col>
  AND (t.col, ...) IN (SELECT col, ... FROM t2 GROUP BY ...);

Chaque ligne du jeu de résultats devant satisfaire la condition t.col = subquery_col, la contrainte sur t.col implique une contrainte équivalente sur subquery_col. L'optimiseur réécrit alors la sous-requête pour appliquer cette contrainte plus tôt, ce qui diminue le nombre de lignes matérialisées et analysées.

Exemple de réécriture : La requête

SELECT * FROM t WHERE a < 5 AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c);

est réécrite en interne comme suit :

SELECT * FROM t WHERE a < 5 AND (a, b) IN (SELECT c, MAX(d) FROM t2 WHERE c < 5 GROUP BY c);

La destination de la condition reportée dépend de la présence de la colonne corrélée dans la clause GROUP BY :

Position de la colonne	Reportée vers
Dans `GROUP BY`	Clause `WHERE` de la sous-requête
Hors de `GROUP BY` (par exemple, `MAX(col)`)	Clause `HAVING` de la sous-requête

Limites

Le report de condition n'est pas pris en charge dans les cas suivants :

**Clause LIMIT dans la sous-requête** — Le résultat de la sous-requête étant déjà borné, l'application du filtre après LIMIT modifierait la sémantique de la requête.
```
SELECT * FROM t WHERE a > 1 AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c LIMIT 2);
```
Condition externe non déterministe ou référence à la sous-requête — La colonne de la condition externe fait référence à une sous-requête ou est non déterministe (par exemple, RAND()), ce qui peut produire des résultats différents pour les mêmes conditions d'entrée.
```
SELECT * FROM t WHERE a > 5*RAND() AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c);
```
Fonction stockée dans la condition externe — Les fonctions stockées complexes peuvent ne pas bénéficier d'exécutions répétées et ne sont donc pas éligibles au report.
```
CREATE FUNCTION f1() RETURNS INT
BEGIN
...
END;

SELECT * FROM t WHERE a > f1() AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c);
```

Activer le report de condition

Configurer le paramètre

Définissez le paramètre loose_subquery_cond_pushdown_mode au niveau Global. Pour plus d'informations, consultez Configurer les paramètres de cluster et de nœud.

Paramètre	Niveau	Description
`loose_subquery_cond_pushdown_mode`	Global	Contrôle le report des conditions des clauses `WHERE` vers les sous-requêtes `IN`. Valeurs valides : `OFF` (par défaut, fonctionnalité désactivée), `ON` (active le report de condition), `REPLICA_ON` (activé uniquement sur les nœuds en lecture seule).

Utiliser les hints d'optimiseur

Pour contrôler le report pour des requêtes individuelles sans modifier loose_subquery_cond_pushdown_mode, utilisez des hints d'optimiseur avec QB_NAME afin de nommer le bloc de requête cible :

SUBQUERY_CONDITION_PUSHDOWN(@<qb_name>) — Force le report pour le bloc de requête nommé.
NO_SUBQUERY_CONDITION_PUSHDOWN(@<qb_name>) — Désactive le report pour le bloc de requête nommé.

CREATE TABLE t1 (a INT, b INT, c INT, d INT);
CREATE TABLE t2 (e INT, f INT, g INT);

-- Suppress pushdown for the subquery named subq1
SELECT /*+ NO_SUBQUERY_CONDITION_PUSHDOWN(@subq1) */ * FROM t1
WHERE t1.c < 25
  AND (t1.a, t1.c) IN (SELECT /*+ QB_NAME(subq1) */ t2.e, MAX(t2.g) FROM t2 WHERE t2.e < 5 GROUP BY t2.e);

-- Force pushdown for the subquery named subq1
SELECT /*+ SUBQUERY_CONDITION_PUSHDOWN(@subq1) */ * FROM t1
WHERE t1.c < 25
  AND (t1.a, t1.c) IN (SELECT /*+ QB_NAME(subq1) */ t2.e, MAX(t2.g) FROM t2 WHERE t2.e < 5 GROUP BY t2.e);

Exemples

Les exemples ci-dessous utilisent les tables suivantes :

CREATE TABLE t1 (a INT, b INT, c INT, d INT);
CREATE TABLE t2 (e INT, f INT, g INT);

Exemple 1 : Condition reportée vers HAVING

Exécutez EXPLAIN FORMAT=TREE pour comparer les plans d'exécution avant et après l'activation du report de condition :

EXPLAIN FORMAT=TREE SELECT * FROM t1
WHERE t1.c < 25
  AND (t1.a, t1.c) IN (SELECT t2.e, MAX(t2.g) FROM t2 WHERE t2.e < 5 GROUP BY t2.e);

Sortie :

-- Before enabling loose_subquery_cond_pushdown_mode
-> Filter: ((t1.c < 25) and <in_optimizer>((t1.a,t1.c),(t1.a,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (e=t1.a, MAX(t2.g)=t1.c)
                    -> Materialize with deduplication
                        -> Table scan on <temporary>
                            -> Aggregate using temporary table
                                -> Filter: (t2.e < 5)  (cost=*** rows=***)
                                    -> Table scan on t2  (cost=*** rows=***)

-- After enabling loose_subquery_cond_pushdown_mode
-> Filter: ((t1.c < 25) and <in_optimizer>((t1.a,t1.c),(t1.a,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (e=t1.a, MAX(t2.g)=t1.c)
                    -> Materialize with deduplication
                        -> Filter: (max(t2.g) < 25)
                            -> Table scan on <temporary>
                                -> Aggregate using temporary table
                                    -> Filter: (t2.e < 5)  (cost=*** rows=***)
                                        -> Table scan on t2  (cost=*** rows=***)

Remarque

Dans la sous-requête, t1.c équivaut à MAX(t2.g). Ainsi, la condition externe t1.c < 25 implique MAX(t2.g) < 25. Comme g n'apparaît pas dans GROUP BY, l'optimiseur reporte la condition sous forme de filtre HAVING, visible via la mention Filter: (max(t2.g) < 25) lors de l'étape de matérialisation.

Exemple 2 : Condition reportée vers WHERE

EXPLAIN FORMAT=TREE SELECT * FROM t1
WHERE ((t1.a < 2 OR t1.a = 5) AND t1.b > 3)
  AND (t1.a, t1.b, t1.c) IN (
    SELECT t2.e, t2.f, MAX(t2.g)
    FROM t2
    WHERE t2.e < 5
    GROUP BY t2.e, t2.f
  );

Sortie :

-- Before enabling loose_subquery_cond_pushdown_mode
-> Filter: ((t1.b > 3) and ((t1.a < 2) or (t1.a = 5)) and <in_optimizer>((t1.a,t1.b,t1.c),(t1.a,t1.b,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.b = `<materialized_subquery>`.f) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (e=t1.a, f=t1.b, MAX(t2.g)=t1.c)
                    -> Materialize with deduplication
                        -> Table scan on <temporary>
                            -> Aggregate using temporary table
                                -> Filter: (t2.e < 5)  (cost=*** rows=***)
                                    -> Table scan on t2  (cost=*** rows=***)

-- After enabling loose_subquery_cond_pushdown_mode
-> Filter: ((t1.b > 3) and ((t1.a < 2) or (t1.a = 5)) and <in_optimizer>((t1.a,t1.b,t1.c),(t1.a,t1.b,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.b = `<materialized_subquery>`.f) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (e=t1.a, f=t1.b, MAX(t2.g)=t1.c)
                    -> Materialize with deduplication
                        -> Table scan on <temporary>
                            -> Aggregate using temporary table
                                -> Filter: ((t2.e < 5) and (t2.f > 3) and ((t2.e < 2) or (t2.e = 5)))  (cost=*** rows=***)
                                    -> Table scan on t2  (cost=*** rows=***)

Remarque

Les colonnes t1.a et t1.b correspondent respectivement à t2.e et t2.f, qui figurent toutes deux dans GROUP BY. L'optimiseur reporte la condition composée ((t1.a < 2 OR t1.a = 5) AND t1.b > 3) directement dans la clause WHERE de la sous-requête sous la forme ((t2.e < 2 OR t2.e = 5) AND t2.f > 3).

Règle clé : Une condition reportée devient un filtre WHERE ou HAVING selon l'emplacement de la colonne corrélée. Les colonnes présentes dans GROUP BY génèrent un filtre WHERE (Exemple 2) ; celles situées hors de GROUP BY, comme les résultats d'agrégation, produisent un filtre HAVING (Exemple 1).