Was ist ein Präfixindex in MySQL?

In MySQL ist der Präfixindex ein spezieller Indextyp, der die ersten Zeichen des Textes indiziert. Dieser Indextyp kann die Größe des Index bis zu einem gewissen Grad reduzieren und einige spezifische Abfragevorgänge effizienter verarbeiten. Unter normalen Umständen sollte die Indexlänge so kurz wie möglich sein, da kurze Indizes die Größe des Index verringern können. Wenn die Indexlänge jedoch zu kurz ist, kann es bei der Verwendung zu einem Indexfehler kommen, was zu einer langsameren Abfrageeffizienz führt Präfix-Indizes Dabei müssen Sie die entsprechende Indexlänge entsprechend der jeweiligen Situation auswählen.

Die Betriebsumgebung dieses Tutorials: Windows7-System, MySQL8-Version, Dell G3-Computer.

1. Was ist ein Präfixindex?

MySQL ist ein weit verbreitetes relationales Datenbankverwaltungssystem, das eine hohe Effizienz und Flexibilität bei der Datenspeicherung und -abfrage bietet. In MySQL ist ein Index eine Datenstruktur, die zur Beschleunigung von Abfragevorgängen verwendet wird, wodurch die Geschwindigkeit und Effizienz von Abfragen erheblich verbessert werden kann. Der Präfixindex ist ein spezieller Indextyp in MySQL, der einige spezifische Abfragevorgänge effizienter verarbeiten kann.

Der sogenannte Präfixindex besteht, um es ganz klar auszudrücken, darin, die ersten paar Zeichen des Textes zu indizieren (Konkret werden diese Zeichen beim Erstellen des Indexes angegeben, zum Beispiel die ersten 10 Zeichen des Produktnamens). werden verwendet, um den Index zu erstellen. Der Index ist kleiner und die Abfrageeffizienz ist schneller!

Präfixindizierung bedeutet, dass nur der Präfixteil der Zeichenfolge indiziert wird, nicht die gesamte Zeichenfolge. Dieser Indextyp kann die Größe des Index bis zu einem gewissen Grad reduzieren. Wenn Sie beispielsweise eine Tabelle mit zehn Millionen Zeichenfolgen haben, ist jede Zeichenfolge 100 Zeichen lang. Wenn Sie die vollständige Zeichenfolge als Index verwenden, beträgt die Indexgröße wird sehr groß sein und die Abfrageeffizienz wird sehr gering sein. Wenn Sie jedoch nur die ersten 10 Zeichen der Zeichenfolge indizieren, wird die Indexgröße erheblich reduziert und die Abfrageeffizienz erheblich verbessert.

Es ähnelt in gewisser Weise der Verwendung der Left-Funktion für Felder in Oracle zum Erstellen eines Funktionsindex, mit der Ausnahme, dass der Präfixindex von MySQL den Abgleich intern während der Abfrage automatisch abschließt und die Left-Funktion nicht verwendet werden muss.

In MySQL erfordert die Verwendung eines Präfixindex die Angabe der Länge des Index. Normalerweise sollte die Indexlänge so kurz wie möglich sein, da kurze Indizes die Größe des Index verringern können. Wenn die Indexlänge jedoch zu kurz ist, kann es zu Indexfehlern kommen, was zu einer langsameren Abfrageeffizienz führt. Daher müssen Sie bei der Verwendung von Präfixindizes die geeignete Indexlänge entsprechend der jeweiligen Situation auswählen.

2. Warum einen Präfixindex verwenden

Einige Schüler fragen sich vielleicht: Warum nicht das gesamte Feld indizieren?

Wenn die Datenmenge in einem bestimmten Feld zu groß ist und die Abfrage sehr häufig erfolgt, kann die Verwendung eines Präfixindex die Größe der Indexdatei effektiv reduzieren, sodass auf jeder Indexseite mehr Indexwerte gespeichert werden können Geschwindigkeit von Indexabfragen.

