如何替换SQLite数据存在性判断逻辑 提升大数据库处理速度

原有代码核心问题 #

• 全量拉取所有Data like 'P%'的记录到内存遍历，数据量越大，数据库IO、数据传输、内存遍历的开销越高，是耗时过长的核心原因
• 逻辑存在严重错误：每遇到一条不匹配的记录就执行一次插入语句，会触发大量无意义的数据库写操作，额外占用大量资源
• 未对Data字段加索引，所有涉及Data的查询都是全表扫描，效率极低
• 循环内重复创建SQLiteCommand对象，产生不必要的性能开销
• 生成bItems时直接用+=拼接字符串，频繁生成新的string对象，额外占用内存

优化步骤 #

1. 先给表B的Data字段创建唯一索引，一次创建永久生效，既可以把存在性查询的耗时从O(n)降到O(1)，也能从数据库层面避免重复数据插入

CREATE UNIQUE INDEX IF NOT EXISTS idx_b_data ON B(Data);

2. 重构数据库逻辑：去掉全量查询+内存遍历的逻辑，所有存在性判断放到数据库端完成，直接查询目标bItems是否存在，不需要拉取无关数据。用SQLite的INSERT OR IGNORE语法配合查询，只需要一次数据库交互就能实现「不存在则插入、存在则直接返回ID」的需求。

3. 替换字符串拼接方式为StringBuilder，降低字符串生成的开销。

优化后完整代码 #

// 优化bItems生成逻辑，避免频繁string内存分配
StringBuilder sb = new StringBuilder();
for (int p = 0; p < 256; p++)
{
    sb.Append("P");
    sb.Append(buffer[p]);
}
string bItems = sb.ToString();
string sItems = string.Empty;

using (SQLiteConnection con = new SQLiteConnection(databaseObject.myConnection))
{
    con.Open();
    // 单组SQL实现：不存在则插入，随后直接返回匹配的ID
    const string sql = @"
        INSERT OR IGNORE INTO B (Data) VALUES (@Data);
        SELECT ID FROM B WHERE Data = @Data;
    ";
    using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@Data", bItems);
        // 直接获取匹配的ID，不需要遍历所有记录
        object result = cmd.ExecuteScalar();
        if (result != null)
        {
            sItems = "B" + result.ToString();
        }
    }
}

Console.WriteLine(sItems);

性能提升说明 #

优化后原来的全表拉取+内存遍历逻辑被替换为单次索引查询，即使表内数据量达到百万级，查询耗时也可以稳定在毫秒级，相比原逻辑性能提升至少两个数量级。

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