O que os bancos de dados relacionais obtêm ao definir um tipo de dados predefinido para cada coluna?

44

Estou trabalhando com um banco de dados SQL agora, e isso sempre me deixou curioso, mas as pesquisas do Google não dão muito certo: por que os tipos de dados estritos?

Entendo por que você tem alguns tipos de dados diferentes, por exemplo como a diferenciação entre dados binários e texto simples é importante . Em vez de armazenar os 1s e 0s de dados binários como texto simples, agora entendo que é mais eficiente armazenar os dados binários como seu próprio formato.

Mas o que eu não entendo é o benefício de ter tantos tipos de dados diferentes:

  • Por que mediumtext , longtext e text ?
  • Por que decimal , float e int ?
  • etc.

Qual é o benefício de informar ao banco de dados "Haverá apenas 256 bytes de dados de texto simples nas entradas desta coluna." ou "Esta coluna pode ter entradas de texto de até 16.777.215 bytes"?

É um benefício de desempenho? Em caso afirmativo, por que saber o tamanho da entrada antes da mão ajuda no desempenho? Ou melhor, é algo completamente diferente?

    
por john doe 26.05.2017 / 20:13
fonte

9 respostas

50

SQL é um linguagem estaticamente tipada . Isso significa que você precisa saber que tipo de variável (ou campo, neste caso) é antes de poder usá-la. Isso é o oposto de linguagens tipificadas dinamicamente, onde isso não é necessariamente o caso.

Em sua essência, o SQL é projetado para definir dados ( DDL ) e acessar dados ( DML ) em um mecanismo de banco de dados relacional . A tipagem estática apresenta vários benefícios em relação à digitação dinâmica para esse tipo de sistema.

  • Índices , usados para acessar rapidamente registros específicos, funcionam muito bem quando o tamanho é fixo. Considere uma consulta que utiliza um índice, possivelmente com vários campos: se os tipos e tamanhos de dados forem conhecidos antecipadamente, posso comparar rapidamente meu predicado (cláusula WHERE ou critério JOIN) com os valores do índice e localizar os registros desejados mais rapidamente .

  • Considere dois valores número inteiro . Em um sistema de tipo dinâmico, eles podem ser de tamanho variável (pense em Java BigInteger , ou inteiros de precisão arbitrária integrados do Python). Se eu quiser comparar os números inteiros, eu preciso saber o comprimento do bit primeiro. Este é um aspecto da comparação de inteiros que é largamente oculto pelas linguagens modernas, mas é muito real no nível da CPU. Se os tamanhos forem fixos e conhecidos com antecedência, uma etapa inteira será removida do processo. Mais uma vez, os bancos de dados devem ser capazes de processar zilhões de transações o mais rápido possível. A velocidade é rei.

  • O SQL foi projetado nos anos 70. Nos primeiros dias da microcomputação, a memória estava em um prêmio. A limitação de dados ajudou a manter os requisitos de armazenamento sob controle. Se um inteiro nunca ultrapassar um byte, por que alocar mais armazenamento para ele? Isso é um desperdício de espaço na era da memória limitada. Mesmo nos tempos modernos, esses bytes extras podem aumentar e eliminar o desempenho do cache de uma CPU. Lembre-se, esses são mecanismos de banco de dados que podem atender centenas de transações por segundo, não apenas seu pequeno ambiente de desenvolvimento.

  • Ao longo das linhas de armazenamento limitado, é útil poder ajustar um único registro em uma única página na memória. Depois de percorrer uma página, há mais falhas na página e mais acesso lento à memória. Mecanismos mais recentes têm otimizações para tornar isso um problema menor, mas ainda está lá. Ao dimensionar os dados adequadamente, você pode reduzir esse risco.

  • Mais ainda, nos tempos modernos, o SQL é usado para se conectar a outras linguagens através do ORM ou < href="https://en.wikipedia.org/wiki/Open_Database_Connectivity"> ODBC ou alguma outra camada. Algumas dessas linguagens têm regras sobre a exigência de tipos estáticos strongs. É melhor estar em conformidade com os requisitos mais rigorosos, pois as linguagens dinamicamente digitadas podem lidar com tipos estáticos mais facilmente do que o contrário.

  • O SQL suporta a tipagem estática porque os mecanismos de banco de dados precisam disso para desempenho, como mostrado acima.

É interessante notar que existem implementações de SQL que não são strongmente tipadas. O SQLite é provavelmente o exemplo mais popular desse mecanismo de banco de dados relacional. Então, novamente, ele é projetado para uso single-threaded em um único sistema, então as preocupações de desempenho podem não ser tão pronunciadas quanto em, e. um banco de dados Oracle corporativo atendendo milhões de solicitações por minuto.

    
por 26.05.2017 / 20:48
fonte
24

