时间戳

首页 > 解决方案 > 在 Self 上使用 IN 子查询优化更新查询

mysql - 在 Self 上使用 IN 子查询优化更新查询

问题描述

我有一个 80000 行数据库，结果编号在 130000000 和 168000000 之间，结果使用字段 pid 配对。我需要将行的状态从“G”更改为“X”，其中结果对的差异为 4300000。

我想出了下面的查询，它有效但速度很慢，可以提高速度吗？

UPDATE table1 SET status = 'X'
WHERE id IN (
SELECT id FROM (
    SELECT a.id AS id FROM table1 a, table1 b
    WHERE a.result = b.result + 4300000
    AND a.pid = b.pid
    AND a.result between 130000000 and 168000000
    AND a.status = 'G'
    ) AS c
);

索引是：-

table1  0   PRIMARY 1   id  A   80233   NULL    NULL        BTREE
table1  1   id  1       id  A   80233   NULL    NULL        BTREE
table1  1   id  2   result  A   80233   NULL    NULL        BTREE
table1  1   id  3   status  A   80233   4   NULL    YES BTREE
table1  1   id  4   name    A   80233   32  NULL        BTREE
table1  1   id  5    pid    A   80233   16  NULL        BTREE

标签： mysqlsql

解决方案

在子句中使用子查询IN(..)在 MySQL 中通常是低效的。相反，您可以使用UPDATE .. JOIN语法重写更新查询并使用“自联接”：

UPDATE table1 AS a 
JOIN table1 AS b 
  ON b.pid = a.pid 
     AND b.result = a.result - 4300000 
SET a.status = 'X'
WHERE a.result between 130000000 and 168000000 
  AND a.status = 'G'

为了获得良好的性能（如果我正确理解NLJ（Nested-Loop-Join）），您将需要两个索引：(status,result)和(pid).

第一个（复合）索引将用于考虑表别名中的行a。由于我们有范围条件result，最好先定义status，否则由于范围条件，MySQL 将简单地停在result索引中的字段（如果先定义）。

第二个索引将用于在 Joined 表别名中查找b，使用NLJ 算法。

推荐阅读