Hochleistungs-MySQL – ausführliche Erklärung zum Erstellen von Hochleistungsindizes (Bild und Text)

In diesem Artikel geht es um die Erstellung eines Index:
(1) Indextyp
(2) Vorteile des Index
(3) Strategien zur Optimierung von Indizes

Hier ist eine Mindmap der Indizierung:

Index ist eine Methode, die von Speicher-Engines verwendet wird, um die Datenstruktur von Datensätzen schnell zu finden . Indizes sind das effektivste Mittel zur Optimierung der Abfrageleistung. Indizes können die Abfrageleistung problemlos um mehrere Größenordnungen verbessern. Indizierung Im Allgemeinen fügen wir einer bestimmten Spalte einen Index hinzu.

Die Speicher-Engine findet zuerst den entsprechenden Wert im Index und dann die entsprechende Datenzeile basierend auf der Zeilen-ID im passenden Indexdatensatz. Führen Sie beispielsweise die folgende Abfrageanweisung aus:

SELECT first_name from actor where actor_id=5;

Wenn es einen Index für die Spalte „actor_id“ gibt, verwendet MySQL den Index, um die Zeile zu finden, die „actor_id“ 5 entspricht. Das heißt Das heißt, MySQL sucht zuerst nach einem Wert in einem Index und gibt alle Zeilen zurück, die diesen Wert enthalten.

Der Index kann die Werte einer oder mehrerer Spalten enthalten. Wenn der Index mehrere Spalten enthält, ist auch die Reihenfolge der Spalten sehr wichtig, da MySQL nur die Präfixspalte ganz links effizient verwenden kann des Indexes. Es gibt einen großen Unterschied zwischen der Erstellung eines Index, der zwei Spalten enthält, und der Erstellung von zwei Indizes, die eine Spalte enthalten.

1. Arten von Indexdatenstrukturen:

Die häufigsten Indizes sind B-Tree-Indizes und Hash-Indizes.

(1) B-Tree-Baumindex

Im Allgemeinen beziehen sich Indizes auf B-Tree-Indizes, die die B-Tree-Datenstruktur zum Speichern von Daten verwenden. Tatsächlich wird es basierend auf B+Tree implementiert. Jeder Blattknoten enthält einen Zeiger auf den nächsten Blattknoten.

B-Baum bedeutet, dass alle Werte in der Reihenfolge gespeichert werden. Beispielsweise werden sie für das Name-Attribut in der Reihenfolge von a-z gespeichert. Nach der Verwendung des B-Tree-Index muss die Speicher-Engine keinen vollständigen Tabellenscan mehr durchführen, um die erforderlichen Daten zu erhalten. Das Endergebnis ist, dass entweder der entsprechende Wert gefunden wird Der Datensatz existiert nicht. Dies ermöglicht einen schnelleren Zugriff auf Daten.

B-Tree organisiert und speichert Indexspalten nacheinander und eignet sich daher sehr gut für die Suche nach Bereichsdaten. (Zum Beispiel ist die Suche nach Namen, die mit I-k beginnen, sehr effizient)

B-Tree-Index geeigneter Abfragetyp
(1) Vollständige Wertübereinstimmung: und im index Alle Spalten werden abgeglichen.

(2) Passen Sie das Präfix ganz links an: Bei einem Index mit mehreren Spalten wird nur die erste Spalte des Index verwendet.

(3) Spaltenpräfix abgleichen: Übereinstimmung mit dem Anfang des Werts einer bestimmten Spalte. (Beim Abgleichen des Namensfelds werden beispielsweise nur Namen abgeglichen, die mit J beginnen.) Hier wird nur die erste Spalte des Index verwendet.

(4) Übereinstimmungsbereichswert: Entspricht Datensätzen, deren Felder in einen bestimmten Bereich fallen. Hier wird nur die erste Spalte des Index verwendet.

