Vertieftes Verständnis von Indizes in MySQL (Verwendung, Klassifizierung, Matching-Methoden)

Dieser Artikel vermittelt Ihnen ein tiefgreifendes Verständnis des Indexes in MySQL und stellt die Vorteile, Verwendungen, Klassifizierungen, Fachbegriffe und Matching-Methoden des Indexes vor. Ich hoffe, er wird Ihnen hilfreich sein!

Für die fortgeschrittene Entwicklung müssen wir häufig komplexes SQL schreiben. Um das Schreiben ineffizienten SQLs zu verhindern, müssen wir daher einige Grundkenntnisse der Indizierung verstehen. Durch dieses Grundwissen können wir effizienteres SQL schreiben. [Verwandte Empfehlungen: MySQL-Video-Tutorial]

01 Vorteile von Indizes

• Reduzieren Sie die Datenmenge, die der Server scannen muss, erheblich, dh die Menge an E/A.
• Helfen Sie dem Server, dies zu vermeiden Sortierung und temporäre Tabellen (versuchen Sie, die Sortierung von Dateien zu vermeiden, verwenden Sie stattdessen die Indexsortierung)
• Verwandeln Sie zufällige E/A in sequentielle E/A

02 Der Nutzen von Indizes

• Schnelles Finden von Zeilen, die einer Where-Klausel entsprechen
• If Sie können zwischen mehreren Indizes wählen. MySQL verwendet normalerweise den Index, der die wenigsten Zeilen findet
• Wenn die Tabelle einen mehrspaltigen Index hat, kann der Optimierer jedes Präfix ganz links im Index verwenden, um die Zeile zu finden
• Wenn dort Bei Tabellenverknüpfungen werden Zeilendaten aus anderen Tabellen abgerufen
• Finden Sie die Werte von Min. und Max. einer bestimmten Indexspalte
• Sortieren und gruppieren Sie die Tabelle, wenn die Sortierung oder Gruppierung mit dem Präfix ganz links im Index erfolgen kann
• In einigen Fällen kann die Abfrage optimiert werden, um Datenwerte abzurufen, ohne Datenzeilen zu suchen

03 Klassifizierung von Indizes

Der von der Datenbank erstellte Standardindex ist für eindeutige Schlüssel gedacht

• Primärschlüsselindex (einzigartig und nicht leer)
• Einzigartiger Index (einzigartig und kann leer sein)
• Gewöhnlicher Index (Index gewöhnlicher Felder)
• Volltextindex (im Allgemeinen auf Varchar, Char und Texttypen aufgebaut, aber selten verwendet)
• Kombinierter Index (auf mehreren Wörtern aufgebauter Index)

04 indiziert Fachbegriff

1. Tabellenrückgabe

Das Namensfeld ist ein gewöhnlicher Index. Suchen Sie den Primärschlüssel aus dem B+-Baum der Namensspalte und suchen Sie dann die endgültigen Daten aus dem B+-Baum des Primärschlüssels. Dies ist die Tabellenrückgabe. (Die Blattknoten des Primärschlüsselindex speichern alle Daten der Spalte, aber im Allgemeinen speichern alle Blattknoten die entsprechende Primärschlüssel-ID.)

Wie in der Abbildung gezeigt: Die Indexstruktur SQL wird anhand des Namens in einer Verwendungstabelle erstellt select * from use where name='sun' Zuerst wird die Primärschlüssel-ID=2, die Sonne entspricht, über den Nicht-Primärschlüssel-Indexnamen gefunden, und dann werden die gesamten Zeilendaten darin gefunden der Primärschlüsselindex bis id=2 und zurückgegeben wird. Dies ist die Rückgabeoberfläche. select * from use where name='sun'首先会通过name这个非主键索引找到sun对应的主键Id=2,然后通过id=2在主键索引中找到整个行数据，并返回，这个就是回表。

2. 覆盖索引

在非主键索引上可以查询到所需要的字段，不需要回表再次查询就叫覆盖索引。

如上图name索引，sql是 select id,name from user where name ="1" ，id的值在第一步非主键索引就已经有了，就不需要根据ID到主键索引中查询行数据了。

3. 最左匹配

组合索引中 先匹配左边，再继续向后匹配；比如user表中有name+age组成的联合索引，select * from user where name="纪先生" and age = 18 就符合最左匹配，可以用的索引。而select * from user where age = 18就不符合，用不到这个索引。

扩展；

如果是下面两个sql怎么建立索引

select * from user where name="纪先生" and age = 18;
select * from user where age = 18;

由于最左匹配原则：只需要建立一个组合索引age+name即可

如果是下面三个sql呢

select * from user where name="纪先生" and age = 18;
select * from user where name= "纪先生";

建立name+age和age索引，或者建立age+name和name索引,看着两个都可以。

其实name+age和age更好，因为索引也是需要持久化存储的，占用磁盘空间，读取的时候也是占用内存的，name+age和age+name这两个占用是一样的，但是name和age单独比较，肯定age占用空间更少，name更长（索引越大，IO次数可能更多）

注意！注意！注意！：

