Index MySQL

Un index MySQL est une structure de données utilisée pour accélérer la vitesse et les performances des requêtes de base de données.

La création d'index MySQL est importante pour le fonctionnement efficace de MySQL ; les index peuvent grandement améliorer la vitesse de récupération de MySQL.

Un index MySQL est similaire à l'index d'un livre. En stockant des pointeurs vers les lignes de données, il peut rapidement localiser et accéder à des données spécifiques dans une table.

Par analogie, si une base de données MySQL avec des index bien conçus et correctement utilisés est une Lamborghini, alors une base de données MySQL sans index conçus et utilisés est un tricycle à propulsion humaine.

Prenons l'exemple de la page d'index d'un dictionnaire chinois : nous pouvons rapidement trouver les caractères nécessaires en utilisant la table des matières (index) triée par pinyin, nombre de traits, radicaux, etc.

Les index sont divisés en index à colonne unique et index composites :

  • Un index à colonne unique signifie qu'un index ne contient qu'une seule colonne ; une table peut avoir plusieurs index à colonne unique.
  • Un index composite signifie qu'un index contient plusieurs colonnes.

Lors de la création d'un index, vous devez vous assurer que l'index est appliqué aux conditions dans les instructions de requête SQL (généralement comme conditions dans la clause WHERE).

En fait, un index est aussi une table qui stocke la clé primaire et les champs d'index, et pointe vers les enregistrements de la table d'entité.

Bien que les index puissent améliorer les performances de requête, vous devez également noter les points suivants :

  • Les index nécessitent un espace de stockage supplémentaire.
  • Lors de l'exécution d'opérations d'insertion, de mise à jour et de suppression sur une table, les index doivent être maintenus, ce qui peut affecter les performances.
  • Trop d'index ou des index déraisonnables peuvent entraîner une dégradation des performances, il faut donc choisir et planifier les index avec soin.

Index normal

Les index peuvent améliorer considérablement la vitesse des requêtes, en particulier lors de la recherche dans de grandes tables. En utilisant les index, MySQL peut localiser directement les lignes de données qui répondent aux conditions de requête sans parcourir toute la table ligne par ligne.

Créer un index

Utilisez l'instructionCREATE INDEXpour créer un index normal.

Un index normal est le type d'index le plus courant, utilisé pour accélérer les requêtes sur les données d'une table.

CREATE INDEXLa syntaxe est la suivante :

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE INDEX: Le mot-clé utilisé pour créer un index normal.
  • index_name: Spécifie le nom de l'index à créer. Le nom de l'index doit être unique dans la table.
  • table_name: Spécifie sur quelle table créer l'index.
  • (column1, column2, ...): Spécifie les noms de colonnes de table à indexer. Vous pouvez spécifier une ou plusieurs colonnes comme index composite. Les types de données de ces colonnes sont généralement numériques, texte ou date.
  • ASCetDESC(Facultatif) : Utilisé pour spécifier l'ordre de tri de l'index. Par défaut, l'index est trié par ordre croissant (ASC).

L'exemple suivant suppose que nous avons une table nommée students, contenant les colonnes id, name et age. Nous allons créer un index normal sur la colonne name.

CREATE INDEX idx_name ON students (name);

L'instruction ci-dessus créera un index normal nommé idx_name sur la colonne name de la table students, ce qui aidera à améliorer les performances des requêtes pour la recherche par name.

Notez que si la quantité de données dans la table est importante, la création d'un index peut prendre du temps, mais une fois créé, les performances des requêtes seront considérablement améliorées.

Modifier la structure de la table (ajouter un index)

Nous pouvons utiliser la commandeALTER TABLEpour créer un index dans une table existante.

ALTER TABLE permet de modifier la structure d'une table, y compris l'ajout, la modification ou la suppression d'index.

La syntaxe pour créer un index avec ALTER TABLE :

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • ALTER TABLE: Le mot-clé utilisé pour modifier la structure de la table.
  • table_name: Spécifie le nom de la table à modifier.
  • ADD INDEX: La clause pour ajouter un index.ADD INDEXUtilisé pour créer un index normal.
  • index_name: Spécifie le nom de l'index à créer. Le nom de l'index doit être unique dans la table.
  • (column1, column2, ...): Spécifie les noms de colonnes de table à indexer. Vous pouvez spécifier une ou plusieurs colonnes comme index composite. Les types de données de ces colonnes sont généralement numériques, texte ou date.
  • ASCetDESC(Facultatif) : Utilisé pour spécifier l'ordre de tri de l'index. Par défaut, l'index est trié par ordre croissant (ASC).

Voici un exemple. Nous allons créer un index normal sur une table existante nommée employees :

ALTER TABLE employees
ADD INDEX idx_age (age);

L'instruction ci-dessus créera un index normal nommé idx_age sur la colonne age de la table employees.

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

