Início / dba / Perguntas / 305649
Accepted
O. Jones
Asked: 2022-01-06 04:20:11 +0800 CST

É necessário um índice exclusivo para um id sequencial (incremento automático)?

  • 772

Se uma coluna AUTO_INCREMENT sequencial não for também a chave primária de uma tabela MySQL, ela precisa ter seu próprio índice UNIQUE para garantir a integridade dos dados a longo prazo?

Estou trabalhando na reindexação de uma tabela MySQL específica para acelerar certas cláusulas WHERE muito comuns. A tabela é do CMS do WordPress . Sua definição fora da distribuição, simplificada, é essa.

CREATE TABLE wp_postmeta (
    meta_id BIGINT UNSIGNED AUTO_INCREMENT,
    post_id BIGINT UNSIGNED,
    meta_key VARCHAR(255),
    meta_value LONGTEXT,
    PRIMARY KEY (meta_id),
    INDEX post_id (post_id),
    INDEX meta_key (meta_key)
);

MariaDB / MySQL (InnoDB) agora usa índices clusterizados, então alterei o PK para este:

    PRIMARY KEY (post_id, meta_key, meta_id)

Isso significa que filtros WHERE como post_id = 42 AND meta_key = 'value'podem ser satisfeitos usando o índice clusterizado e a busca LONGTEXT. Está comprovado que resolve o problema de desempenho dos meus usuários.

A questão é esta: essa tabela reindexada também precisa de um índice exclusivo na coluna de ID de incremento automático para continuar funcionando corretamente a longo prazo? Ou posso omitir esse índice para economizar um pouco de espaço e desempenho de UPDATE/INSERT?

    UNIQUE INDEX meta_id (meta_id),

(Estou ciente de que pode haver designs de tabela muito melhores do que este. Mas minha atribuição atual não me permite alterar a tabela, apenas os índices.)

mysql index-tuning

5 respostas

  1. Acontece que essa foi uma pergunta idiota. O DBMS não permitirá uma coluna de incremento automático sem um índice. Ele lança o erro 1075 nas tentativas de fazer isso.

    Por causa de outros padrões de filtro não mencionados na pergunta, vou com esses índices.

    PRIMARY KEY (post_id, meta_key, meta_id)
UNIQUE INDEX meta_id (meta_id)
INDEX meta_key (meta_key, meta_value(32), post_id)
INDEX meta_value (meta_value(32))

    Os índices de prefixo estão na coluna LONGTEXT. Em meta_keyse eu pudesse fazer

    INDEX meta_key (meta_key, meta_value(32)) INCLUDE(post_id)

    Eu faria, mas este RDBMS não INCLUDE () coisas em índices.

    • 2
  2. Best Answer

    Você quer mudar PRIMARY KEYde

    PRIMARY KEY (meta_id)

    para

    PRIMARY KEY (post_id, meta_key, meta_id)

    Isso é permitido no mecanismo InnoDB, mas requer que você tenha um índice (não necessariamente UNIQUEligado (meta_id)ou um índice com mais colunas e meta_idcomo a primeira coluna.

    Então, respondendo a última pergunta:

    Ou posso omitir esse índice para economizar um pouco de espaço e desempenho de UPDATE/INSERT?

    Não, você ainda precisará de um índice no meta_id e (único ou não) do espaço extra.

    Reclassificando a pergunta sobre integridade:

    Se uma coluna AUTO_INCREMENT sequencial não for também a chave primária de uma tabela MySQL, ela precisa ter seu próprio índice UNIQUE para garantir a integridade dos dados a longo prazo ?

    Isso realmente depende se a exclusividade é necessária para o (meta_id). Você terá que verificar se algum outro procedimento do WordPress e seu aplicativo dependem de (meta_id) ser exclusivo.

    Caso contrário, você não precisa ter o índice como UNIQUE.

    Como precaução, porém, sugiro que você o mantenha como ÚNICO. Você nunca sabe se em alguns anos, você ou o próximo desenvolvedor decide mudar o PK de volta para o padrão ou se você adiciona um plugin que quebra com valores meta_id não exclusivos. Ou mude para uma plataforma diferente e ter esses recursos exclusivos simplifica a migração.

    Voltando a economizar espaço, você pode considerar que com o novo PK com 3 colunas, qualquer outro índice secundário (único ou não) tem todas essas 3 colunas incluídas também.

    Então seus índices:

    PRIMARY KEY (post_id, meta_key, meta_id),
UNIQUE INDEX (meta_id),
INDEX post_id (post_id),
INDEX meta_key (meta_key)

    são realmente (sob o capô):

    PRIMARY KEY (post_id, meta_key, meta_id),
UNIQUE INDEX (meta_id) INCLUDE (post_id, meta_key),
INDEX post_id (post_id) INCLUDE (meta_key, meta_id),
INDEX meta_key (meta_key) INCLUDE (post_id, meta_id)

    O post_idíndice é, portanto, redundante e você pode eliminá-lo sem nenhuma perda. Qualquer consulta que antes usasse o post_id, agora pode usar o PK.

    Re: comprimento do PK. Se houver exatamente um índice secundário, o espaço em disco necessário ao ter PRIMARY KEY(id), INDEX(foo, bar)é quase idêntico ao espaço para arquivos PRIMARY KEY(foo, bar, id), INDEX(id).

    Se houver mais de um índice secundário, um PK maior levará a mais espaço em disco.

    • 2

  3. Resposta curta: no InnoDB, a coluna de incremento automático deve ser a coluna mais à esquerda de algum índice, seja o índice de chave primária (agrupado), ou algum índice secundário. Não precisa ser uma chave exclusiva, qualquer índice de árvore B serve.

    O seguinte não é válido...

    PRIMARY KEY (post_id, meta_key, meta_id)

    a menos que outro índice (também conhecido como chave) seja definido com meta_id como a coluna mais à esquerda.

    KEY (meta_id)

    Como uma coluna de incremento automático é naturalmente única por si só, é incomum criar uma chave primária ou exclusiva da coluna de incremento automático junto com outras colunas.

    • 1 
  4. INDEX(meta_id)

    É o mínimo necessário para satisfazer o MySQL. (Como mencionado em outras respostas.)

    No entanto, isso permite que o usuário adicione explicitamentemeta_id um valor duplicado para . Presumivelmente , o WP nunca faz essa coisa 'burra'.

    UNIQUE(meta_id)

    Desacelera um INSERTspouco -- porque a inserção não retornará ao cliente até que a exclusividade seja verificada. Com um plain INDEX(meta_id), o Insert coloca a atualização desse índice no "buffer de alteração" para armazenamento posterior no BTree para esse índice secundário.

    Outra diferença entre UNIQUEe INDEXvem quando SELECTingpor um valor específico para meta_id. Com INDEX, a consulta continua lendo linhas, procurando mais linhas com o mesmo meta_id. Por outro lado, UNIQUEpode parar depois de acertar a primeira ocorrência.

    Nenhuma dessas "diferenças" vale a pena se preocupar ao se preocupar com o desempenho. Estou apenas mencionando-os para completar.

    • 1

  5. Depende do que você quer dizer com " continuar funcionando corretamente a longo prazo "? Um índice exclusivo só fará seu trabalho de impedir que valores duplicados sejam inseridos, independentemente de o campo ter uma especificação de incremento automático ou não. Mas isso não impede que alguém insira manualmente um valor fora de sequência exclusivo no meta_idcampo.

    • 0

relate perguntas