Des optimisations SQL sympas qui ne dépendent pas du modèle de coût. Partie 2

Des optimisations SQL sympas qui ne dépendent pas du modèle de coût. Partie 1

4. Élimination des prédicats « dénués de sens »

Tout aussi dénués de sens sont les prédicats qui sont (presque) toujours vrais. Comme vous pouvez l'imaginer, si vous demandez :

SELECT * FROM actor WHERE 1 = 1;

... alors les bases de données ne l'exécuteront pas réellement, mais l'ignoreront simplement. J'ai répondu une fois à une question à ce sujet sur Stack Overflow et c'est pourquoi j'ai décidé d'écrire cet article. Je laisse le test au lecteur comme exercice, mais que se passe-t-il si le prédicat est un peu moins « dénué de sens » ? Par exemple:

SELECT * FROM film WHERE release_year = release_year;

Avez-vous vraiment besoin de comparer la valeur à elle-même pour chaque ligne ? Non, il n'y a aucune valeur pour laquelle ce prédicat serait FALSE , n'est-ce pas ? Mais nous devons encore le vérifier. Bien que le prédicat ne puisse pas être égal à FALSE , il peut très bien être égal à NULL partout , encore une fois en raison d'une logique à trois valeurs. La colonne RELEASE_YEAR est nullable, et si l'une des lignes a RELEASE_YEAR IS NULL , alors NULL = NULL donne NULL et la ligne doit être éliminée. La requête devient donc la suivante :

SELECT * FROM film WHERE release_year IS NOT NULL;

Quelles bases de données font cela ?

DB2

Oui!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

MySQL

C'est dommage, mais MySQL, encore une fois, ne mappe pas les prédicats dans les plans d'exécution, donc déterminer si MySQL implémente cette optimisation particulière est un peu délicat. Vous pouvez effectuer une évaluation des performances et voir si des comparaisons à grande échelle sont effectuées. Ou vous pouvez ajouter un index :

CREATE INDEX i_release_year ON film (release_year);

Et obtenez en retour des plans pour les demandes suivantes :

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

Si l'optimisation fonctionne, les plans des deux requêtes devraient être à peu près les mêmes. Mais dans ce cas, ce n'est pas le cas :

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

Comme vous pouvez le constater, nos deux requêtes diffèrent considérablement dans les valeurs des colonnes POSSIBLE_KEYS et FILTERED . J'oserais donc raisonnablement supposer que MySQL n'optimise pas cela.

Oracle

Oui!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

Malheureusement non!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

Les forfaits et les coûts varient. A savoir, regardez l'évaluation de la cardinalité, qui ne sert absolument à rien, alors que ce prédicat :

SELECT * FROM film WHERE release_year IS NOT NULL;

donne de bien meilleurs résultats :

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

Déception!

serveur SQL

Curieusement, SQL Server ne semble pas non plus faire cela :

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

Toutefois, compte tenu de l’apparence du plan, l’évaluation de la cardinalité est correcte, tout comme le coût. Mais d'après mon expérience avec SQL Server, je dirais que dans ce cas, aucune optimisation ne se produit, puisque SQL Server afficherait le prédicat réellement exécuté dans le plan (pour voir pourquoi, jetez un œil aux exemples de contraintes CHECK ci-dessous). Qu'en est-il des prédicats « dénués de sens » sur les colonnes NOT NULL ? La conversion ci-dessus n'était nécessaire que parce que RELEASE_YEAR peut être indéfini. Que se passe-t-il si vous exécutez la même requête inutile sur, par exemple, la colonne FILM_ID ?

SELECT * FROM film WHERE film_id = film_id

Cela ne correspond-il désormais à aucun prédicat ? Ou du moins, c'est comme ça que ça devrait être. Mais est-ce le cas ?

DB2

Oui!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

Aucun prédicat n'est appliqué et nous sélectionnons tous les films.

MySQL

Oui! (Encore une fois, supposition éclairée)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

Notez que la colonne EXTRA est désormais vide, comme si nous n'avions pas du tout de clause WHERE !

Oracle

Oui!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

Encore une fois, aucun prédicat ne s’applique.

PostgreSQL

Wow, non encore !

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

Le filtre est appliqué et le score de cardinalité est toujours de 5. Dommage !

serveur SQL

Et là encore non !

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

Résumé

Cela semble être une simple optimisation, mais elle n'est pas utilisée dans tous les SGBD ; en particulier, curieusement, elle n'est pas utilisée dans SQL Server !

5. Projections dans les sous-requêtes EXISTS

Fait intéressant, on me pose des questions à leur sujet tout le temps lors de ma master class, où je défends le point de vue selon lequel SELECT * ne mène généralement à rien de bon. La question est : est-il possible d'utiliser SELECT * dans une sous-requête EXISTS ? Par exemple, si nous avons besoin de trouver des acteurs qui ont joué dans des films...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Et la réponse est oui. Peut. L'astérisque n'affecte pas la demande. Comment pouvez-vous en être sûr ? Considérez la requête suivante :

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

Toutes ces bases de données rapportent une erreur de division par zéro. Notez un fait intéressant : dans MySQL, lorsque nous divisons par zéro, nous obtenons NULL au lieu d'une erreur, nous devons donc faire autre chose qui n'est pas autorisé. Maintenant, que se passe-t-il si nous exécutons, au lieu de ce qui précède, les requêtes suivantes ?

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

Désormais, aucune des bases de données ne renvoie d'erreur. Ils renvoient tous TRUE ou 1 . Cela signifie qu'aucune de nos bases de données n'évalue réellement la projection (c'est-à-dire la clause SELECT ) de la sous-requête EXISTS . SQL Server, par exemple, affiche le plan suivant :

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

Comme vous pouvez le constater, l' expression CASE a été convertie en constante et la sous-requête a été éliminée. D'autres bases de données stockent la sous-requête dans le plan et ne mentionnent rien sur la projection. Examinons donc à nouveau le plan de requête d'origine dans Oracle :

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Le plan de requête ci-dessus ressemble à ceci :

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

Nous observons des informations sur la projection à Id=3 . En fait, nous n'accédons même pas à la table FILM_ACTOR car nous n'en avons pas besoin. Le prédicat EXISTS peut être exécuté en utilisant un index de clé étrangère sur une seule colonne ACTOR_ID - tout ce qui est nécessaire pour cette requête - même si nous avons écrit SELECT * .

Résumé

Heureusement, toutes nos bases de données suppriment la projection des sous-requêtes EXISTS : Restez à l'écoute pour la partie 3 , où nous discuterons d'autres optimisations SQL intéressantes.