Primeiro: texto simples é binário (não são nem os caracteres UTF8 ou ASCII "0" e "1", mas os bits on / off reais)

Dito isto, algumas das razões são:

  • Restrições de negócios / design: permitindo que o número 7626355112 na coluna ALTURA da tabela PESSOA esteja incorreto. Permitir "Howya" na coluna DATE de uma INVOICE estaria errado.
  • Código propenso a erros: você não precisa escrever código para garantir que os dados recuperados de uma coluna de datas sejam realmente uma data. Se os tipos de coluna fossem dinâmicos, você teria que fazer muitas verificações de tipo ao lê-los.
  • Eficiência de computação: Se uma coluna for do tipo INTEGER e você SUM (), o RDBMS não precisará aplicar aritmética de ponto flutuante.
  • Eficiência de armazenamento: afirmando que uma coluna é VARCHAR (10) permite que o RDBMS aloque o espaço com mais precisão.
  • Integridade referencial e unicidade: PK (ou FKs) de uma tabela não deve permitir floats, pois a igualdade de ponto flutuante é complicada, portanto você deve declará-los em um tipo não-flutuante, como caracteres ou inteiro.
  • Existem RDBMSs com tipos de coluna dinâmicos (não estritos) (SQLite) . Ele usa o conceito de "afinidade de tipo" enquanto ainda permite que você insira praticamente qualquer coisa em qualquer coluna sem reclamar. Existem trade-offs que não serão discutidos aqui. Veja esta questão .
por 26.05.2017 / 20:41
fonte
8

É para que o código subjacente no qual o banco de dados é gravado possa alocar e usar registros de tamanho fixo; se souber que um campo específico pode conter de 0 a 256 caracteres de texto, ele poderá alocar um bloco de 256 bytes para armazenar em.

Isso torna as coisas muito mais rápidas, por exemplo você não precisa alocar armazenamento adicional como o usuário digita, já que um determinado campo sempre inicia x bytes no registro que uma pesquisa ou selecione naquele campo sabe sempre verificar x bytes em cada registro, etc.

    
por 26.05.2017 / 20:42
fonte
6

Quando as colunas de um banco de dados recebem tipos definidos, os tipos geralmente são definidos para ter um determinado tamanho em bits. Como resultado:

1) quando o mecanismo de banco de dados está percorrendo as linhas em uma tabela, ele não precisa fazer nenhuma análise para determinar onde cada registro termina, ele pode apenas saber que cada linha consiste em, digamos, 32 bytes para obter o próximo registro, basta adicionar 32 bytes à localização atual dos registros.

2) ao procurar um campo em uma linha, é possível saber um deslocamento exato para esse campo novamente sem analisar nada, portanto, as pesquisas de coluna são uma operação aritmética simples, em vez de uma operação de processamento de dados potencialmente cara.

    
por 26.05.2017 / 20:40
fonte
3

Você perguntou por que os DBMSs têm tipos de dados estáticos.

  1. Velocidade de pesquisa. O ponto principal de um DBMS é armazenar muito mais dados do que você poderia carregar em um programa. Pense "todos os cartões de crédito gerados no mundo nos últimos dez anos". Para pesquisar esses dados com eficiência, os tipos de dados de comprimento fixo são úteis. Isso é especialmente verdadeiro para dados estruturados, como carimbos de data e números de conta. Se você sabe com o que está lidando com antecedência, é mais fácil carregar em índices eficientes.

  2. Integridade e restrições. É mais fácil manter os dados limpos se tiver tipos de dados fixos.

  3. Histórico. Os RDBMSs começaram quando os computadores tinham apenas alguns megabytes de RAM, e o armazenamento em escala de terabytes era extremamente caro. Salvar uma dúzia de bytes em cada linha de uma tabela pode economizar milhares de dólares e horas nessas circunstâncias.

  4. A maldição da base de clientes. Os RDBMSs hoje são muito complexos e altamente otimizados, e estão em uso há décadas, acumulando dados. Eles estão maduros. Eles trabalham. Uma falha de RDBMS que resulta em perda de dados em larga escala é extremamente rara nos dias de hoje. Mudar para algo com um sistema de digitação de dados mais flexível não vale o custo ou risco para a maioria das organizações.

Analogia: pode ser óbvio que os sistemas de metrô urbanos funcionariam melhor (mais silenciosos, mais rápidos, mais eficientes) em um trilho mais estreito. Mas como você vai mudar todos os trilhos do sistema de metrô de Nova York para realizar essas melhorias? Você não é, então você otimiza o que você tem.

    
por 28.05.2017 / 13:11
fonte
3

Em geral, quanto mais detalhes você informa ao banco de dados sobre o que está armazenando, mais ele pode tentar otimizar várias métricas de desempenho relacionadas a esses dados, como a quantidade de espaço a ser alocada disco ou quanta memória para alocar ao recuperá-lo.

