Detaillierte Erläuterung des MySQL-Indexprinzips und der Abfrageoptimierung

Indexklassifizierung 1. Gewöhnlicher Indexindex: Suche beschleunigen 2. Eindeutiger Index Primärschlüsselindex: Primärschlüssel: Suche beschleunigen + Einschränkung (nicht leer und eindeutig)
    Eindeutiger Index: eindeutig: Suche beschleunigen + Einschränkung (eindeutig)
3. Kombinierter Index - Primärschlüssel (ID, Name): Kombinierter Primärschlüsselindex - eindeutig (ID, Name): Kombinierter eindeutiger Index - Index (ID, Name): Kombinierter normaler Index 4. Volltextindex Volltext: Dieser ist am effektivsten, wenn nach sehr langen Artikeln gesucht wird.
5. Räumlicher Index: einfach verstehen, fast nie verwendet

1 Sie erstellen beispielsweise ein Mitgliedskartensystem für ein Einkaufszentrum.
 2 
 3 Dieses System hat eine Mitgliedertabelle 4 mit den folgenden Feldern:
 5 Mitgliedsnummer INT
 6 Mitgliedsname VARCHAR(10)
 7 Mitglieds-ID-Nummer VARCHAR(18)
 8 Telefonnummer des Mitglieds VARCHAR(10)
 9 Mitgliedsadresse VARCHAR(50)
10 Anmerkungen der Mitglieder TEXT
11 
12 Anschließend wird diese Mitgliedsnummer als Primärschlüssel verwendet, mit PRIMARY
13 Wenn Sie einen Index für Mitgliedsnamen erstellen möchten, dann ist es ein normaler INDEX
14 Wenn Sie einen Index für die Mitglieds-ID-Nummer erstellen möchten, können Sie UNIQUE (eindeutig, keine Duplizierung zulässig) wählen.
15 
16 #Darüber hinaus gibt es einen Volltextindex, nämlich FULLTEXT
17 Mitgliedsnotizeninformationen. Wenn Sie einen Index erstellen müssen, können Sie die Volltextsuche auswählen.
18 funktioniert am besten bei der Suche nach sehr langen Artikeln.
19 wird für kürzere Texte verwendet. Wenn es nur ein oder zwei Zeilen sind, funktioniert auch ein normaler INDEX.
20 Tatsächlich verwenden wir für die Volltextsuche jedoch nicht den mit MySQL gelieferten Index, sondern wählen Software von Drittanbietern wie Sphinx, die speziell für die Volltextsuche gedacht ist.
einundzwanzig 
22 #Andere Indizes wie der räumliche Index SPATIAL, verstehen Sie sie einfach, es gibt fast keine Anwendungsszenarien, in denen nicht jeder Index verwendet wird

#Wenn wir den obigen Index erstellen, können wir den Indextyp dafür angeben. Es gibt zwei Arten von Hash-Typ-Indizes: schnelle Einzelabfrage und langsame Bereichsabfrage. Btree-Typ-Index: B+-Baum, je mehr Schichten, desto exponentieller Anstieg des Datenvolumens (wir verwenden ihn, weil InnoDB ihn standardmäßig unterstützt)
#Verschiedene Speicher-Engines unterstützen unterschiedliche Indextypen. InnoDB unterstützt Transaktionen, Zeilensperren, B-Tree, Volltext und andere Indizes, aber keine Hash-Indizes.
MyISAM unterstützt keine Transaktionen, aber Sperren auf Tabellenebene, B-Baum, Volltext und andere Indizes, jedoch keine Hash-Indizes.
Der Speicher unterstützt keine Transaktionen, aber Sperren auf Tabellenebene, B-Baum, Hash und andere Indizes, jedoch keine Volltextindizes.
NDB unterstützt Transaktionen, Zeilensperren und Hash-Indizes, jedoch keine B-Baum-, Volltext- und anderen Indizes.
Das Archiv unterstützt keine Transaktionen, aber Sperren auf Tabellenebene. B-Tree-, Hash-, Volltext- und andere Indizes werden nicht unterstützt.

1 #Methode 1: Beim Erstellen einer Tabelle 2 CREATE TABLE Tabellenname (
 3 Feldname 1 Datentyp [Integritätsbeschränkungen…],
 4 Feldname 2 Datentyp [Integritätsbeschränkungen…],
 5 [EINZIGARTIG | VOLLSTÄNDIGER TEXT | RÄUMLICH] INDEX | SCHLÜSSEL
 6 [Indexname] (Feldname [(Länge)] [ASC | DESC]) 
 7 );
 8 
 9 
