如何在分组中查询最短字符串？SQL分组取最短字段优化方案

问题背景 #

表结构 #

CREATE TABLE `character_unique` (
        `id` int(11) NOT NULL,
        `name` varchar(256) NOT NULL,
        `category` varchar(64) DEFAULT NULL,
        `name_without_stop_words` varchar(320) DEFAULT NULL,
        `name_first_word_with_exception` varchar(320) DEFAULT NULL,
        `master` varchar(320) DEFAULT NULL,
        `nb_letter` int(11) DEFAULT NULL,
        `count` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `character_unique_count_index` (`count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

需求 #

按name_first_word_with_exception和category字段分组，获取每组内name_without_stop_words字段中字符串长度最短的值。

异常场景 #

分类为Harry Potter且name_first_word_with_exception为Patronus的分组中，name_without_stop_words的取值如下：

Patronus d'Harry Potter - Translucide
Patronus Ron Weasley - Translucide
Patronus Hermione Granger - Translucide
Patronus Remus Lupin
Patronus Albus Dumbledore
Patronus Severus
Patronus Minerva McGonagall
Patronus Remus Lupin
Patronus Severus Snape

原有分组逻辑得到的最短名称是Patronus Albus Dumbledore，但实际最短的是Patronus Severus，说明原有逻辑存在错误。

原有慢查询方案 #

使用子查询能得到正确结果，但查询速度极慢：

SELECT *, 
    (SELECT name_without_stop_words 
     FROM character_unique 
     WHERE category LIKE CONCAT('%', 'Harry Potter', '%') 
        AND name_first_word_with_exception = character_name 
     ORDER BY nb_letter LIMIT 1) as name_without_stop_words 
FROM (
    SELECT id as unque_character_id,
       null as id_character_added_manually,
       category,
       name_first_word_with_exception as character_name,
       master,
       count
    FROM character_unique 
    WHERE category LIKE CONCAT('%', 'Harry Potter', '%')
    GROUP BY character_name, category
) as t1;

高效解决方案 #

方案1：窗口函数（推荐，MySQL 8.0+支持） #

利用ROW_NUMBER()窗口函数按分组字段分区，再按nb_letter排序，取每组第一条数据，性能远优于关联子查询：

SELECT 
    id as unque_character_id,
    NULL as id_character_added_manually,
    category,
    name_first_word_with_exception as character_name,
    master,
    count,
    name_without_stop_words
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY name_first_word_with_exception, category 
            ORDER BY nb_letter ASC
        ) AS rn
    FROM character_unique
    WHERE category LIKE '%Harry Potter%'
) t
WHERE rn = 1;

方案2：JOIN + 聚合查询（兼容低版本MySQL） #

先通过聚合查询获取每组的最小nb_letter，再关联原表匹配对应字段：

SELECT 
    cu.id as unque_character_id,
    NULL as id_character_added_manually,
    cu.category,
    cu.name_first_word_with_exception as character_name,
    cu.master,
    cu.count,
    cu.name_without_stop_words
FROM character_unique cu
JOIN (
    SELECT 
        name_first_word_with_exception,
        category,
        MIN(nb_letter) AS min_nb_letter
    FROM character_unique
    WHERE category LIKE '%Harry Potter%'
    GROUP BY name_first_word_with_exception, category
) t ON cu.name_first_word_with_exception = t.name_first_word_with_exception 
    AND cu.category = t.category 
    AND cu.nb_letter = t.min_nb_letter
WHERE cu.category LIKE '%Harry Potter%';

性能优化建议 #

• 创建联合索引：CREATE INDEX idx_category_firstword_nbletter ON character_unique(category, name_first_word_with_exception, nb_letter);，可大幅提升分组、排序的查询效率。
• 避免左模糊匹配LIKE '%xxx%'，若业务允许，改为前缀匹配LIKE 'xxx%'，或使用全文索引优化模糊查询。

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