Tamanho e character set de campos VARCHAR e consequências

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]
mysql> set max_heap_table_size = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%heap%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| max_heap_table_size | 16384 |
+---------------------+-------+
1 row in set (0.00 sec)
[/mysql]

Observamos que o mínimo que conseguimos será 16KB. Vamos ver o que acontece com campos VARCHAR (ie, de comprimento [VAR]iável):
[mysql]
mysql> CREATE TABLE `varchar_small` (
-> `id` varchar(36) NOT NULL
-> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into varchar_small values('abcd');
Query OK, 1 row affected (0.00 sec)

mysql> -- Vamos enchendo ate nao dar mais....
mysql> insert into varchar_small select * from varchar_small;
ERROR 1114 (HY000): The table 'varchar_small' is full

mysql> CREATE TABLE `var_char` (
-> `id` varchar(36) NOT NULL
-> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into var_char values('abcdefgh-ijkl-mnop-qrst-uvwxyzabcedf');
Query OK, 1 row affected (0.00 sec)

mysql> -- O mesmo: vamos enchendo ate nao dar mais...
mysql> insert into var_char select * from var_char;
ERROR 1114 (HY000): The table 'var_char' is full

mysql> select count(1) from var_char;
+----------+
| count(1) |
+----------+
| 320 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from varchar_small;
+----------+
| count(1) |
+----------+
| 320 |
+----------+
1 row in set (0.00 sec)
[/mysql]
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]
mysql> CREATE TABLE `varchar_utf8` (
-> `id` varchar(36) NOT NULL
-> ) ENGINE=MEMORY DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `varchar_latin1` (
-> `id` varchar(36) NOT NULL
-> ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)
[/mysql]
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]
mysql_connect('localhost','root','');
mysql_select_db('test');
mysql_query("SET NAMES UTF8");
mysql_query("INSERT INTO varchar_utf8 VALUES('ãããããããã-ãããã-ãããã-ãããã-ãããããããããããã')");
[/php]
[php]
mysql_connect('localhost','root','');
mysql_select_db('test');
mysql_query("SET NAMES Latin1");
mysql_query("INSERT INTO varchar_latin1 VALUES('ãããããããã-ãããã-ãããã-ãããã-ãããããããããããã')");
EOF
[/php]
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]
[root@speedy ~]# cat varchar_latin1.php.temp | iconv -f utf8 -t iso-8859-1 > varchar_latin1.php
[/code]
E executei os dois:
[code]
[root@speedy ~]# php -f varchar_utf8.php
[root@speedy ~]# php -f varchar_latin1.php
[/code]
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]
mysql> -- ...
mysql> insert into varchar_utf8 select * from varchar_utf8;
ERROR 1114 (HY000): The table 'mem_utf8' is full

mysql> -- ...
insert into varchar_latin1 select * from varchar_latin1;
ERROR 1114 (HY000): The table 'mem_latin1' is full

mysql> select count(1) from mem_utf8;
+----------+
| count(1) |
+----------+
| 126 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from mem_latin1;
+----------+
| count(1) |
+----------+
| 320 |
+----------+
1 row in set (0.00 sec)
[/mysql]

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]
mysql> select UUID();
+--------------------------------------+
| UUID() |
+--------------------------------------+
| 27d6a670-a64b-11de-866d-0017083bf00f |
+--------------------------------------+
1 row in set (0.00 sec)
[/mysql]
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.