Início / dba / Perguntas / 3213
Accepted
Incognito
Asked: 2011-06-08 06:24:18 +0800 CST

Como devo otimizar o armazenamento para esta tabela?

  • 772

Estou usando o MySql para armazenar uma tabela básica deste formato:

    id      int(11)          //Auto-Incrementing ID
    data    varchar(5120)    //Random input data, compressed by a program, not mysql.
    -----------------------
    Row size ø  916 B

Isso é um pouco preocupante, porque agora tenho cerca de 5.000 registros por mês e gostaria de otimizar isso melhor do que acabar com uma média de 1 MB/1.000 registros.

Eu configurei dessa forma originalmente porque queria capturar os conjuntos maiores, mas eles são muito raros, como você pode ver aqui neste gráfico:

insira a descrição da imagem aqui

Link para a API do gráfico

count   n*128 bytes
+-------+----------
1       28
1       26
1       24
2       22
8       21
4       20
13      19
12      18
16      17
27      16
43      15
58      14
69      13
114     12
184     11
262     10
399     9
588     8
807     7
1224    6
1245    5
546     4
73      3
9       2
6       1
1       0

Minha principal preocupação aqui é quanto espaço desperdicei apenas para acomodar o final desse gráfico, e o que acontece quando acabo com outliers ainda maiores? Terei que aumentar meu tamanho de linha novamente, quando aproximadamente 80% dos dados se encaixam muito bem em um bloco varchar 1024, 1/5 do tamanho do que estou usando agora.

Então, como eu deveria ter construído essa coisa em primeiro lugar?

mysql optimization

2 respostas

  1. Best Answer

    Como já está construído, vamos ver o que você tem. Existe uma maneira interessante de planejar definições de colunas para dados atualmente presentes. Se o nome da tabela for mydata, tente executar esta consulta:

    SELECIONE dados DE mydata PROCEDURE ANALYZE ();

    Isso não exibirá nenhum dos seus dados. Isso examinará a dataprópria coluna, calculará as estatísticas com base nos primeiros 256 valores distintos (por padrão, você pode especificar a contagem de valores distintos) e recomendará o tipo de coluna adequado que a tabela deve ter.

    Desde que a tabela seja MyISAM, não se preocupe muito com o tamanho das linhas porque o formato de linha padrão é DYNAMIC. Se a tabela for InnoDB (e espero que não seja), verifique se os dados não fazem parte da PRIMARY KEY. Seu índice agrupado crescerá como derramar MiracleGro não diluído em seu quintal.

    Pode ser necessário dividir a coluna de dados em blocos de 128 e executar MD5 (saída de 32 caracteres) ou SHA1 (saída de 40 caracteres), concatenar essas saídas MD5 ou SHA1 e armazená-las. Isso economizaria 75% de armazenamento. Divirta-se tentando codificar essa separação. Esta foi apenas uma sugestão divagar em cima da minha cabeça.

    Você pode querer considerar o uso do Sphinx Indexing também.

    • 4

  2. Para MyISAM, se você nunca ATUALIZAR ou EXCLUIR registros, um registro com N bytes para o blob ocupará N+8 bytes no arquivo .MYD.

    O MyISAM PK será encontrado no arquivo .MYI; será bem pequeno - cerca de 11 bytes/linha, mais um pouco de sobrecarga, arredondado para blocos de 1 KB.

    Se você estiver usando o InnoDB, o layout é bastante complicado. Existem blocos de dados de 16KB que nunca estão totalmente cheios, existem extensões de blob (mesmo para VARCHAR), que entram em ação após 767 bytes (talvez antes, dependendo da versão), etc. As extensões são alocadas em blocos de 1 MB. Planeje algo como 2N bytes por média de linha.

    O InnoDB PK é armazenado com os dados, por isso está completamente incluído acima. E, sendo AUTO_INCREMENT, seus blocos tenderão a ficar relativamente cheios. Nota: os blocos têm 16 KB de tamanho, permitindo pelo menos 20 por bloco.

    Não jogue jogos com sua própria divisão; isso tornará as coisas mais lentas, complicará seu código, etc. O disco é barato. (Sim, é divertido jogar esses jogos.)

    Parece que metade dos seus registros não passará do limite 767.

    Um aparte: se o blob for um dado compactado, você realmente deve usar o BLOB, não o VARCHAR. VARCHAR assume atributos CHARACTER SET, o que realmente o queimaria se você mudasse para utf8. Se você precisar usar VAR-xx, use VARBINARY.

    • 2

relate perguntas