Início / dba / Perguntas / 3972
Accepted
Mokus
Asked: 2011-07-20 23:24:46 +0800 CST

Código de erro 1117 Muitas colunas; Limite de colunas do MySQL na tabela

  • 772

Tenho uma tabela com 1699 colunas e quando tento inserir mais colunas recebo,

Código de erro: 1117. Muitas colunas

Nesta tabela eu tenho apenas 1000 linhas. Para mim, o mais importante é o número de colunas. Há alguma limitação na mesa? Eu quero criar 2000 colunas. Isso é possível?

mysql table

6 respostas

  1. Best Answer

    Por que você precisaria criar uma tabela com até 20 colunas, quanto mais 2000 ???

    Concedido, dados desnormalizados podem evitar a necessidade de fazer JOINs para recuperar muitas colunas de dados. No entanto, se você tiver mais de 10 colunas, deve parar e pensar no que aconteceria nos bastidores durante a recuperação de dados.

    Se uma tabela de 2.000 colunas passar por SELECT * FROM ... WHERE, você geraria grandes tabelas temporárias durante o processamento, buscando colunas desnecessárias e criando muitos cenários em que os pacotes de comunicação ( max_allowed_packet ) seriam empurrados para o limite em cada consulta.

    Nos meus primeiros dias como desenvolvedor, trabalhei em uma empresa em 1995 onde o DB2 era o principal RDBMS. A empresa tinha uma única tabela com 270 colunas, dezenas de índices e problemas de desempenho na recuperação de dados. Eles contataram a IBM e pediram a consultores que examinassem a arquitetura de seu sistema, incluindo essa tabela monolítica. A empresa foi informada "Se você não normalizar esta tabela nos próximos 2 anos, o DB2 falhará nas consultas que executam o Processamento Stage2 (qualquer consulta que exija classificação em colunas não indexadas)". Isso foi dito a uma empresa de vários trilhões de dólares, para normalizar uma tabela de 270 colunas. Quanto mais uma tabela de 2000 colunas.

    Em termos de mysql, você teria que compensar esse design ruim configurando opções comparáveis ​​ao DB2 Stage2 Processing. Nesse caso, essas opções seriam

    Ajustar essas configurações para compensar a presença de dezenas, quanto mais centenas, de colunas funciona bem se você tiver TBs de RAM.

    Este problema se multiplica geometricamente se você usar o InnoDB, pois terá que lidar com o MVCC (Multiversion Concurrency Control) tentando proteger toneladas de colunas com cada SELECT, UPDATE e DELETE através do isolamento de transações.

    CONCLUSÃO

    Não há substituto ou band-aid que possa compensar um design ruim. Por favor, para o bem de sua sanidade no futuro, normalize essa tabela hoje !!!

    • 40

  2. Estou tendo problemas para imaginar qualquer coisa em que o modelo de dados possa conter legitimamente 2.000 colunas em uma tabela devidamente normalizada.

    Meu palpite é que você provavelmente está fazendo algum tipo de esquema desnormalizado de "preencher os espaços em branco", onde você está armazenando todos os tipos diferentes de dados em uma tabela e, em vez de dividir os dados em tabelas separadas e fazer relações , você tem vários campos que registram que "tipo" de dados é armazenado em uma determinada linha e 90% de seus campos são NULL. Mesmo assim, querer chegar a 2000 colunas... caramba.

    A solução para o seu problema é repensar seu modelo de dados. Se você estiver armazenando uma grande pilha de dados de chave/valor associados a um determinado registro, por que não modelá-lo dessa maneira? Algo como:

    CREATE TABLE master (
    id INT PRIMARY KEY AUTO_INCREMENT,
    <fields that really do relate to the
    master records on a 1-to-1 basis>
);

CREATE TABLE sensor_readings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    master_id INT NOT NULL,   -- The id of the record in the
                              -- master table this field belongs to
    sensor_id INT NOT NULL,
    value VARCHAR(255)
);

CREATE TABLE sensors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    <fields relating to sensors>
);

    Então, para obter todas as entradas do sensor associadas a um determinado registro "mestre", você pode simplesmente SELECT sensor_id,value FROM sensor_readings WHERE master_id=<some master ID>. Se você precisar obter os dados de um registro na mastertabela junto com todos os dados do sensor para esse registro, use uma junção:

    SELECT master.*,sensor_readings.sensor_id,sensor_readings.value
