SQLite多对多关联表查询优化方案对比及测试需求

我有两张多对多关联的表，需要从表1中检索与表2某列特定条件相关联的结果。比如在「pizza（披萨）」和「toppings（配料）」表中，查询所有含配料a、b、c或d的披萨。

我想明确两个核心问题：

• 两种查询方式哪种性能更优：一是单次遍历表2字段筛选，二是多次遍历后取结果ID交集？
• 寻求更大的多对多关联数据库用于性能测试。

注：我使用SQLite数据库，性能优化以该数据库为核心。

我已用Kaggle的sqlite-sakila.db测试，代码如下，但当前表行数不超10000，时间差异无法明确方案优劣，想了解大数据库下的通用性能表现。

测试代码 #

public static void CheckStringWithEachID(SQLiteConnection conn) {
    Stopwatch sw = new Stopwatch();
    int count = 0;

    sw.Start();
    // AND happens before OR
    using (var cmd = new SQLiteCommand(conn)) {
        cmd.CommandText = @"            SELECT act.first_name,act.last_name, fi.title  FROM actor act
            JOIN film_actor fa ON act.actor_id = fa.actor_id
            JOIN film fi ON fa.film_id = fi.film_id
            WHERE LOWER(fi.title) LIKE LOWER('t%')
                AND LOWER(fi.title) LIKE LOWER('%r')
                OR LOWER(fi.title) LIKE LOWER('%bird%')
                OR LOWER(fi.title) LIKE LOWER('%house%')
            ";
        var reader = cmd.ExecuteReader();
        while (reader.Read()) {
            //Console.WriteLine(string.Format("{0,-12} - {1,-13} {2,-20}", reader.GetValue(0),reader.GetValue(1),reader.GetValue(2)));
            count++;
        }
    }
    sw.Stop();
    Console.WriteLine("Result query count = " + count);
    Console.WriteLine("Elapsed={0}", sw.Elapsed); //00:00:00.0539724
}
public static void CheckIfInIDs(SQLiteConnection conn) {
    Stopwatch sw = new Stopwatch();
    int count = 0;
    sw.Start();
    using (var cmd = new SQLiteCommand(conn)) {
        cmd.CommandText = @"            SELECT act.* FROM actor act
            JOIN film_actor fa ON act.actor_id = fa.actor_id
            JOIN film fi ON fa.film_id = fi.film_id
            WHERE LOWER(fi.film_id)  IN (SELECT film_id FROM film WHERE LOWER(title) LIKE LOWER('t%'))
                AND LOWER(fi.film_id)  IN (SELECT film_id FROM film WHERE LOWER(title) LIKE LOWER('%r'))
                OR LOWER(fi.film_id)  IN (SELECT film_id FROM film WHERE LOWER(title) LIKE LOWER('%bird%'))
                OR LOWER(fi.film_id)  IN (SELECT film_id FROM film WHERE LOWER(title) LIKE LOWER('%house%'))
            ";
        var reader = cmd.ExecuteReader();
        while (reader.Read()) {
            //Console.WriteLine(string.Format("{0,-12} - {1,-13} {2,-20}", reader.GetValue(0), reader.GetValue(1), reader.GetValue(2)));
            count++;
        }
    }
    sw.Stop();
    Console.WriteLine("Result query count = " + count);
    Console.WriteLine("Elapsed={0}", sw.Elapsed);
}
static void Main(string[] args) {
    using (var conn = new SQLiteConnection("Data Source=" + "sqlite-sakila.db")){
        conn.Open();
        CheckStringWithEachID(conn); // connection first time overhead? slower for some reason
        CheckStringWithEachID(conn);
        CheckIfInIDs(conn);
        conn.Close();

    }

}

测试结果 #

Result query count = 31
Elapsed=00:00:00.0058455
Result query count = 31
Elapsed=00:00:00.0059143

Result query count = 87
Elapsed=00:00:00.0080970
Result query count = 87
Elapsed=00:00:00.0065998

Result query count = 77
Elapsed=00:00:00.0049120
Result query count = 77
Elapsed=00:00:00.004717

问题解答 #

1. 两种查询方式的性能对比 #

首先要注意：你当前的测试SQL存在逻辑不一致问题——AND优先级高于OR，两种写法的过滤逻辑实际不同，导致结果行数差异，无法直接对比性能。先修正逻辑，把需要同时满足的条件用括号包裹，确保两种写法的业务逻辑一致。

另外，LOWER(fi.film_id)完全没必要，film_id是数值类型，无需转小写。

在SQLite大数据量场景下的性能表现：

• 单次遍历筛选（第一种写法）：
  只扫描film表一次，通过JOIN关联后直接过滤，避免多次子查询的重复扫描开销。前提是给film.title创建表达式索引：CREATE INDEX idx_film_title_lower ON film(LOWER(title));，否则LIKE查询会触发全表扫描，性能骤降。
• 多次子查询取交集（第二种写法）：
  每个IN子查询都会单独扫描film表（无索引时），多次扫描的叠加开销远高于单次遍历。即使有索引，多个子查询的执行计划合并效率也不如单次JOIN。仅当子查询结果集极小的时候，SQLite可能优化成哈希交集，但这种场景极少。

结论：SQLite中，单次遍历+JOIN的写法性能更优，数据量越大，优势越明显。

2. 大数据库测试方案 #

（1）生成模拟数据

用脚本生成百万级多对多关联表，比如：

• pizza表：100万条记录，含pizza_id、name字段
• toppings表：1000条记录，含topping_id、name字段
• pizza_toppings表：500万条关联记录（平均每个披萨关联5种配料）

（2）扩容公开数据集

• 导出PostgreSQL的dvdrental示例数据库到SQLite，再用数据生成工具扩容到百万级
• 生成电商类模拟数据集：包含products、categories、product_categories多对多关联表，将数据量扩至百万级

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