通过直接查询索引获得不同的值

Question

我正在构建一个相对较大的SQLite数据库。该数据库的一般访问简介是：

1. 初始负载为3亿行。
2. 对每一行进行初始读取，并更新大约3000万行
3. 通过带有MyDatabaseReader类的python脚本正在进行只读访问。

数据结构是一个扁平的非规范化表，查询的形式如下：

WHERE chromosome=TEXT, position=INT, reference_sequence=TEXT, alternate_sequence=TEXT

我想验证一个传入的查询根据数据库的内容请求一个有效的染色体名。请求染色体=‘the 13’是有效的，但是对于'13‘或'chr31’，我希望我的python脚本抛出一个错误，而不是默默地返回零行。为了启用这个功能，在python类的init期间，它进行了一个初始查询，以获得一组有效的染色体名称：

SELECT DISTINCT chromosome from dbsnp;

这个查询需要很长的时间，并且无法执行。我尝试了染色体上的复合索引(染色体、位置、reference_sequence、alternate_sequence)以及染色体上的单一索引，并通过解释查询方案验证了在这两种情况下都使用了索引。

sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT chromosome FROM dbsnp;
order|from|detail
0|0|TABLE dbsnp WITH INDEX chromosome ORDER BY

我的问题是:是否有一些SQL技巧可以直接从染色体索引中查询。我不关心行中的其他内容，而且索引似乎是我想返回的数据的预构建版本。

或者，我正在考虑构建一个chromosome_names表，在数据库加载和更新之后，我在上面使用我的SELECT DISTINCT查询填充该表。因为它让我害怕构建一个可能与主表不同步的静态表，所以我正在考虑触发器来更新主表更改后的chromosome_names表。但是，我担心，如果我更新主表中的行，这可能会引起很大的波动，更重要的是，我正在重新发明索引中包含的内容。

是否有一种好的方法可以直接从索引中获取我的不同值查询，或者是否有一种方法可以让SQLite抛出一个错误，如果染色体的被查询值在包含值集之外(注意:位置、引用和alt序列有时会查询意外值，因此返回的零行错误将无法工作)。

谢谢

Answer 1

我从来没有用过SQLite，所以在这里要忍受我。但是，这个问题似乎在许多RDBMS平台中很常见。

当从列中选择不同的值时，最终会扫描索引中的所有行：

如果表中没有很多行，或者列中没有很多重复的值，这可能是一个很好的策略。但是，如果每个不同的值都有数百万行，那么您将扫描数百万行，以返回一个唯一值。对于这样的数据集，有时最好是获得第一个不同的值，然后跳到下一个值，以此类推。这可以通过在某些平台上的递归来实现。您还可以一次运行一个查询，每个查询都获得下一个不同的值。例如，您可以通过这个查询获得第一个值：

SELECT MIN(chromosome) FROM dbsnp;

然后使用此查询获取下一个值(用第一个查询的值替换过滤器)：

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_1' ORDER BY chromosome LIMIT 1;

下一个是：

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_2' ORDER BY chromosome LIMIT 1;

诸若此类。对于这些查询，我得到的索引查找：

对于一个相对较小的数据集，单个不同的查询大约需要320 ms，而一系列LIMIT 1查询只需要4 ms。当然，您需要编写更多的代码来使用此解决方案，但这可能值得一试。

db小提琴

Answer 2

在SQLite中，您可以使用INDEXED BY my_index告诉优化器使用特定的命名索引(https://www.tutorialspoint.com/sqlite/sqlite_索引_by.htm)。

试试看：

SELECT chromosome FROM dbsnp INDEXED BY chromosome GROUP BY chromosome

编辑:在1亿张唱片之后，这确实会减慢很多速度。在浏览了一些之后，您最好更改您的程序，以便在处理之前对DB进行快速检查：

db.execute("SELECT COUNT(*) FROM (SELECT 1 FROM dbsnp WHERE chromosome = ? LIMIT 1) sub", input).fetchall()

如果数据集存在，则为1，如果不存在，则为0，且运行速度较快。