Développement du logiciel Ruby on Rails. Index v2

Lorsque l'on travaille avec le cadre Ruby on Rails, on a généralement affaire à des bases de données relationnelles telles que MySQL ou PostgreSQL. Lorsque nous définissons des migrations à l'aide d'Active Record Migrations, nous rencontrons ce que l'on appelle des index, mais les débutants ne comprennent pas toujours très bien les index et les avantages qu'ils apportent.

Dans ce billet, j'aimerais expliquer ce que sont les index, à quoi ils servent et présenter quelques bonnes pratiques sur la façon de les utiliser.

Base de données

Il existe de nombreux moteurs de base de données, et l'un des plus populaires est MySQL, PostgreSQL, Oracle ou Microsoft SQL Server. Ce sont toutes des bases de données relationnelles, ce qui signifie que toutes les données sont liées les unes aux autres et stockées dans des tables. Chaque ligne d'une table est appelée un enregistrement, et chaque enregistrement possède son propre identifiant (id). Vous pouvez consulter le classement des moteurs de base de données les plus populaires sur le site https://db-engines.com/en/ranking. Vous y trouverez également des bases de données non relationnelles, telles que MongoDB.

Création d'un index

Les tables de nos bases de données peuvent comporter de quelques colonnes à plusieurs dizaines de colonnes - dans les cas extrêmes, jusqu'à plusieurs centaines. N'oubliez pas que chaque table peut avoir un nombre illimité de lignes. Ce nombre ne résulte pas directement de la structure de la base de données et nous devrions toujours supposer que le nombre d'enregistrements augmentera successivement et que, par conséquent, notre base de données s'accroîtra. Les hypothèses initiales et les requêtes écrites dans les applications existantes peuvent être excellentes pour un petit ou moyen nombre d'enregistrements, mais avec le temps, lorsque davantage de données arrivent, la communication de l'application avec la base de données cesse d'être efficace.

Le rôle du programmeur est d'écrire des requêtes pour extraire des données de la ou des tables, mais la manière optimale de traiter la requête dépend du moteur de la base de données. N'oubliez pas que les moteurs de base de données chargent les données du disque dans la mémoire et les analysent ensuite. Cela signifie que si de nombreux utilisateurs effectuent des opérations complexes en même temps, plusieurs d'entre eux devront attendre leur tour en raison du manque de ressources pour mettre en œuvre leurs recherches. C'est pourquoi des index pertinents sont si importants.

Wiki : Index - structure de données qui augmente la vitesse des opérations de recherche sur une table.

Pour chaque index, nous devons définir des clés (pour une ou plusieurs colonnes) qui seront utilisées pour rechercher des enregistrements dans le tableau. Les données de l'index seront triées en fonction de la clé définie précédemment, ce qui accélérera considérablement la recherche de données dans la table. L'exemple le plus simple de la vie quotidienne est celui d'un annuaire téléphonique dans lequel les personnes sont triées par nom et prénom. On peut dire que notre index dans ce cas sera le prénom et le nom de famille.

Comment choisir la meilleure clé d'indexation ? Ce n'est pas difficile - il suffit de se rappeler quelques règles. Créez un index basé sur des colonnes qui :

- sera souvent utilisé dans nos demandes de renseignements (OÙ),

