Flask下MariaDB慢查询主键分块优化方案咨询

• 技术栈：MariaDB 10.5.15、Flask 2.1.2、Flask-Session 0.2.0、Python 3.9
• 核心故障：数据库查询耗时过长，查询返回大结果集（尤其是动态过滤条件为空、触发全表关联查询）时性能劣化极其明显
• 触发慢查询的SQL：

WITH pf 
AS (
SELECT short_description, long_description, count, unit, cost_characteristic, size, family_id, is_parent  
FROM positions 
INNER JOIN project_crafts 
USING (project_craft_id) 
INNER JOIN projects 
USING (project_id) 
WHERE ---dynamic WHERE conditions---
) 
SELECT positions.short_description, positions.long_description, positions.count, positions.unit, positions.cost_characteristic, project_crafts.size, positions.family_id, positions.is_parent  
FROM positions 
INNER JOIN pf 
ON positions.family_id=pf.family_id 
AND COALESCE(positions.is_parent, -1) <> COALESCE(pf.is_parent, -1) 
INNER JOIN project_crafts 
USING (project_craft_id)
UNION SELECT short_description, long_description, count, unit, cost_characteristic, size, family_id, is_parent  
FROM pf

计划将单条慢查询按主键固定步长拆分为多条子查询，每条子查询追加主键范围过滤条件，规则如下：

• 子查询1：Select ... FROM ... WHERE ... AND primary_key > 0 AND primary_key <= 100 ...
• 子查询2：Select ... FROM ... WHERE ... AND primary_key > 100 AND primary_key <= 200 ...
• 子查询3：Select ... FROM ... WHERE ... AND primary_key > 200 AND primary_key <= 300 ...
• 按固定步长持续拆分直到覆盖全量数据

配套业务逻辑设计：

• 查询结果用于响应POST请求，通过后台独立线程/进程执行分块查询，将每块结果持续写入Session中名为search_query的列表变量
• 写入过程中结果量达到阈值、或第一块数据查询完成时，优先返回第一块数据，通过流式连接持续推送后续结果
• 方案预设前提：
  1. 数据库查询逻辑运行在独立Python线程/进程中
  2. 服务端与请求端建立流式连接
  3. 仅单进程写入目标Session变量、无删除操作，不会出现并发访问冲突

这个方案有几个很容易踩的坑，实际跑起来大概率达不到预期效果：

1. 固定步长切分的性能不稳定
   主键如果存在删除空洞、或某段主键范围内匹配的数据量远高于其他段，会出现单个子查询耗时波动极大，甚至某条子查询本身就是慢查询，根本起不到拆分降低单次查询耗时的作用。
2. 拆分后总计算开销反而暴涨
   你当前的SQL用了CTE结构，在MariaDB 10.5中，每条子查询如果保留原CTE逻辑，会重复执行CTE内的三表关联、重复做UNION去重计算，拆成N条子查询就会把原有的重计算逻辑跑N次，数据库总负载会比原来跑单条查询高几倍。
3. Flask-Session存储大结果集完全不可行
   Flask-Session的默认持久化逻辑是请求结束时才会把Session数据序列化写入后端存储，你在独立后台线程写Session变量，根本不会触发持久化，后续前端发请求拉取后续分块时根本读不到写入的数据。就算你手动触发持久化，大列表的序列化/反序列化开销、多worker部署下的Session不同步问题、用户中途断开请求后残留的垃圾Session数据，都会拖垮服务。
4. 后台线程+流式响应的可靠性差
   受Python GIL限制，后台线程执行数据库IO和数据计算时，主线程的流式传输会被阻塞；如果后台线程/进程意外崩溃，前端拿不到完整数据也没有感知，会返回残缺结果。

先做数据库层面的根因优化，这部分收益最高 #

你这个慢查询的核心问题不是数据量大，而是SQL写法和索引有问题，先做这几步：

1. 补全关联字段索引，直接干掉全表扫描
   给三张表的关联字段建联合索引，覆盖查询用到的字段，避免回表：

-- positions表索引，覆盖关联字段、判断字段、返回字段
CREATE INDEX idx_pos_craft_family_parent ON positions(project_craft_id, family_id, is_parent, short_description, long_description, count, unit, cost_characteristic);
-- project_crafts表索引
CREATE INDEX idx_pc_craft_project ON project_crafts(project_craft_id, project_id, size);
-- projects表关联字段索引
CREATE INDEX idx_prj_id ON projects(project_id);

2. 改写SQL去掉冗余计算
   你当前的CTE+自关联+UNION的写法重复做了两次三表关联，逻辑本质是：取出满足动态条件的记录，再取出和这些记录同family_id、is_parent值不同的关联记录，最后合并去重。完全可以改写为单轮扫描+窗口函数判断，避免重复关联和UNION的排序去重开销：

SELECT 
    short_description, long_description, count, unit, cost_characteristic,
    size, family_id, is_parent
FROM (
    SELECT 
        p.short_description, p.long_description, p.count, p.unit, p.cost_characteristic,
        pc.size, p.family_id, p.is_parent,
        COUNT(DISTINCT COALESCE(p.is_parent, -1)) OVER (PARTITION BY p.family_id) as parent_type_cnt
    FROM positions p
    INNER JOIN project_crafts pc USING (project_craft_id)
    LEFT JOIN projects pr USING (project_id)
    WHERE ---dynamic WHERE conditions---
) t
-- 同family下存在不同is_parent值的记录全部返回，和原逻辑等价，不需要自关联+UNION
WHERE parent_type_cnt > 1

如果动态条件为空需要返回全表，可以先把过滤后的主键结果存入带索引的内存临时表，再基于临时表做关联，避免重复计算。

分块查询和业务逻辑的优化 #

如果做完上面的优化，全量查询还是耗时太长需要拆分成块，不要用固定步长切主键，也不要用Session存结果：

• 用游标分页代替固定步长切分：每次查询记录当前批次的最大主键，下一批次用primary_key > 上次最大主键 LIMIT 块大小的方式拉取，避免主键空洞、数据倾斜导致的单查询耗时波动，而且不需要重复计算全量范围。
• 不要用Session存大结果集：给每个查询生成唯一的query_id，把分块结果存在带过期时间的缓存（比如Redis）里，前端每次传query_id和块序号拉取对应数据，用户断开连接后缓存自动过期清理，没有垃圾数据问题，也不会有Session持久化的坑。
• 流式响应不需要单独开后台线程：直接用Flask的生成器响应，在生成器里顺序执行分块查询，每查到一块就yield给前端，用户断开连接时生成器自动终止，不会继续跑无效查询，也没有并发写冲突。
• 如果结果集超过1万条，直接改成异步任务模式：后台生成完整结果文件后给用户返回下载链接，比同步流式传输稳定得多，也不会占着请求连接。

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