Fichier Log en base de données, optimisation du traitement

Fichier Log en base de données

L'avantage d'avoir un fichier Log en base de données et d'avoir immédiatement les données sous la main pour un traitement statistique via PHP/MySql.

Les inconvénients résident :

  • dans la taille de la base de données qui peut augmenter considérablement sur les sites web avec beaucoup de trafic.
  • dans la lenteur de traitement due à cette taille.

Le problème de la base de données, c'est que lorsque vous faites une recherche sur une date, le serveur va parcourir l'ensemble des enregistrements de votre table à la recherche de cette valeur, ce qui peut être très long lorsque vous avez plusieurs centaines de milliers de lignes. Mais c'est le fonctionnement logique d'un SGBD.

La caractéristique d'un fichier Log dans une table réside dans son immuabilité contrairement à une table classique (comme une table clients par exemple) qui peut faire l'objet de mises à jour ou de suppressions. Elle réside également dans l'ajout chronologique des lignes. C'est à dire que l'identifiant des enregistrements est toujours croissant en respectant cette chronologie.

Nous allons mettre à profit cette caractéristique pour accélérer considérablement les traitements.

Pour se faire, j'ai créé la table supplémentaire suivante :

Associée à une tâche CRON qui s'exécute toutes les nuits :

<?php

// codes d'accès à la base de données

define('DB_LOGS_HOST', 'xxxxxxxx');  
define('DB_LOGS_USER', 'xxxxxxxx');  
define('DB_LOGS_PASS', 'xxxxxxxx');  
define('DB_LOGS_NAME', 'xxxxxxxx'); 

$date_start2 = strtotime(Date('Y-m-d').'- 1 day');
	
$date_start = Date('Y-m-d', $date_start2);

// à noter que pour des raisons pratique je stocke la date sur 3 champs (stat_year / stat_month / stat_day)

// connexion à la base de données
$db = mysqli_connect(DB_LOGS_HOST, DB_LOGS_USER, DB_LOGS_PASS, DB_LOGS_NAME) or die ("Error during connection ".mysqli_error($db));

// stats est la table qui stock le fichier Log

$sql = 'SELECT stat_id
			FROM stats
				WHERE stat_year = '.substr($date_start, 0, 4).'
					AND stat_month = '.substr($date_start, 5, 2).'
					AND stat_day =  '.substr($date_start, 8, 2).'
						ORDER BY stat_date_time ASC
							LIMIT 1';

$sql = mysqli_query($db, $sql) or die ('Erreur SQL !'.mysqli_error($db));

$ligne = mysqli_fetch_array($sql);

$stat_date_id_start = $ligne['stat_id'];

//
$sql = 'SELECT stat_id
			FROM stats
				WHERE stat_year = '.substr($date_start, 0, 4).'
					AND stat_month = '.substr($date_start, 5, 2).'
					AND stat_day =  '.substr($date_start, 8, 2).'
						ORDER BY stat_date_time DESC
							LIMIT 1';

$sql = mysqli_query($db, $sql) or die ('Erreur SQL !'.mysqli_error($db));

$ligne = mysqli_fetch_array($sql);

$stat_date_id_end = $ligne['stat_id'];

//
$sql = 'INSERT INTO stats_dates_ids
			SET stat_date_id_date = "'.$date_start.'",
				stat_date_id_start = '.$stat_date_id_start.',
				stat_date_id_end = '.$stat_date_id_end;
				
$sql = mysqli_query($db, $sql) or die ('Erreur SQL !'.mysqli_error($db));

mysqli_close($db);

?>

Le but de ce traitement est de récupérer pour chaque date le premier et le dernier identifiant automatique dans votre table statistiques.

De cette façon, et par la suite, pour calculer vos statistiques pour une date, un mois, ou une plage de date, plutôt que de faire :

SELECT mes_champs
	FROM mes_stats 
		WHERE ma_date_time LIKE "'.$date_jour.'%"

Vous allez faire :

SELECT mes_champs
	FROM mes_stats 
		WHERE stat_id >= '.$stat_date_id_start.'
			AND stat_id <= '.$stat_date_id_end.'

en utilisant les valeurs $stat_date_id_start et $stat_date_id_end que vous aurez préalablement récupérer via une petite requette.

En jettant un coup d'oeil sur un extrait de la table vous comprenez l'économie réalisée pour, par exemple, calculer les stats du 12 juillet. Votre requête parcourra les enregistrements de l'id 493539 à l'id 494030 au lieu d'avoir à parcourir près de 500.000 lignes !

Le traitement de notre page affichant un résumé plutôt exhaustif des stats d'un jour passe ainsi de 7.024 secondes à 0.104 seconde et le calcul mensuel passe de 9.341 secondes à 1.097 seconde !

D'autres optimisations sont possibles comme le stockage des résultats, après les calculs, dans une table ad hoc de la base de données pour alors un affichage pratiquement instantané.

A vous de jouer !

Tags MySql