Início / dba / Perguntas / 63652
Accepted
BenMorel
Asked: 2014-04-23 07:31:19 +0800 CST

Eficiência da chave primária do InnoDB

  • 772

Eu li vários artigos sobre eficiência de chaves primárias, dependendo do mecanismo de armazenamento, e estou confuso.

Dada uma tabela muitos-para-muitos simples com dois campos storeIde zoneId, qual dos seguintes designs é o mais eficiente com o InnoDB e por quê?

  • usando os dois campos como uma chave primária composta:
CREATE TABLE store_zone(
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(storeId, zoneId)
);
  • Usando uma chave primária específica de incremento automático:
CREATE TABLE store_zone(
    id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY(storeId, zoneId)
);

Notas:

  • Eu preciso de uma chave única no par ( storeId, ) de qualquer maneirazoneId
  • Eu tenho chaves estrangeiras para as tabelas storee zone, não mostradas aqui para facilitar a leitura , portanto, em ambos os casos, há um índice extra necessário zoneIdtambém
mysql innodb

1 respostas

  1. Best Answer

    Análise

    Já que estamos falando do InnoDB, vamos nos concentrar no gen_clust_index. É um índice especial que mantém a PRIMARY KEY e os dados de linha associados acessíveis nas mesmas páginas do InnoDB.

    De acordo com a documentação do MySQL no gen_clust_index

    • Se você definir uma PRIMARY KEY em sua tabela, o InnoDB a usará como o índice clusterizado.

    • Se você não definir uma PRIMARY KEY para sua tabela, o MySQL escolhe o primeiro índice UNIQUE que possui apenas colunas NOT NULL como chave primária e o InnoDB o usa como índice clusterizado.

    • Se a tabela não tiver PRIMARY KEY ou índice UNIQUE adequado, o InnoDB gera internamente um índice clusterizado oculto em uma coluna sintética contendo valores de ID de linha. As linhas são ordenadas pelo ID que o InnoDB atribui às linhas dessa tabela. O ID da linha é um campo de 6 bytes que aumenta monotonicamente à medida que novas linhas são inseridas. Assim, as linhas ordenadas pelo ID da linha estão fisicamente na ordem de inserção.

    Sendo assim, você realmente sentiria eficiência ou deficiência nas seguintes áreas:

    Espaço em disco

    Ter a única coluna auto_increment como PRIMARY KEYmantém o tamanho geral da PRIMARY KEY menor do que ter duas colunas. Por quê? As páginas BTREE seriam duas vezes maiores se PRIMARY KEYfossem dois INTs em vez de um. Isso se torna ainda mais doloroso se você usar restrições de chave estrangeira e índices secundários, pois eles devem aumentar de tamanho também.

    Nesse caso, você escolheria o segundo esquema para melhor uso do índice.

    Inserir desempenho

    Inserir dados de linha em uma tabela InnoDB que possui dois índices UNIQUE exige o dobro de gerenciamento de BTREE e verificações exclusivas.

    • Um para o gen_clust_index (ditado por auto_increment)
    • Um para o outro índice UNIQUE (ditado por (storeId, zoneId))

    Microssegundos adicionais para cada índice UNIQUE adicional seriam somados no tempo de CPU ao inserir milhões de linhas.

    Nesse caso, você escolheria o primeiro esquema para INSERTs mais rápidos.

    Conclusão

    O espaço em disco e os problemas de desempenho de inserção praticamente o forçam a escolher. Obviamente, você escolhe com qual viver. Se você usar restrições de chave estrangeira que representam (storeId, zoneId), precisará do segundo esquema usando o auto_increment como referência em tabelas externas.

    • 5

relate perguntas