Eu tenho alguns arquivos CSV bastante grandes que estou carregando no meu banco de dados MySQL 5.7. Os arquivos têm vários gigabytes de tamanho, vários milhões de linhas e larguras de coluna grandes que devem ser usadas em junções (às vezes, com aproximadamente 500 caracteres).
Os dados são todos os caracteres padrão do inglês, e a maioria das colunas pode caber em um único conjunto de caracteres de byte como latin1. No entanto, várias das colunas exigem unicode para coisas como símbolos de marca registrada/registrada/direitos autorais, símbolos de medição (polegadas, pés, raio, etc.) e, portanto, tenho usado utf8mb4em todas as tabelas.
O problema de fazer isso é duplo. Ele expande nossos tamanhos de índice, portanto, em alguns casos, não podemos criar um índice em uma(s) coluna(s) porque a largura se torna maior que 3072. Além disso, parece estar causando um impacto significativo no desempenho, provavelmente porque o tamanho dos dados é 4x.
O que eu gostaria de fazer é usar latin1em todas as colunas da tabela e apenas utf8mb4nas colunas que precisam. Isso leva às minhas perguntas -
Qual é a melhor maneira de identificar com certeza quais colunas estão realmente armazenando caracteres multibyte? Posso detectar isso de alguma forma, dentro do meu CSV antes de carregar (usando python/pandas talvez?), ou de dentro do banco de dados? Os arquivos são armazenados como utf8. Eles estão atualmente carregados em uma utf8mb4tabela. Se eu pudesse escanear facilmente a tabela e dizer "esta coluna não contém dados multibyte", eu poderia alterá-la para latin1.
Segundo, terei problemas se tentar criar índices compostos com colunas usando codificações diferentes? Digamos que a coluna Aseja utf8mb4 e a coluna Bseja latin1. Há algo de errado em criar um índice nessas duas colunas? ou seja: CREATE INDEX my_index
ON my_table(A, B);. Estou assumindo que não há problema em fazer isso.
O tamanho dos dados não é 4x. O texto em inglês, mesmo em utf8mb4, leva apenas um byte por caractere. Os símbolos de marca registrada(etc) são multi-byte. Então aqueles que você mencionou são apenas 2 bytes. Emoji e alguns chineses é onde 4 bytes se tornam necessários.
Não crie índices em colunas grandes. Não crie índices até que você tenha as consultas -- obtenha os índices ideais das consultas.
Está perfeitamente bem (no MySQL, pelo menos) ter uma coluna como latin1 e outra como utf8mb4 (etc). E ambos podem estar no mesmo índice.
Sugiro fazer algumas passagens sobre os dados. Primeiro traga tudo com utf8mb4, sem índices, colunas largas (por exemplo,
TEXT). Então analise o que você tem --SELECT MAX(CHAR_LENGTH(col2)), ...; teste para non-latin1, etc. Para a segunda passagem, refaça o esquema para ficar mais próximo de max len, etc.