更新语句中的MySQL查询缓慢 [英] Slow MySQL query on update statement

问题描述

我正在尝试将一些数据从数据库移动到另一个数据库.我目前在数据库中有超过一百万个条目，我期望这花费很长时间，但已经过去了50分钟，但没有结果:). 这是我的查询:

I am trying to move some data from a database to another. I am currently having over a million entries in my database and I was expecting this to take long but already passed 50min and no result :) . Here is my query:

UPDATE xxx.product AS p 
LEFT JOIN xx.tof_art_lookup AS l ON p.model_view = l.ARL_SEARCH_NUMBER 
SET p.model = l.ARL_DISPLAY_NR 
WHERE p.model_view = l.ARL_SEARCH_NUMBER;

欢迎提供有关如何改进此查询的任何帮助.预先感谢！

Any help on how to improve this query will be welcome. Thanks in advance!

推荐答案

p.model_view上的索引，如果您不想摆脱JOIN的话，则指向l.ARL_SEARCH_NUMBER.
实际上，可以使用以下方法根据实际数据量及其值(存在NULL)对其进行优化:
1.监视查询执行计划，如果不好的话，为编译器放置查询提示，或为子查询交换JOIN，以便编译器在其中使用另一种连接类型(合并/嵌套循环/哈希/其他)
2.使存储过程具有更复杂但更快速的逻辑
3.一小部分进行更新

Indexes on p.model_view, l.ARL_SEARCH_NUMBER if you not gonna get rid of JOINs.
Actually, it might be optimized depending on actual data amounts and their values (NULLs presence) by use of:
1. Monitoring query execution plan and , if it's not good, putting query hints for compiler or exchange JOINs for subqueries so compiler uses another type of join inside it (merge/nested loops/hashs/whatever)
2. Making a stored procedure with more comlicated but faster logic
3. Doing updates by small portions

这篇关于更新语句中的MySQL查询缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！