Wie lautet die Syntax des MySQL-Index?

Indexdefinition

Ein Index ist eine geordnete Datenstruktur, die MySQL dabei hilft, Daten effizient zu erhalten. Dies ist MySQLs offizielle Definition eines Index. Zur Verbesserung der Abfrageeffizienz sind Indizes ein Mechanismus, der Feldern in Datenbanktabellen hinzugefügt wird. Zusätzlich zu den Daten verwaltet das Datenbanksystem auch Datenstrukturen, die bestimmte Suchalgorithmen erfüllen. Diese Datenstrukturen verweisen auf die Daten, sodass erweiterte Suchalgorithmen auf diesen Datenstrukturen implementiert werden können Index. Wie im Diagramm unten gezeigt:

Tatsächlich ist ein Index, einfach ausgedrückt, eine sortierte Datenstruktur

Die linke Seite ist die Datentabelle mit insgesamt zwei Spalten und sieben Datensätzen und die ganz linke Seite Eine davon ist die physische Struktur der Datensatzadresse (beachten Sie, dass logisch benachbarte Datensätze nicht unbedingt physisch benachbart auf der Festplatte liegen). Um die Suche nach Col2 zu beschleunigen, können Sie einen binären Suchbaum pflegen, wie rechts gezeigt. Jeder Knoten enthält einen Indexschlüsselwert und einen Zeiger auf die physische Adresse des entsprechenden Datensatzes Verwenden Sie die binäre Suche, um schnell an die entsprechenden Daten zu gelangen.

Indexvorteile

• Beschleunigen Sie die Geschwindigkeit von Suchen und Sortieren, reduzieren Sie die E/A-Kosten der Datenbank und den CPU-Verbrauch

• Durch die Erstellung eines eindeutigen Indexes können Sie die Einzigartigkeit jedes einzelnen Indexes sicherstellen Datenzeile in der Datenbanktabelle.

Nachteile des Index

1. Der Index ist eigentlich eine Tabelle, die den Primärschlüssel und das Indexfeld speichert und auf den Datensatz der Entitätsklasse verweist.

2. Obwohl Es erhöht die Abfrageeffizienz. Jedes Mal, wenn die Tabelle geändert wird, muss der Index aktualisiert werden. Natürlich müssen neue Knoten zum Indexbaum hinzugefügt werden Der Indexbaum kann ungültig werden, was bedeutet, dass viele Knoten in diesem Indexbaum ungültig sind. Änderung: Der -Zeiger des Knotens im Indexbaum muss möglicherweise geändert werden

Tatsächlich verwenden wir ihn jedoch nicht binärer Suchbaum zum Speichern in MySQL Warum?

Sie müssen wissen, dass in einem binären Suchbaum ein Knoten hier nur ein Datenelement speichern kann und ein Knoten einem Festplattenblock in MySQL entspricht. Auf diese Weise können wir jedes Mal einen Festplattenblock lesen Um ein einzelnes Datenelement zu erhalten, ist die Effizienz besonders gering, daher werden wir darüber nachdenken, eine B-Baum-Struktur zu verwenden, um es zu speichern.

Indexstruktur

Indizes werden in der Speicher-Engine-Schicht von MySQL implementiert, nicht in der Serverschicht. Daher können sich die Indizes zwischen den Speicher-Engines unterscheiden und nicht alle Engines unterstützen alle Arten von Indizes.

• BTREE-Index: Der häufigste Indextyp. Die meisten Indizes unterstützen B-Tree-Indizes.

• HASH-Index: Wird nur von der Speicher-Engine unterstützt, das Nutzungsszenario ist einfach.

• R-Baum-Index (räumlicher Index): Der räumliche Index ist ein spezieller Indextyp der MyISAM-Engine. Er wird hauptsächlich für Geodatentypen verwendet und wird nicht speziell eingeführt.

• Volltext (Volltextindex): Der Volltextindex ist ebenfalls ein spezieller Indextyp von MyISAM, der hauptsächlich für den Volltextindex verwendet wird, beginnend mit der Mysql5.6-Version.

Die drei Speicher-Engines MyISAM, InnoDB und Memory unterstützen verschiedene Indextypen MEMORY-Engine

Die Indizes, auf die wir uns normalerweise beziehen, sind, sofern nicht ausdrücklich angegeben, mithilfe einer B+-Baumstruktur (einem mehrseitigen Suchbaum, nicht unbedingt binär) organisiert. Clustered-Indizes, zusammengesetzte Indizes, Präfixindizes und eindeutige Indizes, sogenannte Indizes, verwenden standardmäßig alle B+-Baumindizes.

BTREE

Mehrfach ausgeglichener Suchbaum, ein m-Ordnung (m-Fork) BTREE erfüllt:

• Maximal m Kinder pro Knoten Anzahl der Kinder: ceil(m/2) bis m Anzahl der Schlüsselwörter: ceil (m/2) -1 bis m-1 Baum wird nicht zerstört

