Início / dba / Perguntas / 23012
Accepted
Ran
Asked: 2012-08-24 07:12:42 +0800 CST

MySQL - Quanto espaço físico será reduzido alterando o tipo de coluna?

  • 772

Digamos que eu tenha a seguinte tabela:

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `relationship_status` varchar(48) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

e digamos que esta tabela tenha muitos registros: 100M

Eu tenho 2 valores possíveis para relationship_status: 'following' ou 'not_following'

já que eu quero reduzir o tamanho do banco de dados (tamanho no disco rígido), como isso afetaria se eu mudasse relationship_statuspara Boolean em vez de varchar(48) (onde if pode definir 0 como não seguinte e 1 como seguinte)?

qual tipo de coluna você usaria? minúsculoint?

mysql mysql-5.5

2 respostas

  1. Best Answer

    Acho que TINYINTseria uma boa escolha. Eu sugeriria não indexar relationship_statusnada. Você não deve indexar relationship_statussozinho porque a cardinalidade do índice é 2. O Query Optimizer nunca usaria o índice. Você poderia indexar da seguinte forma:

    ALTER TABLE my_table ADD INDEX (relationship_status,user_id);

    Dessa forma, no pior caso, o Query Optimizer executaria uma varredura de índice em consultas como:

    SELECT user_id FROM my_table WHERE relationship_status = 1;
SELECT relationship_status,COUNT(1) statuscount
FROM my_table GROUP BY relationship_status;

    O plano EXPLAIN deve ser melhorado para que a classificação e o uso da tabela temporária sejam minimizados ou eliminados.

    Agora com relação ao espaço em disco, vamos calcular a economia de espaço. Primeiro, qual é o comprimento de cada string?

    mysql> select length('following') followlength,length('not_following') notfollowlength;
+--------------+-----------------+
| followlength | notfollowlength |
+--------------+-----------------+
|            9 |              13 |
+--------------+-----------------+
1 row in set (0.01 sec)

    Cada VARCHAR possui um byte adicional para rastrear o comprimento da string. Os valores são realmente 10 e 14. Isso é ignorado, pois você está reduzindo o campo para 1 byte. Se você relationship_statusreduzir para TINYINT, a economia será a seguinte

    SELECT
    BB SavingsBytes,
    BB/POWER(1024,1) SavingsKB,
    BB/POWER(1024,2) SavingsMB,
    BB/POWER(1024,3) SavingsGB
FROM
(
    SELECT
        fcount*length('following')+ncount*length('not_following') BB
    FROM
        (
            SELECT COUNT(1) fcount FROM my_table
            WHERE relationship_status='following'
        ) F,
        (
            SELECT COUNT(1) ncount FROM my_table
            WHERE relationship_status='not_following'
        ) N
);

    Como o número de linhas é de cerca de 100 milhões, a economia de espaço em disco deve estar entre 900 milhões e 1,3 G.

    ATUALIZAÇÃO 2012-08-23 13:00 EDT

    Para responder ao seu comentário

    digamos que a tabela tenha mais 2 colunas, 1 que é varchar(255) e outra que é DATETIME, digamos que eu possa alterar os dois tipos de coluna para int(11) unsigned, quanto espaço em disco isso economizaria?

    Em primeiro lugar, a DATETIMEtem 8 bytes e INT(11)tem 4 bytes. Como 8 - 4 = 4, sua economia seria de 4 bytes por linha. Para a mesa,SELECT COUNT(1)*4 FROM my_table;

    Agora, o campo varchar(255). Como mencionei antes, um varchar tem um byte extra para gerenciamento de comprimento. Some todos os comprimentos de todos os campos varchar e adicione a contagem de linhas (o campo from extra em cada linha) e subtraia 4 bytes vezes a contagem de linhas.

    Colocando os cálculos DATETIME e VARCHAR juntos, você obtém isso

    SELECT
    BB SavingsBytes,
    BB/POWER(1024,1) SavingsKB,
    BB/POWER(1024,2) SavingsMB,
    BB/POWER(1024,3) SavingsGB
FROM
(
    SELECT 
        (VarCharBytes - (RowCount * 3)) BB
    FROM
    (
        SELECT SUM(LENGTH(varcharfield)) VarCharBytes
        FROM my_table
    ) A,
    (SELECT COUNT(1) RowCount FROM my_table) T
);
    • 3

  2. Pessoalmente, eu usaria um tipo de dados BIT(1) e alteraria o nome da coluna para "following" apenas para que fizesse sentido lógico.

    O espaço usado pelo seu design atual é o seguinte (usando o comprimento do texto NOT_FOLLOWING que é de 13 bytes):

    (13 * 100.000.000) / (1024 ^ 3) => 1,21 GB aprox.

    Usar um campo de bit usaria o seguinte espaço:

    (1 * 100.000.000) / (1024 ^ 3) => 0,093 GB

    Isso lhe daria uma economia de espaço de:

    1,21 - 0,093 => 1,12 GB aprox.

    Espero que isso ajude você.

    • 2

relate perguntas