Introduction détaillée à la clé et à l'index dans MySQL

1. Présentation

1. Concepts de base

(1) la clé est la structure physique de la base de données. La première couche est la contrainte, qui est utilisée pour contraindre. données L'unicité et l'exhaustivité ; la première couche est la fonction d'index, qui est utilisée pour créer des index et optimiser la vitesse des requêtes, et a la même fonction que l'index.

(2) Clé ordinaire : Il n'y a pas d'effet de contrainte, mais un index sera établi sur cette clé.

(3) clé primaire : une clé primaire ; une table peut avoir une clé primaire, qui est divisée en une clé primaire unique (contenant une seule colonne) et une clé primaire composite (également appelée clé primaire commune , qui peut contenir plusieurs colonnes) ; une clé primaire de stockage peut être spécifiée, et en même temps standardiser l'unicité des données, un index sera établi sur cette clé ; La clé primaire n'est pas nécessaire, mais elle est fortement recommandée [Quelques bonnes habitudes d'utilisation des clés primaires : pas de modification, pas de réutilisation]

(4) clé unique : standardise l'unicité des données ; en même temps, cette clé sera utilisée pour créer un index sur.

(5) clé étrangère : clé étrangère ; normalise l'intégrité référentielle des données en même temps, un index sera établi sur cette clé ;

(6) index : une dimension de clé, qui peut parfois remplacer le mot-clé clé.

2. Clé primaire et clé unique

(1) Similitudes : contrainte d'unicité

(2) Différences

1) Le point de départ /function est différent : le premier est l'identifiant unique d'une ligne de données, tandis que le second n'est utilisé que pour éviter la duplication des données.
2) Une ou plusieurs colonnes de la première doivent toutes être non nulles ; si l'une des colonnes est nulle, elle deviendra non nulle lors de l'ajout de la clé primaire. Si la clé primaire est supprimée, le caractère nullable de la colonne. va changer. Cette dernière colonne peut être nulle.
3) Une table ne peut avoir qu’une seule clé primaire et peut avoir plusieurs clés uniques. [Une table peut-elle n'avoir pas de clé primaire ? ? ? 】
4) Pour la colonne correspondant à la clé unique, null peut être inséré plusieurs fois (bien que ce soit aussi une sorte de répétition) ; .

2. Syntaxe

1 Ajouter un niveau de champ

(1) lors de la création. Clé ordinaire : créer une table t (id int non null key);

(2) Clé primaire : créer une table t (id int not null primaire key) Les deux ont le même effet, c'est-à-dire spécifier le key spécifie également la clé primaire et ne peut être spécifiée qu'une seule fois dans une table (ne peut pas être spécifiée plusieurs fois en tant que clé primaire commune)

(3) clé unique : créer une table t (id int non null clé unique );

(4) clé étrangère : Cela ne devrait pas fonctionner

(5) index : Toutes les clés ne peuvent pas être transposées index

2. Ajouter - niveau table lors de la création

(1) Clé ordinaire : différente de la spécification au niveau du champ, la clé ordinaire ici n'est plus la même que la clé primaire Même si la clé primaire n'est pas spécifiée, MySQL ne le fera pas. utilisez la clé comme clé primaire.

create table t(id int, key (id)); S'il existe d'autres clés utilisant id (comme une clé étrangère), utilisez d'autres clés pour la nommer If ; aucun n'est nommé, l'identifiant est utilisé ; si plusieurs colonnes sont spécifiées comme clés à la fois, le nom de la première colonne est utilisé comme nom de clé.

create table t(id int, key kismet(id)); préciser le nom de la clé

contrainte : ne peut pas être utilisée, après tout, c'est ordinaire La clé n'a aucun effet de contrainte

(2) clé primaire

créer une table t(id int, clé primaire (id));

créer une table t(id int, clé primaire kismet(id )); peut être exécuté, mais le nom ne fonctionne pas

créer une table t(id int, contrainte kismet Primary Key(id)); , mais le nom ne fonctionne pas

(3) clé unique

create table t(id int, unique key (id)); Les règles de dénomination sont différentes de key. Seule la première colonne est utilisée comme nom de clé

create table t(id int, unique key kismet( id)); Précisez le nom de la clé

create table t(id int, contrainte kismet unique key(id)); Préciser le nom de la clé

