Effektive Arbeit mit SQLite am Beispiel von ICQ

    Wie in vielen anderen Anwendungen müssen wir viele Informationen in mobilen ICQ speichern: Nachrichten, Kontakte und dergleichen. Wenn die Anzahl der Anforderungen für diese Daten einen kritischen Wert erreicht, wird die Anwendung langsamer. Ein langer Start, langsames Öffnen eines Chats, langsames Senden von Nachrichten, ständige Spinner - all das ist furchtbar ärgerlich. Meistens ist die Ursache für die Bremsen ein Fehler beim Arbeiten mit Daten. In diesem Artikel möchte ich unsere Erfahrungen mit dem Refactoring von Datenstrukturen, der Abfrageoptimierung und einigen praktischen Techniken für die Migration teilen.

    Ein paar Worte zur ursprünglichen Aufgabe. Unser Hauptaugenmerk liegt auf dem ICQ-Profil mit einer Liste von Kontakten und Nachrichten. Unsere Anwendung existiert seit vielen Jahren, sie wurde von verschiedenen Personen mit unterschiedlichen Ansätzen entwickelt, die Versionsnummer der Hauptdatenbank näherte sich souverän 30. Darüber hinaus kann die Anzahl der Funktionen im Produkt nicht im Voraus vorhergesagt werden, was sich auch auf die Architektur auswirkte. Im Allgemeinen war das Datenmodell ursprünglich ungefähr so:


    Wir hatten mehrere Dateien, in denen wir in binären Formularprofilen, Kontakten und einer Liste aktiver Chats gespeichert waren. Die Korrespondenz wurde ursprünglich separat in der Datenbank gespeichert, für jedes Profil war sie unterschiedlich. Irgendwann wurden die Kontaktdaten in eine neue Datenbank übertragen. Anschließend fügte die Anwendung Unterstützung für Mediennachrichten hinzu: Dateien, Bilder und Videos. [Ironie] Logischerweise [/ Ironie] haben sie eine separate Datenbank erstellt. Darüber hinaus haben wir einige gemeinsame Einstellungen verwendet, in denen wir wichtige Daten über unseren Zugriff auf das Netzwerk gespeichert haben, um sie nicht an die Hauptdatenbanken zu binden. Allmählich wurde die Arbeit mit Dateien sehr anstrengend. Stellen Sie sich vor, Sie haben einen Kontakt geändert, müssen aber dennoch alle Kontakte serialisieren und in eine Datei schreiben! Als wir entschieden, dass es in dieser Struktur nicht mehr möglich ist, sie zu verstehen und zu entwickeln, traten Synchronisationsprobleme auf. Zum Beispiel, wenn Sie atomar sowohl in eine Datei als auch in die Datenbank schreiben müssen (und Transaktionen nicht verfügbar sind!). Ich wollte alle Daten in einer einzigen Datenbank speichern und die Vorteile der relationalen Struktur und des DBMS nutzen. Für alle Daten außer dem Verlauf der Korrespondenz gab es keine Speicherprobleme.

    Dies ist unsere Beispieltabelle mit Nachrichten. Beachten Sie, dass hier zwei Schlüsselfelder vorhanden sind - Text und Ganzzahl:



    Wir haben zwischen zwei Optionen zum Speichern des Verlaufs gewählt: Für jeden Kontakt wurde eine separate Tabelle mit der obigen Struktur erstellt, oder der gesamte Verlauf wurde in einer Tabelle gespeichert, und die Kontaktinformationen wurden gespeichert in einem separaten Schlüsselfeld. Dementsprechend war in der ersten Variante die Kontakt-ID im Tabellennamen enthalten. Ungefähr so:



    Die erste Option ist ziemlich schnell, wenn wir viel mit einem Chat arbeiten, d. H. wenn Sie Nachrichten für einen Kontakt lesen und schreiben müssen. Die Anwendung enthielt jedoch viel Code, der bestimmt, in welche Tabelle geschrieben werden soll, welche Indizes verwendet werden sollen, und daher schien es logisch, all dies in eine große Tabelle zu übertragen. Aber besorgt über das Leistungsproblem. Wenn Sie im Internet nach der Antwort auf die Frage suchen, wie es in solchen Situationen besser ist, Daten zu speichern, lauten die Antworten meistens wie folgt: „Wenn Sie eine kleine Anzahl von Tabellen haben und diese im Voraus kennen, ist es besser, sie aufzuschlüsseln. Wenn Sie häufig auf Daten aus verschiedenen Tabellen zugreifen, ist es besser, diese zu kombinieren. " Um genau zu verstehen, welche Struktur besser ist, haben wir eine kleine Testanwendung geschrieben und die Geschwindigkeit der Abfragen verglichen. Betrachten Sie ein Beispiel aus dem Leben. Der Benutzer hat 30 Kontakte, Insgesamt werden 20.000 Nachrichten gespeichert. Ein aktiver Benutzer sendet durchschnittlich 30 Nachrichten pro Tag und empfängt ungefähr doppelt so viele. Insgesamt 100 Nachrichten pro Tag, dh 20.000 - das sind ungefähr sechs Monate Korrespondenz.

    Erstellen wir zwei Datenbanken: In einer werden die Daten in verschiedenen Tabellen verteilt, in der zweiten - alles wird in einer Tabelle gespeichert. Unmittelbar nach dem Erstellen der Dateien werden wir feststellen, dass im ersten Fall die Größe unserer Datenbank fast 400 KB beträgt und im zweiten Fall nur 32 Kilobyte. Warum passiert das? Die Größe der Datenbankdatei ist immer ein Vielfaches einer bestimmten Größe - der Größe der Seite. Die Datenbank speichert Seite für Seite Daten in sich. Die Seitengröße kann im System mit dem Befehl Pragma page_size angepasst werden. In Android beträgt die Standardeinstellung standardmäßig 4 Kilobyte. Wir haben mit diesem Parameter experimentiert und versucht, ihn zu erhöhen und zu verringern. Die maximale Seitengröße gemäß Dokumentation beträgt jetzt 64 Kilobyte, die minimale 512 Byte. Änderungen an diesem Parameter führten zu einer Verschlechterung der Tests. Tatsächlich, Die Seitengröße in jedem Betriebssystem ist für Festplattenvorgänge optimiert. Es ist bekannt, dass die Daten nach Sektoren gelesen werden, und diese Seitengröße ist am besten für die Arbeit mit der Festplatte geeignet. Weitere Informationen zum Dateiformat finden Sie unteroffizielle Dokumentation .

    Nach dem Hinzufügen von Testdaten wurden die Größen unserer Dateien auf den Seiten ausgerichtet, aber der Ansatz mit einer Reihe von Tabellen wird voraussichtlich verlieren.



    Optimierung einfügen


    Schauen wir uns die Optionen für Einsätze an. Die gröbste Option: Nachrichten werden in einer Schleife ohne Transaktionen hinzugefügt. In beiden Fällen dauert es unanständig 5,5 Minuten. Wenn wir in unserem Beispiel 1000 Datensätze in einer Transaktion gruppieren, wird der Zeitaufwand erheblich reduziert. Was kann man außer Transaktionen noch tun? Wenn wir die insert () -Methode aus der SQLiteDatabase-Klasse aufrufen, erstellt sie jedes Mal eine vorbereitete Anweisung - eine kompilierte SQL-Anweisung - in der Datenbank und führt diese Anweisung für jede Abfrage aus. Dies ist in Bezug auf die Leistung ziemlich aufwändig. Daher kann die SQL-Anweisung, die wir für Einfügungen verwenden, herausgebracht werden. Jedes Mal müssen wir es nur mit neuen Daten füllen. Wenn wir diesen Ansatz anwenden, werden wir weitere 20% gewinnen.

    Um eine noch größere Leistungssteigerung zu erzielen, müssen wir ein wenig an den Datenbankeinstellungen basteln. Ein kleiner technischer Exkurs zum Thema Transaktionen. Eine Transaktion in einer Datenbank sollte die folgenden Eigenschaften aufweisen: Atomizität, Konsistenz, Unabhängigkeit, Zuverlässigkeit ( https://ru.wikipedia.org/wiki/ACID ). Um all diese Eigenschaften beizubehalten, kann die Datenbank keine Transaktionen direkt direkt in die Datenbankdatei auf der Festplatte schreiben. Sie muss eine separate Struktur pflegen - ein Transaktionsprotokoll. Dies ist ein ziemlich leistungsfähiges Tool, mit dem Sie die oben genannten Transaktionseigenschaften unterstützen können.

    SQLite verfügt über viele interessante Transaktionsprotokollierungsmodi, z. B. In-Memory. In diesem Modus wird das Transaktionsprotokoll direkt im Speicher gespeichert und eine enorme Datenbankleistung erzielt. Wenn die Stromversorgung jedoch versehentlich ausgeschaltet wird, gehen die Daten verloren. Erfahren Sie mehr über Transaktionsprotokolle .

    Wir werden an zwei Betriebsarten interessiert sein, in denen Daten sicher gespeichert werden. Der erste ist der Standardmodus in Android - journal_mode Rollback. Wenn Sie es über "Pragma" konfigurieren, wird es als "journal_mode = delete" festgelegt. In diesem Fall erstellt die Datenbank eine spezielle Datenbank-Journaldatei. Es schreibt die Daten, die für das Rollback der Transaktion erforderlich sind, wenn ein Fehler aufgetreten ist. Die Transaktionsdaten werden in die Hauptdatenbankdatei geschrieben. Wenn die Transaktion erfolgreich abgeschlossen wurde, wird die Journaldatei mit einem speziellen Flag markiert, das nicht verwendet wird. Für uns ist es wichtig, dass die Datenbank in diesem Modus gleichzeitig in zwei Dateien schreibt: die Rollback-Datei und die Hauptdatenbankdatei.

    In SQLite Version 3.7 (dies ist Android API 16) wurde der neue Transaktionsprotokollmodus Write Ahead Logging eingeführt. In diesem Modus werden Daten nicht in die Hauptdatenbankdatei geschrieben, sondern in eine temporäre Datei namens db-wal. Damit die Anwendung genauso wie mit der Hauptdatenbank damit arbeiten kann, wird eine Datei mit Indizes erstellt - db-shm. Es funktioniert so. Transaktionsdaten werden in eine temporäre WAL-Datei mit derselben Seitenstruktur geschrieben. Wenn die Anzahl der Seiten einen bestimmten Schwellenwert erreicht, werden die Daten aus der WAL-Datei in die Hauptdatenbank übertragen. Wenn in diesem Moment die Stromversorgung ausgeschaltet wird und die Daten nicht übertragen werden, findet SQLite beim nächsten Start diese Datei und stellt die Struktur wieder her. Wenn Sie diesen Modus aktivieren, wird die Leistung unseres Tests immer noch erhöht. Hier gewinnen wir ein wenig, weil wir nur 20 Transaktionen haben. Wenn es jedoch viele davon gibt, kann die Beschleunigung in verschiedenen Tests 40% erreichen. Daher ist die Verwendung von Wal für neue Gadget-Modelle eine gute Lösung.

    Die folgende Grafik zeigt, um wie viel wir das Einfügen von Datensätzen beschleunigt haben.



    Probenoptimierung


    Schauen wir uns die Datenerfassung an. Für unsere Anwendung ist dies möglicherweise wichtiger, da Anwendungen am häufigsten Daten lesen. Hier werde ich Ihnen ziemlich offensichtliche Dinge erzählen, die die meisten von Ihnen wahrscheinlich benutzen. Für viele Anwendungen ist dies jedoch immer noch relevant. Zunächst müssen Sie Indizes verwenden. Dies ist eine spezielle Struktur, die getrennt von der Tabelle gespeichert wird und Daten für einen bestimmten Schlüssel mit einem Eintrag in der Haupttabelle vergleicht. Bei Verwendung des Index sucht das DBMS anstelle eines vollständigen Scans der gesamten Tabelle zunächst anhand des Schlüsselfelds nach O (Protokoll N) nach der ID der Datensätze im Index und liest dann die erforderlichen Daten punktweise aus der Tabelle.

    Selbst mit Indizes kann jedoch noch viel beschleunigt werden. Betrachten Sie dieses Beispiel: Wenn Sie die Anwendung starten, müssen Sie für jeden Kontakt eine Nachricht lesen, um Chats anzuzeigen. Wenn Sie einfach jede Nachricht in einer Schleife lesen, beträgt die Gesamtlesezeit beispielsweise bei 100 Iterationen etwa 5 bis 6 Sekunden. Anstelle separater Anforderungen ist es besser, die benötigten Schlüssel in einer einzigen Anforderung zu gruppieren. Zum Beispiel können wir das where in construct verwenden und eine ID-Liste übergeben. Am effektivsten ist es jedoch, Daten aus dem Index selbst zu lesen. In Indizes können Sie nicht ein Feld, sondern mehrere speichern. Solche Indizes werden zusammengesetzt genannt. Dies verlangsamt das Einfügen etwas, aber wenn Sie die Felder auswählen, aus denen der Index besteht, liest die Datenbank nicht zwei Strukturen - den Index und die Hauptdatei - sondern nur den Index. Wenn Sie Daten per ID erhalten,



    Es scheint ein gutes Ergebnis zu sein, aber wenn Sie wal aktivieren, können Sie die Anwendung unter bestimmten Bedingungen noch weiter beschleunigen. Der Grund dafür ist, dass in diesem Modus separate Datenkanäle zum Schreiben und Lesen verwendet werden (das Schreiben blockiert das Lesen nicht: www.sqlite.org/wal.html ).

    Man kann nur ein sehr praktisches Tool zum Debuggen von Abfragen erwähnen. SQLite verfügt über den Befehl "EXPLAIN-Abfrageplan". Betrachten Sie ein Beispiel für diesen Befehl.



    Bei der Ausführung dieser Abfrage zeigt der DB-Browser an, was das DBMS beim Ausführen der SQL-Abfrage tut. In diesem Fall sehen wir, dass SQLite nach Index sucht (unter Verwendung des Deckungsindex). Dieser Befehl kann uns vier verschiedene Ergebnisse zurückgeben:
    1. Scan-Tabelle - Die Datenbank ist eine Volltextsuche, durchläuft jeden Datensatz und sucht nach einer Übereinstimmung. Dies ist die ineffizienteste Option.
    2. Suchtabelle mit Index - Es gibt einen erforderlichen Index für die Suche nach Datensätzen, aber die Suchdaten sind nicht darin enthalten.
    3. Suchtabelle mit Deckungsindex - im effektivsten Fall befinden sich die gesuchten Daten bereits im Index;
    4. use temp B-TREE - Wenn wir Konstruktionen der Typgruppe by, order by in der Abfrage verwenden und für dieses Feld kein Index vorhanden ist, sollte die Datenbank meistens Folgendes tun: Alle Datensätze abrufen, die das Kriterium erfüllen, und einen Baum im Speicher zum Sortieren erstellen diese Daten. Dies ist normalerweise ein langsamer Prozess. Wenn Sie etwas gruppieren oder organisieren müssen, ist es besser, einen Index für dieses Feld zu führen.

    Ich wiederhole: Es ist ratsam, jede Abfrage, die Sie optimieren möchten, mithilfe des "Abfrageplans erklären" auszuführen und zu sehen, was die Datenbank tut. Wir wenden uns nun der Migration zu: Es ist notwendig, die umfangreiche Struktur in eine einzige Datenbank zu übertragen, um keine Daten zu verlieren.



    Höchstwahrscheinlich verwenden viele die SQLiteOpenHelper-Klasse, mit der Sie die Version der Datenbank verfolgen und in der onUpgrade-Methode Code ausführen können, der Ihre Struktur ändert. Ich muss sagen, dass diese Methode innerhalb einer einzelnen Transaktion aufgerufen wird. Wenn Sie versuchen, Daten in der onUpgrade-Methode zu kopieren oder komplexe Aktionen auszuführen, die Ausnahmen auslösen können, besteht das Risiko, dass Sie nichts aktualisieren. Ich empfehle, diese Methode nur zu verwenden, um die Struktur zu ändern. Um die Daten selbst zu übertragen, ist es besser, eine separate Methode aufzurufen.

    Übrigens hatten wir bei einem der Upgrades einen unglücklichen Fehler. Wir haben Konstanten im SQL-Code verwendet, und irgendwann haben sich die Konstanten geändert. Und die Anwendungsversionen, von denen wir versucht haben, die Struktur zu aktualisieren, funktionierten natürlich mit den alten Tabellen- und Feldnamen. Gleichzeitig funktionierte alles im Code des aktuellen Upgrades von der vorherigen Version, jedoch nicht beim Upgrade von der vorletzten Version, da wir die Konstante geändert haben und es völlig unklar war, wo nach dem Fehler gesucht werden sollte. Aus diesem Grund haben wir beschlossen, keine Konstanten mehr in SQL-Abfragen zu verwenden.

    Nachdem wir die Datenbankstruktur aktualisiert hatten, mussten wir eine Reihe von Datensätzen aus verschiedenen alten Datenbanken kopieren. Die einfachste Lösung besteht darin, alle alten Daten in den Cursor einzulesen, jeden Datensatz zu analysieren und zur neuen Datenbank hinzuzufügen. Dies ist jedoch sehr langsam. Wenn wir eine lange Korrespondenzgeschichte haben, Zehntausende von Nachrichten, dauert dieser Vorgang einige Sekunden. Der Benutzer wird sich langsam langweilen. Möglicherweise denken Sie, dass die Anwendung hängen geblieben ist, und lehnen sie im schlimmsten Fall insgesamt ab. Daher war es uns wichtig, dieses Verfahren zu beschleunigen. Es stellte sich heraus, dass SQLite über einen sehr praktischen Befehl zum Anhängen einer Datenbank verfügt, mit dem Sie Datenbanken von Drittanbietern mit Ihrer Datenbank verbinden können. Um von angehängten Datenbanken auf Tabellen zuzugreifen, müssen Sie nur den Datenbanknamen an den Tabellennamen anhängen. Somit können Sie SQLite-Tools für die Migration ohne Analyse verwenden.

    Dieser Ansatz weist einige Einschränkungen auf. Der Befehl Attach database selbst, eine Erweiterung von SQLite, funktioniert nicht innerhalb einer Transaktion und kann daher nicht innerhalb der onUpgrade-Methode aufgerufen werden. Darüber hinaus funktionieren Transaktionen in der Datenbank "Anhängen" nur mit Abfragen an eine Datenbank. In unserer Version mussten wir, wenn mehrere Datenbanken zu einer zusammengeführt werden mussten, mit jeder Datenbank zu einer Transaktion arbeiten. Aber es funktioniert viel schneller als Cursor, wir könnten alle Daten in weniger als einer Sekunde genau übertragen!

    Nach dem Upgrade ist es auch an der Zeit, über die Aktivierung der effektiven Arbeitsmodi mit der Datenbank, beispielsweise WAL, nachzudenken. Wenn Ihre Anwendung die alte API unterstützt, müssen Sie überprüfen, ob die aktuelle API höher als Version 16 ist.

    Es ist auch nützlich, eine gewisse Defragmentierung der Daten in der Datenbank durchzuführen. SQLite verfügt über einen sehr praktischen Vacuum-Befehl, mit dem Sie alle nicht verwendeten Seiten und Defragmentierungsdaten löschen können. Es ist kein Geheimnis, dass, wenn Sie einfach den Löschbefehl ausführen, die Daten von der Festplatte physisch nicht gelöscht werden und Datensätze oder ganze Seiten als gelöscht markiert werden. Mit diesem Befehl können Sie nicht verwendete Fragmente entfernen und Indizes neu erstellen.

    Fazit


    In diesem Artikel haben wir daher einige Ansätze zur Datenbankoptimierung am Beispiel der mobilen ICQ-Anwendung untersucht. Natürlich finden Sie fast alle Empfehlungen auf www.sqlite.org und anderen thematischen Ressourcen. Wenn Sie jedoch ein bestimmtes Problem lösen, können Sie am besten verstehen, was genau im Darm des DBMS geschieht.

    Jetzt auch beliebt: