Familiarité avec les index MySQL-tutoriel mysql-php.cn

Familiarité avec les index MySQL

1. Introduction à l'index
(1) La signification et la spécificité de l'index
(2) Classification de l'index
(3) Principes de conception de l'index

2. Créer un index
(1) Créer un index lors de la création d'une table
(2) Créer un index sur une table existante
(3) Supprimer un index

(Recommandation d'apprentissage gratuite :Tutoriel vidéo mysql)

1. Introduction aux index

Les indices sont utilisés pour trouver rapidement des lignes avec une valeur spécifique dans une colonne. Sans utiliser d'index, MySQL doit lire la table entière en commençant par le premier enregistrement jusqu'à trouver les lignes pertinentes. Plus la table est grande, plus l'interrogation des données prend du temps. Si la colonne interrogée dans la table possède un index, MySQL peut accéder rapidement à un emplacement pour rechercher le fichier de données sans avoir à examiner toutes les données.

(1) La signification et la spécificité de l'index

Signification : Un index est une structure de base de données distincte stockée sur le disque, qui contient des pointeurs de référence vers tous les enregistrements de la table de données. Utilisé pour rechercher rapidement des lignes avec une valeur spécifique dans une ou plusieurs colonnes.

Les index sont implémentés dans les moteurs de stockage, donc les index de chaque moteur de stockage ne sont pas nécessairement exactement les mêmes, et chaque moteur de stockage ne prend pas nécessairement en charge tous les types d'index. Définissez le nombre maximum d'index et la longueur maximale de l'index pour chaque table en fonction du moteur de stockage. Tous les moteurs de stockage prennent en charge au moins 16 index par table, avec une longueur totale d'index d'au moins 256 octets. La plupart des moteurs de stockage ont des limites plus élevées.

Il existe deux types d'index de stockage dans MySQL : BTREE et HASH, qui sont spécifiquement liés au moteur de stockage de la table ; les moteurs de stockage MyISAM et InnoDB ne prennent en charge que les index BTREE ; les moteurs peuvent prendre en charge les index HASH et BTREE.

Avantages de l'index :
1. En créant un index unique, l'unicité de chaque ligne de données dans la table de la base de données peut être garantie.
2. Peut accélérer considérablement la requête de données. (La principale raison de la création d'un index)
3. En termes d'intégrité référentielle des données, cela peut accélérer la connexion entre les tables et les tables.
4. Lors de l'utilisation de clauses de regroupement et de tri pour la requête de données, le temps de regroupement et de tri dans la requête peut également être considérablement réduit.

Inconvénients de l'ajout d'index :
1. La création et la maintenance d'index prennent du temps, et à mesure que la quantité de données augmente, le temps passé augmentera également.
2. Les index occupent de l'espace disque. En plus de l'espace de données occupé par la table de données, chaque index occupe également une certaine quantité d'espace physique. S'il y a un grand nombre d'index, le fichier d'index peut atteindre le fichier maximum. taille plus rapide que le fichier de données.
3. Lors de l'ajout, de la suppression et de la modification de données dans le tableau, l'index doit également être maintenu dynamiquement, ce qui réduit la vitesse de maintenance des données.

(2) Classification des index

1. Index ordinaire et index unique (unique)

L'index ordinaire est le type d'index de base dans MySQL, permettant la définition. Insérez les valeurs en double et les valeurs nulles dans les colonnes indexées.
Indice unique, la valeur de la colonne d'index doit être unique, mais les valeurs nulles sont autorisées. Dans le cas d'un index composite, la combinaison des valeurs des colonnes doit être unique.
L'index de clé primaire est un index unique spécial qui n'autorise pas les valeurs nulles.

2. Index à colonne unique et index combiné

Colonne unique, vous avez donc un index qui ne contient qu'une seule colonne, et une table peut avoir plusieurs index à une seule colonne. .
L'index combiné fait référence à un index créé sur une combinaison de plusieurs champs de la table. L'index sera utilisé uniquement lorsque les champs de gauche de ces champs sont utilisés dans les conditions de requête.
Suivez le préfixe le plus à gauche lorsque vous utilisez des index combinés.

3. Index de texte intégral (fulltext)

Le type d'index de texte intégral est FULLTEXT, qui prend en charge la recherche en texte intégral des valeurs sur les colonnes où l'index est défini, permettant l'insertion dans ces colonnes d'index. Des valeurs en double et des valeurs nulles, des index de texte intégral peuvent être créés sur des colonnes de type char, varchar ou text. Seul le moteur de stockage MyISAM de MySQL prend en charge l'indexation de texte intégral.

4. Index spatial (spatial)

Un index spatial est un index établi pour les champs de types de données spatiales. Il existe 4 types de données spatiales dans MySQL, à savoir la géométrie. , point, ligne et polygone. MySQL a été étendu avec le mot-clé spatial, permettant de créer des index spatiaux avec une syntaxe similaire à celle utilisée pour créer des index réguliers. Les colonnes utilisées pour créer des index spatiaux doivent être déclarées non nulles. Les index spatiaux ne peuvent être créés que dans des tables dont le moteur de stockage est MySQL.

(3) Principes de conception d'index

Une conception d'index déraisonnable ou l'absence d'index entraînera des obstacles aux performances de la base de données et des applications. Des index efficaces sont très importants pour obtenir de bonnes performances. Lors de la conception d'index, les directives suivantes doivent être prises en compte :

1.
2. Évitez l'indexation excessive sur les tables fréquemment mises à jour et indexez le moins de colonnes possible.
3. Il est préférable de ne pas utiliser d'index pour les tables avec de petits volumes de données.
4. Créez des index sur des colonnes avec de nombreuses valeurs différentes qui sont souvent utilisées dans les expressions conditionnelles. Ne créez pas d'index sur des colonnes avec peu de valeurs différentes.
5. Lorsque l'unicité est une caractéristique des données elles-mêmes, spécifiez un index unique.
6. Créez des index sur les colonnes qui sont fréquemment triées ou regroupées (opérations de regroupement ou d'ordre par). S'il y a plusieurs colonnes à trier, vous pouvez créer un index combiné sur ces colonnes.

2. Créer un index

Format de syntaxe :

create table table_name [col_name date_type][unique|fulltext|spatial] [index|key] [index_name] (col_name [length]) [asc | desc]

Copier après la connexion

unique, fulltext et spatial sont des paramètres facultatifs, représentant respectivement l'index unique, l'index de texte intégral et l'index spatial.
Index et clé sont des synonymes. Ils ont la même fonction et servent à spécifier la création d'un index.
col_name est la colonne de champ qui doit être indexée. Cette colonne doit être sélectionnée parmi plusieurs colonnes définies dans la table de données.
index_name spécifie le nom de l'index, qui est un paramètre facultatif. S'il n'est pas spécifié, MySQL utilise par défaut col_name comme valeur d'index.
length est un paramètre facultatif, indiquant la longueur de l'index. Seuls les champs de type chaîne peuvent spécifier la longueur de l'index.
asc ou desc spécifie le stockage de la valeur d'index par ordre croissant ou décroissant.

(1) Créer un index lors de la création d'une table

①Créer un index normal

Un index normal est le type d'index le plus basique et n'a aucune restriction telle que unicité, sa fonction est uniquement d'accélérer l'accès aux données.

[Exemple 1] Créez un index commun sur le champ year_publication dans la table book. L'instruction SQL est la suivante :

mysql> create table book    -> (
    -> bookid int not null,
    -> bookname varchar(255) not null,
    -> authors varchar(255) not null,
    -> info varchar(255) null,
    -> comment varchar(255) null,
    -> year_publication year not null,
    -> index(year_publication)
    -> );Query OK, 0 rows affected (0.21 sec)mysql> show create table book \G*************************** 1. row ***************************
       Table: bookCreate Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from book where year_publication=1990 \G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication          key: year_publication
      key_len: 1
          ref: const         rows: 1
     filtered: 100.00
        Extra: NULL1 row in set, 1 warning (0.00 sec)

Copier après la connexion

L'explication de chaque ligne du résultat de sortie de l'instruction explan. est la suivante :

table spécifie le nom de la table de données lue par la base de données, et elles sont classées dans l'ordre dans lequel elles sont lues. La ligne
type spécifie la relation entre cette table de base de données et d'autres tables de base de données. Les valeurs possibles incluent system, const, eq_ref, ref, range, index et all.
Ligne possible_keys. Donne les différents index que MySQL peut utiliser lors de la recherche d'enregistrements de données.
key row est l'index réellement sélectionné par MySQL.
La ligne key_len donne la longueur de l'index en octets. Plus la valeur key_len est petite, plus il est rapide. La ligne
ref donne le nom de la colonne de données dans une autre table de données de la relation.
rows rows est le nombre de lignes de données que MySQL s'attend à lire dans cette table de données lors de l'exécution de cette requête. La
ligne supplémentaire fournit des informations relatives à l'opération associée.

Comme vous pouvez le voir, les valeurs de possible_key et key sont year_publication, et l'index est utilisé dans la requête.

②Créer un index unique

La principale raison de la création d'un index unique est de réduire le temps d'exécution des opérations d'interrogation des colonnes d'index, en particulier pour les tables de données relativement volumineuses. Il est similaire à l'index ordinaire précédent, sauf que la valeur de la colonne d'index doit être unique, mais les valeurs nulles sont autorisées. Dans le cas d'un index composite, la combinaison des valeurs des colonnes doit être unique.

[Exemple 2] Créez une table t1 et utilisez le mot-clé unique pour créer un index unique sur le champ id de la table.

mysql> create table t1    -> (
    -> id int not null
    -> ,name char(30) not null,
    -> unique index uniqidx(id)
    -> );Query OK, 0 rows affected (0.27 sec)mysql> show create table t1 \G*************************** 1. row ***************************
       Table: t1Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `uniqidx` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)

Copier après la connexion

③Créer un index à une seule colonne

Un index à une seule colonne est un index créé sur un certain champ dans la table de données. Plusieurs index à une seule colonne peuvent être créés dans une table.

[Exemple 3] Créez une table t2 et créez un index à une seule colonne sur le champ de nom de la table.

mysql> create table t2    -> (
    -> id int not null,
    -> name char(50) null,
    -> index singleidx(name(20))
    -> );Query OK, 0 rows affected (0.06 sec)mysql> show create table t2 \G*************************** 1. row ***************************
       Table: t2Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `singleidx` (`name`(20))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)

Copier après la connexion

Comme vous pouvez le voir d'après les résultats, un index à une seule colonne nommé SingleIdx a été établi avec succès sur le champ id, avec une longueur d'index de 20.

④Créer un index combiné

Un index combiné consiste à créer un index sur plusieurs champs.

[Exemple 4] Créez la table t3 et créez un index combiné sur les champs id, name et age dans la table L'instruction SQL est la suivante :

mysql> create table t3    -> (
    -> id int not null,
    -> name char(30) not null,
    -> age int not null,
    -> info varchar(255),
    -> index mulitiidx(id,name,age)
    -> );Query OK, 0 rows affected (0.07 sec)mysql> show create table t3 \G*************************** 1. row ***************************
       Table: t3Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `mulitiidx` (`id`,`name`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from t3 where id = 1 and name = 'joe' \G*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: mulitiidx          key: mulitiidx
      key_len: 124
          ref: const,const         rows: 1
     filtered: 100.00
        Extra: Using index condition1 row in set, 1 warning (0.06 sec)

Copier après la connexion

L'index combiné joue le rôle. de plusieurs index Cependant, lors de son utilisation, il n'est pas possible d'utiliser l'index pour interroger n'importe quel champ, mais de suivre le « préfixe le plus à gauche » : en utilisant l'ensemble de colonnes le plus à gauche de l'index pour faire correspondre les lignes, un tel ensemble de colonnes est appelé. le préfixe le plus à gauche.

Par exemple, voici un index composé de trois champs : id, name et age. Les lignes de l'index sont triées dans l'ordre id/nam/age. L'index peut rechercher des combinaisons de champs : (id. , nom, âge), ( id, nom) ou id. Si la colonne ne constitue pas le préfixe le plus à gauche de l'index, MySQL ne peut pas utiliser l'index local. Par exemple, la combinaison (âge) ou (nom, âge) ne peut pas utiliser la requête d'index. Lors de l'interrogation des champs id et name, l'index multiidx est utilisé. Si la combinaison de (nom, âge) est interrogée ou si les champs nom et age sont interrogés séparément, l'index est nul.

⑤Créer un index de texte intégral

L'index de texte intégral peut être utilisé pour la recherche en texte intégral. Seul le moteur de stockage MyISAM prend en charge les index de texte intégral et crée uniquement des index pour les colonnes char, varchar et text. L'indexation est toujours effectuée sur la colonne entière, les index locaux (préfixe) ne sont pas pris en charge.

[Exemple 5] Créez la table t4 et établissez un index de texte intégral sur le champ d'information de la table. L'instruction SQL est la suivante :

mysql> create table t4    -> (
    -> id int not null,
    -> name char(30) not null,
    -> age int not null,
    -> info varchar(255),
    -> fulltext index fulltxtidx(info)
    -> )engine=MyISAM;Query OK, 0 rows affected (0.08 sec)mysql> show create table t4 \G*************************** 1. row ***************************
       Table: t4Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `fulltxtidx` (`info`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)

Copier après la connexion

L'index de texte intégral est très approprié pour. les grands ensembles de données, et pour les petits ensembles de données, son utilité est relativement faible.

6. Créer un index spatial

L'index spatial doit être créé dans une table de type MyISAM, et le champ de type spatial doit être non nul.

[Exemple 6] Créez la table t5 et créez un index spatial sur le champ avec une géométrie de type spatial :

mysql> create table t5    -> ( g geometry not null,spatial index spatidx(g) ) ENGINE=MyISAM;Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> show create table t5 \G*************************** 1. row ***************************
       Table: t5Create Table: CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`g`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.05 sec)

Copier après la connexion

Comme vous pouvez le voir, le nom est créé sur. le champ g du tableau t5 est l'indice spatial de spatIdx. Spécifiez la contrainte non nulle de la valeur du champ de type spatial lors de la création, et le moteur de stockage de la table est MyISAM.

(2) Créer un index sur une table existante

Pour créer un index sur une table existante, vous pouvez utiliser l'instruction alter table ou l'instruction create index.

1. Utilisez l'instruction alter table pour créer un index

Syntaxe de base :

alter table table_name add [unique|fulltext|spatial] [index|key][index_name] (col_name[length],...) [asc |dec]

Copier après la connexion

[Exemple 7] Créez un nom dans le champ bookname dans la table du livre Est l'index ordinaire de BkNameIdx.

Avant d'ajouter un index, utilisez l'instruction show index pour afficher l'index créé dans la table spécifiée :

mysql> show index from book \G*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL1 row in set (0.10 sec)

Copier après la connexion

其中，各个主要参数的含义为;

table表示创建索引的表。
Non_unique表示索引非唯一，1表示非唯一，0表示唯一。
Key_name表示索引的名称。
Seq_in_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序。
Column_name表示定义索引的列字段。
Sub_part表示索引的长度。
Null表示该字段是否能为空值。
Index_type表示索引类型。

可以看到book表中已经存在一个索引，即year_publication索引，该索引为非唯一索引，下面使用alter table 在bookname字段上添加索引，SQL语句如下：

mysql> alter table book add index bknameidx( bookname(30) );Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from book \G*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bknameidx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL2 rows in set (0.05 sec)

Copier après la connexion

可以看到表中有了两个索引，另一个为通过alter table语句添加的名称为bknameidx的索引，该索引为非唯一索引，长度为30。

【例8】在book表的bookid字段上建立名称为uniqididx的唯一索引，SQL语句如下：

mysql> alter table book add unique index uniqididx(bookid);Query OK, 0 rows affected (0.17 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from book \G1...2...*************************** 3. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bknameidx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL3 rows in set (0.01 sec)

Copier après la connexion

可以看到，Non_unique的属性值为0，表示名称为Uniqididx的索引为唯一索引，创建唯一索引成功。

【例9】在book表的comment字段上建立单列索引，SQL语句如下：

mysql> alter table book add index bkcmtidx ( comment(50) );Query OK, 0 rows affected (0.11 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from book \G1...2...3...*************************** 4. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkcmtidx
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL4 rows in set (0.01 sec)

Copier après la connexion

可以看到，语句执行之后再book表的comment字段上建立了名称为bkcmtidx的索引，长度为50，在查询时，只需要检索前50个字符。

【例10】在book表的authors和info字段上建立组合索引，SQL语句如下：

mysql> alter table book add index bkauandinfoidx (authors(30),info(50) );Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from book \G1...2...3...4...*************************** 5. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkauandinfoidx
 Seq_in_index: 1
  Column_name: authors
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL*************************** 6. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkauandinfoidx
 Seq_in_index: 2
  Column_name: info
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL6 rows in set (0.06 sec)

Copier après la connexion

可以看到名称为bkauandinfoidx的索引由两个字段组成，authors字段长度为30，在组合索引中的序号为1，该字段不允许空值null；info字段长度为50，在组合索引中的序号为2，该字段可以为空值null。

【例11】创建表t6，在t6表上使用alter table创建全文索引，SQL语句如下：

mysql> create table t6    -> (
    -> id int not null,
    -> info char(255)
    -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t6 add fulltext index infofiidx( info );Query OK, 0 rows affected (0.13 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from t6 \G*************************** 1. row ***************************
        Table: t6
   Non_unique: 1
     Key_name: infofiidx
 Seq_in_index: 1
  Column_name: info
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT      Comment:
Index_comment:
      Visible: YES
   Expression: NULL1 row in set (0.05 sec)

Copier après la connexion

可以看到，t6表中已经创建了名称为infoftidx的索引，该索引在info字段上创建，类型为fulltext，允许空值。

【例12】创建表t7，t7的空间类型字段g上创建名称为spatidx的空间索引，SQL语句如下：

mysql> create table t7(g geometry not null)ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t7 add spatial index spatidx(g);Query OK, 0 rows affected, 1 warning (0.06 sec)Records: 0  Duplicates: 0  Warnings: 1mysql> show index from t7 \G*************************** 1. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: spatidx
 Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL      Comment:
Index_comment:
      Visible: YES
   Expression: NULL1 row in set (0.01 sec)

Copier après la connexion

可以看到，t7表的g字段上创建了名为spatidx的空间索引。

2.使用create index 创建索引

create index 语句可以在已经存在的表上添加索引，MySQL中create index被映射到一个alter table语句上，基本语法为：

create [unique|fulltext|spatial] index index_nameon table_name (col_name[length],...) [asc|desc]

Copier après la connexion

可以看到create index语句和alter index语句的语法基本一样，只是关键字不同，使用相同的表book，假设该表中没有任何索引值，创建book表语句如下：

create table book(bookid int not null,bookname varchar(255) not null,authors varchar(255) not null,info varchar(255) null,comment varchar(255) null,year_publication year not null);

Copier après la connexion

【例13】在book表的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：

mysql> create index BkNameOdx on book(bookname);Query OK, 0 rows affected (0.10 sec)Records: 0  Duplicates: 0  Warnings: 0

Copier après la connexion

【例14】在book表的bookid字段上建立名为UniqidIdx的唯一索引，SQL语句如下：

mysql> create unique index UniqiiIdx on book(bookid);Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0

Copier après la connexion

【例15】在book表的comment字段上建立单列索引，SQL语句如下：

mysql> create index BkcmtIdx on book(bookid);Query OK, 0 rows affected (0.08 sec)Records: 0  Duplicates: 0  Warnings: 0

Copier après la connexion

【例16】在book表的authors和info字段上建立组合索引，SQL语句如下：

mysql> create index BkAuAndInfoIdx on book (authors(20),info(50));Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0

Copier après la connexion

【例17】删除表t6，重新建立表t6，在t6表中使用create index 语句，在char类型的info字段上创建全文索引。

mysql> drop table t6;Query OK, 0 rows affected (0.02 sec)mysql> create table t6    -> (
    -> id int not null,
    -> info char(255)
    -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create fulltext index infoftidx on t6(info);Query OK, 0 rows affected (0.07 sec)Records: 0  Duplicates: 0  Warnings: 0

Copier après la connexion

【例18】删除表t7，重新创建表t7，在t7表中使用create index语句，在空间数据类型字段g上创建名称为spatIdx的空间索引。

mysql> drop table t7;Query OK, 0 rows affected (0.06 sec)mysql> create table t7 (g geometry not null )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create spatial index spatIdx on t7 (g);Query OK, 0 rows affected, 1 warning (0.07 sec)Records: 0  Duplicates: 0  Warnings: 1

Copier après la connexion

(3)删除索引

MySQL中删除索引使用alter table或者drop index 语句，两者可实现相同的功能，drop index 语句在内部被映射到一个alter table语句中。

1.使用alter table删除索引

alter table 删除索引的基本语法格式:

alter table table_name drop index index_name

Copier après la connexion

【例1】删除book表中的名称为UniqidIdx的唯一索引。

mysql> show create table book \G*************************** 1. row ***************************
       Table: bookCreate Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  UNIQUE KEY `UniqiIdx` (`bookid`),
  KEY `BkNameOdx` (`bookname`),
  KEY `BkcmtIdx` (`bookid`),
  KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)mysql> alter table book drop index UniqiIdx;Query OK, 0 rows affected (0.19 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table book \G*************************** 1. row ***************************
       Table: bookCreate Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `BkNameOdx` (`bookname`),
  KEY `BkcmtIdx` (`bookid`),
  KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

Copier après la connexion

可以看到，book表中已经没有名称为UniqidIdx的唯一索引，删除索引成功。

注意：添加auto_increment约束字段的唯一索引不能被删除。

2.使用drop index 语句删除索引

drop index语句删除索引的基本语法格式：

drop index inde _name on table_name

Copier après la connexion

【例2】删除book表中名称为BkAuAndInfoIdx的组合索引，SQL语句如下：

mysql> drop index BkAuAndInfoIdx on book;Query OK, 0 rows affected (0.08 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create  table book \G*************************** 1. row ***************************
       Table: bookCreate Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `BkNameOdx` (`bookname`),
  KEY `BkcmtIdx` (`bookid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

Copier après la connexion

可以看到，book表中已经没有名称为BkAuAndInfoIdx的组合索引，删除索引成功。

注意：删除表中的列时，如果要删除的列为索引的组成部分，则该部分也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。