6.1 Présentation

Passons maintenant de la théorie à la pratique.

« En théorie, il n'y a pas de différence entre la théorie et la pratique. En pratique, ils le sont."

Nous vivons dans le monde réel et tous les produits logiciels sont finalement créés pour des personnes vivantes. Et ces vivants sont très agacés par les sites qui se chargent lentement, et les programmes qui ralentissent.

Et si une requête de base de données prend plus d'une seconde, c'est inacceptable . Les utilisateurs n'utiliseront tout simplement pas un produit dont les pages/fonctionnalités sont si lentes.

Mais souvent, pour afficher une page, vous devez effectuer plusieurs dizaines de requêtes dans la base de données. Et s'ils sont exécutés séquentiellement, alors vous n'avez plus de seconde limite, mais disons 100ms par requête.

Voici les 5 principales façons dont les programmeurs accélèrent les requêtes de base de données :

  1. Ajout d'index aux tables de la base de données.
  2. Réécriture et optimisation des requêtes.
  3. Activez (et configurez) la mise en cache côté base de données.
  4. Activez la mise en cache côté client.
  5. Exécution de la dénormalisation de la base de données.

Vous êtes déjà familiarisé avec toutes ces choses pour la plupart, donc ce qui suit ne sera que des conseils pratiques.

6.2 Indices

Ce n'est un secret pour personne que travailler avec une base de données occupe la majeure partie du travail de presque tous les sites. Et c'est le travail avec la base de données qui est le plus souvent le goulot d'étranglement des applications web.

Java webinar

Dans cet article, je voudrais donner des conseils pratiques sur l'utilisation de MySQL.

je dirai tout de suite:

  • cet article est écrit sur MySQL, bien que les choses générales soient probablement vraies pour n'importe quel SGBD.
  • tout ce qui est écrit dans l'article est mon point de vue personnel et n'est pas la vérité ultime.
  • les conseils ne prétendent pas être nouveaux et sont le résultat d'une généralisation de la littérature lue et de l'expérience personnelle.
  • dans le cadre de cet article, je n'aborderai pas les problèmes de configuration de MySQL.

Les problèmes liés à l'utilisation de MySQL peuvent être divisés en trois groupes (par ordre d'importance) :

  1. Non-utilisation ou mauvaise utilisation des index.
  2. Mauvaise structure de la base de données.
  3. Requêtes SQL incorrectes \ sous-optimales.

Examinons de plus près chacun de ces groupes.

Utilisation des index

Ne pas utiliser ou mal utiliser les index est ce qui ralentit le plus souvent les requêtes. Pour ceux qui ne sont pas familiers avec le mécanisme de fonctionnement des index ou qui n'ont pas encore lu à ce sujet dans le manuel, je vous conseille vivement de le lire.

Conseils d'utilisation des index :

  • Vous n'avez pas besoin de tout indexer . Très souvent, sans en comprendre le sens, les gens indexent simplement tous les champs d'une table. Les index accélèrent les récupérations, mais ralentissent les insertions et les mises à jour de lignes, de sorte que le choix de chaque index doit être significatif.
  • L'un des principaux paramètres qui caractérisent l'indice est la sélectivité, c'est-à-dire le nombre d'éléments différents dans l'indice. Cela n'a aucun sens d'indexer un champ qui a deux ou trois valeurs possibles. Il y aura peu d'avantages à un tel indice.
  • Le choix des index doit commencer par une analyse de toutes les requêtes sur une table donnée. Très souvent, après une telle analyse, au lieu de trois ou quatre indices, vous pouvez en faire un composite.
  • Lors de l'utilisation d'index composites, l'ordre des champs dans l'index est critique.
  • N'oubliez pas de couvrir les index. Si toutes les données d'une requête peuvent être extraites d'un index, MySQL n'accédera pas directement à la table. Ces demandes seront exécutées très rapidement. Par exemple, pour une requête SELECT name FROM user WHERE login='test'avec un index (login, nom), l'accès à la table n'est pas obligatoire. Parfois, il est judicieux d'ajouter un champ supplémentaire à un index composite, ce qui couvrira l'index et accélérera les requêtes.
  • Pour les index de ligne, il suffit souvent d'indexer seulement une partie de la ligne. Cela peut réduire considérablement la taille de l'index.
  • Si %c'est au début, LIKE(SELECT * FROM table WHERE field LIKE '%test')les index ne seront pas utilisés.
  • L'index FULLTEXT n'est utilisé qu'avec la syntaxe MATCH ... AGAINST .