Aufgrund von Level 3 kann es höchstens 2 Knoten geben, also sind 26 und 30 zunächst zusammen, und dann beginnt 85, sich zu teilen, 30 wird die obere Mittelposition sein, 26 bleiben übrig 85 geht nach rechts

Die mittlere Position Die obere Position

, dann bleibt die linke Seite am alten Knoten und die rechte Seite geht zum neuen Knoten

Wenn 70 erneut in das Bild eingefügt wird, 70 ist zufällig die obere Position in der Mitte, dann bleibt 62 übrig und 85 wird in einen neuen Knoten geteilt

Einfach weiter nach oben teilen. Ähnlich

Im Vergleich zu binären Suchbäumen ist die Höhe/Tiefe geringer und die natürliche Abfrageeffizienz höher.

B+BAUM

B+-Baum hat zwei Arten von Knoten: interne Knoten (auch

genannt) und Blattknoten

klein bis groß

• Jeder Blattknoten speichert Zeiger auf benachbarte Blattknoten. Die Blattknoten selbst sind entsprechend der Größe der Schlüsselwörter in der Reihenfolge von klein nach groß verbunden.

  Der übergeordnete Knoten speichert den Index
des ersten Elements des
• rechten untergeordneten Knotens.

• Im Vergleich zu den Vorteilen

Die Abfrageeffizienz von B+Tree
• ist stabiler

  . Da nur die Blattknoten von B+Tree Schlüsselinformationen speichern, erfordert die Abfrage eines beliebigen Schlüssels den Übergang von der Wurzel zu den Blättern und ist daher stabiler.

Sie müssen nur die Blattknoten durchqueren, um den gesamten Baum zu durchqueren.

• Aufmerksame Schüler können erkennen, was der größte Unterschied zwischen diesem Bild und unserem binären Suchbaumdiagramm ist?

Vom
• binären Suchbaum zum B-Baum

  besteht eine wesentliche Änderung darin, dass ein Knoten mehrere Daten speichern kann, was einem Festplattenblock entspricht, der mehrere Daten speichern kann, was unsere E/A-Frequenz erheblich reduziert! !

B+Baumindexstrukturdiagramm in MySQL:

Binäres Suchbaumdiagramm:

• Indexprinzip

  BBaumindex:

  Initialisierungseinführung

d. h. 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. „

Datenelemente, die die Suchrichtung bestimmen

Suchvorgang

Wenn Sie das Datenelement 29 finden möchten, wird zunächst Festplattenblock 1 von der Festplatte in den Speicher geladen und zu diesem Zeitpunkt erfolgt ein E/A. Verwenden Sie eine binäre Suche im Speicher, um festzustellen, ob 29 zwischen 17 und 35 liegt, und sperren Sie den P2-Zeiger von Festplattenblock 1. Die Speicherzeit ist vernachlässigbar, da sie sehr kurz ist (im Vergleich zur E/A der Festplatte). Die Adresse des P2-Zeigers von Plattenblock 1 auf Plattenblock 3 wird von der Platte in den Speicher geladen. Der zweite IO liegt zwischen 26 und 30. Der P2-Zeiger von Plattenblock 3 wird gesperrt Der Speicher durchläuft den Zeiger. Der dritte IO erfolgt gleichzeitig. Die binäre Suche erreicht 29 und beendet die Abfrage, was zu insgesamt drei IOs führt.

Die reale Situation ist, dass ein dreischichtiger B+-Baum Millionen von Daten darstellen kann. Wenn für Millionen von Datensuchen nur drei E/As erforderlich sind, ist die Leistungsverbesserung enorm. Wenn kein Index vorhanden ist, muss jedes Datenelement durchsucht werden . Ein IO erfordert insgesamt Millionen von IOs. Die Kosten sind natürlich sehr, sehr hoch.

Indexklassifizierung

Eine indexorganisierte Tabelle ist eine Tabelle, die in Primärschlüsselreihenfolge als Index gespeichert ist. Diese Methode ist für die InnoDB-Engine geeignet. Da InnoDB das B+-Baum-Indexmodell verwendet, werden die Daten im B+-Baum gespeichert.

Jeder Index entspricht einem B+-Baum in InnoDB.
Angenommen, wir haben eine Tabelle mit der Primärschlüsselspalte als ID, es gibt das Feld k in der Tabelle und es gibt einen Index für k.
Die Tabellenerstellungsanweisung dieser Tabelle lautet:

mysql> create table T( 
  id int primary key, 
  k int not null,  
  name varchar(16), 
  index (k))engine=InnoDB; 
复制代码

Die (ID,k)-Werte von R1~R5 in der Tabelle sind (100,1), (200,2), (300,3), (500,5) und (600,6), das Beispieldiagramm zweier Bäume lautet wie folgt:

Aus der Abbildung ist leicht ersichtlich, dass der Indextyp entsprechend dem Inhalt des Blattknotens lautet unterteilt in Primärschlüsselindex und Nicht-Primärschlüsselindex.

