为何MySQL 8预处理语句中带>?的MATCH AGAINST无法使用全文索引而5.7可以？

问题现象 #

执行以下SQL代码时，出现版本间的索引使用差异：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FULLTEXT name (first_name, last_name)
);

DELIMITER //
CREATE PROCEDURE populate_users()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 100 DO
        INSERT INTO users (first_name, last_name, email, password, created_at, updated_at)
        VALUES (
            CONCAT('FirstName', i),
            CONCAT('LastName', i),
            CONCAT('user', i, '@example.com'),
            'password123',
            NOW(),
            NOW()
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL populate_users();

PREPARE stmt1 FROM 'EXPLAIN SELECT *, MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) as search_score FROM users WHERE MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) > 0';
SET @a = "FirstName6";
SET @b = "FirstName6";
EXECUTE stmt1 USING @a, @b;
DEALLOCATE PREPARE stmt1;

PREPARE stmt2 FROM 'EXPLAIN SELECT *, MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) as search_score FROM users WHERE MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) > ?';
SET @c = "FirstName6";
SET @d = "FirstName6";
SET @e = 0;
EXECUTE stmt2 USING @c, @d, @e;
DEALLOCATE PREPARE stmt2;

PREPARE stmt3 FROM 'EXPLAIN SELECT *, MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) as search_score FROM users USE INDEX (name) WHERE MATCH(first_name, last_name) AGAINST(? IN BOOLEAN MODE) > ?';
SET @f = "FirstName6";
SET @g = "FirstName6";
SET @h = 0;
EXECUTE stmt3 USING @f, @g, @h;
DEALLOCATE PREPARE stmt3;

• 在MySQL 8.0中：stmt1可以正常使用name全文索引，但stmt2和stmt3（即使强制指定USE INDEX）均无法使用该索引。
• 在MySQL 5.7中：三条语句都能正常使用name全文索引。

差异原因 #

这是MySQL 8.0对预处理语句的优化逻辑调整导致的：

1. 执行计划确定时机的变化

   • MySQL 5.7的优化器会在EXECUTE阶段（传入实际参数时）确定执行计划，此时能识别出MATCH(...) > 0的过滤逻辑，从而选择全文索引。
   • MySQL 8.0的优化器改为在PREPARE阶段就确定执行计划，此时无法预知?参数的具体值。对于MATCH(...) > ?的条件，优化器无法提前判断参数是否为0，因此不会将全文索引纳入执行计划选项——即使后续EXECUTE传入参数0，已经确定的计划也不会变更。

2. 硬编码常量与参数的逻辑区别
   stmt1中的> 0是硬编码的常量，优化器在PREPARE阶段就能明确这是符合全文索引使用的过滤条件，因此可以正常选择索引。而stmt2和stmt3用参数替代常量0，触发了8.0的新逻辑限制。

3. 强制索引失效的本质
   USE INDEX无法生效，是因为8.0优化器在PREPARE阶段就判定该全文索引无法适配带参数的过滤条件，强制指定索引也无法绕过这个逻辑判断——优化器认为即使使用该索引，也无法处理参数未知的过滤需求。

内容的提问来源于stack exchange，提问作者Sebastian Mares