Eine Fallstudie zum gründlichen Verständnis der korrekten Verwendung des MySQL-InnDB-Joint-Index

Es gibt ein Unternehmen, das die 5 zuletzt überprüften Daten abfragt.

Wählen Sie `id`, `title`
VON `th_content`
WO `audit_time` < 1541984478
 UND `status` = 'ONLINE'
ORDER BY `audit_time` DESC, `id` DESC
GRENZE 5;

Beim Überprüfen des Überwachungsstatus zu diesem Zeitpunkt lag die CPU-Auslastung bei über 100 %. show processlist zeigte, dass sich viele ähnliche Abfragen im Status create sort index befanden.

Sehen Sie sich die Struktur der Tabelle an

CREATE TABLE `th_content` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT '' KOMMENTAR 'Inhaltstitel',
 `content` mediumtext CHARACTER SET utf8 NOT NULL COMMENT 'Hauptinhalt',
 `audit_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Audit-Zeit',
 `last_edit_time` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Letzte Bearbeitungszeit',
 `status` enum('ERSTELLT','PRÜFEN','IGNORIERT','ONLINE','OFFLINE') ZEICHENSATZ utf8 NICHT NULL STANDARD 'ERSTELLT' KOMMENTAR 'Informationsstatus',
 Primärschlüssel (`id`),
 SCHLÜSSEL `idx_at_let` (`Audit_Time`,`letzte_Bearbeitungszeit`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;

Der Index hat einen gemeinsamen Index mit audit_time auf der linken Seite und keinen Index für status .

Analysieren Sie die Logik der obigen SQL-Ausführung:

• Suche nach allen Primärschlüssel-IDs, die kleiner sind als die Überprüfungszeit aus dem gemeinsamen Index (wenn 1 Million Daten vor diesem Zeitstempel überprüft wurden, werden die Primärschlüssel-IDs der entsprechenden 1 Million Daten aus dem gemeinsamen Index abgerufen)
• Es wäre schön, wenn es in Zukunft eine Optimierung gäbe. Aktuell gibt es: Sortiert die 100 Primärschlüssel-IDs, und dann können im nächsten Schritt der Tabellenrückgabeoperation die Primärschlüssel, die nahe beieinander liegen, in einem Disk-I/O abgerufen werden.
• Gehen Sie nacheinander zur Tabelle zurück, finden Sie 1 Million Zeilen und filtern Sie die Zeilen mit dem Status „ONLINE“ heraus.
• Sortieren Sie abschließend die Abfrageergebnisse (wenn 500.000 Zeilen alle ONLINE sind, sortieren Sie diese 500.000 Zeilen weiter).

Schließlich werden, da die Datenmenge groß ist, obwohl nur 5 Zeilen entnommen werden, gemäß dem Extrembeispiel, das wir gerade angeführt haben, tatsächlich 1 Million Datenzeilen abgefragt und schließlich eine Speichersortierung von 500.000 Zeilen der Datenbank im Speicher durchgeführt.

Es ist also sehr ineffizient.

Um den Abfragevorgang im ersten Schritt zu verdeutlichen, wird ein schematisches Diagramm gezeichnet. Der rosa Teil stellt die Datenzeilen dar, die am Ende in der Tabelle abgefragt werden müssen.

Im Bild habe ich einige Daten gemäß den Indexspeicherregeln gefälscht und ausgefüllt. Wenn etwas nicht stimmt, hinterlassen Sie bitte eine Nachricht, um darauf hinzuweisen. Ich hoffe, dass Sie anhand dieses Bilds die Methode der gemeinsamen Indexspeicherung und Indexabfrage erkennen können.

Verbesserungsidee 1

Bereichssuchen sind mit guten Indizes nicht einfach zu verwenden. Welche Verbesserungen ergeben sich, wenn wir einen gemeinsamen Index für audit_time und status hinzufügen?

ALTER TABLE `th_content` ADD INDEX `idx_audit_status` (`audit_time`, `status`);

mysql> erläutern Sie, wählen Sie „id“, „title“ aus „th_content“, wobei „audit_time“ < 1541984478 und „status“ = „ONLINE“, sortiert nach „audit_time“ desc, „id“ desc-Limit 5;
+----+----------+------------+-------+------------------------------------------+------------------+--------+------+--------+-------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+------------+-------+------------------------------------------+------------------+--------+------+--------+-------------+
| 1 | SIMPLE | th_content | Bereich | idx_at_ft_pt_let,idx_audit_status | idx_audit_status | 4 | NULL | 209754 | Verwenden von „where“ |
+----+----------+------------+-------+------------------------------------------+------------------+--------+------+--------+-------------+

Details: Da audit_time eine Bereichssuche ist, wird der Index der zweiten Spalte nicht verwendet. Nur audit_time kann verwendet werden, daher ist key_len 4. In der folgenden Idee 2 ist key_len dieser beiden Felder immer noch 5.

Lassen Sie uns den Ausführungsprozess nach dem Hinzufügen des Indexes analysieren:

• Suchen Sie den gemeinsamen Index der Zeile mit der größten audit_time , die kürzer ist als die Audit-Zeit vom gemeinsamen Index
• Suchen Sie dann Schritt für Schritt nach unten, da es sich bei < audit_time um eine Bereichssuche handelt und die Werte des zweiten Spaltenindex verstreut sind. Daher müssen Sie nacheinander vorwärts suchen, um die Indexzeilen zu finden, die die Bedingung erfüllen ( status = „ONLINE“), bis Sie die 5. Zeile erhalten.
• Kehren Sie zur Tabelle zurück, um die gewünschten spezifischen Daten abzufragen

Im obigen Diagramm zeigt die rosa Farbe die Zeilen an, die die Indexanforderungen der ersten Spalte erfüllen. Wenn wir die Abfrage einzeln nach vorne ausführen, finden wir drei Datensätze in diesem Blattknoten. Dann müssen wir die Abfrage nach links am vorherigen Blattknoten fortsetzen. Bis 5 Zeilen gefunden sind, die den Datensätzen entsprechen, und schließlich zur Tabelle zurückkehren.

Verbesserungen

Da sich status im Index befindet, ist es beim Filtern von Zeilen mit status „ONLINE“ nicht erforderlich, zur Abfrage in die Tabelle zurückzukehren. Bei der Rückkehr zur Tabelle werden nur 5 Datenzeilen abgefragt, was iops erheblich reduziert.

Nachteile dieses Indexes

Wenn status aller fünf in idx_audit_status gescannten Zeilen ONLINE ist, müssen nur fünf Zeilen gescannt werden.

Wenn nur 4 Zeilen in den ersten 1 Million, die in idx_audit_status gescannt wurden, status ONLINE haben, müssen Sie 1 Million und 1 Zeile scannen, um die erforderlichen 5 Zeilen zu erhalten. Der Index muss eine unbestimmte Anzahl Zeilen scannen.

Verbesserungsidee 2

ALTER TABLE `th_content` DROP INDEX `idx_audit_status`;
ALTER TABLE `th_content` ADD INDEX `idx_status_audit` (`status`, `audit_time`); 

Somit entsteht kein Druck, egal ob beim Sortieren oder beim Zurücklegen an den Tisch.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM. Wenn Sie mehr darüber erfahren möchten, schauen Sie sich bitte die folgenden Links an