Vertrautheit mit MySQL-Indizes

1. Einführung in Indizes
(1) Die Bedeutung und Spezifität von Indizes
(2) Klassifizierung von Indizes
(3) Gestaltungsprinzipien von Indizes

2. Erstellen Sie Indizes
(1) Erstellen Sie Indizes beim Erstellen von Tabellen
(2) Erstellen Sie einen Index für eine vorhandene Tabelle
(3) Löschen Sie den Index

(Kostenlose Lernempfehlung: MySQL-Video-Tutorial )

---

1. Einführung in Indizes

Indizes werden verwendet, um schnell herauszufinden, ob es einen gibt ein bestimmter Wert in einer Spaltenzeile mit Werten. Ohne die Verwendung eines Index muss MySQL die gesamte Tabelle beginnend mit dem ersten Datensatz lesen, bis die relevanten Zeilen gefunden werden. Je größer die Tabelle, desto länger dauert die Abfrage der Daten. Wenn die abgefragte Spalte in der Tabelle über einen Index verfügt, kann MySQL schnell an einen Ort gelangen, an dem die Datendatei durchsucht werden kann, ohne sich alle Daten ansehen zu müssen.

(1)Die Bedeutung und spezifische Bedeutung des Index: Der Index ist eine separate Datenbankstruktur, die auf der Festplatte gespeichert ist und Referenzzeiger auf alle Datensätze in der Datentabelle enthält. Wird verwendet, um schnell Zeilen mit einem bestimmten Wert in einer oder mehreren Spalten zu finden.

Indizes werden in Speicher-Engines implementiert, sodass die Indizes der einzelnen Speicher-Engines nicht unbedingt genau gleich sind und nicht jede Speicher-Engine unbedingt alle Indextypen unterstützt. Definieren Sie die maximale Anzahl von Indizes und die maximale Indexlänge für jede Tabelle entsprechend der Speicher-Engine. Alle Speicher-Engines unterstützen mindestens 16 Indizes pro Tabelle mit einer Gesamtindexlänge von mindestens 256 Byte. Die meisten Speicher-Engines haben höhere Grenzwerte.

Es gibt zwei Speichertypen von Indizes in MySQL:

, die sich speziell auf die Speicher-Engine der Tabelle beziehen; MyISAM- und InnoDB-Speicher-Engines unterstützen nur BTREE-Indizes;

BTREE和HASHVorteile des Index:

1. Durch die Erstellung eines eindeutigen Index kann die Eindeutigkeit jeder Datenzeile in der Datenbanktabelle garantiert werden.

2. Kann die Datenabfragegeschwindigkeit erheblich beschleunigen. (Der Hauptgrund für die Erstellung eines Index)
3. Im Hinblick auf die Erzielung der referenziellen Integrität von Daten kann die Verbindung zwischen Tabellen und Tabellen beschleunigt werden.
4. Durch die Verwendung von Gruppierungs- und Sortierklauseln für die Datenabfrage kann auch die Zeit für das Gruppieren und Sortieren in der Abfrage erheblich verkürzt werden.

Nachteile beim Hinzufügen von Indizes:

1. Das Erstellen und Verwalten von Indizes nimmt Zeit in Anspruch, und mit zunehmender Datenmenge steigt auch der Zeitaufwand.

2. Indizes belegen zusätzlich zum von der Datentabelle belegten Datenspeicherplatz auch eine bestimmte Menge an physischem Speicherplatz. Bei einer großen Anzahl von Indizes erreicht die Indexdatei möglicherweise schneller die maximale Dateigröße als die Datendatei.
3. Beim Hinzufügen, Löschen und Ändern von Daten in der Tabelle muss der Index auch dynamisch gepflegt werden, was die Geschwindigkeit der Datenpflege verringert.

(2) Klassifizierung von Indizes

1. Gewöhnlicher Index und eindeutiger Index (einzigartig)

Gewöhnlicher Index ist der grundlegende Indextyp in MySQL, der das Einfügen doppelter Werte und Nullwerte in die Spalte ermöglicht das definiert den Index.

