他の多くのアプリケーションと同様に、モバイルICQにはメッセージ、連絡先などの多くの情報を保存する必要があります。 このデータに対するリクエストの数が重要な値に達すると、アプリケーションの速度が低下し始めます。 長い起動、遅いチャットの開始、遅いメッセージの送信、絶え間ないスピナー-これはすべて非常に迷惑です。 ほとんどの場合、ブレーキの原因はデータの処理の失敗です。 この記事では、データ構造のリファクタリング、クエリの最適化、および移行のための便利なテクニックに関する経験を共有したいと思います。



元の問題に関するいくつかの言葉。 私たちの主なエッセンスは、連絡先のリストがあり、メッセージが含まれているICQプロファイルです。 私たちのアプリケーションは長年にわたって存在し、さまざまなアプローチを持つさまざまな人々によって開発されました。メインデータベースのバージョン番号は自信を持って30に近づきました。さらに、製品の機能の数は事前に予測できません。 一般に、データモデルはもともと次のようなものでした。





バイナリ形式のプロファイル、連絡先、アクティブなチャットのリストに保存したファイルがいくつかありました。 対応は、最初はデータベースごとに別々に保存され、プロファイルごとに異なっていました。 ある時点で、連絡先データが新しいデータベースに転送されました。 次に、アプリケーションはメディアメッセージのサポートを追加しました：ファイル、写真、ビデオ。 [皮肉]論理的に[/皮肉]、彼らは別のデータベースを作成しました。 さらに、共有設定を使用して、ネットワークへのアクセスに関する重要なデータを保存し、メインデータベースにバインドしないようにしました。 次第に、ファイルの操作に大きな負担がかかり始めました。 1つの連絡先を変更したとしますが、すべての連絡先をシリアル化してファイルに書き込む必要があります。 この構造を理解および開発することはもはや不可能であると判断した後、たとえば、ファイルとデータベースの両方にアトミックに書き込む必要がある場合（およびトランザクションは利用できません！）、同期の問題が発生し始めました。 すべてのデータを単一のデータベースに入れ、リレーショナル構造とDBMSの利点を享受したかったのです。 通信の履歴を除くすべてのデータについて、ストレージの問題はありませんでした。



これはサンプルのメッセージテーブルです。ここには、テキストと整数の2つのキーフィールドがあります。







履歴を保存するための2つのオプションから選択しました。各連絡先について、上記の構造で個別のテーブルを作成するか、履歴全体を1つのテーブルに保持し、連絡先情報を個別のキーフィールドに入れました。 したがって、第１の実施形態では、連絡先ＩＤはテーブル名に含まれていた。 このようなもの：







1つのチャットで多くの作業を行う場合、最初のオプションはかなり高速です。 1つの連絡先のメッセージを読み書きする必要がある場合。 ただし、アプリケーションには、書き込むテーブル、使用するインデックスを決定する多くのコードが含まれていたため、これらすべてを1つの大きなテーブルに転送することは論理的に思えました。 しかし、パフォーマンスの問題が心配でした。 インターネットで質問の答えを探している場合、そのような状況でデータを保存する方が良い方法は次のようになります。ほとんどの場合、答えは次のようになります。「テーブルの数が少なく、事前に知っている場合は、それらを分解することをお勧めします。 異なるテーブルのデータに頻繁にアクセスする場合は、それらを結合することをお勧めします。 どの構造が優れているかを正確に理解するために、小さなテストアプリケーションを作成し、クエリの速度を比較しました。 人生の例を考えてみましょう。 ユーザーには30件の連絡先があり、合計20,000件のメッセージが保存されています。 アクティブなユーザーは、1日に平均約30のメッセージを送信し、約2倍のメッセージを受信します。 1日に合計100通のメッセージ、つまり2万通は、約6か月の通信です。



2つのデータベースを作成しましょう。1つはデータが異なるテーブルに散在しており、もう1つはすべてが1つのテーブルに格納されています。 ファイルを作成した直後に、最初のケースではデータベースのサイズがほぼ400 KBであり、2番目のケースではわずか32 KBであることがわかります。 なぜこれが起こっているのですか？ データベースファイルのサイズは、常に特定のサイズ（ページのサイズ）の倍数です。 データベースは、ページごとにデータベース内にデータを保存します。 ページサイズは、AndroidのPragma page_sizeコマンドを使用してシステムで調整できます。デフォルトでは4キロバイトです。 このパラメーターを試し、増減させようとしました。 ドキュメントによると、最大ページサイズは64キロバイトで、最小は512バイトです。 このパラメーターを変更すると、テストが悪化します。 実際、各オペレーティングシステムのページサイズはディスク操作用に最適化されています。 データはセクターごとに読み取られることが知られており、このページサイズはディスクでの作業に最適です。 ファイル形式の詳細については、 公式ドキュメントを参照してください。



テストデータを追加した後、ページ内のファイルのサイズは調整されましたが、多数のテーブルを使用するアプローチは失われることが予想されます。

挿入の最適化

