So erstellen Sie Hochleistungsindizes für MySQL

1 Index-Grundlagen

1.1 Indexfunktion

In MySQL wird bei der Suche nach Daten Suchen Sie zuerst den entsprechenden Wert im Index und dann die entsprechende Datenzeile basierend auf dem passenden Indexdatensatz. Wenn Sie die folgende Abfrageanweisung ausführen möchten:

SELECT	* FROM  USER  WHERE uid = 5;

Wenn ein Index auf der UID basiert , MySQL verwendet den Index. Suchen Sie zuerst die Zeile mit der UID 5, was bedeutet, dass MySQL zuerst nach Wert im Index sucht und dann alle Datenzeilen zurückgibt, die diesen Wert enthalten.

1.2 Häufig verwendete Datenstrukturen für MySQL-Indizes

MySQL-Indizes werden auf der Ebene der Speicher-Engine implementiert, nicht auf dem Server. Daher gibt es keinen einheitlichen Indexierungsstandard: Indizes in verschiedenen Speicher-Engines funktionieren unterschiedlich.

1.2.1 B-Tree

Die meisten MySQL-Engines unterstützen diese Art von Index-B-Tree, auch wenn mehrere Speicher-Engines denselben Indextyp unterstützen, kann die zugrunde liegende Implementierung auftreten unterscheiden sich auch. InnoDB verwendet beispielsweise B+Tree.

Speicher-Engines implementieren B-Tree auf unterschiedliche Weise, mit unterschiedlichen Leistungen und Vorteilen. MyISAM verwendet beispielsweise die Präfixkomprimierungstechnologie, um Indizes zu verkleinern, während InnoDB die Daten gemäß dem ursprünglichen Datenformat speichert. MyISAM-Indizes beziehen sich auf die indizierten Zeilen anhand des physischen Speicherorts der Daten, während InnoDB die indizierten Zeilen entsprechend der Komponente anwendet .

Alle Werte im B-Baum werden nacheinander gespeichert und der Abstand von jeder Blattseite zur Wurzel ist gleich. Die folgende Abbildung zeigt grob die Funktionsweise des InnoDB-Index. Die von MyISAM verwendete Struktur ist unterschiedlich. Die grundlegende Implementierung ist jedoch ähnlich.

Instanzdiagrammbeschreibung:

Jeder Knoten belegt einen Festplattenblock, und auf einem Knoten gibt es zwei aufsteigende Sortierschlüssel. Wort und drei Zeiger auf den Wurzelknoten des Teilbaums. Die Zeiger speichern die Adresse des Plattenblocks, in dem sich der untergeordnete Knoten befindet. Die drei durch die beiden Schlüsselwörter geteilten Bereichsfelder entsprechen den Bereichsfeldern der Daten des Teilbaums, auf den die drei Zeiger zeigen. Am Beispiel des Wurzelknotens lauten die Schlüsselwörter 16 und 34, der Datenbereich des Teilbaums, auf den der P1-Zeiger zeigt, ist kleiner als 16, der Datenbereich des Teilbaums, auf den der P2-Zeiger zeigt, beträgt 16 bis 34 und die Daten Der Bereich des Teilbaums, auf den der P3-Zeiger zeigt, ist größer als 34. Schlüsselwortsuchprozess:

• Suchen Sie Festplattenblock 1 basierend auf dem Wurzelknoten und lesen Sie ihn in den Speicher. [Festplatten-E/A-Vorgang zum ersten Mal]

• Vergleiche Schlüsselwort 28 Suchen Sie im Intervall (16,34) den Zeiger P2 von Festplattenblock 1.

• Suchen Sie Plattenblock 3 anhand des P2-Zeigers und lesen Sie ihn in den Speicher ein. [Festplatten-E/A-Vorgang zum zweiten Mal]

• Vergleiche Schlüsselwort 28 Suchen Sie im Intervall (25,31) den Zeiger P2 von Festplattenblock 3.

