如何优化从海量数据JSON列统计驳回原因计数的MySQL查询

性能瓶颈核心 #

你当前查询慢的根本原因是使用了SELECT子句中的关联子查询，每返回一行外层查询的结果，就要重复遍历一次200多万行的job_order_finish_product_serial_no表，相当于嵌套了N次全表/索引扫描，耗时自然会指数级上升。

优化方案 #

1. 替换关联子查询为条件聚合（性能提升最明显） #

一次扫描满足条件的序列号表，通过条件判断直接统计各驳回原因的数量，避免重复查表：

SELECT 
    DATE_FORMAT(tc.tc_date, '%d-%m-%Y') AS `date`,
    SUM(CASE WHEN jfps.specification->>'$.Thickness.status' = 'rejected' THEN 1 ELSE 0 END) AS rejected_thickness,
    SUM(CASE WHEN jfps.specification->>'$.Leakage.status' = 'rejected' THEN 1 ELSE 0 END) AS rejected_leakage,
    SUM(CASE WHEN jfps.specification->>'$.Bung.status' = 'rejected' THEN 1 ELSE 0 END) AS rejected_bung,
    SUM(CASE WHEN jfps.specification->>'$.Diameter.status' = 'rejected' THEN 1 ELSE 0 END) AS rejected_diameter
FROM nucleus.tc_details tc
INNER JOIN nucleus.job_order_finish_product_serial_no jfps 
    ON jfps.tc_id = tc.id 
    AND jfps.client_id = 154
INNER JOIN nucleus.job_order_finish_products jofp 
    ON jofp.id = jfps.job_order_finish_products_id 
    AND jofp.client_id = 154
WHERE 
    tc.tc_date BETWEEN '2021-09-18 00:00:00' AND '2021-09-22 23:59:59'
    AND tc.client_id = 154
GROUP BY DATE(tc.tc_date)

上述查询按天聚合和你给出的期望输出格式匹配，如果你确实需要按产品+日期分组，把GROUP BY子句改回你原来的job_order_finish_product_id,tc.tc_date即可。

2. 添加针对性索引（进一步降低扫描成本） #

针对你用到的过滤、关联字段，建立如下复合索引：

• tc_details表加联合索引：idx_client_date(client_id, tc_date)，覆盖WHERE条件的过滤字段
• job_order_finish_product_serial_no表加联合索引：idx_client_tc(client_id, tc_id, job_order_finish_products_id)，覆盖关联条件和过滤条件
• 如果你的MySQL版本≥5.7，可对JSON中高频查询的状态字段生成虚拟列并建立索引，避免每次查询都解析JSON：

  -- 新增虚拟列
  ALTER TABLE job_order_finish_product_serial_no 
  ADD COLUMN reject_thickness_status VARCHAR(32) GENERATED ALWAYS AS (specification->>'$.Thickness.status') VIRTUAL,
  ADD COLUMN reject_leakage_status VARCHAR(32) GENERATED ALWAYS AS (specification->>'$.Leakage.status') VIRTUAL,
  ADD COLUMN reject_bung_status VARCHAR(32) GENERATED ALWAYS AS (specification->>'$.Bung.status') VIRTUAL,
  ADD COLUMN reject_diameter_status VARCHAR(32) GENERATED ALWAYS AS (specification->>'$.Diameter.status') VIRTUAL;
  
  -- 给虚拟列加索引（可和前面的联合索引合并）
  ALTER TABLE job_order_finish_product_serial_no 
  ADD INDEX idx_reject_status(client_id, tc_id, reject_thickness_status, reject_leakage_status, reject_bung_status, reject_diameter_status);
  

索引建好后，把查询里的specification->>'$.xxx.status'换成对应的虚拟列名，查询速度会进一步提升。

3. 长期迭代优化 #

如果数据量还在持续增长，可以考虑把驳回状态字段从JSON中提取出来作为普通字段存储，或者定期生成汇总统计结果存到中间表，查询时直接读中间表，耗时可以降到毫秒级。

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