Différences entre INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
quelles sont les différences entre primaire, UNIQUE, INDEX et FULLTEXT lors de la création de tables MySQL?
comment les utiliser?
4 réponses
différences
-
ou L'indice fait référence à un indice normal non unique. Des valeurs Non distinctes pour l'indice sont autorisées, de sorte que l'indice peut contenir des lignes avec des valeurs identiques dans toutes les colonnes de l'indice. Ces index n'imposent aucune contrainte sur vos données, ils ne sont donc utilisés que pour s'assurer que certaines requêtes peuvent être exécutées 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 rangée peut ne pas avoir des valeurs identiques non nulles pour toutes les colonnes de cet index comme une autre rangée. En plus d'être utilisé pour accélérer les requêtes, des index uniques peuvent être utilisés pour imposer des restrictions sur les données, parce que le système de base de données ne permet pas cette règle de valeurs distinctes à briser lors de l'insertion ou la mise à jour des données.
votre système de base de données peut permet d'appliquer un index UNIQUE aux colonnes qui permettent des valeurs nulles, auquel cas deux lignes peuvent être identiques si elles contiennent toutes deux une valeur NULL (la raison ici est que NULL n'est pas considéré comme égal à lui-même). En fonction de votre application, cependant, vous peut trouver cela indésirable: si vous souhaitez empêcher cela, vous devriez rejeter les valeurs nulles dans les colonnes pertinentes.
-
primaire agit exactement comme un index UNIQUE, sauf qu'il est toujours appelé 'primaire', et il peut y avoir un seul sur une table (et là devrait toujours être un; bien que certains systèmes de base de données ne font pas appliquer cela). Un index primaire est conçu comme un moyen primaire pour identifier de façon unique n'importe quelle ligne dans le tableau, ainsi à la différence D'UNIQUE il ne devrait pas être employé sur n'importe quelles colonnes qui permettent des valeurs nulles. Votre index primaire doit être sur le plus petit nombre de colonnes qui sont suffisantes pour identifier de façon unique rangée. Souvent, il ne s'agit que d'une colonne contenant un nombre incrémenté unique, mais s'il y a quelque chose d'autre qui peut identifier une ligne de façon unique, comme "country code" dans une liste de pays, vous pouvez l'utiliser à la place.
certains systèmes de bases de données (comme InnoDB de MySQL) stockeront les enregistrements d'une table sur disque dans l'ordre où ils apparaissent dans l'index primaire.
-
les index" FULLTEXT sont différents de tout ce qui précède, et leur comportement diffère considérablement entre les systèmes de bases 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 précédents - qui sont généralement implémentés en interne à l'aide d'arborescences b (permettant de sélectionner, de trier ou de faire des plages à partir de la colonne la plus à gauche) ou de tables de hachage (permettant la sélection à partir de la colonne la plus à gauche).
lorsque les autres types d'indices sont à usage général, a L'index FULLTEXT est spécialisé, en ce sens qu'il sert à une fin précise: il n'est utilisé que pour une fonction de "recherche en texte intégral".
similitudes
-
tous ces indices peuvent comporter plus d'une colonne.
-
à l'exception du texte intégral, l'ordre des colonnes est significatif: pour que l'index soit utile dans une requête, la requête doit utiliser les colonnes de l'index à partir de la gauche - il ne peut pas utiliser seulement la deuxième, troisième ou quatrième partie d'un index, à moins qu'il ne soit également en utilisant les colonnes précédentes de l'index pour correspondre à des valeurs statiques. (Pour qu'un index en texte intégral soit utile à une requête, la requête doit utiliser toutes les colonnes de l'index.)
ce sont tous des types d'indices.
primaire: doit être unique, est un indice, est (probablement) l'Indice physique, peut être seulement un par tableau.
unique: comme il est dit. Vous ne pouvez pas avoir plus d'une ligne avec un tuple de cette valeur. A noter que depuis une clé unique peut être sur plus d'une colonne, cela ne signifie pas nécessairement que chaque colonne de l'index est unique, mais que chaque la combinaison des valeurs entre ces colonnes est unique.
index: s'il n'est pas primaire ou unique, il ne contraint pas les valeurs insérées dans le tableau, mais il permet de les rechercher plus efficacement.
fulltext: une forme plus spécialisée d'indexation qui permet la recherche en texte intégral. Il pense que (essentiellement) la création d'un "indice" pour chaque "mot" dans la colonne spécifiée.
je pense que cela a été bien couvert, peut-être à l'exception de ce qui suit:
-
Simple
KEY/INDEX(ou autrement appeléSECONDARY INDEX) font augmenter la performance si la sélectivité est suffisante. Sur ce point, la recommandation habituelle est que si le nombre d'enregistrements dans le jeu de résultats sur lesquels un index est appliquée dépasse 20% du montant total d'enregistrements de la table parent, alors que l'indice sera inefficace. Dans pratiquer chaque architecture sera différente mais, l'idée est toujours correcte. -
les index secondaires (et cela est très spécifique à 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 cette information connue, fournir un outil supplémentaire à la DBA mysql: dans Mysql, les index intègrent la clé primaire. Elle entraîne des améliorations importantes du rendement, particulièrement lorsque: astucieusement construire des indices de couverture implicite tels que décrit là
"
-
si vous pensez que vos données devraient être
UNIQUE, utilisez un index unique. Vous pouvez penser qu'il est optionnel (par exemple, le faire au niveau de l'application) et qu'un index normal fera l'affaire, mais il représente en fait une garantie pour Mysql que chaque ligne est unique, ce qui fournit incidemment un avantage de performance. -
Vous pouvez seulement utiliser
FULLTEXT(ou autrement appeléSEARCH INDEX) avec Innodb (MySQL 5.6.4) et Myisam Moteurs - vous ne pouvez utiliser
FULLTEXTque surCHAR,VARCHARetTEXTtypes de colonnes -
FULLTEXTl'indice implique bien plus que la simple création d'un indice. Il y a un tas de tables système créées, un système de mise en cache complètement séparé et quelques règles spécifiques et optimisation appliquée. Voir http://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.html et http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html
primaire
un index unique où toutes les colonnes clés doivent être définies comme N'étant pas nulles. Si elles ne sont pas explicitement déclarées comme non nulles, MySQL Les déclare implicitement (et silencieusement). Une table ne peut avoir qu'une seule clé primaire. Le nom D'une clé primaire est toujours primaire, qui ne peut donc pas être utilisé comme nom pour tout autre type d'index.
si vous n'avez pas de clé primaire et qu'une application demande la clé primaire dans vos tables, MySQL retourne le premier index UNIQUE qui n'a pas de colonnes nulles comme clé primaire.
dans les tables InnoDB, garder la touche primaire courte afin de minimiser les frais généraux de stockage pour les index secondaires. Chaque entrée de l'index secondaire contient une copie des colonnes des clés primaires de la rangée correspondante.
dans la table créée, une clé primaire est placée en premier, suivie de tous les index uniques, puis des index non uniques. Cela aide L'optimiseur MySQL à prioriser quel indice utiliser et aussi plus rapidement pour détecter les clés uniques dupliquées.
une clé primaire peut être un index à colonnes multiples. Cependant, vous ne pouvez pas créer un index à colonnes multiples en utilisant l'attribut clé primaire dans une spécification de colonne. Ce faisant, il marque seulement cette colonne unique comme principale. Vous devez utiliser une clé primaire séparée(index_col_name,...)clause.
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 in SELECT statements.
UNIQUE
un indice UNIQUE crée une contrainte telle que toutes les valeurs de l'indice 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 permet des valeurs nulles multiples pour les colonnes qui peuvent 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 à l'intérieur du préfixe. Si un index unique se compose de seulement une colonne qui a un type entier, vous pouvez également vous référer à la colonne comme _rowid dans les déclarations SELECT.
CLÉ / INDEX
est normalement synonyme D'INDEX. fait référence à un indice normal non unique.
FULLTEXT
un index en texte intégral est un type particulier d'index utilisé pour les recherches en texte intégral. Seuls les moteurs de stockage InnoDB et MyISAM prennent en charge les index FULLTEXT. Ils ne peuvent être créés qu'à partir de CHAR, VARCHAR, et les colonnes de TEXTE. L'indexation s'effectue toujours sur toute la colonne; l'indexation par préfixe de colonne n'est pas supportée et toute longueur de préfixe est ignorée si elle est spécifiée. Une clause avec PARSER peut être spécifiée en tant que valeur anindex_option pour associer un plugin parser à l'index si les opérations d'indexation et de recherche en texte intégral nécessitent des manipulations spéciales. Cette clause n'est valable que pour les index texte intégral.InnoDB et MyISAM prennent en charge les plugins full-text parser.