Primärschlüsselindex

Die Primärschlüsselspalte der Datentabelle verwendet den Primärschlüsselindex und wird standardmäßig erstellt. Aus diesem Grund sagte uns der Lehrer, bevor wir die Indizierung lernten, dass die Suche basierend auf dem Primärschlüssel schneller sei Es stellt sich heraus, dass der Primärschlüssel selbst der Index ist. Der Blattknoten des
Primärschlüsselindex speichert die gesamte Datenzeile. In InnoDB wird der Primärschlüsselindex auch „Clustered Index“ (Clustered Index) genannt. Der Blattknoteninhalt des Hilfsindex

ist der Wert des Primärschlüssels. In InnoDB wird der Hilfsindex auch „Sekundärindex“ (Sekundärindex) genannt. Wie unten gezeigt:

Der Primärschlüsselindex speichert

• Der Hilfsindex speichert nur sich selbst und der ID-Primärschlüssel wird für Tabellenrückgabeabfragen verwendet

Was ist der Unterschied zwischen Abfragen, die auf Primärschlüsselindizes und Hilfsindizes basieren?

Wenn die Anweisung „select * from T“ ist, wobei ID = 500 ist, also die Primärschlüssel-Abfragemethode, müssen Sie nur den B+-Baum der ID durchsuchen.

• Wenn die Anweisung „select * from“ ist T mit k = 5, d. h. bei der normalen Indexabfragemethode müssen Sie zuerst den

  k-Indexbaum
durchsuchen, um den ID-Wert 500 zu erhalten,
• und dann erneut im ID-Indexbaum

  suchen. Dieser Vorgang wird „Zurück zum Tisch“ genannt. Mit anderen Worten: Abfragen, die auf dem Hilfsindex basieren, müssen einen weiteren Indexbaum durchsuchen. Daher sollten wir versuchen, in unseren Anwendungen Primärschlüsselabfragen zu verwenden.

  Abgesehen davon, dass die Daten, die wir abfragen möchten, zufällig in unserem Indexbaum vorhanden sind, nennen wir ihn „Covering Index“ – das heißt, die Indexspalte enthält alle Daten, die wir abfragen möchten.

Unique Key: Unique Key ist ebenfalls eine Einschränkung.
Duplizierte Daten können nicht in der Attributspalte eines eindeutigen Index angezeigt werden, die Daten dürfen jedoch NULL sein. Eine Tabelle ermöglicht die Erstellung mehrerer eindeutiger Indizes.
In den meisten Fällen dient die Einrichtung eines eindeutigen Index eher der Eindeutigkeit der Daten in der Attributspalte als der Abfrageeffizienz.

• Gewöhnlicher Index (Index): Die einzige Funktion eines gewöhnlichen Index besteht darin, Daten schnell abzufragen. Eine Tabelle ermöglicht die Erstellung mehrerer gewöhnlicher Indizes und ermöglicht die Duplizierung von Daten und NULL.

• Präfixindex (Präfix): Der Präfixindex gilt nur für Daten vom Typ Zeichenfolge. Der Präfixindex erstellt einen Index für die ersten Zeichen des Textes. Im Vergleich zum normalen Index sind die erstellten Daten kleiner, da nur die ersten Zeichen abgerufen werden.

Volltextindex (Volltext)
• : Der Volltextindex wird hauptsächlich zum Abrufen von Schlüsselwortinformationen in großen Textdaten verwendet. Es handelt sich um eine Technologie, die derzeit von Suchmaschinendatenbanken verwendet wird. Vor Mysql5.6 unterstützte nur die MYISAM-Engine die Volltextindizierung. Nach 5.6 unterstützt InnoDB auch die Volltextindizierung

Extension--index pushdown

Der sogenannte Pushdown ist, wie der Name schon sagt, eigentlich Verschiebung unseres Tabellenrückgabevorgangs#🎜🎜 #, MySQL Wir werden nicht einfach aufgefordert, die Tabelle zurückzugeben, da dies sehr verschwenderisch ist. Was bedeutet es? Betrachten Sie das folgende Beispiel.

SELECT name FROM tb_seller WHERE name like '小米%' and status ='1' ;
复制代码

创建复合索引:
	CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;
复制代码

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB
复制代码

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]
复制代码

select index from tbl_name\G;
复制代码

drop INDEX index_name on tbl_name ;
复制代码

1). alter  table  tb_name  add  primary  key(column_list); 
	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL	
	
2). alter  table  tb_name  add  unique index_name(column_list);
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
3). alter  table  tb_name  add  index index_name(column_list);
	添加普通索引， 索引值可以出现多次。
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	该语句指定了索引为FULLTEXT， 用于全文索引
复制代码

show status like 'Handler_read%';	 -- 查看当前会话索引使用情况

show global status like 'Handler_read%';	-- 查看全局索引使用情况
复制代码

Das obige ist der detaillierte Inhalt vonWie lautet die Syntax des MySQL-Index?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!