10 #Methode 2: CREATE erstellt einen Index für eine vorhandene Tabelle 11 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX Indexname 12 ON Tabellenname (Feldname [(Länge)] [ASC | DESC]);
13 
14 
15 #Methode 3: ALTER TABLE Erstellen Sie einen Index für eine vorhandene Tabelle 16 ALTER TABLE Tabellenname ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
17 Indexname (Feldname [(Länge)] [ASC | DESC]);
18                              
19 #Index löschen: DROP INDEX Indexname ON Tabellenname;
Syntax zum Erstellen/Löschen eines Indexes

Nutzen Sie die Hilfedokumente, um
Hilfe beim Erstellen des Index
==================
1. Erstellen Sie einen Index - erstellen Sie ihn beim Erstellen der Tabelle (einige Punkte sind zu beachten)
    Tabelle s1 erstellen(
    id int, #Sie können hier den Primärschlüssel hinzufügen
    #id int index #Sie können auf diese Weise keinen Index hinzufügen, da der Index nur ein Index und keine Einschränkung ist.
    #Sie können beim Definieren eines Felds, wie etwa eines Primärschlüssels oder einer eindeutigen Einschränkung, keinen Indexnamen char(20) hinzufügen.
    Alter int,
    E-Mail varchar(30)
    #Primärschlüssel (ID) #Sie können auch einen Index (ID) hinzufügen. #Sie können ihn so hinzufügen);
    - Erstellen Sie nach dem Erstellen der Tabelle einen Indexnamen auf s1 (Name); #Fügen Sie einen gemeinsamen Index hinzu. Erstellen Sie ein eindeutiges Alter auf s1 (Alter). Fügen Sie einen eindeutigen Index hinzu. Ändern Sie die Tabelle s1. Fügen Sie den Primärschlüssel (ID) hinzu. #Fügen Sie einen Wohnungs- und Bauindex hinzu, d. h. fügen Sie dem ID-Feld eine Primärschlüsseleinschränkung hinzu. Erstellen Sie einen Indexnamen auf s1 (ID, Name). #Fügen Sie einen gemeinsamen gemeinsamen Index hinzu. 2. Löschen Sie den Index. Löschen Sie die Index-ID auf s1.
    drop index name on s1; #Einen gemeinsamen Index löschendrop index age on s1; #Einen eindeutigen Index löschen, genau wie bei einem gemeinsamen Index müssen Sie zum Löschen nicht „unique“ vor den Index setzen, Sie können ihn direkt löschenalter table s1 drop primary key; #Den Primärschlüssel löschen (da er gemäß alter hinzugefügt wurde, verwenden wir auch alter, um ihn zu löschen)

#1. Tabelle vorbereiten create table s1(
Ich würde int,
Name varchar(20),
Geschlecht char(6),
E-Mail varchar(50)
);
#2. Erstellen Sie eine gespeicherte Prozedur, um Datensätze in Stapel einzufügen. Trennzeichen $$ #Deklarieren Sie das Endsymbol der gespeicherten Prozedur als $$
Prozedur auto_insert1() erstellen
BEGINNEN
    deklariere i als int default 1;
    während(i<3000000)mache
        in s1-Werte einfügen (i, concat('egon', i), 'männlich', concat('egon', i, '@oldboy'));
        setze i=i+1;
    Ende während;
END$$ #$$Ende-Trennzeichen; #Deklarieren Sie das Semikolon erneut als Endsymbol#3. Zeigen Sie die gespeicherte Prozedur an show create procedure auto_insert1\G 
#4. Rufen Sie die gespeicherte Prozedur call auto_insert1(); auf.

#Kein Index: Scannen Sie von Anfang bis Ende, daher ist die Abfragegeschwindigkeit sehr langsam mysql> select * from s1 where id=333;
+------+---------+--------+----------------+
| ID | Name | Geschlecht | E-Mail |
+------+---------+--------+----------------+
| 333 | egon333 | männlich | [email protected] |
| 333 | egon333 | f | alex333@oldboy |
| 333 | egon333 | f | alex333@oldboy |
+------+---------+--------+----------------+
Zeilen im Satz (0,32 Sek.)
mysql> wähle * von s1, wobei E-Mail = "egon333@oldboy" ist;
....
... Zeilen im Satz (0,36 Sek.)

