MySQL-Datenbankoptimierung: Indeximplementierungsprinzip und Nutzungsanalyse

Dieser Artikel veranschaulicht anhand von Beispielen die Prinzipien und die Verwendung der Indeximplementierung zur MySQL-Datenbankoptimierung. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Index

Was ist ein Index

Indizes werden verwendet, um Datensätze mit bestimmten Werten schnell zu finden. Alle MySQL-Indizes werden in Form von B-Bäumen gespeichert. Wenn kein Index vorhanden ist, muss MySQL beim Ausführen einer Abfrage alle Datensätze in der gesamten Tabelle beginnend mit dem ersten Datensatz scannen, bis es einen Datensatz findet, der die Anforderungen erfüllt. Je mehr Datensätze die Tabelle enthält, desto aufwändiger ist dieser Vorgang. Wenn für die als Suchbedingung verwendete Spalte ein Index erstellt wurde, kann MySQL den Speicherort des Zieldatensatzes schnell ermitteln, ohne irgendwelche Datensätze zu scannen. Wenn die Tabelle 1.000 Datensätze enthält, ist die Suche nach einem Datensatz mithilfe eines Indexes mindestens 100-mal schneller als das sequenzielle Durchsuchen der Datensätze.

Indexklassifizierung

Primärschlüsselindex

Ein Primärschlüssel ist ein eindeutiger Index, er muss jedoch als „PRIMARY KEY“ angegeben werden. Wenn Sie schon einmal AUTO_INCREMENT-Spalten verwendet haben, sind Sie möglicherweise bereits mit Konzepten wie Primärschlüsseln vertraut. Der Primärschlüssel wird üblicherweise beim Erstellen einer Tabelle angegeben, beispielsweise „CREATE TABLE tablename ([…], PRIMARY KEY (column list));“. Wir können jedoch auch einen Primärschlüssel hinzufügen, indem wir die Tabelle ändern, z. B. mit „ALTER TABLE tablename ADD PRIMARY KEY (column list);“. Jede Tabelle kann nur einen Primärschlüssel haben.

Erstellen eines Primärschlüsselindexes

Ein Primärschlüssel ist ein eindeutiger Index, er muss jedoch als „PRIMARY KEY“ angegeben werden. Wenn Sie schon einmal AUTO_INCREMENT-Spalten verwendet haben, sind Sie möglicherweise bereits mit Konzepten wie Primärschlüsseln vertraut. Der Primärschlüssel wird üblicherweise beim Erstellen einer Tabelle angegeben, beispielsweise „CREATE TABLE tablename ([…], PRIMARY KEY (column list));“. Wir können jedoch auch einen Primärschlüssel hinzufügen, indem wir die Tabelle ändern, z. B. mit „ALTER TABLE tablename ADD PRIMARY KEY (column list);“. Jede Tabelle kann nur einen Primärschlüssel haben.

Wenn eine Tabelle eine Spalte als Primärschlüssel festlegt, ist die Spalte der Primärschlüsselindex.

Tabelle aaa erstellen
(id int vorzeichenloser Primärschlüssel auto_increment,
Name varchar(32) nicht null Standard '');

Dies ist die ID-Spalte, die den Primärschlüsselindex darstellt.

Erstellen Sie die Tabelle bbb (ID int, Name varchar(32), nicht null, Standard '');

Wenn Sie beim Erstellen der Tabelle keinen Primärschlüsselindex angegeben haben, können Sie ihn auch nach dem Erstellen der Tabelle hinzufügen. Befehl:

Beispiel:

Tabelle ändern, Tabellenname, Primärschlüssel hinzufügen (Spaltenname);

Löschen des Primärschlüsselindexes

Tabellenartikel ändern, Primärschlüssel löschen;

Abfrageindex

desc Tabellenname; Indexname kann nicht angezeigt werden. Index aus Tabellenname anzeigen. Schlüssel aus Tabellenname anzeigen.

Volltextindex

Tabellenstruktur erstellen

CREATE TABLE-Artikel (
    id INT UNSIGNED AUTO_INCREMENT NICHT NULL PRIMARY KEY,
    Titel VARCHAR(200),
    Textkörper,
    VOLLTEXT (Titel, Text)
   )engine=myisam-Zeichensatz utf8;
