MySQL etabliert eine effiziente Indexbeispielanalyse

Dieser Artikel beschreibt anhand von Beispielen, wie Sie effiziente Indizes in MySQL erstellen. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Wie erstellt man einen idealen Index?

• Abfragehäufigkeit
• Diskriminierung
• Indexlänge
• Felder überschreiben

Diskriminierung

Bei einer angenommenen Nutzerzahl von 1 Million beträgt das Geschlechterverhältnis grundsätzlich 500.000 männlich und 500.000 weiblich, die Differenzierung ist also gering.

Kurze Länge

Die Indexlänge wirkt sich direkt auf die Größe der Indexdatei sowie auf die Geschwindigkeit des Hinzufügens, Löschens und Änderns aus und wirkt sich indirekt auf die Abfragegeschwindigkeit aus (belegt mehr Speicher).

Hohe Unterscheidungskraft, kurze Länge

Frage: Was passiert, wenn wir die Unterscheidungsfähigkeit hoch und die Länge klein machen?

Antwort: Einen Index können Sie erstellen indem Sie die Werte in der Spalte von links nach rechts abschneiden.

(1) Je kürzer der Schnitt, desto höher die Wiederholung, desto geringer die Differenzierung und desto schlechter der Indizierungseffekt. (2) Je länger der Schnitt, desto geringer die Wiederholung, desto höher die Differenzierung und desto besser der Indizierungseffekt, aber desto größer die Auswirkung – das Hinzufügen und Löschen ist langsam und die Abfragegeschwindigkeit wird beeinträchtigt.

Daher müssen wir ein Gleichgewicht zwischen Unterscheidungsvermögen und Länge finden. Konventionelle Methode: Unterschiedliche Längen abschneiden und deren Unterscheidungsvermögen testen.

Angenommen, wir haben eine Tabelle: eine Vokabeltabelle für Englisch Level 4, die 13.324 Datensätze enthält. Wie fügen wir dem Namensfeld einen Index hinzu?

Wie berechnet man Diskriminierung?

Extrahieren Sie die erste nicht wiederholte Zahl eines Wortes:

Wählen Sie Anzahl (eindeutig links (Name, 1)) aus dem Wörterbuch aus.

Gesamtmenge:

wähle count(*) aus dict

Unterscheidung: eindeutige Nummer/Gesamtnummer, das SQL-Statement lautet:

Wählen Sie (Wählen Sie Anzahl (eindeutig links (Name, 1)) aus dem Wörterbuch) / (Wählen Sie Anzahl (*) aus dem Wörterbuch) als Rate;

Befolgen Sie dann diese Schritte, um die Unterscheidung entsprechend anderer Längen herauszufinden. Wenn wir uns dieses Diagramm ansehen, können wir sehen, dass bei einer Länge von 11 die Wiederholungsrate nur 1 % beträgt. Wir können die Erstellung eines 11-Bit-Index in Betracht ziehen.

Tabelle ändern, Indexnamen hinzufügen, Name(11);

Das linke Präfix ist schwer zu unterscheiden

Tipps zum Erstellen von Indizes für Spalten, deren linkes Präfix nicht leicht zu unterscheiden ist

Wie URL-Spalte

http://www.baidu.com
http://www.web-bc.cn

Die ersten 11 Zeichen der Spalte sind gleich und schwer zu unterscheiden. Sie können die folgenden beiden Methoden verwenden, um dieses Problem zu lösen.

(1) Speichern Sie den Spalteninhalt in umgekehrter Reihenfolge und erstellen Sie einen Index

moc.udiab.www//:ptth
nc.cb-bew.www//://ptth

Auf diese Weise hat das linke Präfix eine große Unterscheidungskraft

(2) Pseudo-Hash-Index-Effekt

Speichern Sie sowohl URL- als auch URL_Hash-Spalten

#Tabelle erstellen create table t10 (
id int Primärschlüssel,
URL char(60) nicht null Standard ''
);
#In t10-Werte einfügen
(1,'http://www.baidu.com'),
(2,'http://www.sina.com'),
(3,'http://www.sohu.com.cn'),
(4,'http://www.onlinedown.net'),
(5, „http://www.gov.cn“);
#Ändern Sie die Tabellenstruktur und fügen Sie die Spalte „urlcrc“ hinzu. alter table t10 add urlcrc int unsigned not null;

Fügen Sie beim Speichern den der URL entsprechenden CRC32-Code in die Datenbank ein und erstellen Sie dann einen Index gemäß dem Feld „urlcrc“. Bei der Suche konvertieren wir die entsprechende URL in der Business-Schicht zur Suche in CRC32 und können dann den Index verwenden.

Da das Ergebnis von CRC eine vorzeichenlose 32-Bit-Int-Zahl ist, kommt es zu Duplikaten, wenn die Daten 4 Milliarden überschreiten, aber es lohnt sich. (Die Indexlänge beträgt int4 Bytes)

Mehrspaltige Indizes

Überlegungen zu mehrspaltigen Indizes - Spaltenabfragehäufigkeit, Spaltendifferenzierung und unbedingte Kombination mit tatsächlichen Geschäftsszenarien

Am Beispiel von ecshop sind cat_id und brand_id in der Warentabelle mehrspaltige Indizes. Aus der Perspektive der Differenzierung hat brand_id eine höhere Differenzierung. Im tatsächlichen Geschäft des Einkaufszentrums wählen Kunden jedoch im Allgemeinen zuerst große Kategorien -> kleine Kategorien -> Marken und entscheiden sich schließlich für die Erstellung von zwei Indizes:

(1) index(Kat.-ID,Marken-ID)
(2) index(Kat.-ID,Shop-Preis)

Sie können sogar (3) Index (cat_id, brand_id, shop_price) hinzufügen, 3 redundante Indizes

Aber die ersten beiden Spalten in (3) sind dieselben wie die ersten beiden Spalten in (1), also können wir (1) entfernen und zwei Indizes erstellen.

index(cat_id,price) und index(cat_id,brand_id,shop_price);

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „Zusammenfassung der Kenntnisse im Bereich MySQL-Indexoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Allgemeinfunktionen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Protokolloperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Transaktionsoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-gespeicherte Prozeduren“ und „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Datenbanksperren“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.