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,1000）
- GROUP BY 分组后总量远大于单页数据量
- 无法改造索引或表结构
核心优化点
| 优化前 | 优化后 |
|---|---|
| 全表GROUP BY + DISTINCT → LIMIT | 先分页取主键batch_number → 再IN查询 |
| 900w+数据参与去重 | 仅500条数据参与去重 |
效果
查询时间从 60s+ 降至 20s 左右，优化约66%。
问题分析
| SIMPLE | task | PRIMARY,index_task_id | PRIMARY | 202 | const | 1 | Using temporary; Using filesort |
|---|---|---|---|---|---|---|---|
| SIMPLE | template | PRIMARY,idx_template_id,idx_task_id,idx_acceptance_template_id | idx_task_id | 403 | const | 2 | |
| SIMPLE | a | idx_batch_task,idx_acc_template_id | idx_acc_template_id | 203 | acceptancedoc.template.acceptance_template_id | 1118 | Using index condition |
explain 显示索引全命中，但 COUNT(DISTINCT) 和 GROUP_CONCAT(DISTINCT) 导致索引扫描后还需额外去重，成为性能瓶颈。
在小数据量时，查询1s内，数据量达到900w+时，查询时间来到1min+
排查后，发现，count(distinct)以及group_concat(distinct)严重拖慢了查询效率
或者说主要是distinct，原先只需扫描索引，现在多了一步去重。
优化思路
受业务限制，分页最大500条，但分组后总量8000+。参考游标分页思想——缩小WHERE范围。
由于无法使用 > 游标，改用两步法：
- 查询所需页的主键id
SELECT 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成左右