Optimisation par élagage des colonnes pour les tables dérivées et les vues - PolarDB

Lorsqu'une requête ne sélectionne que quelques colonnes d'une table dérivée ou d'une vue qui en comporte un grand nombre, la base de données lit et traite néanmoins l'ensemble des colonnes définies dans la requête interne, y compris celles que la requête externe n'utilise jamais. Cette surcharge est particulièrement perceptible avec les tables larges et les vues génériques partagées entre de nombreuses requêtes.

L'élagage des colonnes élimine ce gaspillage. Durant la phase d'optimisation logique, PolarDB for MySQL réécrit la requête interne afin de supprimer les colonnes non référencées par les clauses SELECT, WHERE ou JOIN externes. Cette opération réduit les E/S, la consommation CPU, l'utilisation de la mémoire et les transferts réseau, diminuant ainsi la latence des requêtes et améliorant le débit du système.

Applicabilité

Série de produit : Cluster Edition, Standard Edition
Version du noyau : MySQL 8.0.2 avec la version de révision 8.0.2.2.31.1 ou ultérieure

Fonctionnement

L'élagage des colonnes s'applique sous forme de règle de réécriture de requête lors de la phase d'optimisation logique. L'optimiseur analyse l'intégralité de l'instruction de recherche pour identifier, dans une table dérivée ou une vue, les colonnes absentes de la liste SELECT finale, de la clause WHERE ou de la clause JOIN. Si une colonne s'avère redondante, l'optimiseur réécrit la requête interne pour la supprimer avant le début de l'exécution. Cela évite de lire et de traiter les données de cette colonne à la source, réduisant ainsi la consommation d'E/S et de ressources CPU.

Activer l'élagage des colonnes

Le paramètre loose_derived_table_pruning_mode permet de contrôler l'élagage des colonnes.

Paramètre	Niveau	Description
`loose_derived_table_pruning_mode`	Global/Session	Contrôle l'élagage des colonnes. Valeurs valides : `REPLICA_ON` (par défaut) — active l'élagage uniquement sur les nœuds en lecture seule ; `ON` — active l'élagage sur tous les nœuds ; `OFF` — désactive l'élagage.

**Dans la console PolarDB :** Recherchez et modifiez les paramètres portant le préfixe loose_.

Dans une session de base de données (ligne de commande ou client) : Utilisez la commande SET sans le préfixe :

SET derived_table_pruning_mode = 'ON';

Exemples

Exemple 1 : Requête sur table dérivée

Cet exemple illustre la réécriture d'une requête imbriquée sur table dérivée par l'élagage des colonnes.

Avant optimisation :

SELECT c1 FROM (SELECT c1 FROM (SELECT c1, c2 FROM t1 GROUP BY c1, c2) v) t;

Après optimisation :

SELECT c1 FROM (SELECT c1 FROM t1 GROUP BY c1, c2) v

La couche intermédiaire de sous-requête est supprimée car la requête la plus externe nécessite uniquement c1.

Pour vérifier l'optimisation avec EXPLAIN :

Créez la table de test et insérez des données :

CREATE TABLE t1 (id INT PRIMARY KEY, c1 INT, c2 INT, c3 VARCHAR(100));
INSERT INTO t1 VALUES (1, 10, 100, 'data'), (2, 20, 200, 'data');

Exécutez EXPLAIN avec l'élagage désactivé :

SET derived_table_pruning_mode = 'OFF';
EXPLAIN SELECT c1 FROM (SELECT c1 FROM (SELECT c1, c2 FROM t1 GROUP BY c1, c2) v) t;
SHOW WARNINGS;

Sortie attendue — la requête interne sélectionne à la fois c1 et c2 :

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                         |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `v`.`c1` AS `c1` from (/* select#3 */ select `testdb`.`t1`.`c1` AS `c1`,`testdb`.`t1`.`c2` AS `c2` from `testdb`.`t1` group by `testdb`.`t1`.`c1`,`testdb`.`t1`.`c2`) `v` |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Exécutez la même requête avec l'élagage activé :

SET derived_table_pruning_mode = 'ON';
EXPLAIN SELECT c1 FROM (SELECT c1 FROM (SELECT c1, c2 FROM t1 GROUP BY c1, c2) v) t;
SHOW WARNINGS;

Sortie attendue — c2 est retiré de la liste de sélection de la requête interne :

+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                              |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `v`.`c1` AS `c1` from (/* select#3 */ select `testdb`.`t1`.`c1` AS `c1` from `testdb`.`t1` group by `testdb`.`t1`.`c1`,`testdb`.`t1`.`c2`) `v` |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

La clause GROUP BY c1, c2 est conservée car c2 reste nécessaire pour le regroupement. Toutefois, l'optimiseur ne projette plus c2 dans la sortie de la requête externe.

Exemple 2 : Requête sur vue

L'élagage des colonnes s'applique aux vues comme aux tables dérivées. Soit la définition suivante :

CREATE VIEW v1 AS
  SELECT COUNT(*) AS a, (SELECT a FROM t2 WHERE a = FLOOR(COUNT(t1.a)/2)) AS s
  FROM t1;

L'exécution de SELECT a FROM v1 avant optimisation entraîne la lecture complète de la vue, y compris la sous-requête corrélée pour la colonne s. Après optimisation, la requête est réécrite ainsi :

SELECT COUNT(*) AS a FROM t1;

La sous-requête corrélée pour s est entièrement supprimée puisque la requête externe ne référence pas s.

Limites

L'élagage des colonnes ne s'applique que si la requête réécrite garantit une équivalence sémantique stricte avec la requête originale. Une colonne n'est pas élaguée si elle apparaît dans :

Une clause ORDER BY, GROUP BY, HAVING ou DISTINCT
Une clause PARTITION BY ou ORDER BY au sein d'une fonction de fenêtrage

Les colonnes générées par des fonctions non déterministes telles que RAND() ou UUID() peuvent être élaguées. Dans ce cas, la fonction n'est pas exécutée, ce qui évite des calculs inutiles. Il s'agit d'un effet d'optimisation voulu et non d'une limite.

FAQ

La modification de derived_table_pruning_mode nécessite-t-elle un redémarrage du cluster ?

Non. Le paramètre prend effet immédiatement dans la session en cours lorsqu'il est défini avec SET, ou globalement sans redémarrage lorsqu'il est modifié dans la console PolarDB.

Pourquoi les performances des requêtes ne se sont-elles pas améliorées après avoir défini derived_table_pruning_mode = ON sur le nœud principal ?

La valeur par défaut REPLICA_ON active l'élagage uniquement sur les nœuds en lecture seule. Pour l'activer également sur le nœud principal, définissez le paramètre sur ON.

Si les performances ne s'améliorent toujours pas après avoir défini la valeur sur ON, vérifiez deux points. D'une part, confirmez que la requête externe n'utilise qu'un sous-ensemble des colonnes définies dans la table dérivée ou la vue ; si elle sélectionne toutes les colonnes, rien ne peut être élagué. D'autre part, exécutez EXPLAIN suivi de SHOW WARNINGS et examinez la requête réécrite dans la sortie. Si la liste des colonnes de la requête interne reste inchangée, consultez la section Limites Limites pour vérifier si votre requête contient des clauses empêchant l'élagage.