MySql-Index-Dinge

Heute werden wir über MySql-Indizes sprechen. In diesem Artikel werde ich hauptsächlich über die Datenstruktur von Indizes unter InnoDB sprechen, wie Indizes funktionieren und wie Indizes besser genutzt werden können, um die Effizienz zu verbessern.

1. Was ist ein Index

Ein Datenbankindex ist eine sortierte Datenstruktur in einem Datenbankverwaltungssystem, die bei der schnellen Abfrage und Aktualisierung von Daten in der Datenbank hilft Tische. Genau wie das Inhaltsverzeichnis des Xinhua-Wörterbuchs, das wir zuvor verwendet haben, kann es uns helfen, schnell nach einem bestimmten Wort zu suchen.

2. Klassifizierung von Indizes

tr> tbody>

Klassifizierungswinkel

Indexname

Datenstruktur td>

B+-Baum, Hash-Index, R-Baum usw.

Speicherebene

分类角度	索引名称
数据结构	B+树，Hash索引，R-Tree等
存储层面	聚簇索引，非聚簇索引
逻辑层面	主键索引，普通索引，复合索引，唯一索引，空间索引等

Clustered Index, Non-Clustered Index

Logische Ebene

Primärschlüsselindex, gewöhnlicher Index, zusammengesetzter Index, eindeutiger Index, räumlicher Index usw.

3. Analyse von Indexbeispielen (am Beispiel von InnoDB)

3.1 Die Struktur des Index unter InnoDB

Unter InnoDB werden Tabellen in Form von Indizes gemäß dem gespeichert Die Reihenfolge der Primärschlüssel wird auch als Clustered-Index bezeichnet. Dies bedeutet, dass die Datenzeilen und benachbarten Schlüsselwerte kompakt zusammen gespeichert werden, dh die Datenzeilen werden tatsächlich im Blatt gespeichert Seiten des Indexes. Wir erstellen eine Tabelle, um die Indexstruktur unter InnoDB tatsächlich zu veranschaulichen. Die Anweisung zur Tabellenerstellung lautet wie folgt:

create table person(id int primary key, 
age int not nullindex (age)engine=InnoDB;

Dann fügen wir fünf Datenelemente ein: (1,15), (2,17), (6 ,20 ), (10,18), (19,21), die Indexbaumstruktur ist wie folgt:

Das obige Bild zeigt zwei Teile des Inhalts, den Das erste Bild ist Poly. Aus dem Inhalt des Clusterindex (Primärschlüsselindex) können Sie ersehen, dass die Daten nach der Größe der ID sortiert sind und der entsprechende Index die gesamte Datenzeile für den Index enthält.

Das zweite Bild zeigt das Indexstrukturdiagramm unter Verwendung des Alters als Index, bei dem es sich um einen nicht gruppierten Index (Nicht-Primärschlüsselindex) handelt. Sie können sehen, dass der Index nach Alter sortiert ist, jedoch im Gegensatz zum Primärindex Schlüsselindex, Alter Der Index entspricht der ID, sodass wir wissen können, dass der Inhalt des Nicht-Primärschlüssel-Indexdatensatzes der Wert des Primärschlüsselindex ist.

Einige Schüler hier haben möglicherweise Fragen, wenn ich beim Erstellen der Tabelle keinen Primärschlüssel spezifiziere. Wie ist dann die Indexstruktur? Wenn in InnoDB der Primärschlüssel nicht definiert ist, wird stattdessen ein eindeutiger, nicht leerer Index ausgewählt. Wenn kein solcher Index vorhanden ist, wird implizit ein Primärschlüssel als Clustered-Index definiert. Unabhängig davon, ob Sie einen Primärschlüssel festlegen oder nicht, hilft Ihnen InnoDB weiterhin bei der Indizierung von Daten in der oben gezeigten Form. Als nächstes analysieren wir den Prozess der Indexabfrage.

3.2 Indexabfrageanalyse

Angenommen, wir führen eine Abfrageanweisung „select * from person“ mit ID = 6 aus. Da die Primärschlüssel-ID-Abfrage direkt verwendet wird, wird der Primärschlüsselindex verwendet der Primärschlüsselindex Alle Daten in der gesamten Zeile stehen in direktem Zusammenhang, sodass die Engine sie nur einmal ausführen muss, um die Ergebnisse abzufragen.

Wenn die ausgeführte SQL-Anweisung ein Nicht-Primärschlüsselindex ist

select * from person where age = 18

Der Prozess, den Primärschlüsselindex aus dem gewöhnlichen Index zu finden und dann die Daten abzufragen, wird als Tabellenrückgabe bezeichnet. Da die Rückgabe der Tabelle eine weitere Abfrage erfordert, sind Primärschlüsselindizes schneller als gewöhnliche Indizes. Daher sollten wir versuchen, so oft wie möglich Primärschlüsselabfragen zu verwenden. Die obige Anweisung verwendet den gewöhnlichen Altersindex. Der Index sucht zunächst nach Indexdatensätzen, die dem Alter entsprechen, findet den Datensatz mit der ID = 10, durchsucht dann den Primärschlüsselindex einmal und nimmt ihn dann heraus Daten, die abgefragt werden müssen.

3.3 Index abdecken

Wir erstellen normalerweise Indizes basierend auf der Where-Bedingung der Abfrage, aber dies ist nur unsere übliche Praxis. Basierend auf der obigen Analyse können wir das wissen Wir wollen Wenn Sie eine hohe Abfrageeffizienz erzielen möchten, verwenden Sie erstens den Primärschlüsselindex und zweitens vermeiden Sie die Rückgabe der Tabelle, dh Sie können so viele gewünschte Daten wie möglich im Index erhalten. Wenn ein Index die Felder enthält, die abgefragt werden müssen, dann nennen wir ihn einen „Covering Index“.

Wie erstellt man also einen umfassenden Index? Die Antwort besteht darin, dies durch einen gemeinsamen Index zu erreichen. Die abzufragenden Felder werden durch die Felder des gemeinsamen Index abgedeckt, um den Effekt der Indexabdeckung zu erzielen.

我们把上面的建表语句改造下，来分析下如何实现覆盖索引。

 CREATE TABLE `person` (  `id` int(11) NOT NULL,  `age` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,  `sex` varchar(1) DEFAULT NULL,

上面我创建了一个name和age的联合索引，索引结构图表示如下：

我们根据图可以知道，联合索引是和创建索引字段顺序有关的，上面这个例子就是先以name排序，然后name相同再以age为标准排序。那么我们建表后该如何达到覆盖索引的效果呢？相信有些同学已经知道了怎么写sql可以达到覆盖索引效果，sql如下：

select name,age from person where name = "Barry"

因为我们需要查询的字段name和age，都在索引中可以直接查询到了，所以不需要查找到主键ID，然后再回表了。

看到这里，肯定有同学会说，既然这样的话，我把所有需要查询的字段组合都建上联合索引不就行了吗？答案是：不行。因为索引也是需要消耗空间的，而且维护索引也是需要成本的，这一点我会在后面的优缺点中提到。那么有没有别的方式可以尽可能的实现不回表的效果呢？这里我们就要引入MySql的最左前缀原则了。

什么叫最左前缀原则呢？就是在索引的匹配中，可以以索引的最左N个字段,也可以是字符串索引的最左N个字符。比如在上图中，要查询以A开头的名字，查询语句就是

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select name from person where name like &#39;A%&#39;</span><br/>

这个时候就可以满足最左前缀规则来使用索引查询了，这里就会依赖索引查询到第一个首字母是A的名字，然后向后遍历，直到不满足条件为止。

那么最左N个字段是什么意思呢？意思就是索引（name,age),可以直接利用 name来当做单独索引使用，可以只使用联合索引的部分字段，但是必须是顺序一致，比如索引（a,b,c)，如果要想使用最左前缀规则，可以使用索引a,ab。

