为何SQLite查询远慢于Python代码？如何优化至同速？

背景与需求 #

我有一个SQLite数据库，其中users和posts表的创建语句如下：

cursor.execute('''CREATE TABLE IF NOT EXISTS users
             (user_id INTEGER PRIMARY KEY,
              user_name TEXT,
              num_posts_parsed INTEGER DEFAULT 0,
              num_posts_userpage INTEGER DEFAULT 0,
              num_received_likes INTEGER DEFAULT 0,
              num_points INTEGER DEFAULT 0,
              banned INTEGER DEFAULT 0,
              deleted INTEGER DEFAULT 0,
              join_date TEXT)''')

cursor.execute('''CREATE TABLE IF NOT EXISTS posts
             (post_id INTEGER PRIMARY KEY,
              thread_id INTEGER,
              author_id INTEGER,
              post_number INTEGER,
              creation_date TEXT,
              post_text TEXT,
              post_link TEXT,
              number_chars INTEGER DEFAULT 0,
              number_words INTEGER DEFAULT 0,
              number_smilies INTEGER DEFAULT 0,
              number_likes INTEGER DEFAULT 0,
              number_edits INTEGER DEFAULT 0,
              FOREIGN KEY (thread_id) REFERENCES threads(thread_id),
              FOREIGN KEY (author_id) REFERENCES users(user_id))''')

posts表有210万行数据，users表约1万条记录。需要实现以下需求：

• 获取所有2019-04-01之后创建且包含至少一个词的帖子；
• 为发布此类帖子的每位作者统计单帖获赞数（number_likes字段）；
• 仅纳入2019-04-01之后发帖量≥100的作者；
• 检索单帖获赞比最高的前n位作者（用户名）。

原SQL查询（速度极慢） #

我写的SQL查询运行数小时后被迫终止：

SELECT users.user_name, CAST(SUM(posts.number_likes) AS FLOAT) / COUNT(posts.post_id) as ratio
FROM users 
JOIN (
    SELECT *
    FROM posts
    WHERE number_words > 0 AND creation_date >= '2019-04-01'
) AS posts ON users.user_id = posts.author_id
WHERE users.user_id IN (
    SELECT author_id
    FROM posts
    WHERE creation_date >= '2019-04-01'
    GROUP BY author_id
    HAVING COUNT(*) >= 100
)
GROUP BY users.user_name
HAVING SUM(posts.number_likes) > 0
ORDER BY ratio DESC
LIMIT ?

对比：Python函数（几秒完成） #

对应的Python函数仅需几秒就能完成：

def get_users_likes_per_post_after_like_introduction(db, n):
    query = '''SELECT author_id, number_likes
                FROM posts 
                JOIN users ON users.user_id = posts.author_id
                WHERE number_words > 0 AND creation_date >= '2019-04-01'
                '''
    db.cursor.execute(query)
    authors_likes = db.cursor.fetchall()
    res = {}
    for (author_id, likes) in authors_likes:
        if author_id in res:
            res[author_id][0] += 1
            res[author_id][1] += likes
        else:
            res[author_id] = [1, likes]
    likes_per_post = [(author_id, likes / num_posts) for author_id, (num_posts, likes) in res.items() if
                      num_posts >= 100]
    likes_per_post_sorted = sorted(likes_per_post, key=lambda x: x[1], reverse=True)

    relevant_part = likes_per_post_sorted[:n]
    user_ids = [x[0] for x in relevant_part]
    like_ratio = [x[1] for x in relevant_part]

    user_names = []
    for uid in user_ids:
        query = f"SELECT user_name FROM users WHERE user_id  = {uid}"
        user_names.append(db.cursor.execute(query).fetchone()[0])

    return [(name, like_ratio) for name, like_ratio in zip(user_names, like_ratio)]

问题核心 #

• 为什么这条SQL语句速度如此缓慢？
• 如何写出速度与Python代码相近的SQL语句？

注：数据库未创建任何索引。

原因分析：SQL慢的根源 #

1. 重复全表扫描：posts表210万行，无索引情况下，两次子查询都会遍历整个表——一次筛选符合条件的帖子，一次统计作者发帖量，双重开销直接拖慢速度。
2. 低效的子查询与JOIN：内层子查询SELECT *返回所有字段，JOIN时要处理大量冗余数据；同时IN子查询在无索引时会触发嵌套循环，进一步增加耗时。
3. 不合理的GROUP BY：先JOIN再GROUP BY会处理远超必要的数据量，而且按user_name（字符串）分组比按user_id（整数主键）分组慢很多，因为字符串排序和哈希的开销更大。

优化后的SQL方案（接近Python速度） #

第一步：创建必要索引（关键前提） #

没有索引的话，SQL不可能达到Python的速度，先创建复合索引：

-- 覆盖筛选、关联、聚合的核心字段，避免回表
CREATE INDEX idx_posts_author_date_words ON posts(author_id, creation_date, number_words);
CREATE INDEX idx_posts_author_likes ON posts(author_id, number_likes);

这些索引能让数据库直接定位目标数据，无需遍历全表，同时从索引中直接获取聚合所需字段，跳过回表查询。

第二步：优化后的查询语句 #

SELECT u.user_name, 
       CAST(SUM(p.number_likes) AS FLOAT) / COUNT(p.post_id) AS ratio
FROM (
    -- 先筛选出符合发帖量要求的作者，缩小后续处理范围
    SELECT author_id
    FROM posts
    WHERE creation_date >= '2019-04-01'
    GROUP BY author_id
    HAVING COUNT(*) >= 100
) AS qualified_authors
-- 仅关联合格作者的有效帖子（时间+字数符合要求）
JOIN posts p 
    ON qualified_authors.author_id = p.author_id
    AND p.creation_date >= '2019-04-01'
    AND p.number_words > 0
-- 关联用户表获取用户名
JOIN users u ON qualified_authors.author_id = u.user_id
-- 按user_id（整数）分组，比按用户名高效
GROUP BY u.user_id, u.user_name
HAVING SUM(p.number_likes) > 0
ORDER BY ratio DESC
LIMIT ?;

优化点说明 #

1. 先缩范围再处理：先筛选出发帖量达标的作者，再关联他们的帖子，大幅减少后续JOIN和聚合的数据量。
2. 避免冗余数据：不再用SELECT *，只处理需要的字段，降低内存和IO开销。
3. 高效分组：按user_id（整数主键）分组，比字符串类型的user_name排序分组快得多。
4. 索引加持：利用创建的复合索引，数据库可以快速定位数据，无需全表扫描，直接从索引中提取聚合所需字段。

为什么Python代码更快？ #

Python代码只做了一次全表扫描，把符合条件的author_id和number_likes读到内存后，用字典做聚合筛选，避免了SQL中多次全表扫描和低效的子查询组合。但加上合适的索引后，SQL的执行效率会追上甚至超过Python——因为数据库的聚合逻辑是C级别的底层优化，比Python的内存处理更高效。

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