Semi-join overview and usage - PolarDB - Centre de documentation Alibaba Cloud

Utilisez une semi-jointure pour optimiser les sous-requêtes, réduire le nombre d'exécutions et améliorer les performances. Cette rubrique présente les principes fondamentaux des semi-jointures et leur utilisation.

Prérequis

Votre cluster PolarDB doit être un cluster PolarDB for MySQL 8,0 exécutant l'une des versions de révision suivantes :

8.0.1.0.5 ou ultérieure.
8.0.2.2.7 ou ultérieure.

Pour vérifier la version de votre cluster, consultez la section Interroger la version du moteur.

Contexte

MySQL 5.6.5 a introduit l'optimisation par semi-jointure. Lorsqu'une semi-jointure trouve une correspondance pour une ligne de la table externe dans la table interne, elle renvoie la ligne de la table externe. Même s'il existe plusieurs correspondances dans la table interne, la ligne de la table externe n'est renvoyée qu'une seule fois. Cette approche est plus efficace qu'une sous-requête standard, qui peut réexécuter la sous-requête pour chaque ligne éligible de la table externe. Une semi-jointure améliore les performances en convertissant la sous-requête en jointure. L'optimiseur traite alors conjointement les tables internes et externes, ce qui réduit considérablement le temps d'exécution de la requête.

Stratégies

Une semi-jointure utilise l'une des stratégies suivantes :

Stratégie DuplicateWeedout

Cette stratégie crée une table temporaire contenant des ID uniques basés sur le row id afin d'éliminer les doublons.

explain select * from t1 where a in (select a from t11);
id  select_type  table  partitions  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1 SIMPLE  t11  NULL  ALL  NULL  NULL  NULL  0  0.00  Start temporary
1 SIMPLE  t1  NULL  ALL  NULL  NULL  NULL  3  33.33  Using where; End temporary; Using join buffer (hash join)
Warnings:
Note  1003  /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`  from `test`.`t1` semi join (`test`.`t11`) where (`test`.`t1`.`a` = `test`.`t11`.`a`)

Stratégie de matérialisation

Cette stratégie matérialise les nested tables d'une sous-requête dans une table temporaire. L'optimiseur élimine ensuite les doublons en recherchant ou en analysant cette table matérialisée lors de la jointure avec la table externe.

explain select * from t1 where a in (select a from t11);
id  select_type table partitions  type  possible_keys key key_len ref rows  filtered  Extra
1 SIMPLE  <subquery2> NULL  ALL NULL  NULL  NULL  NULL 0.00  NULL
1 SIMPLE  t1  NULL  ALL NULL  NULL  NULL  NULL  3 33.33 Using where; Using join buffer (hash join)
2 MATERIALIZED  t11 NULL  ALL NULL  NULL  NULL  NULL  0 0.00  NULL
Warnings:
Note  1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t11`) where (`test`.`t1`.`a` = `<subquery2>`.`a`)

Stratégie Firstmatch

Cette stratégie effectue une analyse séquentielle. Après avoir trouvé la première ligne correspondante, elle passe immédiatement à la ligne suivante de la table externe, éliminant ainsi les doublons.

Lorsque vous exécutez une instruction EXPLAIN pour afficher le plan de requête, la mention FirstMatch(t1) dans la colonne Extra indique que l'optimiseur a choisi la stratégie de semi-jointure FirstMatch.

explain select * from t1 where a in (select a from t11);
id  select_type  table  partitions  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1 SIMPLE  t1  NULL  ALL NULL  NULL  NULL  NULL  3 100.00  NULL
1 SIMPLE  t11  NULL  ALL NULL  NULL  NULL  NULL  0 0.00  Using where; FirstMatch(t1); Using join buffer (hash join)
Warnings:
Note  1003  /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t11`) where (`test`.`t11`.`a` = `test`.`t1`.`a`)

Stratégie LooseScan

Cette stratégie regroupe la table interne selon un index. Elle joint ensuite chaque groupe à la table externe pour trouver les conditions de jointure correspondantes. Si une correspondance est trouvée, la requête renvoie la ligne de la table externe et l'analyse passe au groupe suivant de la table interne. Cette méthode évite de traiter les lignes en double de la table interne.

Dans la sortie EXPLAIN, la mention Using index; LooseScan dans la colonne Extra pour la table t3 indique que la sous-requête a été optimisée en semi-jointure utilisant la stratégie LooseScan.
```
explain select count(a) from t2 where a in ( SELECT  a FROM t3);
id  select_type table partitions  type  possible_keys key key_len ref rows   filtered  Extra
1 SIMPLE  t3  NULL  index  a a 5 NULL  30000 3.33  Using where; Using index; LooseScan
1 SIMPLE  t2  NULL  ref  a a 5 test.t3.a 1 100.00  Using index
Warnings:
Note  1003 /* select#1 */ select count(`test`.`t2`.`a`) AS `count(a)` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`)
```

