MySQL-Index, was bedeutet das?

In MySQL bezieht sich Index auf „Index“, eine spezielle Datenbankstruktur, die aus einer oder mehreren Spalten in der Datentabelle besteht. Sie kann zum schnellen Abfragen von Datensätzen mit einem bestimmten Wert in der Datentabelle verwendet werden. Über den Index müssen Sie beim Abfragen der Daten nicht alle Informationen des Datensatzes lesen, sondern nur die Indexspalte abfragen. Andernfalls liest das Datenbanksystem alle Informationen jedes Datensatzes zum Abgleich. Daher kann die Verwendung von Indizes die Abfragegeschwindigkeit der Datenbank erheblich verbessern und die Leistung des Datenbanksystems effektiv verbessern.

Die Betriebsumgebung dieses Tutorials: Windows7-System, MySQL8-Version, Dell G3-Computer.

In MySQL bezieht sich Index auf „Index“, eine spezielle Datenbankstruktur, die aus einer oder mehreren Spalten in der Datentabelle besteht. Sie kann zum schnellen Abfragen von Datensätzen mit einem bestimmten Wert in der Datentabelle verwendet werden.

Bei Indizes müssen Sie beim Abfragen von Daten nicht alle aufgezeichneten Informationen lesen, sondern nur die Indexspalten abfragen. Andernfalls liest das Datenbanksystem alle Informationen jedes Datensatzes zum Abgleich.

Der Index kann mit der phonetischen Reihenfolge des Xinhua-Wörterbuchs verglichen werden. Wenn Sie beispielsweise das Wort „ku“ nachschlagen möchten und keine Lautfolge verwenden, müssen Sie es Seite für Seite in den 400 Seiten des Wörterbuchs finden. Wenn Sie jedoch das Pinyin extrahieren, um eine phonetische Sequenz zu bilden, müssen Sie es nur direkt in der Phonetiktabelle von mehr als 10 Seiten nachschlagen. Dadurch kann viel Zeit gespart werden.

Daher kann die Verwendung von Indizes die Abfragegeschwindigkeit der Datenbank erheblich verbessern und die Leistung des Datenbanksystems effektiv verbessern.

Warum einen Index verwenden

Ein Index ist eine Tabelle mit Korrespondenzen zwischen Spaltenwerten und Datensatzzeilen, die in einer bestimmten Reihenfolge basierend auf einer Spalte oder mehreren Spalten in der Tabelle erstellt wird Indexspalte. Eine geordnete Tabelle, die eine Eins-zu-Eins-Entsprechung mit den Datensatzzeilen in der Originaltabelle aufweist.

Index ist ein sehr wichtiges Datenbankobjekt in MySQL. Es ist die Grundlage der Datenbankleistungsoptimierungstechnologie und wird häufig zum schnellen Abrufen von Daten verwendet.

In MySQL gibt es normalerweise zwei Möglichkeiten, auf Zeilendaten einer Datenbanktabelle zuzugreifen:

1) Sequenzieller Zugriff

Sequentieller Zugriff besteht darin, einen vollständigen Tabellenscan in der Tabelle durchzuführen und sie Zeile für Zeile von Anfang bis Ende zu durchlaufen Die ungeordneten Zieldaten, die die Bedingungen erfüllen, werden in den Zeilendaten gefunden.

Sequentieller Zugriff ist relativ einfach zu implementieren, aber wenn die Tabelle eine große Datenmenge enthält, ist die Effizienz sehr gering. Wenn Sie beispielsweise unter mehreren zehn Millionen Daten nach einer kleinen Datenmenge suchen, werden bei Verwendung des sequentiellen Zugriffs alle Daten durchsucht, was viel Zeit in Anspruch nimmt und sich offensichtlich auf die Verarbeitungsleistung der Datenbank auswirkt.

2) Indexzugriff

Der Indexzugriff ist eine Möglichkeit, durch Durchlaufen des Index direkt auf Datensatzzeilen in der Tabelle zuzugreifen.

Die Voraussetzung für die Verwendung dieser Methode besteht darin, einen Index für die Tabelle zu erstellen. Nachdem Sie den Index für die Spalte erstellt haben, können Sie bei der Suche nach Daten direkt die Position der entsprechenden Datensatzzeile basierend auf dem Index für die Spalte finden schnelles Auffinden der Daten. Der Index speichert Zeiger auf die Datenwerte der angegebenen Spalten und sortiert diese Zeiger gemäß der angegebenen Sortierreihenfolge.

Wenn beispielsweise in der Tabelle mit den grundlegenden Studenteninformationen tb_students ein Index basierend auf student_id erstellt wird, erstellt das System eine Zuordnungstabelle von der Indexspalte zum tatsächlichen Datensatz. Wenn der Benutzer die Daten mit student_id 12022 finden muss, findet das System zuerst den Datensatz im Index student_id, dann direkt die Datenzeile über die Zuordnungstabelle und gibt die Datenzeile zurück. Da die Geschwindigkeit beim Scannen von Indizes im Allgemeinen viel höher ist als die Geschwindigkeit beim Scannen tatsächlicher Datenzeilen, kann die Verwendung von Indizes die Effizienz der Datenbank erheblich verbessern.