Zum Beispiel sind einige Kundengeschäftsnamen sehr lang und andere sehr kurz. Wenn der Index vollständig mit vollständiger Abdeckung erstellt wird, kann der Speicherplatz des Index sehr groß sein. Einige Tabellen enthalten möglicherweise sogar eine große Anzahl Wenn viele Indizes erstellt werden, ist der Speicherplatz des Indexes viel größer als der Speicherplatz der Datentabelle. Daher können wir für dieses Feld mit langem Text die ersten paar Zeichen abfangen, um den Index zu erstellen Bis zu einem gewissen Grad kann es nicht nur die Anforderungen an die Effizienz der Datenabfrage erfüllen, sondern auch Indexspeicherplatz sparen.

Aber andererseits hat der Präfixindex auch seine Nachteile. In MySQL kann der Präfixindex weder für ORDER BY noch für das Coverage-Scannen verwendet werden Die ersten paar Zeichen sind genau gleich. Zu diesem Zeitpunkt sind die Vorteile des Präfixindex nicht mehr offensichtlich und es besteht keine Notwendigkeit, einen Präfixindex zu erstellen.

Das kommt also auf das Konzept der

Selektivität in Indizes zurück!

Bezüglich der Selektivität von Datenbanktabellenindizes werde ich es in einem separaten Artikel erläutern. Sie müssen sich nur eines merken:

Je höher die Selektivität des Index, desto höher ist die Abfrageeffizienz, da ein hochselektiver Index dies ermöglichen kann MySQL zum Durchführen von Suchvorgängen beim Suchen. Filtern Sie mehr Zeilen heraus und fragen Sie Daten schneller ab!

Wenn die ersten paar Ziffern eines bestimmten Feldinhalts gut unterscheidbar sind, kann die Verwendung des Präfixindex zu diesem Zeitpunkt eine sehr hohe Kostenleistung in Bezug auf Abfrageleistung und Speicherplatz erzielen.

Dann stellt sich die Frage: Wie erstellt man einen Präfixindex?

3. So erstellen Sie einen Präfixindex

Der Weg zum Erstellen eines Präfixindex ist wie folgt:

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

Der Parameter

bedeutet die Präfixlänge. Er wird normalerweise auf folgende Weise bestätigt: prefix_length

Der erste Schritt besteht darin, die Diskriminierung der gesamten Spalte eines Feldes zu berechnen.

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

Der zweite Schritt besteht darin, die Präfixlänge zu berechnen, die der Diskriminierung der gesamten Spalte am ähnlichsten ist

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

最后，不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是我们想要的值。

下面以某个测试表为例，数据体量在 100 万以上，表结构如下！

CREATE TABLE `tb_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

测试一下正常的带name条件查询，效率如下：

select * from tb_test where name like '1805.59281427%'

我们以name字段为例，创建前缀索引，找出最合适的prefix_length值。

首先，我们大致计算一下name字段全列的区分度。

可以看到，结果为 0.9945，也就是说全局不相同的数据率在99.45%这个比例。

下面我们一起来看看，不同的prefix_length值下，对应的数据不重复比例。

当prefix_length为5，区分度为0.2237

当prefix_length为10，区分度为0.9944

当prefix_length为11，区分度为0.9945

通过对比，我们发现当prefix_length为11，最接近全局区分度，因此可以为name创建一个长度为11的前缀索引，创建索引语句如下：

alter table tb_test add key(name(11));

下面，我们再试试上面那个语句查询！

创建前缀索引之后，查询效率倍增！

四、使用前缀索引需要注意的事项

是不是所有的字段，都适合用前缀索引呢？

答案显然不是，在上文我们也说到了，当某个索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，使用索引的前部分字符串作为索引值，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多，这时前缀索引显现的作用就会非常明显，前缀索引本质是索引查询性能和存储空间的一种平衡。

对于 BLOB 和 TEXT 列进行索引，或者非常长的 VARCHAR 列，就必须使用前缀索引，因为 MySQL 不允许索引它们的全部长度。

但是如果某个字段内容，比如前缀部分相似度很高，此时的前缀索引显现效果就不会很明显，采用覆盖索引效果会更好！

【相关推荐：mysql视频教程】

Das obige ist der detaillierte Inhalt vonWas ist ein Präfixindex in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!