在MySQL中，用update join，里面有order by，会导致MySQL崩溃吗？

Question

在MySQL中，用update join，里面有order by，会导致MySQL崩溃吗？

Jinyun

36516

发布于
2023-10-10 美国

更新于
2023-10-13

我现在有两个SQL文，第一个可以正常执行，第二个一执行MySQL就崩溃。。
崩溃指的是：docker里面对应的那个MySQL容器直接没了，docker ps -a都看不到的那种。

第一个SQL文：

update
    cfg_dev_network_push_status as T1
inner join cfg_dev_network_bind_status as T2 on
    T1.task_id = T2.id
    and T1.del_flag = '0'
    and T1.push_type = '1'
    and T1.push_result != '0'
    and T1.push_retry_times < 3
    and T2.del_flag = '0'
    and T2.dev_id = 'theDevId'
    and T2.status = '1' set
    T1.push_retry_times = T1.push_retry_times + 1,
    T1.push_status = '1',
    T1.push_end_time = '20231010141154002',
    T1.push_result = '1',
    T1.fail_reason = '109'
order by
    T1.id asc

第二个SQL文：

update
    cfg_dev_network_push_status as T1
inner join cfg_dev_network_bind_status as T2 on
    T1.task_id = T2.id
    and T1.del_flag = '0'
    and T1.push_type = '1'
    and T1.push_result != '0'
    and T1.push_retry_times < 3
    and T2.del_flag = '0'
    and T2.dev_id = 'theDevId'
    and T2.status = '1' set
    T1.push_retry_times = T1.push_retry_times + 1,
    T1.push_status = '1',
    T1.push_end_time = '20231010141154002',
    T1.push_result = '1',
    T1.fail_reason = '109'
order by
    T1.push_status desc,
    T1.push_retry_times desc,
    T1.id asc

经测试，第二个SQL文里面，T1.push_status desc和T1.push_retry_times desc，加任意一个，都会导致MySQL崩溃，只有T1.id asc的时候，是可以正常执行的。
push_status的数据类型是char(1)，数据里面非0即1。
push_retry_times的数据类型是int。

数据库中两个表的数据都在50条左右，不存在内存过大的影响；
直接在DBeaver中进行数据查询，不考虑锁的影响。

之后我在服务器中没找到MySQL相关崩溃的日志。。。。

所以，为啥会崩呗，求各路大神帮助~

=================================================================
建表文如下所示

-- `lebon-infra-dev`.cfg_dev_network_push_status definition

CREATE TABLE `cfg_dev_network_push_status` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '流水号  | 与id为同一字段，自增数列',
  `task_id` varchar(16) NOT NULL COMMENT '任务ID',
  `push_status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT '推送状态(0:待推送/1:已推送/2:已取消)',
  `push_plan_time` char(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '预约推送时间（YYYYMMDDHHMMSS）',
  `push_type` char(1) NOT NULL COMMENT '推送种类(1:推送配置项/2:推送优先度/3:推送重启命令)',
  `dev_id` varchar(32) NOT NULL COMMENT '设备ID',
  `config_group_id` varchar(16) NOT NULL COMMENT '配置组ID',
  `config_item_id` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '配置项ID',
  `push_begin_time` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '00000000000000000' COMMENT '推送开始时间（YYYYMMDDHHMMSSFFF）',
  `push_end_time` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '00000000000000000' COMMENT '推送结束时间（YYYYMMDDHHMMSSFFF）',
  `push_duration_s` int NOT NULL DEFAULT '0' COMMENT '推送耗时',
  `push_result` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '推送结果0:成功/1:失败',
  `fail_reason` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '失败原因',
  `push_retry_times` int NOT NULL DEFAULT '0' COMMENT '重试次数',
  `remark` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '备注',
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT '删除状态',
  `version` int NOT NULL DEFAULT '0' COMMENT '版本锁',
  `create_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `cfg_dev_network_push_status_un` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=149 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='配置-终端网络配置推送状态';

-- `lebon-infra-dev`.cfg_dev_network_bind_status definition