Kurz gesagt: Ohne die Verwendung eines Index muss MySQL die gesamte Tabelle lesen, beginnend mit dem ersten Datensatz, 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 zum Durchsuchen der Datendatei gelangen, ohne sich alle Daten ansehen zu müssen, was viel Zeit spart.

Vor- und Nachteile der Indexierung

Die Indexierung hat ihre offensichtlichen Vorteile und ihre unvermeidlichen Nachteile.

Vorteile

Die Vorteile von Indizes sind wie folgt:

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

• Sie können Indizes für alle MySQL-Spaltentypen festlegen.

• kann die Datenabfrage erheblich beschleunigen, was der Hauptgrund für die Verwendung von Indizes ist.

• kann die Verbindung zwischen Tabellen beschleunigen, um die referenzielle Integrität von Daten zu erreichen.

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

Das Erstellen und Verwalten von Indexgruppen nimmt Zeit in Anspruch, und diese Zeit nimmt mit zunehmender Datenmenge zu.

Indizes müssen zusätzlich zum von der Datentabelle belegten Datenspeicherplatz auch eine bestimmte Menge an physischem Speicherplatz belegen. Wenn Sie über eine große Anzahl von Indizes verfügen, erreichen die Indexdateien möglicherweise schneller ihre maximale Dateigröße als die Datendateien.

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.

Bei der Verwendung von Indizes müssen Sie die Vor- und Nachteile von Indizes berücksichtigen.

Indizes können die Abfragegeschwindigkeit erhöhen, wirken sich jedoch auf die Geschwindigkeit beim Einfügen von Datensätzen aus. Denn beim Einfügen von Datensätzen in eine indizierte Tabelle sortiert das Datenbanksystem nach dem Index, wodurch die Geschwindigkeit beim Einfügen von Datensätzen verringert wird. Die Auswirkungen auf die Geschwindigkeit werden beim Einfügen einer großen Anzahl von Datensätzen deutlicher. In diesem Fall besteht die beste Möglichkeit darin, zuerst den Index in der Tabelle zu löschen, dann die Daten einzufügen und nach Abschluss des Einfügens den Index zu erstellen.

Index-Designprinzipien

1) Es ist zu beachten, dass Indizes auch Speicherplatz belegen, sodass mehr nicht immer besser ist.

2) Zu viele Indizes beeinträchtigen die Ausführungseffizienz von INSERT, DELETE, UPDATE und anderen Anweisungen.

3) Es wird nicht empfohlen, einen Index zu erstellen, wenn zu wenig Daten vorhanden sind.

4) Für eindeutige Einschränkungen sollte der entsprechende eindeutige Index verwendet werden.

5) Versuchen Sie, Felder mit geringer Differenzierung nicht zu indizieren. Beispiel: Das Geschlechtsfeld des Aufzählungstyps enthält nur Männer und Frauen, wodurch keine Optimierungseffekte erzielt werden.

6) Es wird empfohlen, beim Erstellen der Tabelle einen Index zu erstellen. Wenn die Tabelle eine große Anzahl von Datensätzen enthält, werden alle Datensätze in der Tabelle geändert, nachdem die Spalte als Index erstellt wurde.

7) Es wird nicht empfohlen, Indizes für Felder zu erstellen, die nicht häufig abgefragt werden.

Indextyp

Typ	Funktion
Normaler Index	Grundlegende Indexierungsmethode, keine besonderen Anweisungen
Einzigartiger Index	Daten sind eindeutig, zulässig. Es gibt null
Primärschlüsselindex	Es gibt nur einen Primärschlüssel in der Tabelle, die Daten sind eindeutig, Null ist nicht zulässig
Zusammengesetzter Index	Er kann zwei oder mehr Indexspalten enthalten

Wählen Sie die aus Entsprechender Index entsprechend verschiedenen Umgebungen. Geben Sie einfach ein.

Indexsyntax erstellen

Nehmen Sie einen gewöhnlichen Index als Beispiel, erstellen Sie einen Index, wenn Sie eine Tabelle erstellen

create table 表名(
 字段定义…
 index 索引名称(字段));

Nehmen Sie einen gewöhnlichen Index als Beispiel, ändern Sie die Tabelle in einen Index

alter table 表名 add index 索引名称(字段);

Sie können stattdessen den Schlüssel verwenden Index, und der Indexname kann weggelassen werden. Ohne Hinzufügen eines Indexnamens wird der Feldname standardmäßig als Indexname verwendet.

Normaler Index

Nichts zu beachten, es ist die grundlegendste Indexierungsmethode.

