Os riscos de usar UUID como chave primária no SQLite

 (andersmurphy.com)
1 pontos por GN⁺ 4 시간 전 | 1 comentários | Compartilhar no WhatsApp
  • A implementação de chave primária no SQLite faz a ordem de armazenamento físico variar entre tabelas rowid comuns e tabelas WITHOUT ROWID, e usar um UUID4 aleatório como índice clusterizado gera rebalanceamento de B-tree e custo extra de paginação
  • A linha de base com rowid inteiro fica em cerca de 1 milhão de inserções por segundo em inserções em lotes de 1 milhão de linhas, enquanto UUID4 WITHOUT ROWID registrou tempos de inserção 14 a 16 vezes mais lentos
  • A característica não ordenada do UUID4 faz as linhas serem inseridas aleatoriamente na B-tree, e o profiling mostrou mais tempo gasto com balanceamento da árvore e operações de leitura e escrita
  • UUID7 WITHOUT ROWID usa UUIDs ordenados por tempo para reduzir o problema de ordenação do UUID4, mostrando tempos de inserção mais razoáveis, mas ainda é mais lento que uma chave inteira de 8 bytes por usar uma chave BLOB de 16 bytes
  • UUID4 WITH ROWID aproveita a sequencialidade do rowid oculto, mas a amplificação de escrita causada por dois índices e o custo de inserções aleatórias no índice permanecem, resultando em desempenho inferior ao de UUID7 WITHOUT ROWID

O que é um índice clusterizado?

  • Um índice clusterizado é o índice que determina a ordem física de armazenamento das linhas de uma tabela
  • Como as linhas só podem ser ordenadas fisicamente de uma única forma, cada tabela pode ter apenas um índice clusterizado
  • O índice clusterizado é a própria tabela, enquanto índices não clusterizados armazenam apenas a coluna indexada e um ponteiro para a localização dos dados reais da linha

Rowid

  • Toda tabela SQLite comum possui uma chave primária implícita de inteiro de 64 bits chamada rowid
  • Os dados da tabela são armazenados em uma estrutura B-tree com uma entrada por linha, usando o valor de rowid como chave
  • Na prática, o rowid é o índice clusterizado do SQLite, e a ordem física de armazenamento das linhas segue a ordem do rowid
Publicidade

WITHOUT ROWID

  • O SQLite oferece suporte a tabelas WITHOUT ROWID, que não têm rowid implícito
  • Em tabelas WITHOUT ROWID, a chave primária declarada passa a cumprir o papel de índice clusterizado
  • Tabelas SQLite com rowid são implementadas como uma B*-Tree em que todo o conteúdo fica nas folhas, enquanto tabelas WITHOUT ROWID usam uma B-Tree comum que armazena conteúdo tanto nas folhas quanto nos nós intermediários

Linha de base: chave primária inteira com rowid

  • A linha de base mede o tempo de inserção em lotes de 1 milhão de linhas em uma tabela rowid comum com a estrutura id INTEGER PRIMARY KEY, data BLOB
  • O total de linhas na tabela de resultados vai de 10 milhões a 100 milhões, com tempos medidos entre 692ms e 838ms
  • O desempenho de referência fica em aproximadamente 1 milhão de inserções por segundo

UUID4 WITHOUT ROWID

  • O teste com UUID4 usa uma tabela WITHOUT ROWID com a estrutura id BLOB PRIMARY KEY, data BLOB, inserindo valores random-uuid4-bytes como chave primária
  • Na tabela de resultados, os tempos medidos vão de 2649ms com 10 milhões de linhas a 12586ms com 100 milhões de linhas
  • O desempenho de inserção ficou de 14 a 16 vezes mais lento que a linha de base com rowid inteiro

Profiling

  • O diffgraph normalizado compara snapshots de profiling de INTEGER e UUID4 e mostra as diferenças em formato de flamegraph
  • A visualização normalizada ajusta o número total de amostras dos dois perfis para que a diferença relativa possa ser observada em porcentagens
  • Quadros azuis indicam que o tempo daquela função caiu no segundo perfil, o de UUID4, em relação ao INTEGER; quadros vermelhos indicam aumento no UUID4
  • A intensidade da cor representa a variação relativa no número de amostras do próprio quadro, ou seja, a mudança no self time delta
  • No diffgraph, há mais tempo gasto com rebalanceamento da árvore e operações de leitura e escrita
  • Por causa da natureza não ordenada do UUID4, as chaves são organizadas em ordem aleatória, e o SQLite precisa rebalancear continuamente a B-tree
Publicidade

UUID7 WITHOUT ROWID

  • UUID7 é um UUID ordenado por tempo, uma abordagem que pode eliminar o problema de ordenação do UUID4
  • O teste com UUID7 também foi executado em uma tabela WITHOUT ROWID com a estrutura id BLOB PRIMARY KEY, data BLOB
  • Na tabela de resultados, os tempos medidos foram de 1372ms com 10 milhões de linhas a 1258ms com 100 milhões de linhas
  • UUID7 WITHOUT ROWID voltou a números mais razoáveis do que UUID4 WITHOUT ROWID, mas ainda teve desempenho inferior à linha de base
  • A diferença é que a chave primária UUID em BLOB tem 16 bytes, enquanto a chave primária inteira tem 8 bytes