6.3 Structure de la base de données

Une base de données bien conçue est la clé d'un travail rapide et efficace avec la base de données. En revanche, une base de données mal conçue est toujours un casse-tête pour les développeurs.

Conseils de conception de base de données :

  1. Utilisez les types de données les plus petits possibles. Plus le type de données est grand, plus la table est grande, plus il faut d'accès au disque pour obtenir les données. Utilisez une procédure très pratique : SELECT * FROM table_name PROCEDURE ANALYSE();pour déterminer les types de données minimaux possibles.
  2. Observez les formes normales pendant la phase de conception. Souvent, les programmeurs ont recours à la dénormalisation déjà à ce stade. Cependant, dans la plupart des cas, au début du projet, il est loin d'être évident comment cela peut en résulter. Dénormaliser une table est beaucoup plus facile que de souffrir d'une dénormalisation sous-optimale. Et JOINparfois, cela fonctionne plus rapidement que les tables mal dénormalisées.
  3. N'utilisez pas NULLde colonnes à moins que vous n'en ayez consciemment besoin.

6.4 Requêtes SQL.

Tout aussi souvent, on souhaite réécrire toutes les requêtes en SQL natif afin que la requête soit la plus rapide possible. Si vous décidez de le faire, voici quelques conseils :

  1. Évitez les demandes en boucle. SQL est un langage d'ensembles, et l'écriture des requêtes doit être abordée non pas dans le langage des fonctions, mais dans le langage des ensembles.
  2. Évitez *(astérisques) dans les requêtes. N'hésitez pas à lister exactement les champs que vous choisissez. Cela réduira la quantité de données récupérées et envoyées. Aussi, n'oubliez pas de couvrir les index. Même si vous sélectionnez tous les champs du tableau, il est préférable de les lister. Tout d'abord , cela améliore la lisibilité du code. Lors de l'utilisation d'astérisques, il est impossible de savoir quels champs se trouvent dans le tableau sans y jeter un coup d'œil. Deuxièmement , aujourd'hui, votre table comporte cinq colonnes INT , et un mois plus tard, un autre TEXT et BLOB ont été ajoutés , et l'astérisque est resté tel quel.
  3. Lorsqu'il est paginé, pour obtenir le nombre total d'enregistrements, utilisez SQL_CALC_FOUND_ROWSet SELECT FOUND_ROWS();Lorsqu'il est utilisé SQL_CALC_FOUND_ROWS MySQL, met en cache le nombre de lignes sélectionné (avant que LIMIT ne soit appliqué) et, lorsqu'il est utilisé, SELECT FOUND_ROWS()ne renvoie que cette valeur mise en cache sans avoir à réexécuter la requête.
  4. N'oubliez pas qu'il INSERTexiste une syntaxe pour les insertions multiples. Une requête s'exécutera un ordre de grandeur plus rapidement que plusieurs requêtes dans une boucle.
  5. Utilisez LIMITlà où vous n'avez pas besoin de toutes les données.
  6. Utiliser INSERT… ON DUPLICATE KEY UPDATE…à la place de et INSERTou UPDATEaprès la sélection, et souvent à la place de REPLACE.
  7. N'oubliez pas cette fonctionnalité étonnante GROUP_CONCAT. Il peut aider avec des requêtes complexes.