Lors de la création d'une table, vous pouvezCREATE TABLEspécifier directement les index dans l'instruction pour créer une combinaison de table et d'index.

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
  • CREATE TABLE: Le mot-clé utilisé pour créer une nouvelle table.
  • table_name: Spécifie le nom de la table à créer.
  • (column1 data_type, column2 data_type, ...): Définit les noms de colonnes et les types de données de la table. Vous pouvez spécifier une ou plusieurs colonnes comme index composite. Les types de données de ces colonnes sont généralement numériques, texte ou date.
  • INDEX: Le mot-clé utilisé pour créer un index normal.
  • index_name: Spécifie le nom de l'index à créer. Le nom de l'index doit être unique dans la table.
  • (column1, column2, ...): Spécifie les noms de colonnes de table à indexer. Vous pouvez spécifier une ou plusieurs colonnes comme index composite. Les types de données de ces colonnes sont généralement numériques, texte ou date.
  • ASCetDESC(Facultatif) : Utilisé pour spécifier l'ordre de tri de l'index. Par défaut, l'index est trié par ordre croissant (ASC).

Ci-dessous un exemple. Nous allons créer une table nommée students et créer un index normal sur la colonne age.

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_age (age)
);

Dans l'exemple ci-dessus, nous avons créé un index normal nommé idx_age sur la colonne age de la table students.

Syntaxe pour supprimer un index

Nous pouvons utiliser l'instructionDROP INDEXpour supprimer un index.

La syntaxe de DROP INDEX :

DROP INDEX index_name ON table_name;
  • DROP INDEX: Le mot-clé utilisé pour supprimer un index.
  • index_name: Spécifie le nom de l'index à supprimer.
  • ON table_name: Spécifie sur quelle table supprimer l'index.

UtilisezALTER TABLELa syntaxe pour supprimer un index avec l'instruction est la suivante :

ALTER TABLE table_name
DROP INDEX index_name;
  • ALTER TABLE: Le mot-clé utilisé pour modifier la structure de la table.
  • table_name: Spécifie le nom de la table à modifier.
  • DROP INDEX: La clause utilisée pour supprimer un index.
  • index_name: Spécifie le nom de l'index à supprimer.

L'exemple suivant suppose que nous avons une table nommée employees avec un index nommé idx_age sur la colonne age. Maintenant, nous voulons supprimer cet index :

DROP INDEX idx_age ON employees;

Ou utilisez l'instructionALTER TABLE:

ALTER TABLE employees
DROP INDEX idx_age;

Les deux commandes supprimeront l'index nommé idx_age de la table employees.

Si l'index n'existe pas, une erreur se produira lors de l'exécution de la commande. Par conséquent, avant de supprimer un index, il est préférable de vérifier si l'index existe, ou d'utiliser un mécanisme de gestion des erreurs pour gérer les situations d'erreur possibles.

Index unique

Dans MySQL, vous pouvez utiliser l'instructionCREATE UNIQUE INDEXpour créer un index unique.

Un index unique garantit que les valeurs de l'index sont uniques et que les valeurs en double ne sont pas autorisées.

Créer un index

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE UNIQUE INDEX: La combinaison de mots-clés utilisée pour créer un index unique.
  • index_name: Spécifie le nom de l'index unique à créer. Le nom de l'index doit être unique dans la table.
  • table_name: Spécifie sur quelle table créer l'index unique.
  • (column1, column2, ...): Spécifie les noms de colonnes de table à indexer. Vous pouvez spécifier une ou plusieurs colonnes comme index composite. Les types de données de ces colonnes sont généralement numériques, texte ou date.
  • ASCetDESC(Facultatif) : Utilisé pour spécifier l'ordre de tri de l'index. Par défaut, l'index est trié par ordre croissant (ASC).

Voici un exemple de création d'un index unique : supposons que nous ayons une table nommée employees avec les colonnes id et email. Maintenant, nous voulons créer un index unique sur la colonne email pour garantir que l'adresse e-mail de chaque employé est unique.

CREATE UNIQUE INDEX idx_email ON employees (email);

L'exemple ci-dessus créera un index unique nommé idx_email sur la colonne email de la table employees.

Modifier la structure de la table pour ajouter un index

Nous pouvons utiliser la commandeALTER TABLEpour créer un index unique.

ALTER TABLELa commande permet de modifier la structure d'une table existante, y compris l'ajout de nouveaux index.

ALTER table table_name 
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);
  • ALTER TABLE: Le mot-clé utilisé pour modifier la structure de la table.
  • table_name: Spécifie le nom de la table à modifier.
  • ADD CONSTRAINT: Il s'agit du mot-clé utilisé pour ajouter des contraintes (y compris les index uniques).
  • unique_constraint_name : Spécifie le nom de l'index unique à créer. Le nom de la contrainte doit être unique dans la table.
  • UNIQUE (column1, column2, ...): Spécifie les noms de colonnes de table à indexer. Vous pouvez spécifier une ou plusieurs colonnes comme index composite. Les types de données de ces colonnes sont généralement numériques, texte ou date.

