Optimierte Aufzeichnung der Verwendung von IN-Datenvolumen in Mysql

Die MySQL-Versionsnummer ist 5.7.28. Tabelle A hat 3,9 Millionen Datensätze und verwendet die InnoDB-Engine. Das Feld mac vom Typ varchar wurde mithilfe von B-tree indiziert. Tabelle B hat nur mehr als 5.000 Datensätze.

Es gibt eine SQL-Anweisung, die wie folgt geschrieben ist:

SELECT * FROM A WHERE mac IN("aa:aa:aa:aa:aa:aa","bb:bb:bb:bb:bb:bb:b",...mehr als 900 Einträge ausgelassen)

Es dauerte 294,428 Sekunden, um die Ergebnisse der Abfrage zu erhalten. Ja, fast 5 Minuten.

Verwenden Sie EXPLAIN zur Analyse von:

Der Zugriffstyp ist Bereich, der Index wurde erreicht und es sind nur 587776 Zeilen vorhanden. Warum dauert die Abfrage so lange?

Die Indizierungsmethode von Mac verwendet B-Tree. Vergleichen wir es mit HASH und fassen es kurz zusammen: Der B-Tree-Index kann für =, >, >=, <, <= und zwischen Berechnungen verwendet werden, während HASH nur Gleichheitsoperationen ausführen und keine Bereichssuchen durchführen kann. Dann ist IN eine äquivalente Operation und beide Indizierungsmethoden sind anwendbar. Ändern Sie in diesem Fall die Indexmethode des Mac in HASH, und die gleiche Abfrage dauert einige Zeit.

Da das Anpassen der Indexmethode die Abfrageleistung der Anweisung nicht wesentlich verbessern kann, kann es nur aus der Anweisung selbst heraus verarbeitet werden. Tatsächlich erkennt jeder mit einem scharfen Auge auf den ersten Blick, dass SELECT * sehr leistungsintensiv ist. Daher fragen wir nur die vom Unternehmen benötigten Felder ab und passen die Anweisung wie folgt an:

SELECT id,mileage FROM A WHERE mac IN("aa:aa:aa:aa:aa:aa","bb:bb:bb:bb:bb:bb:b",...mehr als 900 Einträge ausgelassen)

Es ist kein nennenswerter Anstieg des Zeitaufwandes zu verzeichnen.

Können wir auf die Verwendung von LEFT JOIN verzichten, da die IN-Methode so schwer zu optimieren ist? Die Aussage wird angepasst zu:

Wählen Sie a.id,a.mileage von A a links beitreten B b auf b.mac = a.mac wo b.create_time >= '2020-01-01'

Wenn es länger als 5 Minuten dauert, geben Sie auf.

Wir wissen, dass es keinen offensichtlichen Unterschied zwischen den Auswirkungen von EXISTS und IN gibt, wenn die Anzahl der Bedingungen gering ist. Aber wenn viele Bedingungen vorliegen, ist IN effizienter als EXISTS. Versuchen wir es mit EXISTS:

Wählen Sie ID, Meilenzahl aus A a, wobei „Existiert“ (wählen Sie „mac“ aus B, wobei „Erstellungszeit“ >= „2020-01-01“ und „mac“ = a.mac)

Es dauert auch mehr als 5 Minuten. IN ist tatsächlich effizienter als EXISTS, also habe ich aufgegeben.

Die Schlussfolgerung lautet also: Wenn auf IN ein String mit großen Datenmengen folgt, sollten Sie vorsichtig sein.

Im Projekt verwende ich mac als eindeutigen Bezeichner, um eine entsprechende Tabelle mit ID zu erstellen. In Tabelle A wird mac_id anstelle von mac verwendet und bei der Abfrage IN(1,2,3...) verwendet. Die Effizienz wird verbessert. Derzeit ist auch die Verwendung von NoSQL eine Möglichkeit.

Zusammenfassen

Dies ist das Ende dieses Artikels über die Optimierung von MySQL mit IN für große Datenmengen. Weitere Informationen zur Optimierung von MySQL mit IN für große Datenmengen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!