(5) Passen Sie genau mit einer bestimmten Spalte und dem Bereich mit einer anderen Spalte überein: Für den Fall, dass ein Index beispielsweise mehrere Felder enthält, stimmen Sie genau mit der ersten Spalte überein und stimmen Sie mit dem Bereich der zweiten Spalte überein.

(6) Abfrage, die nur auf den Index zugreift: Sie greift auf die Indexzeilen zu, ohne auf die Datenzeilen anderer Felder im Datensatz zuzugreifen.

Der obige Bereichsabgleich ist hauptsächlich darauf zurückzuführen, dass der Index die Indexspalten der Reihe nach speichert, was zu einer hohen Effizienz des Bereichsabgleichs führt.

Es gibt auch einige Einschränkungen für B-Tree-Indizes:
(1) Der Index kann nur in der Spalte ganz links suchen

(2) Wenn eine Bereichssuche nach einem bestimmten Wert erfolgt Spalte in der Abfrage, dann können alle Spalten rechts davon nicht die Indexoptimierung verwenden.

Angesichts der beiden oben genannten Einschränkungen sollten Sie verstehen, dass die Reihenfolge der Indexspalten sehr wichtig ist, wenn der Index mehrere Spalten enthält.

(2) Hash-Hash-Index

Der Hash-Index wird basierend auf der Hash-Tabelle implementiert. Nur Abfragen, die genau mit allen Spalten des Index übereinstimmen, sind gültig. Für jede Datenzeile berechnet die Datenspeicher-Engine einen Hash-Code für alle Indexspalten. Der Hash-Code ist kleiner und die für Zeilen mit unterschiedlichen Schlüsselwerten berechneten Hash-Codes sind ebenfalls unterschiedlich.

1) Der Hash-Index speichert nur Hash-Werte und Zeilenzeiger und keine spezifischen Feldwerte, daher muss ein Prozess zum Lesen von Zeilen vorhanden sein.

2) Der Hash-Index wird nicht in der Reihenfolge des Indexwerts gespeichert und kann daher nicht zum Sortieren verwendet werden.

3) Der Hash-Index unterstützt nur Gleichheitsvergleichsabfragen und keine Bereichsvergleichsabfragen. Dies hängt mit den Eigenschaften der Hash-Tabelle zusammen.

4) Bei Hash-Indizes besteht das Problem von Hash-Konflikten. Bei Hash-Konfliktdaten müssen alle Zeilenzeiger in der verknüpften Liste durchlaufen werden.

Aufgrund der oben genannten Einschränkungen sind Hash-Indizes nur für bestimmte Anlässe geeignet. Sobald sie jedoch für Hash-Indizes geeignet sind, ist die Leistung besonders hoch.

Wenn Sie einen Hash-Index verwenden, müssen Sie normalerweise den Wert vor dem Hash in den Abfragebedingungen hinzufügen, wie zum Beispiel:

mysql>select * from words where crc=crc32(‘gnu’) and word=’gnu’;

这里crc字段就是word字段哈希之后的值，因为hash之后可能存在冲突，带上原本的值做上二次比较，就可以精确定位。

2.索引的优点：

索引可以让服务器快速定位到表的指定位置。但是这不是唯一的作用，比如：
（1）对于B-Tree索引，由于B-Tree是按照顺序存储数据的，所以用来做order by 操作或则是 group by操作的效率很高。

（2）因为索引中存储了实际的列值，所以某些查询只需要索引就可以完成全部查询。

总结来说就是3点：
（1）索引大大减少服务器需要扫描的数据量；

（2）索引可以帮助服务器避免排序和临时表；

（3）索引可以将随机IO变为排序IO。

3.高性能的索引策略

先概括一下索引的策略：
 1）单列索引
 2）多列索引
 3）前缀索引
 4）聚簇索引
 5）覆盖索引

单列索引

所谓单列索引是指：使用数据表字段中的某一列作为索引。但是索引列不能是表达式的一部分，也不能是函数的参数。

比如对于下面的一个例子：

select actor_id from actor where actor_id+1=5;

对于这样的一个SQL，where语句后面 是一个表达式，其实很明显是actor_id=4的条件，但是MySQL却无法解析，索引无法正却使用索引。

