Visualisation des index SQLite

Visualisation des index SQLite : structure

• Importance des index : SQLite est un SGBD largement utilisé dans les navigateurs, les applications mobiles et les systèmes d’exploitation, ce qui en fait un bon sujet pour comprendre la structure des index et explorer leur mode de stockage sur disque et en mémoire.

Structure des nœuds et des pages

• Structure en B-arbre : les index de SQLite sont stockés dans une structure en B-arbre, et chaque nœud possède plusieurs enfants.
• Pages et cellules : les pages stockent les données des cellules et contiennent un lien vers la page enfant de droite. Les cellules incluent les données d’index, le rowId et un lien vers la page enfant de gauche.

Analyse du code source de SQLite

• Exemple de code : écriture d’une fonction pour analyser les index. Par exemple, la fonction sqlite3DebugBtreeIndexDump lit et affiche le contenu de l’index sélectionné.
• Utilisation de Docker : Docker peut être utilisé pour tester le dump d’index.

Visualisation des index

• Outil de visualisation : la bibliothèque d3-org-tree a été envisagée pour visualiser la structure des index, mais une représentation textuelle de la structure s’est révélée plus simple.
• PHP ImageMagick : l’extension ImageMagick de PHP est utilisée pour générer des images permettant de contrôler la mise en page et l’espacement.

Divers exemples d’index

• Index de base : un index simple composé d’un seul enregistrement.
• Nombre variable d’enregistrements : des index contenant 1 000 et 1 000 000 d’enregistrements.
• Comparaison du sens de tri : comparaison entre des index triés en ASC et en DESC.
• Données basées sur des expressions : création d’index à l’aide d’expressions.
• Index uniques avec des valeurs NULL : SQLite prend en charge les index uniques contenant des valeurs NULL.
• Index partiels : création d’index en filtrant les valeurs NULL.
• Index multicolonnes : création d’index incluant plusieurs colonnes.

Optimisation des index

• VACUUM et REINDEX : commandes utilisées pour optimiser des index existants.
• Données textuelles : les chaînes courtes sont stockées directement dans les cellules d’index, tandis que les textes longs sont stockés séparément.
• Données en virgule flottante : création d’index contenant des données en virgule flottante.

Conclusion

• Compréhension de la structure des index : compréhension de la structure des index de SQLite et de la manière dont les B-arbres stockent et permettent d’accéder aux données.
• Importance de la visualisation : la visualisation a permis d’analyser et de comparer différents index.
• Projets futurs : visualisation des recherches basées sur les index et exploration de requêtes SQL intéressantes à venir.

[GN⁺]

Avis Hacker News

• Chaque ligne d’une table SQLite possède par défaut un rowId unique, qui agit comme une clé primaire lorsqu’elle n’est pas définie explicitement

  • En pratique, même s’il existe une clé primaire, rowId est utilisé
  • Il serait intéressant de visualiser l’index de clé primaire des tables WITHOUT ROWID
  • Les deux index semblent similaires, mais le second, avec moins de pages, peut être plus rapide
  • Avoir moins de nœuds ne signifie pas nécessairement être « plus rapide »
  • Le plus important est la hauteur de l’arbre
  • Le deuxième point le plus important est ce qui se passe lorsqu’on trouve une valeur dans l’index
  • Le fait de devoir charger le reste depuis une table séparée (rowid) ou d’avoir déjà les données (sans ROWID) est particulièrement important pour les requêtes de plage

• Je voulais voir comment un système de gestion de base de données (DBMS) stocke et recherche les index sur disque et en mémoire

  • J’ai choisi SQLite comme terrain d’expérimentation
  • SQLite a quelques particularités dans sa manière de tout gérer
  • C’est particulièrement vrai pour le traitement des requêtes
  • SQLite tend à privilégier la simplicité à la performance, et son implémentation diffère donc de celle des autres bases de données
  • SQLite n’est pas en concurrence avec les autres bases de données
  • Il est en concurrence avec les fichiers JSON et XML pour la persistance
  • Cela signifie que la façon dont SQLite implémente quoi que ce soit n’apprend presque rien sur le fonctionnement réel des bases de données

• Le site est très facile à lire, donc j’ai envie de le lire

• « indexes » est utilisé à la fois comme forme verbale à la 3e personne du singulier du verbe « to index » et comme pluriel du nom « index »

  • À l’inverse, « indices » est la forme plurielle traditionnelle, particulièrement fréquente en mathématiques et en sciences
  • En anglais courant, « indexes » est souvent utilisé, mais dans le domaine technique, « indices » est préféré pour conserver une précision terminologique
  • Employer « indices » améliore la clarté en distinguant l’action d’indexer du pluriel d’index

• Ce serait bien de voir comment PostgreSQL effectue la même tâche, de comparer et d’ajouter quelques notes

• On peut générer du tgf pour yEd afin d’obtenir plus de mises en page avec moins de travail