PolarDB:Génération de prédicats transitifs (descente de condition améliorée)

Prérequis

Cette fonctionnalité nécessite le moteur de base de données MySQL 8.0.2 en version de révision 8.0.2.2.23 ou ultérieure. Pour vérifier votre version actuelle, consultez Interroger la version du moteur.

Fonctionnement

L'optimiseur vise à générer autant de conditions sur table unique que possible plutôt qu'à énumérer toutes les relations entre variables par permutations. Lorsqu'il détecte une chaîne telle que v1.a > t1.a et t1.a > 1, il en déduit v1.a > 1 comme nouvelle condition sur table unique et l'applique avant l'exécution de la jointure. Plus l'optimiseur dispose de conditions sur table unique, plus il peut élaguer efficacement les données au niveau de l'analyse.

Cette capacité améliore les types de descente de condition suivants :

• **Descente de HAVING vers WHERE** : convertit les conditions HAVING applicables avant l'agrégation en conditions WHERE, réduisant ainsi le volume de données à agréger. Consultez Descente de condition (HAVING vers WHERE).

• **Descente de condition WHERE vers une table dérivée** : propage les conditions WHERE de la requête externe dans une table dérivée pour un filtrage anticipé. Consultez Descente de condition (descente de condition WHERE vers table dérivée).

• **Descente de clause WHERE vers une sous-requête IN** : transfère les conditions WHERE de la requête externe dans une sous-requête IN afin d'améliorer l'efficacité de son exécution. Consultez Descente de condition (descente de clause WHERE vers sous-requête IN).

• Descente de condition de jointure vers une table dérivée matérialisée : applique directement les conditions de jointure aux tables dérivées matérialisées, limitant ainsi les analyses de données inutiles. Consultez Descente de condition (descente de condition de jointure vers table dérivée matérialisée).

Activer la génération de prédicats transitifs

Définissez le paramètre loose_predicate_deduce_mode au niveau du cluster. Pour les étapes de configuration, consultez Configurer les paramètres de cluster et de nœud.

• OFF (par défaut) : désactive la génération de prédicats transitifs.

• ON : active la génération de prédicats transitifs sur tous les nœuds.

• REPLICA_ON : active la génération de prédicats transitifs uniquement sur les nœuds en lecture seule.

Scénarios pris en charge

-- Original
SELECT * FROM t1, v1 WHERE v1.a > t1.a AND t1.a > 1;
-- Derived: v1.a > 1 is added as a new single-table condition
SELECT * FROM t1, v1 WHERE v1.a > t1.a AND t1.a > 1 AND v1.a > 1;

-- Original
SELECT * FROM t1, v1 WHERE v1.b < t1.c AND t1.c < t2.c AND t2.c = 1;
-- Derived: propagates through the chain to add v1.b < 1 and t1.c < 1
SELECT * FROM t1, v1 WHERE v1.b < t1.c AND t1.c < t2.c AND t2.c = 1 AND v1.b < 1 AND t1.c < 1;

-- Original
SELECT * FROM t1, v2 WHERE v2.c1 < t1.c2 + t1.c1 AND t1.c1 + t1.c2 < 2;
-- t1.c2 + t1.c1 and t1.c1 + t1.c2 are recognized as equivalent; v2.c1 < 2 is derived
SELECT * FROM t1, v2 WHERE v2.c1 < t1.c2 + t1.c1 AND t1.c1 + t1.c2 < 2 AND v2.c1 < 2;

-- Original
SELECT * FROM t3, v3 WHERE v3.c2 = t3.c2 AND t3.c2 LIKE "%00%";
-- The LIKE condition propagates through the equality to v3.c2
SELECT * FROM t3, v3 WHERE v3.c2 = t3.c2 AND t3.c2 LIKE "%00%" AND v3.c2 LIKE "%00%";

-- Original
SELECT * FROM t1, v2 WHERE v2.c1 = t1.c2 AND t1.c2 IN (1,5,7);
-- The IN list propagates through the equality to v2.c1
SELECT * FROM t1, v2 WHERE v2.c1 = t1.c2 AND t1.c2 IN (1,5,7) AND v2.c1 IN (1,5,7);

