MySQL 8含`IS NOT NULL`表达式的复合索引未被使用问题排查

问题背景 #

使用MySQL 8.0.33版本，创建了包含recurrence_rule IS NOT NULL表达式与user_id的复合索引，但执行指定用户重复预约查询时，索引未被触发，执行计划显示全表扫描。

表与索引定义 #

CREATE TABLE appointments 
(
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  recurrence_rule VARCHAR(255)
);

CREATE INDEX idx1
ON appointments ((recurrence_rule IS NOT NULL), user_id);

查询语句与执行计划 #

执行查询：

explain analyze select * from appointments where user_id = 7 and recurrence_rule is not null;

执行计划结果：

-> Filter: ((appointments.user_id = 7) and (appointments.recurrence_rule is not null)) (cost=150.25 rows=135) (actual time=0.356..0.625 rows=4 loops=1)
-> Table scan on appointments (cost=150.25 rows=1500) (actual time=0.143..0.528 rows=1500 loops=1)

业务场景 #

appointments表存储百万级预约数据，其中仅1%为重复预约（recurrence_rule非空），需高效查询指定用户的重复预约，但索引未生效。

测试数据生成 #

存储过程：

DELIMITER $$

CREATE PROCEDURE GenerateData()
BEGIN
    -- 声明变量
    DECLARE i INT DEFAULT 1; -- 循环计数器
    DECLARE user_id INT;
    DECLARE recurrence_rule VARCHAR(255);
    
    -- 每25条预约为一条重复预约，每个用户分配100条预约
    WHILE i <= 1500 DO
        SET user_id = CEIL(i / 100.0); -- 计算user_id，确保向上取整
        
        -- 判断是否为重复预约
        IF i % 25 = 0 THEN
            SET recurrence_rule = CONCAT('FREQ=', ELT(FLOOR(1 + RAND() * 4), 'DAILY', 'WEEKLY', 'MONTHLY', 'YEARLY'), ';INTERVAL=', FLOOR(1 + RAND() * 10));
        ELSE
            -- 非重复预约设置recurrence_rule为NULL
            SET recurrence_rule = NULL;
        END IF;
        
        -- 插入数据到appointments表
        INSERT INTO appointments (user_id, recurrence_rule)
        VALUES (user_id, recurrence_rule);
        
        -- 递增循环计数器
        SET i = i + 1;
    END WHILE;
    
END$$

DELIMITER ;

调用存储过程生成数据：

call GenerateData;

问题原因与解决方案 #

核心原因 #

1. 测试数据量过小：当前测试仅1500条数据，MySQL优化器判定全表扫描的IO成本低于使用索引（索引需额外读取索引页再回表获取全字段），因此选择全表扫描。当数据量达到百万级时，全表扫描成本会显著上升，优化器会自动切换为使用索引。
2. 索引顺序适配性不足：当前索引以(recurrence_rule IS NOT NULL)为第一列，该表达式仅返回0/1两种值，虽业务中仅1%数据为1，但索引顺序导致优化器计算成本时，可能认为先筛选表达式再匹配user_id的效率不如调整顺序后的索引。

解决方案 #

• 增大测试数据量：将测试数据扩容至百万级，验证优化器是否自动选择索引。
• 调整索引顺序：将user_id放在复合索引的第一列，表达式列为第二列，更贴合查询中先指定用户的业务逻辑：

  CREATE INDEX idx_user_recurrence ON appointments(user_id, (recurrence_rule IS NOT NULL));
  

• 使用覆盖索引减少回表成本：若查询无需所有字段，可将必要字段加入索引的包含列（MySQL 8.0.19+支持），避免回表操作，提升索引被选中的概率：

  CREATE INDEX idx_user_recurrence_include ON appointments(user_id, (recurrence_rule IS NOT NULL)) INCLUDE (id, recurrence_rule);
  

• 强制使用索引验证：通过FORCE INDEX强制使用目标索引，验证索引本身是否有效：

  explain analyze select * from appointments FORCE INDEX(idx1) where user_id = 7 and recurrence_rule is not null;
  

内容的提问来源于stack exchange，提问作者Felix Livni