Syntaxe

Une sous-requête IN ou EXISTS déclenche généralement une semi-jointure.

SELECT * FROM Employee WHERE DeptName IN (   SELECT DeptName   FROM Dept )

EXISTS

SELECT * FROM Employee WHERE EXISTS (   SELECT 1   FROM Dept   WHERE Employee.DeptName = Dept.DeptName )

Performances des semi-jointures parallèles

Pour les requêtes utilisant une stratégie de semi-jointure, PolarDB propose une accélération parallèle pour toutes les stratégies de semi-jointure. Il divise une tâche de semi-jointure en un ensemble de sous-tâches qui s'exécutent simultanément selon un modèle multithread. Cela améliore la capacité de déduplication et augmente considérablement les performances des requêtes. À partir de PolarDB 8.0.2.2.7, la requête parallèle multiphase est prise en charge pour la stratégie de matérialisation, ce qui booste encore davantage les performances des semi-jointures. L'exemple Q20 suivant illustre cette fonctionnalité.

SELECT
   s_name,
   s_address 
FROM
   supplier,
   nation 
WHERE
   s_suppkey IN 
   (
      SELECT
         ps_suppkey 
      FROM
         partsupp 
      WHERE
         ps_partkey IN 
         (
            SELECT
               p_partkey 
            FROM
               part 
            WHERE
               p_name LIKE '[COLOR]%' 
         )
         AND ps_availqty > ( 
         SELECT
            0.5 * SUM(l_quantity) 
         FROM
            lineitem 
         WHERE
            l_partkey = ps_partkey 
            AND l_suppkey = ps_suppkey 
            AND l_shipdate >= date('[DATE]') 
            AND l_shipdate < date('[DATE]') + interval '1' year ) 
   )
   AND s_nationkey = n_nationkey 
   AND n_name = '[NATION]' 
ORDER BY
   s_name;

Dans cet exemple, la sous-requête et la requête externe s'exécutent toutes deux en parallèle avec un degré de parallélisme (DOP) de 32. La sous-requête génère d'abord une table matérialisée en parallèle. La requête externe s'exécute également en parallèle. Cette approche exploite pleinement la puissance de traitement du CPU pour maximiser le parallélisme des requêtes. Ce qui suit montre la capacité de traitement parallèle multiphase dans un scénario de données chaudes avec un jeu de données TPC-H à l'échelle de 100 Go.

Remarque

L'implémentation TPC-H présentée dans cette rubrique est basée sur le benchmark TPC-H. Ces résultats de test ne peuvent pas être comparés aux résultats publiés du benchmark TPC-H, car les tests ne satisfont pas pleinement à toutes les exigences de TPC-H.

