AndroidのSQLiteのGUIDのような主キー

イントロ

SQLiteの各テーブルには、デフォルトで、自動生成された64ビット整数に基づく秘密鍵が含まれています。 ほとんどの状況で効果的で便利です。 不便は、おそらく次の2つの場合にのみ始まります。

• 64ビットの範囲では不十分な場合（SQLiteタスクの利便性について考える必要があります）
• ストレージが「分散」になったとき

SQLiteと組み合わせた2番目のタスクは発生しないように思えるかもしれませんが、配布は常にBigDataのようなものを意味するわけではありません。 典型的な例（私が個人的にこのトピックに関する研究を必要とした理由）は、デバイス間でデータを同期する機能を備えたアプリケーションです。 ノートブックのように小さなものでも、ブラウザの履歴のようにもっとロードされたものでもかまいません。 ここでの問題は、いくつかのデータベースのマージほどデータの量ではありません。 明らかに、1から始まる整数レコードカウンターは必然的に競合するシーケンスを生成します。つまり、複数のデバイス上のレコードの一意の識別子として使用することはできなくなります。 サブ識別子を転送する前にサブ識別子に分割したり、レコード識別子を「シフト」したりすると混乱することがありますが、これらはすべて曲がった壊れやすい松葉杖です。 もちろん、誰もそれをしません。 代わりに、各デバイスはGUIDのようなものをそのエントリに-簡単かつ確実に割り当てます。

主キーとしてのGUID

GUIDは、ランダムな128ビットの「番号」です。 つまり、データベースでは、BLOBの形式では16バイト、または文字列の形式では少なくとも32バイトになります。 非常に効率的に格納されるデフォルトのキーと比較して、特定のオーバーヘッド（特に他の列が小さい場合）。通常は8バイトではなく、キー値が表示に必要な量です。 問題の解決のためにこのオーバーヘッドを支払う準備はできていますが、それを悪化させたくはありません。したがって、もちろん、テキスト文字列ではなくバイナリ形式で保存することを好みます。

さて、blobを使用して列を宣言するのは簡単です。プリミティブラベルを作成しましょう。

CREATE TABLE records (id BLOB PRIMARY KEY, data CHARACTER);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

また、最適化のためのテーブル修飾子としてWITHOUT ROWID



を追加することもできます。これにより、SQLiteは暗黙的なキー列を追加またはサポートしません。

デフォルトの整数キーの場合と同じように、データベースが独自に識別子を生成するように強制する必要がない場合は、トピックを閉じることができます。 実際の GUID（単なる乱数ではなく、いくつかの定義済みのビットがある）に対する基本的な要件がない場合、これも簡単です。

 CREATE TABLE records (id BLOB PRIMARY KEY DEFAULT (randomblob(16)), data CHARACTER);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

完璧主義のためでなければ、これは再び止められるでしょう。 これから先、厳しい完璧主義であっても、何百万ものレコードをデータベースに挿入しないのであれば、これはやめる価値があります。 しかし、そうすることができる場合は、インデックスに費用がかかるため、長い乱数はキーとしてあまり適していないことに注意する必要があります。レコードを1つずつ追加してクイック検索を期待するだけでは機能しません。

幸いなことに、この問題は長い間成功裏に解決されており、このソリューションは多くのデータベースに適用されています 。 つまり、すべてが単純です。識別子の最初の6バイトはタイムスタンプに置き換えられます。 その結果、レコードはすぐに（部分的に）順序付けされて作成され、インデックス作成が非常に容易になります。 衝突の可能性は高くなりますが、大きくはなりません。 また、Android APIのSQLiteDatabaseを使用して外部関数を定義し、ガイドのようなBLOBを生成できた場合 、ストーリーはこの時点で正確に終了します。 もちろん、Javaコードで生成し、すべての挿入リクエストにバインドできますが、これはスポーツマンらしくないです。 さらに、これを行わない他の理由があるかもしれません。 たとえば、「グローバル」識別子を「ローカル」識別子とは別に保持し、トリガーを使用して必要に応じて生成する必要があります。

さて、unixタイムスタンプから6バイトを取得できますが、次のように半分になります 。

 SELECT round((julianday('now') - 2440587.5) * 86400000) & 0xFFFFFFFFFFFF AS ts;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果は数値になります。 たとえば、これは：1489877740453-執筆時点。 良いニュースは、通常は減少しないことであり、データベース自体の手段と考えることができます。 しかし、その後、いくつかの困難が始まります。 実際のところ、SQLiteにはBLOBを操作するための非常に限られた関数セットがありsubstr()



）とglue（ ||



）のみです。 また、数値をバイト文字列として解釈する方法は明確ではありません。 つまり、確かにCAST(... AS BLOB)