• Suchen Sie Plattenblock 8 entsprechend dem P2-Zeiger und lesen Sie ihn in den Speicher. [Festplatten-E/A-Vorgang 3.]

• Suchen Sie das Schlüsselwort 28 in der Schlüsselwortliste im Festplattenblock 8.

Nachteile:

• Jeder Knoten hat einen Schlüssel und bei Gleichzeitig Es enthält auch Daten und der Speicherplatz jeder Seite ist begrenzt. Wenn die Datenmenge relativ groß ist, wird die Anzahl der in jedem Knoten gespeicherten Schlüssel kleiner. 🎜🎜#Wenn die Datenmenge beim Speichern groß ist, ist die Tiefe groß, was die Anzahl der Festplatten-E/As während der Abfrage erhöht und sich somit auf die Abfrageleistung auswirkt.

• 1.2.2 B+Tree-Index

B+-Baum ist eine Variante des B-Baums. Unterschied zum B-Baum: Der B+-Baum speichert Daten nur in Blattknoten, und Nicht-Blattknoten speichern nur Schlüsselwerte und Zeiger.

Es gibt zwei Zeiger im B+-Baum, einer zeigt auf den Wurzelblattknoten, der andere zeigt auf den Blattknoten mit dem kleinsten Schlüsselwort, und zwischen allen Blattknoten (d. h. Daten) gibt es einen Kettenring Knoten)-Struktur, so dass zwei Suchoperationen für den B+-Baum durchgeführt werden können: eine ist eine Bereichssuche für die Komponente und die andere ist eine Zufallssuche ausgehend vom Wurzelknoten.

Der B*-Baum ähnelt der B+-Zahl. Der Unterschied besteht darin, dass die B*-Zahl auch eine Kettenringstruktur zwischen Nicht-Blattknoten aufweist.

1.2.3 Hash-Index

Der Hash-Index basiert auf der Hash-Tabelle und nur auf Abfragen, die genau mit allen übereinstimmen Spalten des Index wirksam ist. Für jede Datenzeile berechnet die Speicher-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. Ein Hash-Index speichert alle Hash-Codes im Index und einen Zeiger auf jede Datenzeile in der Hash-Tabelle.

In MySQL ist nur der Standardindextyp des Speichers der verwendete Hash-Index, und der Speicher unterstützt auch B-Tree-Indizes. Gleichzeitig unterstützt die Speicher-Engine nicht eindeutige Hash-Indizes. Wenn die Hash-Werte mehrerer Spalten gleich sind, speichert der Index mehrere Zeiger im selben Hash-Eintrag in einer verknüpften Liste. Ähnlich wie HashMap.

Vorteile:

Der Index selbst muss nur den entsprechenden Hashwert speichern, also die Indexstruktur ist sehr kompakt und gehasht, sodass Suchvorgänge sehr schnell erfolgen.

Nachteile:

Wenn Sie Hash-Speicher verwenden, müssen Sie alle Datendateien zum Speicher hinzufügen, was mehr verbraucht Speicherplatz;# 🎜🎜#

• Hash-Indexdaten werden nicht in der richtigen Reihenfolge gespeichert und können daher nicht zum Sortieren verwendet werden;

• Wenn alle Abfragen gleichwertige Abfragen sind, ist das Hashing sehr schnell, aber in einem Unternehmen oder einer tatsächlichen Arbeitsumgebung müssen mehr Daten in Bereichen durchsucht werden als in äquivalenten Abfragen Hash ist nicht geeignet;

• Wenn es viele Hash-Konflikte gibt, sind die Kosten für Indexwartungsvorgänge sehr hoch. Dies wird auch durch das Hinzufügen von Rot-Schwarz-Bäumen gelöst das spätere Stadium von HashMap.

2 Hochleistungsindexstrategie

2.1 Clustered-Index und Nicht-Clustered-Index

#🎜🎜 #Clustered Index

