performance - 在sqlite中如何在DELETE期间避免锁定？

Question

DELETE查询很简单：

DELETE FROM pages WHERE status = 0

完成大约15分钟（删除约2万行）。这是一个约500 MB的数据库，可映射本地文件系统，并包含约300万条记录。

结构：


pages-仅有几条记录
files-约23万条记录，包含带有 ON DELETE CASCADE的外键约束，该约束引用 pages中的列
meta-大约300万条记录，包含带有 ON DELETE CASCADE的外键约束，这些约束引用 files和 pages中的列
search-FTS4表，几乎与 meta完全相同。该表的完整性由触发器维护。

CREATE TABLE pages(
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  slug     TEXT,                           
  name     TEXT NOT NULL,
  type     INTEGER NOT NULL DEFAULT 1,     
  data     TEXT,
  parent   INTEGER,                        
  status   INTEGER DEFAULT 1,              
  comments INTEGER DEFAULT 1,              
  priority INTEGER DEFAULT 0,              
  UNIQUE(slug),
  FOREIGN KEY(parent) REFERENCES pages(id) ON DELETE CASCADE   
);

CREATE INDEX "pageParent" ON "pages"("parent");


CREATE TABLE files(
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  gallery   INTEGER NOT NULL,                      
  type      INTEGER NOT NULL DEFAULT 1,            
  sPath     TEXT,                                  
  rPath     TEXT,                                  
  parent    INTEGER,  
  hero      INTEGER,
  hidden    INTEGER DEFAULT 0,                     
  createdAt DATETIME,                              
  mTime     TEXT,                                  
  UNIQUE(sPath),
  FOREIGN KEY(gallery) REFERENCES pages(id) ON DELETE CASCADE,
  FOREIGN KEY(parent)  REFERENCES files(id) ON DELETE CASCADE,
  FOREIGN KEY(hero)    REFERENCES files(id) ON DELETE SET NULL
);

CREATE INDEX "fileGallery" ON "files"("gallery");
CREATE INDEX "fileType"    ON "files"("type");
CREATE INDEX "fileParent"  ON "files"("parent");
CREATE INDEX "fileRPathNS" ON "files"("rPath" COLLATE NATSORT);


CREATE TABLE thumbs(          
  hash    TEXT,  
  image   INTEGER,
  width   INTEGER,
  height  INTEGER,            
  FOREIGN KEY(image) REFERENCES files(id) ON DELETE CASCADE,
  PRIMARY KEY(hash, image) ON CONFLICT REPLACE
);

CREATE INDEX "thumbImage" ON "thumbs"("image");


CREATE TABLE meta(
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  file    INTEGER NOT NULL,
  key     TEXT NOT NULL,
  value   TEXT,         
  extra   TEXT,         
  gallery INTEGER,
  FOREIGN KEY(gallery) REFERENCES pages(id) ON DELETE CASCADE,  
  FOREIGN KEY(file) REFERENCES files(id) ON DELETE CASCADE
);

CREATE INDEX "metaFileId" ON "meta"("file"); 
CREATE INDEX "metaKey"    ON "meta"("key"); 
CREATE INDEX "metaExtra"  ON "meta"("extra"); 


CREATE VIRTUAL TABLE search USING fts4(file, key, value, gallery);

CREATE TRIGGER metaBeforeUpd BEFORE UPDATE ON meta BEGIN
  DELETE FROM search WHERE docid = OLD.rowid;
END;

CREATE TRIGGER metaBeforeDel BEFORE DELETE ON meta BEGIN
  DELETE FROM search WHERE docid = OLD.rowid;
END;

CREATE TRIGGER metaAfterUpd AFTER UPDATE ON meta BEGIN
  INSERT INTO search(docid, file, key, value, gallery) VALUES(NEW.rowid, NEW.file, NEW.key, NEW.value, NEW.gallery);
END;

CREATE TRIGGER metaAfterIns AFTER INSERT ON meta BEGIN
  INSERT INTO search(docid, file, key, value, gallery) VALUES(NEW.rowid, NEW.file, NEW.key, NEW.value, NEW.gallery);
END;

问题在于，不仅速度很慢，而且还会锁定这些表，因此在此期间我无法对其进行任何更改。

我尝试了 this question and answers中的所有建议，但没有明显改进。将日志记录模式设置为MEMORY并关闭同步可以使其运行得更快一些，但这太冒险了。

为了避免长时间的写锁定，我尝试逐步删除记录，每次删除40条记录，间隔为0.5秒。但这甚至会使整个过程减慢10倍

还有其他方法可以提高速度和/或避免锁定吗？



PS：让我感到困惑的是INSERT的速度要快得多。插入要删除的记录数量需要2分钟，而这段时间包括一些繁重的文件处理（Exif从大量图像中读取）。为什么删除记录比插入慢？

Answer 1

从pages删除很慢，因为gallery表的meta列上没有索引。
每当真正删除pages记录时，数据库都必须搜索与ON DELETE CASCADE约束匹配的任何meta记录；这将对每个已删除的记录进行全表扫描。

（INSERT更快，因为不需要进行此类检查。）

SQLite不是为并发而设计的。永远不可能同时拥有多个作者。
但是，要允许多个读者同时作为一个作者，请考虑启用write-ahead logging。