-- Original
SELECT * FROM t1 GROUP BY a, b, c HAVING MAX(b) > 20;
-- Derived: filters rows at scan time instead of after aggregation
SELECT * FROM t1 WHERE b > 20 GROUP BY a, b, c;

-- Original
SELECT * FROM t1 GROUP BY a, b, c HAVING MIN(b) < 20;
-- Derived
SELECT * FROM t1 WHERE b < 20 GROUP BY a, b, c;

Limitations

La génération de prédicats transitifs ne s'applique pas lorsque l'une des conditions suivantes est remplie :

Incompatibilité de type dans la chaîne de dérivation

La dérivation est ignorée si toutes les conditions suivantes sont réunies :

• La même variable intermédiaire apparaît dans deux comparaisons.

• Les deux colonnes comparées ont des types différents (par exemple, INT et VARCHAR).

• Forcer la dérivation modifierait la sémantique originale de la requête.

Dans l'exemple ci-dessous, t1.c2 (INT) est comparé à la fois avec v2.c1 (INT) et v2.c2 (VARCHAR). Comme les méthodes de comparaison diffèrent, la dérivation n'est pas appliquée :

CREATE TABLE t1(c1 INT, c2 INT);
CREATE TABLE t2(c1 INT, c2 VARCHAR(64));
CREATE VIEW v2 AS SELECT * FROM t2;

-- Derivation is NOT applied because t1.c2 is compared against different types
SELECT * FROM v2, t1 WHERE v2.c1 > t1.c2 AND t1.c2 > v2.c2;

Fonctions d'agrégation multiples associées à MIN/MAX

La dérivation MIN/MAX est ignorée si toutes les conditions suivantes sont réunies :

• La requête contient un MIN() ou un MAX() dans la clause HAVING.

• Au moins une autre fonction d'agrégation (telle que AVG) est également présente.

• L'ajout d'une condition WHERE modifierait le résultat de l'autre fonction d'agrégation.

Dans l'exemple suivant, l'ajout de WHERE b < 20 exclurait des lignes du calcul de AVG(c), ce qui en changerait le résultat :

-- Derivation is NOT applied because AVG(c) is present alongside MIN(b)
SELECT a, MIN(b), AVG(c) FROM t1 GROUP BY a HAVING MIN(b) < 20;

Vérification avec EXPLAIN

Utilisez EXPLAIN FORMAT = TREE pour confirmer que les conditions dérivées apparaissent bien dans le plan d'exécution.

CREATE VIEW v1 AS
SELECT c1, c2, MAX(c3) AS max_c3, AVG(c4) AS avg_c4
FROM t2
GROUP BY c1, c2
HAVING max_c3 > 10;

EXPLAIN FORMAT = TREE SELECT * FROM v1, t1 WHERE v1.c1 >= t1.c2 AND t1.c2 = v1.c2;

-> Inner hash join (t1.c2 = v1.c2)
    -> Table scan on t1  (cost=0.18 rows=10)
    -> Hash
        -> Table scan on v1
            -> Materialize
                -> Filter: (max_c3 > 10)
                    -> Table scan on <temporary>
                        -> Aggregate using temporary table
                            -> Filter: (t2.c1 >= t2.c2)  (cost=0.75 rows=2)
                                -> Table scan on t2  (cost=0.75 rows=5)

SELECT a, b, ee
FROM (
    SELECT b, a, MIN(c) AS ee
    FROM t1
    GROUP BY a, b
) dt
GROUP BY a, b
HAVING MIN(ee) < 20;

SELECT a, b, ee
FROM (
    SELECT b, a, MIN(c) AS ee
    FROM t1
    WHERE c < 20
    GROUP BY a, b
) dt
GROUP BY a, b;

-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Table scan on dt
            -> Materialize
                -> Table scan on <temporary>
                    -> Aggregate using temporary table
                        -> Filter: (t1.c < 20)  (cost=*** rows=***)
                            -> Table scan on t1  (cost=*** rows=***)

Étapes suivantes

• Descente de condition (HAVING vers WHERE)

• Descente de condition (descente de condition WHERE vers table dérivée)

• Descente de condition (descente de clause WHERE vers sous-requête IN)

• Descente de condition (descente de condition de jointure vers table dérivée matérialisée)