还有一种是函数参数：也是无法正常的使用索引的

select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

多列索引以及选择合适的索引顺序

注意这里要区分：为每个列创建独立的索引和为多个列创建一个索引的区别。

比如下面这种情况：

CREATE TABLE t{
c1 int,
c2 int,
c3 int,key(c1),key(c2),key(c3)
}

这一种就是为表中的3个列都创建了索引。

但是多个列创建索引就是：创建了一个索引，包含customer_id,和staff_id

alter table payment add KEY(customer_id, staff_id);

上面这个索引其实是包含了两个索引，一个是customer_id这个索引，还有一个是(customer_id，staff_id)。注意staff_id并不能作为单独的索引使用。

---

对于多列索引，最重要的就是怎么选择索引列的顺序，其实这一点与实际的查询需求有关。主要是为了满足排序和分组。

先从数据结构层次来分析，我们知道索引是以B-Tree的形式存储的，在一个多列索引列中，索引的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以对于一个多列索引，如果以第二列或则第三列直接作为索引，基本是没有用到索引。由于索引的有序性很好的满足了order by、group by和distinct等子句的查询需求。

从上面的分析我们就能认识到多列索引中列的顺序是多么重要。关于多列索引中有一点经验法则：
（1）在不需要考虑排序和分组时，通常情况下将选择性最高的列放在索引最前列。(这时候索引只需要优化where查询条件，能够很快过滤出需要的行)

索引列的选择性定义：不重复的索引值和数据表的记录总数的比值。索引的选择性越高也就是查询效率越高。比如对于人员信息表，phone这一字段的选择性是很高的，几乎为1，但是对于sex性别这一字段的选择性是非常低的，因为只有两个选择男或则是女，几乎为0。

（2）实际情况下也与数据的分布有很大关系。
以下面的查询为例：

SELECT * FROM item WHERE staff_id=2 AND customer_id=584;

这时候应该创建（staff_id, customer_id）的索引还是应该创建（customer_id，staff_id）的索引呢？这时候就应该确认一下那个字段的选择性更高，先查询一下staff_id和customer_id的总数，哪个小就将哪个放在前面。

前缀索引

前缀索引：有时候需要索引的列可能会很长，这时候会导致索引大而且很慢，我们可以只索引列开始的部分（也就是只索引某一列的前面几个字符），这样可以大大节省索引空间也能加快索引的速度，但是也会降低索引的选择性（也就是索引查出来的结果会变多）。

使用的技巧在于：选择足够长的前缀保证较高的选择性，同时又不能太长，避免占用太多的存储空间。

Clustered-Index

Clustered-Index ist kein separater Indextyp, sondern eine Datenspeichermethode. Hier verwenden wir hauptsächlich InnoDB als Beispiel, um den Clustered-Index zu veranschaulichen.

Der Clustered-Index in InnoDB speichert tatsächlich den B-Tree-Index und die Datenzeilen in derselben Struktur. Wenn eine Tabelle einen Clustered-Index enthält, werden seine Datenzeilen tatsächlich in den Blattseiten des Index gespeichert. Die Bedeutung von Clustering besteht eigentlich darin, dass Datenzeilen und Schlüsselwerte im benachbarten B-Baum kompakt zusammen gespeichert werden. Datenzeilen können nur an einem Ort gespeichert werden, daher kann es nur einen Clustered-Index geben.

Das Folgende ist ein Beispieldiagramm zur Veranschaulichung: Die Indexspalte ist ein ganzzahliger Wert, die Blattseite enthält alle Daten der Zeile, aber die Knotenseite enthält nur die Indexspalte (den ganzzahligen Wert in der Abbildung). unten).

In der aktuellen Version von MySQL unterstützt der Clustered-Index von InnoDB nur die Verwendung von Primärschlüsseln zum Clustern von Daten. Wenn kein Primärschlüssel definiert ist, wählt InnoDB stattdessen einen eindeutigen, nicht leeren Index.