UUID4 WITH ROWID

  • O teste com UUID4 WITH ROWID usa a tabela id BLOB PRIMARY KEY, data BLOB sem WITHOUT ROWID
  • Nessa configuração, o rowid oculto é o índice clusterizado, e a vantagem do rowid é a sequencialidade
  • A desvantagem é que a tabela passa a ter dois índices, com a consequente amplificação de escrita
  • Na tabela de resultados, os tempos medidos vão de 2003ms com 10 milhões de linhas a 7119ms com 100 milhões de linhas
  • UUID4 WITH ROWID não teve desempenho tão bom quanto UUID7 WITHOUT ROWID, pois, mesmo sem ser o índice clusterizado, a estrutura ainda precisa construir continuamente o índice com inserções aleatórias

Conclusão

  • No SQLite, usar UUID como chave primária pode afetar fortemente o desempenho de inserção dependendo do índice clusterizado e da ordenação das chaves
  • O problema de UUIDs aleatórios não se limita ao SQLite e também se estende a outros bancos de dados que usam índices clusterizados
  • Todo o código do benchmark está disponível no repositório no GitHub

Leituras relacionadas

1 comentários

 
GN⁺ 4 시간 전
Comentários no Lobste.rs

  • Bom. Também seria interessante ver números para o caso em que, em uma tabela rowid, a chave inteira não é monotonicamente crescente, mas aleatória
    Um ponto importante que o texto não menciona é que o índice primário de tabelas rowid é uma árvore B+ e tabelas without rowid usam árvore B
    Então, em geral, quando o tamanho médio do registro ultrapassa certo limiar, a segunda opção deixa de ser ideal. Isso porque os nós internos do índice armazenam o registro inteiro e, se bem me lembro, o manual do SQLite sugere como regra prática 1/20 do tamanho da página

  • Dedicaram bastante esforço para medir o desempenho de UUID, mas não consideraram chaves naturais
    Seja inteiro, UUID ou outra forma, chaves substitutas adicionam complexidade, não acrescentam informação e escondem erros de normalização
    O autor cita “evitar duplicatas” como motivo para usar UUID, mas isso não é motivo. Toda linha de toda tabela em todo banco de dados deve ter pelo menos uma chave, isto é, um conjunto de colunas que a identifique de forma única
    Um banco sem essa chave contém informação duplicada e fica vulnerável a inconsistências lógicas. Se essa chave já existe, não há necessidade de chave substituta. Se a chave natural já existe e é imposta, dizer que “é necessário por desempenho” implica custo extra e complexidade desnecessária, então isso precisa ser demonstrado

    • Posso estar sem imaginação, mas, ao lidar com dados vindos do mundo real, em geral acho difícil encontrar uma chave natural de verdade
      Valores que parecem únicos à primeira vista muitas vezes não são tão únicos quanto se espera, ou valores que se supõe imutáveis acabam precisando mudar
      Já com uma chave substituta, posso definir a identidade dentro do meu sistema sem depender de como outra pessoa definiu identificabilidade — ou, em geral, deixou de definir direito
      Há exceções. Se eu mesmo defini todo o modelo e os dados não vêm do chamado mundo real, a chave natural faz mais sentido. Mas, ao projetar um esquema para dados do mundo real que provavelmente nunca foram totalmente normalizados, chaves substitutas costumam ser úteis
    • Há bastante utilidade prática imediata em chaves substitutas inteiras monotonicamente crescentes
      Poder referenciar qualquer linha com um inteiro simplifica bastante o acesso, e também é fácil para humanos memorizar e usar em consultas
      Se for monotonicamente crescente, isso por si só também carrega informação. É informação redundante, mas ainda assim é um fato
      A velocidade de busca também é otimizada. É um caso quase ideal para indexação com árvores B, bitmaps etc.
      Acho que as pessoas usam UUID principalmente por confusão. Normalmente a lógica é ofuscar a chave e torná-la imprevisível, mas desisti de tentar entender por que acham que isso deve ser imposto até à chave primária, em vez de usar um identificador separado para esse objetivo
    • A expressão “evitar duplicatas” não aparece no post. Na verdade, o texto nem explica por que usar UUID

  • UUID versão 7 tem um timestamp de 48 bits no início, então não fica distribuído aleatoriamente desse jeito. Portanto, também deve reduzir paginação excessiva e rebalanceamento

    • Sim. O texto trata de UUID7

  • As pessoas realmente usam UUID como chave primária? Quando é preciso UUID, qual é a vantagem de fazer isso em vez de mantê-lo como chave secundária?

    • É por escalabilidade. Se você precisa gerar IDs únicos de forma distribuída, em alta velocidade, em muitos computadores e vários datacenters ao redor do mundo — por exemplo, em uploads para o S3 — não vai querer colocar um lock em um único inteiro crescente. Sincronizar locks globalmente é lento
      GUIDs e UUIDs resolvem esse problema por construção
      v1 e v6 codificam ID da máquina e timestamp, então se aproximam de inteiros autoincrementais com um namespace por máquina
      Muita confusão vem do fato de muita gente assumir que UUID é aleatório. Isso só vale para v4 e, infelizmente, escolher v4 tem custo
      Muitas vezes é preciso algum grau de determinismo, como em v3, v5 e v7, para organizar os dados ou por garantias de desempenho e colisão
    • Mesmo que UUID aleatório ou um valor aleatório uniformemente distribuído fique como chave secundária, as inserções ainda ficam mais lentas. Mesmo não sendo um índice clusterizado, ainda haverá inserções aleatórias na árvore B desse índice