Index MySQL

L'établissement des index de MySQL est très important pour son fonctionnement efficace, les index peuvent considérablement améliorer la vitesse de recherche de MySQL.

Pour faire une analogie, si un MySQL bien conçu et utilisant des index est une Lamborghini, alors un MySQL sans conception et sans utilisation des index est un tricycle à人力.

Prenez en exemple la page de sommaire d'un dictionnaire chinois (index) : nous pouvons rapidement trouver le mot désiré en ordre alphabétique, de pincé, de composants latéraux, etc.

Les index se divisent en index sur une seule colonne et index composés. Un index sur une seule colonne, c'est-à-dire qu'un index ne contient qu'une seule colonne, une table peut avoir plusieurs index sur une seule colonne, mais ce n'est pas un index composé. Un index composé, c'est-à-dire qu'un index contient plusieurs colonnes.

Lors de la création d'un index, vous devez vous assurer que cet index est appliqué à la condition de la requête SQL (généralement en tant que condition de la clause WHERE).

En réalité, un index est également une table, qui conserve la clé principale et le champ d'index, et pointe vers les enregistrements de la table réelle.

On parle ici des avantages de l'utilisation des index, mais une utilisation excessive des index peut entraîner un abus. Par conséquent, les index ont également leurs inconvénients : bien que les index augmentent considérablement la vitesse de recherche, ils ralentissent également la vitesse de mise à jour de la table, comme les opérations INSERT, UPDATE et DELETE sur la table. Parce que lors de la mise à jour de la table, MySQL doit non seulement sauvegarder les données, mais aussi sauvegarder le fichier d'index.

La création d'un index occupe de l'espace disque pour le fichier d'index.

Index normal

Créer un index

C'est l'index de base, qui n'a aucune restriction. Il existe plusieurs méthodes de création :

CREATE INDEX indexName ON table_name (column_name)

Pour les types CHAR et VARCHAR, la longueur peut être inférieure à la longueur réelle du champ ; pour les types BLOB et TEXT, la longueur doit être spécifiée.

Modifier la structure de la table (ajouter un index)

ALTER table tableName ADD INDEX indexName(columnName)

Spécifier directement lors de la création de la table

CREATE TABLE mytable(　　
　
ID INT NOT NULL,　　　
　
username VARCHAR(16) NOT NULL,　　
　
INDEX [indexName] (username(length))　　
　
);

Syntaxe de suppression d'un index

DROP INDEX [indexName] ON mytable;

Index unique

C'est similaire à l'index normal mentionné précédemment, mais avec une différence : les valeurs de la colonne de l'index doivent être uniques, mais peuvent être vides. Si c'est un index composite, la combinaison des valeurs des colonnes doit être unique. Il existe plusieurs méthodes pour le créer :

Créer un index

CREATE UNIQUE INDEX indexName ON mytable(username(length))

Modifier la structure de la table

ALTER table mytable ADD UNIQUE [indexName] (username(length))

Spécifier directement lors de la création de la table

CREATE TABLE mytable(　　
　
ID INT NOT NULL,　　　
　
username VARCHAR(16) NOT NULL,　　
　
UNIQUE [indexName] (username(length))　　
　
);

Ajouter et supprimer des index en utilisant la commande ALTER

Il y a quatre méthodes pour ajouter des index à une table de données :

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): Cette instruction ajoute une clé principale, ce qui signifie que la valeur de l'index doit être unique et ne peut pas être NULL.

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): Cette instruction crée une valeur d'index unique (sauf pour NULL, qui peut apparaître plusieurs fois).

• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Ajouter un index normal, où la valeur de l'index peut apparaître plusieurs fois.

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):Cette instruction spécifie que l'index est FULLTEXT, utilisé pour l'indexation de texte complet.

Voici un exemple pour ajouter un index dans une table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Vous pouvez également utiliser la clause DROP dans la commande ALTER pour supprimer des index. Essayez l'exemple suivant pour supprimer un index :

mysql>　ALTER　TABLE　testalter_tbl　DROP　INDEX　c;

Ajout et suppression de la clé primaire à l'aide de la commande ALTER

La clé primaire agit sur les colonnes (peut être une colonne ou une combinaison de plusieurs colonnes en tant que clé primaire). Lorsque vous ajoutez un index de clé primaire, vous devez vous assurer que la clé primaire par défaut n'est pas vide (NOT NULL). Voici un exemple :

mysql>　ALTER　TABLE　testalter_tbl　MODIFY　i　INT　NOT　NULL;
mysql>　ALTER　TABLE　testalter_tbl　ADD　PRIMARY　KEY　(i);

Vous pouvez également utiliser la commande ALTER pour supprimer la clé primaire :

mysql>　ALTER　TABLE　testalter_tbl　DROP　PRIMARY　KEY;

Lors de la suppression de la clé primaire, il suffit de spécifier PRIMARY KEY, mais lors de la suppression de l'index, vous devez connaître le nom de l'index.

Affichage des informations d'index

Vous pouvez utiliser la commande SHOW INDEX pour lister les informations d'index liées à la table. Vous pouvez ajouter \G pour formater la sortie des informations.

Essayer les exemples suivants:

mysql>　SHOW　INDEX　FROM　table_name;　\G
........