Dessine-moi MySQL : structure d’un index MyISAM et InnoDB

24 octobre 2008 par arnaud

Deuxième exemplaire (traduire « 2ème essai ») concernant ces schémas « à main levée », au menu du jour une comparaison entre la structure d’un index MyISAM et celle d’un index InnoDB :

J’ai tenté de tenir compte de vos commentaires précédents, notamment sur les majuscules, est-ce plus lisible ?

Selon les « règles » établies pour cette série « Dessine-moi MySQL », les schémas doivent être suffisamment explicites pour ne pas nécessiter d’explications supplémentaires…
Cependant, vu que je n’ai pas 10 ans d’école d’arts graphiques derrière moi (ça s’est vu ?), et que chaque règle a son exception, je rajouterai tout de même ceci :

Sous MyISAM, il n’y a pas de différence entre une clé primaire et un index « classique » du point de vue de leur structure, ils sont implémentés de la même façon : ils sont triés et pointent vers le ou les enregistrements correspondants. A noter que MyISAM stocke les enregistrements dans l’ordre où ils sont insérés.

En revanche sous InnoDB, une clé primaire est un index dit « clustered« . L’index est dans ce cas « accolé » aux données, il n’y a pas de « saut » supplémentaire à effectuer pour aller chercher l’enregistrement une fois qu’on est positionné sur la valeur recherchée de la clé primaire.
Avec InnoDB, les enregistrements sont stockés non pas dans l’ordre d’insertion mais triés selon la clé primaire.

Rajoutons pour terminer que sous InnoDB, un index secondaire contient systématiquement la clé primaire. C’est en effet la clé primaire qui permet de retrouver l’enregistrement recherché.
Deux conséquences :
- il faut bien choisir sa clé primaire (concise de préférence puisque celle-ci sera présente dans chaque index secondaire).
- le fait que la clé primaire soit présente sur chaque index secondaire permet « d’émuler » la présence d’un index supplémenaire sur votre table, autrement dit il existe des stratégies d’optimisation au rayon des covering index à ce sujet…

Ce dernier point sonne comme un proverbe chinois ? Alors ne manquez pas le prochain épisode sur les covering index, le support de cours est justement sous vos yeux ;)

Mots-clefs : ,

5 commentaires sur “Dessine-moi MySQL : structure d’un index MyISAM et InnoDB”

  1. StephaneC dit :

    J’ajoute 2 petites précisions pour compléter le schéma :
    les index sont structurés sous forme d’arbres (BTree) alors que le schéma fait plutôt penser à une hash table
    MyISAM stocke index et données dans 2 fichiers séparés alors qu’InnoDB stocke tout dans son tablespace

  2. arnaud dit :

    Exact, merci Stéphane pour la précision.

    Pour davantage d’infos sur la différence entre BTREE et HASH index : http://www.dbnewz.com/2008/05/19/choisir-limplementation-de-ses-index-b-tree-ou-hash-quelles-differences/

  3. [...] faire suite au dernier schéma sur les structures comparées d’un index MyISAM et InnoDB, ce billet a pour but de détailler [...]

  4. [...] avec l’index login. Si la dernière phrase ne vous a paru très claire, regardez donc cet article et celui-là [...]

  5. Julien dit :

    Salut, je réagit par rapport à ta question sur les majuscules (par-ce que je sais que ça peut « ralentir » l’écriture) : Oui, c’est beaucoup plus claire. Pour être franc, j’ai mis plusieurs minutes à déchiffrer l’autre image (celle de la réplication master-slave)

    ++

Laisser une réponse