listmonk数据库查询重写：提升性能的高级技巧

listmonk数据库查询重写：提升性能的高级技巧

【免费下载链接】listmonkHigh performance, self-hosted, newsletter and mailing list manager with a modern dashboard. Single binary app.项目地址: https://gitcode.com/GitHub_Trending/li/listmonk

你是否遇到过发送大量邮件时系统卡顿？或者查询订阅者数据需要等待很久？本文将分享几个实用的数据库查询优化技巧，帮助你显著提升listmonk的性能表现。读完本文后，你将能够：识别低效查询、添加适当索引、重写复杂SQL语句、利用缓存机制，以及监控查询性能。

识别性能瓶颈

在优化查询之前，首先需要找到性能瓶颈。listmonk使用PostgreSQL数据库，我们可以通过分析慢查询日志来识别问题。查看项目中的数据库查询定义文件queries.sql，特别是包含复杂JOIN和子查询的语句。

例如，在queries.sql的第306-324行，有一个用于查询订阅者的复杂SQL：

-- name: query-subscribers -- raw: true -- Unprepared statement for issuring arbitrary WHERE conditions for -- searching subscribers. While the results are sliced using offset+limit, -- there's a COUNT() OVER() that still returns the total result count -- for pagination in the frontend, albeit being a field that'll repeat -- with every resultant row. SELECT subscribers.* FROM subscribers LEFT JOIN subscriber_lists ON ( -- Optional list filtering. (CASE WHEN CARDINALITY($1::INT[]) > 0 THEN true ELSE false END) AND subscriber_lists.subscriber_id = subscribers.id AND ($2 = '' OR subscriber_lists.status = $2::subscription_status) ) WHERE (CARDINALITY($1) = 0 OR subscriber_lists.list_id = ANY($1::INT[])) AND (CASE WHEN $3 != '' THEN name ~* $3 OR email ~* $3 ELSE TRUE END) AND %query% ORDER BY %order% OFFSET $4 LIMIT (CASE WHEN $5 < 1 THEN NULL ELSE $5 END);

这个查询在处理大量数据时可能会变慢，特别是当%query%部分包含复杂条件时。

优化索引策略

合理的索引是提升查询性能的关键。查看数据库模式定义文件schema.sql，可以发现listmonk已经定义了一些索引，但可能还有优化空间。

例如，在schema.sql的第29-33行，为subscribers表定义了多个索引：

DROP INDEX IF EXISTS idx_subs_email; CREATE UNIQUE INDEX idx_subs_email ON subscribers(LOWER(email)); DROP INDEX IF EXISTS idx_subs_status; CREATE INDEX idx_subs_status ON subscribers(status); DROP INDEX IF EXISTS idx_subs_id_status; CREATE INDEX idx_subs_id_status ON subscribers(id, status); DROP INDEX IF EXISTS idx_subs_created_at; CREATE INDEX idx_subs_created_at ON subscribers(created_at); DROP INDEX IF EXISTS idx_subs_updated_at; CREATE INDEX idx_subs_updated_at ON subscribers(updated_at);

如果你的查询经常根据订阅状态和创建日期过滤，可以考虑创建一个组合索引：

CREATE INDEX idx_subs_status_created_at ON subscribers(status, created_at);

重写复杂查询

复杂的子查询和JOIN操作往往是性能问题的根源。让我们以queries.sql中第306行的query-subscribers查询为例，看看如何优化。

原查询使用了LEFT JOIN和多个CASE语句，这在数据量大时可能效率低下。我们可以尝试将其重写为使用 EXISTS 子句，减少不必要的数据连接：

-- 优化后的查询示例 SELECT s.* FROM subscribers s WHERE (CARDINALITY($1) = 0 OR EXISTS ( SELECT 1 FROM subscriber_lists sl WHERE sl.subscriber_id = s.id AND sl.list_id = ANY($1::INT[]) AND ($2 = '' OR sl.status = $2::subscription_status) )) AND (CASE WHEN $3 != '' THEN s.name ~* $3 OR s.email ~* $3 ELSE TRUE END) AND %query% ORDER BY %order% OFFSET $4 LIMIT (CASE WHEN $5 < 1 THEN NULL ELSE $5 END);

这种方式可以避免不必要的行连接，特别是当subscriber_lists表很大时。

利用缓存机制

listmonk已经实现了一些缓存机制来提高性能。在internal/core/subscribers.go的第548-558行，可以看到系统尝试从缓存中获取订阅者数量：

// 如果没有查询条件，尝试从缓存获取 if queryExp == "" { _ = c.refreshCache(matListSubStats, false) total := 0 if err := c.q.QuerySubscribersCountAll.Get(&total, pq.Array(listIDs), subStatus); err != nil { return 0, echo.NewHTTPError(http.StatusInternalServerError, c.i18n.Ts("globals.messages.errorFetching", "name", "{globals.terms.subscribers}", "error", pqErrMsg(err))) } return total, nil }

你可以通过修改配置文件启用更多缓存功能，或调整缓存刷新频率来平衡性能和数据实时性。

监控查询性能

优化完成后，需要持续监控查询性能。你可以使用PostgreSQL的内置工具如pg_stat_statements来跟踪慢查询。以下是一个示例查询，用于找出执行时间最长的查询：

SELECT queryid, query, total_time, calls FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

定期检查这些指标，可以帮助你及时发现新的性能问题。

总结

通过识别性能瓶颈、优化索引、重写复杂查询、利用缓存和持续监控，你可以显著提升listmonk的数据库性能。这些技巧不仅适用于listmonk，也可应用于其他PostgreSQL数据库项目。

主要优化点回顾：

1. 分析queries.sql中的复杂查询
2. 优化schema.sql中的索引策略
3. 重写子查询和JOIN操作
4. 利用internal/core/subscribers.go中的缓存机制
5. 定期监控查询性能

通过这些方法，即使在处理大量订阅者和邮件 campaign 时，你的 listmonk 系统也能保持流畅运行。

【免费下载链接】listmonkHigh performance, self-hosted, newsletter and mailing list manager with a modern dashboard. Single binary app.项目地址: https://gitcode.com/GitHub_Trending/li/listmonk