supervision-boot/sql/20261027_profile_query_performance.md

单位 / 个人问题库查询优化（MySQL 5.7.21）

本次改动

• 先按单位 ID / 身份证号聚合明细，再连接名称、职务等展示字段，避免在多字段的明细结果上分组。
• 分页使用自定义 count SQL，使用 EXISTS 判断是否有符合条件的问题，不再重复统计每个单位的涉及人数或每个人的问题数。
• 保留 DISTINCT 去重语义、筛选条件、全量排序后分页及总数。不新增缓存，不降低数据实时性。
• 单位仍按问题创建时间统计；个人仍按发现时间、已办结、属实/部分属实及个人责任记录统计，不调整业务口径。

生产核查步骤

1. 在业务库执行 20261027_profile_query_explain.sql，收集版本、表规模估计、索引及优化前后 8 份 EXPLAIN。默认不限时间、不选单位；如果慢请求带筛选条件，应按实际请求补上同样条件。
2. 检查 negative_blame 是否按 negativeId / blameIdCode 走索引、negative 是否使用对应时间和状态索引，以及连接字段的数据类型和字符集是否一致。小的聚合结果出现临时表或排序不等于优化失败。
3. 对照现有索引评估 20261027_profile_query_indexes.sql。脚本按完整左前缀跳过已有等价索引，不删除旧索引。先确认 InnoDB、字段长度、磁盘空间和同名索引冲突；低峰期执行。在线建索引仍消耗 I/O 并可能等待元数据锁，LOCK=NONE 不支持时会停止，不降级为锁表。
4. 索引准备后部署后端改动，再执行优化后 EXPLAIN 和实际请求。MySQL 5.7 不支持 EXPLAIN ANALYZE；EXPLAIN 的 rows 是估计值，不能用来证明 3 秒达标。
5. 性能验收同时计入总数 SQL、列表 SQL、接口开销和网络耗时。分别验证首次请求与重复请求、不限时间与短时间范围、单位/姓名筛选、升降序、后续分页，以及实际并发下的耗时。避免把查询缓存命中误判为 SQL 本身已优化。

验证状态

• 后端编译、SQL 映射与排序/筛选回归测试已通过。
• 本地模拟使用 SQLite 内存数据库及 12 万条问题，属于结果一致性和优化方向验证，不是生产 MySQL 5.7 性能验收。
• 开发数据库连接失败；生产索引、执行计划和端到端耗时尚未取得，不能承诺已达到 3 秒。
• 索引脚本尚未对任何数据库执行；本次未改前端或运行前端构建。

本地模拟记录

数据：12 万条问题、约 43.6 万条责任记录、1.2 万个人、200 个三级单位，包含重复人员资料、重复责任记录、空身份证号和不符合状态/时间条件的记录。使用 SQLite 内存数据库及拟新增索引；新旧查询在相同索引下比较。

两类列表各 10 组筛选/排序场景的全部返回行、总数和第二页条数一致。每次计时包含分页总数查询及第一页 10 条列表查询，取三次中位数：

查询	原查询	优化后
单位问题库	9435.6 ms	2198.2 ms
个人问题库	3227.7 ms	1009.9 ms

这些数字仅用于本地优化方向验证；不含生产网络、真实字段宽度/索引分布、磁盘和并发开销，不能据此认定生产接口已达到 3 秒。

生产执行计划反馈（2026-10-27）

result.txt 的表规模为 information_schema 估计：negative 112186 行、negative_blame 117768 行、sup_police 21144 行。

• 单位原查询和改写后的聚合查询中，negative_blame 都未出现可用的连接索引（possible_keys 为空），扫描 idx_negativeId_blameIdCode 全索引并使用 Block Nested Loop。该索引明明以 negativeId 开头，需优先检查其与 negative.id 的数据类型、字符集和排序规则。
• 单位人数表 sup_depart_police_size 有 depart_id 主键，却以 ALL + Block Nested Loop 关联；sup_depart.id 的主键 key_len 为 4，需检查两侧类型是否一致。
• 单位分页总数的 DEPENDENT SUBQUERY 估计扫描 negative 112186 条索引记录；需检查 negative.involveDepartId 与 sup_depart.id 的类型转换。
• 个人优化后列表中 sup_police 已使用 index_id_code 的 ref 查找，negative 使用主键 eq_ref；这是计划改善，但尚无实测耗时。其 ref=func 表明关联表达式经过计算，需通过 JSON 计划确认具体转换。
• 上述推导出的万亿级派生表 rows 是优化器估计，不是实际生成或扫描的行数。

下一步执行 20261027_profile_query_column_check.sql。现有候选索引脚本暂缓执行；在明确字段定义前不添加 CAST、CONVERT、FORCE INDEX，也不直接修改生产字段，避免统计语义变化或无效建索引。

已确认的索引失效原因及修正（result1.txt）

• negative.id 为 utf8mb4，negative_blame.negativeId 为 utf8。原执行计划在 nb.negativeId 上执行 CONVERT(... USING utf8mb4)，导致责任表全索引扫描。单位列表改为在驱动侧 n.id 转成 utf8，并显式指定 utf8_general_ci，让 nb.negativeId 保持原始索引列。
• sup_depart.id 为 int，negative.involveDepartId 和 sup_depart_police_size.depart_id 为 utf8 varchar。MySQL 返回 Warning 1739，无法对这两个字符串索引做 ref/range 查找。列表和总数查询改为将 d.id 转成 utf8 字符串，不对被查找的字符串列做函数转换。
• 个人列表中 n 主键原本已为 eq_ref，不盲目反转该关联的转换方向。继续保留先聚合后关联的优化，通过生产复测决定是否需要覆盖索引。

执行 20261027_profile_query_retest.sql：先单独执行数据检查，三项 affected_rows 全为 0 才部署单位查询；非零说明前导零/非标准数字或不能转换为 utf8 的问题 ID 可能影响新旧统计一致性，必须先核查。后续 SQL 提供新计划和 SQL_NO_CACHE 实测，SHOW PROFILES 中列表与总数的 Duration 之和才是该页的数据库耗时；接口还包含网络与应用开销。

本次未执行生产 DDL；候选索引仍暂缓，优先复用已有索引验证转换方向修正的收益。生产 3 秒目标尚未验收。