如何提高sqlite查询速度

Question

我在空间-gui公司工作，用的是sqlite。我有一个大表(80,000,000条记录)，我想要创建一个表，其中包括来自另一个表的代码，该表大约有48,000条记录。

我编写了以下代码，并在我的大表的第一个80k记录上进行了测试；运行时间大约为1:25。当我把它放在大桌子上的时候，它跑了100个小时，最后我取消了，结果没有结果。

我能做些什么来提高查询性能？我有经度索引，但没有纬度索引；这两个字段都不是唯一的。lut.pointgeom和lut.gridgeom都是BLOB字段，并不是严格必要的。

CREATE TABLE policy_gcode AS
SELECT 
  p.*,
  lut.gcode,
  lut.pointgeom,
  lut.gridgeom
FROM
  allpol AS p
  JOIN policylutgrid AS lut 
    ON  p.latitude = lut.latitude 
    AND p.longitude = lut.longitude;

Answer 1

在此之前，您希望在每个具有纬度和经度字段的表上使用一种方形的多层胶印，排序方式与加入它们的方式相同。这将允许数据库引擎优化查询中发生的联接操作。

CREATE INDEX allpol_idx ON allpol(latitude, longitude);
CREATE INDEX policylutgrid_idx ON policylutgrid(latitude, longitude);

只选择您真正需要的字段也是一个好主意:如果您不需要BLOB(或其他字段)，不要要求它。这将减少数据库必须处理的数据量。

如果这些措施不能解决问题，那么您必须查看图片的其他部分：

• 向数据库分配更多的系统资源
• 迁移到比sqlite更强大的RDBMS

Answer 2

显然，每个表中的(latitude, longitude)索引--如果没有它们--将有助于查询(实际上，一个索引就足够了)。但最基本的问题可能是数据的大小。

如果结果集是。。。真的很大。您可以通过运行以下命令来计算大小：

select sum(p.cnt * lut.cnt)
from (select latitude, longitude, count(*) as cnt
      from allpos
      group by latitude, longitude
     ) p join
     (select latitude, longitude, count(*) as cnt
      from policylutgrid
      group by latitude, longitude
     ) lut
     on p.latitude = lut.latitude and p.longitude = lut.longitude;

这是您期望的表的大小。如果它真的很大--比如说超过几十亿行--结果集的大小可能是决定性因素。如果是这样的话，那么问题是表中lats/long的重复值。进一步的调查可能会建议对他们采取什么行动。