Visualização de índices no SQLite

Visualização de índices no SQLite: estrutura

• Importância dos índices: o SQLite é um SGBD amplamente usado em navegadores, aplicativos móveis e sistemas operacionais, sendo adequado para entender a estrutura de índices e explorar como eles são armazenados em disco e na memória.

Estrutura de nós e páginas

• Estrutura em B-tree: os índices do SQLite são armazenados em uma estrutura de B-tree, e cada nó possui vários filhos.
• Páginas e células: as páginas armazenam os dados das células e têm um link para a página filha à direita. As células incluem os dados do índice, o rowId e um link para a página filha à esquerda.

Análise do código-fonte do SQLite

• Exemplo de código: criação de uma função para analisar índices. Por exemplo, a função sqlite3DebugBtreeIndexDump lê e imprime o conteúdo do índice selecionado.
• Uso de Docker: é possível usar Docker para testar o dump do índice.

Visualização de índices

• Ferramenta de visualização: tentou-se usar a biblioteca d3-org-tree para visualizar a estrutura do índice, mas mostrar a estrutura em texto é mais simples.
• PHP ImageMagick: usa a extensão ImageMagick do PHP para gerar imagens com controle sobre design e espaçamento.

Vários exemplos de índices

• Índice básico: um índice simples composto por 1 registro.
• Diferentes quantidades de registros: índices com 1.000 e 1.000.000 de registros.
• Comparação de direção de ordenação: comparação entre índices com ordenação ASC e DESC.
• Dados baseados em expressão: criação de índices usando expressões.
• Índice único com valores NULL: o SQLite oferece suporte a índices únicos com valores NULL.
• Índice parcial: criação de um índice filtrando valores NULL.
• Índice multicoluna: criação de índices que incluem várias colunas.

Otimização de índices

• VACUUM e REINDEX: comandos usados para otimizar índices existentes.
• Dados de texto: strings curtas são armazenadas diretamente na célula do índice, enquanto textos longos são armazenados separadamente.
• Dados de ponto flutuante: criação de índices que incluem dados de ponto flutuante.

Conclusão

• Entendimento da estrutura de índices: compreensão da estrutura de índices do SQLite e de como a B-tree armazena e acessa dados.
• Importância da visualização: a visualização permitiu analisar e comparar vários tipos de índices.
• Planos futuros: pretende explorar visualizações de buscas baseadas em índices e consultas SQL interessantes.

[GN⁺]

Comentários do Hacker News

• Cada linha de uma tabela SQLite tem, por padrão, um rowId único, que funciona como uma chave primária quando não é definido explicitamente

  • Na prática, mesmo havendo chave primária, ele usa o rowId
  • Seria bom visualizar o índice de chave primária de uma tabela WITHOUT ROWID
  • Os dois índices parecem semelhantes, mas o segundo, com menos páginas, pode ser mais rápido
  • Ter menos nós não significa necessariamente ser "mais rápido"
  • O mais importante é a altura da árvore
  • O segundo ponto mais importante é o que acontece quando um valor é encontrado no índice
  • É especialmente importante em consultas de intervalo saber se é preciso carregar o restante de uma tabela separada (rowid) ou se os dados já estão ali (sem ROWID)

• Eu queria ver como um sistema de gerenciamento de banco de dados (DBMS) armazena e recupera índices no disco e na memória

  • Escolhi o SQLite como objeto de experimento
  • O SQLite tem algumas particularidades na forma como lida com tudo
  • Isso é ainda mais verdadeiro no processamento de consultas
  • O SQLite tende a preferir simplicidade em vez de desempenho, então sua implementação difere da de outros bancos de dados
  • O SQLite não compete com outros bancos de dados
  • Ele compete com arquivos JSON e XML e com armazenamento persistente
  • Isso significa que a forma como o SQLite implementa qualquer coisa quase não ensina como bancos de dados de fato funcionam

• O site é muito fácil de ler, então quero ler

• "indexes" é usado tanto como a forma de terceira pessoa do singular do verbo "to index" quanto como plural do substantivo "index"

  • Já "indices" é a forma plural tradicional e é especialmente comum em matemática e ciências
  • No inglês geral, "indexes" é comum, mas na área técnica "indices" é preferido para manter a precisão linguística
  • Usar "indices" aumenta a clareza ao distinguir a ação de indexar do plural de índice

• Acho que seria bom ver como o PostgreSQL faz a mesma coisa, comparar e deixar algumas anotações

• É possível gerar TGF para o yEd e obter uma variedade maior de layouts com menos trabalho