ist kein separater Indextyp, sondern eine Datenspeichermethode. In der InnoDB-Speicher-Engine speichert der Clustered-Index tatsächlich Schlüsselwerte und Datenzeilen die gleiche Struktur. Wenn eine Tabelle über einen Clustered-Index verfügt, werden ihre Datenzeilen tatsächlich in den Blattseiten des Index gespeichert. Da Datenzeilen nicht gleichzeitig an verschiedenen Orten gespeichert werden können, kann es in einer Tabelle nur einen Clustered-Index geben (die Indexabdeckung kann die Situation mehrerer Clustered-Indizes simulieren).

Vorteile des Clustered-Index:

Kann verwandte Daten zusammen speichern; der Datenzugriff ist schneller, da der Index und die Daten gespeichert werden Derselbe Baum; Abfragen, die abdeckende Indexscans verwenden, können den Primärschlüsselwert im Seitenknoten direkt verwenden. Wenn sich alle Daten im Speicher befinden, hat die Einfügungsgeschwindigkeit keinen Vorteil Die Einfügereihenfolge hängt stark davon ab, und das Einfügen in der Reihenfolge des Primärschlüssels ist der schnellste Weg. Das Aktualisieren der Clustered-Index-Spalten ist sehr kostspielig, da jede aktualisierte Zeile auf der Grundlage eines Clustered-Index an eine neue Position verschoben werden muss Wenn eine neue Zeile eingefügt wird oder der Primärschlüssel aktualisiert wird und die Zeile verschoben werden muss, kann es zu Problemen mit der Seitenaufteilung kommen. Dies führt möglicherweise dazu, dass der vollständige Tabellenscan langsamer wird, insbesondere wenn die Zeilen spärlich sind Die Datenspeicherung ist aufgrund der Seitenaufteilung diskontinuierlich. Separat speichern. Normalerweise können Sie eine Spalte verwenden, die mit einem Teil der Zeichenfolge beginnt. Dies spart erheblich Indexplatz und verbessert dadurch die Indexeffizienz. Dies verringert jedoch die Selektivität des Index. Die Selektivität des Index bezieht sich auf: den eindeutigen Indexwert (auch genannt). Kardinalität) und die Gesamtzahl der Datentabellendatensätze im Bereich von 1/#T bis 1. Je höher die Selektivität des Index, desto höher die Abfrageeffizienz, da ein selektiverer Index es MySQL ermöglicht, bei der Suche mehr Zeilen herauszufiltern.

Im Allgemeinen ist die Selektivität eines bestimmten Spaltenpräfixes hoch genug, um die Abfrageleistung zu erfüllen. Für Spalten der Typen BLOB, TEXT und VARCHAR müssen jedoch Präfixindizes verwendet werden, da dies bei MySQL nicht der Fall ist Lassen Sie es zu. Der Trick bei dieser Methode besteht darin, ein Präfix zu wählen, das lang genug ist, um eine hohe Selektivität zu gewährleisten.

Beispiel

Tabellenstruktur und Datendownload von der offiziellen MySQL-Website oder GitHub.

Stadttabellenspalten

Feldname

Bedeutung

city_id

Stadt-Primärschlüssel-ID

cityStadtname#🎜 🎜 #country_idlast_update:#🎜 🎜 # Erstellt oder zuletzt aktualisiert

--计算完整列的选择性
select count(distinct left(city,3))/count(*) as sel3,
    count(distinct left(city,4))/count(*) as sel4,
    count(distinct left(city,5))/count(*) as sel5,
    count(distinct left(city,6))/count(*) as sel6,
    count(distinct left(city,7))/count(*) as sel7,
    count(distinct left(city,8))/count(*) as sel8 
from citydemo;

可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了。由此最佳创建前缀索引长度为7。

2.3 回表

要理解回表需要先了解聚族索引和普通索引。聚族索引即建表时设置的主键索引，如果没有设置MySQL自动将第一个非空唯一值作为索引，如果还是没有InnoDB会创建一个隐藏的row-id作为索引（oracle数据库row-id显式展示，可以用于分页）；普通索引就是给普通列创建的索引。普通列索引在叶子节点中存储的并不是整行数据而是主键，当按普通索引查找时会先在B+树中查找该列的主键，然后根据主键所在的B+树中查找改行数据，这就是回表。

