Kann mir jemand helfen, die Abfrage neu zu schreiben, um die Ausführungszeit zu verkürzen? Die Ausführung dauerte 37 Sekunden.

DELETE FROM storefront_categories 
WHERE userid IN (SELECT userid 
                FROM MASTER 
                where expirydate<'2020-2-4' 
                )

Gleichzeitig dauerte die Ausführung dieser Abfrage nur 4,69 Sekunden.

DELETE FROM storefront_categories 
WHERE userid NOT IN (SELECT userid FROM MASTER)

Die Tabelle storefront_categories enthält 97.000 Datensätze, wobei MASTER 40.000 Datensätze enthält. Wir haben einen Index für das Feld MASTER.expirydate erstellt.

2
Cbpro Ads 19 Feb. 2020 im 14:07

4 Antworten

Beste Antwort

Erwarten Sie beim Löschen von 40-KB-Zeilen einige Zeit. Die Hauptkosten (unter der Annahme einer angemessenen Indizierung und einer anständigen Abfrage) sind der Aufwand für die Transaktionssemantik eines "atomaren" Löschvorgangs. Dies beinhaltet das Erstellen einer Kopie jeder zu löschenden Zeile für den Fall eines Absturzes. Auf diese Weise kann InnoDB die Datenbank auf den Stand vor dem Absturz zurücksetzen.

Wenn Sie 40% einer Tabelle löschen, ist es viel schneller, die Zeilen zu kopieren, um sie in einer anderen Tabelle zu speichern, und dann Tabellen auszutauschen.

Wenn Sie eine große Anzahl von Zeilen löschen (unabhängig vom Prozentsatz), ist es besser, dies in Blöcken zu tun. Und es ist am besten, anhand des PRIMARY KEY durch die Tabelle zu gehen.

Ich diskutiere diese beiden Techniken und andere in http://mysql.rjweb.org/doc .php / deletebig

Wie für die Abfrageformulierung:

  • Es ist versionabhängig; Alte Versionen von MySQL haben bei einigen Geschmacksrichtungen schlechte Arbeit geleistet.
  • NOT IN (SELECT ...) und NOT EXISTS sind in der Regel die schlechtesten Performer.
  • IN (SELECT ...) und / oder EXISTS sind möglicherweise besser.
  • "Multi-Table DELETE ist eine weitere Option. Es funktioniert wie JOIN.
  • (Fazit: Sie haben nicht gesagt, welche Version Sie verwenden. Ich kann nicht vorhersagen, welche Formulierung die beste ist.)
  • Mein Blog vermeidet die Formulierungsdebatte.
0
Rick James 19 Feb. 2020 im 20:08

Ich würde Ihnen raten, NOT EXISTS mit dem richtigen Index zu verwenden:

DELETE sc
    FROM storefront_categories sc
    WHERE NOT EXISTS (SELECT 1
                      FROM master m
                      WHERE m.userid = sc.userid AND
                            m.expirydate < '2020-02-04' 
                     );

Der gewünschte Index befindet sich auf master(userid, expirydate). Die Reihenfolge der Spalten ist wichtig. Für diese Version hilft ein Index für storefront_categories nicht.

Beachten Sie, dass ich das Datumsformat geändert habe. Ich empfehle die Verwendung von JJJJ-MM-TT, um Mehrdeutigkeiten zu vermeiden - und die vollen 10 Zeichen zu verwenden.

1
Gordon Linoff 19 Feb. 2020 im 11:33

Die Abfrage sieht gut aus.

Ich würde die folgenden Indizes zur Optimierung vorschlagen:

master(expiry_date, userid)
storefront_categories(userid)

Der erste Index ist ein abdeckender Index für die Unterabfrage auf master: Dies bedeutet, dass die Datenbank die Unterabfrage nur anhand des Index ausführen kann (während nur {{X1} } im Index müssen noch die Tabellendaten überprüft werden, um das zugehörige userid) abzurufen.

Mit dem zweiten Index kann die Datenbank die Operation in optimieren.

1
GMB 19 Feb. 2020 im 11:11

Ich würde es mit exists versuchen:

DELETE 
FROM storefront_categories 
WHERE EXISTS (SELECT 1 
              FROM MASTER M 
              WHERE M.userid = storefront_categories.userid AND
                    M.expirydate <'2020-02-04'  
              );

Der Index wäre hier besser. Ich würde einen Index für storefront_categories(userid) & MASTER(userid, expirydate) erwarten.

1
Yogesh Sharma 19 Feb. 2020 im 11:15