[ABD] Démo sur l'utilité des buffer (tempon) pour un SGBD

Le buffer pour un SGBD est un espace de plusieurs blocs en mémoire centrale. L'objectif de son utilisation est de limiter le nombre d'accès au disque (qui sont très coûteux en terme de temps de réponse) en gardant quelques pages (quelques blocs) dans le buffer.
Plusieurs algorithmes peuvent être utilisés comme l'algorithme FIFO (First In/First Out, l'algorithme le plus simple et le plus intuitif) et l'algorithme LRU (Least Recently Used). Ce dernier est l'algorithme le plus utilisé; il convient bien aux exigences des SGBD et à la nature des traitements effectués par les différentes applications basées sur des bases des données.
Dans cet exemple, nous allons essayer de montrer, dans la pratique, le gain réalisé grâce aux buffers.
La base des données exemple est constituée d'une seule table personne :

Create Table Personne(
  id BigInt Primary Key AUTO_INCREMENT,
  nom char(30),
  prenom char(30)
);

Pour obtenir des valeurs un peu élevé, nous allons procéder à un remplissage aléatoire de cette table par environ 100.000 enregistrements. Nous allons utiliser un petit code en Java comme suit :

import java.sql.*;
import java.util.Random;

public class ChargerDonneesAleatoires{

  public static String PRENOMS[] = {"Mohammed", "Abubakr", "Omar", "Othmane", "Ali", "Khaled",
     "Soufiane", "Karim", "Houcine", "Rabeh"};
  public static String NOMS[] = {"Benamer", "Kaouche", "Brik", "Laggoune", "Bensalem", "Sahraoui",
     "Benabdallah", "Didouche", "Benbouali", "Amirouche"};

  final static String URLSGBD = "jdbc:mysql://localhost:3306/ABD2";
  final static String USER = "username"; // Mettez votre propre username
  final static String PASSWORD = "password"; // Mettez votre propre mot de passe
  
  
  public static void main(String args[]){
    long nombre = 1000000;
    
    if(args.length >= 1){
      nombre = Long.parseLong(args[0]);
    }
    
    // Chargement des données SQL
    try {
    
      Class.forName("com.mysql.jdbc.Driver");
      Connection con = DriverManager.getConnection(URLSGBD, USER, PASSWORD);
      
      Statement st = con.createStatement();
      
      System.out.println("Chargement de " + nombre + " ligne(s)");
      
      for(long i = 0; i < nombre; i++){
 
 Random random = new Random();
 
 int posNoms = random.nextInt(10);
 int posPrenom = random.nextInt(10);
 
 String requete = "Insert into Personne (nom, prenom) Values ('" + NOMS[posNoms] +"', '" + PRENOMS[posPrenom] + "')";
 st.executeUpdate(requete);
 
      }
      
      st.close();
      con.close();
      
    }catch(SQLException sqle){
      System.out.println("Erreur avec la base des donénes");
      sqle.printStackTrace();
    }catch(Exception e){
      System.out.println("Erreur inconnue");
      e.printStackTrace();
    }
    
    
  }
  
  
}

Ce code peut être exécuté avec ou sans paramètres, si aucun paramètre n'est passé, le code insère 1.000.000 enregistrements, cela risque de prendre plus qu'un demi journée. Par la ligne suivante, nous allons lancer le code pour insérer 10 lignes (le driver de MySQL est dans le même répertoire) :

java -cp "mysql-connector-java-5.1.38-bin.jar:." ChargerDonneesAleatoires 10

Essayant maintenant d'exécuter une commande coûteuse. Pour cela nous allons tenter une requête qui :
N'implique pas une condition sur la clé primaire (indexée par défaut),
Fait appel à une fonction d'agrégation (cela ajoute une fraction de seconde et permet d'obtenir un affichage plus simple pour les 100.000 enregistrements de la base).
La requête est la suivante :

Select nom, count(nom)
From Personne
Group by nom;

Une première exécution sous mysql nous donnera les valeurs suivantes :

mysql> Select nom, count(nom)
    -> From Personne
    -> Group by nom;
+-------------+------------+
| nom         | count(nom) |
+-------------+------------+
| Amirouche   |      10001 |
| Benabdallah |       9918 |
| Benamer     |      10013 |
| Benbouali   |      10068 |
| Bensalem    |       9881 |
| Brik        |       9962 |
| Didouche    |      10142 |
| Kaouche     |      10163 |
| Laggoune    |       9878 |
| Sahraoui    |       9984 |
+-------------+------------+
10 rows in set (0.18 sec)

Ainsi, le temps d'exécution est d'environ 0,18 secondes. Cette requête était la première à être exécutée, ainsi, le buffer était vide et son remplissage nécessitait le maximum d'accès disque.
Reprenons la même requête (sans quitter et relancer mysql) :

mysql> Select nom, count(nom) From Personne Group by nom;
+-------------+------------+
| nom         | count(nom) |
+-------------+------------+
| Amirouche   |      10001 |
| Benabdallah |       9918 |
| Benamer     |      10013 |
| Benbouali   |      10068 |
| Bensalem    |       9881 |
| Brik        |       9962 |
| Didouche    |      10142 |
| Kaouche     |      10163 |
| Laggoune    |       9878 |
| Sahraoui    |       9984 |
+-------------+------------+
10 rows in set (0.00 sec)

L'effet du buffer se montre clairement : le temps d'exécution est réduit à 0.0 seconde; toutes les informations nécessaires (blocs résultats du traitement de la première requête) sont encore en mémoire centrale et seule l'opération d'agrégation est exécutée sans aucun accès disque.