MySQL生日查询优化：应创建哪些列的索引以提升查询效率？

表结构与查询语句 #

表cl_clientes结构 #

id int AI PK,
nome varchar(100),  -- 姓名
email varchar(200),
datanasc datetime,  -- 出生日期
data_envio_email datetime  -- 邮件发送日期

目标查询语句 #

select
    A.id,
    A.nome,
    A.email,
    A.datanasc,
    A.data_envio_email
from cl_clientes A
where
    (A.data_envio_email is null or year(A.data_envio_email) <= year(curdate()))
    and A.email is not null and A.email <> ''
    and (month(A.datanasc) = month(curdate()) and day(A.datanasc) = day(curdate()))

问题分析：单独给datanasc建索引无效的原因 #

查询中对datanasc使用了month()和day()函数，这会导致索引失效——MySQL无法直接利用datanasc的索引快速定位符合条件的行，只能执行全表扫描或索引全扫描，无法发挥索引的过滤效率。

索引优化方案 #

方案1：新增虚拟列并建立复合索引 #

通过虚拟列提前计算出生日期的月日组合，避免查询时使用函数：

1. 添加存储型虚拟列：

ALTER TABLE cl_clientes ADD COLUMN mes_dia VARCHAR(5) GENERATED ALWAYS AS (CONCAT(LPAD(MONTH(datanasc), 2, '0'), '-', LPAD(DAY(datanasc), 2, '0'))) STORED;

2. 创建覆盖过滤条件的复合索引：

CREATE INDEX idx_cl_clientes_mesdia_email_envio ON cl_clientes (mes_dia, email, data_envio_email);

3. 修改查询语句，直接匹配虚拟列：

select
    A.id,
    A.nome,
    A.email,
    A.datanasc,
    A.data_envio_email
from cl_clientes A
where
    mes_dia = CONCAT(LPAD(MONTH(curdate()), 2, '0'), '-', LPAD(DAY(curdate()), 2, '0'))
    and A.email is not null and A.email <> ''
    and (A.data_envio_email is null or year(A.data_envio_email) <= year(curdate()))

该方案让MySQL通过mes_dia快速定位当天生日的用户，同时索引包含email和data_envio_email过滤列，避免回表查询，大幅提升效率。

方案2：改写查询条件为范围扫描（无需新增列） #

将生日条件改写为不使用函数的范围查询，让datanasc索引可以被利用：

select
    A.id,
    A.nome,
    A.email,
    A.datanasc,
    A.data_envio_email
from cl_clientes A
where
    (A.data_envio_email is null or year(A.data_envio_email) <= year(curdate()))
    and A.email is not null and A.email <> ''
    and datanasc >= DATE_FORMAT(curdate(), '%Y-01-01') + INTERVAL (MONTH(curdate())-1) MONTH + INTERVAL (DAY(curdate())-1) DAY
    and datanasc < DATE_FORMAT(curdate(), '%Y-01-01') + INTERVAL (MONTH(curdate())-1) MONTH + INTERVAL DAY(curdate()) DAY

同时创建复合索引：

CREATE INDEX idx_cl_clientes_datanasc_email_envio ON cl_clientes (datanasc, email, data_envio_email);

注意：该方案在非闰年无法匹配2月29日出生的用户，需额外处理这类特殊情况。

方案3：优化data_envio_email过滤逻辑 #

如果data_envio_email的条件能过滤掉大量数据，可以考虑将year(data_envio_email)的判断改为范围查询（比如data_envio_email < DATE_FORMAT(curdate(), '%Y-01-01') + INTERVAL 1 YEAR），进一步提升索引的利用效率，但核心仍需保证生日条件能被索引覆盖。

内容的提问来源于stack exchange，提问作者Luiz Alves