SQL优化案例巧用主键分页减少DISTINCT开销SELECT task.task_name AS taskName, GROUP_CONCAT( DISTINCT template.template_name SEPARATOR ; ) AS templateName, a.batch_number AS batchNumber, COUNT( DISTINCT a.task_detail_id ) AS detailNum, MIN( a.STATUS ) AS STATUS, SUM( CASE WHEN a.STATUS 2 THEN 1 ELSE 0 END ) AS docNum, SUM( CASE WHEN a.STATUS 2 THEN 1 ELSE 0 END ) AS successNum, SUM( CASE WHEN a.STATUS 1 THEN 1 ELSE 0 END ) AS failNum, a.generation_method AS generationMethod, MIN( a.create_time ) AS createTime, a.last_update_time AS lastUpdateTime FROM table_batch a INNER JOIN table_template template ON a.acc_template_id template.acceptance_template_id INNER JOIN table_task task ON task.task_id template.task_id WHERE task.task_id 123456 GROUP BY a.batch_number ORDER BY createTime DESC LIMIT 20 # limit是分页器添加的背景线上一张分表数据量达900w某查询在小数据量时1s数据量上来后飙到1min。适用场景页面数据量有上限可预期比如500,1000GROUP BY 分组后总量远大于单页数据量无法改造索引或表结构核心优化点优化前优化后全表GROUP BY DISTINCT → LIMIT先分页取主键batch_number → 再IN查询900w数据参与去重仅500条数据参与去重效果查询时间从 60s 降至 20s 左右优化约66%。问题分析SIMPLEtaskPRIMARY,index_task_idPRIMARY202const1Using temporary; Using filesortSIMPLEtemplatePRIMARY,idx_template_id,idx_task_id,idx_acceptance_template_ididx_task_id403const2SIMPLEaidx_batch_task,idx_acc_template_ididx_acc_template_id203acceptancedoc.template.acceptance_template_id1118Using index conditionexplain 显示索引全命中但 COUNT(DISTINCT) 和 GROUP_CONCAT(DISTINCT) 导致索引扫描后还需额外去重成为性能瓶颈。在小数据量时查询1s内数据量达到900w时查询时间来到1min排查后发现count(distinct)以及group_concat(distinct)严重拖慢了查询效率或者说主要是distinct原先只需扫描索引现在多了一步去重。优化思路受业务限制分页最大500条但分组后总量8000。参考游标分页思想——缩小WHERE范围。由于无法使用 游标改用两步法查询所需页的主键idSELECT a.batch_number AS batchNumber FROM table_batch a INNER JOIN table_template template ON a.acc_template_id template.acceptance_template_id INNER JOIN table_task task ON task.task_id template.task_id WHERE task.task_id 123456 GROUP BY a.batch_number ORDER BY MIN( a.create_time ) DESC limit 20按主键id查询所需页的所有数据SELECT task.task_name AS taskName, GROUP_CONCAT( DISTINCT template.template_name SEPARATOR ; ) AS templateName, a.batch_number AS batchNumber, COUNT( DISTINCT a.task_detail_id ) AS detailNum, MIN( a.STATUS ) AS STATUS, SUM( CASE WHEN a.STATUS 2 THEN 1 ELSE 0 END ) AS docNum, SUM( CASE WHEN a.STATUS 2 THEN 1 ELSE 0 END ) AS successNum, SUM( CASE WHEN a.STATUS 1 THEN 1 ELSE 0 END ) AS failNum, a.generation_method AS generationMethod, MIN( a.create_time ) AS createTime, a.last_update_time AS lastUpdateTime FROM table_batch a INNER JOIN table_template template ON a.acc_template_id template.acceptance_template_id INNER JOIN table_task task ON task.task_id template.task_id WHERE task.task_id 123456 and batch_number in #{pageBatchNumber} GROUP BY a.batch_number ORDER BY createTime DESC拆成两个sql减少了去重的数据量由原先对所有去重再分页变成了先分页再去重尽管先分页获取主键id受限于大数据量下group by的速率但相较之前查询速度还是优化了7成左右 SQL优化案例：巧用主键分页减少DISTINCT开销
SQL优化案例巧用主键分页减少DISTINCT开销SELECT task.task_name AS taskName, GROUP_CONCAT( DISTINCT template.template_name SEPARATOR ; ) AS templateName, a.batch_number AS batchNumber, COUNT( DISTINCT a.task_detail_id ) AS detailNum, MIN( a.STATUS ) AS STATUS, SUM( CASE WHEN a.STATUS 2 THEN 1 ELSE 0 END ) AS docNum, SUM( CASE WHEN a.STATUS 2 THEN 1 ELSE 0 END ) AS successNum, SUM( CASE WHEN a.STATUS 1 THEN 1 ELSE 0 END ) AS failNum, a.generation_method AS generationMethod, MIN( a.create_time ) AS createTime, a.last_update_time AS lastUpdateTime FROM table_batch a INNER JOIN table_template template ON a.acc_template_id template.acceptance_template_id INNER JOIN table_task task ON task.task_id template.task_id WHERE task.task_id 123456 GROUP BY a.batch_number ORDER BY createTime DESC LIMIT 20 # limit是分页器添加的背景线上一张分表数据量达900w某查询在小数据量时1s数据量上来后飙到1min。适用场景页面数据量有上限可预期比如500,1000GROUP BY 分组后总量远大于单页数据量无法改造索引或表结构核心优化点优化前优化后全表GROUP BY DISTINCT → LIMIT先分页取主键batch_number → 再IN查询900w数据参与去重仅500条数据参与去重效果查询时间从 60s 降至 20s 左右优化约66%。问题分析SIMPLEtaskPRIMARY,index_task_idPRIMARY202const1Using temporary; Using filesortSIMPLEtemplatePRIMARY,idx_template_id,idx_task_id,idx_acceptance_template_ididx_task_id403const2SIMPLEaidx_batch_task,idx_acc_template_ididx_acc_template_id203acceptancedoc.template.acceptance_template_id1118Using index conditionexplain 显示索引全命中但 COUNT(DISTINCT) 和 GROUP_CONCAT(DISTINCT) 导致索引扫描后还需额外去重成为性能瓶颈。在小数据量时查询1s内数据量达到900w时查询时间来到1min排查后发现count(distinct)以及group_concat(distinct)严重拖慢了查询效率或者说主要是distinct原先只需扫描索引现在多了一步去重。优化思路受业务限制分页最大500条但分组后总量8000。参考游标分页思想——缩小WHERE范围。由于无法使用 游标改用两步法查询所需页的主键idSELECT a.batch_number AS batchNumber FROM table_batch a INNER JOIN table_template template ON a.acc_template_id template.acceptance_template_id INNER JOIN table_task task ON task.task_id template.task_id WHERE task.task_id 123456 GROUP BY a.batch_number ORDER BY MIN( a.create_time ) DESC limit 20按主键id查询所需页的所有数据SELECT task.task_name AS taskName, GROUP_CONCAT( DISTINCT template.template_name SEPARATOR ; ) AS templateName, a.batch_number AS batchNumber, COUNT( DISTINCT a.task_detail_id ) AS detailNum, MIN( a.STATUS ) AS STATUS, SUM( CASE WHEN a.STATUS 2 THEN 1 ELSE 0 END ) AS docNum, SUM( CASE WHEN a.STATUS 2 THEN 1 ELSE 0 END ) AS successNum, SUM( CASE WHEN a.STATUS 1 THEN 1 ELSE 0 END ) AS failNum, a.generation_method AS generationMethod, MIN( a.create_time ) AS createTime, a.last_update_time AS lastUpdateTime FROM table_batch a INNER JOIN table_template template ON a.acc_template_id template.acceptance_template_id INNER JOIN table_task task ON task.task_id template.task_id WHERE task.task_id 123456 and batch_number in #{pageBatchNumber} GROUP BY a.batch_number ORDER BY createTime DESC拆成两个sql减少了去重的数据量由原先对所有去重再分页变成了先分页再去重尽管先分页获取主键id受限于大数据量下group by的速率但相较之前查询速度还是优化了7成左右