Depuis sa version 5.1 MySQL a fait beaucoup d'heureux puisqu'il assure enfin le support du partionnement.

Pour remettre dans le contexte, par analogie avec un disque dur, le partionnement de tables permet 3 grandes fonctionnalités :

• Organiser proprement ses données
• Accéder plus rapidement aux données
• Pouvoir supprimer simplement des données

Dans notre cas, nous utilisons le partionnement avant tout pour des raisons de performances.

Je ne reviendrai pas sur toutes les possibilités qu'offre MySQL en la matière (horizontal, vertical, etc.), vous pouvez directement consulter la doc officielle à l'adresse suivante :

http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

Nous avons dû mettre en place du partionnement du table à l'agence pour faire face à des problématiques de performances.

Si vous travaillez avec de grosses tables de log et/ou reporting, vous devez avoir conscience que plus le temps passe, plus vos tables grossissent et plus vos requêtes sont longues à s'éxécuter, bien quelles soient bien indexées.

Pour palier à ça, on peut mettre en place du partioning.

Pour qu'il soit efficace, il faut analyser vos requêtes pour voir sur quelle colonne vous devez partitionner. L'idée est de réduire considérablement la portée de la requête.

Dans notre cas présent, nous avions des tables de reporting de plus de 6M de recordsets, constitués en seulement 3 mois. Les requêtes commençaient à mettre plus de 2 secondes à s'éxécuter et ca n'allait qu'empirer.

Nos requêtes de reporting permettaient d'extraire des statistiques entre 2 dates, l'idée était donc de partitionner par range basé sur la date d'ajout du recordset.

Par défaut, les 2 bornes de notre applicatif constituaient une semaine, et après analyse, nous avions environ 500K recordsets par semaine.

Le calcul était simple : 2M de recordsets par mois > quasi 25M par an, nous allons partitionner par semaine sur la date d'ajout.

Notre table de base de 6M de recordsets était la suivante :

