So erstellen Sie einen Tabellenindex in MySQL

Das Konzept der Indizierung lässt sich am einfachsten anhand eines Beispiels verstehen. Im Folgenden finden Sie ein solches Beispiel.

Angenommen, wir müssen eine Online-Dating-Website entwerfen, deren Benutzerprofile viele Spalten haben, wie etwa Nationalität, Provinz, Stadt, Geschlecht, Alter, Augenfarbe usw. Die Site muss die Suche nach Benutzerprofilen in mehreren Kombinationen unterstützen. Gleichzeitig ist es auch erforderlich, das Sortieren und Zurückgeben begrenzter Ergebnisse basierend auf der jüngsten Onlinezeit des Benutzers und den Kommentaren anderer Benutzer usw. zu unterstützen. Wie entwerfen wir Indizes für solch komplexe Szenarien?

Etwas seltsam: Als Erstes müssen wir entscheiden, ob wir die Indexsortierung verwenden müssen oder ob eine Sortierung nach dem Abruf akzeptabel ist. Die Indexreihenfolge schränkt die Art und Weise ein, wie Indizes und Abfragen erstellt werden. Beispielsweise können wir nicht denselben Index für eine Abfrage wie „WHERE age BETWEEN 18 AND 25“ und eine Sortierung basierend auf anderen Benutzerbewertungen verwenden. Wenn MySQL einen Index für eine Bereichsabfrage verwendet, kann es keinen anderen Index für eine Sortierung verwenden. Gehen Sie davon aus, dass dies die am häufigsten verwendete WHERE-Bedingung ist und wir für die meisten Abfragen auch die Sortierung unterstützen müssen.

Unterstützt mehrere Filterarten

Nun müssen wir schauen, welche Spalten stärker gestreute Werte aufweisen und welche Spalten am häufigsten in der WHERE-Bedingung vorkommen. Die Filterleistung ist für Datenspalten mit verstreuten Werten gut. Dies ist normalerweise eine gute Sache, da MySQL dadurch irrelevante Zeilen effizient herausfiltern kann.

Die Spalte „Nationalität“ lässt sich möglicherweise nicht so gut filtern, wird aber wahrscheinlich am häufigsten durchsucht. Die Spalte „Geschlecht“ ist im Normalfall nicht filterbar, wird aber häufig bei Abfragen verwendet. Basierend auf diesem Verständnis haben wir eine Reihe von Indizes für viele verschiedene Spaltenkombinationen erstellt, und diese Indizes begannen mit (Geschlecht, Land).

Die traditionelle Auffassung besteht darin, dass es sinnlos ist, Indizes für Spalten mit geringen Filtereigenschaften zu erstellen. Warum fügen wir also am Anfang jedes Indexes nicht filterbare Spalten hinzu? Wir haben zwei Gründe, dies zu tun. Der erste Grund ist, dass, wie bereits erwähnt, das Geschlecht in fast jeder Abfrage verwendet wird. Wir haben es sogar so konzipiert, dass Benutzer immer nur nach einem Geschlecht gleichzeitig suchen können. Aber was noch wichtiger ist: Das Hinzufügen einer solchen Spalte hat nicht viele Nachteile, da wir einen kleinen Trick verwenden.

Hier ist unser Trick: Auch ohne die Abfrage auf das Geschlecht zu beschränken, können wir sicherstellen, dass der Index greift, indem wir der WHERE-Klausel AND sex IN('m', 'f') hinzufügen. Dadurch werden die gewünschten Zeilen nicht herausgefiltert und es hat denselben Effekt, als würde das Geschlecht in der WHERE-Klausel nicht berücksichtigt. Da MySQL diese Spalte jedoch jedem Index mit mehr Spalten voranstellt, müssen wir diese Spalte einschließen. Dieser Trick funktioniert in diesem Szenario, aber nicht, wenn die Spalte viele unterschiedliche Werte hat, weil das zu vielen Spalten in IN() führen würde.