Why mediumtext, longtext, and text?

Não tenho certeza de qual banco de dados você está usando , então vou ter que adivinhar: Eu acho que dois desses tipos de dados têm limites superiores, um deles não. O uso de tipos de dados para texto com limites superiores informa ao banco de dados quanto espaço de armazenamento será necessário para cada registro. Também é possível que alguns bancos de dados possam ter diferentes maneiras de armazenar textos grandes (possivelmente ilimitados) versus pequenos textos de tamanho fixo (isso pode variar de acordo com o banco de dados, verifique o manual para ver o seu).

Why decimal, float, and int?

Diferentes níveis de precisão exigem diferentes quantidades de armazenamento, e nem todo uso requer maiores graus de precisão. Por exemplo, veja aqui: link

O Oracle possui um grande número de tipos numéricos diferentes com requisitos de armazenamento diferentes e recursos diferentes em termos de nível de precisão e tamanho do número que podem ser representados.

    
por 26.05.2017 / 20:42
fonte
2

Até certo ponto, é histórico.

Era uma vez, os dados tabulares eram armazenados em arquivos compostos de registros de comprimento fixo, por sua vez compostos de campos pré-definidos, de modo que um determinado campo era sempre do mesmo tipo e no mesmo lugar em cada registro. Isso tornou o processamento eficiente e limitou a complexidade da codificação.

Adicione alguns índices a esse arquivo e você terá o início de um banco de dados relacional.

À medida que os bancos de dados relacionais evoluíram, eles começaram a introduzir mais tipos de dados e opções de armazenamento, incluindo campos de texto ou binários de comprimento variável. Mas isso introduziu registros de tamanho variável e quebrou a capacidade de localizar registros de forma consistente por meio de cálculos ou campos por meio de um deslocamento fixo. Não importa, as máquinas são muito mais poderosas hoje do que eram naquela época.

Às vezes, é útil definir um tamanho específico para um campo para ajudar a impor alguma lógica empresarial - digamos, 10 dígitos para um número de telefone da América do Norte. A maior parte do tempo é apenas um pouco de legado de computação.

    
por 28.05.2017 / 03:43
fonte
1

Se um banco de dados usar registros de tamanho fixo, qualquer registro no banco de dados continuará a caber, no mesmo local, mesmo que seu conteúdo seja alterado. Por outro lado, se um banco de dados tentar armazenar registros usando exatamente a quantidade de armazenamento necessária para seus campos, alterar o nome de Emma Smith para Emma Johnson pode fazer com que seu registro seja grande demais para caber em seu local atual. Se o registro for movido para algum lugar com espaço suficiente, qualquer índice que rastreie onde ele está precisaria ser atualizado para refletir o novo local.

Existem várias maneiras de reduzir o custo associado a essas atualizações. Por exemplo, se o sistema mantiver uma lista de números de registros e locais de dados, essa lista será a única coisa que precisaria ser atualizada se um registro se mover. Infelizmente, tais abordagens ainda têm um custo significativo (por exemplo, manter um mapeamento entre números de registro e locais exigiria que a recuperação de registros exigiria uma etapa extra para recuperar os dados associados a um determinado número de registro). Usar registros de tamanho fixo pode parecer ineficiente, mas torna as coisas muito mais simples.

    
por 26.05.2017 / 23:49
fonte
1

Para muito do que você faz como desenvolvedor web, não há necessidade de entender o que está acontecendo "por baixo do capô". Há momentos, no entanto, quando ajuda.

What is the benefit of telling the database "There'll only be 256 bytes of plain text data in entries to this column." or "This column can have text entries of up to 16,777,215 bytes"?

Como você suspeita, o motivo é fazer com eficiência. As abstrações vazam . Uma consulta como SELECT author FROM books pode ser executada rapidamente quando o tamanho de todos os campos a mesa é conhecida.

Como Joel diz,

How does a relational database implement SELECT author FROM books? In a relational database, every row in a table (e.g. the books table) is exactly the same length in bytes, and every fields is always at a fixed offset from the beginning of the row. So, for example, if each record in the books table is 100 bytes long, and the author field is at offset 23, then there are authors stored at byte 23, 123, 223, 323, etc. What is the code to move to the next record in the result of this query? Basically, it’s this:

pointer += 100;

One CPU instruction. Faaaaaaaaaast.

Na maior parte do tempo, você está trabalhando longe o suficiente dos fundamentos que você não precisa se preocupar com isso. Como um web devedor baseado em PHP, você se importa sobre quantas instruções de CPU seu código usa? Na maioria das vezes, não, não realmente. Mas às vezes é útil saber, por dois motivos: pode explicar as decisões tomadas por suas bibliotecas; e às vezes você precisa se preocupar com velocidade em seu próprio código.

    
por 27.05.2017 / 14:25
fonte