Voici un exemple d'utilisation deALTER TABLEpour créer un index unique avec une commande : supposons que nous ayons une table nommée employees contenant les colonnes id et email, et maintenant nous voulons créer un index unique sur la colonne email pour garantir que l'adresse e-mail de chaque employé est unique.

ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);

L'exemple ci-dessus créera un index unique nommé idx_email sur la colonne email de la table employees.

Notez que s'il existe déjà des valeurs email en double dans la table, l'ajout d'un index unique échouera. Avant de créer un index unique, vous devrez peut-être vous assurer que la colonne email de la table ne contient aucune valeur en double.

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

Nous pouvons également créer la table et, en même temps, vous pouvez utiliser le mot-cléCREATE TABLEdans l'instructionUNIQUEpour créer un index unique.

Cela définit la contrainte d'index unique en même temps que la table est créée.

CREATE TABLESyntaxe de création d'un index unique dans l'instruction :

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
  • CREATE TABLE: Mot-clé utilisé pour créer une nouvelle table.
  • table_name: Spécifie le nom de la table à créer.
  • (column1 data_type, column2 data_type, ...): Définit les noms de colonnes et les types de données de la table. Vous pouvez spécifier une ou plusieurs colonnes comme combinaison d'index. Les types de données de ces colonnes sont généralement numériques, texte ou date.
  • CONSTRAINT: Mot-clé utilisé pour ajouter une contrainte.
  • index_name: Spécifie le nom de l'index unique à créer. Le nom de la contrainte doit être unique dans la table.
  • UNIQUE (column1, column2, ...): Spécifie le nom de la colonne de table à indexer.
  • ASCetDESC(Facultatif) : Utilisé pour spécifier l'ordre de tri de l'index. Par défaut, l'index est trié par ordre croissant (ASC).

Voici un exemple de création d'un index unique lors de la création d'une table : Supposons que nous voulions créer une table nommée employees contenant les colonnes id, name et email, et que nous voulions que les valeurs de la colonne email soient uniques, nous définissons donc un index unique lors de la création de la table.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100) UNIQUE
);

Dans cet exemple, la colonne email est définie comme un index unique car le mot-clé UNIQUE est ajouté après elle.

Notez qu'après avoir utilisé le mot-cléUNIQUE, le nom de l'index est automatiquement généré, et vous pouvez également spécifier un nom d'index si nécessaire.

Utiliser la commande ALTER pour ajouter et supprimer des index

Il existe quatre façons d'ajouter des index à une table de données :

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):Cette instruction ajoute une clé primaire. Les valeurs de la colonne de clé primaire doivent être uniques. La liste des colonnes de clé primaire peut contenir une ou plusieurs colonnes, et ne peut pas contenir de valeurs NULL.
  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):Les valeurs de l'index créé par cette instruction doivent être uniques (sauf NULL, qui peut apparaître plusieurs fois).
  • ALTER TABLE tbl_name ADD INDEX index_name (column_list):Ajoute un index normal ; les valeurs d'index peuvent apparaître plusieurs fois.
  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):Cette instruction spécifie l'index comme FULLTEXT, utilisé pour l'indexation en texte intégral.

L'exemple suivant ajoute un index à une table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

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

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Utiliser la commande ALTER pour ajouter et supprimer des clés primaires

Une clé primaire s'applique à une colonne (il peut s'agir d'une seule colonne ou de plusieurs colonnes combinées comme clé primaire). Lors de l'ajout d'une clé primaire, vous devez vous assurer que la clé primaire n'est pas nulle par défaut (NOT NULL). 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 une clé primaire :

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

Afficher les informations sur les index

Vous pouvez utiliser la commandeSHOW INDEXpour lister les informations pertinentes sur les index dans une table.

Vous pouvez ajouter\Gpour formater les informations de sortie.

SHOW INDEXInstruction :

mysql> SHOW INDEX FROM table_name\G
........
  • SHOW INDEX: Mot-clé utilisé pour afficher les informations sur les index.
  • FROM table_name: Spécifie le nom de la table dont vous souhaitez consulter les informations d'index.
  • \G: Formate les informations de sortie.

Après avoir exécuté la commande ci-dessus, des informations détaillées sur tous les index de la table spécifiée seront affichées, y compris le nom de l'index (Key_name), la colonne d'index (Column_name), s'il s'agit d'un index unique (Non_unique), la méthode de tri (Collation), la cardinalité de l'index (Cardinality), etc.

Autres extensions