作成できますが、そうではありません。 数値を文字列に変換し 、受信した文字列のバイトを取得します。つまり、6バイトを13に変換します。 -12.転がりません。

数値をBLOBに変換します

... SQLiteでは不可能-GoogleとStackOverflowが答えます。 もちろん、それは真実ですが、本当にやりたいのであれば、実際にできます。 インターネット上で何かを見つけることができなかったので、自分で発明しなければなりませんでした。 私はすぐに言わなければなりません：それは汚れます:)

したがって、接着（ ||



）があります。つまり、2つのバイト行（タイムスタンプとランダム部分）があり、ジミーネルソンのCOMBを取得できます。

 SELECT ts_bytes || randomblob(10);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

望ましいts_bytes



は、整数を表す6バイトの文字列です。 もう一度見てみましょう： 1489877740453



。 または0x 01 5A E3 A2 2B A5



。 各バイトを個別にBLOBの形式で取得し、それらを一緒に接着できる場合-手動モードでも、すべて（常に）6個の接着剤です。 さて、数字をバイトに分割してみましょう。 それらの数値は、少しの算術を使用して取得できます。

• 最初： ts >> 40 == 1
  
  
  
  （0x01）
• 秒： (ts >> 32) % 256 == 90
  
  
  
  （0x5A）
• 3番目： (ts >> 24) % 256 == 227
  
  
  
  （0xE3）
• ...

しかし、再び、これらはまだバイトではありません。 SQLiteインタープリターは、これを単に数字と見なします。

 SELECT typeof( (1489877740453 >> 24) % 256 ); integer
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、BLOBが必要です。 受信した番号を表す1バイトのBLOB。 もちろん、これはできませんが、テーブルのようなものがあった場合、バイト値は256個しかありません。 ここでは、SQLiteで使用可能な2番目の操作を思い出しsubstr



。これは、インデックスによって文字またはバイトの部分文字列を返すsubstr



返します 。 ビンゴ！ すべてのバイト値を文字列にハードコードします。インデックス自体はこのバイトの値になります。 幸いなことに、x'DEADBEEF 'の形式の構文を使用してバイナリリテラルを記述できます。

 SELECT X' 000102030405060708090A0B0C0D0E0F 101112131415161718191A1B1C1D1E1F 202122232425262728292A2B2C2D2E2F 303132333435363738393A3B3C3D3E3F 404142434445464748494A4B4C4D4E4F 505152535455565758595A5B5C5D5E5F 606162636465666768696A6B6C6D6E6F 707172737475767778797A7B7C7D7E7F 808182838485868788898A8B8C8D8E8F 909192939495969798999A9B9C9D9E9F A0A1A2A3A4A5A6A7A8A9AAABACADAEAF B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF' as b;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

行をこのようにラップすることはできませんが、これは少し擬似的なコードですが、より視覚的であり、コードでは1行もフォーマットできます。 しかし、今やるべきことは、テーブルから目的のバイトを「切り取り」、他のバイトと接着することだけです。 6回：

 SELECT substr(b, (ts >> 40) + 1, 1) || substr(b, (ts >> 32) % 256 + 1, 1) || substr(b, (ts >> 24) % 256 + 1, 1) || substr(b, (ts >> 16) % 256 + 1, 1) || substr(b, (ts >> 8) % 256 + 1, 1) || substr(b, ts % 256 + 1, 1) || randomblob(10);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

バイナリ形式の疑似GUIDの準備ができました！ 実際、SQLiteは受信したバイト文字列を「テキスト」と見なしますが、 CAST(... AS BLOB)



は必要なすべてを実行します。 実際には、これは必要です。さもないと、この列からの読み取りは16バイトではなく、null文字列ターミネータで17バイトを返すからです。 式をデフォルトの列値として置き換えることは残ります。

自動挿入ID

テーブル列定義のDEFAULT(...)



内にこのすべての「トレーニング」を単純にプッシュすることは不可能DEFAULT(...)



なぜなら、「単純な」式のみが存在するはずであり、同じもののコピーアンドペーストと複数の計算を避けるためにネストされたSELECTが必要だからです

幸いなことに、SQLiteにはトリガーを使用して、貼り付け時にその場で行を変更できます。 残念ながら、 BEFORE INSERT



フェーズもAFTER INSERT



もPRIMARY KEY



サービスには適していません。 暗黙的な条件NOT NULL



を満たすには、列値を初期クエリで指定する必要があります。 また、このようなトリガーの場合、UPDATE式ではプリミティブ式のみが許可されます。 ただし、 INSTEAD OF INSERT



トリガーのタイプは使用可能です。これにより、生成されたblobを追加して、転送された値に基づいて新しいレコードを作成できます。 ドキュメントには指定されていない機能が1つだけあります。INSTEADOF INSERTトリガーはテーブルに作成できません。 VIEWでのみ可能です。

