Optimiser sa base de données: diviser ses données (pour mieux regner).

Comme nous l'avons vu dans l'article précédent, plus vos enregistrements contiennent de données, moins votre serveur de base de données sera efficace dans sa recherche (le temps d'ouverture d'un enregistrement dépend du volume de données à lire).

Aussi quel est le but de mysql ? Pour quel usage l'utilisez-vous ? En quoi est-il le plus efficace ?
La grande erreur des sites d'aujourd'hui est d'utiliser une base mysql comme l'endroit de stockage de toutes les données du projet: une poubelle à données.
Or, autant mysql est efficace dans la recherche de petits enregistrements, autant il est totalement inefficace en tant que poubelle à données. La bonne pratique est d'utiliser mysql uniquement comme moteur de recherche, et d'y laisser uniquement les données de recherche, ainsi que les données a afficher dans des listes (les previews).

5 type de données existent dans un site:

• Des données de recherche (les ids, date, etc...)
• Les données d'affichages (previews dans des listes et données complètes). Par exemple, la description d'un membre ou d'un produit: Le preview sera un abstract de taille fixe (char180 ou char255), et la donnée sera de taille variable (text).
• Les données "froides": les données qui ne seront jamais affichées ou utilisées (à part peut-être dans l'interface d'admin) mais qu'on garde pour des raisons légales ou des raisons système. Exemple: l'ip ayant servi à la création d'un compte, la date de cloture d'un compte, etc...
• Les fichiers utilisateurs (La photo a afficher sur son profil, son cv, etc...)
• Les fichiers "statiques" (liés au site et non aux données): les fichiers javascript, css, les images du thème du site, etc...

Seules les données de recherche et d'affichage-preview concernent mysql.
Nous utiliserons en parallèle de mysql une base de données non-relationelle (par exemple noSQL) et un serveur de média.

Le but est de laisser dans mysql uniquement les données "recherche" et "liste", en mettant la base de données non-relationelle en "poubelle à données". La base NoSQL sera garante de toutes les données: mysql pourra etre vidé a chaque instant et re-rempli à partir des données de noSQL.

Le serveur de média, quand a lui, hebergera tous les fichiers binaires. Par exemple, la photo d'un produits sera sur le serveur de media, qui vous retournera un identifiant d'une dixaine-vingtaine de lettres. Cette identifiant sera bien plus facile a stocker dans mysql et dans NoSQL. Il stockera aussi les fichiers statiques. Ces fichiers seront directement accessibles via une url qui mènera au serveur de média.

La puissance de NoSQL réside dans le fait qu'il n'aura a s'occuper que d'un enregistrement à la fois, en lecture comme en écriture. NoSQL est très rapide pour trouver un enregistrement à partir de son id, toute autre recherche étant proscrite. Il pourra en revanche retourner ou modifier les données de cet enregistrement. Bien que NoSQL mette lui aussi beaucoup de temps à ouvrir ou modifier un gros enregistrement, cela sera négligeable car il n'aura à traiter qu'un enregistrement par requête, tandis que mysql sera appelé pour retourner les données d'une recherche ou d'une liste paginée.

Sur mysql, si l'on suit la bonne pratique, chaque table de liaison aura de 3 à 5 champs, et chaque table "objet" (table d'utilisateur, table de produits, etc...) devra avoir une dixaine-quinzaine de champs maximum.

Le désavantage du système de division de données est que pour chaque modification de données, il vous faudra requêter 2 bases (mysql + nosql), voir 3 avec le serveur de medias.
Néanmoins, NoSQL sera requêté lors de l'affichage d'un seul enregistrement (profil, produit) et le serveur de média sera requêté en dehors du chargement de la page (la page construit l'url de l'image, les image sont chargées par la suite).
Diviser les données sur plusieurs serveurs est donc particulièrement efficace dans un site de lecture (les chateaux de la loire, les blogs, rue du commerce, amazon, forums) avec au final bien plus de lecture que d'écriture, et proscrit dans les sites à fort taux d'écriture (boursorama, etc...).


Conclusion:
Diviser les données permet à mysql de passer de plusieurs centaines de gigas de données à quelques centaines de méga au plus. Sa rapidité en est ainsi augmenté d'environ 20% lors d'une recherche.
Mysql étant réduit à un outil de recherche, la modification des données devra se faire sur mysql et noSQL, augmentant le temps d'écriture.
Ceci dit, la base de données non-relationnelle, ainsi que le serveur de média, pourront, eux, être mutualisés avec d'autres projets sans trop de perte de perf. Si noSQL commence à ramer, changez le disque dur par un disque dur électronique SSD et mettez-le tout seul sur un NAS.