mysql-Performance-Problem mit Code und Tabellenentwurf

Question

Ich brauche ein paar Optionen.

Ich habe eine Tabelle, die wie folgt aufgebaut ist, mit etwa 78.000.000 Zeilen...

• id INT (Primärschlüssel)
• loc VARCHAR (indiziert)
• date VARCHAR (indiziert)
• Zeit VARCHAR
• ip VARCHAR
• lookup VARCHAR

Hier ist ein Beispiel für eine Abfrage, die ich habe.

SELECT lookup, date, time, count(lookup) as count FROM dnstable
WHERE STR_TO_DATE(`date`, '%d-%b-%Y') >= '$date1' AND STR_TO_DATE(`date`, '%d-%b-%Y')   <= '$date2' AND
time >= '$hour1%' AND time <= '$hour2%' AND
`loc` LIKE '%$prov%' AND
lookup REGEXP 'ca|com|org|net' AND
lookup NOT LIKE '%.arpa' AND
lookup NOT LIKE '%domain.ca' AND 
ip NOT LIKE '192.168.2.1' AND
ip NOT LIKE '192.168.2.2' AND
ip NOT LIKE '192.168.2.3'
GROUP BY lookup
ORDER BY count DESC
LIMIT 100

Ich habe meinen Mysql-Server so konfiguriert, wie ich es in einigen Beispielen mit hoher Auslastung gefunden habe. Die Hardware ist gut, 4 Kerne, 8 Gigabyte RAM.

Diese Abfrage dauert etwa 180 Sekunden... Hat jemand Tipps, wie man dies effizienter gestalten kann?

Answer 1

Hier sind viele Dinge falsch. Sehr viele Dinge. Ich würde in den anderen Antworten nach Abfrageoptionen suchen (Sie verwenden eine Menge LIKES, NOT LIKES und Funktionen.... und Sie tun sie auf nicht verschlüsselten Spalten...). Wenn ich in Ihrem Fall wäre, würde ich meine gesamte Datenbank umgestalten. Es sieht so aus, als ob Sie dies zum Speichern von DNS-Einträgen verwenden - Hostnamen zu IP-Adressen.

Möglicherweise haben Sie nicht die Möglichkeit, Ihre Datenbank neu zu gestalten - vielleicht handelt es sich um eine Kundendatenbank oder etwas anderes, worüber Sie keine Kontrolle haben. Vielleicht gibt es eine Reihe von Anwendungen, die von der aktuellen Datenbank abhängen. Wenn Sie jedoch die Möglichkeit haben, Ihre Datenbank zu refaktorisieren, würde ich Ihnen das dringend empfehlen.

Hier ist eine grobe Übersicht über das, was ich tun würde:

1. Speichern Sie die TLDs (Top-Level-Domains) in einer separaten Spalte als ENUM. Machen Sie daraus einen Index, so dass er leicht durchsuchbar ist, anstatt zu versuchen, .com, .arpa usw. zu regexieren. TLDs sind ohnehin begrenzt und ändern sich nicht oft, daher ist dies ein guter Kandidat für eine ENUM.

2. Speichern Sie die Domäne ohne die TLD in einer normalen und einer umgekehrten Spalte. Sie können beide Spalten indizieren, aber je nach Ihren Suchanfragen brauchen Sie vielleicht nur die umgekehrte Spalte zu indizieren. Grundsätzlich können Sie mit einer Reverse-Spalte nach allen Hosts in einer Domain (z.B. google) suchen, ohne jedes Mal eine Volltextsuche durchführen zu müssen. MySQL kann eine Schlüsselsuche nach der Zeichenfolge "elgoog" in der Reverse-Spalte durchführen. Da DNS eine Hierarchie ist, passt dies perfekt.

3. Ändern Sie die Spalten Datum und Uhrzeit von VARCHAR in DATE bzw. TIME. Dies ist eine offensichtliche Änderung. Kein str_to_time, str_to_date, etc. mehr. Das macht absolut keinen Sinn.

