Analyse der Gründe, warum der Index bei der Suche im MySql-Bereich nicht wirksam wird

1 Problembeschreibung

Dieser Artikel sortiert den erstellten zusammengesetzten Index und nimmt die nicht indexierten Felder im Datensatz. Es stellt sich heraus, dass der Index nicht wirksam ist. Beispielsweise gibt es die folgende Tabelle und die DDL-Anweisung lautet:

CREATE TABLE `Mitarbeiter` (
 `emp_no` int(11) NICHT NULL,
 `Geburtsdatum` Datum NICHT NULL,
 `Vorname` varchar(14) NICHT NULL,
 `Nachname` varchar(16) NICHT NULL,
 `Geschlecht` enum('M','F') NICHT NULL,
 `hire_date` Datum NICHT NULL,
 `Alter` int(11) NICHT NULL,
 PRIMÄRSCHLÜSSEL (`emp_no`),
 SCHLÜSSEL `unique_birth_name` (`Vorname`,`Nachname`) USING BTREE
 )ENGINE=InnoDB STANDARD-CHARSET=utf8;

Der zusammengesetzte Index ist unique_birth_name (first_name,last_name) . Verwenden Sie die folgende Anweisung:

ERKLÄREN AUSWÄHLEN
 Geschlecht
AUS
 Mitarbeiter
BESTELLEN BIS
 Vorname,
 Nachname 

Gemäß der obigen Abbildung: Typ: Alles und Extra: Dateisortierung verwenden, der Index ist nicht wirksam.

Setzen Sie das Experiment fort und schreiben Sie die Abfrageanweisung weiter um, um eine Bereichssuche hinzuzufügen:

ERKLÄREN AUSWÄHLEN
 Geschlecht
AUS
 Mitarbeiter
WO Vorname > 'Leah'
BESTELLEN BIS
 Vorname,
 Nachname

Der Ausführungsplan ist in der folgenden Abbildung dargestellt:

Die Ergebnisse unterscheiden sich hier nicht von der ersten SQL-Analyse. Experimentieren Sie weiter.

Schreiben Sie die SQL-Anweisung neu:

ERKLÄREN AUSWÄHLEN
 Geschlecht
AUS
 Mitarbeiter
WO Vorname > 'Tzvetan'
BESTELLEN BIS
 Vorname,
 Nachname 

An diesem Punkt funktioniert der Index überraschenderweise.

2 Problemanalyse

An dieser Stelle wagen wir eine gewagte Vermutung:

Wenn Sie zum ersten Mal eine SQL-Analyse durchführen, werden nach der ersten Sortierung immer noch die Daten der gesamten Tabelle abgerufen. Wenn jedes Geschlecht anhand des im zusammengesetzten Index enthaltenen Primärschlüssels durchsucht und gespleißt wird, ist dies natürlich sehr ressourcen- und zeitaufwändig. MySQL würde so etwas Dummes nicht tun. Es ist besser, die gesamte Tabelle direkt zu scannen und die einzelnen gescannten Daten mit den per Order by erhaltenen temporären Daten zu verknüpfen, um die erforderlichen Daten zu erhalten.

Um die Richtigkeit der obigen Idee zu überprüfen, analysieren wir drei SQL-Anweisungen.

Die durch das erste SQL basierend auf dem zusammengesetzten Index erhaltene Datenmenge beträgt: 300024 , das sind die Daten der gesamten Tabelle

WÄHLEN
 COUNT(Vorname)
AUS
 Mitarbeiter
BESTELLEN BIS
 Vorname,
 Nachname 

Die durch das zweite umgeschriebene SQL basierend auf dem zusammengesetzten Index erhaltene Datenmenge beträgt: 159149 , also die Hälfte des gesamten Tabellendatenvolumens.

WÄHLEN
 COUNT(Vorname)
AUS
 Mitarbeiter
WO Vorname > 'Leah'
BESTELLEN BIS
 Vorname,
 Nachname 

Die durch das dritte umgeschriebene SQL basierend auf dem zusammengesetzten Index erhaltene Datenmenge beträgt 36731 , was 1/10 des gesamten Tabellendatenvolumens entspricht.

WÄHLEN
  COUNT(Vorname)
AUS
  Mitarbeiter
WO Vorname > 'Tzvetan'
BESTELLEN BIS
  Vorname,
  Nachname 

Durch Vergleich wurde festgestellt, dass die Datenmenge, die durch das zweite neu geschriebene SQL basierend auf dem zusammengesetzten Index erhalten wurde, die Hälfte der gesamten Tabellendatenmenge betrug. An diesem Punkt ist MySQL noch nicht so weit gekommen, dass Indizes für sekundäre Suchvorgänge verwendet werden können. Die Datenmenge, die durch das dritte umgeschriebene SQL basierend auf dem zusammengesetzten Index erhalten wird, beträgt 1/10 des gesamten Tabellendatenvolumens und erreicht das Niveau von MySQL, das den Index für die sekundäre Suche verwendet. Daher ist aus dem Ausführungsplan ersichtlich, dass das dritte umgeschriebene SQL den Index verwendet hat.

3 Fazit

Ob MySQL eine sekundäre Suche basierend auf dem Primärschlüssel durchführt, der beim ersten Index abgefragt wurde, hängt auch von der abgefragten Datenmenge ab. Wenn die Datenmenge nahe an der Datenmenge der gesamten Tabelle liegt, wird ein vollständiger Tabellenscan durchgeführt. Andernfalls wird eine sekundäre Suche basierend auf dem Primärschlüssel durchgeführt, der beim ersten Mal abgefragt wurde.

Damit ist dieser Artikel zur Analyse der Ursachen des Problems, dass der Index während der MySql-Bereichssuche nicht wirksam wird, abgeschlossen. Weitere relevante Inhalte zum Problem, dass der Index während der MySql-Bereichssuche nicht wirksam wird, finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den verwandten Artikeln weiter unten. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!