SQL查询生成指定HashMap结构数据及高效获取方案咨询

实现逻辑 #

第一步：数据库查询 #

优先使用数据库自带的分组聚合能力直接生成每个城市对应的用户名集合，避免全量拉取原始数据到应用层处理：

• MySQL 写法：

SELECT city, GROUP_CONCAT(username SEPARATOR ',') as user_list 
FROM users 
WHERE city IS NOT NULL 
GROUP BY city;

• PostgreSQL 写法：

SELECT city, ARRAY_AGG(username) as user_list 
FROM users 
WHERE city IS NOT NULL 
GROUP BY city;

第二步：组装目标结构 #

在应用层把查询到的结果拼装为要求的格式，以Python为例：

import pymysql
import json

# 数据库连接逻辑省略
cursor = db.cursor()
cursor.execute("SELECT city, GROUP_CONCAT(username SEPARATOR ',') as user_list FROM users WHERE city IS NOT NULL GROUP BY city")
rows = cursor.fetchall()

result = {
    "cities_arry": {}
}
for city, user_str in rows:
    result["cities_arry"][city] = user_str.split(",")

# 输出JSON结果
print(json.dumps(result, indent=2))
# 如果存储到MongoDB，直接把result字典插入对应集合即可

存储适配说明 #

如果用Redis存储的话，也可以做结构适配：把cities_arry下的每个城市作为独立的Redis键，比如命名规则为city:users:{城市名}，值存储对应用户名数组的JSON序列化字符串，不需要嵌套结构也能满足查询需求。如果要求必须保留原嵌套结构，直接写入MongoDB即可，不需要额外做结构转换。 #

性能优化方案 #

• 加索引：给city字段建立普通索引，既可以加快分组聚合的速度，也可以避免全表扫描，1万条数据的场景下基本可以做到毫秒级返回
• 优先用数据库聚合：不要把全量1万条原始数据拉到应用层再做分组，数据库层面的聚合经过专项优化，效率比应用层自己处理高3~5倍，同时还能大幅减少数据传输量
• 过滤无效数据：查询阶段就过滤city为空的记录，避免后续不必要的计算
• 大数据量适配：如果后续数据量涨到百万级以上，可以用流式查询分批拉取聚合结果，避免一次性加载全部数据导致应用层内存溢出

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