Optimisations SQL sympas qui ne dépendent pas du modèle de coût. Partie 4

Optimisations SQL sympas qui ne dépendent pas du modèle de coût. Partie 1 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 3

8. VÉRIFIER les restrictions

Oh, c'est un truc sympa ! Notre base de données Sakila a une contrainte CHECK sur la colonne FILM.RATING :

CREATE TABLE film (
  ..
  RATING varchar(10) DEFAULT 'G',
  ..
  CONSTRAINT check_special_rating
    CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
  ..
);

Sérieusement, utilisez les contraintes CHECK pour garantir l’intégrité des données. Le coût de leur ajout est extrêmement faible - bien inférieur à d'autres restrictions, par exemple PRIMARY , UNIQUE ou FOREIGN KEY , car ils ne nécessitent pas d'index pour fonctionner, vous les obtenez donc pratiquement "gratuitement". Mais il y a une nuance intéressante liée à l’optimisation ! Considérez les requêtes suivantes :

Prédicats impossibles

Nous avons déjà rencontré des prédicats impossibles , même des contraintes NOT NULL (qui sont en fait un type particulier de contrainte CHECK ), mais celle-ci est encore plus cool :

SELECT *
FROM film
WHERE rating = 'N/A';

Il n'existe pas de film de ce type, et il ne peut y en avoir, puisque la contrainte CHECK empêche son insertion (ou sa mise à jour). Encore une fois, cela devrait se traduire par une commande de ne rien faire. Qu'en est-il de cette demande ?

CREATE INDEX idx_film_rating ON film (rating);

SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');

Grâce à l'index ci-dessus, il suffit probablement de faire une analyse rapide de l'index et de compter tous les films avec rating = 'NC-17' , puisque c'est la seule note restante. La requête doit donc être réécrite comme ceci :

SELECT count(*)
FROM film
WHERE rating = 'NC-17';

Cela devrait être le cas quel que soit l'index, car comparer une colonne avec une valeur est plus rapide que comparer avec 4. Alors, quelles bases de données peuvent faire cela ?

DB2

Prédicat impossible (note = 'N/A') Cool !

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

Predicate Information
 2 - RESID (1 = 0)

Prédicat inversé (note = 'NC-17') Non...

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |   34
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |   34
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |   34

Predicate Information
 3 - SARG  NOT(Q1.RATING IN ('G', 'PG', 'PG-13', 'R'))

Bien que l'étape ID=3 utilise un index et que les cardinalités soient correctes, une analyse complète se produit car le plan n'a pas de prédicat de plage, mais uniquement le prédicat « SARG ». Voir la critique de Marcus Wynand pour plus de détails . Vous pouvez également le démontrer en inversant manuellement le prédicat et en obtenant :

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |    7
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |    7
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |    7

Predicate Information
 3 - START (Q1.RATING = 'NC-17')
      STOP (Q1.RATING = 'NC-17')

Nous avons maintenant le prédicat de plage souhaité.

MySQL

MySQL prend en charge la syntaxe de contrainte CHECK , mais pour une raison quelconque, ne les applique pas. Essaye ça:

CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;

et vous obtiendrez :

A
-
0

Zéro point pour MySQL (vraiment, pourquoi ne pas simplement prendre en charge les contraintes CHECK ?)

Oracle

Prédicat impossible (note = 'N/A')

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |
|*  1 |  FILTER            |      |      1 |        |      0 |
|*  2 |   TABLE ACCESS FULL| FILM |      0 |     89 |      0 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("RATING"='N/A')

Encore une fois, un filtre très étrange NULL IS NOT NULL , coupant FULL TABLE SCAN , qui pourrait tout aussi facilement être complètement supprimé du plan. Mais au moins ça marche ! Prédicat inversé (note = 'NC-17') Oups :

----------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| IDX_FILM_RATING |      1 |    415 |    210 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))

Le prédicat ne peut pas être inversé, l'évaluation de la cardinalité est très boiteuse, en plus nous obtenons INDEX FAST FULL SCAN au lieu de INDEX RANGE SCAN , et le prédicat de filtre au lieu du prédicat d'accès . Mais voici ce que l’on devrait obtenir, par exemple, en inversant manuellement le prédicat :

------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE   |                 |      1 |      1 |      1 |
|*  2 |   INDEX RANGE SCAN| IDX_FILM_RATING |      1 |    210 |    210 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RATING"='NC-17')

Déception!

PostgreSQL

