MySQL一对多关系下指定村庄批量随机抽20条调研编号的优化查询需求

背景与需求 #

• 表结构：
  • village_info（表A）：字段village_id、is_active
  • survey_info（表B）：字段survey_id、survey_numbers、village_id、is_active
  • 关联关系：一对多，单个村庄对应多条survey_numbers
• 核心需求：针对指定村庄列表village_list = [village_id1, village_id2...]，为每个村庄随机抽取20条is_active=1的survey_numbers
• 现状：survey_info表数据量超2000万，窗口函数、GROUP BY等常规方法查询耗时极长，性能极差

优化方案与实现 #

方案1：索引加持的批量单村抽样（推荐）

前提：先给survey_info创建复合索引，让数据库直接定位目标数据，避免全表扫描与回表：

CREATE INDEX idx_village_active_survey ON survey_info(village_id, is_active, survey_numbers);

SQL实现：通过UNION ALL拆解全局查询为多个单村小查询，每个子查询仅针对单个村庄的有效数据集做随机排序抽样：

-- 针对village_id1抽样
SELECT village_id, survey_numbers
FROM (
    SELECT village_id, survey_numbers, RAND() AS rand_score
    FROM survey_info
    WHERE village_id = 'village_id1' AND is_active = 1
    ORDER BY rand_score
    LIMIT 20
) t1
UNION ALL
-- 针对village_id2抽样
SELECT village_id, survey_numbers
FROM (
    SELECT village_id, survey_numbers, RAND() AS rand_score
    FROM survey_info
    WHERE village_id = 'village_id2' AND is_active = 1
    ORDER BY rand_score
    LIMIT 20
) t2
-- 按需继续添加其他村庄的子查询

若村庄数量较多，可通过程序动态生成UNION ALL语句，无需手动编写。该方案将大查询拆分为多个低成本小查询，性能提升显著。

方案2：随机偏移量抽样（适用于数据量充足的场景）

如果每个村庄的有效survey_numbers数量远大于20，可使用随机偏移量避免排序操作，进一步降低性能消耗：

SELECT village_id, survey_numbers
FROM survey_info
WHERE village_id = 'village_id1' AND is_active = 1
LIMIT FLOOR(RAND() * (
    SELECT COUNT(*) 
    FROM survey_info 
    WHERE village_id = 'village_id1' AND is_active = 1
)), 20;

注意：需提前判断村庄有效数据量是否≥20，否则可能返回不足20条结果；若数据实时更新频繁，偏移量可能存在微小误差，但性能比排序抽样更优。

方案3：分区表优化（可选）

若survey_info已按village_id做分区（或可改成分区表），上述方案的性能会进一步提升——分区表会直接定位到目标村庄所在的分区，避免扫描其他分区的数据。若无法改分区，可在查询中添加FORCE INDEX(idx_village_active_survey)强制使用指定索引。

关键优化原则 #

1. 缩小查询范围优先：先筛选指定村庄+is_active=1的数据，再在小数据集内抽样，避免全局扫描。
2. 拒绝全局窗口函数：ROW_NUMBER() OVER(PARTITION BY village_id)这类全局窗口函数需要扫描全表并排序，2000万数据量下性能灾难。
3. 索引是核心：必须创建覆盖查询字段的复合索引，让数据库无需回表即可获取所需数据。

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