Question Différences entre INDEX, PRIMARY, UNIQUE, FULLTEXT dans MySQL?


Quelles sont les différences entre PRIMARY, UNIQUE, INDEX et FULLTEXT lors de la création de tables MySQL?

Comment pourrais-je les utiliser?


527
2018-04-02 00:32


origine


Réponses:


Différences

  • CLÉ ou INDICE se réfère à un index normal non-unique. Les valeurs non distinctes pour l'index sont autorisées, donc l'index mai contient des lignes avec des valeurs identiques dans toutes les colonnes de l'index. Ces index n'appliquent aucune contrainte sur vos données afin qu'ils soient utilisés uniquement pour s'assurer que certaines requêtes peuvent s'exécuter rapidement.

  • UNIQUE fait référence à un index où toutes les lignes de l'index doivent être uniques. C'est-à-dire que la même ligne peut ne pas avoir des valeurs non-NULL identiques pour toutes les colonnes de cet index en tant qu'une autre ligne. En plus d'être utilisés pour accélérer les requêtes, les index UNIQUE peuvent être utilisés pour appliquer des restrictions sur les données, car le système de base de données ne permet pas de rompre cette règle de valeurs distinctes lors de l'insertion ou de la mise à jour des données.

    Votre système de base de données peut autoriser l'application d'un index UNIQUE aux colonnes qui autorisent les valeurs NULL, auquel cas deux lignes peuvent être identiques si elles contiennent toutes les deux une valeur NULL (la logique est que NULL est considéré comme non égal à lui-même). En fonction de votre application, cependant, vous mai trouvez ceci indésirable: si vous souhaitez éviter cela, vous devez interdire les valeurs NULL dans les colonnes appropriées.

  • PRIMAIRE agit exactement comme un index UNIQUE, sauf qu'il est toujours nommé "PRIMAIRE", et il peut y avoir un seul sur une table (et il devrait toujours être un; bien que certains systèmes de base de données ne l'appliquent pas). Un index PRIMARY est conçu comme un moyen principal pour identifier de façon unique n'importe quelle ligne dans la table, donc contrairement à UNIQUE, il ne doit pas être utilisé sur les colonnes qui autorisent les valeurs NULL. Votre index PRIMARY doit être sur le plus petit nombre de colonnes suffisant pour identifier une ligne de manière unique. Souvent, il s'agit d'une seule colonne contenant un numéro unique auto-incrémenté, mais s'il y a autre chose qui peut identifier une ligne de façon unique, comme "countrycode" dans une liste de pays, vous pouvez l'utiliser à la place.

    Certains systèmes de base de données (tels que InnoDB de MySQL) stockent les enregistrements d'une table sur le disque dans l'ordre dans lequel ils apparaissent dans l'index PRIMARY.

  • TEXTE INTÉGRAL les index sont différents de tout ce qui précède, et leur comportement diffère considérablement entre les systèmes de base de données. Les index FULLTEXT ne sont utiles que pour les recherches en texte intégral effectuées avec la clause MATCH () / AGAINST (), contrairement aux trois exemples ci-dessus qui sont généralement implémentés en interne à l'aide de b-trees (permettant de sélectionner, trier ou tables de hachage (permettant la sélection à partir de la colonne la plus à gauche).

    Lorsque les autres types d'index sont à usage général, un index FULLTEXT est spécialisé, en ce sens qu'il sert un objectif précis: il est uniquement utilisé pour une fonctionnalité de "recherche en texte intégral".

Similitudes

  • Tous ces index peuvent contenir plus d'une colonne.

  • A l'exception de FULLTEXT, l'ordre des colonnes est significatif: pour que l'index soit utile dans une requête, la requête doit utiliser des colonnes de l'index en partant de la gauche - il ne peut pas utiliser seulement la deuxième, troisième ou quatrième partie index, sauf s'il utilise également les colonnes précédentes de l'index pour faire correspondre des valeurs statiques. (Pour qu'un index FULLTEXT soit utile à une requête, la requête doit utiliser tout colonnes de l'index.)


602
2018-04-02 06:22



Tous sont des sortes d'indices.

primaire: doit être unique, est un index, est (probablement) l'index physique, peut être seulement un par table.

unique: comme il est dit Vous ne pouvez pas avoir plus d'une ligne avec un tuple de cette valeur. Notez que, comme une clé unique peut contenir plus d'une colonne, cela ne signifie pas nécessairement que chaque colonne de l'index est unique, mais que chaque combinaison de valeurs entre ces colonnes est unique.

indice: s'il n'est pas primaire ou unique, il ne contraint pas les valeurs insérées dans la table, mais il permet de les rechercher plus efficacement.

texte intégral: une forme plus spécialisée d'indexation qui permet la recherche en texte intégral. Pensez-y comme (essentiellement) la création d'un "index" pour chaque "mot" dans la colonne spécifiée.


132
2018-04-02 00:45



