Comment éliminer les opérations DISTINCT redondantes en SQL - PolarDB

Lorsqu'une requête utilise DISTINCT, la base de données supprime les lignes en double par tri ou hachage. Ces opérations consomment beaucoup de ressources CPU et de mémoire, particulièrement sur des jeux de données volumineux. Toutefois, le jeu de résultats est souvent déjà unique grâce à la clé primaire ou aux index uniques de la table. PolarDB for MySQL détecte automatiquement ces situations et supprime l'opération DISTINCT superflue, ce qui réduit le temps d'exécution, l'utilisation du CPU et la consommation mémoire sans modifier les résultats.

Fonctionnement

DISTINCT correspond à un cas particulier de GROUP BY. Par exemple, ces deux requêtes produisent des résultats identiques :

SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, c2 FROM t1 GROUP BY c1, c2;

En raison de cette équivalence, l'optimiseur applique la même analyse d'unicité aux deux formes. Lors de l'analyse d'une requête avec DISTINCT, il s'appuie sur les métadonnées de la table (clés primaires, index uniques) et sur les conditions de la requête pour déterminer si le jeu de résultats est déjà unique. Si l'unicité est garantie, l'optimiseur retire DISTINCT du plan d'exécution.

Le concept clé est la dépendance fonctionnelle : si les valeurs d'une ou plusieurs colonnes déterminent de manière unique celles d'autres colonnes, la présence d'une clé unique dans la liste SELECT rend l'ensemble du jeu de résultats unique. Dans ce cas, DISTINCT devient inutile.

L'optimiseur tient strictement compte des contraintes NOT NULL pour déduire l'unicité. Si une colonne d'une clé unique autorise les valeurs NULL , l'optimiseur peut ne pas être en mesure d'éliminer DISTINCT .

Prérequis

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

Cas d'élimination de DISTINCT

L'optimiseur supprime DISTINCT dès que l'une des conditions suivantes est remplie :

La liste SELECT contient une clé unique. Lorsque les colonnes projetées incluent la clé primaire de la table ou toutes les colonnes d'un index unique, l'unicité du jeu de résultats est garantie.
```
-- user_id is the primary key of the users table
SELECT DISTINCT user_id, user_name FROM users;  -- DISTINCT can be eliminated
```
Toutes les colonnes projetées dépendent fonctionnellement d'une clé unique. Si chaque colonne de la liste SELECT est déterminée de façon unique par une clé unique, le résultat est également unique. Cela s'applique aux requêtes sur une seule table ainsi qu'aux jointures un-à-un ou plusieurs-à-un avec une table de faits.
```
-- user_id is the primary key; user_name and email are uniquely determined by user_id
SELECT DISTINCT user_name, email FROM users WHERE user_id = 123;  -- DISTINCT can be eliminated
```
Toutes les colonnes projetées sont des constantes. Quand chaque colonne de la liste SELECT est une constante, le résultat comporte au plus une ligne. L'optimiseur retire alors DISTINCT et ajoute LIMIT 1.
```
SELECT DISTINCT 'hello', 123 FROM t1 WHERE a = 1;  -- DISTINCT is eliminated; LIMIT 1 is added
```
Chaque SELECT d'une requête UNION DISTINCT produit déjà des résultats uniques. L'optimiseur élimine DISTINCT des instructions SELECT internes.

Activer la fonctionnalité

Définissez le paramètre distinct_elimination_mode pour contrôler cette optimisation.

Le nom du paramètre varie selon l'endroit où vous le configurez :

Emplacement	Nom du paramètre	Notes
Console PolarDB	`loose_distinct_elimination_mode`	Le préfixe `loose_` est requis pour assurer la compatibilité avec les fichiers de configuration MySQL.
Session de base de données (commande `SET`)	`distinct_elimination_mode`	Omettez le préfixe `loose_` lors de la définition du paramètre dans une session.

Paramètre : loose_distinct_elimination_mode

Niveau	Valeur	Description
Global/Session	`REPLICA_ON`	Valeur par défaut. Active la fonctionnalité uniquement sur les nœuds en lecture seule (RO).
	`ON`	Active la fonctionnalité sur tous les nœuds.
	`OFF`	Désactive la fonctionnalité.