INSERT INTO Artikel (Titel, Text) Werte
   ('MySQL-Tutorial', 'DBMS steht für DataBase ...'),
   ('So verwenden Sie MySQL richtig','Nachdem Sie durch ... gegangen sind'),
   ('MySQL optimieren','In diesem Tutorial zeigen wir ...'),
   ('1001 MySQL-Tricks','1. Führen Sie mysqld niemals als Root aus. 2. ...'),
   ('MySQL vs. YourSQL','Im folgenden Datenbankvergleich ...'),
   ('MySQL-Sicherheit', 'Bei richtiger Konfiguration funktioniert MySQL ...');

Falsche Verwendung:

select * from articles where body like '%mysql%'; falscher Verwendungsindex wird nicht wirksam

Richtige Verwendung:

select * from articles where match(title,body) against ( 'database')

veranschaulichen:

1. In MySQL ist der Volltextindex nur für MyISAM gültig
2. Der von MySQL bereitgestellte Volltext ist für Englisch gültig -> Sphinx (Coreseek)-Technologie verarbeitet Chinesisch
3. Die Verwendungsmethode ist „(Feldname...) mit (,Schlüsselwort‘) abgleichen“.
4. Volltextindex: Stoppwörter. Da ein Index in einem Text unendlich viele Zeichen enthält, werden einige häufig verwendete Wörter und Zeichen nicht erstellt. Diese Wörter werden Stoppwörter genannt. Zum Beispiel (a, b, mysql, the)

mysql> select match(title,body) against ('database') from articles; (Die Ausgabe ist der Übereinstimmungsgrad zwischen jeder Zeile und der Datenbank)

Eindeutiger Index

Dieser Indextyp entspricht grundsätzlich dem bisherigen „normalen Index“, allerdings mit einem Unterschied: Alle Werte in der Indexspalte dürfen nur einmal vorkommen, müssen also eindeutig sein. Eindeutige Indizes können auf folgende Arten erstellt werden:

Erstellen Sie einen Index, zum Beispiel CREATE UNIQUE INDEX <Indexname> ON Tabellenname (Spaltenliste);

Ändern Sie die Tabelle, zum Beispiel mit ALTER TABLE tablename ADD UNIQUE [Indexname] (Spaltenliste);

Geben Sie beim Erstellen einer Tabelle den Index an, zum Beispiel CREATE TABLE tablename ([…], UNIQUE [Indexname] (Spaltenliste));

Tabellenstruktur erstellen

Tabelle erstellen ddd(ID int Primärschlüssel auto_increment, Name varchar(32) eindeutig);

Beachten

Ein eindeutiges Feld kann NULL sein und mehrere NULL-Werte enthalten, wenn es sich jedoch um einen bestimmten Inhalt handelt, kann dieser nicht wiederholt werden.

Aber es darf keine Wiederholung von leeren Zeichenfolgen geben.''

Normaler Index

Die einzige Aufgabe eines normalen Indexes (ein Index, der durch das Schlüsselwort KEY oder INDEX definiert ist) besteht darin, den Zugriff auf Daten zu beschleunigen. Daher sollten Sie Indizes nur für die Spalten erstellen, die am häufigsten in Abfragebedingungen (WHEREcolumn=) oder Sortierbedingungen (ORDERBYcolumn) vorkommen. Wählen Sie zum Erstellen eines Index möglichst eine Spalte mit den übersichtlichsten und kompaktesten Daten (z. B. eine Ganzzahlspalte).

Tabelle ccc erstellen(
ID int ohne Vorzeichen,
Name varchar(32)
)
Erstellen Sie einen Index mit Indexnamen für die Tabelle (Spalte 1, Spaltenname 2).

So funktioniert der Index

Ein Datenbankindex ist eine sortierte Datenstruktur in einem Datenbankverwaltungssystem, die das schnelle Abfragen und Aktualisieren von Daten in Datenbanktabellen unterstützt. Indizes werden normalerweise mithilfe von B-Bäumen und deren Varianten B+-Bäumen implementiert.
Zusätzlich zu den Daten verwaltet das Datenbanksystem auch Datenstrukturen, die bestimmten Suchalgorithmen genügen. Diese Datenstrukturen verweisen in irgendeiner Weise auf die Daten (verweisen auf sie), sodass erweiterte Suchalgorithmen auf diese Datenstrukturen implementiert werden können. Diese Datenstruktur ist ein Index.
Das Setzen von Indizes für Tabellen hat seinen Preis: Erstens erhöht sich dadurch der Speicherplatz der Datenbank, und zweitens nimmt das Einfügen und Ändern von Daten mehr Zeit in Anspruch (weil auch der Index entsprechend geändert werden muss).