(4) clé étrangère [Personnellement, je pense que la soi-disant création de deux clés est deux niveaux de logique, c'est-à-dire les contraintes d'intégrité des données et l'optimisation de l'index]

créer une table t(id int, Foreign key (dage_id) fait référence à dage(id)); peut être exécuté, et le résultat de l'exécution est une clé étrangère automatiquement nommée et une clé ordinaire automatiquement nommée.

créer une table t(id int, les références de clé étrangère kismet(dage_id) dage(id)); clé étrangère et une clé normale nommée kismet.

créer une table t(id int, contrainte kismet Foreign Key(dage_id) références dage(id)); une table nommée La clé étrangère de kismet et une clé normale nommée kismet.

(5) index : La clé en clé et la clé unique (niveau table) peuvent être transposées en index, et l'effet est le même.

3. Après la création

(1) Ajouter une clé : ajouter, par exemple : modifier la table pour ajouter une clé primaire (id)

(2) Supprimer une clé, supprimer, une clé primaire ; Utilisez alter table pour supprimer la clé primaire ; d'autres clés peuvent être supprimées en utilisant leurs noms. Faites attention à la différence entre la suppression de clés et la suppression de colonnes.

4. Afficher les informations : affichez la table de création nom_table ; vous pouvez afficher divers attributs de la table, notamment les attributs clés, le moteur de stockage, le jeu de caractères, l'état de la partition, etc.

3. Clé étrangère

1. Fonction : Elle peut associer deux tables pour assurer la cohérence des données et en implémenter. Opérations en cascade ;

2. Moteur de stockage prenant en charge les clés étrangères : InnoDB, prise en charge de la vérification de la mémoire, d'autres n'ont pas été vérifiés.

3. Syntaxe complète

(1) [Symbole de CONTRAINTE] CLÉ ÉTRANGÈRE [id] (index_col_name, ...) RÉFÉRENCES tbl_name (index_col_name, ...)

[Sur suppression {RESTRICT | Cascade | Définir NULL | Définir DEFAUT}]

(2) Utilisation : Cette syntaxe peut être utilisée lors de la création d'une table et de la modification d'une table

(3) Le symbole CONSTRAINT spécifie le nom de la clé. S'il n'est pas spécifié,

le sera. généré automatiquement (4) lors de la suppression et de la mise à jour représentent les paramètres de déclenchement d'événements. Les paramètres peuvent être définis :

