目录

一、分页：LIMIT 与深翻页

二、排序：ORDER BY、索引与文件排序

三、分组：GROUP BY 与 HAVING

四、去重：DISTINCT、GROUP BY、窗口函数三剑客

五、聚合函数：COUNT/SUM/AVG/MAX/MIN 与 Rollup

六、五虎联动：一条报表 SQL 的完整调优

七、结语：把五虎将排兵布阵

---

一、分页：LIMIT 与深翻页

1.1 语法速览

SELECT * FROM t_order ORDER BY id DESC LIMIT 20;          -- 取前 20 条
SELECT * FROM t_order ORDER BY id DESC LIMIT 20 OFFSET 40;-- 取第 41~60 条
-- 简写：
SELECT * FROM t_order ORDER BY id DESC LIMIT 40,20;

1.2 深翻页的噩梦

SELECT * FROM t_order ORDER BY create_time LIMIT 500000,20;

• 优化器仍然要先扫描 500 000 行再丢掉前 500 000 行。
• 延迟、IO、CPU、Buffer Pool 全部爆炸。

1.3 游标分页（Keyset Pagination）
思路：记住“上一页最后一条”的排序键，直接 SEEK。

-- 上一页最后 create_time = '2025-08-14 12:00:00', id = 123456
SELECT * FROM t_order
WHERE (create_time,id) < ('2025-08-14 12:00:00',123456)
ORDER BY create_time DESC, id DESC
LIMIT 20;

排序列必须唯一或联合唯一，否则出现“跳行”。
• 可完美利用联合索引 (create_time DESC, id DESC)。

1.4 覆盖索引 + 延迟回表

SELECT a.* FROM t_order a
JOIN (
  SELECT id FROM t_order
  ORDER BY create_time DESC
  LIMIT 500000,20
) b ON a.id = b.id;

子查询只扫描二级索引，回表 20 次，IO 骤降。

1.5 分区裁剪
如果表按月 RANGE 分区，查询 2025-08 月份数据时，LIMIT 只会在 1 个分区里翻页，物理行数骤减。

二、排序：ORDER BY、索引与文件排序

2.1 单字段排序

SELECT * FROM t_user ORDER BY age DESC;

• 无索引 → Using filesort（内存/磁盘双路归并）。
• 有索引 age DESC 则可“索引顺序扫描”。

2.2 联合排序

SELECT * FROM t_order
WHERE status = 'PAID'
ORDER BY amount DESC, id ASC;

联合索引 (status, amount DESC, id ASC) 可实现“索引有序”。
• 注意最左前缀：WHERE 必须命中 status，否则索引失效。

2.3 排序缓冲区

SHOW VARIABLES LIKE 'sort_buffer_size'; -- 默认 256 KB

• 单条结果太大或并发太高时，可调大到 1~2 MB。
• 8.0 引入“增量排序”避免一次性分配大内存。

2.4 ORDER BY + LIMIT 的优化
MySQL 会在找到 LIMIT 行数后立即停止排序，所以深翻页场景下，排序成本与 OFFSET 成正比。

三、分组：GROUP BY 与 HAVING
 

3.1 基本语法

SELECT user_id, COUNT(*) AS cnt
FROM t_order
GROUP BY user_id;

3.2 HAVING 与 WHERE 区别
• WHERE 在分组前过滤行，HAVING 在分组后过滤组。

SELECT user_id, SUM(amount) AS total
FROM t_order
WHERE status = 'PAID'
GROUP BY user_id
HAVING total > 10000;

3.3 松散索引扫描 vs 紧凑索引扫描
• 松散：直接利用 (user_id) 索引，跳组。
• 紧凑：回表取行再聚合。

EXPLAIN FORMAT=JSON
SELECT user_id, COUNT(*) FROM t_order GROUP BY user_id;

Extra 出现 “Using index for group-by” 说明松散扫描成功。

3.4 分组+排序

SELECT user_id, COUNT(*) AS cnt
FROM t_order
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10;

需要额外排序步骤；可建 (user_id) 索引减少分组成本，再外部排序。

3.5 GROUPING SETS / ROLLUP

SELECT status, user_id, SUM(amount) AS total
FROM t_order
GROUP BY status, user_id WITH ROLLUP;

一键生成小计、合计；8.0 支持 GROUPING() 函数区分 NULL 与“汇总行”。

四、去重：DISTINCT、GROUP BY、窗口函数三剑客

4.1 DISTINCT

SELECT DISTINCT user_id FROM t_order;

