[MySQL] Partitionnement horizontal utile
Bonjour à tous !
Je me tourne vers vous pour quelques avis et/où retours d'exp.
Voici notre problématique, nous possédons une appli Java/J2EE qui attaque une BD MySQL (full table innoDB).
Sur cette BD, se trouve une table qui peut attendre les 100 millions d'enregistrement (voire plus). Cette table est attaquée la plupart du temps en écriture (70% du temps) et le reste en lecture (en fait c'est une table qui stocke les résultats de notre application).
Une de nos obligations est de fournir au client graphique de notre appli la possibilité de purger/archive/restaurer certaines lignes de cette table. (Ces actions se font selon une plage de temps précise, par exemple : je veux détruire tous les résultats entre les dates D1 & D2).
En résumé le client peut :
- Détruire une partie des résultats sans faire de sauvegarde
- Détruire une partie des résultats en faisant une sauvegarde (sous forme de dump réalisé avec mysqldump)
- Restaurer une partie des résultats grâce à une sauvegarde faite précédemment avec mysqldump.
Le problème étant que ces opérations (surtout la destruction) prennent énormément de temps.
- Nous avions donc mis en place un index sur la colonne des dates : ça a améliorait les perfos mais bon pas assez au goût du client
- Nous avons essayé une autre façon de faire, à savoir récupérer les identifiants primaires des résultats en fonction des dates saisies par le client (comme la colonne des primary key est auto incrémentée, on prend les 2 bornes des dates, on récupère les primary id correspondants, et on fait un DELETE FROM ... WHERE PRIMARY_KEY BETWEEN V1 & V2) : toujours pas top.
- Donc, nouvelle piste, et c'est là que j'ai besoin de vos conseils, nous pensions réalisé un partitionnement horizontal (partition en fonction de cette fameuse colonne de date).
Cependant, nous avons pu constater que MySQL souffrait d'une limitation assez pénible, à savoir qu'un table partitionnée ne supporte pas les foreign keys, et comme par hasard, cette table en question en possède un certain nombre.
J'ai pu lire des articles sur le sujet qui indiquent des contournements, comme par exemple, placer des triggers pré-insertion pour vérifier les contraintes de foreign keys...
- Pensez-vous que cela vaille le coup d'établir un partitionnement horizontal sur cette table, sachant qu'il va falloir mettre en place ce système de triggers pour vérifier les contraintes de keys?
- Sinon, avez-vous des tuyaux concernant la suppression massive de lignes sur une table InnoDB (je sais que, InnoDB n'est pas forcément prévu pour ça mais bon, la structure était déjà en place que nous n'étions pas nés...) ?
Merci pour vos futurs conseils :)
