MySQL update语句带子查询的优化探寻

MySQL update带IN子查询慢查询优化

原创于 2024-12-16 14:10:28 发布 · 483 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #android #数据库

0 背景

某个需求下有两张表（已简化）：

sql

代码解读

复制代码

CREATE TABLE `record` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` varchar(36) NOT NULL, `status` tinyint(3) unsigned NOT NULL DEFAULT '0', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `record_uuid_uindex` (`uuid`) ); CREATE TABLE `bill` ( `id` int(11) NOT NULL AUTO_INCREMENT, `apply_id` varchar(36) NOT NULL, `record_uuid` varchar(36) NOT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `bill_record_uuid_index` (`record_uuid`), KEY `bill_apply_id_index` (`apply_id`)

假设需求要将某个 apply_id 关联的 record 记录 status 值设为2，update语句如下：

sql

代码解读

复制代码

UPDATE record SET status=2 WHERE uuid in ( SELECT record_uuid FROM bill WHERE apply_id='xxxxxx' );

这样的SQL咋一看，record.uuid、bill.apply_id 均有索引，似乎并没有什么问题，但实际上线后（record表千万数量级）却会执行超时（5秒）。

1 探寻慢查询原因

测试环境 MySQL 版本 5.7.21，构造假数据量如下：

其中 apply_id='b1e83226-d827-4132-a4ef-e8d0e929179b' 关联的record数据仅3行

SELECT * FROM record WHERE uuid IN (SELECT record_uuid FROM bill WHERE apply_id='b1e83226-d827-4132-a4ef-e8d0e929179b');

查看该 SELECT 语句执行计划，可以看到索引使用和扫描行数是正常的：

MySQL优化器将该语句优化成了联表查询：

对应的update语句耗时和执行计划：

执行计划咋一看有走索引，实际上从key和扫描行数rows可看出走了全表扫描，导致执行时长需0.12秒。

为何会走全表扫描？

既然key用了主键，那么用主键做关联会有改善吗？建表 bill_id，其中 record_id 关联 record 表主键

sql

代码解读

复制代码

CREATE TABLE `bill_id` ( `id` int(11) NOT NULL AUTO_INCREMENT, `apply_id` varchar(36) NOT NULL, `record_id` int(11) NOT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `bill_id_apply_id_index` (`apply_id`), KEY `bill_id_record_id_index` (`record_id`)

结果如下，表现一致，仍就走了全表扫描。