Die obige Abbildung zeigt einen möglichen Indizierungsansatz. Links ist die Datentabelle, die zwei Spalten und sieben Datensätze enthält. Der ganz linke ist die physische Adresse des Datensatzes (beachten Sie, dass logisch benachbarte Datensätze auf der Festplatte nicht unbedingt physisch benachbart sind). Um die Suche in Spalte 2 zu beschleunigen, können wir einen binären Suchbaum wie rechts gezeigt pflegen. Jeder Knoten enthält einen Indexschlüsselwert und einen Zeiger auf die physikalische Adresse des entsprechenden Datensatzes. Auf diese Weise können wir die binäre Suche verwenden, um die entsprechenden Daten innerhalb der Komplexität von O(log2n) zu erhalten.

Durch das Erstellen von Indizes kann die Systemleistung erheblich verbessert werden.

Erstens kann durch die Erstellung eines eindeutigen Indexes die Eindeutigkeit jeder Datenzeile in der Datenbanktabelle gewährleistet werden.
Zweitens kann es den Datenabruf erheblich beschleunigen, was auch der Hauptgrund für die Erstellung eines Indexes ist.
Drittens kann es die Verbindung zwischen Tabellen beschleunigen, was insbesondere bei der Implementierung der referenziellen Integrität von Daten sinnvoll ist.
Viertens kann durch die Verwendung von Gruppierungs- und Sortierklauseln zum Datenabruf auch die Zeit für die Gruppierung und Sortierung in der Abfrage erheblich reduziert werden.
Fünftens können Sie durch die Verwendung von Indizes optimierte Abfragen während des Abfrageprozesses verwenden, um die Systemleistung zu verbessern.

Manche Leute fragen sich vielleicht: Da das Hinzufügen von Indizes so viele Vorteile hat, warum nicht für jede Spalte in der Tabelle einen Index erstellen? Denn das Hinzufügen von Indizes hat auch viele Nachteile.

Erstens nimmt das Erstellen und Verwalten von Indizes Zeit in Anspruch, und dieser Zeitaufwand erhöht sich mit der zunehmenden Datenmenge.
Zweitens benötigen Indizes physischen Speicherplatz. Zusätzlich zu dem von der Datentabelle belegten Datenspeicherplatz nimmt jeder Index auch eine bestimmte Menge physischen Speicherplatzes ein. Wenn Sie einen gruppierten Index erstellen möchten, ist der erforderliche Speicherplatz sogar noch größer.
Drittens muss beim Hinzufügen, Löschen und Ändern von Daten in der Tabelle auch der Index dynamisch verwaltet werden, was die Geschwindigkeit der Datenverwaltung verringert.

Indizes werden für bestimmte Spalten in einer Datenbanktabelle erstellt. Beim Erstellen eines Index sollten Sie berücksichtigen, welche Spalten indiziert werden können und welche nicht. Im Allgemeinen sollten Indizes für die folgenden Spalten erstellt werden: für häufig durchsuchte Spalten, um die Suchvorgänge zu beschleunigen; für Spalten, die als Primärschlüssel dienen, um die Eindeutigkeit der Spalte zu gewährleisten und die Datenstruktur in der Tabelle zu organisieren; für Spalten, die häufig in Verknüpfungen verwendet werden und bei denen es sich hauptsächlich um Fremdschlüssel handelt, um Verknüpfungen zu beschleunigen; erstellen Sie Indizes für Spalten, in denen häufig auf Basis von Bereichen gesucht wird, da der Index bereits sortiert ist und der angegebene Bereich fortlaufend ist; erstellen Sie Indizes für häufig sortierte Spalten, da der Index bereits sortiert ist, sodass Abfragen die Indexsortierung ausnutzen können, um die Sortierabfragezeit zu beschleunigen; erstellen Sie Indizes für Spalten, die häufig in WHERE-Klauseln verwendet werden, um die Bedingungsbestimmung zu beschleunigen.

Ebenso gibt es einige Spalten, für die keine Indizes erstellt werden sollten. Im Allgemeinen weisen Spalten, die nicht indiziert werden sollten, die folgenden Merkmale auf:

