Diskussion über sinnvollere Erstellungsregeln für MySQL-String-Indizes

Vorwort

In Bezug auf die Verwendung von MySQL-Indizes haben wir zuvor Konzepte wie die Regel für das am weitesten links stehende Präfix von Indizes, die Indexabdeckung, die Verwendung eindeutiger und gemeinsamer Indizes sowie die Indexauswahl des Optimierers vorgestellt. Heute werden wir besprechen, wie Indizes für Zeichenfolgen sinnvoller erstellt werden können.

So erstellen Sie String-Indizes besser

Wir wissen, dass sich in MySQL alle Daten und Indizes auf einem B+-Baum befinden. Wenn wir einen Index erstellen, ist die Abrufgeschwindigkeit umso höher, je kleiner der vom Baum belegte Platz ist. Einige Zeichenfolgen im varchar-Format können sehr lang sein. Wie können wir also in der heutigen Welt, in der Effizienz oberste Priorität hat, Zeichenfolgenindizes sinnvoller erstellen?
Angenommen, eine Tabelle enthält ein E-Mail-Feld und wir möchten einen Index für das E-Mail-Feld erstellen. Das Format des E-Mail-Feldwerts lautet: [email protected].

Es gibt zwei Möglichkeiten zum Erstellen von Indizes:

1. Erstellen Sie direkt einen Index für das E-Mail-Feld: alter table t add index index1(email);

Die Indexbaumstruktur ist:

2. Erstellen Sie einen Präfixindex für E-Mail: alter table t add index index2(email(6));

Die Indexdatenstruktur ist:

Derzeit ist unsere Abfrageanweisung: select id,name,email from t where email='[email protected]';

Bei Verwendung von index1 lauten die Ausführungsschritte:

1. Suchen Sie im Indexbaum Index1 nach dem Primärschlüsselwert ID1, dessen Indexwert [email protected] ist.

2. Gehen Sie zurück zur Tabelle basierend auf ID1 und stellen Sie fest, dass die Datenzeile tatsächlich [email protected] lautet. Fügen Sie das Ergebnis dem Ergebnissatz hinzu.

3. Suchen Sie weiter, ob der nächste Indexwert des Indexbaums index1 [email protected] erfüllt. Wenn nicht, beenden Sie die Abfrage.

Bei Verwendung von index2 lauten die Ausführungsschritte:

1. Suchen Sie im Indexbaum Index2 nach dem Primärschlüsselwert ID1 mit dem Indexwert zhangs.

2. Gehen Sie zurück zur Tabelle basierend auf ID1 und stellen Sie fest, dass die Datenzeile tatsächlich [email protected] lautet. Fügen Sie das Ergebnis dem Ergebnissatz hinzu.

3. Suchen Sie weiter, ob der nächste Indexwert des Indexbaums index2 zhangs erfüllt. Wenn ja, kehren Sie weiter zur Tabelle zurück, um abzufragen, ob die Zeilendaten [email protected] lauten. Wenn nicht, überspringen Sie den Vorgang und setzen Sie die Suche fort.

4. Durchsuchen Sie den Indexbaum Index2 weiter, bis der Indexwert nicht mehr zhangs ist.

Aus der obigen Analyse können wir ersehen, dass der Vollfeldindex die Anzahl der Tabellenrückgaben im Vergleich zum Präfixindex reduziert. Wenn wir jedoch das Präfix von 6 auf 7 oder 8 erhöhen, verringert sich die Anzahl der Tabellenrückgaben des Präfixindex. Mit anderen Worten, solange die Präfixlänge definiert ist, können wir Platz sparen und Effizienz sicherstellen.

Die Frage ist also, wie messen wir die Länge des Präfixindex?

1. Verwenden Sie select count(distinct email) as L from t; .

2. Wählen Sie nacheinander unterschiedliche Präfixlängen aus, um die Anzahl der unterschiedlichen Werte anzuzeigen:

wählen
 count(distinct left(email,4)）als L4,
 count(distinct left(email,5)）als L5,
 count(distinct left(email,6)）als L6,
 count(distinct left(email,7)）als L7,
von t;

Anschließend wird entsprechend der tatsächlich akzeptablen Verlustquote die kürzeste geeignete Präfixlänge ausgewählt.

Wir haben das Problem der Präfixlänge gelöst, ein weiteres Problem besteht jedoch darin, dass unsere Indexabdeckungsfunktion nicht genutzt wird, wenn wir einen Präfixindex verwenden.
Bei Verwendung des vollständigen Feldindex können wir bei der Abfrage select id,email from t where email='[email protected]'; die ID- und E-Mail-Felder direkt finden, ohne zur Tabelle zurückkehren zu müssen.

Bei Verwendung eines Präfixindex weiß MySQL jedoch nicht, ob das Präfix den gesamten E-Mail-Wert abdeckt. Unabhängig davon, ob es vollständig enthalten ist, wird dies durch Abfragen der Tabelle basierend auf dem Primärschlüsselwert ermittelt.

Obwohl die Verwendung eines Präfixindex Platz spart und die Effizienz sicherstellt, können die Eigenschaften des Überdeckungsindex nicht genutzt werden. Ob er verwendet wird, hängt von bestimmten Überlegungen ab.

Andere Möglichkeiten zum Erstellen von Zeichenfolgenindizes

In der Praxis können nicht alle Zeichenfolgen mit Präfixkürzung indiziert werden. Beispielsweise ist es nicht sinnvoll, Präfixindizes für Zeichenfolgen wie ID-Nummern oder IP-Adressen zu verwenden. Die ersten Ziffern von ID-Nummern sind für Personen in derselben Region im Allgemeinen gleich, daher ist es nicht sinnvoll, Präfixindizes zu verwenden. In der Praxis konvertieren wir IP-Werte normalerweise zur Speicherung in Zahlen.

Für die Ausweisnummer können wir die umgekehrte Speicherung verwenden, das Präfix nehmen, um einen Index zu erstellen, oder die Funktion crc32() verwenden, um einen Hash-Prüfcode (int-Wert) als Index zu erhalten.

Umgekehrt: select field_list from t where id_card = reverse('input_id_card_string');

crc32: select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

Diese beiden Methoden sind relativ effizient. Keine unterstützt Bereichssuchen, aber Suchen nach gleichen Werten.

Bei der umgekehrten Methode muss die Umkehrfunktion verwendet werden, die Anzahl der Tabellenrückgaben kann jedoch höher sein als bei der Hash-Methode.

Im Hash-Modus müssen Sie ein neues Indexfeld erstellen und die Funktion crc32() aufrufen. (Hinweis: Es gibt keine Garantie dafür, dass das von der Funktion crc32() erhaltene Ergebnis eindeutig ist. Es kann zu Duplikaten kommen, die Wahrscheinlichkeit hierfür ist jedoch gering.) Die Häufigkeit, mit der die Tabelle zurückgegeben wird, ist gering, fast einmal ist ausreichend.

endlich

Es gibt grundsätzlich mehrere Möglichkeiten, Zeichenfolgenindizes zu erstellen:

1. Der String ist kurz, daher wird das ganze Feld direkt indexiert

2. Die Zeichenfolge ist lang und das Präfix weist eine gute Unterscheidung auf. Erstellen Sie daher einen Präfixindex

3. Die Zeichenfolge ist lang und das Präfix ist nicht unterscheidbar. Erstellen Sie einen Index in umgekehrter Reihenfolge oder im Hash-Modus (diese Methode funktioniert nicht für Bereichsabfragen).

4. Abhängig von der tatsächlichen Situation werden spezielle Zeichenfolgen speziell behandelt, z. B. IP.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.