sqlite中的rowid

我在 sqlite 中維護了一個大型且不斷增長的表（250M+ 行）。我將其用作不同系統（在 postgres 中）上工作數據的存檔備份。一般來說，存檔大小比存檔性能更重要。因此，sqlite db 沒有索引。

每條記錄都有一個時間戳（儲存在 epoch ms 中），並且永遠不會插入過時的數據。多條記錄可以有相同的時間戳，但時間戳只會隨著時間的推移而增加。偶爾會刪除記錄。

為了驗證存檔是否與我的生產數據一起跟踪，我執行了某種“校驗和”，計算給定日期範圍內的記錄數，並將存檔與生產數據集進行比較。在存檔上執行 aselect count(*) where ts > nnn速度很慢（如預期的那樣），但我突然想到，我可能能夠使用 rowid 對 ts = nnn 的第一條記錄進行二進制搜尋，然後（可能）減去 rowids 以獲得我的計數記錄比普通選擇快得多，有點像自製索引（或者，至少，允許我通過添加where rowid > mmm and rowid < ooo到查詢中來限制需要搜尋的數據庫部分。

所以，一些問題：

1. rowid 是否以“插入順序”維護，這樣如果我的數據按時間戳順序插入，我可以假設更高的 rowid 永遠不會有更低的時間戳？
2. 因此，我可以實現自己的二進制搜尋來快速找到具有特定 ts 的記錄嗎？（或使用 max(rowid) 查找最大時間戳）？
3. 刪除記錄是否會創建 sqlite 將嘗試在後續插入中“填充”的“漏洞”？
4. 哪些事件會導致給定記錄的 rowid 發生變化？
5. 如果我決定插入過時的數據，我該如何對數據進行排序並重置 rowid 來解決這個問題？
6. 有沒有一種不那麼笨拙的方法來利用我的數據已排序這一事實，以便在不滾動自己的情況下至少獲得索引的一些好處？
7. 有沒有比計數更好的方法來驗證儲存在兩個不同系統上的數據庫中的指定日期範圍內的記錄，一個在 postgres 中，一個在 sqlite 中，包含相同的數據？（我想他們會這樣做，但我是一個偏執狂，尤其是在我的同步過程可能會以某種方式丟棄記錄的想法。我不太擔心兩個對應的記錄會包含不同的值）。

還有一些可能無關緊要的細節：postgres 生產系統在空間非常有限的機器上，只能保存大約 6 個月的數據。sqlite 數據庫在一個非常慢的機器上，有大量的磁碟空間，並且保存了幾年前的全部存檔。因為我偶爾會與其他研究人員共享整個存檔，所以 sqlite 是一個方便的容器，但我不索引，因為我希望文件在備份和運送時很小。如上所述，我對存檔數據集所做的唯一工作是添加記錄並驗證數據是否已正確備份。在本地複制存檔（例如，製作副本並在壓縮之前刪除索引）在存檔機器上是一項非常慢的操作。

rowid 的確切行為記錄在文件中。該算法是確定性的，因為 SQLite 沒有寫入並發，因此您可以在某些情況下使用 rowid 值來計算行數。

如果按順序插入行，則 rowid 值也將按順序排列。如果您不使用 AUTOINCREMENT 關鍵字，則可以重用表末尾已刪除的值，但永遠不會填充空洞。

如果您有一個 INTEGER PRIMARY KEY 列，則 rowid 值永遠不會改變。否則，它們可以在 VACUUM 期間更改。

更改多行的 rowid 順序的最簡單方法是插入到新表中。

引用自：https://dba.stackexchange.com/questions/276485