• 本质等价于 GROUP BY user_id。
• 覆盖索引 (user_id) 可避免回表。

4.2 GROUP BY 去重

SELECT user_id FROM t_order GROUP BY user_id;

• 与 DISTINCT 相比，可扩展 HAVING COUNT(*) > 5 等过滤条件。

4.3 窗口函数 row_number() 去重（MySQL 8.0+）
需求：每个用户的最新订单。

SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
  FROM t_order o
) AS t
WHERE rn = 1;

• 相比自连接写法，代码更短、执行计划更稳定。

4.4 去重 + 分页

SELECT user_id
FROM t_order
GROUP BY user_id
ORDER BY user_id
LIMIT 20 OFFSET 100;

• 与游标分页思路一致，可用 user_id > ? 改写。

五、聚合函数：COUNT/SUM/AVG/MAX/MIN 与 Rollup

5.1 COUNT() vs COUNT(col)
• COUNT() 统计行数，包含 NULL。
• COUNT(col) 忽略 NULL；col 可为表达式。

5.2 SUM/AVG 的精度

SELECT SUM(amount), AVG(amount)
FROM t_order
WHERE status = 'PAID';

• DECIMAL 类型可保持金融精度；FLOAT/DOUBLE 有累积误差。

5.3 MAX/MIN 与索引
• 单列 MAX/MIN 可直接利用 B+Tree 最右/最左节点，复杂度 O(log n)。

SELECT MAX(amount) FROM t_order USE INDEX(idx_amount);

5.4 聚合 + CASE WHEN

SELECT
  SUM(CASE WHEN status='PAID' THEN amount ELSE 0 END) AS paid_amount,
  SUM(CASE WHEN status='REFUND' THEN amount ELSE 0 END) AS refund_amount
FROM t_order;

5.5 GROUP_CONCAT

SELECT user_id,
       GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR '|') AS products
FROM t_order_item oi
JOIN t_product p ON p.id = oi.product_id
GROUP BY user_id;

默认长度 1024，可临时调大：

SET SESSION group_concat_max_len = 65535;

5.6 JSON_ARRAYAGG / JSON_OBJECTAGG（8.0）
把聚合结果直接拼成 JSON，便于 API 直出。

六、五虎联动：一条报表 SQL 的完整调优

需求：2025 年 8 月，统计每个用户已付款订单总金额，取前 20 名，再分页展示第 3 页。

6.1 建表 & 索引

CREATE TABLE t_order (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2),
  status ENUM('PAID','UNPAID','REFUND'),
  create_time DATETIME,
  INDEX idx_ct_status_uid_amount (create_time, status, user_id, amount)
);

6.2 查询（深翻页写法）

SELECT user_id, SUM(amount) AS total
FROM t_order
WHERE create_time BETWEEN '2025-08-01' AND '2025-08-31'
  AND status = 'PAID'
GROUP BY user_id
ORDER BY total DESC
LIMIT 40,20;      -- 第 3 页

• 40 万用户时，LIMIT 40,20 仍需扫描 40 万分组结果；性能差。

6.3 游标分页改写

-- 上一页第 40 条记录 total = 9876.50, user_id = 12345
SELECT user_id, SUM(amount) AS total
FROM t_order
WHERE create_time BETWEEN '2025-08-01' AND '2025-08-31'
  AND status = 'PAID'
GROUP BY user_id
HAVING total < 9876.50 OR (total = 9876.50 AND user_id < 12345)
ORDER BY total DESC, user_id DESC
LIMIT 20;

• 利用 (total,user_id) 的唯一性，避免 OFFSET。

6.4 覆盖索引 + 临时表
• 上述 SQL 无法完全覆盖（需要回表拿 amount）。
• 优化：把 amount 作为索引列，GROUP BY 时只扫描二级索引。

6.5 结果缓存
报表类场景可打开 query_cache_type = DEMAND，或使用 Redis 缓存 5 分钟。

七、结语：把五虎将排兵布阵

• 分页：能游标就别 OFFSET。
• 排序：让 ORDER BY 走索引，filesort 是最后的倔强。
• 分组：松散扫描 + 覆盖索引，GROUP BY 也能飞。
• 去重：DISTINCT 够用就别开窗，8.0 以后优先考虑 row_number()。
• 聚合：COUNT/SUM/AVG 精度、GROUP_CONCAT 长度、JSON 聚合，全是隐藏地雷。

把以上五条写进团队规范，再配合定期 EXPLAIN 与慢日志 Review，你的 MySQL 查询将不再是“能跑就行”，而是“跑得漂亮”。