MySQL 8.0.34全文搜索相关性排序异常问题咨询

问题背景 #

环境：MySQL 8.0.34
执行以下全文搜索查询时，结果未按预期的相关性排序，且相同数据（如第31行和第41行的记录）未在排序序列中连续展示：

SELECT
    `platform`.`sample_search`.`name`,
        `platform`.`sample_search`.`search` 
FROM
    `platform`.`sample_search`
WHERE
    1=1
    AND MATCH (`platform`.`sample_search`.`search`) AGAINST ('+steve*' IN BOOLEAN MODE)
ORDER BY
    MATCH (`platform`.`sample_search`.`search`) AGAINST ('+steve*' IN BOOLEAN MODE) DESC
LIMIT 50;

对应的建表及插入数据脚本：

建表脚本 #

CREATE TABLE sample_search (
    name varchar(191) CHARACTER
    SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    search text CHARACTER
    SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    FULLTEXT KEY contacts_search_idx (search)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

插入数据脚本 #

INSERT INTO sample_search (name,search) VALUES
    ('Steve Stevenson','steve st_ stevenson'),
    ('Steve Tussing','steve st_ tussing tu_'),
    ('Steve Gousing','steve st_ gousing go_'),
    ('Stephani Kobayashi Stevenson','stephani st_ kobayashi ko_ stevenson'),
    ('STEVEi LUCCHESI','stevei st_ lucchesi lu_'),
    ('Dost 2','dost do_ steve st_'),
    ('Steve Gous','steve st_ gous go_'),
    ('Dost 3','dost do_ steve st_ gousing go_'),
    ('steve bik rye','steve st_ bik bi_ rye ry_'),
    ('HOPPENFELD STEVE','hoppenfeld ho_ steve st_');

INSERT INTO sample_search (name,search) VALUES
    ('MASHING Steveston','mashing ma_ steveston st_'),
    ('bik steve rye','bik bi_ steve st_ rye ry_'),
    ('gousing steve','gousing go_ steve st_'),
    ('steve gouslo rye','steve st_ gouslo go_ rye ry_'),
    ('steve rye gouslo','steve st_ rye ry_ gouslo go_'),
    ('gouslo steve rye','gouslo go_ steve st_ rye ry_'),
    ('gouslo rye steve','gouslo go_ rye ry_ steve st_'),
    ('gouslo rye steve','gouslo go_ rye ry_ steve st_'),
    ('steve gousing rye','steve st_ gousing go_ rye ry_'),
    ('steve rye gousing','steve st_ rye ry_ gousing go_');

INSERT INTO sample_search (name,search) VALUES
    ('rye steve gousing','rye ry_ steve st_ gousing go_'),
    ('stevel gousingy','stevel st_ gousingy go_'),
    ('gousing steve rye','gousing go_ steve st_ rye ry_'),
    ('gousing rye steve','gousing go_ rye ry_ steve st_'),
    ('bik rye steve','bik bi_ rye ry_ steve st_ qa qa_'),
    ('co stevel','co co_ stevel st_ qa qa_'),
    ('cop rye stevel','cop co_ rye ry_ stevel st_ qa qa_'),
    ('Dost','dost do_ steve7_at_packagex_d_xyz st_ steve7 packagex pa_ at_packagex_d_xyz a undefined_at_packagex_d_xyz un packagex_d_xyz qa qa_'),
    ('Steve Ali','steve st_ ali al_ jonathan jo_'),
    ('Stevel','stevel st_ jonathan jo_');

INSERT INTO sample_search (name,search) VALUES 
    ('steve','steve st_ jonathan jo_ ali al_'),
    ('stevel gousingy','stevel st_ gousingy go_'),
    ('steve rye gouslo','steve st_ rye ry_ gouslo go_'),
    ('gouslo steve rye','gouslo go_ steve st_ rye ry_'),
    ('gouslo rye steve','gouslo go_ rye ry_ steve st_'),
    ('steve gousing rye','steve st_ gousing go_ rye ry_'),
    ('steve rye gousing','steve st_ rye ry_ gousing go_'),
    ('rye steve gousing','rye ry_ steve st_ gousing go_'),
    ('gousing steve rye','gousing go_ steve st_ rye ry_'),
    ('gousing rye steve','gousing go_ rye ry_ steve st_');

INSERT INTO sample_search (name, search) VALUES
    ('steve', 'steve st_ jonathan jo_ ali al_');

问题原因 #

1. 布尔模式得分区分度低：MySQL布尔全文搜索模式下，使用+steve*前缀匹配时，大量匹配记录的相关性得分相同（仅返回0或1，或得分差异极小），导致排序后相同得分的记录顺序随机。
2. 无主键导致排序不稳定：目标表未定义主键，相同得分的记录无法按固定顺序排列，因此相同数据无法连续展示。

解决方案 #

1. 添加主键并稳定排序顺序 #

先给表添加自增主键，确保相同得分的记录有固定排序依据：

ALTER TABLE sample_search ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST;

修改查询语句，在相关性排序后追加主键排序：

SELECT
    `platform`.`sample_search`.`name`,
    `platform`.`sample_search`.`search` 
FROM
    `platform`.`sample_search`
WHERE
    MATCH (`platform`.`sample_search`.`search`) AGAINST ('+steve*' IN BOOLEAN MODE)
ORDER BY
    -- 优先按原生相关性得分排序
    MATCH (`platform`.`sample_search`.`search`) AGAINST ('+steve*' IN BOOLEAN MODE) DESC,
    -- 相同得分时按主键排序，保证顺序稳定、相同数据连续
    id ASC
LIMIT 50;

2. 优化相关性得分区分度 #

如果需要更精细的相关性排序，可以自定义得分逻辑，结合匹配词的出现频率、字段长度等维度：

SELECT
    `name`,
    `search`,
    -- 原生布尔模式相关性得分
    MATCH(`search`) AGAINST ('+steve*' IN BOOLEAN MODE) AS relevance,
    -- 自定义得分：计算"steve"出现频率（归一化后），增强区分度
    ROUND((LENGTH(`search`) - LENGTH(REPLACE(`search`, 'steve', ''))) / LENGTH('steve') / LENGTH(`search`), 4) AS custom_relevance
FROM
    `platform`.`sample_search`
WHERE
    MATCH(`search`) AGAINST ('+steve*' IN BOOLEAN MODE)
ORDER BY
    relevance DESC,
    -- 优先按自定义得分排序，再按主键稳定顺序
    custom_relevance DESC,
    id ASC
LIMIT 50;

3. 验证前缀匹配配置 #

确保MySQL全文索引支持前缀匹配，检查相关配置参数：

SHOW VARIABLES LIKE 'ft_min_word_len';
SHOW VARIABLES LIKE 'ft_max_word_len';

若steve的长度（5）小于ft_min_word_len，需修改my.cnf（或my.ini）调整参数，重启MySQL后重建全文索引：

ALTER TABLE sample_search DROP INDEX contacts_search_idx;
ALTER TABLE sample_search ADD FULLTEXT KEY contacts_search_idx (search);

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