Problèmes de performances SQL dus à un « travail inutile mais requis »

Niveau de connaissances requis pour comprendre l'article : une compréhension générale des bases de données et de SQL, une certaine expérience pratique avec les SGBD. L’indexation est probablement la chose la plus importante que vous puissiez apprendre pour écrire des requêtes SQL efficaces. Cependant, en deuxième position, tout près derrière, se trouve le fait de savoir que de nombreux clients SQL exigent que la base de données effectue beaucoup de « travail inutile mais nécessaire » . Répète après moi: Qu’est-ce qu’un « travail inutile mais obligatoire » ? Comme nous le dit Captain Obvious, elle :

Inutile

Laissez notre application client avoir besoin des données suivantes : Rien d'inhabituel. Nous travaillons avec une base de données de films (telle que la base de données Sakila ) et souhaitons afficher le titre et la note de tous les films aux utilisateurs. La requête suivante peut donner le résultat dont nous avons besoin :

SELECT title, rating
FROM film

Cependant, notre application (ou notre ORM) exécute à la place cette requête :

SELECT *
FROM film

Qu’obtient-on en conséquence ? Deviner. Nous recevons beaucoup d’informations inutiles : Sur la droite, vous pouvez même voir du JSON complexe en cours de chargement :

• à partir du disque
• mettre en cache
• Par fil
• à la mémoire du client
• et finalement jeté [car inutile]

Oui, nous jetons la plupart de ces informations. Toutes les actions entreprises pour extraire ces informations se sont révélées totalement inutiles. Est-ce vrai? Est-ce vrai.

Obligatoire

