主页 / dba / 问题 / 281171
Accepted
std_unordered_map
Asked: 2020-12-10 02:40:46 +0800 CST

Sqlite：在由整数元组组成的表中查找下一个或上一个元素

  • 772

我有一个名为 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;

这真的很慢。

这里的简短问题是：有没有办法加快这个过程？理想情况下，响应应该只需要几毫秒，例如搜索元组的确切值（基本上是瞬时的）。使用其他/更多索引、多个和/或其他查询，甚至更改数据库结构都是有效的解决方案。

编辑：元组的每个元素都可以覆盖整个允许的整数范围。

query-performance performance-tuning

2 个回答

  1. Best Answer

    目前我试图找到下一个元组 ("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

    小提琴

    • 9

  2. 您应该将 4 个值连接起来存储，然后在Generated Column中转换为 int 。然后，您可以索引该列以获得更好的性能，然后您的查询只是与一个更简单/更易于维护的列的比较。

    您查找下一个元组的查询如下所示：

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

相关问题