挿入のオプションを見てみましょう。 最も大まかなオプション：トランザクションなしでループにメッセージが追加されます。どちらの場合も、わずらわしい5.5分かかります。 この例で1つのトランザクションで1000レコードをグループ化すると、費やされる時間が大幅に削減されます。 トランザクション以外に何ができますか？ SQLiteDatabaseクラスからinsert（）メソッドを呼び出すと、毎回データベース内に準備済みステートメント（コンパイル済みSQLステートメント）が作成され、各クエリに対してこのステートメントが実行されます。 これはパフォーマンスの点でかなりのオーバーヘッドです。 したがって、挿入に使用するSQLステートメントを引き出すことができます。 毎回、新しいデータを入力するだけで済みます。 このアプローチを適用すると、さらに20％を獲得できます。



パフォーマンスをさらに向上させるには、データベース設定を少し調整する必要があります。 トランザクションのトピックに関する小さな技術的余談。 データベース内のトランザクションには、原子性、一貫性、独立性、信頼性（ https://ru.wikipedia.org/wiki/ACID ）のプロパティが必要です。 これらのプロパティをすべて維持するために、データベースはディスク上のデータベースファイルに直接トランザクションを直接書き込むことはできません。 彼女は、トランザクションログという別の構造を維持する必要があります。 これは、上記のトランザクションプロパティをサポートできる非常に強力なツールです。



SQLiteには、メモリ内など、多くの興味深いトランザクションログモードがあります。 このモードでは、トランザクションログがメモリに直接保持され、データベースのパフォーマンスが大幅に向上します。 ただし、誤って電源を切った場合、データは失われます。 トランザクションログの詳細をご覧ください。



データを安全に保存する2つの動作モードに関心があります。 最初はAndroidのデフォルトモード-journal_modeロールバックです。 「プラグマ」を使用して構成すると、「journal_mode = delete」として設定されます。 この場合、データベースは特別なdb-journalファイルを作成します。 何か問題が発生した場合、トランザクションのロールバックに必要なデータを書き込みます。 また、トランザクションデータはメインデータベースファイルに書き込まれます。 トランザクションが正常に完了すると、ジャーナルファイルは使用されていない特別なフラグでマークされます。 このモードでは、データベースがロールバックファイルとメインデータベースファイルの2つのファイルに一度に書き込むことが重要です。



SQLiteバージョン3.7（Android API 16）は、新しいトランザクションログモード、Write Ahead Loggingを導入しました。 このモードでは、データはメインデータベースファイルではなく、db-walという一時ファイルに書き込まれます。 そして、アプリケーションがメインデータベースと同じ方法でそれを操作できるように、インデックス付きのファイルが作成されます-db-shm。 このように動作します。 トランザクションデータは、同じページ構造を持つ一時的なWALファイルに書き込まれます。 ページ数が特定のしきい値に達すると、WALファイルのデータがメインデータベースに転送されます。 この時点で電源がオフになり、データが転送されない場合、SQLiteは次の起動時にこのファイルを見つけ、構造を復元します。 テストに関しては、このモードをオンにしてもパフォーマンスは向上します。 20件のトランザクションしかないため、ここで少し勝ちます。 しかし、それらが多数ある場合、さまざまなテストで加速が40％に達する可能性があります。 したがって、新しいガジェットモデルでwalを使用するのが良い解決策になります。



以下のグラフは、レコードの挿入をどれだけ高速化したかを示しています。

サンプルの最適化

データのサンプリングを見てみましょう。 私たちのアプリケーションにとって、アプリケーションはデータを読み取ることが最も多いため、これはおそらくより重要です。 ここでは、ほとんどの人がおそらく使用するかなり明白なことを説明します。 ただし、多くのアプリケーションでは、これは依然として関連しています。 まず、インデックスを使用する必要があります。 これは、テーブルとは別に保存される特別な構造で、特定のキーのデータをメインテーブルのエントリと比較します。 インデックスを使用する場合、DBMSは、テーブル全体のフルスキャンの代わりに、最初にO（log N）のキーフィールドでインデックス内のレコードのIDを検索し、次にテーブルから必要なデータをポイントごとに読み取ります。



ただし、インデックスを使用しても、高速化できるものはまだたくさんあります。 この例を考えてみましょう。アプリケーションを起動すると、チャットを表示するために連絡先ごとに1つのメッセージを読む必要があります。 各メッセージをループで単純に読み取る場合、たとえば100回の繰り返しで、合計読み取り時間は約5〜6秒になります。 個別のリクエストではなく、必要なキーを単一のリクエストにグループ化することをお勧めします。 たとえば、where構造を使用して、idリストを渡すことができます。 ただし、最も効果的なオプションは、インデックス自体からデータを読み取ることです。 インデックスでは、1つのフィールドではなく、複数のフィールドを保存できます。 このようなインデックスは複合と呼ばれます。 これにより挿​​入が遅くなりますが、インデックスを構成するフィールドを選択すると、データベースは2つの構造（インデックスとメインファイル）ではなく、インデックスのみを読み取ります。 既にインデックスに登録されているIDでデータを取得すると、大幅に高速化できます。







