Zusammenfassung zur Verwendung von MySQL-Isolationsspalten und Präfixindizes

Datenspalten isolieren

Häufig stoßen wir auf Abfragen, die MySQL daran hindern, Indizes zu verwenden. MySQL verwendet keine Indizes für Spalten, es sei denn, sie werden unabhängig in einer Abfrage verwendet. „Isolieren“ bedeutet, dass die indizierten Spalten nicht Teil eines Ausdrucks oder im Hauptteil einer Abfragefunktion sein sollten. Beispielsweise wird im folgenden Beispiel der Index „actor_id“ nicht erreicht.

Wählen Sie `actor_id` aus `actor`, wobei `actor_id` + 1 = 2 ist.

Für einen Menschen ist es leicht zu erkennen, dass die Abfragebedingung eigentlich „actor_id = 4“ lautet, MySQL behandelt dies jedoch nicht auf diese Weise. Gewöhnen Sie sich daher an, die WHERE-Entscheidungsbedingung zu vereinfachen. Dies bedeutet, dass die Indexspalte allein auf einer Seite des Vergleichsoperators steht. Hier ist ein weiteres Beispiel für einen häufigen Fehler:

AUSWÄHLEN ... WO HEUTE_TAGE(AKTUELLES_DATUM) - HEUTE_TAGE(Datumsspalte) <= 10;

Präfixindizes und Indexselektivität

Manchmal müssen Sie einen Index für eine Spalte mit sehr langen Zeichen erstellen. Dies führt jedoch dazu, dass der Index viel Speicherplatz beansprucht und die Abfrage verlangsamt. Eine Strategie besteht darin, eine Hash-Index-Simulation zu verwenden, aber manchmal ist dies möglicherweise nicht gut genug. Was sollten wir jetzt tun?

Normalerweise können Sie einige der Zeichen vor der Indexspalte indizieren, um den vollständigen Feldindex zu ersetzen und so die Leistung zu verbessern und Speicherplatz zu sparen. Dieser Ansatz führt jedoch zu einer schlechten Selektivität. Die Selektivität eines Index bezeichnet den Anteil der durch unabhängige Indexwerte herausgefilterten Daten am gesamten Datensatz. Durch hochselektive Indizes ist MySQL in der Lage, mehr irrelevante Daten herauszufiltern. Beispielsweise beträgt die Selektivität eines eindeutigen Indexes 1. Das Präfix der Spalte bietet im Allgemeinen eine ausreichend gute Leistung hinsichtlich der Selektivität. Wenn Sie BLOB- oder TEXT- oder sehr lange VARCHAR-Spalten verwenden, müssen Sie Präfixindizes definieren, da MySQL keine Indizes in voller Länge zulässt.

Sie müssen einen Ausgleich zwischen der Verwendung längerer Präfixe für eine bessere Selektivität und ausreichend kurzen Präfixen zum Einsparen von Speicherplatz finden. Um eine geeignete Präfixlänge zu bestimmen, suchen Sie den häufigsten Wert und vergleichen Sie ihn mit dem häufigsten Präfix. Am Beispiel der Stadtdatentabelle können wir zum Zählen die folgende Anweisung verwenden:

SELECT COUNT(*) als Anzahl, `Name` FROM `common_city` GROUP BY `Name` ORDER BY Anzahl DESC LIMIT 10

Es ist zu erkennen, dass diese Städtenamen häufiger vorkommen. Jetzt können wir das 1-Wort-Präfix verwenden, um die häufigsten Städtenamen-Präfixe zu finden.

SELECT COUNT(*) als Anzahl, LEFT(`name`, 1) als Präferenz FROM `common_city` GROUP BY Präferenz ORDER BY Anzahl DESC LIMIT 10

Es ist ersichtlich, dass für ein Wort mehr Datensätze gefunden werden, was zu geringeren Chancen einer unabhängigen Auswahl führt. Daher muss die Länge des Präfixes angepasst werden. Passen Sie es beispielsweise auf 3 Wörter an.

SELECT COUNT(*) als Anzahl, LEFT(`name`, 3) als Präferenz FROM `common_city` GROUP BY Präferenz ORDER BY Anzahl DESC LIMIT 10

Man sieht, dass sich dies nicht groß von der vollen Länge unterscheidet, sodass ein dreistelliges Präfix eigentlich ausreicht (der Originaltext verwendet eine englische Städtedatentabelle mit mehr Zeichen). Eine andere Möglichkeit besteht darin, die Angemessenheit anhand des Verhältnisses der Anzahl der Präfixe unterschiedlicher Länge zur Anzahl der vollständigen Felder zu beurteilen. Zum Beispiel:

WÄHLEN 
  ANZAHL(DISTINCT LEFT(`name`, 1)) / ANZAHL(`name`) als pref1, 
  ANZAHL(DISTINCT LEFT(`name`, 2)) / ANZAHL(`name`) als pref2, 
  ANZAHL(DISTINCT LEFT(`name`, 3)) / ANZAHL(`name`) als pref3, 
  ANZAHL(DISTINCT LEFT(`name`, 4)) / ANZAHL(`name`) als pref4 
VON `common_city`

Je näher der Wert bei 1 liegt, desto besser ist der Effekt. Allerdings ist zu erkennen, dass mit zunehmender Präfixlänge der Spielraum für Verbesserungen abnimmt. Es ist keine gute Idee, nur den Durchschnitt zu betrachten. Sie müssen auch das Worst-Case-Szenario berücksichtigen. Sie denken vielleicht, dass 3–4 Wörter ausreichen, aber wenn Ihre Daten sehr ungleichmäßig verteilt sind, kann es Fallstricke geben. Daher muss auch geprüft werden, ob es eine Situation gibt, in der die Daten, die einem Präfix mit weniger Präfixen entsprechen, im Vergleich zu den anderen extrem groß sind. Schließlich können Sie der angegebenen Spalte einen Präfixindex hinzufügen.

ALTER TABLE `common_city` ADD KEY (name(3));

Präfixindizes sind hinsichtlich der Platzersparnis und Effizienzsteigerung leistungsfähig, weisen jedoch auch einen Fehler auf, nämlich dass der Index nicht in ORDER BY und GROUP BY verwendet werden kann (eine tatsächliche Überprüfung ist auch in MySQL-Versionen über 5.7 nützlich). Ein weiteres häufiges Szenario ist, dass in einer längeren hexadezimalen Zeichenfolge, z. B. einer gespeicherten Sitzungs-ID, die Verwendung des ersten 8-stelligen Präfixes als Index viele irrelevante Daten herausfiltert, was sehr effektiv ist.

Oben finden Sie den detaillierten Inhalt der Zusammenfassung zur Verwendung von MySQL-Isolationsdatenspalten und Präfixindizes. Weitere Informationen zu MySQL-Isolationsdatenspalten und Präfixindizes finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!