mysql - 即时计算用户年龄，优化。数据库

Question

我有下一个(奇怪的)查询

SELECT DISTINCT c.id
FROM z1 INNER JOIN c c ON (z1.id=c.id) 
INNER JOIN i ON (c.member_id=i.member_id)
WHERE DATE_FORMAT(CONCAT(i.birthyear,"-",i.birthmonth,"-",i.birthday),"%Y%m%d000000") BETWEEN '19820605000000' AND '19930604235959' AND c.id NOT IN (658887)
GROUP BY c.id

用户的生日在数据库中保存在三个不同的列中。但这里的任务是找出特定年龄范围内的用户的东西。

最糟糕的是，mysql 会计算每个选定记录的年龄并将其与条件进行比较，这并不好:(有什么办法可以让它更快吗？

这是计划

+----+-------------+-------+--------+-------------------+---------+---------+--------------------+--------+----------+-----------------------------------------------------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref                | rows   | filtered | Extra                                                     |
+----+-------------+-------+--------+-------------------+---------+---------+--------------------+--------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | z1    | index  | PRIMARY           | PRIMARY | 4       | NULL               | 176659 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,member_id | PRIMARY | 4       | z1.id          |      1 |   100.00 |                                                           |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY           | PRIMARY | 4       | c.member_id |      1 |   100.00 | Using where                                               |
+----+-------------+-------+--------+-------------------+---------+---------+--------------------+--------+----------+-----------------------------------------------------------+

Answer 1

像往常一样，正确的答案是修复您的架构。即数据应该规范化，在可行的情况下使用本地 key 并使用正确的数据类型。

看看你的帖子，至少你提供了一个 EXPLAIN 计划 - 但表结构也会有所帮助。

为什么查询中是表z1？您没有明确地使用它进行过滤，也没有在任何地方使用结果。

为什么要使用 DISTINCT 和 GROUP BY 机器人 - 您要求 DBMS 两次执行相同的工作。

为什么使用“c”作为“c”的别名？

为什么要使用 NOT IN 来排除单个值？

为什么要将日期值作为字符串进行比较？

优化器可能对解决查询的最佳方法感到困惑 - 但您没有提供任何信息来支持这一点 - 年龄规则过滤了多少比例的数据？使用 birthday/i 表来驱动查询可能会得到更好的结果:

SELECT DISTINCT c.id
FROM c 
INNER JOIN i ON (c.member_id=i.member_id)
WHERE STR_TO_DATE(
       CONCAT(i.birthyear,'-', i.birthmonth,'-',i.birthday)
       ,"%Y-%m-%d")    
BETWEEN 19820605000000 AND 19930604235959 
AND c.id <> 658887
AND i.birthyear BETWEEN 1982 AND 1993