mysql 查询当天过生日sql

             线上发现有这么一个慢sql:查询当天生日的用户，由于用户量达到了百万级，原来的慢sql如下：

            

select t1.id as id, t1.user_name, t1.real_name, t1.user_id as userid,
t1.xinge_token as xingetoken , t1.sex as sex, t1.register_time as registertime
from user_detail t1
where t1.source_app = 'ORA' and t1.is_enable = 1

and date_format(t1.birthday, '%m-%d') =
date_format('1987-09-03 00:00:00', '%m-%d') and t1.is_enable = 1

  

 

   使用执行计划，发现竟然全表扫描了：原因是使用了date_format库

    

 大概查询了5S多

 

 

 

 

   于是思考如何去优化这个慢sql，百度知乎，发现有类似的函数：

    DAYOFYEAR、WEEKOFYEAR，具体就是查询一年中的第多少天以及第多少周的。但是发现一个奇怪的现象是： 如果查询只查询生日一个字段，会很快，但是如果带上比如id、name之类的，

立马就慢了一个数量级。

 

 

 

 

select t1.birthday
from user_detail t1
where t1.source_app = 'ORA' and t1.is_enable = 1
and DAYOFYEAR(t1.birthday )=DAYOFYEAR("2022-09-03");

 

 

 

 

   

     

 

 

 

 

     0.4秒多，如果带上id的条件：

  

select t1.birthday,t1.user_id
from user_detail t1
where t1.source_app = 'ORA' and t1.is_enable = 1
and DAYOFYEAR(t1.birthday )=DAYOFYEAR("1930-09-03");

 

 

 

 

  于是优化就改为了，先根据日期查询当天的具体的生日，再用这个为条件，去sql过滤查询。这样就快多了

select t1.id as id, t1.user_name, t1.real_name, t1.user_id as userid,
t1.xinge_token as xingetoken , t1.sex as sex, t1.register_time as registertime
from user_detail t1
where t1.source_app = 'ORA' and t1.is_enable = 1
and t1.birthday in ('1903-09-03 00:00:00','1911-09-03 00:00:00','1919-09-03 00:00:00','1945-09-03 00:00:00','1958-09-03 00:00:00','1961-09-03 00:00:00','1962-09-03 00:00:00','1964-09-02 00:00:00','1965-09-03 00:00:00','1967-09-03 00:00:00','1967-09-03 00:00:00','1968-09-02 00:00:00','1968-09-02 00:00:00','1968-09-02 00:00:00','1968-09-02 00:00:00','1969-09-03 00:00:00','1969-09-03 00:00:00','1969-09-03 00:00:00','1969-09-03 00:00:00','1970-09-03 00:00:00','1970-09-03 00:00:00','1971-09-03 00:00:00','1972-09-02 00:00:00','1972-09-02 00:00:00','1972-09-02 00:00:00','1972-09-02 00:00:00','1973-09-03 00:00:00','1975-09-03 00:00:00','1975-09-03 00:00:00','1976-09-02 00:00:00','1977-09-03 00:00:00','1977-09-03 00:00:00','1977-09-03 00:00:00','1977-09-03 00:00:00','1977-09-03 00:00:00','1978-09-03 00:00:00','1978-09-03 00:00:00','1979-09-03 00:00:00','1979-09-03 00:00:00','1979-09-03 00:00:00','1979-09-03 00:00:00','1979-09-03 00:00:00','1979-09-03 00:00:00','1979-09-03 00:00:00','1979-09-03 00:00:00','1979-09-03 00:00:00','1980-09-02 00:00:00','1980-09-02 00:00:00','1980-09-02 00:00:00','1980-09-02 00:00:00','1980-09-02 00:00:00','2022-09-03 14:32:49');

 

 

 

 

  具体为何查询生日很快，但是加一个字段为何这么快呢？