Visualización de índices en SQLite

Visualización de índices en SQLite: estructura

• Importancia de los índices: SQLite es un DBMS ampliamente usado en navegadores, apps móviles y sistemas operativos, por lo que es ideal para entender la estructura de los índices y explorar cómo se almacenan en disco y en memoria.

Estructura de nodos y páginas

• Estructura de árbol B: Los índices de SQLite se almacenan en una estructura de árbol B, y cada nodo tiene varios hijos.
• Páginas y celdas: Las páginas almacenan datos de celdas y tienen un enlace a la página hija derecha. Las celdas incluyen los datos del índice, el rowId y un enlace a la página hija izquierda.

Análisis del código fuente de SQLite

• Ejemplo de código: Se escribe una función para analizar índices. Por ejemplo, la función sqlite3DebugBtreeIndexDump lee e imprime el contenido del índice seleccionado.
• Uso de Docker: Se puede usar Docker para probar el volcado de índices.

Visualización de índices

• Herramienta de visualización: Se intentó usar la librería d3-org-tree para visualizar la estructura del índice, pero mostrar la estructura en texto resulta más simple.
• PHP ImageMagick: Se generan imágenes usando la extensión ImageMagick de PHP, lo que permite controlar el diseño y el espaciado.

Ejemplos de distintos índices

• Índice básico: Un índice simple compuesto por 1 registro.
• Distintas cantidades de registros: Índices con 1,000 y 1,000,000 de registros.
• Comparación de dirección de ordenamiento: Comparación entre índices con orden ASC y DESC.
• Datos basados en expresiones: Creación de índices usando expresiones.
• Índice único con valores NULL: SQLite admite índices únicos con valores NULL.
• Índice parcial: Creación de un índice filtrando valores NULL.
• Índice de múltiples columnas: Creación de índices que incluyen varias columnas.

Optimización de índices

• VACUUM y REINDEX: Comandos usados para optimizar índices existentes.
• Datos de texto: Las cadenas cortas se almacenan directamente en las celdas del índice, mientras que los textos largos se almacenan por separado.
• Datos de punto flotante: Creación de índices que incluyen datos de punto flotante.

Conclusión

• Comprensión de la estructura de los índices: Se entiende la estructura de índices de SQLite y la forma en que el árbol B almacena y accede a los datos.
• Importancia de la visualización: La visualización permitió analizar y comparar distintos índices.
• Planes futuros: Se planea explorar la visualización de búsquedas basadas en índices y consultas SQL interesantes.

[GN⁺]

Opinión en Hacker News

• Cada fila de una tabla de SQLite tiene por defecto un rowId único, que funciona como una clave primaria si no se define una explícitamente

  • En la práctica, incluso si existe una clave primaria, se usa rowId
  • Sería bueno visualizar el índice de clave primaria de una tabla WITHOUT ROWID
  • Los dos índices pueden verse similares, pero el segundo, con menos páginas, podría ser más rápido
  • Tener menos nodos no significa necesariamente que sea más "rápido"
  • Lo más importante es la altura del árbol
  • Lo segundo más importante es qué ocurre cuando encuentras el valor en el índice
  • Es especialmente importante en consultas por rango si hay que cargar el resto desde una tabla separada (rowid) o si los datos ya están ahí (WITHOUT ROWID)

• Quería ver cómo un sistema de gestión de bases de datos (DBMS) almacena y busca índices en disco y en memoria

  • Elegí SQLite como objeto de experimentación
  • SQLite tiene algunas peculiaridades en la forma en que maneja todo
  • Esto es aún más notable en el procesamiento de consultas
  • SQLite tiende a preferir la simplicidad sobre el rendimiento, así que su implementación es distinta a la de otras bases de datos
  • SQLite no compite con otras bases de datos
  • Compite con archivos JSON y XML para almacenamiento persistente
  • Eso significa que la forma en que SQLite implementa cualquier cosa casi no dice nada sobre cómo funcionan las bases de datos reales

• El sitio web es muy fácil de leer, así que dan ganas de leerlo

• "indexes" se usa tanto como tercera persona singular del presente del verbo "to index" como plural del sustantivo "index"

  • En cambio, "indices" es la forma plural tradicional y se usa especialmente en matemáticas y ciencias
  • En el inglés general, "indexes" es común, pero en el ámbito técnico suele preferirse "indices" para mantener la precisión lingüística
  • Usar "indices" ayuda a distinguir entre la acción de indexar y el plural de índice, lo que aporta más claridad

• Sería bueno ver cómo PostgreSQL hace lo mismo, compararlo y dejar algunas notas

• Se podría generar tgf para yEd y así obtener una mayor variedad de diseños con menos trabajo