双列主键SQLite DELETE操作缓慢问题及优化咨询

首先，你的核心问题出在DELETE语句的WHERE条件写法错误：你用了按位与运算符&，而不是SQL标准的逻辑与AND。SQLite中&是整数位运算，这会让数据库优化器无法识别出你是在针对主键(colA, colB)做等值查询，只能触发全表扫描，这就是你看到大量pread64调用的原因。

下面是具体的优化步骤：

• 修正WHERE条件的运算符：把&替换成AND，正确的SQL语句应该是：

  DELETE FROM mytable
    WHERE colA = ? AND colB = ?;
  

  这样SQLite就能直接利用主键索引定位到目标行，避免全表扫描，这是提升性能最关键的一步。

• 使用预编译语句：因为是在循环中重复执行删除操作，不要每次都重新解析编译SQL。用预编译的Prepared Statement，只编译一次，后续循环里只需要绑定参数执行，能大幅减少重复编译的开销。

• 批量删除减少执行次数：如果循环中要删除大量行，建议把多个待删除的(colA, colB)对收集起来，用一次DELETE语句批量删除，比如：

  DELETE FROM mytable
    WHERE (colA, colB) IN ((?, ?), (?, ?), ...);
  

  这样能减少事务内的SQL执行次数，降低数据库的IO和事务开销。

• 开启WAL日志模式：SQLite默认的DELETE日志模式写性能较差，开启WAL模式可以大幅提升写操作的吞吐量，执行以下语句开启：

  PRAGMA journal_mode=WAL;
  

  注意这个设置需要在连接数据库后执行，且对整个连接生效。

• 清理冗余索引：如果表上除了主键索引还有其他不必要的索引，删除操作时需要更新这些索引，会额外增加IO和CPU开销。检查并删除不需要的索引，能提升删除效率。

内容的提问来源于stack exchange，提问作者user3188445