#1. Stellen Sie sicher, dass Sie einen Index für das Suchbedingungsfeld erstellen, z. B. „select * from t1 where age > 5“; dann müssen Sie einen Index für das Alter hinzufügen. #2. Wenn die Tabelle bereits eine große Datenmenge enthält, ist das Erstellen eines Indexes sehr langsam und nimmt Festplattenspeicher in Anspruch. Einfügen, Löschen und Aktualisieren sind alle sehr langsam. Nur die Abfrage ist schnell. Erstellen Sie z. B. den Index „idx“ auf „s1(id);“ durchsucht alle Daten in der Tabelle und verwendet dann „id“ als Datenelement, um eine Indexstruktur zu erstellen und sie in der Tabelle auf der Festplatte zu speichern.
Nach der Erstellung wird die Abfrage sehr schnell sein #3. Es ist zu beachten, dass der Index der InnoDB-Tabelle in der Datei s1.ibd gespeichert wird, während der Index der Myisam-Tabelle eine separate Indexdatei table1.MYI hat

#Analyse wählen Sie * von s1, wobei ID=123;
Das SQL trifft auf den Index, deckt ihn aber nicht ab.
Verwenden Sie id=123, um den Speicherort der ID auf der Festplatte oder in der Datentabelle in der Indexdatenstruktur zu ermitteln.
Das von uns ausgewählte Feld ist jedoch * und wir benötigen neben der ID noch andere Felder. Dies bedeutet, dass es nicht ausreicht, die ID über die Indexstruktur zu erhalten.
Wir müssen die ID auch verwenden, um die anderen Feldwerte der Zeile zu finden, in der sich die ID befindet, was Zeit in Anspruch nimmt. Wenn wir nur die ID auswählen,
Dieses Problem wird wie folgt behoben: select id from s1 where id=123;
Dies ist ein abdeckender Index. Der Index wird gefunden und die Adresse der ID auf der Festplatte wird direkt aus der Indexdatenstruktur abgerufen. Die Geschwindigkeit ist sehr hoch.

#Indexzusammenführung: Mehrere einspaltige Indizes zusammenführen und #Analyse verwenden:
Wir können Indexzusammenführung verwenden, um alles zu lösen, was kombinierte Indizes tun können, z. B. „create index ne on s1(name,email);#Combined index“ Wir können Indizes für Name und E-Mail separat erstellen. Kombinierte Indizes können Folgendes erreichen:
wählen Sie * aus s1, wobei Name = "Egon" ist;
Wählen Sie * aus s1, wobei Name = "egon" und E-Mail = "adf" ist.
Die Indexzusammenführung kann zu Folgendem führen:
wählen Sie * aus s1, wobei Name = "Egon" ist;
Wählen Sie * aus s1, wobei E-Mail = "adf" ist.
Wählen Sie * aus s1, wobei Name = "egon" und E-Mail = "adf" ist.
Auf den ersten Blick scheint es besser zu sein, den Index zusammenzuführen: es kann mehr Fälle treffen, aber tatsächlich hängt es vom Fall ab. Wenn Name='egon' und Email='adf',
Dann ist die Effizienz des kombinierten Index höher als die der Indexzusammenführung. Wenn es sich um eine Abfrage mit einer einzigen Bedingung handelt, ist es sinnvoller, die Indexzusammenführung zu verwenden.

#1. Das Prinzip der Übereinstimmung des ganz linken Präfixes ist ein sehr wichtiges Prinzip.
Erstellen Sie den Index ix_name_email auf s1 (Name, E-Mail,)
- Ganz linkes Präfix-Match: muss von links nach rechts übereinstimmen. select * from s1 where name='egon'; #ok select * from s1 where name='egon' and email='asdf'; #ok select * from s1 where email='[email protected]'; #Nein MySQL gleicht weiter nach rechts ab, bis es auf eine Bereichsabfrage (>, <, between, like) stößt und den Abgleich beendet.
Wenn beispielsweise a = 1 und b = 2 und c > 3 und d = 4, dann erstellen wir einen Index in der Reihenfolge (a, b, c, d),
Für d ist kein Index erforderlich. Wenn Sie einen Index von (a,b,d,c) erstellen, können Sie alles verwenden. Die Reihenfolge von a,b,d kann beliebig angepasst werden.
#2.= und in können in beliebiger Reihenfolge stehen, z. B. a = 1 und b = 2 und c = 3. Sie können einen (a,b,c)-Index in beliebiger Reihenfolge erstellen. Der MySQL-Abfrageoptimierer hilft Ihnen dabei, ihn in eine Form zu optimieren, die der Index erkennen kann. #3. Versuchen Sie, eine Spalte mit hoher Unterscheidung als Index zu wählen. Die Formel für die Unterscheidung lautet count(distinct col)/count(*).
Gibt den Anteil der Felder an, die nicht wiederholt werden. Je größer der Anteil, desto weniger Datensätze werden gescannt. Der eindeutige Schlüssel hat eine Unterscheidung von 1, während einige Staaten,
Angesichts großer Datenmengen kann der Diskriminierungswert im Feld „Geschlecht“ 0 betragen. Daher könnte sich jemand fragen, ob für dieses Verhältnis ein empirischer Wert vorliegt. Verschiedene Nutzungsszenarien,
Dieser Wert ist ebenfalls schwer zu ermitteln. Im Allgemeinen verlangen wir, dass die zu verknüpfenden Felder über 0,1 liegen, d. h., es werden durchschnittlich 10 Datensätze nach 1 Datensatz durchsucht. #4. Indexspalten können nicht in Berechnungen verwendet werden. Halten Sie die Spalten „sauber“, z. B. from_unixtime(create_time) = '2014-05-29'
Der Index kann nicht verwendet werden. Der Grund ist ganz einfach. Der B+-Baum speichert die Feldwerte in der Datentabelle.
Allerdings muss bei einer Suche die Funktion auf alle zu vergleichenden Elemente angewendet werden, was offensichtlich zu aufwändig ist.
Daher sollte die Anweisung wie folgt geschrieben werden: create_time = unix_timestamp('2014-05-29');

