Início / dba / Perguntas / 76469
Accepted
Sonique
Asked: 2014-09-14 02:33:25 +0800 CST

MySQL - comprimento e desempenho do varchar

  • 772

Declarar VARCHARo tamanho faz sentido para o desempenho? Existe alguma diferença (na velocidade) entre VARCHAR(50)e VARCHAR(255)? Ou definir o comprimento é uma restrição lógica/de design?

mysql database-design

2 respostas

  1. Best Answer

    Esta é uma "pergunta de exame/entrevista" muito comum. Vou responder o melhor que puder:

    Nos formatos de linha padrão para InnoDB e MyISAM (dinâmico/compacto) a VARCHAR(50)e a VARCHAR(255)irão armazenar o texto da string da mesma forma - 1 byte para o comprimento e a string real com entre 1 e 4 bytes por caractere (dependendo da codificação e o caractere real armazenado).

    Na verdade, se bem me lembro, lembro de alguém modificando o dicionário de dados com um editor hexadecimal para mudar algo como a VARCHAR(50)em a VARCHAR(100), para que isso pudesse ser feito dinamicamente (normalmente, isso requer uma reconstrução de tabela). E isso foi possível porque os dados reais não foram afetados por essa mudança.

    Isso não é verdade com VARCHAR(256), porque então 2 bytes (pelo menos) para o comprimento são sempre necessários.

    Então, isso significa que devemos sempre fazer VARCHAR(255), não devemos? Não. Existem várias razões.

    Embora o InnoDB possa armazenar um varchar de maneira dinâmica, isso não é verdade para outros mecanismos. MyISAM tem um formato de tamanho de linha fixo e as tabelas MEMORY são sempre de tamanho fixo. Devemos nos preocupar com esses outros motores? Sim, devemos, pois mesmo que não as usemos diretamente, as tabelas MEMORY são muito usadas para resultados intermediários (tabelas temporárias na memória) e, como os resultados não são conhecidos de antemão, a tabela deve ser criada com o tamanho máximo possível - VARCHAR(255)se esse for o nosso tipo. Se você puder pensar no espaço desperdiçado, se estivermos usando a 'utf8' charsetcodificação do MySQL, MEMORY reservará 2 bytes para o comprimento + 3 * 255 bytes por linha(para valores que podem levar apenas alguns bytes no InnoDB). Isso é quase 1 GB em uma tabela de 1 milhão - apenas para o VARCHAR. Isso não apenas causa estresse desnecessário na memória, mas também pode provocar a execução de ações no disco, potencialmente tornando-o lento milhares de vezes. Tudo isso por causa de uma má seleção de seu tipo de dados definido (independentemente do conteúdo).

    Isso também tem algumas consequências para o InnoDB. O tamanho do índice é restrito a 3.072 bytes e índices de coluna única, a 767 bytes*. Portanto, é muito provável que você não consiga indexar totalmente umVARCHAR(255) campo (supondo que você use utf8 ou qualquer outra codificação de comprimento variável).

    Além disso, o tamanho máximo de linha inline para InnoDB é meia página (cerca de 8000 bytes), e campos de comprimento variável como BLOB ou varchar, podem ser armazenados fora da página se não couberem na meia página . Isso tem algumas consequências no desempenho (às vezes boas, às vezes ruins, dependendo do uso) que não podem ser ignoradas. Isso causou alguma estranheza entre os formatos COMPACTO e DINÂMICO. Veja, por exemplo: erro 1118: tamanho da linha muito grande. utf8 innodb

    Por último, mas não menos importante, como @ypercube me lembrou, mais de 1 byte para o comprimento pode ser necessário mesmo se você estiver usando VARCHAR(255), porque a definição está em caracteres, enquanto o comprimento armazena bytes. Por exemplo REPEAT('ñ', 255), tem mais de 2^255 bytes em utf8, portanto, seria necessário mais de 1 byte para armazenar seu comprimento:

    mysql> SELECT LENGTH(REPEAT('ñ', 255));
+---------------------------+
| LENGTH(REPEAT('ñ', 255))  |
+---------------------------+
|                       510 |
+---------------------------+
1 row in set (0.02 sec)

mysql> SELECT CHAR_LENGTH(REPEAT('ñ', 255));
+--------------------------------+
| CHAR_LENGTH(REPEAT('ñ', 255))  |
+--------------------------------+
|                            255 |
+--------------------------------+
1 row in set (0.00 sec)

    Portanto, o conselho geral é usar o menor tipo possível , porque, de outra forma, pode criar problemas de desempenho ou gerenciamento. A VARCHAR(100)é melhor que VARCHAR(255)(embora a VARCHAR(20)seja melhor), mesmo que você não saiba o comprimento exato. Tente ser conservador porque, a menos que a tabela seja muito grande, você sempre poderá alterar a definição posteriormente.

    Atualização: Devido à popularidade explosiva de strings de comprimento variável, por exemplo, com o uso de emojis, a Oracle vem pressionando por um desempenho aprimorado para esses casos. Nas versões mais recentes do MySQL (5.6, 5.7), o InnoDB foi definido como o mecanismo padrão para tabelas temporárias intrínsecas e explícitas, o que significa que os campos de comprimento variável agora são cidadãos de primeira classe. Isso significa que pode haver menos motivos para ter comprimentos de caracteres muito restritos (mas eles ainda existem).

    (*) Segunda atualização : large_prefix_index agora está habilitado por padrão nas versões mais recentes do MySQL (8.0), mas isso ainda é verdade para versões mais antigas ou se você estiver usando formatos de arquivo/linha innodb herdados (diferente de dinâmico ou compactado), mas agora por padrão, os índices de coluna única podem ter até 3.072 bytes.

    • 48

  2. Esqueça o prefixo de 1 versus 2 bytes no VARCHARs.

    • Ele afeta o desempenho por uma quantidade minúscula.
    • É "2" com mais frequência do que a regra óbvia diz.

    A pergunta sobre 255 foi feita e respondida muitas vezes.

    • Muito tempo VARCHARspode levar à falha de CREATE TABLE.
    • Um complexo SELECTusará uma tabela temporária para, por exemplo, fazer a classificação de um arquivo ORDER BY. Uma MEMORYtabela é usada em algumas situações . Em outras situações, MyISAMserá usado. Ao usar MEMORY, VARCHARssão transformados em CHARs(para a tabela temporária). Isso significa, por exemplo, que VARCHAR(255) CHARACTER SET utf8mb4deseja um comprimento fixo de 1020 bytes. E isso é "muito grande" para usar MEMORY, então ele mudará para o MyISAM menos eficiente.

    (No MySQL 8.0, os detalhes das tabelas temporárias foram alterados. O parágrafo anterior se aplica a tabelas de todos os mecanismos anteriores a 8.0.)

    Conclusão: Não use cegamente 255 (ou 256); faça o que faz sentido para o esquema. Se você precisar de 255 (ou 1024 ou qualquer outro), vá em frente e use-o. Estou apenas apontando algumas desvantagens.

    Quanto desempenho atingiu? É difícil prever; geralmente, não vale a pena se preocupar. (A pergunta era sobre desempenho, tentei listar todos os casos em que o número VARCHARimporta, mesmo que um pouco.)

    • 1

relate perguntas