Vorteile geclusterter Daten:
(1) Zusammengehörige Daten können zusammen gespeichert werden. Bei der Abfrage von E-Mail-Adressen wird beispielsweise die Benutzer-ID als Primärschlüssel verwendet und die Daten nach Benutzer-ID geclustert. Auf diese Weise können alle E-Mails eines Benutzers abgerufen werden, indem nur eine kleine Anzahl von Datenseiten von der Festplatte gelesen wird.

(2) Der Datenzugriff ist schneller. Ein Clustered-Index speichert den Index und die Daten in einem B-Baum, sodass das Abrufen von Daten aus einem Clustered-Index normalerweise schneller ist als das Nachschlagen desselben Indexes. (Natürlich gibt es Fälle, in denen die Suchspalte die Indexspalte ist)

(3) Abfragen mit abdeckenden Indexscans können den Primärschlüssel im Seitenknoten direkt verwenden.

Die oben genannten Vorteile können die Leistung beim Abfragen und Entwerfen von Tabellen erheblich verbessern, es gibt jedoch auch einige Nachteile:
(1) Geclusterte Daten verbessern die Leistung IO-intensiver Anwendungen erheblich, aber alle Daten werden platziert Speicher ist die Zugriffsreihenfolge nicht wichtig und der Clustered-Index verliert seinen Vorteil.

(2) Die Einfügungsgeschwindigkeit hängt stark von der Einfügungsreihenfolge ab.

(3) Das Aktualisieren von Clustered-Index-Spalten ist sehr kostspielig und erfordert, dass jede aktualisierte Zeile von InnoDB an einen neuen Speicherort verschoben wird.

Covering-Index

Wenn ein Index die Werte aller Felder enthält (oder abdeckt), die abgefragt werden müssen, nennen wir ihn einen Covering-Index Index.

Covering Index ist ein sehr nützliches Werkzeug. Für Indizes müssen Sie nur den Index scannen, um alle Daten in den Blattknoten des Index zu erhalten, ohne dass eine Rückabfrage an die Tabelle erforderlich ist. was die Leistung erheblich verbessern kann. Es gibt auch viele Vorteile:

(1) Indexeinträge sind normalerweise viel kleiner als die Größe der Datenzeile. Wenn Sie nur den Index lesen müssen, reduziert MySQL den Umfang des Datenzugriffs erheblich Den Cache stark belasten. Sehr wichtig.

(2) Da der Index in der Reihenfolge der Spaltenwerte gespeichert wird, erfordern IO-intensive Bereichssuchen viel weniger Io als das zufällige Lesen jeder Datenzeile von der Festplatte.

Verwendung Index-Scan zum Sortieren

MySQL bietet zwei Möglichkeiten, geordnete Ergebnisse zu generieren:
(1) Durch Reihenfolge nach Sortiervorgang

( 2) Scannen in Indexreihenfolge; >Wenn der erklärte Typwert index ist, bedeutet dies, dass MySQL zum Sortieren den Index-Scan verwendet.

Der Index-Scan selbst ist sehr schnell. Sie müssen nur von einem Datensatz zum nächsten wechseln. Wenn die Indexspalte jedoch nicht alle Abfragefelder abdecken kann, müssen Sie dies bei jedem Scan tun Um zur Tabelle zurückzukehren und sie abzufragen, ist die Leistung nicht so gut wie beim direkten sequentiellen vollständigen Tabellenscan.

Entwerfen Sie so weit wie möglich denselben Index, um sowohl Sortierung als auch Suche zu erfüllen.

4. Nachteile des Index

(1) Für Einfüge-, Aktualisierungs- und Löschvorgänge muss der Index synchron aktualisiert werden, was zu einer langsamen Geschwindigkeit führt.

(2) Indizes beanspruchen viel Speicherplatz.

Das obige ist der detaillierte Inhalt vonHochleistungs-MySQL – ausführliche Erklärung zum Erstellen von Hochleistungsindizes (Bild und Text). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!