Et maintenant, le pire. Même si les optimiseurs peuvent désormais faire beaucoup de choses, ces actions sont obligatoires pour la base de données. La base de données n'a aucun moyen de savoir que l'application cliente n'a pas besoin de 95 % de ces données. Et ce n’est que l’exemple le plus simple. Imaginez connecter plusieurs tables... Et alors, dites-vous, mais les bases de données sont rapides ? Laissez-moi vous éclairer sur certaines choses auxquelles vous n'avez probablement pas pensé. Bien entendu, le temps d’exécution d’une requête individuelle n’affecte pas vraiment quoi que ce soit. D'accord, ça a fonctionné une fois et demie plus lentement, mais on va s'en sortir, non ? Pour plus de commodité? Parfois, c'est vrai. Mais si vous sacrifiez toujours les performances au profit de la commodité , ces petites choses commenceront à s'additionner. Nous ne parlerons plus de performances (la vitesse d'exécution des requêtes individuelles), mais de débit (le temps de réponse du système), et alors de sérieux problèmes commenceront, qui ne sont pas si faciles à résoudre. C'est à ce moment-là que vous perdez l'évolutivité. Jetons un coup d'œil aux plans d'exécution, dans ce cas, le SGBD Oracle :

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   166K|
|   1 |  TABLE ACCESS FULL| FILM |  1000 |   166K|
--------------------------------------------------

comparé à:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 20000 |
|   1 |  TABLE ACCESS FULL| FILM |  1000 | 20000 |
--------------------------------------------------

En exécutant une requête SELECT * au lieu du titre SELECT, la notation utilise 8 fois plus de mémoire dans la base de données. Rien d’inattendu, n’est-ce pas ? Nous savions que cela arriverait. Mais nous acceptons toujours cela pour bon nombre de nos demandes dans lesquelles nous n’avons tout simplement pas besoin de toutes ces données. Nous créons du travail inutile mais obligatoire pour la base de données , qui ne cesse de s'accumuler. Nous utilisons 8 fois plus de mémoire que nécessaire (le multiplicateur changera bien sûr). Pendant ce temps, à toutes les autres étapes (E/S disque, transfert de données sur le réseau, consommation de mémoire par le client), les problèmes sont exactement les mêmes, mais je vais les ignorer et examiner plutôt...

Utiliser des index

Aujourd'hui, la plupart des bases de données ont déjà apprécié le concept d' index de couverture . Un index de couverture n’est pas en soi un type spécial d’index. Mais il peut s'avérer être un "index spécial" pour une requête particulière, soit "par accident", soit parce qu'il était prévu qu'il en soit ainsi. Considérez la requête suivante :

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Il n’y a rien d’inattendu quant à sa mise en œuvre. Il s'agit d'une simple demande. Affichez la plage par index, accédez au tableau - et vous avez terminé :

-------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR               |     8 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACTOR_LAST_NAME |     8 |
-------------------------------------------------------------------

Bon plan, n'est-ce pas ? Eh bien, si nous en avions vraiment besoin, alors non : Évidemment, nous gaspillons de la mémoire, etc. Considérons cette requête comme une alternative :

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'

Son plan est le suivant :

----------------------------------------------------
| Id  | Operation        | Name            | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT |                 |     8 |
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 |
----------------------------------------------------

Nous avons pu supprimer complètement l'accès à la table, grâce à la présence d'un index qui satisfait tous les besoins de notre requête... un index couvrant. Est-ce important? Et comment! Cette approche permet d'accélérer certaines requêtes d'un ordre de grandeur (ou de les ralentir d'un ordre de grandeur lorsque l'index ne couvre plus après quelques changements). Les index de couverture ne peuvent pas toujours être utilisés. Vous devez payer pour les index et vous ne devriez pas en ajouter trop. Mais dans ce cas, tout est évident. Évaluons les performances :

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 100000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Улучшенный request, но все равно с доступом к таблице
      SELECT /*+INDEX(actor(last_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Оптимальный request: покрывающий индекс
      SELECT /*+INDEX(actor(last_name, first_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

Notez que la table des acteurs ne comporte que 4 colonnes, donc la différence de performances entre les instructions 1 et 2 n'est pas si grande, mais elle reste significative. Je noterai également que j'ai utilisé les astuces de l'optimiseur Oracle pour que l'optimiseur sélectionne l'un ou l'autre index spécifique pour la requête. L'opérateur 3 est le vainqueur incontesté de notre course. Ses performances sont bien meilleures et nous parlons d’une requête extrêmement simple. Encore une fois, lorsque nous écrivons SELECT *, nous créons un travail inutile mais obligatoire pour la base de données qu'elle ne peut pas optimiser. Elle ne sélectionnera pas l'index de couverture car il a une surcharge légèrement supérieure à l'index LAST_NAME qu'elle a choisi et, entre autres, elle doit toujours accéder à la table pour récupérer une colonne LAST_UPDATE inutile, par exemple. Mais plus nous analysons SELECT * en profondeur, plus les choses se révèlent pires. Parlons de...

Conversions SQL

Les optimiseurs fonctionnent si bien parce qu'ils transforment les requêtes SQL ( j'ai expliqué comment cela fonctionne lors de ma récente conférence aux Voxxed Days à Zurich ). Par exemple, il existe une transformation « exception JOIN » extrêmement puissante. Considérez la vue d'assistance suivante que nous avons dû créer pour éviter de joindre toutes ces tables manuellement à chaque fois :

CREATE VIEW v_customer AS
SELECT
  c.first_name, c.last_name,
  a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)

Cette vue effectue simplement toutes les jointures "...-à-un" entre la table client CUSTOMER et diverses tables pour des parties de leur adresse. Merci, normalisation. Imaginez qu'après avoir travaillé un peu avec cette vue, nous nous y sommes habitués et avons oublié les tableaux qui la sous-tendent. Et maintenant, nous exécutons la requête suivante :

SELECT *
FROM v_customer

En conséquence, nous obtenons un plan très impressionnant :

----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   599 | 47920 |   14 |
|*  1 |  HASH JOIN           |          |   599 | 47920 |   14 |
|   2 |   TABLE ACCESS FULL  | COUNTRY  |   109 |  1526 |    2 |
|*  3 |   HASH JOIN          |          |   599 | 39534 |   11 |
|   4 |    TABLE ACCESS FULL | CITY     |   600 | 10800 |    3 |
|*  5 |    HASH JOIN         |          |   599 | 28752 |    8 |
|   6 |     TABLE ACCESS FULL| CUSTOMER |   599 | 11381 |    4 |
|   7 |     TABLE ACCESS FULL| ADDRESS  |   603 | 17487 |    3 |
----------------------------------------------------------------

Oui bien sur. La base de données effectue toutes ces jointures et analyses de tables complètes parce que c'est ce que nous lui avons demandé de faire : récupérer toutes ces données. Maintenant, encore une fois, imaginez que tout ce dont nous avions réellement besoin était ceci : Quoi, sérieusement, non ? Maintenant, vous commencez à comprendre de quoi je parle. Mais imaginez que nous ayons appris quelque chose des erreurs passées et exécutons cette requête plus optimale :

SELECT first_name, last_name
FROM v_customer

Voyons maintenant ce qui s'est passé !

------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   599 | 16173 |     4 |
|   1 |  NESTED LOOPS      |             |   599 | 16173 |     4 |
|   2 |   TABLE ACCESS FULL| CUSTOMER    |   599 | 11381 |     4 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C007120 |     1 |     8 |     0 |
------------------------------------------------------------------

Des changements drastiques pour le mieux en termes d’exécution. Les jointures ont été supprimées car l'optimiseur peut désormais voir qu'elles sont inutiles , et s'il peut le voir (et vous n'avez pas rendu ce travail obligatoire en sélectionnant *), alors il ne peut tout simplement pas faire tout ce travail. Pourquoi en est-il ainsi dans ce cas ? La clé étrangère CUSTOMER.ADDRESS_ID à la clé primaire ADDRESS.ADDRESS_ID garantit exactement une valeur de cette dernière, ce qui signifie que l'opération JOIN sera une jointure "...-to-one" qui n'augmente ni ne diminue le nombre de lignes. . Et comme nous ne sélectionnons ni ne demandons aucune ligne, cela ne sert à rien de les charger. La suppression de JOIN n'affectera probablement pas du tout le résultat de la requête. Les bases de données font cela tout le temps. Vous pouvez exécuter la requête suivante sur presque toutes les bases de données :

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)

Dans ce cas, vous pouvez vous attendre à ce qu'une exception arithmétique soit levée, comme lors de l'exécution de la requête suivante :

SELECT 1 / 0 FROM dual

Mais cela n'arrive pas. L'optimiseur (ou même l'analyseur) peut garantir qu'aucun élément de la liste de sélection dans le prédicat EXISTS (SELECT ..) ne modifiera le résultat de la requête, il n'est donc pas nécessaire de l'exécuter. Comme ça!

Entre-temps...

L'un des problèmes les plus ennuyeux avec les ORM est qu'ils sont si faciles à écrire des requêtes SELECT *. En fait par exemple, en HQL/JPQL ils sont généralement utilisés par défaut. Nous pouvons omettre complètement la clause SELECT, car nous allons récupérer l'entité entière, n'est-ce pas ? Par exemple:

FROM v_customer

Par exemple, Vlad Mihalcea, expert et défenseur du développement avec Hibernate , recommande d'utiliser presque toujours des requêtes [qualifiées] lorsque vous êtes sûr de ne vouloir enregistrer aucune modification après le paiement. Les ORM facilitent grandement la résolution du problème de persistance des graphes d'objets. Remarque : Persistance. Les tâches de modification réelle des graphiques d'objets et d'enregistrement des modifications sont inextricablement liées. Mais si vous ne comptez pas faire cela, alors pourquoi s’embêter à en extraire l’essence ? Pourquoi ne pas rédiger une demande [affinée] ? Soyons clairs : du point de vue des performances, écrire une requête spécifiquement adaptée à votre cas d'utilisation spécifique est évidemment meilleure que toute autre option. Vous ne vous en souciez peut-être pas puisque votre ensemble de données est petit et cela n'a pas d'importance. Super. Mais lorsque vous aurez finalement besoin d'évolutivité, la refonte de vos applications pour utiliser des requêtes au lieu d'un parcours impératif du graphe d'entité sera assez difficile. Et vous aurez quelque chose à faire sans.

Compter les lignes pour savoir si quelque chose est présent

L'un des pires gaspillages de ressources consiste à exécuter des requêtes COUNT(*) juste pour voir si quelque chose se trouve dans la base de données. Par exemple, nous devons savoir si un utilisateur donné a des commandes. Et nous exécutons la requête :

SELECT count(*)
FROM orders
WHERE user_id = :user_id

Élémentaire. Si COUNT = 0, alors il n’y a aucune commande. Sinon, oui. Les performances ne seront pas si mauvaises puisque nous avons probablement un index sur la colonne ORDERS.USER_ID. Mais que pensez-vous que les performances de la requête ci-dessus seront comparées à l'option suivante :

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
)

Il n'est pas nécessaire d'être un génie pour comprendre qu'un véritable prédicat d'existence cessera de rechercher des chaînes supplémentaires dès qu'il trouvera la première . Donc si le résultat s’avère « pas de commandes », alors la vitesse sera comparable. Si toutefois le résultat est « oui, il y a des commandes », alors dans le cas où la quantité exacte n'a pas besoin d'être comptée, la réponse sera reçue beaucoup plus rapidement. Après tout, le nombre exact ne nous intéresse pas. Cependant, nous avons demandé à la base de données de le calculer ( travail inutile ) et la base de données ne sait pas que nous ignorons tous les résultats supérieurs à 1 ( travail requis ). Bien sûr, ce serait bien pire si nous appelions list.size() sur une collection sauvegardée par JPA pour obtenir les mêmes résultats. J'ai déjà écrit à ce sujet sur mon blog plus tôt et effectué des tests comparatifs des deux options sur...

Conclusion

Cet article énonce une évidence. Ne forcez pas la base de données à effectuer un travail inutile mais requis . Ce n’est pas nécessaire car, compte tenu des exigences, vous savez qu’un travail spécifique n’a pas besoin d’être effectué. Cependant, vous dites à la base de données de le faire. Cela est nécessaire car la base de données n'a aucun moyen de garantir que ce travail est inutile . Ces informations sont uniquement disponibles pour le client et ne sont pas disponibles pour le serveur. La base de données doit donc l'exécuter. L'article s'est concentré sur SELECT *, principalement parce que c'est un objet très pratique à regarder. Mais cela ne s'applique pas uniquement aux bases de données. Cela s'applique à tous les algorithmes distribués dans lesquels le client demande au serveur d'effectuer un travail inutile mais requis . Combien de tâches N+1 y a-t-il dans votre application AngularJS moyenne où l'interface utilisateur parcourt le résultat du service A, appelant le service B plusieurs fois, plutôt que de regrouper tous les appels vers B en un seul appel ? C'est un phénomène très courant. La solution est toujours la même. Plus vous fournissez d’informations à l’entité exécutant vos commandes, plus elle exécute (théoriquement) rapidement ces commandes. Écrivez des requêtes optimales. Toujours. Votre système tout entier vous en remerciera. Article original