Le plan de requête parallèle est le suivant :

 -> Sort: <temporary>.s_name  (cost=5014616.15 rows=100942)
    -> Stream results
        -> Nested loop inner join  (cost=127689.96 rows=100942)
            -> Gather (slice: 2; workers: 64; nodes: 2)  (cost=6187.68 rows=100928)
                -> Nested loop inner join  (cost=1052.43 rows=1577)
                    -> Filter: (nation.N_NAME = 'KENYA')  (cost=2.29 rows=3)
                        -> Table scan on nation  (cost=2.29 rows=25)
                    -> Parallel index lookup on supplier using SUPPLIER_FK1 (S_NATIONKEY=nation.N_NATIONKEY), with index condition: (supplier.S_SUPPKEY is not null), with parallel partitions: 863  (cost=381.79 rows=631)
            -> Single-row index lookup on <subquery2> using <auto_distinct_key> (ps_suppkey=supplier.S_SUPPKEY)
                -> Materialize with deduplication
                    -> Gather (slice: 1; workers: 64; nodes: 2)  (cost=487376.70 rows=8142336)
                        -> Nested loop inner join  (cost=73888.70 rows=127224)
                            -> Filter: (part.P_NAME like 'lime%')  (cost=31271.54 rows=33159)
                                -> Parallel table scan on part, with parallel partitions: 6244  (cost=31271.54 rows=298459)
                            -> Filter: (partsupp.PS_AVAILQTY > (select #4))  (cost=0.94 rows=4)
                                -> Index lookup on partsupp using PRIMARY (PS_PARTKEY=part.P_PARTKEY)  (cost=0.94 rows=4)
                                -> Select #4 (subquery in condition; dependent)
                                    -> Aggregate: sum(lineitem.L_QUANTITY)
                                        -> Filter: ((lineitem.L_SHIPDATE >= DATE'1994-01-01') and (lineitem.L_SHIPDATE < <cache>((DATE'1994-01-01' + interval '1' year))))  (cost=4.05 rows=1)
                                            -> Index lookup on lineitem using LINEITEM_FK2 (L_PARTKEY=partsupp.PS_PARTKEY, L_SUPPKEY=partsupp.PS_SUPPKEY)  (cost=4.05 rows=7)

Dans un scénario standard de données chaudes TPC-H à l'échelle de 100 Go, le temps d'exécution en série est le suivant :

| Supplier#000999085 | egFwcBv5TkH                                  |
| Supplier#000999105 | 1CKYsKKIxqM                                  |
| Supplier#000999253 | q0nlouqFchhsbmkPq                            |
| Supplier#000999314 | 1MLMPBBnYSnMl1lRRjXiu2B2sxahjItRt0v          |
| Supplier#000999319 | hkc5LIrtAz9clk2Edz8ENngn4PdhcSD02YRxN        |
| Supplier#000999347 | L,CPr2clOoPg91gYxqCsie7DNf                   |
| Supplier#000999357 | tQW7OYPfNDzfqqzHQCx                          |
| Supplier#000999362 | X7 Rxrst808LeHI1sYlVIW5Usqu                  |
| Supplier#000999394 | TZn2ZOsZCxMmW09                              |
| Supplier#000999486 | SMiqFfRyUuXldJp                              |
| Supplier#000999684 | SwmVOJNeJwTdDJcE0                            |
| Supplier#000999814 | Tlh9Z1u5EPk1drhEbiTZpRHJJwTX3FwJoE           |
| Supplier#000999841 | 9e5iYCk2pntVLLKnP5YJ3xT2IY0I7gENyfqy        |
| Supplier#000999850 | XEzRaermdYPO5XX                              |
| Supplier#000999902 | D4XvfAYuocmiUFM1N,EScgAHQcF                  |
| Supplier#000999936 | GkUI05zvDkNpMPlE,AplBgF8PxfEhe               |
| Supplier#000999949 | bRcyGJoAryorYRUKGtYfNt4ZlgvC6vZ              |
| Supplier#000999956 | 5r fovH1Bwu087yF5L7YHitAZWtmK                |
| Supplier#000999969 | 0xHYbgscQREncmbZziaM3dxg51jA,PKhyrAQ         |
+--------------------+----------------------------------------------+
17978 rows in set (43.52 sec)

Avec l'exécution parallèle multi-nœuds activée, le temps d'exécution est le suivant :

| Supplier#000999085 | egFwcBv5TkH                                |
| Supplier#000999105 | 1CKYsKKIxqM                                |
| Supplier#000999253 | q0nlouqFchhsbmkPq                          |
| Supplier#000999314 | 1MLMPBBnYSnMl1lRRjXiu2B2sxahjItRt0v        |
| Supplier#000999319 | hkc5LIrtAz9clk2Edz8ENngn4PdhcSD02YRxN      |
| Supplier#000999347 | L,CPr2cl0oPg91gYxqCsie7DNf                 |
| Supplier#000999357 | tQW7OYPfNDzfqqzHQCx                        |
| Supplier#000999362 | X7 Rxrst808LeHI1sYlVIW5Usqu                |
| Supplier#000999394 | TZn2ZOsZCxMmW09                            |
| Supplier#000999486 | SMiqFfRyUuXldJp                            |
| Supplier#000999684 | SwmVOJNeJwTdDJcE0                          |
| Supplier#000999814 | Tlh9Z1u5EPk1drhEbiTZpRHJJwTX3FwJoE         |
| Supplier#000999841 | 9e5iYCk2pntVLLKnP5YJ3xT2IY0I7gENyfqy      |
| Supplier#000999850 | XEzRaermdYPO5XX                            |
| Supplier#000999902 | D4XvfAYuocmiUFM1N,EScgAHQcF                |
| Supplier#000999936 | GkUI0SzvDkNpMPlE,AplBgF8PxfEhe             |
| Supplier#000999949 | bRcyGJoAryorYRUKGtYfNt4ZlgvC6vZ            |
| Supplier#000999956 | 5r fovH1Bwu087yF5L7YHitAZWtmK              |
| Supplier#000999969 | 0xHYbgscQREncmbZziaM3dxg51jA,PKhyrAQ       |
17978 rows in set (2.29 sec)

Le temps d'exécution est passé de 43,52 secondes à 2,29 secondes, soit une amélioration des performances d'un facteur 19.