mysql - SQL查询中年龄计算的性能

Question

我正在构建一个能够处理许多不同搜索条件的动态 MySQL 用户搜索查询。我考虑编写一个存储过程，但最终在客户端构建查询(PHP 中的准备语句)。其中一个标准是能够搜索用户的年龄，即 X 到 Y 岁之间。我想知道如何尽可能有效地做到这一点。最终查询将相当复杂并且有多个连接并且将来可能会在几百万行上运行，所以我需要尽可能地优化它。我将用户的出生日期存储在具有 YYYY-MM-DD 格式的索引 DATE 列中。我有以下用于计算用户年龄的用户定义函数 (UDF):

RETURN (DATE_FORMAT(current_time, '%Y') - DATE_FORMAT(date_of_birth, '%Y') - (DATE_FORMAT(current_time, '00-%m-%d') < DATE_FORMAT(date_of_birth, '00-%m-%d')));

计算的细节并不重要；我更关心它是如何使用的。我担心的一个问题是，在我的 WHERE 子句中使用此 UDF 会显着降低查询速度，因为它需要在每一行上运行，即使我使 UDF 具有确定性。我不能保证在检查年龄之前会有其他标准来缩小匹配行的范围。我不能只根据日期核对出生日期，因为那是不准确的。我在考虑是否将上述计算从 UDF 中拉出并将其直接嵌入到查询的 WHERE 子句中是否会产生显着差异(我认为是的)。不利的一面是 WHERE 子句因这样的计算而变得更加复杂(或者实际上是两个，除非有一种方法可以重用结果)。但我想没有办法避免这些计算。在 WHERE 子句中执行此计算是提高性能的方法，还是有更好的方法？

理论上，我想我什至可以在 user 表中添加一个 age 列，并计算用户注册和每晚运行计划作业/cronjob 的年龄为今天生日的用户更新年龄(如果我可以有效地选择)。这肯定会加快我的搜索查询速度，但会引入冗余数据。因此，如果无法在搜索查询本身内有效地完成计算，我真的只想这样做。

因此，总结一下:我需要搜索某个年龄段(例如 25 到 30 岁)内的用户。我应该在 WHERE 子句中计算年龄，还是会因为必须在每一行上计算而变得非常慢？这是我必须做出的牺牲，还是我有更好的选择？

非常感谢任何帮助。

Answer 1

如果你想根据当前日期进行准确的年龄计算，那么你应该尝试类似的方法:

where date_of_birth between date(now()) - interval 30 years and date(now()) - interval 25 year

在这种情况下，您不对date_of_birth 进行任何转换，因此可以使用索引进行查询。

另外，你不应该使用像这样的表达式:

DATE_FORMAT(current_time, '%Y') - DATE_FORMAT(date_of_birth, '%Y')

DATE_FORMAT() 将参数转换为字符串。你想要一个数字，所以只需使用:

year(now()) - year(date_of_birth)

它将从日期到字符串的转换保存到 int，然后直接转到 int。

编辑:

要处理“25”真正表示“最多 26”的情况，请通过显式比较实现逻辑:

where date_of_birth >= date(now()) - interval 30 years and
      date_of_birth < date(now()) - interval 26 year