GreatSQL Optimiseur - Le filtrage conditionnel peut sélectionner un plan sous-optimal
- Comment condition_fanout_filter peut rendre le plan sous-optimal
L'optimiseur de GreatSQL doit déterminer l'ordre d'exécution des tables dans une jointure en fonction du nombre de lignes et du coût. Cela implique d'estimer les données qui satisferont les conditions. La fonction condition_fanout_filter calcule un pourcentage de filtrage des données, appelé coefficient filtered. Ce coefficient varie entre [0,1], où une valeur plus petite indique un meilleur filtrage. En multipliant ce coefficient par le nombre total de lignes, on obtient le nombre de lignes à parcourir, ce qui permet de réduire considérablement les coûts et le temps d'exécution.
Cette fonctionnalité est contrôlée par le paramètre OPTIMIZER_SWITCH_COND_FANOUT_FILTER, qui est activé par défaut. Dans la plupart des cas, il n'est pas nécessaire de le désactiver, mais si vous rencontrez des problèmes de lenteur d'exécution, vous pouvez envisager sa désactivation.
Voici un exemple qui montre comment condition_fanout_filter peut parfois conduire à un choix incorrect :
-- Création de deux tables avec index uniquement sur la deuxième colonne
-- La dernière colonne de la tableA dispose également d'un index
CREATE TABLE tableA (col_a1 INT, col_a2 INT, col_a3 DATETIME(6));
INSERT INTO tableA VALUES
(1,2,'2021-03-25 16:44:00.123456'),
(2,10,'2021-03-25 16:44:00.123456'),
(3,4,'2022-03-25 16:44:00.123456'),
(4,6,'2023-03-25 16:44:00.123456'),
(NULL,7,'2024-03-25 16:44:00.123456'),
(4,3,'2024-04-25 16:44:00.123456'),
(NULL,8,'2025-03-25 16:44:00.123456'),
(3,4,'2022-06-25 16:44:00.123456'),
(5,4,'2021-11-25 16:44:00.123456');
CREATE TABLE tableB (col_b1 INT, col_b2 INT, col_b3 VARCHAR(100));
INSERT INTO tableB VALUES
(1,2,'aa1'),(2,1,'bb1'),(2,3,'cc1'),(3,3,'cc1'),(4,2,'ff1'),
(4,4,'ert'),(4,2,'f5fg'),(NULL,2,'ee'),(5,30,'cc1'),(5,4,'fcc1'),
(4,10,'cc1'),(6,4,'ccd1'),(NULL,1,'fee'),(1,2,'aa1'),(2,1,'bb1'),
(2,3,'cc1'),(3,3,'cc1'),(4,2,'ff1'),(4,4,'ert'),(4,2,'f5fg'),
(NULL,2,'ee'),(5,30,'cc1'),(5,4,'fcc1'),(4,10,'cc1'),(6,4,'ccd1'),
(NULL,1,'fee'),(1,2,'aa1'),(2,1,'bb1'),(2,3,'cc1'),(3,3,'cc1'),
(4,2,'ff1'),(4,4,'ert'),(4,2,'f5fg'),(NULL,2,'ee'),(5,30,'cc1'),
(5,4,'fcc1'),(4,10,'cc1'),(6,4,'ccd1'),(NULL,1,'fee');
CREATE INDEX idx_a2 ON tableA(col_a2);
CREATE INDEX idx_a3 ON tableA(col_a3);
CREATE INDEX idx_b2 ON tableB(col_b2);
Exécutons une requête JOIN où les colonnes de la clause WHERE ne contiennent pas d'index de tableB, mais contiennent des index de tableA.
Avec le filtrage conditionnel activé, le résultat montre que tableB effectue un scan complet en premier, avec un nombre de lignes estimées de 39 × 33,33% = 13 lignes, tandis que tableA effectue un scan d'index ref avec 1 × 11,11% = 0,1 ligne, pour un total de 2 lignes.
greatsql> EXPLAIN SELECT * FROM tableB JOIN tableA ON tableB.col_b1=tableA.col_a1 AND tableB.col_b2=tableA.col_a2 WHERE tableB.col_b1<5 AND tableA.col_a3 < '2023-11-15';
+----+-------------+---------+------------+------+---------------+--------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | tableB | NULL | ALL | idx_b2 | NULL | NULL | NULL | 39 | 33.33 | Using where |
| 1 | SIMPLE | tableA | NULL | ref | idx_a2,idx_a3 | idx_a2 | 5 | db1.tableB.col_b2 | 1 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+--------+---------+-----------+------+----------+-------------+
Désactivons le filtrage conditionnel pour comparer : le résultat montre que tableA exécute un scan par plage en premier, avec un nombre de lignes estimées de 6 × 100% = 6 lignes, et tableB effectue un scan d'index ref avec 6 × 100% = 6 lignes, pour un total de 39 lignes.
greatsql> EXPLAIN SELECT /*+ set_var(optimizer_switch='condition_fanout_filter=off') */ * FROM tableB JOIN tableA ON tableB.col_b1=tableA.col_a1 AND tableB.col_b2=tableA.col_a2 WHERE tableB.col_b1<5 AND tableA.col_a3 < '2023-11-15';
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | tableA | NULL | range | idx_a2,idx_a3 | idx_a3 | 9 | NULL | 6 | 100.00 | Using index condition; Using where |
| 1 | SIMPLE | tableB | NULL | ref | idx_b2 | idx_b2 | 5 | db1.tableA.col_a2 | 6 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
Comparons maintenant le coût réel lorsque condition_fanout_filter est désactivé et que nous forçons l'ordre tableB puis tableA. Comme le montrent les deux résultats ci-dessous, tableB a effectué un scan complet avec un coût réel de 21,70, soit plus du double de l'estimation.
greatsql> EXPLAIN FORMAT=TREE SELECT * FROM tableB JOIN tableA ON tableB.col_b1=tableA.col_a1 AND tableB.col_b2=tableA.col_a2 WHERE tableB.col_b1<5 AND tableA.col_a3 < '2023-11-15';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=10.00 rows=2)
-> Filter: ((tableB.col_b1 < 5) and (tableB.col_b2 is not null)) (cost=4.15 rows=13)
-> Table scan on tableB (cost=4.15 rows=39)
-> Filter: ((tableA.col_a1 = tableB.col_b1) and (tableA.col_a3 < TIMESTAMP'2023-11-15 00:00:00')) (cost=0.32 rows=0.1)
-> Index lookup on tableA using idx_a2 (col_a2=tableB.col_b2) (cost=0.32 rows=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
greatsql> EXPLAIN FORMAT=TREE SELECT /*+ set_var(optimizer_switch='condition_fanout_filter=off') qb_name(qb1) JOIN_ORDER(@qb1 tableB,tableA) */ * FROM tableB JOIN tableA ON tableB.col_b1=tableA.col_a1 AND tableB.col_b2=tableA.col_a2 WHERE tableB.col_b1<5 AND tableA.col_a3 < '2023-11-15';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=21.70 rows=50)
-> Filter: ((tableB.col_b1 < 5) and (tableB.col_b2 is not null)) (cost=4.15 rows=39)
-> Table scan on tableB (cost=4.15 rows=39)
-> Filter: ((tableA.col_a1 = tableB.col_b1) and (tableA.col_a3 < TIMESTAMP'2023-11-15 00:00:00')) (cost=0.32 rows=1)
-> Index lookup on tableA using idx_a2 (col_a2=tableB.col_b2) (cost=0.32 rows=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Dans cet exemple, le paramètre condition_fanout_filter a conduit à différents ordres de tablespilotes, et les comportements de scan finaux sont différents. Cependant, il est clair que l'exécution du scan d'index par plage de tableA est plus efficace que le scan complet de tableB. Cet exemple montre que le pourcentage de filtrage estimé par condition_fanout_filter contient une part de subjectivité, ce qui peut finalement conduire à un chemin d'optimisation incorrect.
Tableau des types de méthodes d'accès join_type
| Type de méthode d'accès join_type | Description |
|---|---|
| JT_UNKNOWN | Invalide |
| JT_SYSTEM | La table ne contient qu'une seule ligne, ex: SELECT * FROM (SELECT 1) |
| JT_CONST | La table n'a qu'une seule ligne satisfaisante, ex: WHERE table.pk = 3 |
| JT_EQ_REF | Le symbole = est utilisé sur un index unique |
| JT_REF | Le symbole = est utilisé sur un index non unique |
| JT_ALL | Scan complet de table |
| JT_RANGE | Scan par plage |
| JT_INDEX_SCAN | Scan d'index |
| JT_FT | Scan d'index Fulltext |
| JT_REF_OR_NULL | Contient des valeurs NULL, ex: "WHERE col = ... OR col IS NULL" |
| JT_INDEX_MERGE | Une table exécute plusieurs scans par plage et fusionne les résultats |
Les différents types de scan, du plus rapide au plus lent : system > const > eq_ref > ref > range > index > ALL
- Solutions sans désactiver condition_fanout_filter
Existe-t-il un moyen de forcer l'ordre de jointure sans désactiver condition_fanout_filter ? Oui, il y a trois méthodes que vous pouvez utiliser selon vos besoins.
Méthode 1 : Utiliser le hint qb_name pour spécifier l'ordre de jointure
greatsql> EXPLAIN SELECT /*+ qb_name(qb1) JOIN_ORDER(@qb1 tableA,tableB) */ * FROM tableB JOIN tableA ON tableB.col_b1=tableA.col_a1 AND tableB.col_b2=tableA.col_a2 WHERE tableB.col_b1<5 AND tableA.col_a3 < '2023-11-15';
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | tableA | NULL | range | idx_a2,idx_a3 | idx_a3 | 9 | NULL | 6 | 100.00 | Using index condition; Using where |
| 1 | SIMPLE | tableB | NULL | ref | idx_b2 | idx_b2 | 5 | db1.tableA.col_a2 | 6 | 3.33 | Using where |
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
Méthode 2 : Créer des index sur toutes les colonnes de la clause WHERE
greatsql> CREATE INDEX idx_b1 ON tableB(col_b1);
greatsql> EXPLAIN SELECT * FROM tableB JOIN tableA ON tableB.col_b1=tableA.col_a1 AND tableB.col_b2=tableA.col_a2 WHERE tableB.col_b1<5 AND tableA.col_a3 < '2023-11-15';
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | tableA | NULL | range | idx_a2,idx_a3 | idx_a3 | 9 | NULL | 6 | 100.00 | Using index condition; Using where |
| 1 | SIMPLE | tableB | NULL | ref | idx_b2,idx_b1 | idx_b1 | 5 | db1.tableA.col_a1 | 5 | 16.67 | Using where |
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
Méthode 3 : Utiliser le hint JOIN_FIXED_ORDER avec l'ordre des tables
greatsql> EXPLAIN SELECT /*+ qb_name(qb1) JOIN_FIXED_ORDER(@qb1) */ * FROM tableA JOIN tableB ON tableB.col_b1=tableA.col_a1 AND tableB.col_b2=tableA.col_a2 WHERE tableB.col_b1<5 AND tableA.col_a3 < '2023-11-15';
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
| 1 | SIMPLE | tableA | NULL | range | idx_a2,idx_a3 | idx_a3 | 9 | NULL | 6 | 100.00 | Using index condition; Using where |
| 1 | SIMPLE | tableB | NULL | ref | idx_b2 | idx_b2 | 5 | db1.tableA.col_a2 | 6 | 3.33 | Using where |
+----+-------------+---------+------------+-------+---------------+--------+---------+-------------+------+----------+------------------------------------+
- Comment diagnostiquer ce type de problème
D'après les exemples ci-dessus, l'activation du filtrage conditionnel n'améliore pas toujours les performances. L'optimiseur peut surestimer l'impact du filtrage conditionnel, et dans certains cas, son utilisation peut反而 dégrader les performances. Le paramètre condition_fanout_filter de GreatSQL est activé par défaut, il est donc nécessaire de juger vous-même si cette fonctionnalité est appropriée. En général, vous devez faire attention dans les scénarios suivants :
| Situation | Solution |
|---|---|
| Les tables jointes contiennent de grandes tables et les colonnes de condition n'ont pas d'index | Si les champs de jointure n'ont pas d'index, vous devez d'abord en ajouter un pour permettre à l'optimiseur de comprendre la distribution des valeurs des champs et d'estimer plus précisément le nombre de lignes. |
| Les tables jointes comprennent une très grande table et une petite table | Vérifiez si l'ordre de jointure des tables est approprié enmodifiant l'ordre de jointure pour qu'une table plus petite serve de table pilote. Vous pouvez utiliser des hints pour forcer l'optimiseur à utiliser l'ordre de jointure spécifié. |
| Avant d'exécuter le SQL, utilisez d'abord EXPLAIN pour vérifier le plan d'exécution et déterminer si le résultat du filtrage conditionnel est raisonnable | Si les performances sont meilleures sans le filtrage conditionnel, vous pouvez désactiver cette fonctionnalité au niveau de la session. |
- Résumé
Cette section a présenté un exemple de cas où le filtrage conditionnel peut être mal évalué. Nous avons vu que l'activation du filtrage conditionnel n'améliore pas toujours les performances. L'optiimseur peut surestimer l'impact du filtrage conditionnel, et dans certains cas, son utilisation peut dégrader les performances. Le paramètre condition_fanout_filter de GreatSQL est activé par défaut, il est donc nécessaire de juger vous-même si cette fonctionnalité est appropriée.