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

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

10. Pousser les prédicats

Cette optimisation n’est pas tout à fait appropriée ici, car on ne peut pas dire qu’elle ne repose pas du tout sur le modèle des coûts. Mais comme je ne vois pas une seule raison pour laquelle l'optimiseur ne devrait pas insérer les prédicats dans les tables dérivées, je vais la lister ici, avec le reste des optimisations sans coût. Considérez la demande :

SELECT *
FROM (
  SELECT *
  FROM actor
) a
WHERE a.actor_id = 1;

La table dérivée de cette requête n'a aucun sens et doit être éliminée en réduisant le nombre de niveaux d'imbrication de la requête. Mais ignorons cela pour l'instant. Vous pouvez vous attendre à ce que la base de données exécute la requête suivante au lieu de celle ci-dessus :

SELECT *
FROM (
  SELECT *
  FROM actor
  WHERE actor_id = 1
) a;

Et puis, encore une fois, éliminez peut-être la demande externe. Un exemple plus complexe est obtenu en utilisant UNION :

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
) people
WHERE people.last_name = 'DAVIS';

Le résultat de cette requête :

FIRST_NAME  LAST_NAME  TYPE
----------------------------
JENNIFER    DAVIS      actor
SUSAN       DAVIS      actor
SUSAN       DAVIS      actor
JENNIFER    DAVIS      customer

Maintenant, ce serait formidable si l'optimiseur de base de données exécutait plutôt une requête comme celle-ci :

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  WHERE last_name = 'DAVIS'
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
  WHERE last_name = 'DAVIS'
) people;

C'est-à-dire qu'il pousse le prédicat dans la table dérivée, et de là dans deux sous-requêtes UNION ALL , car, après tout, nous avons un index à la fois sur la colonne ACTOR.LAST_NAME et sur la colonne CUSTOMER.LAST_NAME . Encore une fois, cette conversion est probablement basée sur les estimations de coûts dans la plupart des bases de données, mais je pense toujours que c'est une évidence puisque, avec n'importe quel algorithme, il est presque toujours préférable de réduire le nombre de tuples traités le plus tôt possible. Si vous connaissez un cas où une telle transformation s’avère être une mauvaise idée, je serai ravi d’entendre vos commentaires ! Je serais très intéressé. Alors, laquelle de nos bases de données peut faire cela ? (Et s'il vous plaît, c'est si simple et si important, que la réponse soit : tout)

DB2

Table dérivée simple Oui

Explain Plan
--------------------------------------------------
ID | Operation         |               Rows | Cost
 1 | RETURN            |                    |    6
 2 |  FETCH ACTOR      |   1 of 1 (100.00%) |    6
 3 |   IXSCAN PK_ACTOR | 1 of 200 (   .50%) |    0
Predicate Information
 3 - START (Q1.ACTOR_ID = 1)
      STOP (Q1.ACTOR_ID = 1)

Table dérivée avec UNION Oui aussi :

Explain Plan
-----------------------------------------------------------------
ID | Operation                        |               Rows | Cost
 1 | RETURN                           |                    |   20
 2 |  UNION                           |             2 of 1 |   20
 3 |   FETCH CUSTOMER                 |   1 of 1 (100.00%) |   13
 4 |    IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 (   .17%) |    6
 5 |   FETCH ACTOR                    |   1 of 1 (100.00%) |    6
 6 |    IXSCAN IDX_ACTOR_LAST_NAME    | 1 of 200 (   .50%) |    0
Predicate Information
 4 - START (Q1.LAST_NAME = 'DAVIS')
      STOP (Q1.LAST_NAME = 'DAVIS')
 6 - START (Q3.LAST_NAME = 'DAVIS')
      STOP (Q3.LAST_NAME = 'DAVIS')

De plus, dans les deux cas, la table dérivée (vue) a été exclue du plan car elle n'était pas réellement nécessaire.

MySQL

Table dérivée simple Oui

ID  TABLE  TYPE   KEY      REF    EXTRA
---------------------------------------
1   actor  const  PRIMARY  const

L'accès habituel à la clé primaire par valeur constante est utilisé. Table dérivée avec UNION Oups, non.

ID  SELECT_TYPE  TABLE       TYPE  KEY          REF    ROWS  EXTRA
------------------------------------------------------------------
1   PRIMARY        ref   	const  10
2   DERIVED      actor       ALL                       200
3   UNION        customer    ALL                       599

La conversion manuelle aboutit au plan :

ID  SELECT_TYPE  TABLE       TYPE  KEY                  REF    ROWS  EXTRA
--------------------------------------------------------------------------
1   PRIMARY        ALL                               5
2   DERIVED      actor       ref   idx_actor_last_name  const  3
3   UNION        customer    ref   idx_last_name        const  1

