避免使用覆盖索引的SQLite3中的重复数据
避免使用覆盖索引的SQLite3中的重复数据
提问于 2016-04-15 15:51:18
在我们公司，我们有一个相当大的SQLite3数据库，比如说，有一些兴趣点(POI)。数据库只创建一次，并在移动用户应用程序中以只读模式使用.
POI的名字可以包含几个字和几个字母与对话。为了在应用程序中快速搜索POI，在主表中有一个带有单个大写ASCII单词和相应ID的附加表。还有一个覆盖指数。数据库看起来如下(简化)：
CREATE TABLE poi(id INTEGER PRIMARY KEY, name TEXT, attributes TEXT);
CREATE TABLE poi_search (word TEXT, poi_id INTEGER);
CREATE INDEX poi_search_idx ON poi_search(word, poi_id);然后，您可以使用这样的请求查询其名称包含"FOO"的POI：
SELECT * from poi INNER JOIN poi_search ON poi.id=poi_search.poi_id
WHERE poi_search.word < 'FOO' AND poi_search.word < 'FOP';查询非常快速，并且使用覆盖索引，因此根本不需要访问poi_search表：
sqlite> EXPLAIN QUERY PLAN SELECT * from poi INNER JOIN poi_search ON poi.id=poi_search.poi_id WHERE poi_search.word < 'FOO' AND poi_search.word < 'FOP';
0|0|1|SEARCH TABLE poi_search USING COVERING INDEX poi_search_idx (word<?)
0|1|0|SEARCH TABLE poi USING INTEGER PRIMARY KEY (rowid=?)我刚刚意识到这是一个很大的空间浪费，因为覆盖索引重复了索引表的所有数据。在应用程序中，实际上从未使用过表poi_search。
在这种情况下，删除或截断poi_search表，同时将所有数据保存在覆盖索引中，甚至是一种棘手的方法？我知道这样的数据库将处于一种不连贯的状态，因此官方API可能无法进行这样的攻击。
对于数据库的生成，我不在乎SQLite3的黑客版本；但是DB必须在普通的SQLite3客户机中为给定的请求生成正确的搜索值。
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-04-15 17:24:48
没有任何棘手的方法，或黑客，去做你想做的事。您必须使用记录方式，这保证了数据库的一致性：
CREATE TABLE poi_search (
word TEXT PRIMARY KEY,
poi_id INTEGER
) WITHOUT ROWID;
-- no other index needed页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36651647
复制相关文章
相似问题
腾讯云开发者