良い結果のように思えますが、walを有効にすると、特定の条件下でアプリケーションをさらに高速化できます。 その理由は、このモードでは、書き込みと読み取りに別々のデータチャネルが使用されるためです（書き込みは読み取りをブロックしません： www.sqlite.org/wal.html ）。



クエリをデバッグするための非常に便利なツールに言及するしかありません。 SQLiteには「クエリプランの説明」コマンドがあります。 このコマンドの例を考えてみましょう。







このクエリを実行すると、DBブラウザーは、SQLクエリの実行時にDBMSが何をするかを示します。 この場合、SQLiteはインデックスで検索することがわかります（カバーインデックスを使用）。 このコマンドは、4つの異なる結果を返すことができます。

1. スキャンテーブル -データベースは全文検索され、各レコードを反復処理して一致を検索します。これは最も非効率的なオプションです。
2. インデックスを使用した検索テーブル -レコードの検索に必要なインデックスはありますが、検索データは含まれません。
3. カバーインデックスを使用した検索テーブル -最も効果的なケースは、検索されたデータが既にインデックスにあることです。
4. 一時B-TREEを使用する-クエリで並べ替えタイプの構造を使用し、このフィールドにインデックスがない場合、ほとんどの場合、データベースは以下を実行する必要があります：基準を満たすすべてのレコードを取得し、ソート用のメモリにツリーを構築このデータ。 これは通常、遅いプロセスです。 何かをグループ化または整理する必要がある場合は、このフィールドにインデックスを保持することをお勧めします。

繰り返しますが、「explain query plan」を使用して最適化する各クエリを実行し、データベースの機能を確認することをお勧めします。 次に、移行に移ります。データを失わないように、かさばる構造を単一のデータベースに転送する必要があります。







多くの場合、多くはSQLiteOpenHelperクラスを使用します。これにより、データベースのバージョンの変更を追跡し、onUpgradeメソッドで構造を変更するコードを実行できます。 このメソッドは、単一のトランザクション内で呼び出されると言わなければなりません。 onUpgradeメソッドでデータをコピーしようとしたり、例外をスローする可能性のある複雑なアクションを実行しようとすると、何も更新されないリスクが発生します。 この方法は、構造を変更する場合にのみ使用することをお勧めします。 データ自体を転送するには、別のメソッドを呼び出すことをお勧めします。



ところで、アップグレードの1つで、残念なミスがありました。 SQLコードで定数を使用しましたが、ある時点で定数が変更されました。 そして、構造をアップグレードしようとしたアプリケーションのバージョンは、古いテーブル名とフィールド名で自然に機能しました。 同時に、以前のバージョンからの現在のアップグレードではコードですべてが機能しましたが、定数を変更したため、前年からのアップグレードでは機能しませんでした。エラーを探す場所は完全に不明でした そのため、SQLクエリで定数を使用しないことにしました。



データベース構造を更新した後、さまざまな古いデータベースから大量のレコードをコピーする必要がありました。 最も簡単な解決策は、古いデータをすべてカーソルに読み込み、各レコードを解析して新しいデータベースに追加することですが、これは非常に遅いです。 数万メッセージの長い通信履歴がある場合、このプロセスには数秒かかります。 ユーザーは退屈し始めます。アプリケーションがハングしたと思うかもしれませんし、最悪の場合、それを完全に拒否します。 したがって、この手順を促進することが重要でした。 SQLiteには非常に便利な「データベースの接続」コマンドがあり、サードパーティのデータベースをデータベースに接続できることがわかりました。 接続されたデータベースからテーブルにアクセスするには、データベース名をテーブル名に追加するだけです。 したがって、解析せずにSQLiteツールを移行に使用できます。



このアプローチにはいくつかの制限があります。 SQLiteの拡張機能であるAttach databaseコマンド自体はトランザクション内では機能しないため、onUpgradeメソッド内で呼び出すことはできません。 さらに、Attachデータベース内のトランザクションは、1つのデータベースへのクエリでのみ機能します。 このバージョンでは、複数のデータベースを1つにマージする必要がある場合、各データベースを使用してトランザクションを作成する必要がありました。 しかし、カーソルよりもはるかに高速に動作し、すべてのデータを1秒未満で正確に転送できます。



また、アップグレード後は、WALなどのデータベースを操作する効果的なモードを有効にすることを検討してください。 アプリケーションが古いAPIをサポートしている場合、現在のAPIがバージョン16よりも高いことを確認する必要があります。



データベース内のデータの最適化を実行することも役立ちます。 SQLiteには非常に便利な真空コマンドがあり、未使用のページをすべて削除してデータを最適化できます。 deleteコマンドを実行するだけで、ディスクのデータが物理的に削除されず、レコードまたはページ全体が削除済みとしてマークされることは秘密ではありません。 このコマンドを使用すると、未使用のフラグメントを削除し、インデックスを再構築できます。

おわりに

そのため、この記事では、ICQモバイルアプリケーションの例を使用して、データベース最適化へのいくつかのアプローチを検討しました。 もちろん、ほぼすべての推奨事項はwww.sqlite.orgおよびその他のテーマリソースで見つけることができますが、特定の問題を解決すると、DBMSの腸で何が起こっているかを正確に理解できます。