NULL no MySQL (Performance & Storage)

O que exatamente faz o nulo fazer performance e armazenamento (espaço) no MySQL?

Por exemplo:

TINYINT: 1 Byte TINYINT com NULL 1 byte + de alguma forma armazena NULL?

Depende de qual mecanismo de armazenamento você usa.

No formato MyISAM, cada header de linha contém um campo de bits com um bit para cada coluna para codificar o estado NULL. Uma coluna que é NULL ainda ocupa espaço, então NULL não reduz o armazenamento. Veja https://dev.mysql.com/doc/internals/en/myisam-introduction.html

No InnoDB, cada coluna tem um “offset de início de campo” no header da linha, que é um ou dois bytes por coluna. O bit alto nesse deslocamento de início de campo está ativado se a coluna for NULL. Nesse caso, a coluna não precisa ser armazenada. Portanto, se você tiver muito NULL, seu armazenamento deverá ser reduzido significativamente. Consulte https://dev.mysql.com/doc/internals/en/innodb-field-contents.html

EDITAR:

Os bits NULL fazem parte dos headers das linhas, você não escolhe adicioná-los.

A única maneira de imaginar NULLs melhorando o desempenho é que, no InnoDB, uma página de dados pode caber em mais linhas se as linhas contiverem NULLs. Então seus buffers InnoDB podem ser mais efetivos.

Mas ficaria muito surpreso se isso proporcionasse uma vantagem significativa de desempenho na prática. Preocupar-se com o efeito que os NULLs têm no desempenho está no reino da micro-otimização. Você deve focar sua atenção em outro lugar, em áreas que dão maior impacto para o fanfarrão. Por exemplo, adicionando índices bem escolhidos ou aumentando a alocação de cache do database.

A resposta de Bill é boa, mas um pouco desatualizada. O uso de um ou dois bytes para armazenar NULL se aplica somente ao formato de linha InnoDB REDUNDANT. Desde o MySQL 5.0.3, o InnoDB usa o formato de linha COMPACT , que usa apenas um bit para armazenar um NULL (obviamente, um byte é o mínimo), portanto:

Espaço necessário para NULLs = CEILING (N / 8) bytes onde N é o número de colunas NULL em uma linha.

  • 0 NULL = 0 bytes
  • 1 – 8 NULLS = 1 byte
  • 9 – 16 NULLS = 2 bytes
  • 17 – 24 NULLS = 3 bytes
  • etc …

De acordo com o site oficial do MySQL sobre COMPACT vs REDUNDANT:

O formato de linha compacto reduz o espaço de armazenamento de linha em cerca de 20%, ao custo de aumentar o uso da CPU para algumas operações. Se a sua carga de trabalho é típica, limitada pelas taxas de acertos do cache e pela velocidade do disco, é provável que o formato compacto seja mais rápido.

Vantagem de usar NULLS em Strings ou Zeros Vazios:

  • 1 NULL requer 1 byte
  • 1 Empty String requer 1 byte (assumindo VARCHAR)
  • 1 Zero requer 4 bytes (assumindo INT)

Você começa a ver as economias aqui:

  • 8 NULLs requerem 1 byte
  • 8 strings vazias requerem 8 bytes
  • 8 zeros requerem 32 bytes

Por outro lado, sugiro usar NULLs sobre strings ou zeros vazios, porque eles são mais organizados, portáteis e exigem menos espaço. Para melhorar o desempenho e economizar espaço, concentre-se em usar os tipos de dados, índices e consultas adequados em vez de truques estranhos.

Mais informações em: https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html

Eu concordaria com Bill Karwin, embora eu adicionasse essas dicas do MySQL . O número 11 aborda isso especificamente:

Primeiro de tudo, pergunte a si mesmo se existe alguma diferença entre ter um valor de string vazia versus um valor NULL (para campos INT: 0 vs. NULL). Se não houver razão para ter ambos, você não precisa de um campo NULL. (Você sabia que o Oracle considera NULL e string vazia como sendo o mesmo?)

Colunas NULL requerem espaço adicional e podem adicionar complexidade às suas instruções de comparação. Apenas evite-os quando puder. No entanto, eu entendo que algumas pessoas podem ter razões muito específicas para ter valores NULL, o que nem sempre é uma coisa ruim.

Por outro lado, eu ainda utilizo null em tabelas que não têm toneladas de linhas, principalmente porque eu gosto da lógica de dizer NOT NULL.

Atualização Revisitando isso mais tarde, gostaria de acrescentar que eu pessoalmente não gosto de usar 0 em vez de NULL no database, e eu não o recomendo. Isso pode facilmente levar a muitos falsos positivos em sua aplicação se você não for cuidadoso.

dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html

O MySQL pode executar a mesma otimização em col_name IS NULL que pode ser usada para col_name = constant_value. Por exemplo, o MySQL pode usar índices e intervalos para procurar NULL com IS NULL