优化SQL查询：为每种type返回最高得分结果（搜索自动补全）

问题背景 #

现有用于搜索栏自动补全的SQL查询通过UNION拼接多个子查询实现，在80万条数据的property_place_metas表中运行速度逐渐变慢。需求是不使用UNION，通过单查询为每种指定type返回对应数量的最高得分结果（如address取4条、zip_code取15条等），且返回数据结构保持不变。

原UNION查询 #

(SELECT id,type,place_id,name,full_name,score FROM property_place_metas WHERE type = 'address' AND name RLIKE '^".$_GET['keyword']."' AND status = 'active' ORDER BY score DESC LIMIT 4)
UNION
(SELECT id,type,place_id,name,full_name,score FROM property_place_metas WHERE type = 'mls_address' AND name RLIKE '^".$_GET['keyword']."' AND status = 'active' ORDER BY score DESC LIMIT 4)
UNION
(SELECT id,type,place_id,name,full_name,score FROM property_place_metas WHERE type = 'state' AND name RLIKE '^".$_GET['keyword']."' AND status = 'active' ORDER BY score DESC LIMIT 5)
UNION
(SELECT id,type,place_id,name,full_name,score FROM property_place_metas WHERE type = 'county' AND name RLIKE '^".$_GET['keyword']."' AND status = 'active' ORDER BY score DESC LIMIT 5)
UNION
(SELECT id,type,place_id,name,full_name,score FROM property_place_metas WHERE type = 'city' AND name RLIKE '^".$_GET['keyword']."' AND status = 'active' ORDER BY score DESC LIMIT 5)
UNION
(SELECT id,type,place_id,name,full_name,score FROM property_place_metas WHERE type = 'zip_code' AND name RLIKE '^".$_GET['keyword']."' AND status = 'active' ORDER BY score DESC LIMIT 15)
UNION
(SELECT id,type,place_id,name,full_name,score FROM property_place_metas WHERE type = 'high_school' AND name RLIKE '^".$_GET['keyword']."' AND status = 'active' ORDER BY score DESC LIMIT 5)
UNION
(SELECT id,type,place_id,name,full_name,score FROM property_place_metas WHERE type = 'middle_junior_school' AND name RLIKE '^".$_GET['keyword']."' AND status = 'active' ORDER BY score DESC LIMIT 5)

表结构 #

CREATE TABLE `property_place_metas` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('active','suspended') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` enum('address','mls_address','state','county','city','zip_code','high_school','middle_junior_school') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `place_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `place_geo_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `full_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `score` int NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `property_place_metas_status_index` (`status`),
  KEY `property_place_metas_type_index` (`type`),
  KEY `property_place_metas_place_id_index` (`place_id`),
  KEY `property_place_metas_place_geo_id_index` (`place_geo_id`),
  KEY `property_place_metas_name_index` (`name`),
  KEY `property_place_metas_full_name_index` (`full_name`),
  KEY `property_place_metas_score_index` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=1406600 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

解决方案：使用窗口函数实现单查询 #

利用ROW_NUMBER()窗口函数按type分组，组内按score降序生成行号，再结合预定义的各类型返回数量筛选结果。

优化后的SQL #

SELECT id, type, place_id, name, full_name, score
FROM (
    SELECT 
        ppm.id, ppm.type, ppm.place_id, ppm.name, ppm.full_name, ppm.score,
        -- 按类型分组，得分降序生成行号
        ROW_NUMBER() OVER (PARTITION BY ppm.type ORDER BY ppm.score DESC) AS row_num,
        tl.limit_count
    FROM property_place_metas ppm
    -- 关联预定义各类型返回数量的临时数据集
    JOIN (
        SELECT 'address' AS type, 4 AS limit_count UNION ALL
        SELECT 'mls_address' AS type, 4 UNION ALL
        SELECT 'state' AS type, 5 UNION ALL
        SELECT 'county' AS type, 5 UNION ALL
        SELECT 'city' AS type, 5 UNION ALL
        SELECT 'zip_code' AS type, 15 UNION ALL
        SELECT 'high_school' AS type, 5 UNION ALL
        SELECT 'middle_junior_school' AS type, 5
    ) tl ON ppm.type = tl.type
    WHERE ppm.status = 'active' 
      -- 使用CONCAT和QUOTE避免注入风险，替换原字符串拼接方式
      AND ppm.name RLIKE CONCAT('^', QUOTE('.$_GET['keyword'].'))
) t
-- 筛选各类型内不超过指定数量的最高得分记录
WHERE t.row_num <= t.limit_count
-- 可根据需求调整最终排序规则
ORDER BY t.type, t.score DESC;

索引优化建议 #

原表的单字段索引无法高效支撑该查询，建议创建复合覆盖索引，避免回表查询，大幅提升性能：

CREATE INDEX idx_status_type_name_score ON property_place_metas (status, type, name, score DESC);

这个索引包含了查询中用到的status、type、name过滤条件，以及排序用的score，可以让数据库直接通过索引获取所需数据，无需访问表本身。

内容的提问来源于stack exchange，提问作者Ahmed Wagih Refaey