MySQL查询的3个常见坑：排序随机、分页重复与聚合偏差

1、无 ORDER BY 的记录排序不确定性

在没有显式指定 Order By 的情况下，SELECT 语句返回的记录顺序是不确定的。这个顺序受插入顺序、主键分布以及索引使用情况等多重因素影响。如果不强制排序，即使查询相同的条件，不同时期或不同场景下获取的结果顺序也可能发生变化。

以一张用户点击表为例进行分析：

1. 纯插入状态下的表现：当表中新插入的数据尚未发生删除或更新时，查询通常按物理插入顺序排列。此时无论查询全部字段还是部分字段，顺序均保持一致。
2. 主键变更带来的影响：一旦表中发生了 UPDATE 操作修改了主键（如将 ID=3 的行 ID 改为 7），后续查询会按照新的主键顺序排列。这意味着原来排在中间的数据会被移动到末尾，打破了原有的物理插入顺序。
3. 索引介入后的逻辑差异：若在普通字段（如 click）上建立了索引，并且查询使用了覆盖索引（即只查该索引包含的字段），执行计划将从全表扫描变为索引扫描，此时返回的顺序遵循 B+树的叶子节点顺序，可能与主键顺序完全不同。

第1步：创建基础测试表及数据

目的是建立一个带有自增主键的基础数据表，观察默认排序行为。

create table t_user_click
(
    id         int(11) auto_increment primary key,
    obj        varchar(64) default ''                not null comment '点击对象',
    click      smallint    default 0                 not null comment '点击数',
    remark     varchar(64) default ''                not null comment '备注',
    created_at timestamp   default CURRENT_TIMESTAMP not null comment '创建时间',
    updated_at timestamp   default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间'
)
    comment '用户点击表';

INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (1, 'aaa', 10, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (2, 'bbb', 20, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (3, 'ccc', 30, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (4, 'ddd', 40, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');

第2步：验证主键更新对排序的影响

模拟数据变更场景，观察非自增主键对顺序的重排效果。

-- 修改一条记录的主键，将其值改变
UPDATE t_user_click SET id=7 WHERE id=3;

此时再次执行查询：select * from t_user_click where click > 0;。可以看到，原本应该排第三位的 obj=ccc，其主键变成了 7，因此被排到了所有记录的最后一位。

第3步：验证索引覆盖对排序的干扰

在普通字段建立索引并触发 Cover Index 时，查看 SELECT 不同字段组合时的差异。

-- 建立索引
CREATE INDEX t_user_click_click_index ON t_user_click (click);

-- 对比解释计划
EXPLAIN SELECT * FROM t_user_click WHERE click > 0;
EXPLAIN SELECT id, click FROM t_user_click WHERE click > 0;

当使用 select id, click 这种仅涉及索引字段的查询时，由于走的是索引扫描（type=index, extra=Using index），返回顺序完全依赖于 click 索引的树结构顺序，这与前两种情况的物理或主键顺序截然不同。为了避免此类 Bug，**只要业务要求数据有序，务必在 SQL 中加入 ORDER BY 子句**。

2、ORDER BY + LIMIT 导致的数据分页重复

在实现列表的分页功能时，开发者常遇到前后两页之间出现重复记录的问题。这并非 MySQL 内部 bug，而是因为 ORDER BY 排序依据的字段存在大量重复值。MySQL 在处理相同值的排序稳定性时，如果没有额外的唯一键作为二级排序条件，其底层实现可能受到数据在磁盘上的物理位置或索引顺序的影响，从而产生看似随机的输出。

第1步：模拟单字段排序下的分页稳定性

先不使用任何特殊索引，按特定时间顺序插入几条间隔相近的数据进行测试。

create table t_user_list
(
    id         int(11) auto_increment primary key,
    name       varchar(64) default ''                not null,
    age        smallint    default 0                 not null,
    created_at timestamp   default CURRENT_TIMESTAMP not null,
    updated_at timestamp   default CURRENT_TIMESTAMP not null
);

-- 插入时间戳有细微差别的数据
INSERT INTO t_user_list VALUES (1, 'aaa', 1, '2022-10-18 12:55:19', '2022-10-18 12:55:18');
INSERT INTO t_user_list VALUES (2, 'bbb', 2, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list VALUES (3, 'ccc', 3, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list VALUES (7, 'iii', 7, '2022-10-18 12:55:17', '2022-10-18 12:55:19');

执行分页查询：

SELECT * FROM t_user_list ORDER BY created_at LIMIT 0, 3;
SELECT * FROM t_user_list ORDER BY created_at LIMIT 3, 3;

在未建索引且数据量较小的情况下，往往能观察到固定的顺序，因为此时引擎可能直接基于物理行的存储顺序或简单的比较得出结果。第2步：引入索引后的数据重叠异常

当我们为排序字段 created_at 加上索引后，并在同一时间点密集插入新数据，再次进行分页查询，就会发现第二页开头出现了第一页结尾的那条记录（如 name='ccc'）。

-- 新建索引
CREATE INDEX t_user_list_created_at_index ON t_user_list (created_at);

-- 在同一秒时间级别插入多条数据
INSERT INTO t_user_list VALUES (8, 'jjj', 8, '2022-10-18 12:55:18', '2022-10-18 12:55:19');
INSERT INTO t_user_list VALUES (9, 'kkk', 9, '2022-10-18 12:55:18', '2022-10-18 12:55:19');

此时第二页查询 limit 3, 3 依然包含了 ID=3 的数据。这是因为在索引排序阶段，具有相同 created_at 值的多个条目，其在索引中的相对次序并不稳定。

解决方法：在 ORDER BY 后追加一个唯一的字段作为保底排序项。例如：ORDER BY created_at ASC, id ASC。这样能够彻底保证每条数据的绝对优先级，消除分页重叠的可能。

3、聚合函数结合 LIMIT 产生的逻辑误判

很多初学者认为在聚合查询语句末尾添加 LIMIT N，可以得到前 N 行数据的聚合结果。然而，事实恰恰相反——MySQL 的执行顺序决定了 LIMIT 在聚合完成之后才生效，因此它剔除的是已经算好的聚合结果行，而不是参与计算的原始数据行。

第1步：观察错误的聚合现象

沿用之前的点击表数据，我们分别执行不带限制的全局求和，与带限制的局部求和。

-- 期望计算所有记录的总和
SELECT SUM(click) FROM t_user_click;
-- 结果：210

-- 试图获取前三条点击数的总和
SELECT SUM(click) FROM t_user_click LIMIT 3;
-- 结果：依然是 210 （而非期望的 10+20+40=70）

第2步：理解 SQL 执行逻辑

SQL 语句的执行顺序大致如下：

1. FROM / JOIN / WHERE
2. GROUP BY / HAVING
3. SELECT（计算表达式及聚合函数）
4. ORDER BY
5. LIMIT

在执行到 SELECT 阶段时，数据库已经将 t_user_click 表内所有符合条件的数据完成了 SUM(click) 运算，得到一行最终结果。随后的 LIMIT 3 只是告诉数据库“把这一行结果给我看”，并不会回头去重新截取数据参与运算。

第3步：正确使用 LIMIT 配合聚合

如果业务确实需要“取前几条数据后再计算”的功能，必须利用子查询先行完成物理截断，再在外层进行聚合。

-- 正确的写法：先由子查询筛选出3条，再由外层统计总和
SELECT SUM(tmp.click) 
FROM (
    SELECT click FROM t_user_click LIMIT 3 
) AS tmp;

这种嵌套方式确保了外层的聚合函数面对的是已经被 LIMIT 截断后的临时结果集。

总结

以上分析了 MySQL 中三个高频发生的查询误区：

1. 避免隐式依赖默认排序：没有 ORDER BY 意味着结果集顺序不固定，受索引、数据增删改变动的影响极大。业务上有顺序要求的必须声明排序规则。
2. 分页需保障排序唯一性：ORDER BY 字段若有重复，会导致 LIMIT 分页在不同页码间发生数据遗漏或重复。请务必组合主键等唯一字段作为终态排序。
3. 明确聚合与 LIMIT 的关系：LIMIT 无法干预已经发生的聚合运算。若要分批次聚合，请使用子查询结构。