Erstens sollten Sie keine Indizes für Spalten erstellen, die selten verwendet oder in Abfragen referenziert werden. Dies liegt daran, dass diese Spalten selten verwendet werden und sich die Abfragegeschwindigkeit weder durch ihre Indizierung noch durch Nichtindizierung verbessern lässt. Im Gegenteil, das Hinzufügen von Indizes reduziert die Systemwartungsgeschwindigkeit und erhöht den Platzbedarf.
Zweitens sollten Sie keine Indizes zu Spalten hinzufügen, die nur sehr wenige Datenwerte enthalten. Dies liegt daran, dass diese Spalten nur sehr wenige Werte aufweisen, beispielsweise die Spalte „Geschlecht“ in der Personaltabelle. Im Abfrageergebnis machen die Datenzeilen im Ergebnissatz einen großen Anteil der Datenzeilen in der Tabelle aus, d. h. der Anteil der Datenzeilen, nach denen in der Tabelle gesucht werden muss, ist sehr groß. Das Hinzufügen von Indizes beschleunigt den Abruf nicht wesentlich.
Drittens sollten Indizes nicht zu Spalten hinzugefügt werden, die als Text-, Bild- und Bit-Datentypen definiert sind. Dies liegt daran, dass das Datenvolumen dieser Spalten entweder sehr groß ist oder nur wenige Werte aufweist.
Viertens: Wenn die Änderungsleistung wesentlich höher ist als die Abrufleistung, sollten Sie keinen Index erstellen. Dies liegt daran, dass Änderungsleistung und Abrufleistung im Widerspruch zueinander stehen. Beim Hinzufügen von Indizes wird die Abrufleistung verbessert, die Änderungsleistung jedoch verringert. Wenn Sie die Anzahl der Indizes verringern, verbessert sich die Änderungsleistung und die Abrufleistung verringert sich. Wenn die Änderungsleistung die Abrufleistung deutlich übersteigt, sollten daher keine Indizes erstellt werden.

Abhängig von den Fähigkeiten der Datenbank können im Datenbank-Designer drei Indextypen erstellt werden: eindeutiger Index, Primärschlüsselindex und gruppierter Index.

Eindeutiger Index

Ein eindeutiger Index lässt nicht zu, dass zwei Zeilen denselben Indexwert haben.
Die meisten Datenbanken erlauben es nicht, einen neu erstellten eindeutigen Index mit einer Tabelle zu speichern, wenn in den vorhandenen Daten doppelte Schlüsselwerte vorhanden sind. Außerdem verhindert die Datenbank ggf. das Hinzufügen neuer Daten, da sonst doppelte Schlüsselwerte in der Tabelle entstehen würden. Wenn beispielsweise ein eindeutiger Index für den Nachnamen (lname) eines Mitarbeiters in der Mitarbeitertabelle erstellt wird, können keine zwei Mitarbeiter denselben Nachnamen haben. Primärschlüsselindex Eine Datenbanktabelle verfügt häufig über eine Spalte oder eine Spaltenkombination, deren Wert jede Zeile in der Tabelle eindeutig identifiziert. Diese Spalte wird als Primärschlüssel der Tabelle bezeichnet. Durch die Definition eines Primärschlüssels für eine Tabelle in einem Datenbankdiagramm wird automatisch ein Primärschlüsselindex erstellt, bei dem es sich um einen bestimmten Typ eines eindeutigen Indexes handelt. Dieser Index erfordert, dass jeder Wert im Primärschlüssel eindeutig ist. Es ermöglicht außerdem einen schnellen Zugriff auf Daten, wenn der Primärschlüsselindex in Abfragen verwendet wird. Clustered-Index: In einem Clustered-Index entspricht die physische Reihenfolge der Zeilen in der Tabelle der logischen (Index-)Reihenfolge der Schlüsselwerte. Eine Tabelle kann nur einen gruppierten Index enthalten.
Wenn ein Index kein gruppierter Index ist, stimmt die physische Reihenfolge der Zeilen in der Tabelle nicht mit der logischen Reihenfolge der Schlüsselwerte überein. Clustered-Indizes bieten im Allgemeinen einen schnelleren Zugriff auf Daten als nicht gruppierte Indizes.

Lokalitätsprinzip und Disk-Pre-Reading

