mysql - 在执行 COUNT(*) GROUP BY 字符串时如何实现绝对最快的方法

Question

我将拥有一个相对较大的不断增长的数据集，其中包含 2.5 亿多行，并且具有非常基本的表结构:

id(无符号整数主值)、年龄(tinyint)、电子邮件(某种类型的字符串)

我将在其中根据按电子邮件分组的年龄执行count(*)查询。例如:

select count(*) as count from table 
where age = 38
group by email
order by count
limit 100

email 字段最多可达 60 个字符。

我不需要非常精确的结果，因此只要查询运行得尽可能快，我就可以接受近似结果。因此，我愿意接受聪明的技巧，甚至可能是有趣的方法。

除了为 email 选择正确的数据类型以及将 age 和 email 一起索引之外，还有哪些其他技巧可以提供帮助？

例如，如果电子邮件大小平均大于 32 个字符，我会存储 md5 哈希值。此外，我可以从 email 中删除 @ 和 . 因为我不关心一些电子邮件最终会相互冲突( abc@xyz.com 和 ab@cxyz.com 都是 abcxyzcom，这样就可以了)。另外，我认为压缩电子邮件字段可能是一种选择。

Answer 1

您的表似乎设计得很糟糕(非规范化的方式会损害性能而不是帮助性能)，并且应该重构为两个或更多表。虽然非规范化可能会使某些事情变得更容易，但它将使您需要每两分钟刷新一次的查询变得非常昂贵 - 无论您如何执行。

如果您确实有充分的理由保持表非规范化(并且该原因不仅仅是“这会使插入变得更加困难”)，那么您可能仍然需要一个仅包含唯一电子邮件的伪规范化表地址。在那里，您可以为每个电子邮件地址保留一个数字 ID 和返回原始表的外键(您试图避免这种情况)，或者让电子邮件地址本身作为主键以及您插入或更新的列反射(reflect)该电子邮件地址的计数。每当您插入到原始表中时，您也会插入(使用 ON DUPLICATE KEY UPDATE 子句)到该电子邮件跟踪表中。

我敢打赌，标准化你的原始表比追求这个更好，但这是一个选择。您的问题似乎并不是电子邮件地址非规范化，而是用户帐户信息非规范化 - 您真的允许不同的用户拥有相同的电子邮件吗？如果您尝试报告特定年龄的用户执行特定操作的数量，则应该使用比电子邮件地址更好的 key - 例如数字用户 ID，或者用于跟踪操作计数的表格特定用户。