Diferenças entre INDEX, PRIMARY, UNIQUE, FULLTEXT no MySQL?

Quais são as diferenças entre PRIMARY, UNIQUE, INDEX e FULLTEXT ao criar tabelas MySQL?

Como eu os usaria?

Diferenças

  • KEY ou INDEX refere-se a um índice não exclusivo normal. Valores não distintos para o índice são permitidos, portanto, o índice pode conter linhas com valores idênticos em todas as colunas do índice. Esses índices não impõem restrições aos seus dados, portanto, eles são usados ​​apenas para garantir que determinadas consultas possam ser executadas rapidamente.

  • UNIQUE refere-se a um índice em que todas as linhas do índice devem ser exclusivas. Ou seja, a mesma linha pode não ter valores não-NULL idênticos para todas as colunas nesse índice como outra linha. Além de ser usado para acelerar as consultas, os índices UNIQUE podem ser usados ​​para impor restrições aos dados, porque o sistema de database não permite que essa regra de valores distintos seja quebrada ao inserir ou atualizar dados.

    Seu sistema de database pode permitir que um índice UNIQUE seja aplicado a colunas que permitem valores NULL, caso em que duas linhas podem ser idênticas se ambas contiverem um valor NULL (a lógica aqui é que NULL é considerado diferente de si mesmo). Dependendo da sua aplicação, no entanto, você pode achar isso indesejável: se você quiser evitar isso, você deve proibir os valores NULL nas colunas relevantes.

  • O PRIMARY atua exatamente como um índice UNIQUE, exceto pelo fato de ser sempre denominado ‘PRIMARY’, e pode haver apenas um em uma tabela (e deve sempre haver um, embora alguns sistemas de database não imponham isso). Um índice PRIMARY destina-se como um meio primário para identificar exclusivamente qualquer linha na tabela, portanto, ao contrário de UNIQUE, ele não deve ser usado em nenhuma coluna que permita valores NULL. Seu índice PRIMARY deve estar no menor número de colunas que sejam suficientes para identificar exclusivamente uma linha. Freqüentemente, essa é apenas uma coluna que contém um número auto-incrementado exclusivo, mas se houver algo mais que possa identificar uma linha, como “countrycode” em uma lista de países, você poderá usá-la.

    Alguns sistemas de database (como o InnoDB do MySQL) armazenam os registros de uma tabela no disco na ordem em que aparecem no índice PRIMARY.

  • Os índices FULLTEXT são diferentes de todos os acima, e seu comportamento difere significativamente entre os sistemas de database. Os índices FULLTEXT são úteis apenas para pesquisas de texto completo feitas com a cláusula MATCH () / AGAINST (), ao contrário dos três acima – que são tipicamente implementados internamente usando b-trees (permitindo seleção, sorting ou intervalos começando da coluna mais à esquerda) ou tabelas de hash (permitindo a seleção a partir da coluna mais à esquerda).

    Onde os outros tipos de índices são de propósito geral, um índice FULLTEXT é especializado, pois tem um propósito restrito: é usado apenas para um recurso de “pesquisa de texto completo”.

Semelhanças

  • Todos esses índices podem ter mais de uma coluna.

  • Com a exceção de FULLTEXT, a ordem das colunas é significativa: para que o índice seja útil em uma consulta, a consulta deve usar colunas do índice a partir da esquerda – não pode usar apenas a segunda, terceira ou quarta parte de um índice, a menos que também esteja usando as colunas anteriores no índice para corresponder aos valores estáticos. (Para um índice FULLTEXT ser útil para uma consulta, a consulta deve usar todas as colunas do índice.)

Todos esses são tipos de índices.

primary: deve ser exclusivo, é um índice, é (provavelmente) o índice físico, pode ser apenas um por tabela.

único: como diz. Você não pode ter mais de uma linha com uma tupla desse valor. Observe que, como uma chave exclusiva pode ter mais de uma coluna, isso não significa necessariamente que cada coluna individual do índice seja única, mas que cada combinação de valores nessas colunas seja exclusiva.

índice: se não for principal ou exclusivo, não restringirá os valores inseridos na tabela, mas permitirá que eles sejam pesquisados ​​com mais eficiência.

Texto completo: uma forma mais especializada de indexação que permite a pesquisa de texto completo. Pense nisso como (essencialmente) criando um “índice” para cada “palavra” na coluna especificada.