在看很多文章的时候，经常看到一些对于最左匹配错误的举例：

如果索引是name+age的组合索引，sql是select * from user where age = 18 and name="纪先生"

2. Abdeckindex

Die erforderlichen Felder können auf dem Nicht-Primärschlüsselindex abgefragt werden, ohne dass eine erneute Abfrage zur Tabelle erforderlich ist, was als Abdeckindex bezeichnet wird.

🎜🎜Wie im Namensindex im Bild oben gezeigt, ist der SQL-Code select id,name from user where name = „1“. Der Wert von id ist bereits im Nicht-Primärschlüssel verfügbar Index im ersten Schritt, daher ist es nicht erforderlich, ihn auf der ID zu basieren. Gehen Sie zum Primärschlüsselindex, um Zeilendaten abzufragen. 🎜🎜🎜🎜3. Übereinstimmung ganz links 🎜🎜🎜🎜🎜 Passen Sie im kombinierten Index zuerst die linke Seite an und stimmen Sie dann weiter rückwärts ab. In der Benutzertabelle gibt es beispielsweise einen gemeinsamen Index, der aus Name und Alter besteht * vom Benutzer, wobei Name="Mr. Ji" und Alter = 18 mit der Übereinstimmung ganz links übereinstimmen und als Index verwendet werden können. Wählen Sie jedoch * von einem Benutzer aus, bei dem Alter = 18 nicht konform ist und dieser Index nicht verwendet werden kann. 🎜🎜🎜Erweiterung; 🎜🎜So erstellen Sie einen Index, wenn es sich um die folgenden zwei SQL handelt🎜

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

Nach dem Login kopieren

Nach dem Login kopieren

🎜Aufgrund des am weitesten links liegenden Matching-Prinzips: 🎜Sie müssen nur einen kombinierten Index Alter + Name erstellen🎜🎜🎜Was ist, wenn es das Folgende ist? drei SQL🎜

mysql> alter table staff add index index_n1(first_name,last_name,username);

Nach dem Login kopieren

Nach dem Login kopieren

🎜 Erstellen Sie Name+Alter- und Altersindizes oder erstellen Sie Alters+Name- und Namensindizes, beides ist in Ordnung. 🎜🎜Tatsächlich sind 🎜Name+Alter und Alter besser🎜, da der Index auch dauerhaft gespeichert werden muss und beim Lesen Namen+Alter und Alter+Name gleich viel belegt Separat nimmt das Alter definitiv weniger Platz ein und der Name ist länger (je größer der Index, desto mehr IO-Zeiten) 🎜🎜🎜Achtung! Beachten! Beachten! :🎜🎜🎜🎜Wenn ich viele Artikel lese, sehe ich oft einige Beispiele für Übereinstimmungsfehler ganz links: 🎜🎜Wenn der Index ein kombinierter Index aus Name und Alter ist, lautet die SQL-Anweisung select * from user where age = 18 and name ="Mr. Ji"Viele Leute denken, dass dies nicht indiziert werden kann, aber tatsächlich ist es möglich. Der MySQL-Optimierer optimiert die Anpassungssequenz und passt sie an name="Mr. Ji" und age = 18 an oft wieder an den Tisch🎜

案例：还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数

05 索引匹配方式

mysql官网准备了一些学习测试的数据库，可以直接下载通过source导入到我们自己的数据库

官网地址：dev.mysql.com/doc/index-o…

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql,分别是sakila的库，表和数据的创建脚本。

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通过explain来查看索引的执行情况，执行计划以前有文章详细讲过，具体参考执行计划explain

1. 全值匹配

指和某个索引中的所有列进行匹配，例如使用数据库sakila中的staff表

新建一个三个字段的联合索引：

mysql> alter table staff add index index_n1(first_name,last_name,username);

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'复制代码

其中的ref是三个const， 用到三个字段，能全匹配一条数据

2. 最左前缀匹配

只匹配组合索引中前面几个字段

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

ref只出现2个const,比上面全值匹配少一个，就只匹配了前面两个字段

3. 匹配列前缀

可以匹配某一列的的开头部分，像like属性

执行sql:

mysql> explain select * from staff where first_name like 'Mi%';

type=range ,是个范围查询，可以匹配一个字段的一部分，而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面，否则有索引也不能使用，如下

4. 匹配一个范围值

可以查找某一个范围的数据

mysql> explain select * from staff where first_name > 'Mike';

5. 精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

6. 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，其实就是索引覆盖

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';

extra=Using index 说明是使用了索引覆盖，不需要再次回表查询。

其实一张表中有索引并不总是最好的。总的来说，只有当索引帮助存储引擎快速提高查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对应很小的表，大部分情况下没有索引，全表扫描更高效；对应中大型表，索引时非常有效的；但是对于超大的表，索引的建立和使用代价也就非常高，一般需要单独处理特大型的表，例如分区，分库，分表等。

更多编程相关知识，请访问：编程视频！！

Das obige ist der detaillierte Inhalt vonVertieftes Verständnis von Indizes in MySQL (Verwendung, Klassifizierung, Matching-Methoden). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!