MySQL update语句带子查询的优化探寻在处理大数据量的MySQL表时，性能问题常常让人头疼。本文通过一个实际案例

0 背景

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


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语句如下：


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 表主键

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`)