J'ai l'impression que cela a été bien couvert, peut-être à l'exception de ce qui suit:

  • Simple KEY / INDEX (ou autrement appelé SECONDARY INDEX) augmentent les performances si la sélectivité est suffisante. À ce sujet, la recommandation habituelle est que si la quantité d'enregistrements dans le jeu de résultats sur lequel un index est appliqué dépasse 20% du nombre total d'enregistrements de la table parent, alors l'index sera inefficace. En pratique, chaque architecture sera différente, mais l'idée est toujours correcte.

  • Les index secondaires (et qui sont très spécifiques à mysql) ne doivent pas être considérés comme des objets complètement séparés et différents de la clé primaire. En fait, les deux devraient être utilisés conjointement et, une fois que cette information est connue, fournir un outil supplémentaire au DBA mysql: dans Mysql, les index intègrent la clé primaire. Cela conduit à des améliorations significatives des performances, en particulier lors de la création intelligente d'index de couverture implicites tels que décrit là

  • Si vous pensez que vos données devraient être UNIQUE, utilisez un index unique. Vous pouvez penser que c'est facultatif (par exemple, travailler au niveau de l'application) et qu'un index normal fera l'affaire, mais cela représente en fait une garantie pour Mysql que chaque rangée est unique, ce qui procure un avantage de performance.

  • Vous ne pouvez utiliser FULLTEXT (ou autrement appelé SEARCH INDEX) avec Innodb (MySQL 5.6.4 et plus) et Myisam Engines

  • Vous ne pouvez utiliser FULLTEXT sur CHAR, VARCHAR et TEXT types de colonnes
  • FULLTEXT L'index implique BEAUCOUP plus que simplement créer un index. Il y a un tas de tables système créées, un système de mise en cache complètement séparé et des règles spécifiques et des optimisations appliquées. Voir http://dev.mysql.com/doc/refman/5.7/fr/fulltext-restrictions.html et http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

11
2018-01-19 01:54



PRIMAIRE

Un index unique où toutes les colonnes clés doivent être définies comme NOT NULL. Si elles ne sont pas explicitement déclarées comme NOT NULL, MySQL les déclare implicitement (et silencieusement). Une table peut avoir une seule PRIMARY KEY. Le nom d'une PRIMARY KEY est toujours PRIMARY, ce qui ne peut donc pas être utilisé comme nom pour tout autre type d'index.

Si vous n'avez pas de PRIMARY KEY et qu'une application demande la PRIMARY KEY dans vos tables, MySQL retourne le premier index UNIQUE qui n'a pas de colonnes NULL comme PRIMARY KEY.

Dans les tables InnoDB, conservez le raccourci PRIMARY KEY pour minimiser la charge de stockage pour les index secondaires. Chaque entrée d'index secondaire contient une copie des colonnes de clé primaire pour la ligne correspondante.

Dans la table créée, une PRIMARY KEY est placée en premier, suivie de tous les index UNIQUE, puis des index non uniques. Cela aide l'optimiseur de MySQL à prioriser l'index à utiliser et aussi plus rapidement pour détecter les clés UNIQUE dupliquées.

Une clé primaire peut être un index à plusieurs colonnes. Toutefois, vous ne pouvez pas créer un index à plusieurs colonnes à l'aide de l'attribut de clé PRIMARY KEY dans une spécification de colonne. Cela ne marque qu'une seule colonne comme primaire. Vous devez utiliser une clause distincte PRIMARY KEY (index_col_name, ...).

Si une clé primaire se compose d'une seule colonne qui a un type entier, vous pouvez également vous référer à la colonne comme _rowid dans les instructions SELECT.

UNIQUE

Un index UNIQUE crée une contrainte telle que toutes les valeurs de l'index doivent être distinctes. Une erreur se produit si vous essayez d'ajouter une nouvelle ligne avec une valeur de clé qui correspond à une ligne existante. Pour tous les moteurs, un index UNIQUE autorise plusieurs valeurs NULL pour les colonnes pouvant contenir NULL. Si vous spécifiez une valeur de préfixe pour une colonne dans un index UNIQUE, les valeurs de colonne doivent être uniques dans le préfixe. Si un index UNIQUE se compose d'une seule colonne qui a un type entier, vous pouvez également faire référence à la colonne en tant que _rowid dans les instructions SELECT.

KEY | INDICE

KEY est normalement synonyme d'INDEX. se réfère à un index normal non-unique.

TEXTE INTÉGRAL

Un index FULLTEXT est un type d'index spécial utilisé pour les recherches en texte intégral. Seuls les moteurs de stockage InnoDB et MyISAM prennent en charge les index FULLTEXT. Ils peuvent être créés uniquement à partir des colonnes CHAR, VARCHAR et TEXT. L'indexation se produit toujours sur toute la colonne; l'indexation du préfixe de colonne n'est pas prise en charge et toute longueur de préfixe est ignorée si elle est spécifiée. Une clause WITH PARSER peut être spécifiée en tant que valeur anindex_option pour associer un plugin d'analyseur à l'index si l'indexation de texte intégral et les opérations de recherche nécessitent une gestion spéciale. Cette clause est valide uniquement pour les index FULLTEXT.InnoDB et MyISAM prennent en charge les plugins d'analyseur de texte intégral.


0
2018-06-24 02:14