Erklärung der Rückgabe einer MySQL-Tabelle führt zur Ungültigkeit des Index

Einführung

Wenn die MySQL InnoDB-Engine Datensätze abfragt und keine Indexabdeckung verwenden kann, muss eine Tabellenrückgabeoperation durchgeführt werden, um die erforderlichen Felder des Datensatzes zu erhalten.

Vor der Ausführung von SQL führt MySQL SQL-Optimierung, Indexauswahl und andere Vorgänge durch. MySQL schätzt die für jeden Index erforderlichen Abfragekosten und die erforderlichen Abfragekosten ohne Verwendung des Index und wählt eine Methode aus, von der MySQL glaubt, dass sie die geringsten Kosten für die Ausführung der SQL-Abfrageoperation verursacht. Wenn die Datenmenge in der Tabelle groß ist, schätzt MySQL häufig, dass die Abfragekosten des Tabellenrückgabevorgangs zu hoch sind, was zu einer falschen Indexverwendung führt.

Fall

Das folgende Beispiel erstellt eine Testtabelle in MySQL Version 5.6 und einer Linux-Umgebung mit 1 CPU und 2 GB Speicher und erstellt fast 2 Millionen Datensätze zum Testen.

CREATE TABLE `Gehalt_statisch` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel automatisch inkrementieren',
  `school_id` int(11) NICHT NULL KOMMENTAR 'Schul-ID',
  `student_id` int(11) NOT NULL COMMENT 'Absolventen-ID',
  `Gehalt` int(11) NOT NULL DEFAULT '0' COMMENT 'Abschlussgehalt',
  `Jahr` int(11) NOT NULL COMMENT 'Abschlussjahr',
  Primärschlüssel (`id`),
  SCHLÜSSEL `school_id_key` (`school_id`) MIT BTREE,
  SCHLÜSSEL `Jahresschulschlüssel` (`Jahr`,`Schul-ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Gehaltsstatistik für Hochschulabsolventen';

Trennzeichen //
PROZEDUR ERSTELLEN init_salary_static() 
BEGINNEN 
	DECLARE Jahr INT;
	DECLARE schid INT;
	DECLARE stud INT;
	SET-Jahr = 2000;
	WÄHREND Jahr < 2020 DO
		TRANSAKTION STARTEN; 
		SET schid = 1;
		WHILE schid < 100 DO
			SETZE Student = 1;
			WÄHREND des Studiums < 1000 DO
				Füge in salary_static(school_id,student_id,salary,year) Werte ein (schid,stuid,floor(rand()*10000),year);
				SET stuid = stuid + 1;
			ENDE WÄHREND;
			SET schid = schid + 1;
		ENDE WÄHREND;
		SET Jahr = Jahr + 1;
		BEGEHEN; 
	ENDE WÄHREND;
ENDE //
Trennzeichen ;
rufen Sie init_salary_static() auf;

Nachdem die Testdaten erstellt wurden, führen Sie die folgende SQL-Anweisung für eine statistische Abfrage aus.

Wählen Sie „school_id,avg(salary)“ aus „salary_static“, wobei die Jahre zwischen 2016 und 2019 nach „school_id“ gruppiert sind.

Es wird erwartet, dass SQL den Index year_school_key für die Abfrage verwendet. Über den Befehl „explain“ lässt sich jedoch feststellen, dass SQL den Index school_id_key verwendet. Und da der falsche Index verwendet wird, führt SQL einen vollständigen Tabellenscan durch, was zu einer Abfragezeit von 7 Sekunden führt.

Nachdem die Verwendung des Index „year_school_key“ für die Abfrage erzwungen wurde, wurde festgestellt, dass die Abfragezeit des SQL deutlich auf 0,6 Sekunden reduziert wurde, was 10-mal kürzer ist als die Zeit des Index „school_id_key“.

Wählen Sie „school_id,avg(salary)“ aus „salary_static“, erzwingen Sie den Index „year_school_key“, wobei die Jahre zwischen 2015 und 2019 nach „school_id“ gruppiert werden.

analysieren

Verwenden Sie die Optimierungsablaufverfolgung von MySQL (unterstützt von MySQL 5.6), um den SQL-Ausführungsplan zu analysieren:

SET optimizer_trace="aktiviert=ein";
Wählen Sie „school_id,avg(salary)“ aus „salary_static“, wobei die Jahre zwischen 2016 und 2019 nach „school_id“ gruppiert sind.
WÄHLEN SIE * AUS INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Das Ausgabeergebnis ist eine JSON-Datei, die den Ausführungsplan des SQL-Optimierungsprozesses und des Indexauswahlprozesses in MySQL zeigt.

Konzentrieren Sie sich auf den Inhalt unter „range_analysis“ im JSON des Ausführungsplans, der die Indexauswahl während des Where-Range-Abfrageprozesses zeigt. table_scan gibt einen vollständigen Tabellenscan an, der schätzungsweise 1.973.546 Datensätze scannen muss. Da ein vollständiger Tabellenscan jedoch einen gruppierten Index verwendet und ein sequentieller IO-Lesevorgang ist, sind die Abfragekosten für jeden Datensatz sehr gering und die endgültigen berechneten Abfragekosten betragen 399.741. range_scan_alternatives gibt eine Bereichsabfrage unter Verwendung eines Indexes an. Der Index year_school_key schätzt, dass 812.174 Datensätze gescannt werden müssen. Aufgrund der Notwendigkeit, zur Tabelle zurückzukehren, sind jedoch zufällige IO-Lesevorgänge erforderlich. Die endgültigen berechneten Abfragekosten betragen 974.610. Daher wird für den Where-Abfrageprozess letztendlich anstelle des Indexes der vollständige Tabellenscan gewählt.

"Bereichsanalyse": {
  "Tabellenscan": {
	"Zeilen": 1973546,
	"Kosten": 399741
  },
  "potenzielle_Bereichsindizes": [
	{
	  "index": "PRIMÄR",
	  "verwendbar": falsch,
	  "Ursache": "nicht zutreffend"
	},
	{
	  "index": "Schul-ID-Schlüssel",
	  "verwendbar": wahr,
	  "Schlüsselteile": [
		"Schul-ID",
		"Ausweis"
	  ]
	},
	{
	  "index": "Jahresschulschlüssel",
	  "verwendbar": wahr,
	  "Schlüsselteile": [
		"Jahr",
		"Schul-ID",
		"Ausweis"
	  ]
	}
  ],
  "setup_range_conditions": [
  ],
  "Gruppenindexbereich": {
	"ausgewählt": falsch,
	"Ursache": "nicht anwendbare Aggregatfunktion"
  },
  "Analysebereich_Alternativen": {
	"Bereichsscan-Alternativen": [
	  {
		"index": "Jahresschulschlüssel",
		"Bereiche": [
		  "2016 <= Jahr <= 2019"
		],
		"index_dives_for_eq_ranges": wahr,
		"rowid_ordered": falsch,
		"using_mrr": falsch,
		"index_only": falsch,
		"Zeilen": 812174,
		"Kosten": 974610,
		"ausgewählt": falsch,
		"Ursache": "Kosten"
	  }
	],
	"analyzing_roworder_intersect": {
	  "verwendbar": falsch,
	  "Ursache": "zu wenige Zeilenreihenfolgescans"
	}
  }
}

Der Abfragekostenwert kann hier manuell berechnet werden: Kosten = E/A-Kosten (eine Kosten für jeden Lesevorgang der Datensatzseite, jede Kosten betragen 1,0) + CPU-Kosten (eine Kosten für jeden Datensatz, jede Kosten betragen 0,2).

Kosten für die vollständige Tabellenscan-Abfrage

Wenn table_scan für die vollständige Tabellenüberprüfung verwendet wird, müssen schätzungsweise 1973546 Datensätze überprüf werden. Der Befehl „show table status like "salary_static"“ zeigt, dass die Gesamtzahl der Datensätze in der Tabelle 82411520 Bytes (Datenlänge) beträgt. Jede Datensatzseite in innodb ist 16 KB groß, was bedeutet, dass bei einer vollständigen Tabellenüberprüfung 82411520/1024/16 = 5030 Datensatzseiten gelesen werden müssen.

• I/O-Kosten

5030 * 1,0 = 5030

• CPU-Kosten

1973546 * 0,2 = 394709,2

• Gesamtabfragekosten

5030 + 394709,2 = 399739,2

Kosten der Indexabfrage

Es wird geschätzt, dass beim Indizieren von year_school_key 812.174 Datensätze gescannt werden müssen. Um diesen Index zu verwenden, müssen Sie zuerst die Zeilen-ID über den Index abfragen und dann über die Zeilen-ID zur Tabelle zurückkehren. MySQL berücksichtigt, dass jede Tabellenrückgabe separate I/O-Kosten verursacht

• CPU-Kosten

812174 * 0,2 = 162434,8

• I/O-Kosten

812174 * 1,0 = 812174

• Gesamtabfragekosten

162434,8 + 812174 = 974608,8

Als nächstes ist auf reconsidering_access_paths_for_index_ordering zu achten, dadurch wird die Sortierung am Ende nochmals optimiert. Der Index school_id_key wird hier ausgewählt und verhindert den vollständigen Tabellenscan, der in der obigen Bedingung ausgewählt wurde: „plan_changed“: true. Weitere Einzelheiten finden Sie unter „Group-by-Optimierung“.

{
    "Zugriffspfade für die Indexreihenfolge überdenken": {
      "Klausel": "GROUP BY",
      "index_order_summary": {
        "Tabelle": "`Gehalt_statisch`",
        "index_provides_order": wahr,
        "order_direction": "aufsteigend",
        "index": "Schul-ID-Schlüssel",
        "plan_changed": wahr,
        "Zugriffstyp": "Indexscan"
      }
    }
}

Tatsächlich gibt es auch einen Fehler bei der Sortierindexoptimierung. Weitere Informationen finden Sie unter Fehler Nr. 93845.

Optimierung

Durch die Analyse des SQL-Ausführungsprozesses können wir feststellen, dass der falsche Index ausgewählt wurde, weil die Index-Back-Tabelle year_school_key zu viele Datensätze enthält, was dazu führt, dass die geschätzten Abfragekosten höher sind als beim vollständigen Tabellenscan, und letztendlich der falsche Index ausgewählt wird.

Um die Ausführungszeit von SQL zu verkürzen, besteht die nächste Optimierungslösung darin, die Tabellenrückgabevorgänge von SQL zu reduzieren, d. h. SQL eine Index-Abdeckung durchführen zu lassen. Die SQL-Anweisung umfasst nur drei Felder: school_id, salary und year. Daher wird ein gemeinsamer Index dieser drei Indizes erstellt und auf die Reihenfolge dieser drei Felder im gemeinsamen Index geachtet: Die Where-Filter-Anweisung wird zuerst ausgeführt, sodass das Feld year im gemeinsamen Index an erster Stelle steht. Die Group-By-Anweisung ist im Wesentlichen dieselbe wie die Order-By-Anweisung und wird daher nach der Where-Anweisung platziert, d. h. an zweiter Stelle im gemeinsamen Index. Das Gehalt wird nur am Ende des gemeinsamen Index platziert, um die Anzahl der Tabellenrückgaben zu verringern.

CREATE INDEX year_school_salary_key ON salary_static (Jahr, Schul-ID, Gehalt);

Nach dem Erstellen des gemeinsamen Indexes lauten die Ergebnisse nach der Ausführung der SQL-Anweisung wie folgt: Die Abfrage dauerte nur 0,2 Sekunden, was 35-mal weniger Zeit ist als die Zeit für den school_id_key-Index.

Berechnung der Rücklaufquote

Das oben genannte Problem besteht darin, dass zu viele SQL-Abfragen gleichzeitig erfolgen, wodurch der Aufwand für die Rückkehr zur Tabelle zu hoch wird. Tatsächlich kann der kritische Wert des obigen Phänomens berechnet werden:

Angenommen, die Größe einer Datensatzzeile beträgt a Bytes, die Anzahl der Datensätze in der Tabelle ist b und die kritische Anzahl der Datensätze ist c. Dann beträgt die Anzahl der Datensatzseiten in der Tabelle b*a/1024/16.

Abfragekosten des vollständigen Tabellenscans = E/A-Kosten + CPU-Kosten = b*a/1024/16 * 1,0 + b * 0,2


Abfragekosten des Indexscans = E/A-Kosten + CPU-Kosten = c * 1,0 + c * 0,2 = c * 1,2


b*a/1024/16 * 1,0 + b * 0,2 = c * 1,2
Kritisches Verhältnis = c/b 
= (a/1024/16 + 0,2)/1,2
= a * 5E-5 + 0,1667

Das heißt, wenn eine SQL-Abfrage mehr als etwa 17 % der Datensätze in der Tabelle umfasst und kein abdeckender Index verwendet werden kann, ist der Aufwand zum Zurückgeben des Index an die Tabelle zu hoch und es wird ein vollständiger Tabellenscan gewählt. Und dieses Verhältnis erhöht sich leicht, wenn die Bytegröße einer einzelnen Zeile zunimmt.

Dies ist das Ende dieses Artikels über den Fall der Index-Ungültigkeit, der durch die Rückgabe einer MySQL-Tabelle verursacht wurde. Weitere relevante Inhalte über die Index-Ungültigkeit, die durch die Rückgabe einer MySQL-Tabelle verursacht wurde, finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!