MySQL BLOB COMPRESS / UNCOMPRESS

Pour stocker certains champs volumineux dans une table MySQL, il peut être intéressant de compresser les données de ce champ afin d'économiser de l'espace disque.

MySQL propose nativement 2 fonctions :

• COMPRESS('Lorem ipsum...') : cette fonction compresse la chaîne de caractère qu'on lui passe en paramètre et retourne les octets correspondants au message compressé
• UNCOMPRESS(field) : c'est la réciproque de COMPRESS, elle permet de restituer la chaîne de caractères originale à partir des octets de cette chaine, compressée

La première chose à faire est d'utiliser un champ de type BLOB (Binary Large Object). Voici par exemple une table toute simple qui utilise un champ BLOB :

CREATE TABLE `une_table` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`date_creation` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`bigdata` BLOB NULL COMMENT 'Ce champs est compressé',
	PRIMARY KEY (`id`)
)

Une fois la table crée, les fonctions de compression / décompression s'utilisent très facilement. Voilà comment écrire des données compressées :

INSERT INTO `une_table` (`bigdata`) VALUES (COMPRESS('Lorem ipsum...'));

Et voici comment décompresser ces données pour les lire :

SELECT `id`, `date_creation`, UNCOMPRESS(`bigdata`) FROM `une_table`;

Malheureusement on est obligé d'appeler COMPRESS et UNCOMPRESS explicitement, il n'existe pas d'attribut compressé pour une colonne (qui permettrait de faire la compression et décompression de manière totalement transparente).

Les autres formes de compression

Il existe d'autres méthodes de compression dans MySQL, elles présentent toutes des avantages et des inconvénients.

Allez donc jeter un oeil sur cette page : versionning de fichier excel.

Myisampack

Myisampack est un utilitaire qui permet de compresser les colonnes d'une table utilisant le moteur MyISAM (le moteur de table par défaut dans MySQL).

Cela permet en général de réduire de plus de moitié la taille d'une table (mais le taux de compression dépend beaucoup des données elles-mêmes), tout en conservant de bonnes performances en lecture.

En contrepartie, la table devient read-only (lecture seule), c'est-à-dire qu'on ne peut plus faire d'INSERT INTO ou d'UPDATE dedans. C'est pour ça qu'en général, quand on utilise myisampack, on utilise le partitionnement (plusieurs tables) : par exemple une table par mois, comme le fait Piwik (un système de mesure d'audience de site internet écrit en PHP/MySQL).

Archive engine

L'autre solution, c'est le moteur de table Archive, qui est conçu comme son nom l'indique pour gérer l'archivage de données (comme des logs). Le moteur archive implique 2 contraintes :

• Impossible de mettre à jour les données (UPDATE), seuls les in SELECT et INSERT sont possibles. Cela permet en contrepartie d'avoir d'excellentes performances en écriture.
• Il n'y a pas d'index, du coup une table archive a de mauvaises performances en lecture.

Encore faim ? allez lire ça : vieillir une photo !