FROM master INNER JOIN sensor_readings on master.id=sensor_readings.master_id
WHERE master.id=<some ID>

    E, em seguida, junções adicionais se você precisar de detalhes sobre o que é cada sensor.

    • 25

  3. É um sistema de medição com 2000 sensores

    Ignore todos os comentários gritando sobre normalização - o que você está pedindo pode ser um design de banco de dados sensato (em um mundo ideal) e perfeitamente normalizado, é apenas muito incomum e, como apontado em outros lugares, os RDBMSs geralmente não são projetados para tantas colunas .

    Embora você não esteja atingindo o limite rígido do MySQL , um dos outros fatores mencionados no link provavelmente está impedindo você de ir mais alto

    Como outros sugerem, você pode contornar essa limitação tendo uma tabela filho com id, sensor_id, sensor_value, ou mais simplesmente, você pode criar uma segunda tabela para conter apenas as colunas que não cabem na primeira (e usar o mesmo PK)

    • 21

  4. Limites de contagem de colunas do MySQL 5.0 (ênfase adicionada):

    Há um limite rígido de 4.096 colunas por tabela , mas o máximo efetivo pode ser menor para uma determinada tabela. O limite exato depende de vários fatores de interação.

    • Cada tabela (independentemente do mecanismo de armazenamento) tem um tamanho máximo de linha de 65.535 bytes. Os mecanismos de armazenamento podem impor restrições adicionais a esse limite, reduzindo o tamanho máximo efetivo da linha.

      O tamanho máximo da linha restringe o número (e possivelmente o tamanho) das colunas porque o comprimento total de todas as colunas não pode exceder esse tamanho.

    ...

    Mecanismos de armazenamento individuais podem impor restrições adicionais que limitam a contagem de colunas da tabela. Exemplos:

    • O InnoDB permite até 1.000 colunas.
    • 16

  5. Primeiro um pouco mais de chamas, depois uma solução real...

    Eu concordo principalmente com as chamas já lançadas em você.

    Discordo da normalização de valor-chave. As consultas acabam sendo horríveis; desempenho ainda pior.

    Uma maneira 'simples' de evitar o problema imediato (limitação do número de colunas) é 'particionar verticalmente' os dados. Tenha, digamos, 5 tabelas com 400 colunas cada. Todos eles teriam a mesma chave primária, exceto que um poderia ter AUTO_INCREMENT.

    Talvez seja melhor decidir sobre os doze campos que são mais importantes, colocá-los na tabela 'principal'. Em seguida, agrupe os sensores de alguma forma lógica e coloque-os em várias tabelas paralelas. Com o agrupamento adequado, talvez você não precise JOIN todas as tabelas o tempo todo.

    Você está indexando algum dos valores? Você precisa pesquisar sobre eles? Provavelmente você pesquisa em datetime?

    Se você precisar indexar muitas colunas -- punt.

    Se você precisar indexar alguns - coloque-os na 'tabela principal'.

    Aqui está a solução real (se aplicável) ...

    Se você não precisa da vasta gama de sensores indexados, não faça colunas! Sim, você me ouviu. Em vez disso, colete-os em JSON, compacte o JSON, armazene-o em um campo BLOB. Você economizará muito espaço; você terá apenas uma tabela, sem problemas de limite de colunas; etc. Seu aplicativo será descompactado e, em seguida, usará o JSON como estrutura. Adivinha? Você pode ter estrutura -- você pode agrupar os sensores em arrays, coisas de vários níveis, etc., exatamente como seu aplicativo gostaria. Outro 'recurso' - é aberto. Se você adicionar mais sensores, não precisará ALTERAR a tabela. JSON se flexível dessa maneira.

    (A compactação é opcional; se o seu conjunto de dados for enorme, ajudará com o espaço em disco e, portanto, com o desempenho geral.)

    • 9

  6. Eu vejo isso como um cenário possível no mundo do big data, onde você pode não estar realizando o tradicional select * tipo de consultas. Lidamos com isso no mundo da modelagem preditiva no nível do cliente, onde modelamos um cliente em milhares de dimensões (todas com valores de 0 ou 1). Essa forma de armazenamento facilita as atividades de construção do modelo downstream, etc., quando você tem os fatores de risco na mesma linha e o sinalizador de resultado na mesma linha. Isso pode ser normalizado do ponto de vista do armazenamento com uma estrutura pai-filho, mas o downstream do modelo preditivo precisará convertê-lo novamente em esquema simples. Usamos redshift que faz armazenamento colunar, então suas mais de 1000 colunas quando você carrega os dados, na verdade são armazenadas em um formato colunar...

    Há um tempo e um lugar para este design. Absolutamente. A normalização não é a solução para todos os problemas.

    • 4

relate perguntas