mysql> select * from s1 where id>3 und Name='egon' und E-Mail='[email protected]' und Geschlecht='männlich';
Leeres Set (0,39 Sek.)
mysql> create index idx on s1(id,name,email,gender); #Ganz linkes Präfix nicht befolgt. Abfrage OK, 0 Zeilen betroffen (15,27 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0
mysql> select * from s1 where id>3 und Name='egon' und E-Mail='[email protected]' und Geschlecht='männlich';
Leeres Set (0,43 Sek.)

mysql> Index idx auf s1 löschen;
Abfrage OK, 0 Zeilen betroffen (0,16 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0
mysql> create index idx on s1(name,email,gender,id); #Folgen Sie dem Präfix ganz links. Abfrage OK, 0 Zeilen betroffen (15,97 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0
mysql> select * from s1 where id>3 und Name='egon' und E-Mail='[email protected]' und Geschlecht='männlich';
Leerer Satz (0,03 Sek.)

1 6. Das Präfix ganz links entspricht 2 Index (ID, Alter, E-Mail, Name)
 3 #id muss in der Bedingung erscheinen (solange die ID erscheint, wird die Geschwindigkeit verbessert)
 4 Ich würde
 5 ID-Alter
 6 ID-E-Mail
 7 ID-Name
 8 
 9 E-Mail #Nein, wenn dies der Anfang ist, wird die Geschwindigkeit nicht verbessert. 10 mysql> select count(*) from s1 where id=3000;
11 +---------+
12 | Anzahl(*) |
13 +---------+
14 | 1 |
15 +----------+
16 1 Zeile im Satz (0,11 Sek.)
17 
18 mysql> erstelle Index xxx auf s1 (ID, Name, Alter, E-Mail);
19 Abfrage OK, 0 Zeilen betroffen (6,44 Sek.)
20 Datensätze: 0 Duplikate: 0 Warnungen: 0
einundzwanzig 
22 mysql> wähle count(*) von s1, wobei id=3000;
23 +---------+
24 | Anzahl(*) |
25 +---------+
26 | 1 |
27 +----------+
28 1 Zeile im Set (0,00 Sek.)
29 
30 mysql> wähle count(*) aus s1, wobei Name='egon';
31 +---------+
32 | Anzahl(*) |
33 +---------+
34 | 299999 |
35 +---------+
36 1 Zeile im Satz (0,16 Sek.)
37 
38 mysql> wähle count(*) aus s1, wobei email='[email protected]';
39 +---------+
40 | Anzahl(*) |
41 +---------+
42 | 1 |
43 +---------+
44 1 Zeile im Satz (0,15 Sek.)
45 
46 mysql> wähle count(*) aus s1, wobei id=1000 und email='[email protected]';
47 +----------+
48 | Anzahl(*) |
49 +---------+
50 | 0 |
51 +----------+
52 1 Zeile im Set (0,00 Sek.)
53 
54 mysql> wähle count(*) aus s1, wobei email='[email protected]' und id=3000;
55 +---------+
56 | Anzahl(*) |
57 +---------+
58 | 0 |
59 +----------+
60 1 Zeile im Set (0,00 Sek.)
Erstellen Sie einen gemeinsamen Index, Übereinstimmung ganz links

- wie '%xx'
    Wählen Sie * aus TB1, wo die E-Mail wie „%cn“ aussieht;
    
- Verwenden Sie die Funktion „select * from tb1 where reverse(email) = 'wupeiqi';“
    
- oder
    Wählen Sie * aus tb1, wobei nid = 1 oder Name = „[email protected]“ ist.
    
    Besonderheit: Es ist nur ungültig, wenn es eine Spalte gibt, die in der oder Bedingung nicht indiziert ist. Im Folgenden wird der Index select * from tb1 where nid = 1 or name = 'seven'; verwendet.
            Wählen Sie * aus tb1, wobei nid = 1 oder Name = „[email protected]“ und E-Mail = „alex“ ist.
            
- Typinkonsistenz. Wenn die Spalte vom Typ String ist, muss die Eingabebedingung in Anführungszeichen gesetzt werden, andernfalls ...
    Wählen Sie * aus tb1, wobei E-Mail = 999 ist;
Normaler Index bedeutet nicht, dass der Index nicht verwendet wird - !=
    Wählen Sie * aus tb1, wobei E-Mail != 'Alex' ist.
    Besonderheit: Wenn es sich um einen Primärschlüssel handelt, wird der Index trotzdem verwendet select * from tb1 where nid != 123
->
    Wählen Sie * aus tb1, wo E-Mail > „Alex“
    
    Besonderheit: Wenn der Primärschlüssel oder Index ein Integer-Typ ist, wird der Index trotzdem verwendet select * from tb1 where nid > 123
        wähle * aus tb1, wobei num > 123
        
#Wenn die Sortierbedingung ein Index ist, muss das Auswahlfeld auch ein Indexfeld sein, sonst wird es nicht gefunden - sortieren nach
    Wählen Sie den Namen aus S1, bestellen Sie nach E-Mail-Abstieg;
    Wenn beim Sortieren nach Index das ausgewählte Abfragefeld kein Index ist, wird der Index nicht verwendet. select email from s1 order by email desc;
    Besonderheit: Wenn der Primärschlüssel sortiert ist, wird trotzdem der Index verwendet:
        Wählen Sie * aus TB1, sortiert nach NID Desc;
- Das am weitesten links stehende Präfix des kombinierten Index. Wenn der kombinierte Index lautet: (Name, E-Mail)
    Name und E-Mail -- Index verwenden Name -- Index verwenden E-Mail -- Index nicht verwenden - count(1) oder count(column) statt count(*) macht in MySQL keinen Unterschied - Index xxxx auf tb(title(19)) erstellen #Texttyp, Länge muss angegeben werden

- Vermeiden Sie die Verwendung von „select *“
- count(1) oder count(column) statt count(*)
- Versuchen Sie beim Erstellen einer Tabelle, char statt varchar zu verwenden
- Die Reihenfolge der Felder in der Tabelle ist „Felder mit fester Länge zuerst“ - Zusammengesetzte Indizes anstelle mehrerer einspaltiger Indizes (wenn häufig mehrere Bedingungen für die Abfrage verwendet werden)
- Verwenden Sie möglichst kurze Indizes - Verwenden Sie JOINs anstelle von Unterabfragen
- Beim Verbinden von Tabellen ist auf Konsistenz des Bedingungstyps zu achten - Index-Hashwerte (mit wenigen Duplikaten) sind für die Indizierung nicht geeignet, z.B.: Geschlecht ist nicht geeignet

0. Führen Sie zuerst aus, um zu sehen, ob es wirklich langsam ist, und setzen Sie SQL_NO_CACHE
1. Wenn die Bedingung für die Abfrage einer einzelnen Tabelle vorliegt, sperren Sie die Tabelle mit den Datensätzen mit der Mindestrendite. Dieser Satz bedeutet, dass die Where-Klausel der Abfrageanweisung auf die Tabelle mit der geringsten Anzahl zurückgegebener Datensätze angewendet und die Abfrage gestartet werden soll. Fragen Sie jedes Feld der Tabelle einzeln ab, um zu sehen, welches Feld die höchste Unterscheidungskraft aufweist. 2. Erklären Sie, wie Sie überprüfen, ob der Ausführungsplan mit der Erwartung in 1 übereinstimmt (Starten Sie die Abfrage von der Tabelle mit den wenigsten gesperrten Datensätzen).
3. Verwenden Sie die SQL-Anweisung in der Form „Order by Limit“, um die sortierte Tabelle zu priorisieren. 4. Verstehen Sie die Nutzungsszenarien der Geschäftsseite. 5. Beachten Sie beim Hinzufügen von Indizes die wichtigsten Prinzipien der Indexerstellung. 6. Beobachten Sie die Ergebnisse. Wenn sie nicht den Erwartungen entsprechen, fahren Sie mit der Analyse ab 0 fort.