• Einzigartiger Index, der Wert der Indexspalte muss eindeutig sein, es sind jedoch Nullwerte zulässig. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein.
• Der Primärschlüsselindex ist ein spezieller eindeutiger Index, der keine Nullwerte zulässt.
• 2. Einzelspaltenindex und kombinierter Index

Einzelne Spalte, Sie haben also einen Index, der nur eine einzelne Spalte enthält, und eine Tabelle kann mehrere einzelne Spaltenindizes haben.

• Kombinierter Index bezieht sich auf einen Index, der für eine Kombination mehrerer Felder in der Tabelle erstellt wird. Der Index wird nur verwendet, wenn die linken Felder dieser Felder in den Abfragebedingungen verwendet werden.
• Folgen Sie dem Präfixsatz ganz links, wenn Sie kombinierte Indizes verwenden.
• 3. Volltextindex (Volltext)

Der Volltextindextyp ist FULLTEXT, der die Volltextsuche nach Werten in den Spalten unterstützt, in denen der Index definiert ist, wodurch doppelte Werte und Nullen möglich sind Werte, die in diese Indexspalten eingefügt werden sollen, können in einer Spalte vom Typ char, varchar oder text erstellt werden. Nur die MyISAM-Speicher-Engine in MySQL unterstützt die Volltextindizierung.

• 4. Räumlicher Index (räumlich)

Ein räumlicher Index ist ein Index, der für Felder räumlicher Datentypen erstellt wird. In MySQL gibt es 4 räumliche Datentypen, nämlich Geometrie, Punkt, Linienfolge und Polygon. MySQL wurde um das Schlüsselwort „spatial“ erweitert, sodass räumliche Indizes mit einer Syntax erstellt werden können, die derjenigen ähnelt, die zum Erstellen regulärer Indizes verwendet wird. Spalten, die zum Erstellen räumlicher Indizes verwendet werden, müssen als nicht null deklariert werden. Räumliche Indizes können nur in Tabellen erstellt werden, deren Speicher-Engine MySQL ist.

• (3) Prinzipien des Indexdesigns

Unangemessenes Indexdesign oder fehlende Indizes beeinträchtigen die Leistung der Datenbank und der Anwendungen. Effiziente Indizes sind sehr wichtig, um eine gute Leistung zu erzielen. Beim Entwerfen von Indizes sollten die folgenden Richtlinien berücksichtigt werden:

1.

• 2. Vermeiden Sie eine übermäßige Indizierung häufig aktualisierter Tabellen und indizieren Sie so wenige Spalten wie möglich.
• 3. Es ist am besten, keine Indizes für Tabellen mit kleinen Datenmengen zu verwenden.
• 4. Erstellen Sie Indizes für Spalten mit vielen unterschiedlichen Werten, die häufig in bedingten Ausdrücken verwendet werden. Erstellen Sie keine Indizes für Spalten mit wenigen unterschiedlichen Werten.
• 5. Wenn die Eindeutigkeit ein Merkmal einiger Daten selbst ist, geben Sie einen eindeutigen Index an.
• 6. Erstellen Sie Indizes für Spalten, die häufig sortiert oder gruppiert werden (gruppieren nach oder sortieren nach Vorgängen). Wenn mehrere Spalten sortiert werden müssen, können Sie einen kombinierten Index für diese Spalten erstellen.
• 2. Index erstellen

Syntaxformat:

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

• unique, fulltext und spatial sind optionale Parameter, die jeweils einen eindeutigen Index, einen Volltextindex und einen räumlichen Index darstellen.
• Index und Schlüssel sind Synonyme. Sie haben die gleiche Funktion und werden zur Angabe der Erstellung eines Index verwendet.
• col_name ist die Feldspalte, die indiziert werden muss. Diese Spalte muss aus mehreren in der Datentabelle definierten Spalten ausgewählt werden.
• index_name gibt den Namen des Index an, der ein optionaler Parameter ist. Wenn nicht angegeben, verwendet MySQL standardmäßig col_name als Indexwert.
• length ist ein optionaler Parameter, der die Länge des Index angibt. Nur Felder vom Typ String können die Indexlänge angeben.
• asc oder desc gibt die Speicherung des Indexwerts in aufsteigender oder absteigender Reihenfolge an.