Eu sinto que isso tem sido bem coberto, talvez com exceção do seguinte:

  • Simples KEY / INDEX (ou chamado de SECONDARY INDEX ) aumenta o desempenho se a seletividade for suficiente. Sobre esse assunto, a recomendação usual é que, se a quantidade de registros no conjunto de resultados no qual um índice é aplicado exceder 20% da quantidade total de registros da tabela pai, o índice será ineficaz. Na prática, cada arquitetura será diferente, mas a idéia ainda está correta.

  • Os índices secundários (e isso é muito específico para o mysql) não devem ser vistos como objects completamente separados e diferentes da chave primária. Na verdade, ambos devem ser usados ​​em conjunto e, uma vez que essa informação seja conhecida, fornecer uma ferramenta adicional para o mysql DBA: no Mysql, os índices incorporam a chave primária. Isso leva a melhorias significativas de desempenho, especificamente ao criar inteligentemente índices de cobertura implícitos, como descrito aqui

  • Se você acha que seus dados devem ser UNIQUE , use um índice exclusivo. Você pode pensar que é opcional (por exemplo, trabalhá-lo no nível do aplicativo) e que um índice normal servirá, mas na verdade representa uma garantia para o Mysql que cada linha é única, o que incidentalmente fornece um benefício de desempenho.

  • Você só pode usar FULLTEXT (ou chamado SEARCH INDEX ) com Innodb (no MySQL 5.6.4 e acima) e Myisam Engines

  • Você só pode usar FULLTEXT nos tipos de coluna CHAR , VARCHAR e TEXT
  • FULLTEXT índice FULLTEXT envolve muito mais do que apenas criar um índice. Há um monte de tabelas de sistema criadas, um sistema de cache completamente separado e algumas regras específicas e otimizações aplicadas. Veja http://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.html e http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

PRIMÁRIO

Um índice exclusivo em que todas as colunas-chave devem ser definidas como NOT NULL. Se eles não forem explicitamente declarados como NOT NULL, o MySQL os declarará de forma tão implícita (e silenciosa). Uma tabela pode ter apenas uma chave PRIMARY. O nome de uma PRIMARY KEY é sempre PRIMARY, o que, portanto, não pode ser usado como o nome de qualquer outro tipo de índice.

Se você não tiver uma PRIMARY KEY e um aplicativo solicitar a PRIMARY KEY em suas tabelas, o MySQL retornará o primeiro índice UNIQUE que não possui colunas NULL como PRIMARY KEY.

Em tabelas InnoDB, mantenha o PRIMARY KEY curto para minimizar a sobrecarga de armazenamento para índices secundários. Cada input de índice secundário contém uma cópia das colunas de chave primária para a linha correspondente.

Na tabela criada, uma PRIMARY KEY é colocada primeiro, seguida por todos os índices UNIQUE e, em seguida, pelos índices não exclusivos. Isso ajuda o otimizador do MySQL a priorizar qual índice usar e também mais rapidamente para detectar chaves UNIQUE duplicadas.

A PRIMARY KEY pode ser um índice de múltiplas colunas. No entanto, você não pode criar um índice de várias colunas usando o atributo de chave PRIMARY KEY em uma especificação de coluna. Fazer isso apenas marca essa única coluna como principal. Você deve usar uma cláusula PRIMARY KEY (index_col_name, …) separada.

Se uma PRIMARY KEY consiste em apenas uma coluna que possui um tipo inteiro, você também pode se referir à coluna como _rowid em instruções SELECT.

ÚNICO

Um índice UNIQUE cria uma restrição de modo que todos os valores no índice devam ser distintos. Ocorre um erro se você tentar adicionar uma nova linha com um valor de chave que corresponda a uma linha existente. Para todos os mecanismos, um índice UNIQUE permite vários valores NULL para colunas que podem conter NULL. Se você especificar um valor de prefixo para uma coluna em um índice UNIQUE, os valores da coluna deverão ser exclusivos dentro do prefixo. Se um índice UNIQUE consiste em apenas uma coluna que possui um tipo inteiro, você também pode se referir à coluna como _rowid em instruções SELECT.

CHAVE | ÍNDICE

KEY é normalmente um sinônimo de INDEX. refere-se a um índice não exclusivo normal.

TEXTO COMPLETO

Um índice FULLTEXT é um tipo especial de índice usado para pesquisas de texto completo. Somente os mecanismos de armazenamento InnoDB e MySAM suportam índices FULLTEXT. Eles podem ser criados apenas nas colunas CHAR, VARCHAR e TEXT. A indexação sempre acontece em toda a coluna; a indexação de prefixo de coluna não é suportada e qualquer comprimento de prefixo é ignorado, se especificado. Uma cláusula WITH PARSER pode ser especificada como um valor anindex_option para associar um plug-in do analisador ao índice, se as operações de indexação e pesquisa de texto completo precisarem de tratamento especial. Esta cláusula é válida apenas para índices FULLTEXT. O InnoDB e o MyISAM suportam plug-ins de analisador de texto completo.