CREATE TABLE `cfg_dev_network_bind_status` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '任务ID | 与id为同一字段，自增数列',
  `dev_id` varchar(32) NOT NULL COMMENT '设备ID',
  `config_group_id` varchar(16) NOT NULL COMMENT '配置组ID',
  `config_group_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '配置组名',
  `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT '状态',
  `push_plan_time` char(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '预约推送时间(YYYYMMDDHHMMSS)',
  `push_begin_time` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '00000000000000000' COMMENT '推送开始时间(YYYYMMDDHHMMSSFFF)',
  `push_end_time` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '00000000000000000' COMMENT '推送结束时间(YYYYMMDDHHMMSSFFF)',
  `push_duration_s` int NOT NULL DEFAULT '0' COMMENT '推送耗时',
  `effective_time` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '00000000000000000' COMMENT '实际生效时间(YYYYMMDDHHMMSSFFF)',
  `invalid_time` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '00000000000000000' COMMENT '实际失效时间(YYYYMMDDHHMMSSFFF)',
  `fail_reason` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '失败原因',
  `cancel_reason` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '取消原因',
  `operator` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '操作者',
  `remark` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '备注',
  `version` int NOT NULL DEFAULT '0' COMMENT '版本锁',
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT '删除状态',
  `create_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `cfg_dev_network_bind_status_un` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1070 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='配置-终端网络配置绑定状态';

数据库

mysql mysql优化

阅读 1.3k

AI BotBETA

在MySQL中，使用UPDATE JOIN语句时，ORDER BY子句可能会导致性能问题，尤其当涉及到的表数据量较大时。这是因为ORDER BY会对所有更新的行进行排序，而不仅仅是对影响行进行排序。

在你的第二个SQL语句中，你使用了多个排序条件（T1.push_status desc，T1.push_retry_times desc和T1.id asc）。这会增加排序操作的复杂性，并可能导致MySQL在处理时遇到困难。尤其当push_status和push_retry_times的数据类型是char(1)和int时，可能由于数据的特性导致排序操作出现问题。

另外，MySQL对于使用ORDER BY的UPDATE或DELETE语句有一些限制。当涉及到大量数据时，可能会因为内存不足而崩溃。

如果你需要进行排序，你可以考虑以下几种解决方案：

先进行排序，再进行更新：首先，使用相同的WHERE条件对表进行排序，并将结果存储在一个临时表中。然后，使用该临时表进行更新操作。

CREATE TEMPORARY TABLE TempTable AS
SELECT * FROM cfg_dev_network_push_status 
WHERE push_result != '0' 
AND push_retry_times < 3 
AND del_flag = '0' 
AND push_type = '1' 
AND dev_id = 'theDevId' 
AND status = '1' 
ORDER BY push_status DESC, push_retry_times DESC, id ASC;

UPDATE cfg_dev_network_push_status as T1 
INNER JOIN cfg_dev_network_bind_status as T2 
ON T1.task_id = T2.id 
SET T1.push_retry_times = T1.push_retry_times + 1, 
T1.push_status = '1', 
T1.push_end_time = '20231010141154002', 
T1.push_result = '1', 
T1.fail_reason = '109' 
WHERE T1.id IN (SELECT id FROM TempTable);

使用脚本或程序进行更新：如果你有访问数据库服务器的脚本或程序，你可以考虑通过脚本或程序来进行更新操作。这样可以避免直接在数据库中执行可能存在问题的SQL语句。
考虑数据库设计：如果这些更新操作经常需要进行，考虑修改数据库设计，创建一个字段或表来跟踪需要更新的行。这样可以避免直接使用ORDER BY进行更新的需求。
升级MySQL版本：一些旧版本的MySQL可能在处理某些操作时存在问题。考虑升级到最新稳定版本，看是否解决问题。
增加内存限制：如果数据库服务器有足够的资源，你可以考虑增加MySQL的内存限制。这可以通过调整MySQL配置文件（如my.cnf）中的相关参数来实现。但是，这并不是一个长期解决方案，因为如果你的数据量非常大，增加内存限制可能只是延迟了崩溃的时间。

综上所述，最理想的解决方案可能是首先对数据进行排序，然后进行更新操作。这样可以避免直接在MySQL中使用ORDER BY的更新语句，可能导致的性能问题或崩溃。

1 个回答

得票最新

范捷琦Jackie

2.1k4524

发布于
2023-10-16 江苏

✓ 已被采纳

试了一下，具体问题叫 Incorrect usage of UPDATE and ORDER BY ，意思是update语句如果有join那么是不允许order by的。不过，有点诡异的是你第1条语句居然成功了，我这边试下来你第1、2条语句都会报这个错。如果想先绕过这个问题，当下可以写成：

update
    cfg_dev_network_push_status as T1
set
    T1.push_retry_times = T1.push_retry_times + 1,
    T1.push_status = '1',
    T1.push_end_time = '20231010141154002',
    T1.push_result = '1',
    T1.fail_reason = '109'
where
    T1.del_flag = '0'
    and T1.push_type = '1'
    and T1.push_result != '0'
    and T1.push_retry_times < 3
    and T1.id in (
        select id 
        from cfg_dev_network_bind_status T2 
        where
            T2.del_flag = '0'
            and T2.dev_id = 'theDevId'
            and T2.status = '1' 
    )
order by
    T1.push_status desc,
    T1.push_retry_times desc,
    T1.id asc;

希望能帮助到你。

撰写回答

你尚未登录，登录后可以

和开发者交流问题的细节
关注并接收问题和回答的更新提醒
参与内容的编辑和改进，让解决方法与时俱进

推荐问题

相似问题

找不到问题？创建新问题

在MySQL中，用update join，里面有order by，会导致MySQL崩溃吗？

你尚未登录，登录后可以

我们知道MySQL字段可以存放纯文本，但是富文本有图片，有标题样式等情况，一般是怎么进行存储的呢？

在PHP的Yii框架下如何对接人大金仓数据库呢？

如何在SpringBoot/MySQL事务中并行执行多条SQL？

请问，如下我们在定义Link表的时候，是否是只能定义为4个字段？

(10元)为什么UTF8的TXT文件存入SQLite后大小增加近三倍？

请问表中是否有必要定义一个可以计算出的字段？

上面的sql通过login_time排序但是结果不对？