Aufgrund der Eigenschaften von Speichermedien ist der Festplattenzugriff selbst viel langsamer als der Hauptspeicher. Zusätzlich zum mechanischen Bewegungsverbrauch beträgt die Festplattenzugriffsgeschwindigkeit oft einige Hundertstel der Hauptspeichergeschwindigkeit. Um die Effizienz zu verbessern, sollte daher der Festplatten-E/A minimiert werden. Um dieses Ziel zu erreichen, wird die Platte oft nicht strikt bei Bedarf ausgelesen, sondern jedes Mal vorab. Selbst wenn nur ein Byte benötigt wird, startet die Platte an dieser Stelle und liest eine bestimmte Datenlänge sequenziell rückwärts in den Speicher ein. Die theoretische Grundlage hierfür ist das berühmte Lokalitätsprinzip der Informatik: Wenn ein Datenelement verwendet wird, werden in der Regel sofort auch die nahegelegenen Daten verwendet. Die während der Programmausführung benötigten Daten werden üblicherweise konzentriert.
Da sequentielles Lesen auf der Festplatte sehr effizient ist (es ist keine Suchzeit und nur eine geringe Rotationszeit erforderlich), kann das Vorlesen die E/A-Effizienz für Programme mit Lokalität verbessern.
Die Länge des Vorlesens beträgt im Allgemeinen ein ganzzahliges Vielfaches einer Seite. Eine Seite ist ein logischer Block des Computerverwaltungsspeichers. Hardware und Betriebssysteme unterteilen Hauptspeicher und Festplattenspeicherbereiche häufig in zusammenhängende Blöcke gleicher Größe. Jeder Speicherblock wird als Seite bezeichnet (in vielen Betriebssystemen beträgt die Seitengröße normalerweise 4 KB). Hauptspeicher und Festplatte tauschen Daten in Seiteneinheiten aus. Wenn die Daten, die das Programm lesen möchte, nicht im Hauptspeicher sind, wird eine Seitenfehlerausnahme ausgelöst. Zu diesem Zeitpunkt sendet das System ein Lesesignal an die Festplatte. Die Festplatte findet die Startposition der Daten und liest kontinuierlich eine oder mehrere Seiten und lädt sie in den Speicher. Dann wird die Ausnahme zurückgegeben und das Programm wird weiter ausgeführt.

Leistungsanalyse von B-/+Tree-Indizes

Jetzt können wir endlich die Leistung des B-/+Tree-Index analysieren.
Wie oben erwähnt, wird die Anzahl der Disk-I/Os im Allgemeinen zur Beurteilung der Qualität von Indexstrukturen verwendet. Beginnen wir mit der B-Tree-Analyse. Gemäß der Definition eines B-Trees erfordert eine Suche den Zugriff auf höchstens h Knoten. Die Entwickler des Datenbanksystems haben das Prinzip des Disk-Pre-Reading geschickt ausgenutzt, um die Größe eines Knotens gleich einer Seite festzulegen, sodass jeder Knoten mit nur einem I/O vollständig geladen werden kann. Um dieses Ziel zu erreichen, sind bei der tatsächlichen Implementierung von B-Tree die folgenden Techniken erforderlich:
Bei jeder Erstellung eines neuen Knotens wird direkt eine Seite mit Speicherplatz angefordert. Dadurch wird sichergestellt, dass ein Knoten physisch auf einer Seite gespeichert wird. Darüber hinaus wird die Speicherzuweisung des Computers seitenweise ausgerichtet, sodass ein Knoten nur einen I/O benötigt.
Eine Suche in einem B-Baum erfordert höchstens h-1 I/Os (der Stammknoten befindet sich im Speicher) und die asymptotische Komplexität beträgt O(h)=O(logdN). In allgemeinen praktischen Anwendungen ist der Ausgangsgrad d eine sehr große Zahl, die normalerweise über 100 liegt, sodass h sehr klein ist (normalerweise nicht größer als 3).
Bei einer Rot-Schwarz-Baumstruktur ist h offensichtlich viel tiefer. Da logisch nahe Knoten (übergeordneter und untergeordneter Knoten) physisch weit entfernt sein können und die Lokalität nicht nutzen können, beträgt die asymptotische E/A-Komplexität des Rot-Schwarz-Baums ebenfalls O(h), was erheblich weniger effizient ist als der B-Baum.

Zusammenfassend ist die Verwendung von B-Tree als Indexstruktur sehr effizient.

