三个MySQL查询容易踩的坑_mysql查询踩坑-CSDN博客

本文链接：https://blog.csdn.net/m0_74280230/article/details/127646467

1、无 Order by 的随机问题

简单来说，就是在没有 Order By 的情况下，如果SELECT的字段不同，返回的记录顺序是随机的，不一定一样。

因为返回的记录顺序跟插入顺序、主键顺序、以及字段上是否建立了索引等因素都有关联，可以通过下面的例子进行验证：

假设有这样的一张用户点击表：

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'); INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (5, 'eee', 50, '', '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 (6, 'fff', 60, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57'); 复制代码

首先来看插入顺序带来的影响，在顺序插入几条顺序后，不管查询多少次，获取的结果都与插入顺序一致：

# 全部字段 mysql> select * from t_user_click where click > 0; +----+-----+-------+--------+---------------------+---------------------+ | id | obj | click | remark | created_at | updated_at | +----+-----+-------+--------+---------------------+---------------------+ | 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 3 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | ... # 与上述获取全部字段的SQL结果一致 mysql> select id, click from t_user_click where click > 0; ... 复制代码

其次来看主键顺序这个因素，当该表进行过 DELETE/REPLACE/UPDATE 操作时，不会再按照插入顺序排序了，而是会按照主键ID进行排序。

# 进行update操作 UPDATE t_user_click SET id=7 WHERE id=3; # 此时按照主键排序，obj=ccc 变成最后一条，而不是第3条 mysql> select * from t_user_click where click > 0; +----+-----+-------+--------+---------------------+---------------------+ | id | obj | click | remark | created_at | updated_at | +----+-----+-------+--------+---------------------+---------------------+ | 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 5 | eee | 50 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 6 | fff | 60 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 7 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 | +----+-----+-------+--------+---------------------+---------------------+ # 与上述获取全部字段的SQL结果一致 mysql> select id, click from t_user_click where click > 0; ... 复制代码

最后来看有无索引这个因素，当我们在 click 字段上建立索引时，结果又不一样了：

# 建索引 create index t_user_click_click_index on t_user_click (click); # id=7 在最后一条 mysql> select * from t_user_click where click > 0; +----+-----+-------+--------+---------------------+---------------------+ | id | obj | click | remark | created_at | updated_at | +----+-----+-------+--------+---------------------+---------------------+ | 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 5 | eee | 50 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 6 | fff | 60 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 7 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 | +----+-----+-------+--------+---------------------+---------------------+ 6 rows in set (0.00 sec) # id=7 在第3条 mysql> select id, click from t_user_click where click > 0; +----+-------+ | id | click | +----+-------+ | 1 | 10 | | 2 | 20 | | 7 | 30 | | 4 | 40 | | 5 | 50 | | 6 | 60 | +----+-------+ # 我们可以通过 explain 命令来看两种查询方式的不同： # 两种方式一个没走索引，一个走了索引，从而导致返回结果的不同 mysql> explain select * from t_user_click where click > 0; type：ALL possible_keys：t_user_click_click_index key：NULL Extra：Using where mysql> explain select id, click from t_user_click where click > 0; type：index possible_keys：t_user_click_click_index key：t_user_click_click_index Extra：Using where; Using index 复制代码

2、Order by + Limit 的随机问题

简单来说就是进行**Order by的字段如果不唯一，则MySQL返回的记录是随机的，常见的表现就是数据分页后出现重复**。

不过，这种随机也不是随机算法那种打乱的随机，它跟数据的插入顺序，以及索引的建立也有一定关系，可以看看下面的例子。

假设有一张这样的用户表：

create table t_user_list ( id int(11) auto_increment primary key, name varchar(64) default '' not null comment '名称', age smallint default 0 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_list (id, name, age, created_at, updated_at) VALUES (1, 'aaa', 1, '2022-10-18 12:55:19', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (2, 'bbb', 2, '2022-10-18 12:55:18', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (3, 'ccc', 3, '2022-10-18 12:55:18', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (4, 'ddd', 4, '2022-10-18 12:55:18', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (5, 'eee', 5, '2022-10-18 12:55:18', '2022-10-18 12:55:18'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (6, 'ggg', 6, '2022-10-18 12:55:19', '2022-10-18 12:55:19'); INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (7, 'iii', 7, '2022-10-18 12:55:17', '2022-10-18 12:55:19'); # 分页查询 mysql> select * from t_user_list order by created_at limit 0, 3; +----+------+-----+---------------------+---------------------+ | id | name | age | created_at | updated_at | +----+------+-----+---------------------+---------------------+ | 7 | iii | 7 | 2022-10-18 12:55:17 | 2022-10-18 12:55:19 | | 2 | bbb | 2 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 3 | ccc | 3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | +----+------+-----+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> select * from t_user_list order by created_at limit 3, 3; +----+------+-----+---------------------+---------------------+ | id | name | age | created_at | updated_at | +----+------+-----+---------------------+---------------------+ | 4 | ddd | 4 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 5 | eee | 5 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 1 | aaa | 1 | 2022-10-18 12:55:19 | 2022-10-18 12:55:18 | +----+------+-----+---------------------+---------------------+ 3 rows in set (0.00 sec) 复制代码

而当你这时候新建一个 created_at 索引，并重新插入新的数据时，如果你再次查询，你会惊奇地发现数据重复了。

# 新建索引 create index t_user_list_created_at_index on t_user_list (created_at); # 插入新的测试数据 INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (8, 'jjj', 8, '2022-10-18 12:55:18', '2022-10-18 12:55:19'); INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (9, 'kkk', 9, '2022-10-18 12:55:18', '2022-10-18 12:55:19'); INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (10, 'mmm', 10, '2022-10-18 12:55:18', '2022-10-18 12:55:19'); # 再次分页查询 mysql> select * from t_user_list order by created_at limit 0, 3; +----+------+-----+---------------------+---------------------+ | id | name | age | created_at | updated_at | +----+------+-----+---------------------+---------------------+ | 7 | iii | 7 | 2022-10-18 12:55:17 | 2022-10-18 12:55:19 | | 2 | bbb | 2 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 3 | ccc | 3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | +----+------+-----+---------------------+---------------------+ 3 rows in set (0.00 sec) # id=3 的记录重复了 mysql> select * from t_user_list order by created_at limit 3, 3; +----+------+-----+---------------------+---------------------+ | id | name | age | created_at | updated_at | +----+------+-----+---------------------+---------------------+ | 3 | ccc | 3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 4 | ddd | 4 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | | 5 | eee | 5 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 | +----+------+-----+---------------------+---------------------+ 复制代码

关于记录重复的问题，个人的猜想是一开始没有 created_at 索引，此时磁盘的数据块的顺序与插入顺序一致，所以返回的结果一直是固定的；但是加了索引后，并且增加了 created_at 一致的几条数据，导致索引重建数据块顺序发生变化，从而记录重复（瞎猜的，大家可以一起讨论下）

3、聚合函数 + Limit的不准确问题

简单来说，就是在对数据进行 Limit分页时，同时使用聚合函数（比如SUM、COUNT等）对当前分页的结果进行聚合，则最终得到的聚合结果是不准确的。

还是以第一点的例子和数据为例：

mysql> select * from t_user_click; +----+-----+-------+--------+---------------------+---------------------+ | id | obj | click | remark | created_at | updated_at | +----+-----+-------+--------+---------------------+---------------------+ | 1 | aaa | 10 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 2 | bbb | 20 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 4 | ddd | 40 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 5 | eee | 50 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 6 | fff | 60 | | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 | | 7 | ccc | 30 | | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 | +----+-----+-------+--------+---------------------+---------------------+ # 没分页的总数 mysql> select sum(click) from t_user_click; +------------+ | sum(click) | +------------+ | 210 | +------------+ 1 row in set (0.00 sec) # 分页后的总数 mysql> select sum(click) from t_user_click limit 3; +------------+ | sum(click) | +------------+ | 210 | +------------+ # 可以通过子查询解决 select sum(tmp.click) from (select click from t_user_click limit 3) as tmp; 复制代码

可以看到，在有Limit的情况下，得到的聚合结果其实是所有记录的总和，并不是三条记录的总和。原因就是SELECT语句执行时有一定顺序，分别是 FROM、ON、JOIN、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY和LIMIT。

在上述SQL中，SELECT优先执行，即先执行 select sum(click) from t_user_click，这时已经得到结果为210，最终再执行 limit 3剔除不符合要求的记录。