mysql - 按年份范围分组

Question

我需要从出生日期开始按年份范围分组。这是我到目前为止所做的。如果您运行包含 500000 条记录的存储过程，然后运行我编写的查询，您会发现它大约需要 25 秒。我该如何改进它？

create table people(
id int not null auto_increment primary key,
`dob` date
);

delimiter //
drop procedure if exists date_random //
create procedure date_random(in low date,in upp date,in number int)
begin
declare i int default 0;
while i < number do
    begin
    insert into people (`dob`)  values ( low + interval rand()* datediff(upp,low) day  );
    set i = i + 1;
    end;
end while;
end //
delimiter ;

call date_random('1910-01-01',curdate(),500000);


delimiter // 
create function `age`(dob date) returns int(11)
no sql
begin
return (year(curdate())-year(dob))-(right(curdate(),5)< right(dob,5) );
end //

delimiter ;


explain select sql_no_cache
concat_ws('-',min(age(dob)),max(age(dob))) as years,
count(*) as total
from people
group by if(age(dob)=0,1,ceil(age(dob)/5))

这是解释的输出

+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | people | index | NULL          | ip   | 4       | NULL | 500000 | Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

Answer 1

您的“年龄”功能可能会更有效率。而不是强制 mysql 将日期转换为字符串，做子字符串，比较它们，然后转换为数字以进行最后的减法，(year(now()) - year(dob)) - (dayofyear(now()) < dayofyear(dob)) 怎么样？ - 将其全部保留为数字并消除至少一层转换。

同样，由于它使用 native 日期/时间函数，它增加了 MySQL 可以在 dob 上使用索引的机会。柱子。您当前的方法无法处理索引，因为您在查询时动态地从日期字段派生文本值。