Sie sollten sich die Zeit nehmen, sich mit B-Baum- und B+-Baum-Datenstrukturen vertraut zu machen.

1) B-Baum

Jeder Knoten in einem B-Baum enthält einen Schlüsselwert und einen Zeiger auf die Adresse des Datenobjekts, das dem Schlüsselwert entspricht. Für eine erfolgreiche Suche nach einem Objekt ist es daher nicht erforderlich, den Blattknoten des Baums zu erreichen.
Eine erfolgreiche Suche umfasst die Suche innerhalb eines Knotens und die Suche entlang eines Pfads. Die für eine erfolgreiche Suche erforderliche Zeit hängt von der Ebene ab, auf der sich der Schlüssel befindet, und von der Anzahl der Schlüssel innerhalb des Knotens.
So suchen Sie in einem B-Baum nach einem bestimmten Schlüsselwort: Nehmen Sie zuerst den Stammknoten und suchen Sie in den im Stammknoten enthaltenen Schlüsselwörtern K1, …, kj nach dem angegebenen Schlüsselwort (es kann eine sequentielle oder binäre Suche verwendet werden). Wenn ein Schlüsselwort gefunden wird, das dem angegebenen Wert entspricht, ist die Suche erfolgreich. Andernfalls kann festgestellt werden, dass das zu suchende Schlüsselwort zwischen einem bestimmten Ki oder Ki + 1 liegt. Nehmen Sie dann den Indexknotenblock der nächsten Ebene, auf den Pi zeigt, und setzen Sie die Suche fort, bis er gefunden wird. Andernfalls schlägt die Suche fehl, wenn der Zeiger Pi leer ist.

2) B+ Baum

Der im Nicht-Blattknoten des B+-Baums gespeicherte Schlüsselcode gibt nicht den Adresszeiger des Datenobjekts an. Der Nicht-Blattknoten ist nur der Indexteil. Alle Blattknoten befinden sich auf derselben Ebene und enthalten alle Schlüsselcodes und Speicheradresszeiger der entsprechenden Datenobjekte. Die Blattknoten sind entsprechend dem Schlüsselcode in aufsteigender Reihenfolge verknüpft. Wenn die eigentlichen Datenobjekte in der Reihenfolge gespeichert werden, in der sie hinzugefügt werden, und nicht nach Schlüsselnummer, muss der Blattknotenindex ein dichter Index sein. Wenn die eigentlichen Daten in Schlüsselreihenfolge gespeichert werden, ist der Blattknotenindex ein spärlicher Index.
Der B+-Baum hat zwei Kopfzeiger, einer ist der Wurzelknoten des Baums und der andere ist der Blattknoten mit dem minimalen Schlüsselcode.
Es gibt also zwei Suchmethoden für B+-Bäume:
Eine Möglichkeit besteht darin, in der Reihenfolge der verknüpften Liste zu suchen, die vom Blattknoten selbst aufgerufen wird.
Eine Möglichkeit besteht darin, die Suche vom Stammknoten aus zu starten, was dem B-Baum ähnlich ist. Wenn jedoch der Schlüsselcode eines Nicht-Blattknotens einem bestimmten Wert entspricht, wird die Suche nicht beendet, sondern entlang des rechten Zeigers fortgesetzt, bis der Schlüsselcode auf dem Blattknoten gefunden wird. Unabhängig davon, ob die Suche erfolgreich ist oder nicht, werden alle Ebenen des Baums durchlaufen.
In einem B+-Baum werden Datenobjekte nur in Blattknoten eingefügt und gelöscht.
Der Unterschied zwischen diesen beiden Datenstrukturen zur Handhabung von Indizes:
a. Derselbe Schlüsselwert kommt in einem B-Baum nicht mehrfach vor und kann in einem Blattknoten oder einem Nicht-Blattknoten vorkommen. Die Schlüssel eines B+-Baums müssen in Blattknoten erscheinen und können in Nicht-Blattknoten wiederholt werden, um das Gleichgewicht des B+-Baums aufrechtzuerhalten.
b. Da die Position des B-Baumschlüssels nicht festgelegt ist und er in der gesamten Baumstruktur nur einmal vorkommt, kann dadurch zwar Speicherplatz gespart werden, die Komplexität von Einfüge- und Löschvorgängen wird jedoch erheblich erhöht. B+-Baum ist ein besserer Kompromiss.
c. Die Abfrageeffizienz des B-Baums hängt von der Position des Schlüssels im Baum ab. Die maximale Zeitkomplexität ist dieselbe wie beim B+-Baum (am Blattknoten), und die minimale Zeitkomplexität beträgt 1 (am Wurzelknoten). Bei B+-Bäumen ist die Komplexität für einen bestimmten erstellten Baum festgelegt. Kann Zweierpotenzen scannen.