我们也可以利用该规则来少维护一个或多个索引，比如我们需要 a,ab,abc的查询，那就只需要(a,b,c)联合索引就满足要求了。

3.4 索引下推

在MySql 5.6版本中引入了一个新特性，叫做“索引条件推送（index condition pushdown)”，这也称为索引下推。那么索引下推是这个什么东东呢？其实从“索引条件推送”这个名字就可以表明，这个特性是可以在索引中的字段进行条件判断，然后过滤不满足条件的记录，减少回表的次数。

比如以上图中的数据为准，sql如下：

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select * from person where name like &#39;A%&#39; and age =19;</span><br/>

那么如果没有索引下推的情况下，首先会根据索引查询出名字以A开头的所有记录，然后查询出ID，然后回表去查询对应的ID记录，最后再判断age=19，返回满足条件的语句。因为满足A开头的记录有2条，所以这种情况下，会回表2次。

在索引下推情况下，InnoDB会在索引内部直接判断age=19是否满足条件，过滤掉不满足条件的记录，所以只返回了一条，也就是只需要回表一次。从而提高了性能。

3.5 索引的优点与缺点

说了这么多关于索引的内容，我们来谈谈索引的优缺点。

优点：

减少服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表索引可以将随机IO变为顺序IO

缺点

索引会占用额外的存储空间索引的维护需要一定的成本，插入数据后需要保证原来的索引有序，所以也会影响一定的数据库性能。

五、总结

In diesem Blogbeitrag habe ich hauptsächlich über die Definition von Indizes, die Klassifizierung von Indizes und die gängigen Indextypen aus verschiedenen Perspektiven gesprochen. Dann habe ich mich auf die Datenstruktur des unter InnoDB indizierten Index konzentriert. Der Unterschied zwischen Primärschlüsselindex und Nicht-Primärschlüsselindex besteht darin, dass beim Abfragen des Primärschlüsselindex zuerst die Primärschlüssel-ID abgefragt und dann die Daten abgefragt werden müssen . Wir können die Anzahl der Tabellenrückgaben reduzieren, indem wir Indizes abdecken und so die Leistung verbessern. Nach mysql5.6 kann InnoDB Index-Pushdown unterstützen. Wenn die Bedingungen im Index beurteilt werden können, werden Zeilen, die die Bedingungen nicht erfüllen, im Index gefiltert, wodurch die Anzahl der Tabellenrückgaben verringert wird.

6. Referenz

„High Performance MySql“ 3. Auflage

Spalte „MySql45 Lecture“

【 Empfohlen Kurse: MySQL Video Tutorial]

Das obige ist der detaillierte Inhalt vonMySql-Index-Dinge. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!