Dieses Beispiel veranschaulicht ein Grundprinzip: Halten Sie sich beim Entwurf Ihrer Datentabelle alle Optionen offen. Denken Sie beim Entwerfen von Indizes nicht nur darüber nach, welcher Index für welche Abfrage am besten geeignet ist, sondern auch darüber, wie Sie die Abfrage optimieren können. Wenn Sie einen Index benötigen, aber feststellen, dass andere Abfragen davon betroffen sein könnten, sollten Sie sich zunächst fragen, ob Sie die Abfragen ändern können. Sie sollten sowohl Abfragen als auch Indizes optimieren, um die Lösung zu finden. Sie müssen nicht unbedingt einen perfekten Index entwerfen.

Als Nächstes müssen wir über andere mögliche Kombinationen von WHERE-Bedingungen nachdenken und dann überlegen, welche dieser Kombinationen ohne ordnungsgemäße Indizierung langsam sein werden. Ein Index wie (Geschlecht, Land, Alter) ist die naheliegende Wahl, aber wir möchten möglicherweise auch Indizes wie (Geschlecht, Land, Region, Alter) und (Geschlecht, Land, Region, Stadt, Alter).

Dies führt dazu, dass viele Indizes erstellt werden müssen. Wenn wir Indizes wiederverwenden können, haben wir nicht zu viele Kombinationen. Wir können den IN()-Trick verwenden, um die Indizes (Geschlecht, Land, Alter) und (Geschlecht, Land, Region, Alter) zu entfernen. Wenn diese Spalten im Suchformular nicht angegeben sind, können wir die Länderliste und die Regionenliste verwenden, um sicherzustellen, dass die Einschränkungen im Indexvoranschlag eingehalten werden (es kann viele Kombinationen aus allen Ländern, allen Regionen und allen Geschlechtern geben).

Diese Indizes werden die meisten Suchanfragen erfüllen. Wie gestalten wir jedoch weniger gebräuchliche Filter, wie etwa hochgeladene Bilder (has_pictures), Augenfarbe (eye_color), Haarfarbe (hair_color) und Bildungsniveau (education)? Wenn diese Spalten nicht so selektiv und nicht so häufig verwendet werden, können wir sie einfach überspringen und MySQL einige zusätzliche Zeilen scannen lassen. Dementsprechend können wir sie vor der Spalte „Alter“ hinzufügen und den Trick „IN()“ verwenden, um der Beschreibung voranzustellen und den Fall zu behandeln, in dem diese Spalten nicht angegeben sind.

Ihnen ist vielleicht aufgefallen, dass wir das Alter ans Ende des Index gesetzt haben. Warum wird diese Spalte besonders behandelt? Wir versuchen sicherzustellen, dass MySQL die Indexspalten so gut wie möglich nutzen kann. Weil MySQL die am weitesten links stehende Übereinstimmungsregel verwendet, bis es auf die erste Bereichsabfragebedingung stößt. Alle von uns genannten Spalten können in der WHERE-Klausel für Gleichheitsbedingungen verwendet werden, bei „age“ handelt es sich jedoch höchstwahrscheinlich um eine Bereichsabfrage.

Wir könnten die Bereichsabfrage auch mit einer IN-Abfrage in eine Liste ändern, z. B. Alter IN(18, 19, 20, 21, 22, 23, 24, 25) statt Alter ZWISCHEN 18 UND 25, aber das ist nicht immer möglich. Das allgemeine Prinzip besteht darin, dass wir versuchen, die Bereichsentscheidungsbedingungen an das Ende des Index zu setzen, damit der Optimierer den Index möglichst oft nutzt.

