Optimiser sa base de données: Choisir ses types de données

Dans le billet précédent, nous avons laissé à mysql uniquement les champs de recherche (ids, date) et les champs qui vont être affiché dans une liste.

Le choix des champs peut accélerer la recherche dans mysql.

Par exemple, savez vous que si vous mettez tous vos champs à "NOT NULL", mysql passera par une branche de recherche plus rapide qui, justement, ne testera pas les "NULL"s ?

Ensuite, viens le choix du type des ids. En effet, mysql ira plus vite à rechercher un TINYINT qu'un DOUBLE. Rappelez vous: les performance lors de la recherche d'un enregistrement (même sur un index) dépend du volume de données. Ainsi, si vous avez des millions d'enregistrements dans une table (et donc des millions d'ids dans l'index),  si nous ne pouvons pas jouer sur le nombre d'enregistrement, nous pouvons jouer sur la taille de l'ids.

Ainsi les types de clés numériques conseillées sont:

• UNSIGNED TINYINT: 0-255. Ce type est conseillé sur des tables fixes (sur une table contenant "Monsieur", "Madame" ou "Mademoiselle", par exemple). Nous plus bas comment ne plus avoir de jointures avec ce genre de tables.

• UNSIGNED SMALLINT: 0-65535. Peut définir par exemple le nombre de billets dans un blog, ou le nombre de sous-parties d'un forum. Ce type n'est que peu utilisé.

• UNSIGNED MEDIUMINT: 0-16.7 millions. Le type le plus utilisé. Il peut correspondre aux ids de questions sur un forum, aux ids de blogs, les utilisateurs, commentaires, etc. Quand les projets prennent de l'ampleur, ce type peut être augmenté à l'aide d'un "alter table" au besoin. C'est le type à verifier/changer quand votre projet dépasse le stade de "proof of concept".

• UNSIGNED INT: 0-4.2milliards. Ce type est utilisé sur des données faciles à entrer et presque jamais recherchées (typiquement: les votes sur blog).

• UNSIGNED BIGINT: 0-18446744073709551615. Juste pour info: ils y'a moins de grains de sable sur terre que ce chiffre là. Mettez vous en plein desert et repensez-y.

• Double: Type imprécis après une certaine valeur, inutile dans une primary key. 

Les types ENUM et SET:
Les types ENUM et SET permettent d'apporter un choix de valeur dans une colonne. SET peut stocker plusieurs valeurs (je suis a la fois "Monsieur", "Madame" et "Mademoiselle"), ENUM ne peut en stocker une seule.
Dans votre enregistrement, c'est en interne un smallint que vous stockerez, mysql fera la traduction. Les ENUM et les SET vous eviterons de creer des tables inutiles, et surtout, des jointures inutiles !

Les charactères:
Dans votre base mysql, vous ne devez pas stocker de champs textes variables: ils seront en duplication dans votre base NOSQL. Si vous desirez mettre un abstract dans mysql (par exemple pour l'affichage d'une liste de profil sur meetic, avec le début de leur description), utilisez des champs de taille fixe comme char(180) ou char(255). Un char ne pourra pas dépasser la taille de 255 caractères.


Les dates:

• Year: un octet
• Date: 3 octets
• TIMESTAMP: 4 octets.
• DateTime: 8 octets

Utilisez donc les dates suivant vos besoins. Si vous faites uniquement des comparaisons de date, utilisez timestamp, mais si vous les affichez, utilisez date ou datetime.

Blob et text:
blob et text sont des données à taille variable. Non seulement il vont vous pourrir vos performances, mais surtout: il n'ont rien a foutre là (mysql n'est pas un lieu de stockage de données mais un moteur de recherche !). Text ira donc dans votre noSQL, et le blob ira dans votre serveur de média.

Conclusion:

En choisissant le type de ses champs et en les mettant tous en "NOT NULL", mysql augmente ses temps de recherche (environ 30%). Cela ne se verra pas forcement dans une requête unitaire, mais votre serveur tiendra mieux la charge en période de stress.