その結果、回路は次のように構築されます。

• メインテーブルは明らかに読み取り専用です
• 書き込み要求は、挿入トリガーを使用してVIEWダミーに送られます
• トリガーはblobを生成し、メインテーブルに挿入します

 CREATE TABLE records (id BLOB PRIMARY KEY, data CHARACTER) WITHOUT ROWID; CREATE VIEW fake AS SELECT NULL as ts, NULL as data; CREATE TRIGGER auto_guids INSTEAD OF INSERT ON fake BEGIN INSERT INTO records(id, data) SELECT CAST(new_guid AS BLOB), NEW.data FROM ( SELECT substr(b, (ts >> 40) + 1, 1) || substr(b, (ts >> 32) % 256 + 1, 1) || substr(b, (ts >> 24) % 256 + 1, 1) || substr(b, (ts >> 16) % 256 + 1, 1) || substr(b, (ts >> 8) % 256 + 1, 1) || substr(b, ts % 256 + 1, 1) || randomblob(10) AS new_guid FROM (SELECT round((julianday('now') - 2440587.5) * 86400000) & 0xFFFFFFFFFFFF as ts, x'000102030405060708090A0B0C0D0E0F...' as b ) ); END;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

いつものように読みます：

 SELECT * FROM records;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして次のように書きます：

 INSERT INTO fake (data) VALUES ('Hello COMBs!');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

読みやすくするためにバイトテーブルを別のVIEWに置くこともできますが、これはパフォーマンスにわずかに悪影響を及ぼします。 また、整数カウンターに主キーを残し、 guid



列を一意にして、新しいguid



で行を「再追加」するON AFTER INSERT



トリガーを作成することもできますが、少し先に実行すると、約30％遅くなります。 ところで、パフォーマンスを見てみましょう。

性能

明らかに、手動でバイトを接着することは、組み込みのrandomblob()



関数よりも遅くなります。 多数の挿入で勝ちが現れるはずです。 測定を行います。 「通常の」整数ROWID、 randomblob(16)



基づくキー、および部分的に順序付けられたBLOB（上記の記事で呼ばれたCOMB）を比較します。

テストシナリオは次のとおりです。

• 100万レコードあたり3つの挿入トランザクション
• IDによる挿入されたすべてのレコードのランダム選択

記録時間は、各シリーズおよび記録の20％ごとに内部的に測定されます。 テストは、Android 6.0エミュレーター（SQLite 3.8.10）で実行されました。 ソースはこちら 。

チャート上：20万件のレコードの各後続部分の挿入時間。 もちろん、パフォーマンスベンチマーク、デフォルトの整数インデックス（青い線）。 その速度は、連続挿入の数に依存しません。 黄色の線（COMB）は私たちの患者です。 その速度もほぼ一定ですが、55〜59％低下します。 そして、赤い線は、randomblob（16）に主キーを持つテーブルです。 INTEGER PRIMARY KEYよりも11％だけ遅く、最初の100万回の挿入の後、インデックスを維持するためのオーバーヘッドコストは部分的に順序付けられたシーケンスを超えて増加し続け、300万の終わりまでに75％の減速に達することがわかります。

実際、COMBはさらに高速に実行できます。 現在の問題は、ミリ秒精度のタイムスタンプでは、隣接する行が18〜20個のクラスターに配置され、最初の6バイト（タイムスタンプ）が同じであるため、ランダムバイトの配置の問題が部分的に返されます。 タイムスタンプに追加するレコードのシリアル番号を（少なくともトランザクション内で）何らかの方法で追加すると、オーバーヘッドが「INT」に比べて29-34％に減少し、50万レコード後のrandomblob(16)



に比べて増加します。

欠点は、最も単純な場合、シリアル番号を制御コードから転送する必要があることですが、タスクの条件に応じてこれを行いたくないのです。 さらに、結論はすでに引き出すことができます。

結論

SQLite自体は、GUIDのようなものであっても、非常に優れたインデックス付けを行います。

推定データ量が少なくとも50万レコードを超えない場合、純粋なrandomblob()



パフォーマンスは非常に許容範囲です。 私は現在のプロジェクトに参加しているため、おそらくそれを選択します。

多くのレコードがありますが、それらがめったに挿入されないか、さらに単一レコードの形式である場合でも、主キーのタイプはパフォーマンスにまったく影響しません。 トランザクションの修正（Androidでデフォルトのデータベース設定を使用）のみで、約20〜50ミリ秒かかります。 IOシステムが過負荷になると、さらに何倍にもなります。 これと比較して、マイクロ秒単位で発生する大量のトランザクション内にレコードを挿入すると、どんな状況でも取るに足らない時間がかかります。

SQLiteでは、数字をブロブに変えることができます-それは願いです:)