RESTRICT (restreindre les modifications de clé étrangère dans l'apparence, par défaut)

CASCADE (suivre les changements de clé étrangère)

SET NULL (définir la valeur nulle)

CONFIGURER PAR DÉFAUT

AUCUNE ACTION

4. Exemple

(1) Créer une table, définir des clés étrangères et insérer des données

<span class="pln">CREATE TABLE <span class="str">`dage`<span class="pln"> <span class="pun">(</span></span></span></span>

<span class="pln"> <span class="str">`id`<span class="pln"> <span class="kwd">int<span class="pun">(<span class="lit">11<span class="pun">)<span class="pln"> NOT NULL auto_increment<span class="pun">,</span></span></span></span></span></span></span></span></span>

<span class="pln"> <span class="str">`name`<span class="pln"> varchar<span class="pun">(<span class="lit">32<span class="pun">)<span class="pln"> <span class="kwd">default<span class="pln"> <span class="str">''<span class="pun">,</span></span></span></span></span></span></span></span></span></span></span>

<span class="pln"> PRIMARY KEY <span class="pun">(<span class="str">`id`<span class="pun">)</span></span></span></span>

<span class="pun">);</span>

<span class="pln">CREATE TABLE <span class="str">`xiaodi`<span class="pln"> <span class="pun">(</span></span></span></span>

<span class="pln"> <span class="str">`id`<span class="pln"> <span class="kwd">int<span class="pun">(<span class="lit">11<span class="pun">)<span class="pln"> NOT NULL auto_increment<span class="pun">,</span></span></span></span></span></span></span></span></span>

<span class="pln"> <span class="str">`dage_id`<span class="pln"> <span class="kwd">int<span class="pun">(<span class="lit">11<span class="pun">)<span class="pln"> <span class="kwd">default<span class="pln"> NULL<span class="pun">,</span></span></span></span></span></span></span></span></span></span></span>

<span class="pln"> <span class="str">`name`<span class="pln"> varchar<span class="pun">(<span class="lit">32<span class="pun">)<span class="pln"> <span class="kwd">default<span class="pln"> <span class="str">''<span class="pun">,</span></span></span></span></span></span></span></span></span></span></span>

<span class="pln"> PRIMARY KEY <span class="pun">(<span class="str">`id`<span class="pun">),</span></span></span></span>

<span class="pln"> CONSTRAINT <span class="str">`xiaodi_ibfk_1`<span class="pln"> FOREIGN KEY <span class="pun">(<span class="str">`dage_id`<span class="pun">)<span class="pln"> REFERENCES <span class="str">`dage`<span class="pln"> <span class="pun">(<span class="str">`id`<span class="pun">)</span></span></span></span></span></span></span></span></span></span></span></span>

<span class="pun">);</span>

insert into dage(name) values('铜锣湾'<span class="pun">);</span>

insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A'<span class="pun">);</span>

(2) Si dans S'il y a un frère cadet, supprimez le frère aîné. Le résultat est le suivant :

(3) Si vous souhaitez insérer de force le frère cadet sans établir le frère aîné, le résultat est le suivant

<span class="pun">[<span class="pln">SQL<span class="pun">]<span class="pln"> <span class="kwd">delete<span class="pln"> <span class="kwd">from<span class="pln"> dage <span class="kwd">where<span class="pln"> id<span class="pun">=<span class="lit">1<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span>

<span class="pun">[<span class="typ">Err<span class="pun">]<span class="pln"> <span class="lit">1451<span class="pln"> <span class="pun">-<span class="pln"> <span class="typ">Cannot<span class="pln"> <span class="kwd">delete<span class="pln"> <span class="kwd">or<span class="pln"> update a parent row<span class="pun">:<span class="pln"> a foreign key constraint fails <span class="pun">(<span class="str">`sample`<span class="pun">.<span class="str">`xiaodi`<span class="pun">,<span class="pln"> CONSTRAINT <span class="str">`xiaodi_ibfk_1`<span class="pln"> FOREIGN KEY <span class="pun">(<span class="str">`dage_id`<span class="pun">)<span class="pln"> REFERENCES <span class="str">`dage`<span class="pln"> <span class="pun">(<span class="str">`id`<span class="pun">))</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

(4) Modifier les paramètres de déclenchement d'événement

[SQL] insert into xiaodi(dage_id,name) values(2,'旺角_小弟A'<span class="pun">);</span>

<span class="pln">alter table xiaodi drop foreign key xiaodi_ibfk_1<span class="pun">;</span></span>

<span class="pln">alter table xiaodi add foreign key<span class="pun">(<span class="pln">dage_id<span class="pun">)<span class="pln"> references dage<span class="pun">(<span class="pln">id<span class="pun">)<span class="pln"> on <span class="kwd">delete<span class="pln"> cascade on update cascade<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span>

(5) Si vous supprimez le frère aîné alors qu'il y a encore un frère cadet : le frère aîné et le cadet le frère correspondant au frère aîné sera supprimé ensemble ; Si vous souhaitez insérer de force le frère cadet sans établir le frère aîné, le résultat ne changera pas, c'est-à-dire qu'il échouera.

4. Index [Référence :]

1. >

(1) Rôle : les index ont un impact crucial sur la vitesse des requêtes. S'il n'y a pas d'index, la requête analysera la table entière ; s'il y a un index, la requête analysera uniquement l'index. Étant donné que les données de la base de données ne sont pas dans la mémoire, chaque requête doit transférer les données du disque dur vers la mémoire, et les E/S perdront beaucoup de temps. Étant donné que les index sont beaucoup plus petits que les données, leur utilisation peut améliorer considérablement la vitesse des requêtes, en particulier lorsque la quantité de données est importante.

(2) L'index est implémenté dans le moteur de stockage, pas dans la couche serveur. Par conséquent, les index de chaque moteur de stockage ne sont pas nécessairement exactement les mêmes et tous les moteurs de stockage ne prennent pas en charge tous les types d'index. Actuellement, le moteur de stockage le plus couramment utilisé est InnoDB.

2. Sélectionnez le type de données de l'index : MySQL prend en charge de nombreux types de données, et le choix du type de données approprié pour stocker les données a un grand impact sur les performances. De manière générale, certaines directives peuvent être suivies [(1)(2) ne s'applique pas aux index de hachage] :

