mysql - 用于计算每个邮箱未读电子邮件数量的数据库架构

Question

我正在构建一个电子邮件网络应用程序，但在想出一个能够快速计算每个文件夹未读电子邮件数量的架构时遇到了困难。

我目前的架构如下所示:

CREATE TABLE emails(id INT, user_id INT, folder INT, 
    read_on TIMESTAMP, raw_email_filename VARCHAR(128));

要获取每个文件夹的未读计数，我可以运行如下查询:

SELECT count(*) FROM email WHERE user_id =? AND folder IN (?..) AND read_on IS NULL;

但是，鉴于我的索引是(user_id，文件夹)，这必须遍历每个文件夹上的所有电子邮件才能获取计数。

我正在考虑添加一个索引(user_id,folder, read_on)，但是当我只对感兴趣时，read_on的条目太多了read_on 的 code>NULL 值。

有更好的方法吗？也许通过非规范化？在 Redis 中进行计数或者其他什么？

Answer 1

我会考虑

INDEX(user_id, read_on, folder)

构建最佳索引的准则是首先列出用=测试的列(IS NULL也算这样)，然后INs(文件夹)，然后是一个范围(您没有任何范围)。

不必担心 read_on IS NOT NULL 条目的索引条目被浪费；它们会使磁盘变得困惑，但不会影响性能。

另一方面，当您更改 read_on(通过 UPDATE？)时，会受到轻微影响，因为我建议的 INDEX 中的一个条目> 需要从索引 BTree 中的一个位置移动到另一个位置。同样，这可能不值得担心。

您不想使用GROUP BY吗？

SELECT folder, COUNT(*)
    FROM emails
    WHERE user_id = ?
      AND folder IN (...)
      AND read_on IS NULL
    GROUP BY folder;

执行解释选择...。对于我的 INDEX，EXPLAIN 可能会说 Using index，表示“覆盖”索引。这就是所有所需的列都在索引中的地方。

有关索引创建的更多信息:http://mysql.rjweb.org/doc.php/index_cookbook_mysql