Wir haben erwähnt, dass Sie IN-Abfragen mit so vielen Spalten wie nötig verwenden können, um Indexbedingungen abzudecken, die nicht in der WHERE-Klausel angegeben sind. Aber man kann es übertreiben und neue Probleme schaffen. Die Verwendung mehrerer solcher IN-Abfragelisten führt dazu, dass der Optimierer eine größere Anzahl von Kombinationen auswerten muss, was wiederum die Abfrage verlangsamen kann. Betrachten Sie die folgende Abfrage:

WHERE Augenfarbe IN('braun', 'blau', 'haselnussbraun')
	UND Haarfarbe IN('schwarz', 'rot', 'blond', 'braun')
  	UND Geschlecht IN('M', 'F')

Der Optimierer wandelt dies in 432 = 24 Kombinationen um und die WHERE-Bedingung überprüft jeden Fall. 24 ist noch keine sehr große Kombinationszahl, aber wenn die Zahl mehrere Tausend erreicht, wird es eine sein. Ältere MySQL-Versionen haben möglicherweise mehr Probleme mit großen Zahlen in IN-Abfragen. Der Abfrageoptimierer wird langsamer ausgeführt und verbraucht mehr Speicher. Neuere Versionen von MySQL beenden die Auswertung, wenn zu viele Kombinationen vorliegen. Dies beeinträchtigt jedoch die Fähigkeit von MySQL, Indizes zu verwenden.

Vermeiden Sie Abfragen mit mehreren Bereichen

Nehmen wir an, es gibt eine Spalte „last_online“ und wir müssen Benutzer anzeigen, die in der letzten Woche online waren:

WHERE Augenfarbe IN('braun', 'blau', 'haselnussbraun')
	UND Haarfarbe IN('schwarz', 'rot', 'blond', 'braun')
  	UND Geschlecht IN('M', 'F')
 	UND last_online > DATE_SUB(JETZT(), INTERVALL 7 TAGE)
 	UND Alter ZWISCHEN 18 UND 25 

Das Problem bei dieser Abfrage besteht darin, dass sie zwei Bereichsabfragen enthält. MySQL kann entweder die Bedingung „last_online“ oder „age“ verwenden, jedoch nicht beide. Wenn die Einschränkung „last_online“ ohne eine Alterseinschränkung erscheint oder wenn „last_online“ selektiver ist als „age“, möchten wir möglicherweise einen weiteren Satz von Indizes hinzufügen und „last_online“ ans Ende setzen. Was aber, wenn wir das Alter nicht in eine IN-Abfrage umwandeln können und wir auch die Abfragegeschwindigkeit verbessern möchten, wenn sowohl last_oinline- als auch Altersgruppenabfragen vorhanden sind? Derzeit verfügen wir über keine direkte Methode. Aber wir können einen Bereich in einen Gleichheitsvergleich umwandeln. Dazu fügen wir eine vorkalkulierte aktive Spalte hinzu, die wir regelmäßig pflegen. Wenn sich der Benutzer anmeldet, markieren wir es als 1, und wenn er sich innerhalb von 7 Tagen nicht kontinuierlich anmeldet, markieren wir es wieder auf 0.

Mit dieser Methode kann MySQL einen Index wie (aktiv, Geschlecht, Land, Alter) verwenden. Diese Spalte ist möglicherweise nicht so präzise, ​​aber für diesen Abfragetyp ist möglicherweise keine sehr hohe Präzision erforderlich. Wenn wir eine präzise Suche benötigen, können wir last_online in der WHERE-Bedingung behalten, aber keinen Index hinzufügen. Diese Technik ähnelt der URL-Suche. Diese Bedingung verwendet keine Indizes, da es unwahrscheinlich ist, dass Zeilen herausgefiltert werden, die vom Index getroffen würden. Das Hinzufügen von Indizes ist für Abfragen nicht unbedingt von Vorteil.