(1) Erstellen Sie beim Erstellen einer Tabelle einen Index

①Erstellen Sie einen normalen Index.

Ein normaler Index ist der einfachste Indextyp. Er hat keine Einschränkungen wie Einzigartigkeit. Seine Funktion besteht lediglich darin, den Zugriff auf Daten zu beschleunigen.

[Beispiel 1] Erstellen Sie einen normalen Index für das Feld „year_publication“ in der Buchtabelle. Die SQL-Anweisung lautet wie folgt:

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)

Die Erklärung jeder Zeile des Ausgabeergebnisses der EXPLAN-Anweisung lautet wie folgt:

• select_type line gibt an Der verwendete Auswahlabfragetyp ist hier der Wert „Einfach“, was eine einfache Auswahl ohne Verwendung von Union oder Unterabfrage bedeutet. Andere mögliche Werte sind primär, Union, Unterabfrage usw. Die
• table-Zeile gibt den Namen der von der Datenbank gelesenen Datentabelle an und ist in der Reihenfolge angeordnet, in der sie gelesen werden.
• Die Typzeile gibt die Beziehung zwischen dieser Datenbanktabelle und anderen Datenbanktabellen an. Zu den möglichen Werten gehören System, Const, eq_ref, Ref, Range, Index und alle.
• possible_keys-Zeile Gibt verschiedene Indizes an, die MySQL bei der Suche nach Datensätzen verwenden kann.
• Schlüsselzeile ist der tatsächlich von MySQL ausgewählte Index.
• Die Zeile „key_len“ gibt die Länge des Index in Bytes an. Je kleiner der Wert „key_len“ ist, desto schneller ist er. Die Zeile
• ref gibt den Namen der Datenspalte in der anderen Datentabelle in der Beziehung an.
• rows rows ist die Anzahl der Datenzeilen, die MySQL bei der Ausführung dieser Abfrage voraussichtlich aus dieser Datentabelle lesen wird.
• Die zusätzliche Zeile enthält Informationen zum zugehörigen Vorgang.

Sie können sehen, dass die Werte von möglichem_Schlüssel und Schlüssel Jahr_Veröffentlichung sind und der Index in der Abfrage verwendet wird.

②Erstellen Sie einen eindeutigen Index

Der Hauptgrund für die Erstellung eines eindeutigen Index besteht darin, die Ausführungszeit für die Abfrage von Indexspaltenoperationen zu verkürzen, insbesondere bei relativ großen Datentabellen. Es ähnelt dem vorherigen normalen Index, außer dass der Wert der Indexspalte eindeutig sein muss, Nullwerte jedoch zulässig sind. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein.

【Beispiel 2】Erstellen Sie eine Tabelle t1 und verwenden Sie das Schlüsselwort unique, um einen eindeutigen Index für das ID-Feld in der Tabelle zu erstellen.

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)

③Einen einspaltigen Index erstellen

Ein einspaltiger Index ist ein Index, der für ein bestimmtes Feld in der Datentabelle erstellt wird. In einer Tabelle können mehrere einspaltige Indizes erstellt werden.

【Beispiel 3】Erstellen Sie eine Tabelle t2 und erstellen Sie einen einspaltigen Index für das Namensfeld in der Tabelle.

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)

Wie Sie den Ergebnissen entnehmen können, wurde erfolgreich ein einspaltiger Index namens SingleIdx mit einer Indexlänge von 20 für das ID-Feld eingerichtet.

④Erstellen Sie einen zusammengesetzten Index
Ein zusammengesetzter Index dient dazu, einen Index für mehrere Felder zu erstellen.

