Início / dba / Perguntas / 216125
Accepted
Jon of All Trades
Asked: 2018-08-29 13:59:01 +0800 CST

Pode-se fazer o cluster MySQL em um índice exclusivo em vez de uma chave primária?

  • 772

A documentação do MySQL afirma que:

Quando você define uma PRIMARY KEY em sua tabela, o InnoDB a usa como o índice clusterizado.

Mas essa não é a única possibilidade, você pode agrupar um índice exclusivo:

Se você não definir uma PRIMARY KEY para sua tabela, o MySQL localiza o primeiro índice UNIQUE onde todas as colunas de chave são NOT NULL e o InnoDB o usa como o índice clusterizado.

Parece que se eu primeiro criar um índice exclusivo em uma tabela, ele será marcado como clusterizado; Posso então criar uma chave primária e ela não será agrupada:

CREATE TABLE Tmp_CUQTest
(
ID1 INT NOT NULL,  -- Desired clustering field
ID2 INT NOT NULL   -- Desired PK field
);

CREATE UNIQUE INDEX CUC_Tmp ON Tmp_CUQTest (ID1);
ALTER TABLE Tmp_CUQTest ADD CONSTRAINT PRIMARY KEY PK_Tmp (ID2);

No entanto, quando inspeciono a tabela resultante, minhas esperanças são frustradas:

Key      Type               Uni  Columns
PRIMARY  BTREE (clustered)  YES  ID2
ID1      BTREE              YES  ID1

Outra pergunta aqui no DBA.SE implica que criar um índice como parte do CREATE TABLEscript, e não depois, como uma CREATE INDEXdeclaração separada, pode fazer a diferença. No entanto, recebo o mesmo resultado.

Existe uma maneira de forçar o MySQL a agrupar em um índice exclusivo de minha escolha, que não seja a chave primária, ou essa "opção de fallback" se aplica apenas a tabelas sem chave primária?

mysql mysql-5.7

2 respostas

  1. Best Answer

    PRIMARY KEY

    • O InnoDB tem exatamente um PRIMARY KEYem cada tabela.
    • O PRIMARY KEYé sempre agrupado; não pode haver nenhum outro índice "agrupado".
    • Esse PK é um PK explicitamente declarado (é preferível), a primeira UNIQUEchave (com certas limitações) ou um número de sequência oculto (não exatamente o mesmo que `AUTO_INCREMENT; 6 bytes).
    • O PK é UNIQUE e é e index.
    • O PK (e outros índices) pode conter várias colunas (índice "composto".
    • A tabela é estruturada como um BTree (na verdade, um B+Tree), com todas as colunas presentes, e o BTree é ordenado de acordo com o PK.

    Chaves secundárias

    • Todos os outros índices são chamados de "índices secundários". ("chave" == "índice")
    • A estrutura é uma B+Tree, ordenada de acordo com a chave
    • As "linhas" deste BTree contêm cópias da(s) coluna(s) do PRIMARY KEY(ou seja, o 'índice clusterizado'). É assim que a chave secundária pode chegar à linha.
    • Observe que procurar uma linha por meio de uma chave secundária requer o detalhamento de dois BTrees. (A menos que seja um índice de "cobertura").
    • A UNIQUE KEYé duas coisas: uma chave secundária (a menos que seja promovida a PK) e uma "restrição de exclusividade".

    Outras notas

    • O InnoDB não tem o conceito de "ROWNUM" ou "localizador de linha", ao contrário de alguns outros fornecedores. O PK serve como localizador de linha.
    • O MySQL não possui índices "Heap" ou "Bit" no InnoDB.
    • O InnoDB possui apenas BTree, SPATIAL e FULLTEXT; Não estou discutindo os dois últimos nesta resposta.
    • Para a maioria dos propósitos, não importa se você cria os índices dentro da CREATE TABLEinstrução ou faz CREATE INDEXmais tarde. Idem para definir FOREIGN KEYs.
    • Estruturalmente, os dados PK+ são uma árvore B+; cada índice secundário é um B+Tree. Estas B+Trees parecem, sentem, cheiram, etc, idênticas. A única diferença é o que você encontra nas 'linhas'.

    AUTO_INCREMENT

    • Há muitas ressalvas. Não são necessariamente números consecutivos; há muitas maneiras pelas quais as lacunas podem ocorrer. Apenas confie que eles serão 'únicos'.
    • AUTO_INCREMENTnão precisa ser declarado PRIMARY KEY, ou mesmo UNIQUE.

    Um truque . É possível fazer o seguinte. É isso que você está pescando?? 

    CREATE TABLE ... (
  id INT AUTO_INCREMENT,
  foo ...,
  ...
  PRIMARY KEY (foo, id),  -- to get clustering on `foo` and uniqueness (via `id`)
  INDEX(id)               -- sufficient to keep AUTO_INCREMENT happy.
) ENGINE=InnoDB;
    • 2

  2. Uma chave primária é uma chave exclusiva e um índice. Portanto, crie o PK como o índice exclusivo no qual você deseja agrupar.

    Criar adicionando um índice posteriormente e como parte da tabela de criação produz o mesmo resultado.

    • 1

relate perguntas