2.4 覆盖索引

覆盖索引在InnoDB中特别有用。MySQL中可以使用索引直接获取列的数据，如果索引的叶子节点中已经包含要查询的数据，那么就没必要再回表查询了，如果一个索引包含（覆盖）所有需要查询的字段的值，那么该索引就是覆盖索引。简单的说：不回表直接通过一次索引查找到列的数据就叫覆盖索引。

表信息

CREATE TABLE `t_user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

举例

--将uid设置成主键索引后通过下面的SQL查询 在explain的Extra列可以看到“Using index”
explain select uid from t_user where uid = 1;

覆盖索引在组合索引中用的比较多，举例

explain select age,uname from t_user where age = 10 ;

当不建立组合索引时，会进行回表查询

设置组合索引后再次查询

create index index_user on t_user(age,uname);

2.5 索引匹配方式

2.5.1 最左匹配

在使用组合索引中，比如设置(age,name)为组合索引，单独使用组合索引中最左列是可以匹配索引的，如果不使用最左列则不走索引。例如下面SQL

--走索引
explain select * from t_user where age=10 and uname='zhang';

下面的SQL不走索引

explain select * from t_user where  uname='zhang';

2.5.2 匹配列前缀

可以匹配某一列的值的开头部分，比如like 'abc%'。

2.5.3 匹配范围值

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

explain select * from t_user where age>18;

2.5.4 精确匹配某一列并范围匹配另外一列

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

explain select * from t_user where age=18 and uname like 'zhang%';

2.5.5 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引。

explain select age,uname,update_time from t_user 
            where age=18 and uname= 'zhang' and update_time='123';

3 索引优化最佳实践

1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。

--推荐
select uid,age,uname from t_user where uid=1;

--不推荐
select uid,age,uname from t_user where uid+9=10;

2. 尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询

3. 使用前缀索引参考2.2 前缀索引
4. 使用索引扫描排序mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。
mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序。
举例表结构及数据MySQL官网或GItHub下载。

CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

rental表在rental_date,inventory_id,customer_id上有rental_date的索引。使用rental_date索引为下面的查询做排序

--该查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两个列组合在一起，就形成了索引的最左前缀
explain select rental_id,staff_id from rental 
where rental_date='2005-05-25' order by inventory_id desc

--下面的查询不会利用索引
explain select rental_id,staff_id from rental 
where rental_date>'2005-05-25' order by rental_date,inventory_id

5. union all,in,or都能够使用索引，但是推荐使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

6. 范围列可以用到索引范围条件是：d2714fbb0e49a95306c2048bc19e4f2b、>=、between。范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。

7. 更新十分频繁，数据区分度不高的字段上不宜建立索引

• 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能；

• 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据；

• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算；

8. 创建索引的列，不允许为null，可能会得到不符合预期的结果

9.当需要进行表连接的时候，最好不要超过三张表，如果需要join的字段，数据类型必须一致

10. 能使用limit的时候尽量使用limit

11. 单表索引建议控制在5个以内

12. 单索引字段数不允许超过5个（组合索引）

13. 创建索引的时候应该避免以下错误概念

• 索引越多越好

• 过早优化，在不了解系统的情况下进行优化

4 索引监控

show status like 'Handler_read%';

Country ID

参数	说明
Handler_read_first	读取索引第一个条目的次数
Handler_read_key	通过index获取数据的次数
Handler_read_last	读取索引最后一个条目的次数
Handler_read_next	通过索引读取下一条数据的次数
Handler_read_prev	通过索引读取上一条数据的次数
Handler_read_rnd	从固定位置读取数据的次数
Handler_read_rnd_next	从数据节点读取下一条数据的次数

Das obige ist der detaillierte Inhalt vonSo erstellen Sie Hochleistungsindizes für MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!