CREATE TABLE IF NOT EXISTS `like` (
  `like_id` bigint(20) NOT NULL COMMENT 'Id',  
  `like_ip` varchar(15) NOT NULL COMMENT 'Adresse IP',
  `like_user_agent` longtext COMMENT 'User Agent',
  `like_date_add` datetime NOT NULL COMMENT 'Date d''ajout',
  `news_id` bigint(20) NOT NULL COMMENT 'News',
  PRIMARY KEY (`like_id`,`like_date_add`),  
  KEY `news_id` (`news_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='News - Likes';

Vous remarquerez que la clé primaire est composée de la vrai PK ainsi que de la colonne sur laquelle on souhaite partionner : c'est hélas une obligation MySQL, la colonne sur laquelle on effectue le partionnement doit être unique ou doit être partie d'une clause unique sur plusieurs colonnes sans quoi vous prendrez cette erreur à la tentative de partionnement :

#1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

La requête pour partionner (généré avec un script PHP) :

ALTER TABLE like PARTITION BY RANGE (to_days(like_date_add))(
||t||PARTITION Y2013W01 VALUES LESS THAN (to_days('2013-01-07')),
||t||PARTITION Y2013W02 VALUES LESS THAN (to_days('2013-01-14')),
||t||PARTITION Y2013W03 VALUES LESS THAN (to_days('2013-01-21')),
||t||PARTITION Y2013W04 VALUES LESS THAN (to_days('2013-01-28')),
||t||PARTITION Y2013W05 VALUES LESS THAN (to_days('2013-02-04')),
||t||PARTITION Y2013W06 VALUES LESS THAN (to_days('2013-02-11')),
||t||PARTITION Y2013W07 VALUES LESS THAN (to_days('2013-02-18')),
||t||PARTITION Y2013W08 VALUES LESS THAN (to_days('2013-02-25')),
||t||PARTITION Y2013W09 VALUES LESS THAN (to_days('2013-03-04')),
||t||PARTITION Y2013W10 VALUES LESS THAN (to_days('2013-03-11')),
||t||PARTITION Y2013W11 VALUES LESS THAN (to_days('2013-03-18')),
||t||PARTITION Y2013W12 VALUES LESS THAN (to_days('2013-03-25')),
||t||PARTITION Y2013W13 VALUES LESS THAN (to_days('2013-04-01')),
||t||PARTITION Y2013W14 VALUES LESS THAN (to_days('2013-04-08')),
||t||PARTITION Y2013W15 VALUES LESS THAN (to_days('2013-04-15')),
||t||PARTITION Y2013W16 VALUES LESS THAN (to_days('2013-04-22')),
||t||PARTITION Y2013W17 VALUES LESS THAN (to_days('2013-04-29')),
||t||PARTITION Y2013W18 VALUES LESS THAN (to_days('2013-05-06')),
||t||PARTITION Y2013W19 VALUES LESS THAN (to_days('2013-05-13')),
||t||PARTITION Y2013W20 VALUES LESS THAN (to_days('2013-05-20')),
||t||PARTITION Y2013W21 VALUES LESS THAN (to_days('2013-05-27')),
||t||PARTITION Y2013W22 VALUES LESS THAN (to_days('2013-06-03')),
||t||PARTITION Y2013W23 VALUES LESS THAN (to_days('2013-06-10')),
||t||PARTITION Y2013W24 VALUES LESS THAN (to_days('2013-06-17')),
||t||PARTITION Y2013W25 VALUES LESS THAN (to_days('2013-06-24')),
||t||PARTITION Y2013W26 VALUES LESS THAN (to_days('2013-07-01')),
||t||PARTITION Y2013W27 VALUES LESS THAN (to_days('2013-07-08')),
||t||PARTITION Y2013W28 VALUES LESS THAN (to_days('2013-07-15')),
||t||PARTITION Y2013W29 VALUES LESS THAN (to_days('2013-07-22')),
||t||PARTITION Y2013W30 VALUES LESS THAN (to_days('2013-07-29')),
||t||PARTITION Y2013W31 VALUES LESS THAN (to_days('2013-08-05')),
||t||PARTITION Y2013W32 VALUES LESS THAN (to_days('2013-08-12')),
||t||PARTITION Y2013W33 VALUES LESS THAN (to_days('2013-08-19')),
||t||PARTITION Y2013W34 VALUES LESS THAN (to_days('2013-08-26')),
||t||PARTITION Y2013W35 VALUES LESS THAN (to_days('2013-09-02')),
||t||PARTITION Y2013W36 VALUES LESS THAN (to_days('2013-09-09')),
||t||PARTITION Y2013W37 VALUES LESS THAN (to_days('2013-09-16')),
||t||PARTITION Y2013W38 VALUES LESS THAN (to_days('2013-09-23')),
||t||PARTITION Y2013W39 VALUES LESS THAN (to_days('2013-09-30')),
||t||PARTITION Y2013W40 VALUES LESS THAN (to_days('2013-10-07')),
||t||PARTITION Y2013W41 VALUES LESS THAN (to_days('2013-10-14')),
||t||PARTITION Y2013W42 VALUES LESS THAN (to_days('2013-10-21')),
||t||PARTITION Y2013W43 VALUES LESS THAN (to_days('2013-10-28')),
||t||PARTITION Y2013W44 VALUES LESS THAN (to_days('2013-11-04')),
||t||PARTITION Y2013W45 VALUES LESS THAN (to_days('2013-11-11')),
||t||PARTITION Y2013W46 VALUES LESS THAN (to_days('2013-11-18')),
||t||PARTITION Y2013W47 VALUES LESS THAN (to_days('2013-11-25')),
||t||PARTITION Y2013W48 VALUES LESS THAN (to_days('2013-12-02')),
||t||PARTITION Y2013W49 VALUES LESS THAN (to_days('2013-12-09')),
||t||PARTITION Y2013W50 VALUES LESS THAN (to_days('2013-12-16')),
||t||PARTITION Y2013W51 VALUES LESS THAN (to_days('2013-12-23')),
||t||PARTITION Y2013W52 VALUES LESS THAN (to_days('2013-12-30')),
||t||PARTITION unknown VALUES LESS THAN MAXVALUE
);

Ainsi, au lieu de taper sur 6M de recordsets, nous tapons sur des partitions de 500K enregistrements.

A noter que la partition unknown est la partition poubelle dans laquelle MySQL va "ranger" les recordsets qui ne matchent avec aucune clause de partition.

D'un point de vue performance pure, cela a fait baisser de 80% le temps d'éxécution des requêtes car ce temps est exponentiel par rapport à la taille de la table et plus les tables sont petites, mieux MySQL utilise efficacement ses index.

A ce propos, ne croyez pas que le partionnement résoud tout, vous devez bien entendu le cumuler avec une utilisation intelligente des index.

Réflechissez bien au range de partionnement, c'est la clé ! Si votre applicatif permet de tirer des stats par semestre, il est abérrant de partionner par semaine car MySQL procédera à un UNION ALL de 24 partitions.

A noter que MySQL gère très bien les 3 cas suivants :

• A la déclaration du partionnement, il réorganise automatiquement toutes les données dans les partitions adéquates.
• A l'insertion (INSERT), il insère dans la partition adéquate.
• A la lecture (SELECT), il analyse vos clauses where pour aller taper dans la (ou les) partition(s) adéquates.

Il ne gère en revanche pas la création automatique de partition en fonction de votre range. Dans notre cas, nous devons lancer un cron annuel qui va créer l'ensemble des partitions de l'année N+1 à l'aide d'un alter MySQL.

Enfin, le partionnement est également très utile dans le cas de cron de nettoyage de données obsolètes. Si vous souhaitez supprimer toutes les données d'une semaine, la requête suivante suffit :

ALTER TABLE like DROP PARTITION Y2013W10

Elle sera beaucoup plus rapide qu'un classique :

DELETE 
FROM 
||t||news n
WHERE
||t||DATE_FORMAT(n.news_date_add,'%Y-%v') = '2013-10'