[Beispiel 4] Erstellen Sie Tabelle t3 und erstellen Sie einen kombinierten Index für die Felder ID, Name und Alter in der Tabelle. Die SQL-Anweisung lautet wie folgt:

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)

Der kombinierte Index spielt die Rolle mehrerer Indizes, aber wenn er verwendet wird, Es ist nicht möglich abzufragen, welche Felder alle Indizes verwenden können, sondern dem „ganz links stehenden Präfix“ folgen: Der am weitesten links stehende Spaltensatz im Index wird zum Abgleichen von Zeilen verwendet. Ein solcher Spaltensatz wird als ganz links stehendes Präfix bezeichnet.

Hier ist zum Beispiel ein Index, der aus drei Feldern besteht: ID, Name und Alter. Die Indexzeilen sind in der Reihenfolge ID/Name/Alter sortiert. Der Index kann nach Feldkombinationen suchen: (ID, Name, Alter ), (id, name) oder id. Wenn die Spalte nicht das Präfix ganz links im Index darstellt, kann MySQL den lokalen Index nicht verwenden. Beispielsweise kann die Kombination (Alter) oder (Name, Alter) die Indexabfrage nicht verwenden. Bei der Abfrage der ID- und Namensfelder wird der Multiidx-Index verwendet. Wenn die Kombination aus (Name, Alter) abgefragt wird oder die Namens- und Altersfelder separat abgefragt werden, ist der Index null.

⑤Volltextindex erstellen

Der Volltext-Volltextindex kann für die Volltextsuche verwendet werden. Nur die MyISAM-Speicher-Engine unterstützt Volltextindizes und erstellt nur Indizes für char-, varchar- und text-Spalten. Die Indizierung erfolgt immer für die gesamte Spalte, lokale (Präfix-)Indizes werden nicht unterstützt.

[Beispiel 5] Erstellen Sie Tabelle t4 und erstellen Sie einen Volltextindex für das Infofeld in der Tabelle. Die SQL-Anweisung lautet wie folgt:

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)

Der Volltextindex eignet sich sehr gut für große Datensätze , seine Verwendung ist relativ gering.

6. Erstellen Sie einen räumlichen Index

Der räumliche Index muss in einer MyISAM-Typtabelle erstellt werden und das räumliche Typfeld darf nicht Null sein.

[Beispiel 6] Erstellen Sie Tabelle t5 und erstellen Sie einen räumlichen Index für das Feld mit räumlicher Typgeometrie. Die SQL-Anweisung lautet wie folgt:

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)

Sie können sehen, dass ein räumlicher Index mit dem Namen spatIdx für das g-Feld von Tabelle t5 erstellt wird . Geben Sie beim Erstellen die Nicht-Null-Einschränkung des räumlichen Typfeldwerts an, und die Speicher-Engine der Tabelle ist MyISAM.

(2) Erstellen Sie einen Index für eine vorhandene Tabelle

Um einen Index für eine vorhandene Tabelle zu erstellen, können Sie die Anweisung alter table oder die Anweisung create index verwenden.

1. Verwenden Sie die alter table-Anweisung, um einen Index zu erstellen.

Grundlegende Syntax:

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

[Beispiel 7] Erstellen Sie einen gemeinsamen Index mit dem Namen BkNameIdx für das Buchnamenfeld in der Buchtabelle.

Bevor Sie einen Index hinzufügen, verwenden Sie die Anweisung „show index“, um den in der angegebenen Tabelle erstellten Index anzuzeigen:

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)

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

• 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)

可以看到表中有了两个索引，另一个为通过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)

可以看到，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)

可以看到，语句执行之后再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)

可以看到名称为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)

可以看到，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)

可以看到，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]

可以看到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);

【例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

【例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

【例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

【例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

【例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

【例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

(3)删除索引

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

1.使用alter table删除索引

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

alter table table_name drop index index_name

【例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)

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

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

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

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

drop index inde _name on table_name

【例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)

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

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

相关免费学习推荐：mysql数据库(视频)

Das obige ist der detaillierte Inhalt vonVertrautheit mit MySQL-Indizes. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!