Início / dba / Perguntas / 160251
Accepted
the_nuts
Asked: 2017-01-08 02:32:21 +0800 CST

Alterar coluna da tabela em produção

  • 772

Eu quero alterar uma coluna da tabela de CHAR(2) utf8mb4para CHAR(2) latin1(portanto, usará 2 bytes em vez de 8, utf8 não é necessário porque são códigos de país, portanto, há apenas caracteres latinos).

Esta tabela recebe cerca de 50 novas linhas por segundo.

Existem cerca de 125 milhões de linhas, tamanho total da tabela de 5 GB, portanto, a alteração levará um tempo.

Como posso alterar a tabela sem bloquear as gravações?

mysql innodb

2 respostas

  1. Best Answer

    Questão 1 - Como fazer o ALTERsem bloquear tudo muito mal. Resposta: usar pt-online-schema-change.

    Problema 2 - Armazenamento para tipos de dados do tipo char.

    No passado, CHAR(n)sempre reservava n*c bytes, onde c é o tamanho máximo baseado em CHARACTER SET: 1 para latin1, 3 para utf8, 4 para utf8mb4. Às vezes, isso era útil para Engine=MyISAM com linhas de tamanho FIXO.

    Em algumas ROW_FORMATsversões do InnoDB novas o suficiente (desculpe, não tenho os detalhes), CHAR(n)ocupará entre n e n * c bytes, dependendo dos caracteres reais necessários. Com o InnoDB, não há conceito de linhas de tamanho fixo.

    Um complexo SELECTque precisa de uma mesa temporária (digamos para ORDER BY), tentará usar uma MEMORYmesa para tal. Nesse caso, o comprimento de CHAR(n) e VARCHAR(n) sempre será n*c. Isso às vezes leva a uma ineficiência. Ou pode usar MyISAM para a tabela tmp. A versão 8.0 passará a usar o InnoDB para tabelas tmp, portanto, este parágrafo acabará sendo discutível.

    Converter CHARpara VARCHARe/ou converter a coluna de uma CHARACTER SETpara outra requer um serviço pesado ALTER(não INPLACE). Consulte https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html Esse link é para 5.6; houve mudanças; escolha a página para a sua versão.

    VARCHARtem 1 ou 2 bytes para o comprimento, mais apenas o número de bytes necessários para os caracteres reais. Então é 1+n*c para short max len.

    • 3

  2. CHAR(2) em UTF8MB é armazenado como VARCHAR(2*4) em latin1. Portanto, você não economizará muito - apenas um byte em um campo de comprimento.

    ATUALIZAÇÃO :

    Uma mesa t1:

    CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `country` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    Com dois registros:

    mysql> SELECT * FROM t1;
+----+---------+
| id | country |
+----+---------+
|  1 | ua      |
|  2 | us      |
+----+---------+
2 rows in set (0.00 sec)

    Fica assim no disco:

    0000c060  02 00 1c 69 6e 66 69 6d  75 6d 00 03 00 0b 00 00  |...infimum......|
0000c070  73 75 70 72 65 6d 75 6d  02 00 00 00 10 00 1a 80  |supremum........|
0000c080  00 00 01 00 00 00 00 31  09 a9 00 00 01 1d 01 10  |.......1........|
0000c090  75 61 02 00 00 00 18 ff  d7 80 00 00 02 00 00 00  |ua..............|
0000c0a0  00 31 0a aa 00 00 01 1e  01 10 75 73 00 00 00 00  |.1........us....|

    Vamos dividir.

    Registro 1:

    • 0x80000001isso seria uma INTchave primária assinada
    • 0x000000003109- ID da transação de seis bytes
    • 0xa90000011d0110- ponteiro de reversão de sete bytes
    • 0x7561- isso seria um valor "ua" em hexadecimal. Dois bytes, não quatro.

    Os seguintes bytes pertencem ao segundo registro:

    • 0x02- é um comprimento de countryvalor - dois bytes. O comprimento em si usa um byte porque CHAR(2) pode usar até 2*4 bytes, que é menor que 256.
    • 00- NULLmapa de valores. countrypode ser NULL, então pelo menos um byte para codificar um NULLvalor.
    • 0x000018ffd7- cinco bytes do chamado campo "extra bytes".
    • 0x80000002- chave primária do segundo registro - dois.
    • 0x00000000310a-trx_id
    • 0xaa0000011e0110-roll_ptr
    • 0x7573- "nós"

    Então, esse foi um formato COMPACTe DYNAMIC.

    Vamos fazer o mesmo exercício para REDUNDANTo formato - o formato InnoDB mais antigo disponível desde as versões 4.0.*+

    0000c060  00 00 03 00 87 69 6e 66  69 6d 75 6d 00 09 03 00  |.....infimum....|
0000c070  08 03 00 00 73 75 70 72  65 6d 75 6d 00 19 11 0a  |....supremum....|
0000c080  04 00 00 10 09 00 aa 80  00 00 01 00 00 00 00 31  |...............1|
0000c090  14 b2 00 00 01 26 01 10  75 61 20 20 20 20 20 20  |.....&..ua      |
0000c0a0  19 11 0a 04 00 00 18 09  00 74 80 00 00 02 00 00  |.........t......|
0000c0b0  00 00 31 15 b3 00 00 01  27 01 10 75 73 20 20 20  |..1.....'..us   |
0000c0c0  20 20 20 19 11 0a 04 00  00 20 09 00 74 80 00 00  |   ...... ..t...|

    Diferenças:

    • "Bytes extras" tem seis bytes de comprimento.
    • Há comprimento para cada campo, incluindo comprimento fixo.
    • CHAR(2) é armazenado com espaços à direita para preencher até oito bytes (2*4).
    • 2

relate perguntas