En temps que développeur, vous allez soit commencer une nouvelle application, soit intervenir sur une application existante. Dans le premier cas, vous avez tout intérêt à partir sur de bonnes bases, dans le second cas, vous avez diagnostiqué des lenteurs au niveau de la base. Dans tous les cas, vous êtes au bon endroit.

Deux choses importantes avant de commencer :

  • avant de vouloir optimiser une base de données SQL, il faut savoir qu’une bonne requête est une requête qui n’est pas faite ! Nous verrons donc dans la partie 3 consacrée à java que le cache est primordiale pour accélérer une application.
  • en tant que dev, on a très souvent tendance à développer sur des bases de données avec très peu d’enregistrements (en général, ceux générés par nos propres tests). Or, si vous savez (ou vous espérez) que votre base est amenée à grossir, vous DEVEZ lui mettre des données factices à ras le gosier. Quitte à prendre une demi journée pour développer un script qui va générer de la donnée aléatoire. Croyez moi, rien qu’avec ce conseil, vous éviterez bien des déconvenues quand votre programme sera trituré par l’équipe de sadiques testeurs.

Ne tardons plus, voyons quels sont les moyens simples dont dispose un dév pour gagner des perfs.

Ps : les exemples SQL ci-dessous seront fait sur une syntaxe Postgresql.

1. Les indexes

Définition d’un index :

Indexer une colonne de base de données, c’est trier les données de cette colonne de manière à ce que la base de données puisse faire ses recherches plus efficacement. Comme pour l’ordre des mots dans un dictionnaire.

Attention toutefois : le résultat de l’indexation est enregistré dans dans une structure spécifique sur disque dur. Ce qui fait qu’indexer des données prendra de la place.

De même, à l’enregistrement d’une donnée, la mise à jour de l’index prend du temps. Il faut donc prendre ces inconvénients en compte pour choisir quelles sont les tables/champs à indexer.

Pourquoi se concentrer sur les indexes ?

En tant que développeur, vous êtes le seul à savoir quelles sont les requêtes qui vont être faites sur la base de données. Mieux, vous êtes le seul à savoir quels champs vont servir pour vos recherches. Donc vous tenez là un moyen simple d’améliorer les perfs de votre application.

Les indexes simples

Par index simple, nous entendons un index sur une seule colonne.

La syntaxe pour ajouter un index est la suivante :

CREATE INDEX 'nom_index' ON 'table' ('colonne');

Comme dit plus haut, vous devez indexer les colonnes qui vont être liées aux recherches de votre application.

Exemple :

Vous avez le schéma suivant :

Personne
last_name
first_name
zip_code
phone

Si vous avez un formulaire de recherche qui permet de rechercher une personne par nom OU téléphone. Les champs a indexer sont donc « last_name » et « phone ». Les autres n’auront aucun intérêt puisqu’ils n’entrent pas en compte dans votre recherche.

Les indexes multi colonnes

Si votre moteur de recherche et du type recherche par nom ET téléphone, alors vous devez faire une indexation multi colonnes.

CREATE INDEX 'nom_index' ON 'table' ('colonne1', 'colonne2');

Dans notre exemple, on fait donc un index sur last_name et phone. Ce qui a pour conséquence d’être vraiment plus performant que si on avait fait deux indexes, un sur last_name et un sur phone.

Les indexes couvrants

Je ne l’ai pas dit plus tôt mais que fait la base lorsqu’elle a parcouru son index et trouvé les données voulues ? Elle va récupérer ces données dans la table de stockage.

Pour gagner encore plus de temps, les indexes couvrants sont vos amis.

Ils correspondent à des indexes multi-colonnes qui correspondent à toutes les données de la clause SELECT. Donc, la partie récupération des données dans la table de stockage n’a plus lieu d’être.

Exemple avec notre table personne :

SELECT last_name, phone FROM personne WHERE  last_name = 'toto' AND phone = '0102030405';

Dans ce cas, en parcourant la table d’indexation, la base aura déjà toutes les données nécessaires pour répondre à notre besoin.

Ce qui n’aurait pas été le cas avec la requête suivante :

SELECT last_name,  first_name, phone FROM personne WHERE  last_name = 'toto' AND phone = '0102030405';

Conclusion : il faut récupérer le strict nécessaire pour avoir des requêtes rapides.

Le net regorge de ressources sur les indexes, mais ce site est une perle : https://use-the-index-luke.com/fr/

2. Le partitionnement de table

Partitionner des tables, c’est diviser une table en plusieurs « morceaux physiques ». Ce découpage est totalement transparent pour le développeur (point crucial).

Exemple, si on a 10 millions d’enregistrements dans notre table personne, on pourrait choisir de partitionner les données par département. En tant que dev, vous interrogez toujours la table personne. Pas question pour vous de devoir par exemple faire une requête sur la table personne_83, personne_13 etc pour avoir les personnes de la région paca. Dans cet exemple, la BDD va ranger les données dans autant de « morceau de table » que de département.

Autant dire que les temps de vos requêtes vont s’améliorer drastiquement avec un partitionnement.

Une belle explication du partitionnement par microsoft

3. Les vues matérialisées

Vous avez indexé et partitionné votre BDD ? Vous pouvez la bichonner encore plus en utilisant les vues matérialisées (vues indexées chez microsoft).

Une vue matérialisée, c’est tout simplement une vue dont le résultat va être enregistré. Il est ensuite possible d’indexer et même de partitionner ce résultat. Dit autrement, la BDD n’aura plus besoin de faire une requête complexe avec jointures etc. Je vous laisse imaginer par vous même le gain de performance que vous pouvez obtenir avec un tel outil.

Vu que les données sont issues d’autres tables, une vue matérialisée à une durée de vie qu’il va falloir définir en fonction de la mise à jour des données. Il y a également des stratégies de mises à jour incrémentales, mais ceci est un autre sujet…

4. Données froides

Les données froides sont les données anciennes qui sont rarement accédées. Puisqu’elles sont peu utilisées, l’idée est de désencombrer les tables de données « chaudes », afin de garantir une certaine performance.

Au stade du développement logiciel, il va falloir que vous fassiez une analyse pour définir les données froides. En effet, ce n’est pas la même durée si vos données sont obsolètes au bout de 5 jours, ou au bout de 12 mois.

Une fois définie, vous pouvez stocker cette donnée soit dans une table « archive », soit carrément dans une autre base. A noter que nous parlons d’un point de vue développeur. La gestion de la donnée froide prend une toute autre envergure pour un DBA dans une boite qui gère de la forte volumétrie. Dans ce cas, l’enjeux est de stocker la donnée sur un support de moindre coût. Un petit article sympa à ce sujet

Vous l’aurez compris, l’avantage de s’attaquer à ce problème, c’est que vos tables de données chaudes sont plus petites et donc plus rapides.

L’inconvénient, c’est que vous devez prendre en compte au niveau de votre code la nécessité d’accéder à de la donnée archivée.

Partager sur les réseaux