Die Kosten der Indizierung

Belegter Speicherplatz

Auswirkungen auf die Effizienz von DML-Anweisungen (Update, Delete, Insert)

Hinzufügungen, Löschungen und Änderungen wirken sich auf den Index aus, da der Index neu organisiert werden muss.

Zulässige Indextypen für die Speicher-Engine
myisam btree
innodb btree
Speicher/Yeap Hash, B-Baum

Welche Spalten eignen sich zum Hinzufügen von Indizes?

① Das als Abfragebedingung verwendete Abfragefeld sollte indiziert werden. ② Das Feld mit geringer Eindeutigkeit eignet sich nicht allein zum Erstellen eines Index, selbst wenn es häufig verwendet wird.

Wählen Sie * aus emp, wobei Geschlecht = "männlich" ist

③Aktualisieren Sie Felder häufig und definieren Sie keine Indizes.
④ Erstellen Sie keine Indizes für Felder, die nicht in der Where-Anweisung erscheinen

Zusammenfassung: Indizes sollten nur für Felder erstellt werden, die die folgenden Bedingungen erfüllen:

① Es muss häufig in der Where-Bedingung verwendet werden. ② Der Inhalt des Felds besteht nicht aus wenigen eindeutigen Werten. ③ Der Feldinhalt ändert sich nicht häufig.

Hinweise zur Indizierung

Erstellen einer Tabelle

Abteilungsdaten hinzufügen

Erstellen Sie das VERFAHREN insert_dept(in start int(10),in max_num int(10))
BEGINNEN
 deklariere i int DEFAULT 0;
 setze Autocommit=0;
 WIEDERHOLEN
 setze i=i+1;
 in Abteilungswerte einfügen ((start+i),rand_string(10),rand_string(8));
 BIS i = max_num
 Ende WIEDERHOLEN;
 begehen;
ENDE
Führen Sie den Aufruf insert_dept(100,10) aus.

Erstellen eines Primärschlüsselindexes

Tabelle ändern, Tabellenname, Primärschlüssel hinzufügen (Spaltenname);

Erstellen eines gemeinsamen Indexes

alter table dept add index my_ind (dname,loc); // dname ist die Spalte links, loc ist die Spalte rechts

Beachten:

1. Wenn für einen erstellten mehrspaltigen Index der erste Teil nicht verwendet wird, wird der Index nicht erstellt.
Erklären Sie „select * from dept where loc='aaa'\G“
Der Index wird nicht verwendet
2. Die Fuzzy-Suche schlägt fehl, wenn vor „like“ ein Prozentzeichen steht.
3. Wenn die Bedingung ein „oder“ enthält, wird es nicht verwendet, auch wenn die Bedingung einen Index hat. Mit anderen Worten: Alle Felder, die verwendet werden müssen, müssen indexiert werden. Wir empfehlen, die Verwendung des Schlüsselworts „oder“ möglichst zu vermeiden.
4. Wenn der Spaltentyp eine Zeichenfolge ist, müssen Sie die Daten in der Bedingung in Anführungszeichen setzen. Andernfalls wird der Index nicht verwendet. (Beim Hinzufügen muss die Zeichenfolge „“ sein), das heißt, wenn die Spalte vom Typ Zeichenfolge ist, muss sie in „“ eingeschlossen sein.
5. Wenn MySQL schätzt, dass ein vollständiger Tabellenscan schneller ist als ein Index, wird der Index nicht verwendet.

Abfrage der Nutzungsrate

Status wie „handler_read%“ anzeigen;

Darauf kann jeder achten:

handler_read_key: Je höher der Wert, desto besser. Ein höherer Wert gibt an, wie oft der Index für Abfragen verwendet wird.
handler_read_rnd_next: Je höher dieser Wert ist, desto weniger effizient ist die Abfrage.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „Zusammenfassung der Kenntnisse im Bereich MySQL-Indexoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Allgemeinfunktionen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Protokolloperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Transaktionsoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-gespeicherte Prozeduren“ und „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Datenbanksperren“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.