Sqlite：在由整數元組組成的表中查找下一個或上一個元素

我有一個名為 tuples 的 sqlite 表，定義如下

create table tuples
(
   a INTEGER not null,
   b INTEGER not null,
   c INTEGER not null,
   d INTEGER not null,
   primary key (a, b, c, d)
) without rowid;

充滿了數百萬個獨特的元組（> 1TB）。新元組經常被插入，帶有“隨機”值。僅在極少數情況下才會刪除行。

對於訪問數據庫的外部程序，我需要在表中找到“下一個”或“上一個”現有的 4 元組。

例如：給定元組 (1-1-1-1)、(1-1-1-4) 和 (1-2-3-4)，對於元組 (1-1-1-3)（它確實不需要存在於表中）“下一個”元素是（1-1-1-4），前一個是（1-1-1-1）（兩者都需要存在）。對於 (1-1-1-4) (1-2-3-4) 是“下一個”元素。極端情況：如果實際上沒有“下一個”或“上一個”元素，則結果允許為空。(1-2-3-4) 沒有“下一個”元素。

目前我試圖找到下一個元組 (“center” is (1-1-1-3))

select a,b,c,d from tuple
where (a == 1 AND b == 1 AND c == 1 AND d > 3) OR
     (a == 1 AND b == 1 AND c > 1) OR
     (a == 1 AND b > 1) OR
     (a > 1)
order by a, b, c, d
limit 1;

這真的很慢。

這裡的簡短問題是：有沒有辦法加快這個過程？理想情況下，響應應該只需要幾毫秒，例如搜尋元組的確切值（基本上是瞬時的）。使用其他/更多索引、多個和/或其他查詢，甚至更改數據庫結構都是有效的解決方案。

---

編輯：元組的每個元素都可以覆蓋整個允許的整數範圍。

目前我試圖找到下一個元組 (“center” is (1-1-1-3))

測試

SELECT *
FROM tuples
WHERE (1,1,1,3) < (a,b,c,d)
ORDER BY a,b,c,d LIMIT 1

小提琴

您應該將 4 個值連接起來儲存，然後在Generated Column中轉換為 int 。然後，您可以索引該列以獲得更好的性能，然後您的查詢只是與一個更簡單/更易於維護的列的比較。

您查找下一個元組的查詢如下所示：

select a,b,c,d from tuple
where 1113 < GeneratedColumn
order by GeneratedColumn
limit 1;

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