Como número de colunas pode afetar o desempenho em MySQL?

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.

Por Programação Pular para comentários
Como número de colunas pode afetar o desempenho em MySQL?

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 E5405@2.00GHz.

Com informações: www.mysqlperformanceblog.com

Compartilhe com seus amigos:
Gregory Laborde
Gregory Laborde Estudante da Licenciatura em Computação Pela Universidade Federal Rural de Pernambuco.Técnico em Tecnologia da Informação. Entusiasta do Software Livre e Palestrante.
FACEBOOK // TWITTER: @gregoryylaborde
Quer conversar com o(a) Gregory, comente:
Carregar comentários
Últimas notícias de Programação
  • Google usa Mulher-Maravilha para ajudar meninas a programar

    Google usa Mulher-Maravilha para ajudar meninas a programar

    Meninas ganham um incentivo extra na hora de aprender a programar, a Mulher-Maravilha.

  • Criando um cadastro de usuário em Java

    Criando um cadastro de usuário em Java

    O objetivo deste artigo é desenvolver uma aplicação em JSE (Java Standard Edition) de inserção de dados utilizando alguns padrões de projeto.

  • Quer aprender PHP? Saiba mais

    Quer aprender PHP? Saiba mais

    Chegou o tão aguardado curso online de PHP do Oficina da Net. Você não pode perder. PHP é uma das linguagens mais usadas no mundo. Os conteúdos que vou mostrar no curso, são exatamente o que você precisa saber para iniciar sua carreira como programador.

  • Formulário de contato em php

    Formulário de contato em php

    Guia do PHP: Aprenda a fazer um formulário em PHP que envia via SMTP autenticado o e-mail para um destinatário.

  • Como fazer um GIF?

    Como fazer um GIF?

    Aprenda a criar um GIF animado de vídeos. Descubra como criar os GIFs, imagens animadas que você vê em na internet.

  • O que priorizar na hora de escolher o hosting para seu site?

    O que priorizar na hora de escolher o hosting para seu site?

    Com o crescimento das ofertas na web, saiba o que você deve levar em consideração na hora de escolher a melhor empresa de hosting para seu empreendimento

  • O que preciso fazer para criar um aplicativo?

    O que preciso fazer para criar um aplicativo?

    Temos visto que desenvolver aplicativos que visam o mercado mobile pode ser mais que uma alternativa rentável, pode colocá-lo no topo, deixá-lo rico. Mas nem tudo são flores, e o aspirante a desenvolvedor de app precisa seguir algumas regras.

  • WEBINAR 3.9

    WEBINAR 3.9

    Venha para o Maker e descubra como tornar o seu negócio mais competitivo.