Il s'agit d'un problème sérieux lors de l'utilisation de requêtes imbriquées complexes dans MySQL !

Oracle

Table dérivée simple Oui, ça marche.

---------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |      1 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |      1 |      1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ACTOR"."ACTOR_ID"=1)

Et le nombre de niveaux de nidification a été réduit. La table dérivée avec UNION fonctionne également :

---------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | E-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |        |
|   1 |  VIEW                                 |                        |      4 |
|   2 |   UNION-ALL                           |                        |        |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                  |      3 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME    |      3 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER               |      1 |
|*  6 |     INDEX RANGE SCAN                  | IDX_CUSTOMER_LAST_NAME |      1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LAST_NAME"='DAVIS')
   6 - access("LAST_NAME"='DAVIS')

Cependant, sans réduire le nombre de niveaux de nidification. Id=1 "View" montre que la table dérivée est toujours là. Dans ce cas, ce n’est pas un gros problème, juste peut-être un petit coût supplémentaire.

PostgreSQL

Table dérivée simple Oui, ça marche :

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

Notez cependant que PostgreSQL n'utilise parfois même pas la clé primaire pour rechercher une seule ligne, mais analyse la table entière. Dans ce cas, 200 lignes × 25 octets par ligne (« largeur ») tiennent dans un bloc, alors à quoi bon s'embêter avec des lectures d'index autre que de générer des opérations d'E/S inutiles pour accéder à une si petite table ? Table dérivée avec UNION Oui, ça marche aussi :

QUERY PLAN
-----------------------------------------------------------------------------------
Append  (cost=0.00..12.83 rows=4 width=45)
  ->  Seq Scan on actor  (cost=0.00..4.50 rows=3 width=45)
        Filter: ((last_name)::text = 'DAVIS'::text)
  ->  Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=45)
        Index Cond: ((last_name)::text = 'DAVIS'::text)

Encore une fois, un index sur la colonne ACTOR.LAST_NAME n'est pas utilisé, mais un index sur la colonne CUSTOMER.LAST_NAME est utilisé car la table CUSTOMER est beaucoup plus grande.

serveur SQL

Table dérivée simple Oui, ça marche

|--Nested Loops(Inner Join)
     |--Index Seek(SEEK:([actor_id]=(1)))
     |--RID Lookup(OBJECT:([actor]))

La table dérivée avec UNION fonctionne également.

|--Concatenation
     |--Compute Scalar(DEFINE:([Expr1003]='actor'))
     |    |--Nested Loops(Inner Join)
     |         |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
     |         |--RID Lookup(OBJECT:([actor]))
     |--Compute Scalar(DEFINE:([Expr1007]='customer'))
          |--Nested Loops(Inner Join)
               |--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
               |--RID Lookup(OBJECT:([customer]))

Résumé

Mes espoirs ne se sont pas réalisés. MySQL 8.0.2 ne prend pas encore entièrement en charge cette simple optimisation. Cependant, tout le monde le soutient.

Conclusion

La liste présentée ici est loin d’être complète. Il existe de nombreuses autres transformations SQL simples qui ne sont pas (ou ne devraient pas être) difficiles à mettre en œuvre pour les bases de données, avant même qu'un optimiseur de coûts ne soit impliqué. Ils éliminent le travail inutile et supplémentaire [pour la base de données] ( par opposition au travail inutile et requis , dont j'ai déjà parlé ). Ce sont des outils importants pour :

1. Les erreurs stupides [du développeur] n'ont eu aucun impact sur les performances. Les erreurs sont inévitables et, à mesure qu'un projet grandit et que les requêtes SQL deviennent plus complexes, ces erreurs peuvent s'accumuler, espérons-le, sans aucun effet.


2. Offrent la possibilité de réutiliser des blocs complexes, tels que des vues et des fonctions de table, qui peuvent être intégrés dans des requêtes SQL parentes, transformés, ou partiellement supprimés ou réécrits.

Ces capacités sont essentielles au point 2. Sans eux, il serait très difficile de créer des requêtes SQL de 4 000 lignes avec des performances normales basées sur une bibliothèque de composants SQL réutilisables. À la grande déception des utilisateurs de PostgreSQL et MySQL, ces deux bases de données open source populaires ont encore un long chemin à parcourir par rapport à leurs concurrents commerciaux DB2, Oracle et SQL Server, parmi lesquels DB2 a obtenu les meilleurs résultats, avec Oracle et SQL Server à peu près au coude à coude. cou, un peu en arrière.