Esta postagem traz uma explicação de como o tamanho e a quantidade de informações existentes nas tabelas pode deixar o acesso mais lento. Muito já foi abordado sobre as tabelas que possuem filas longas, o acesso será mais lento do que as tabelas com linhas curtas. Estamos interessados na verificação da influencia do comprimento da linha é a única coisa importante ou se o número de colunas possui influencia direta, nos picos de velocidade do pico de processamento de cada linha.

Foram criadas 3 tabelas, a primeira contendo uma coluna tinyint único que é o mais curto de todos os tipos possíveis (CHAR (0) tomando menos espaço), , tabela com 1 coluna tinyint e coluna char (99) e tabela com 100 colunas tinyint. As duas primeiras tabelas possuem o mesmo comprimento porém numero de coluna diferentes de 50 vezes. Foi criada a tabela 4 que possui 100 colunas, porém possuem valor VARCHAR.

Mais especificamente:

CREATE TABLE `t1` (
  `t1` tinyint(3) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `t1c99` (
  `t1` tinyint(3) unsigned NOT NULL,
  `c99` char(99) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `t100` (
  `t1` tinyint(3) unsigned NOT NULL,
  `t2` tinyint(3) unsigned NOT NULL,
...
  `t99` tinyint(3) unsigned NOT NULL,
  `t100` tinyint(3) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1


CREATE TABLE `t99v1` (
  `t1` tinyint(3) unsigned NOT NULL,
  `t2` tinyint(3) unsigned NOT NULL,
...
  `t99` tinyint(3) unsigned NOT NULL,
  `v1` varchar(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Cada uma das tabelas foi preenchida com 12M. ficando sete bytes na linha da primeira tabela ficando 101 bytes para o segundo e o terceiro.

O resultado foi o seguinte: 
t1 - 1,00 s (linhas 12M / seg; 80MB/sec) 
t1c99 - 1,71 s (7M linhas / seg; 676MB/sec) 
t100 - 1,77 s (7M linhas / seg; 653MB/sec) 
t99v1 - 12,36 s (1M linhas / seg; 93MB/sec)

Isto mostra que certamente existe um problema na tabela dinâmica formada por várias linhas e colunas. Mas será que por conta do grande numero de colunas ou formato dinâmico o torna escravo?

Testamos a Estrutura de Outra tabela:
CREATE TABLE `t1v1` (
  `t1` tinyint(3) unsigned NOT NULL,
  `v` varchar(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1


Esta tabela tem comprimento de 20 linhas (que foi um pouco de surpresa) e temos:
t1v1 - 1,83 s (6,5 milhões de linhas / seg; 125M/sec)


Existem apenas linhas dinâmicas, no entanto não se faz bastante significativa pois o numero de colunas é pequeno. Você precisa tomar bastante cuidado com a grande quantidade de colunas e linhas dinâmicas existentes.

Para as tabelas dinâmicas deve existir um lugar para as estruturas de dados internas que são preenchidos . Este processo de conversão é dependente do numero de colunas das linhas fixas, enquanto o o formato de armazenamento utilizado for o MyISAM corresponde a uma cópia da memoria que não está interligado diretamente ao número de colunas.

Um outra observação bastante interessante e que a velocidade de acesso às colunas é feita de forma diferente. O max (t1) e max (t99) foram as que tomaram mais tempo de processamento, o que nos mostra que não existe nenhum problema para que a coluna que esteja no fim da tabela seja acessada.

A solução de trabalho comum com para tais tabelas largas é usar índices de cobertura. Adicionamos um a t99v1 e foi repetida a consulta:
mysql [localhost] {msandbox} (test) > select max(t1+0) from t99v1;
+-----------+
| max(t1+0) |
+-----------+
|         0 |
+-----------+
1 row in set (3.26 sec)

mysql [localhost] {msandbox} (test) > explain select max(t1+0) from t99v1;
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | t99v1 | index | NULL          | t1   | 1       | NULL | 12000000 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)


Como você pode perceber no scan do índice não é tão rápido quanto uma varredura da tabela scan sobre as linhas 3,7 M/ s, que ainda é muito rápido. Então isso é tudo sobre MyISAM, que está preste Innodb. Aqui estão os resultados para Innodb com todos os dados no buffer, para medir a velocidade de pico, assim

Os resultados para Innodb foram: 
t1 - 5,11 s (2,3 M linhas / sec) 
t1c99 - 5,74 s (2,1 M linhas / sec) 
t100 - 15,16 seg (0.8M linhas / sec) 
t99v1 - 14,93 seg (0.8M linhas / sec) 
t1v1 - 5,26 s (2,3 M linhas / sec) 
t99v1 (cobrindo idx) - 5,62 s (2,1 M linhas / sec)


Como você pode ver InnoDB é muito mais lento e tem comportamento semelhante às tabelas e linhas dinâmicas em ambos os casos. Isto é porque InnoDB não armazena dados em formato MyISAM nativamente e a conversão é necessária em todos os casos. Nós também podemos ver a velocidade da varredura da tabela pode ser até 5 vezes mais lenta, para linhas muito curtas, algumas dessas linhas ao fato do InnoDB tem um monte de overhead’s de controle de transação que lhes são inerentes.

Além disso, observe-se a velocidade de verificação cobrindo o índice, é muito semelhante à velocidade total de varredura da tabela isto é bastante esperad como dados na tabela que é armazenada no índice BTREE muito semelhante a como os índices são armazenados.


Resumo:


Cuidado com as tabelas em formato dinâmico e as linhas com várias colunas, elas podem realizar uma desaceleração de surpresa. MyISAM é muito mais rápido do que Innodb quando se trata de varredura completa na memória da tabela.

Os testes foram feitos em PS MySQL 5.4.2 na Intel (R) Xeon (R) CPU CPU [email protected].

Com informações: www.mysqlperformanceblog.com