Pour activer la fonctionnalité sur tous les nœuds de la session en cours, exécutez :

SET distinct_elimination_mode = ON;

Vérifier l'optimisation

Utilisez EXPLAIN suivi de SHOW WARNINGS pour confirmer que l'optimiseur a bien éliminé DISTINCT. Lorsque l'optimisation est active, la requête réécrite affichée par SHOW WARNINGS ne contient plus DISTINCT.

La sortie de EXPLAIN seule n'indique pas toujours si DISTINCT a été éliminé. Consultez systématiquement SHOW WARNINGS pour examiner la requête réécrite par l'optimiseur.

Exemples

Exemple 1 : Projection de constantes

Si la liste SELECT ne contient que des constantes, l'optimiseur supprime DISTINCT et ajoute LIMIT 1.

Créez la table t1 et insérez des données.

CREATE TABLE testdb.t1 (a INT, b INT);
INSERT INTO testdb.t1 VALUES (1, 10), (1, 20);

Exécutez EXPLAIN sur la requête.

EXPLAIN SELECT DISTINCT a + 1 FROM testdb.t1 WHERE a = 1;

Consultez la requête réécrite par l'optimiseur.

SHOW WARNINGS;

Sortie attendue :

+-------+------+-------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                           |
+-------+------+-------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select (`testdb`.`t1`.`a` + 1) AS `a + 1` from `testdb`.`t1` where (`testdb`.`t1`.`a` = 1) limit 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------+

La requête réécrite contient limit 1 mais plus DISTINCT, ce qui confirme que l'optimisation est active.

Exemple 2 : Unicité déduite par dépendance fonctionnelle

Lorsque la colonne a détermine de manière unique la colonne b, et que b est une clé unique non NULL, l'optimiseur en déduit que a est aussi unique et élimine DISTINCT.

Créez la table t2, où la colonne b est générée à partir de a et possède une contrainte d'unicité.
```
CREATE TABLE t2 (
  a INT,
  b INT AS (a + 1) UNIQUE
);
INSERT INTO t2(a) VALUES (10), (20);
```

Exécutez EXPLAIN sur la requête.

EXPLAIN SELECT DISTINCT a FROM t2 WHERE b IS NOT NULL;

Consultez la requête réécrite par l'optimiseur.

SHOW WARNINGS;

Sortie attendue :

+-------+------+---------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                 |
+-------+------+---------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `testdb`.`t2`.`a` AS `a` from `testdb`.`t2` where (`testdb`.`t2`.`b` is not null) |
+-------+------+---------------------------------------------------------------------------------------------------------+

L'absence de DISTINCT dans la requête réécrite confirme que l'optimisation est active.

Exemple 3 : Résultat de sous-requête naturellement unique

Une sous-requête contenant une clé unique produit naturellement un résultat unique. Si cette unicité est préservée après un JOIN, l'optimiseur peut supprimer le DISTINCT externe.

Créez la table t3, où la colonne c est générée à partir de a et b avec une contrainte d'unicité.

CREATE TABLE t3 (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT AS (a + b) UNIQUE
);
INSERT INTO t3(a, b) VALUES (1, 10), (2, 20);

Exécutez EXPLAIN sur la requête.

EXPLAIN SELECT DISTINCT a, b FROM (SELECT a, b, c FROM t3) dt, (SELECT a AS d FROM t3 limit 1) dt2 WHERE c IS NOT NULL;

Consultez la requête réécrite par l'optimiseur.

SHOW WARNINGS;

Sortie attendue :

+-------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                          |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `testdb`.`t3`.`a` AS `a`,`testdb`.`t3`.`b` AS `b` from `testdb`.`t3` where (`testdb`.`t3`.`c` is not null) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------+

L'absence de DISTINCT dans la requête réécrite confirme que l'optimisation est active.

Performances

Une fois la fonctionnalité activée, les améliorations de performance typiques sont les suivantes :

Métrique	Amélioration
Temps d'exécution	Réduction de 10 % à 60 %
Utilisation de la mémoire	Réduction de 20 % à 70 %
Utilisation du CPU	Diminution de la charge liée au hachage ou au tri