Erstellen Sie eine Tabelle und richten Sie einen normalen Index ein.

mysql> create table test1(id int,name varchar(5),age tinyint,index index_id(id));

Sehen Sie sich die Tabellenstruktur unter dem Schlüssel an.

mysql> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  | MUL | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

test

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

simuliert die Ausführung der SQL-Abfrageanweisung über

explain

, ohne zuerst den Index zu übergeben, um den Datensatz mit dem Namen Zhao Liu abzufragen.

mysql> insert into test1 values(1,'张三',21),(2,'李四',22),(3,'王五',23),(4,'赵六',24);

type:ALL-Element gibt den vollständigen Tabellenscan an, Zeilen: 4 gibt die Anzahl der Zeilen an. Da Zhao Liu der letzte Datensatz in der Tabelle ist, durchläuft die Abfrage die gesamte Tabelle.

Als nächstes fragen Sie das Feld mit dem Namen Zhao Liu über die Indexspalten-ID ab.

mysql> explain select * from test1 where name='赵六'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

rows: 1 bedeutet, dass nur eine Zeile abgerufen und der Datensatz gefunden wurde. Das Urteil ist eindeutig.

Eindeutiger IndexIn einem eindeutigen Index sind die Daten in der Indexspalte eindeutig und es können keine doppelten Werte angezeigt werden. Er wird verwendet, um den Inhalt einzuschränken, und Nullwerte sind zulässig.

Syntax:

mysql> explain select * from test1 where id=4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
   partitions: NULL
         type: ref
possible_keys: index_id
          key: index_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Eindeutige Indizes werden häufig in Feldern verwendet, deren Werte nicht wiederholt werden können, ID-Nummern, Mobiltelefonnummern usw.

Erstellen Sie eine Tabelle und erstellen Sie einen eindeutigen Index mit der Mobiltelefonnummer als Indexspalte.

create table 表名（
 字段定义…
 unique key 索引名(字段名)）;

Sehen Sie sich die Tabellenstruktur an. Die UNI unter dem Schlüssel wird als eindeutiger Index dargestellt.

mysql> create table test2(id int,name varchar(5),phone int,unique key unique_key_phone(phone));

Fügen Sie die Daten ein und testen Sie die Funktionen.

mysql> desc test2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  |     | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
| phone | int(11)    | YES  | UNI | NULL    |       |
+-------+------------+------+-----+---------+-------+

Sehen Sie sich die Tabellendaten an Der Primärschlüsselindex dient zum Festlegen des Primärschlüssels. Jede Tabelle kann höchstens einen Primärschlüssel haben. Die Werte der Primärschlüsselspalte müssen eindeutig sein und Nullwerte sind nicht zulässig.

Syntax:

Tabellennamen erstellen (Feld-Primärschlüssel); Tabellennamen erstellen (Felddefinition...Primärschlüssel-Indexname (Feld));

Tabelle erstellen und festlegen es zum Primärschlüsselindex, die Indexspalte ist id

mysql> insert into test2 values(1,'张三',1111111111);
mysql> insert into test2 values(2,'李四',null);	#可以为null
mysql> insert into test2 values(3,'王五',1111111111);	#值必须唯一
ERROR 1062 (23000): Duplicate entry '1111111111' for key 'unique_key_phone'

Sehen Sie sich die Tabellenstruktur an, unter Schlüssel befindet sich die Primärschlüsselindexspalte von PRI

mysql> select * from test2;
+------+--------+------------+
| id   | name   | phone      |
+------+--------+------------+
|    2 | 李四   |       NULL |
|    1 | 张三   | 1111111111 |
+------+--------+------------+

Fügen Sie Daten ein und testen Sie die Funktion

mysql> create table test3(id int primary key,name varchar(5),age tinyint);

Tabellendaten anzeigen

mysql> desc test3;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

Zusammengesetzter Index

Zusammengesetzter Index Ein Index kann zwei oder mehr Spalten enthalten.

Es gibt keine spezifische Syntax, Sie können Doppelindizes für die Tabelle erstellen

Erstellen Sie eine Tabelle und legen Sie sie als zusammengesetzten Primärschlüssel fest. Die Indexspalten sind ID, Name

mysql> insert into test3 values(1,'张三',23);
mysql> insert into test3 values(null,'张三',23);	#不能为null
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into test3 values(1,'张三',23);	#值不能重复
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Die Eigenschaften des zusammengesetzten Primärschlüssels sind etwas anders aus dem Primärschlüssel müssen Sie nur ein Feld haben, das sich nicht wiederholt.

mysql> select * from test3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   23 |
+----+--------+------+

Tabellendaten anzeigen

mysql> create table test4 (id int,name varchar(5),age tinyint,primary key(id,name));

[Verwandte Empfehlungen: MySQL-Video-Tutorial]

Das obige ist der detaillierte Inhalt vonMySQL-Index, was bedeutet das?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!