Jetzt können Sie das Muster erkennen: Wenn der Benutzer sowohl aktive als auch inaktive Ergebnisse finden möchte, können wir eine IN-Abfrage verwenden. Wir haben viele dieser Listenabfragen hinzugefügt. Eine Problemumgehung besteht darin, für jede Abfragekombination separate Indizes zu erstellen. Wir könnten beispielsweise Indizes für (aktiv, Geschlecht, Land, Alter), (aktiv, Land, Alter), (Geschlecht, Land, Alter) und (Land, Alter) haben. Obwohl ein solcher Index für bestimmte Abfragen die bessere Wahl sein kann, ist diese Strategie möglicherweise aufgrund der negativen Auswirkungen der Pflege dieser Kombinationen und des dafür erforderlichen zusätzlichen Speicherplatzes nicht sinnvoll.

Dies ist ein Fall, in dem eine Änderung am Optimierer die Indexoptimierung erheblich beeinträchtigen kann. Wenn Index-Scans in zukünftigen MySQL-Versionen tatsächlich entfallen können, ist es möglicherweise möglich, mehrere Bereichsbedingungen auf einem Index anzuwenden. In diesem Fall müssen wir dieses Problem nicht mehr mit IN-Abfragen lösen.

Sortierung optimieren

Das letzte Thema ist das Sortieren. Die Ergebnisse kleiner Datenmengen können mithilfe von Filesort schnell sortiert werden. Was aber, wenn Millionen von Datenzeilen vorhanden sind? Zum Beispiel, wenn in der WHERE-Bedingung nur das Geschlecht angegeben ist.

Für solche Szenarien mit geringer Filterung können wir spezifische Indizes zum Sortieren hinzufügen. Beispielsweise könnte ein Index für (Geschlecht, Bewertung) für die folgende Abfrage verwendet werden:

SELECT <Spalten> FROM Profile WHERE Geschlecht='M' ORDER BY Bewertung LIMIT 10;

Diese Abfrage verfügt sowohl über eine Sortier- als auch eine LIMIT-Klausel und kann ohne Index langsam sein. Selbst mit einem Index kann diese Abfrage langsam sein, wenn die Benutzeroberfläche paginierte Abfragen hat und die Seitenzahl nicht nahe am Anfang steht. Im folgenden Beispiel ergeben ORDER BY und LIMIT eine schlechte Kombination:

SELECT <Spalten> FROM Profile WHERE Geschlecht='M' ORDER BY Bewertung LIMIT 100000, 10;

Selbst mit Indizes können Abfragen wie diese schwerwiegende Probleme verursachen. Dies liegt daran, dass eine hohe Schiefe dazu führt, dass eine große Datenmenge verworfen wird und das Scannen lange dauert. Denormalisierung, Vorberechnung und Zwischenspeicherung können das Problem solcher Abfragen möglicherweise lösen. Eine bessere Strategie besteht darin, die Seiten einzuschränken, die Benutzer abfragen können. Das Benutzererlebnis wird dadurch wahrscheinlich nicht beeinträchtigt, da sich niemand wirklich für Seite 10.000 mit Suchergebnissen interessiert.

Eine weitere gute Strategie besteht in der Verwendung abgeleiteter Join-Abfragen. Auf diese Weise können wir mithilfe von abdeckenden Indizes die Primärschlüsselspalten und anschließend die Datenzeilen abrufen. Sie können alle Spalten, die Sie abrufen müssen, kombinieren. Dadurch verringert sich der Arbeitsaufwand von MySQL beim Sammeln der Daten, die verworfen werden müssen. Hier ist ein Beispiel:

SELECT <Spalten> FROM Profile INNER JOIN (
  SELECT <Primärschlüsselspalten> FROM Profile
  WO x.sex='M' ORDER BY Bewertungsgrenze 100000, 10
AS x USING(<Primärschlüsselspalten>);

Oben finden Sie Einzelheiten dazu, wie MySQL Datentabellenindizes erstellt. Weitere Informationen zum Erstellen von Datentabellenindizes mit MySQL finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!