Tamanho e character set de campos VARCHAR e consequências

Ouvir com webReader

Apesar do tamanho dos campos VARCHAR não influenciar o tamanho dos índices, este tipo de dados (e outros semelhantes) comportam uma série de características que podem afectar seriamente a performance. Vamos testar o impacto destes campos em dois cenários: com character sets diferentes e com tamanhos diferentes.

Tendo em conta que o storage engine MEMORY apenas trabalha com tamanhos de tuplos fixos (fixed-length rows), e que este engine é o utilizado para tabelas temporárias (uma coisa a evitar, embora nem sempre seja possível), as consequências podem ser desastrosas.

Para esta demonstração, vamos definir o tamanho máximo das tabelas MEMORY para um valor que possamos atingir em alguns segundos, o mínimo:

MySQL:
  1. mysql> SET max_heap_table_size = 1;
  2. Query OK, 0 rows affected, 1 warning (0.00 sec)
  3.  
  4. mysql> SHOW variables like '%heap%';
  5. +---------------------+-------+
  6. | Variable_name       | Value |
  7. +---------------------+-------+
  8. | max_heap_table_size | 16384 |
  9. +---------------------+-------+
  10. 1 row in SET (0.00 sec)

Observamos que o mínimo que conseguimos será 16KB. Vamos ver o que acontece com campos VARCHAR (ie, de comprimento [VAR]iável):