4. Speichern Sie die IP-Adressen anders. Es gibt keinen Grund, hier einen VARCHAR zu verwenden - das ist ineffizient und macht keinen Sinn. Verwenden Sie stattdessen vier separate Spalten für jedes Oktett (dies ist sicher, da alle IPv4-Adressen vier Oktette haben, nicht mehr und nicht weniger) als vorzeichenlose TINYINT-Werte. So erhalten Sie 0-255, den Bereich, den Sie benötigen. (Jedes IP-Oktett besteht ohnehin aus 8 Bits.) Dies sollte die Suche wesentlich beschleunigen, insbesondere wenn Sie die Spalten verschlüsseln.

   z.B.: select * from table where octet1 != 10; (dies würde alle 10.0.0.0/8 privaten IP-Räume herausfiltern)

Das Grundproblem besteht darin, dass Ihr Datenbankdesign fehlerhaft ist und Ihre Abfrage Spalten verwendet, die nicht indiziert sind, und Ihre Abfragen sind ineffizient.

Wenn Sie mit dem aktuellen Design.... nicht weiterkommen, weiß ich nicht, ob ich Ihnen wirklich helfen kann. Es tut mir leid.

Answer 2

Ich wette, das wirklich große Problem hier sind die STR_TO_DATE-Funktionen. Wenn möglich, dann versuchen Sie, die Datumsspalte wirklich mit dem Datentyp DATE zu versehen. (DATE, DATETIME, TIMESTAMP)

Eine Indizierung dieser neuen oder geänderten Spalte (mit Datentyp Datum) würde die Auswahl über diese Spalte erheblich beschleunigen. Sie müssen das Parsen des Datums vermeiden, das derzeit aufgrund des falschen Datentyps für die Spalte "Datum" fehlt. Dieses Parsen/Konvertieren verhindert, dass MySQL den Index für die Spalte "Datum" verwendet.

Schlussfolgerung : Erzeugen Sie die Spalte "date" mit dem Datentyp "Date", lassen Sie diese Spalte indizieren und verwenden Sie nicht STR_TO_DATE in Ihrer Anweisung.

---

Ich behaupte, dass diese lokalen IP-Adressen nicht sehr selektiv sind, wenn sie mit Negation verwendet werden, richtig? (Dies hängt von den typischen Daten in der Tabelle ab.) Da die ip-Spalte nicht indiziert ist, führt eine Auswahl in dieser Spalte immer zu einem vollständigen Tabellenscan. Wenn die ungleiche (<>) Auswahl für ip sehr selektiv ist, sollten Sie einen Index für diese Spalte einrichten und die Anweisung so ändern, dass nicht "wie", sondern <> verwendet wird. Ich glaube jedoch nicht, dass die ungleiche Auswahl für ip sehr selektiv ist.

Schlussfolgerung : Ich glaube nicht, dass Sie hier etwas Bedeutendes gewinnen können.

Answer 3

Einige Tipps

• Verwenden Sie != anstelle von NOT LIKE
• Vermeiden Sie REGEXP in mysql-Abfrage
• Vermeiden Sie STR_TO_DATE(date, '%d-%b-%Y') >= '$date1' Versuchen Sie, das MySQL-formatierte Datum an die Abfrage zu übergeben, anstatt es mit STR_TO_DATE
• lookup sollte indiziert werden, wenn Sie die Gruppierung nach verwenden müssen.

Versuchen Sie, die Abfrageergebnisse zwischenzuspeichern( wenn möglich ).

Answer 4

Das Problem ist, dass ein LIKE bedeutet eine vollständige Durchquerung der Tabelle! Das ist der Grund, warum Sie dies sehen. Das erste, was ich vorschlagen würde, ist das Loswerden von LIKE '192.168.2.1' denn eigentlich ist das dasselbe wie ='192.168.2.1' Auch die Tatsache, dass Sie die LIMIT 100 am Ende bedeutet, dass die Abfrage gegen alle Datensätze läuft und dann nur die ersten 100 auswählt -- wie wäre es, wenn Sie stattdessen ein SELECT machen, das nur alle anderen Operationen, aber nicht LIKE beinhaltet und dieses einschränken und dann ein zweites SELECT haben, das LIKE verwendet?