Push down join conditions to materialized derived tables - PolarDB

Lorsqu'une requête joint une table dérivée, l'optimiseur matérialise généralement l'intégralité de cette table avant d'appliquer les filtres JOIN ON. PolarDB peut également reporter les conditions de jointure éligibles dans la table dérivée avant sa matérialisation afin que seules les lignes correspondantes soient incluses. Cette approche réduit le volume de données que traitent les opérations de jointure suivantes et améliore ainsi les performances des requêtes.

Cette fonctionnalité diffère du report des conditions de jointure (JPP) . Elle dérive des conditions sur table unique en exploitant les équivalences de prédicats WHERE et les reporte dans les tables dérivées selon une règle heuristique qui améliore généralement les performances. Le JPP cible quant à lui les conditions multi-tables dans les prédicats ON et les transforme en sous-requêtes sur la base d'une estimation de coût, sans garantie d'amélioration des performances.

Fonctionnement

Prenons une requête où t1.a = 1 figure dans la clause WHERE et dt.x > t1.a dans la clause JOIN ON :

SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) dt ON dt.x > t1.a WHERE t1.a = 1;

L'optimiseur connaît la valeur t1.a = 1 au moment de l'exécution de la requête. Il substitue cette valeur dans le prédicat ON pour en déduire dt.x > 1, une condition qui ne dépend que de la table dérivée dt. Cette condition dérivée est ensuite reportée dans dt avant la matérialisation. La requête se comporte alors comme si elle était écrite ainsi :

SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2 WHERE x > 1) dt ON dt.x > t1.a WHERE t1.a = 1;

Sans cette fonctionnalité, toutes les lignes de t2 sont d'abord matérialisées, puis le filtre dt.x > 1 est appliqué ultérieurement.

Prérequis

Avant de commencer, assurez-vous que votre cluster utilise l'une des versions de moteur suivantes. Pour vérifier votre version, consultez Numéro de version de la requête.

MySQL 8.0.1 avec la version de révision 8.0.1.1.44 ou ultérieure
MySQL 8.0.2 avec la version de révision 8.0.2.2.25 ou ultérieure

Limitations

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

**La table dérivée contient une clause LIMIT.**

SELECT * FROM t1 LEFT JOIN (SELECT c, MAX(d) FROM t2 GROUP BY c LIMIT 2) dt
ON t1.a < dt.a AND t1.a = 1;

La condition fait référence à une sous-requête ou à une fonction non déterministe (telle que RAND()).
```
SELECT * FROM t1 LEFT JOIN (SELECT c, MAX(d) FROM t2 GROUP BY c) dt
ON t1.a < dt.a AND t1.a = RAND();
```

La condition fait référence à une procédure stockée ou à une fonction.

SELECT * FROM t1 LEFT JOIN (SELECT c, MAX(d) FROM t2 GROUP BY c) dt
ON t1.a < dt.a AND t1.a = f1();

Configuration de la fonctionnalité

Définissez le paramètre loose_join_cond_push_into_derived_mode en fonction de votre charge de travail. Pour plus d'instructions, consultez Définir les paramètres du cluster et du nœud.

Paramètre	Niveau	Valeurs valides	Description
`loose_join_cond_push_into_derived_mode`	Global	`REPLICA_ON` (par défaut), `ON`, `OFF`	`REPLICA_ON` : active le report uniquement sur les nœuds en lecture seule. `ON` : active la fonctionnalité de report des conditions. `OFF` : désactive le report.

Vérification de l'effet

Utilisez EXPLAIN FORMAT=TREE pour confirmer que les conditions sont reportées dans la table dérivée avant sa matérialisation.

Le report s'applique lorsque toutes les colonnes de l'expression de condition (ou leurs colonnes équivalentes) proviennent de la table dérivée matérialisée.

Créez des tables d'exemple :

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

Avec le report activé (loose_join_cond_push_into_derived_mode = ON) :

EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) dt ON dt.x > t1.a WHERE t1.a = 1;

-> Left hash join (no condition)
    -> Filter: (t1.a = 1)  (cost=0.55 rows=1)
        -> Table scan on t1  (cost=0.55 rows=3)
    -> Hash
        -> Table scan on dt
            -> Materialize
                -> Filter: (t2.x > 1)  (cost=0.45 rows=1)
                    -> Table scan on t2  (cost=0.45 rows=2)

La condition t2.x > 1 apparaît à l'intérieur de Materialize, ce qui confirme que le filtrage a lieu avant la matérialisation de la table dérivée.

Avec le report désactivé (loose_join_cond_push_into_derived_mode = OFF) :

EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) dt ON dt.x > t1.a WHERE t1.a=1;

-> Left hash join (no condition)
    -> Filter: (t1.a = 1)  (cost=0.55 rows=1)
        -> Table scan on t1  (cost=0.55 rows=3)
    -> Hash
        -> Filter: (dt.x > 1)
            -> Table scan on dt
                -> Materialize
                    -> Table scan on t2  (cost=0.45 rows=2)

Le nœud Filter: (dt.x > 1) apparaît en dehors de Materialize, ce qui signifie que toutes les lignes de t2 sont d'abord matérialisées, puis filtrées.