MySQL:
  1. mysql> CREATE TABLE `varchar_small` (
  2.     ->   `id` VARCHAR(36) NOT NULL
  3.     -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
  4. Query OK, 0 rows affected (0.01 sec)
  5.  
  6. mysql> INSERT INTO varchar_small VALUES('abcd');
  7. Query OK, 1 row affected (0.00 sec)
  8.  
  9. mysql> -- Vamos enchendo ate nao dar mais....
  10. mysql> INSERT INTO varchar_small SELECT * FROM varchar_small;
  11. ERROR 1114 (HY000): The table 'varchar_small' is full
  12.  
  13. mysql> CREATE TABLE `var_char` (
  14.     ->   `id` VARCHAR(36) NOT NULL
  15.     -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
  16. Query OK, 0 rows affected (0.00 sec)
  17.  
  18. mysql> INSERT INTO var_char VALUES('abcdefgh-ijkl-mnop-qrst-uvwxyzabcedf');
  19. Query OK, 1 row affected (0.00 sec)
  20.  
  21. mysql> -- O mesmo: vamos enchendo ate nao dar mais...
  22. mysql> INSERT INTO var_char SELECT * FROM var_char;
  23. ERROR 1114 (HY000): The table 'var_char' is full
  24.  
  25. mysql> SELECT count(1) FROM var_char;
  26. +----------+
  27. | count(1) |
  28. +----------+
  29. |      320 |
  30. +----------+
  31. 1 row in SET (0.00 sec)
  32.  
  33. mysql> SELECT count(1) FROM varchar_small;
  34. +----------+
  35. | count(1) |
  36. +----------+
  37. |      320 |
  38. +----------+
  39. 1 row in SET (0.00 sec)

Ora bem, o que fiz foi preencher a primeira tabela com conteúdo bem menor que 36 carácteres (apenas 4) e, na segunda, conteúdo que preenchesse o campo todo. O que podemos observar é que, neste storage engine, o espaço ocupado por um campo pouco cheio ou muito cheio é sempre o mesmo: é o tamanho total associado ao campo. É isso que significa fixed-length, e isto acarreta como consequência que o tamanho de um campo VARCHAR em tabelas do tipo MEMORY (leia-se também: tabelas temporárias) é invariavelmente o tamanho máximo do campo. Consequentemente, ainda que um tamanho máximo de 255 não influencie o tamanho de índice sobre esse campo, sempre que seja necessário transportar esses dados para tabelas temporárias pode verificar-se um desperdício enorme de espaço (multiplicar esse desperdício pelo número de linhas que terão que ser transportadas para a tabela temporária!).

Mas isto não fica por aqui: para além de cuidado e bom senso na definição do tamanho do campo, é necessário também ter em atenção o seu encoding. O segundo ponto é sobre a influência do charset. Tipicamente trabalhávamos em Latin1, mas com a disseminação da localização (i18n) passou-se praticamente a usar sempre UTF-8. Tudo muito bem, deixámos de ter uma série de problemas com a formatação das sequências de carácteres (vulgo strings). Mas nem tudo são rosas: o charset UTf-8 pode consumir até 4 carácteres (em MySQL, 3), ao passo que Latin1 usava apenas 1:

MySQL:
  1. mysql> CREATE TABLE `varchar_utf8` (
  2.     ->   `id` VARCHAR(36) NOT NULL
  3.     -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
  4. Query OK, 0 rows affected (0.01 sec)
  5.  
  6. mysql> CREATE TABLE `varchar_latin1` (
  7.     ->   `id` VARCHAR(36) NOT NULL
  8.     -> ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
  9. Query OK, 0 rows affected (0.00 sec)

A cláusula geral de encoding da tabela propaga-se como default para as colunas. De qualquer forma, poderia especificar o encoding para a coluna de forma individual. Para ter a certeza que não estamos a ser enganados pelo encoding corrente no cliente mysql, usei dois scripts em PHP para criar uma única linha para cada uma destas tabelas (o encoding no meu terminal é UTF-8, como já vai sendo comum):

PHP:
  1. mysql_connect('localhost','root','');
  2. mysql_query("SET NAMES UTF8");
  3. mysql_query("INSERT INTO varchar_utf8 VALUES('ãããããããã-ãããã-ãããã-ãããã-ãããããããããããã')");

PHP:
  1. mysql_connect('localhost','root','');
  2. mysql_query("SET NAMES Latin1");
  3. mysql_query("INSERT INTO varchar_latin1 VALUES('ãããããããã-ãããã-ãããã-ãããã-ãããããããããããã')");
  4. EOF

Gravei os dois scripts acima para varchar_utf8.php e varchar_latin1.php.temp, respectivamente. Por fim, alterei o encoding do script para em Latin1 (porque faça o que fizer, o encoding do meu terminal é UTF-8):

CODE:
  1. [root@speedy ~]# cat varchar_latin1.php.temp | iconv -f utf8 -t iso-8859-1 > varchar_latin1.php

E executei os dois:

CODE:
  1. [root@speedy ~]# php -f varchar_utf8.php
  2. [root@speedy ~]# php -f varchar_latin1.php

OK, agora tenho 1 registo de cada em cada tabela. Vamos usar esse único registo em cada para encher as tabelas até não ser possível adicionar mais registos:

MySQL:
  1. mysql> -- ...
  2. mysql> INSERT INTO varchar_utf8 SELECT * FROM varchar_utf8;
  3. ERROR 1114 (HY000): The table 'mem_utf8' is full
  4.  
  5. mysql> -- ...
  6. INSERT INTO varchar_latin1 SELECT * FROM varchar_latin1;
  7. ERROR 1114 (HY000): The table 'mem_latin1' is full
  8.  
  9. mysql> SELECT count(1) FROM mem_utf8;
  10. +----------+
  11. | count(1) |
  12. +----------+
  13. |      126 |
  14. +----------+
  15. 1 row in SET (0.00 sec)
  16.  
  17. mysql> SELECT count(1) FROM mem_latin1;
  18. +----------+
  19. | count(1) |
  20. +----------+
  21. |      320 |
  22. +----------+
  23. 1 row in SET (0.00 sec)

Pois é, verificámos o segundo problema: no engine MEMORY couberam muito menos linhas na primeira tabela (UTF-8) que na primeira (Latin1) porque que os campos são do seu tamanho máximo possível e, no caso dos VARCHAR em UTF8, cada carácter pode ocupar até 3 bytes. Ora, para caberem 36 carácteres, serão necessários, pelo menos, 36*3 = 108 bytes! Isto representa um consumo de 300% que pode não ter sido estimado no que respeita ao tamanho de memória permitido para tabelas temporárias.

Ambos cenários são bastante frequentes, especialmente em aplicações que usem frameworks - e também nestes casos, pela generalidade de backends que tentam suportar (MySQL, PostgreSQL, Oracle, etc), as queries envolvidas não costumam ser optimizadas para nenhum motor em particular; no caso do MySQL essas queries geram, muitas vezes, tabelas temporárias sem necessidade e optimizar este tipo de queries pode implicar alterações no núcleo da framework ou, no mínimo, criar excepções à regra.

A escolha do nome da coluna nos exemplos acima não foi ao acaso: com efeito, os campos UUID() costumam ser UTF-8 apenas porque toda a tabela é UTF-8 e, imagine-se, não há nenhum carácter num UUID() que não seja ASCII (na verdade, não há nenhum carácter que não seja [a-zA-Z-1-9]!):

MySQL:
  1. mysql> SELECT UUID();
  2. +--------------------------------------+
  3. | UUID()                               |
  4. +--------------------------------------+
  5. | 27d6a670-a64b-11de-866d-0017083bf00f |
  6. +--------------------------------------+
  7. 1 row in SET (0.00 sec)

Como o conjunto de caracteres de um UUID() é charset compatible entre os vários, esta é uma das optimizações que poderão fazer nas vossas aplicações facilmente, contribuindo para o aumento de performance do MySQL sem qualquer impacto na vossa aplicação.


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

3 Responses to “Tamanho e character set de campos VARCHAR e consequências”

  1. [...] Veja o artigo original nesta página. [...]

  2. Muito bom mesmo este post! :)

  3. [...] Setembro escrevi sobre as consequências de deixar ao acaso o tamanho e character set de campos VARCHAR. Entretanto gostaria de complementar as observações com o que escrevo [...]

Leave a Reply