Effizienter Weg, um sicherzustellen, dass eindeutige Zeilen in SQLite3
Ich bin mit SQLite3 in einem meiner Projekte und ich brauche, um sicherzustellen, dass die Zeilen in eine Tabelle eingefügt werden, sind einzigartig im Hinblick auf eine Kombination von einigen Ihrer Spalten. In den meisten Fällen, in die Zeilen eingefügt werden, unterscheiden sich in dieser Hinsicht, aber im Fall einer übereinstimmung die neue Zeile aktualisieren/ersetzen der vorhandenen.
Die offensichtliche Lösung war die Verwendung eines zusammengesetzten Primärschlüssel, mit einer Konflikt-Klausel zu verarbeiten Kollisionen. Thefore dies:
CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT);
wurde diese:
CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT, PRIMARY KEY (Fld0, Fld2, Fld3) ON CONFLICT REPLACE);
Bedeutet ja erzwingen der Eindeutigkeit, wie ich es brauche. Leider ist diese änderung auch für die Leistungseinbußen, die weit über das hinaus, was ich erwartet hatte. Ich habe
ein paar tests mit der sqlite3
Befehlszeilen-Dienstprogramm, um sicherzustellen, dass es nicht einen Fehler in meinem restlichen code. Die tests betreffen die Eingabe 100.000 Zeilen, entweder in einer einzigen
Transaktion oder in 100 Transaktionen von 1.000 Zeilen jeweils. Ich erhielt die folgenden Ergebnisse:
| 1 * 100,000 | 10 * 10,000 | 100 * 1,000 |
|--------------- | --------------- | ---------------|
| Time | CPU | Time | CPU | Time | CPU |
| (sec) | (%) | (sec) | (%) | (sec) | (%) |
-------------------------------- | ------- | ------- | ------- | ------- | ------- | -------|
No primary key | 2.33 | 80 | 3.73 | 50 | 15.1 | 15 |
-------------------------------- | ------- | ------- | ------- | ------- | ------- | -------|
Primary key: Fld3 | 5.19 | 84 | 23.6 | 21 | 226.2 | 3 |
-------------------------------- | ------- | ------- | ------- | ------- | ------- | -------|
Primary key: Fld2, Fld3 | 5.11 | 88 | 24.6 | 22 | 258.8 | 3 |
-------------------------------- | ------- | ------- | ------- | ------- | ------- | -------|
Primary key: Fld0, Fld2, Fld3 | 5.38 | 87 | 23.8 | 23 | 232.3 | 3 |
Meine Anwendung derzeit führt die Geschäfte von höchstens 1000 Zeilen und ich war überrascht von der 15-Fach Tropfen in der Leistung. Ich erwartete höchstens ein 3-Fach Tropfen in Durchsatz und einem Anstieg der CPU-Auslastung, wie in den 100k-Transaktion Fall. Ich denke, die Indizierung beteiligt bei der Aufrechterhaltung der primary key-Einschränkungen erfordert einen erheblich größeren Zahl von synchronen DB-Operationen, so dass meine Festplatten der Engpass in diesem Fall.
Mit WAL-Modus hat eine gewisse Wirkung haben - eine performance-Steigerung von über 15%. Das ist leider nicht genug auf seine eigene. PRAGMA synchronous = NORMAL
nicht scheinen, um eine Wirkung.
Ich könnte in der Lage sich zu erholen einige performance durch die Erhöhung des Transaktionsvolumens, aber ich würde lieber nicht tun, dass, aufgrund der erhöhten memory-Nutzung und der Sorge um die Reaktionsfähigkeit und die
Zuverlässigkeit.
Die text-Felder in jeder Zeile haben variable Längen von 250 bytes im Durchschnitt. Die query-performance spielt keine Rolle, zu viel, aber die insert-performance ist sehr wichtig. Mein code für die Anwendung ist in C und ist (sein soll) portabel, mindestens Windows und Linux.
Gibt es eine Möglichkeit zur Verbesserung der performance von Gewindeeinsätzen ohne Erhöhung der Transaktionsgröße? Entweder ist eine Einstellung in SQLite (was aber dauerhaft zwingt die DB in den asynchronen Betrieb, das ist) oder programmgesteuert in meiner Anwendung code? Zum Beispiel ist es ein Weg, um sicherzustellen, Zeile Einzigartigkeit, ohne die Verwendung eines index?
BOUNTY:
Durch die Verwendung der hashing/Indizierung Methode beschrieben, in meine eigene Antwort, ich habe es etwas Moderat den Leistungsabfall zu einem Punkt, wo es wahrscheinlich akzeptabel für meine Anwendung.
Es scheint aber, dass die Anzahl der Zeilen in der Tabelle erhöht, die Präsenz der index macht die inserts langsamer und langsamer.
Ich bin daran interessiert, jede Technik oder fine-tuning-Einstellung, die die Leistung verbessern in diesem speziellen Fall verwenden, solange es sich nicht um das hacken der SQLite3-code oder sonst nach dem Projekt zu wartbaren.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Habe ich verwendet sqlite einfügen Millionen von Zeilen zur Laufzeit und das ist, was ich verwendet, um die Leistung zu erhöhen:
das einfügen der Daten (Vorbereitung der
Befehl einmal und einfach ändern
Parameter Werte in der Schleife)
PRAGMA synchronous AUS (nicht sicher,
wie funktioniert das mit WAL)
Wenn Sie versuchen, diese bitte posten Sie Ihre test-Ergebnisse. Ich glaube, es wird interessant sein für alle.
fsync()
(d.h. Festplatte schreibt) Operationen pro Transaktion. Der OS-cache, hilft schon mit liest, sowieso...Den
ON CONFLICT REPLACE
Klausel wird SQLite löschen der vorhandenen Zeilen, dann neue Zeilen einfügen. Das bedeutet, dass SQLite ist wahrscheinlich zu verbringen, etwas von seiner ZeitDass ist meine Meinung dazu, basierend auf der SQLite-Dokumentation und das Lesen über andere Datenbank-management-Systeme. Ich schaue nicht auf den Quellcode.
SQLite hat zwei Möglichkeiten, auszudrücken, uniqueness constraints:
PRIMARY KEY
undUNIQUE
. Beide erstellen einen index, wenn.Nun die wirklich wichtigen Dinge . . .
Es ist toll, dass Sie testet. Die meisten Entwickler machen das nicht. Aber ich denke, deine Testergebnisse sind stark irreführend.
In deinem Fall ist es egal, wie schnell Sie können zum einfügen von Zeilen in eine Tabelle, die nicht über einen Primärschlüssel verfügen. Eine Tabelle, die nicht Primärschlüssel nicht erfüllt Ihre grundlegenden Anforderungen für die Integrität der Daten. Das bedeutet, dass Sie können nicht Sie verlassen sich auf Ihre Datenbank, um Ihnen die richtigen Antworten.
Wenn es nicht die passenden Antworten zu geben, ich kann es wirklich, wirklich schnell.
Um einen sinnvollen Zeitpunkt für das einfügen in eine Tabelle ohne key, müssen Sie entweder
um sicherzustellen, dass Sie nicht verletzen die
nicht deklarierte primary key-Einschränkung,
und stellen Sie sicher, aktualisieren Sie vorhandene
Zeilen mit passenden Werten (anstelle
einfügen), oder
Tabelle zu bereinigen, Duplikate auf
(Fld0, Fld2, Fld3), und zu versöhnen
Konflikte
Und, natürlich, die Zeit, die diese Prozesse nehmen muss berücksichtigt werden, zu.
FWIW, ich habe einen test von running 100K SQL-insert-Anweisungen in Ihrem schema in Transaktionen von 1000 Aussagen, und es dauerte nur 30 Sekunden. Eine einzelne Transaktion von 1000 insert-Anweisungen, die scheint zu sein, was Sie erwarten, in der Produktion, nahmen 149 msec.
Vielleicht können Sie die Dinge beschleunigen, indem einfügen in ein nicht mit Schlüsseln versehene temporäre Tabelle, dann die Aktualisierung der eingegebenen Tabelle aus, die.
ON CONFLICT IGNORE
könnte eine Verbesserung, wenn die Daten müssen nicht unbedingt ersetzt werden.(Ich normalerweise nicht Antwort auf meine eigenen Fragen, aber ich möchte das Dokument noch ein paar Ideen/partielle Lösungen für diese.)
Das größte problem mit einem zusammengesetzten Primärschlüssel ist die Art, wie die Indizes verarbeitet werden. Zusammengesetzte Schlüssel bedeuten, ein index, der auf den zusammengesetzten Wert, in meinem Fall bedeutet die Indizierung strings. Beim vergleichen von string-Werten ist das nicht langsam, indizieren einen Wert mit einer Länge von, sagen wir, 500 bytes bedeutet, dass die B-Baum-Knoten in den index passen, weit weniger row - /node-Zeiger als ein B-tree-Indizes eine 64-bit-integer-Wert. Dies bedeutet, dass das laden viel mehr DB-Seiten für jedes index-Suche, wie die Höhe des B-Baumes erhöht.
Um mit diesem Problem umzugehen modifizierte ich meinen code so, dass:
Es nutzt WAL-Modus. Die Leistungssteigerung war sicherlich lohnt sich so eine kleine änderung, da ich nicht irgendwelche Probleme mit der DB-Datei, die sich nicht selbst enthalten.
Benutzte ich die MurmurHash3 hash-Funktion - nach dem neu-schreiben in C und die Anpassung - die Herstellung einer einzelnen 32-bit-hash-Wert aus den Werten der Felder, welche die Schlüssel. Ich hinterlegt dieses hash in eine neue indiziert Spalte. Da dies ist ein integer-Wert, der index ist Recht schnell. Dies ist der einzige index für diese Tabelle. Da es bei den meisten von 10.000.000 Zeilen in der Tabelle, hash-Kollisionen werden nicht ein performance-Problem - obwohl ich nicht wirklich überlegen, den hash-Wert zu
UNIQUE
ist, wird der index nur eine einzelne Zeile zurückgeben, die in den Allgemeinen Fall.An dieser Stelle gibt es zwei alternativen, die ich programmiert haben, und sind derzeit in der Testphase:
DELETE FROM Event WHERE Hash=? AND Fld0=? AND Fld2=? AND Fld3=?
, gefolgt von einemINSERT
.UPDATE Event SET Fld1=?,... WHERE Hash=? AND Fld0=? AND Fld2=? AND Fld3=?
, gefolgt von einemINSERT
wenn keine Zeilen aktualisiert.Erwarte ich, dass die zweite alternative schneller zu sein, aber ich werde den Test zuerst. In jedem Fall scheint es, dass mit diesen änderungen der performance-Einbruch (im Vergleich zu der ursprünglichen index-weniger Tabelle) wurde verringert um einen Faktor von 5 oder so, das ist weit mehr überschaubar.
EDIT:
Ich an dieser Stelle angesiedelt haben, die mit der Verwendung der zweiten Variante, die ist in der Tat etwas schneller. Es scheint jedoch, dass jede Art von index verlangsamt SQLite3 dramatisch, als die indizierte Tabelle wird größer. Erhöhung der DB-Seite Größe 8192 bytes, scheint zu helfen etwas, aber nicht annähernd so drastisch wie ich es gerne hätte.
Ich bin mir nicht zu 100%, dass das einfügen funktioniert wie in SQLite, aber ich denke, es sollte. Das mit der richtigen Indizierung der
Where
- Felder sollte das relativ schnell. Dies ist jedoch von zwei Transaktionen, die ist etwas zu prüfen.CASE
in SQLite ist ein Ausdruck, keine Anweisung. Ich habe nicht in der Lage gewesen, um es so zu benutzen. Sie haben eine SQL-snippet, dass ich versuchen könnte?Zusätzlich zu all den anderen tollen Antworten, eine Sache, die Sie tun können, ist die partition der Daten in mehrere Tabellen.
SQLite Fügt langsamer und langsamer als die Anzahl der Zeilen erhöht, aber wenn, können Sie sich aufspalten einer Tabelle in mehrere, dass die Wirkung vermindert wird (z.B.: "Namen" -> "names_a", "names_b", ... für Namen beginnend mit dem Buchstaben
x
). Später können Sie tunCREATE VIEW "names" AS SELECT * FROM "names_a" UNION SELECT * FROM "names_b" UNION ...
.