(1) Les types de données plus petits sont généralement meilleurs : Les types de données plus petits sont généralement meilleurs Nécessite moins d'espace sur le disque , la mémoire et le cache du processeur, et traite plus rapidement.

(2) Les types de données simples sont meilleurs : les données entières ont moins de surcharge de traitement que les caractères car la comparaison des chaînes est plus complexe. Dans MySQL, vous devez utiliser les types de données date et heure intégrés au lieu de chaînes pour stocker l'heure et utiliser des types de données entiers pour stocker les adresses IP.

Notez que pour les index, si vous pouvez utiliser des entiers, n'utilisez pas de chaînes, surtout lorsque la quantité de données est importante ; un inconvénient des entiers est que la coopération avec le client peut nécessiter un travail supplémentaire (en particulier pour les grands entiers) , mais a peu d’impact sur l’efficacité.

(3) Essayez d'éviter NULL : les colonnes doivent être spécifiées comme NOT NULL, sauf si vous souhaitez stocker NULL. Dans MySQL, les colonnes contenant des valeurs nulles sont difficiles à optimiser dans les requêtes car elles compliquent les index, les statistiques d'index et les opérations de comparaison. Vous devez remplacer les valeurs nulles par 0, une valeur spéciale ou une chaîne vide.

3. Index B-tree : Le résultat est B-tree (arbre binaire équilibré)

(1) Aperçu : La valeur stockée dans l'index est le suivant L'ordre dans la colonne d'index. Vous pouvez utiliser l'index B-Tree pour effectuer des requêtes complètes de mots clés, de plages de mots clés et de préfixes de mots clés.

Si plusieurs colonnes sont indexées (index combiné), l'ordre des colonnes est très important

, MySQL ne peut effectuer une recherche efficace que sur le préfixe le plus à gauche de l'index. (2) Exemple : Son index contient les colonnes last_name, first_name et dob ​​de chaque ligne du tableau.

<span class="pln">CRÉER UNE TABLE <span class="typ">Personnes<span class="pln "> <span class="pun">(<div class="linenums"> <div class="L0"><code class="language-sql"><span class="pln">CREATE TABLE <span class="typ">People<span class="pln"> <span class="pun">(</span></span></span></span>

<span class="pln"> last_name varchar<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span></span></span></span></span><span class="pln"> nom de famille varchar<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> <span class="kwd">pas<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span></span></span></span></span>

<span class="pln"> first_name varchar<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span></span></span></span></span>

<span class="pln"> dob date <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span>

<span class="pln"> gender <span class="kwd">enum<span class="pun">(<span class="str">'m'<span class="pun">,<span class="pln"> <span class="str">'f'<span class="pun">)<span class="pln"> <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span></span></span></span></span></span></span></span></span>

<span class="pln"> prénom varchar<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> <span class="kwd">pas<span class="pln"> <span class="kwd">null<span class="pun">, </span></span></span></span></span></span></span></span></span><classe span="pln"> date de naissance <span class="kwd">pas<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></classe><span class="pln"> sexe <span class="kwd">enum<span class=" pun">(<span class="str">'m'<span class="pun">,<span class="pln"> <span class="str">'f'<span class="pun">)<span class="pln"> <span class="kwd">pas<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span></span></span></span></span></span></span></span></span>

<span class="pln"> key<span class="pun">(<span class="pln">last_name<span class="pun">,<span class="pln"> first_name<span class="pun">,<span class="pln"> dob<span class="pun">)</span></span></span></span></span></span></span></span>

<span class="pun">);</span>

(3) Méthode de correspondance : vous pouvez rechercher ou trier par [Les résultats sont triés, la recherche est donc facile Rapide]

1) Faire correspondre toutes les valeurs : Spécifiez des valeurs spécifiques pour toutes les colonnes de l'index.

2) Faites correspondre le préfixe le plus à gauche : vous pouvez utiliser l'index pour trouver la personne dont le nom de famille est Allen, utilisez simplement la première colonne de l'index.

3) Faire correspondre le préfixe de la colonne : par exemple, vous pouvez utiliser l'index pour rechercher des personnes dont le nom de famille commence par J, ce qui utilise uniquement la colonne 1 de l'index. .

