MySQL多表JOIN的UPDATE语句优化：填充history_price表eur_v字段

背景说明：加密货币（coin）+基础货币（basecoin）组成交易对（pair）；交易对+经纪商（broker）组成资产（asset，每个经纪商与交易对的组合视为独立资产）。另有存储实时汇率的param_forex表。

我的交易历史表history_price中eur_v字段存在大量NULL值，需要通过Basecoin_v乘以对应汇率来填充。我拆分的查询步骤如下：

1. 查询对应货币：

SELECT `history_price`.`param_basecoin`.`Symbol` 
FROM `history_price`.`param_asset` 
INNER JOIN `param_pair` ON `history_price`.`param_asset`.`id_pair` = `history_price`.`param_pair`.`pair_id` 
INNER JOIN `history_price`.`param_basecoin` ON `history_price`.`param_pair`.`Coin2_id` = `history_price`.`param_basecoin`.`basecoin_id` 
WHERE `history_price`.`param_asset`.`Ticker` LIKE '???'

2. 查询汇率：

SELECT `Rate` 
FROM `history_price`.`param_forex` 
WHERE `Coin` LIKE '???' AND `Basecoin` LIKE 'EUR'

3. 更新eur_v字段：

UPDATE `history_price` 
SET `history_price`.`eur_v` = (`history_price`.`Basecoin_v` * ???) 
WHERE `history_price`.`eur_v` IS NULL

4. 整合步骤2到步骤3：

UPDATE `history_price` 
SET `history_price`.`eur_v` = (`history_price`.`Basecoin_v` * (SELECT `Rate` FROM `history_price`.`param_forex` WHERE `Coin` LIKE '???' AND `Basecoin` LIKE 'EUR')) 
WHERE `history_price`.`eur_v` IS NULL

5. 整合所有步骤后的最终语句：

UPDATE `history_price` 
SET `history_price`.`eur_v` = (`history_price`.`Basecoin_v` * (
    SELECT `Rate` 
    FROM `history_price`.`param_forex` 
    WHERE `Coin` LIKE (
        SELECT `history_price`.`param_basecoin`.`Symbol` 
        FROM `history_price`.`param_asset` 
        INNER JOIN `param_pair` ON `history_price`.`param_asset`.`id_pair` = `history_price`.`param_pair`.`pair_id` 
        INNER JOIN `history_price`.`param_basecoin` ON `history_price`.`param_pair`.`Coin2_id` = `history_price`.`param_basecoin`.`basecoin_id` 
        WHERE `history_price`.`param_asset`.`Ticker` LIKE `history_price`.`Ticker`
        ) 
        AND `Basecoin` LIKE 'EUR'
        )
    ) 
WHERE `history_price`.`eur_v` IS NULL;

该语句可正常运行，但执行速度极慢，请问有什么方法可以优化该语句以提升执行效率？

1. 用JOIN替代嵌套子查询 #

原语句的嵌套子查询会对history_price的每一行重复执行两次查询，开销极大。改成JOIN关联所有表，一次性获取数据后完成更新：

UPDATE `history_price` hp
JOIN `param_asset` pa ON hp.`Ticker` = pa.`Ticker`
JOIN `param_pair` pp ON pa.`id_pair` = pp.`pair_id`
JOIN `param_basecoin` pb ON pp.`Coin2_id` = pb.`basecoin_id`
JOIN `param_forex` pf ON pb.`Symbol` = pf.`Coin` AND pf.`Basecoin` = 'EUR'
SET hp.`eur_v` = hp.`Basecoin_v` * pf.`Rate`
WHERE hp.`eur_v` IS NULL;

这种方式将所有关联逻辑一次性完成，避免了逐行重复查询的冗余操作。

2. 添加针对性索引 #

慢查询的核心原因几乎都是缺少合适的索引，给以下字段添加索引：

• param_asset：Ticker（关联history_price）、id_pair（关联param_pair）
• param_pair：pair_id、Coin2_id（关联param_basecoin）
• param_basecoin：basecoin_id、Symbol（关联param_forex）
• param_forex：联合索引(Coin, Basecoin)（匹配汇率查询条件）
• history_price：eur_v（过滤NULL值）、Ticker（关联param_asset）

示例索引创建语句：

-- param_asset索引
CREATE INDEX idx_pa_ticker ON `param_asset`(`Ticker`);
CREATE INDEX idx_pa_idpair ON `param_asset`(`id_pair`);

-- param_pair索引
CREATE INDEX idx_pp_pairid ON `param_pair`(`pair_id`);
CREATE INDEX idx_pp_coin2id ON `param_pair`(`Coin2_id`);

-- param_basecoin索引
CREATE INDEX idx_pb_basecoinid ON `param_basecoin`(`basecoin_id`);
CREATE INDEX idx_pb_symbol ON `param_basecoin`(`Symbol`);

-- param_forex联合索引
CREATE INDEX idx_pf_coin_basecoin ON `param_forex`(`Coin`, `Basecoin`);

-- history_price索引
CREATE INDEX idx_hp_eurv ON `history_price`(`eur_v`);
CREATE INDEX idx_hp_ticker ON `history_price`(`Ticker`);

注意：按需创建索引，避免冗余索引占用额外资源。

3. 分批处理大更新（数据量极大时） #

如果history_price中需要更新的NULL值行数极多，一次性更新可能锁表或占用过多资源，可以分批循环更新：

-- MySQL示例：每次更新1000行，直到没有可更新数据
WHILE EXISTS (SELECT 1 FROM `history_price` WHERE `eur_v` IS NULL) DO
    UPDATE `history_price` hp
    JOIN `param_asset` pa ON hp.`Ticker` = pa.`Ticker`
    JOIN `param_pair` pp ON pa.`id_pair` = pp.`pair_id`
    JOIN `param_basecoin` pb ON pp.`Coin2_id` = pb.`basecoin_id`
    JOIN `param_forex` pf ON pb.`Symbol` = pf.`Coin` AND pf.`Basecoin` = 'EUR'
    SET hp.`eur_v` = hp.`Basecoin_v` * pf.`Rate`
    WHERE hp.`eur_v` IS NULL
    LIMIT 1000;
END WHILE;

不同数据库的循环语法有差异，比如PostgreSQL需要用PL/pgSQL实现类似逻辑。

4. 替换不必要的LIKE操作 #

如果Ticker和Symbol是精确匹配场景，直接用=替代LIKE——LIKE在无通配符时虽然优化器可能处理，但明确使用=更高效，也避免歧义。

内容的提问来源于stack exchange，提问作者Mat