- en combinaison les uns avec les autres donnent une valeur unique (c'est-à-dire une valeur qui indiquera exactement une ligne),

- seront utilisées comme colonnes de connexion (JOIN),

- donnent les clés les plus sélectives, c'est-à-dire celles qui renvoient le moins de lignes lors de l'écriture d'une requête.

Si nous savons déjà quelles clés seront optimales pour notre table, nous pouvons également nous demander de combien d'index nous avons besoin. Dans ce cas, il est préférable de connaître les requêtes qui feront référence à notre table dès la phase de conception.

Créons des index pour les requêtes spécifiques qui apparaîtront, mais ne les écrivons pas pour chaque colonne. Les index, comme les tables, doivent être stockés quelque part, donc lorsque nous créons des tables avec un index pour chaque colonne, nous devons tenir compte du fait que la quantité d'espace utilisée peut augmenter de manière significative.

Créer un index unique

Une autre question à laquelle nous devons réfléchir est celle de l'unicité. Cela vaut la peine de passer cinq minutes supplémentaires à se demander si notre index est vraiment unique. De cette manière, nous indiquons à l'optimiseur de requêtes qu'il ne doit pas s'attendre à des doublons dans la requête. Par exemple, les adresses électroniques :

frozenstringliteral : true

class CreateUsers < ActiveRecord::Migration[6.0]
def change
createtable :users do |t|
t.string :email, null : false
end
addindex :users, :email, unique : true
end
fin

Sur l'exemple du moteur PostgreSQL, je montrerai la différence de vitesse de requête sur la colonne email avec un index unique et sans index.

1. Vous pouvez utiliser des échantillons code sur votre propre base de données pour pouvoir tester l'exemple ci-dessous. Tout d'abord, créons une table vide avec une seule colonne :

 CREATE TABLE users (
   email varchar
 ) ;

2. Générons 10 000 enregistrements pour le test :

 DO $
   BEGIN FOR i IN 1..10000 LOOP
     INSERT INTO users values((select 'user' || i || '@example.com')) ;
   END LOOP ; END ;
 $ ;

Nous allons utiliser EXPLAIN ANALYZE pour vérifier la vitesse de traitement de notre requête lorsque nous voulons trouver un utilisateur spécifique dans la base de données.

EXPLAIN ANALYSE SELECT email FROM users WHERE email = 'user890example.com' ;

Notre requête nous oblige à parcourir l'ensemble de la table à la recherche de l'enregistrement qui nous intéresse.

Ce processus est appelé analyse séquentielle. Dans ce cas, la lecture de l'ensemble du tableau et le filtrage de lignes particulières constituent la meilleure façon d'effectuer le travail.

PostgreSQL filtrera les lignes inutiles et retournera simplement celles qui nous intéressent. C'est vraiment la meilleure chose à faire dans ce cas. Le balayage séquentiel n'est pas toujours mauvais, il y a des cas où le balayage séquentiel est idéal.

4. Il est maintenant temps de vérifier la requête déjà effectuée sur la table qui possède l'INDEX UNIQUE. Définissons l'index et exécutons la requête.

 EATE UNIQUE INDEX index_email on users(email) ;
 EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com' ;

Cette fois-ci, PostgreSQL a tiré parti de l'analyse de l'index, car toutes les colonnes nécessaires se trouvent déjà dans l'index.

La sélection de quelques lignes seulement est très efficace lorsque l'on utilise l'index. En revanche, si le nombre de données sélectionnées est plus important, le balayage de l'index et du tableau prendra trop de temps.

Résumé

Comme vous pouvez le constater, le temps d'exécution d'une requête sur une colonne avec un index est beaucoup plus court (dans l'exemple montré, il s'agit d'une diminution de 1,267 ms à 0,111 ms, soit 91,24% !) La différence la plus importante est la manière dont PostgreSQL recherche l'enregistrement qui nous intéresse. Dans le premier cas, le moteur de base de données a dû rechercher dans toute la table l'enregistrement dont nous avions besoin. Dans le second cas, la structure de l'index est triée et unique, et le moteur sait donc où se trouve l'enregistrement, ce qui accélère considérablement le temps de traitement de la requête.

Dans le cas de bases de données volumineuses et de requêtes très complexes, des index correctement paramétrés peuvent accélérer considérablement le travail de votre application sans qu'il soit nécessaire d'augmenter la vitesse de la machine sur laquelle vous effectuez la recherche dans la base de données.

Il convient de rappeler que la création d'index sur chaque colonne n'est pas une bonne pratique. Des index établis accéléreront le travail de l'optimiseur lors de la recherche de données intéressantes, mais ralentiront en même temps l'insertion de nouvelles données et la mise à jour des données existantes.

En savoir plus :

– Vous avez besoin d'utiliser les frameworks JS les plus courants dans votre application Rails ? Stimulus.js peut être une alternative.

– L'heure d'une nouvelle réalité a sonné. L'ère du travail à distance a commencé il y a un mois

– Développement d'applications web : Pourquoi la technologie Ruby on Rails vaut-elle la peine d'être choisie ?