4) Requête de plage de valeurs correspondantes : vous pouvez utiliser l'index pour rechercher des personnes dont le nom de famille est compris entre Allen et Barrymore, en utilisant uniquement la première colonne du indice.

5) La partie correspondante est précise et l'autre partie est une correspondance de plage : vous pouvez utiliser l'index pour trouver des personnes dont le nom de famille est Allen et dont le prénom commence par la lettre K.

6) Interroger uniquement l'index : Si les colonnes interrogées sont toutes situées dans l'index, il n'est pas nécessaire de lire la valeur du tuple.

7) Si le champ d'index est A+B, l'index A sera-t-il utilisé lors de l'interrogation de A+C -> 🎜>

(4) Limitations

1) La requête doit commencer à partir de la colonne la plus à gauche de l'index.

2) Une colonne d'index ne peut pas être ignorée. Par exemple, vous ne pouvez pas utiliser un index pour rechercher une personne dont le nom de famille était Smith et qui est née un certain jour.

3) Le moteur de stockage ne peut pas utiliser les colonnes du côté droit de la condition de plage dans l'index. Par exemple, si votre instruction de requête est WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23', la requête utilisera uniquement les deux premières colonnes de l'index car LIKE est une requête par plage. .

4. Indice de hachage

(1) Aperçu

1) L'index de hachage calcule la valeur de hachage via la fonction de hachage pour la récupération et peut trouver le pointeur de ligne des données à rechercher, localisant ainsi les données.

2) La valeur de hachage ne dépend pas du type de données de la colonne. L'index d'une colonne TINYINT est aussi grand que l'index d'une longue chaîne. colonne.

3) Le moteur de stockage mémoire prend en charge les index de hachage non uniques. Si plusieurs valeurs ont le même code de hachage, l'index enregistre leurs pointeurs de ligne dans un fichier. liste chaînée au même emplacement dans une entrée de table de hachage.

(2) Limitations

1) Étant donné que l'index ne contient que du code de hachage et un pointeur d'enregistrement, MySQL ne peut pas éviter lire des enregistrements à l'aide d'index. Mais l'accès aux enregistrements en mémoire est très rapide et n'aura pas beaucoup d'impact sur les performances.

2) Le tri par index de hachage ne peut pas être utilisé.

3) L'index de hachage ne prend pas en charge la correspondance partielle des clés, car la valeur de hachage est calculée à travers la valeur entière de l'index.

4) L'index de hachage ne prend en charge que la comparaison d'égalité, par exemple en utilisant =, IN() et <=>. Pour WHERE price>100, cela n’accélère pas la requête.

(3) Exemple

<classe span="pln">CREATE TABLE testhash <span class="pun">(<div class="L0"><code class="language-sql"><span class="pln">CREATE TABLE testhash <span class="pun">(</span></span>

<span class="pln"> fname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NOT NULL<span class="pun">,<code class="language-sql"><span class="pln">   fname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NOT NULL<span class="pun">,</span></span></span></span></span></span>

<span class="pln">   lname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NOT NULL<span class="pun">,</span></span></span></span></span></span>

<span class="pln">   <em><span style="color: #000000">KEY USING HASH</span></em><em><span style="color: #000000"><span class="pun">(<span class="pln">fname<span class="pun">)</span></span></span></span></em></span>

<span class="pln"> lname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NON NULL<span class="pun">,<div class="L4"><code class="language-sql"><span class="pun">)<span class="pln">ENGINE<span class="pun">=<span class="pln">MEMORY<span class="pun">;</span></span></span></span></span>

<span class="pln"> <em><span style="color: # 000000">CLÉ UTILISANT LE HASH<div><em><span style="color: #000000"><span class="pun">(<span class="pln">fname<span class=" jeu de mots">)</span></span></span></span></em></div> <div> <div></div> <div></div> </div> <code class="langue-sql"><span class="pun">)<span class="pln">MOTEUR<span class="pun">=<span class="pln">MÉMOIRE<span class="pun">;<div></div> <div></div> </span></span></span></span></span>

5. Autres index (1) Espace (R-Tree) Index : MyISAM prend en charge les index spatiaux, principalement pour les types de données géospatiales tels que la GÉOMÉTRIE. (2) Index de texte intégral : L'index de texte intégral est un type d'index spécial de MyISAM, principalement utilisé pour la récupération de texte intégral.