Notez que la version PostgreSQL de la base de données Sakila utilise le type ENUM au lieu des contraintes CHECK sur la colonne RATING . J'ai dupliqué la table en utilisant une contrainte CHECK à la place . Prédicat impossible (note = 'N/A') Ne fonctionne pas :

QUERY PLAN
------------------------------------------------------
Seq Scan on film2  (cost=0.00..67.50 rows=1 width=385)
  Filter: ((rating)::text = 'N/A'::text)

Le prédicat inverse (rating = 'NC-17') ne fonctionne pas non plus :

QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=70.53..70.54 rows=1 width=8)
  ->  Seq Scan on film2  (cost=0.00..70.00 rows=210 width=0)
        Filter: ((rating)::text  ALL ('{G,PG,PG-13,R}'::text[]))

Vraiment désolé! Remarque : Comme David Rowley nous l'a gentiment fait remarquer dans les commentaires , cette fonctionnalité peut être activée en définissant le paramètre :

SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan

Prédicat inversé (note = 'NC-17') Oui aussi !

|--Compute Scalar
     |--Stream Aggregate
          |--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))

Résumé

9. Connexions réflexives inutiles.

À mesure que vos requêtes deviennent plus complexes, vous devrez peut-être effectuer une jointure réfléchie sur une table en fonction de sa clé primaire. Croyez-moi, il s'agit d'une pratique très courante lors de la création de vues complexes et de leur connexion les unes aux autres, donc s'assurer que la base de données y prête attention est un élément essentiel de l'optimisation du code SQL complexe. Je ne montrerai pas un exemple complexe, un simple suffira, par exemple :

SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Cela peut être considéré comme un cas particulier d'élimination de JOIN , puisque nous n'avons pas réellement besoin d'une jointure à A2 , nous pouvons faire tout ce dont nous avons besoin avec uniquement la table A1 . Ensuite, l'élimination de INNER JOIN ne fonctionne correctement que s'il existe une FOREIGN KEY , que nous n'avons pas ici. Mais grâce à la clé primaire par ACTOR_ID , on peut prouver qu'en fait A1 = A2 . Dans un sens, c'est encore une fermeture transitive . Vous pouvez aller encore plus loin et utiliser les colonnes des tableaux A1 et A2 :

SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Dans le cas classique de l'élimination de JOIN , il ne serait plus possible de l'éliminer puisque les deux tables sont projetées. Mais puisque nous avons déjà prouvé que A1 = A2 , alors ils sont interchangeables, nous pouvons donc nous attendre à ce que la requête soit convertie en :

SELECT first_name, last_name
FROM actor;

Quel SGBD peut faire cela ?

DB2

Projection du tableau A1 uniquement Oui :

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

Projection des tableaux A1 et A2 ... aussi oui :

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

MySQL

Projection du tableau A1 uniquement Non.

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id  Using index

Projection des tableaux A1 et A2 ...non plus

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id

Déception totale....

Oracle

Projection du tableau A1 uniquement Oui

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

Projection des tableaux A1 et A2 Oui à nouveau

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

PostgreSQL

Projection du tableau A1 uniquement Non :

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=17)
  ->  Hash  (cost=4.00..4.00 rows=200 width=4)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=4)

Projection des tableaux A1 et A2 Et encore non :

QUERY PLAN
---------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=10)
  ->  Hash  (cost=4.00..4.00 rows=200 width=11)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=11)

serveur SQL

Projection du tableau A1 uniquement Bizarrement non ! (Mais gardez à l'esprit que j'utilise SQL Server 2014, les versions plus récentes peuvent résoudre ce problème. Je pourrais certainement utiliser une mise à niveau !)

|--Merge Join(Inner Join, MERGE:([a2].[actor_id])=([a1].[actor_id]))
     |--Index Scan(OBJECT:([a2]))
     |--Sort(ORDER BY:([a1].[actor_id] ASC))
          |--Table Scan(OBJECT:([a1]))

Projection des tableaux A1 et A2 Pas encore, et le plan a même changé pour le pire :

|--Hash Match(Inner Join, HASH:([a1].[actor_id])=([a2].[actor_id]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a1]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a2]))

Résumé

Franchement, je m'attendais à ce que cette optimisation soit effectuée sur toutes les bases de données, mais je me suis malheureusement trompé. En plus d'éliminer JOIN , il s'agit de l'une des optimisations les plus importantes, vous permettant de créer d'énormes requêtes SQL à partir de parties réutilisables telles que des vues et des fonctions de table. Malheureusement, il n'est pas pris en charge dans 3 des 5 bases de données les plus courantes.