sql - 如何提高sqlite查询速度

Question

我正在使用 sqlite db 在 spatialite-gui 中工作。我有一个大表(80,000,000 条记录)，我想创建一个表，其中包含另一个表中的代码，该表有大约 48,000 条记录。

我编写了以下代码，并在我的大表的前 80k 条记录上进行了测试；运行大约需要 1:25。当我在大 table 上运行它时，它运行了 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

毕竟，你想要a sqlite multicolumn indice在具有纬度和经度字段的每个表上，以与加入它们相同的方式排序。这应该允许您的数据库引擎优化查询中发生的 JOIN 操作。

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

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

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

为您的数据库分配更多系统资源

迁移到比 sqlite 更强大的 RDBMS