问

MySQL update select 速度缓慢

亲亲羊to 发布于 2022-11-05 22:52

join

select

copy

有如下两个表，引擎都为MyISAM
a，表a中包含150W条数据

b，表b中包含50W条数据

其中表a的iplong为ip地址的long类型，如果a.iplong >= b.ip1 and a.iplong < b.ip2 表b中的该记录中的country和city填充到a中的country和city

现在写了一条语句

update a inner join (SELECT * FROM b) on a.iplong >=b.ip1 and a.iplong < b.ip2
set a.country = b.country, a.city = b.city ;

粗略估计了一下可能需要16个小时。

请问有什么办法提升速度吗？这条语句耗时在哪部分？

更新1
explain update copy_of_log a use index (primary, iplong) inner join ipdizhi b on a.iplong >=b.ip1 and a.iplong < b.ip2 set a.country = b.country, a.city = b.city

没使用任何索引？是因为连接不会使用索引吗？这种功能难道使用子查询会更快吗？

4 个回答

执行计划看没踏上索引，，iplong 踏不上索引的。能否把建表，索引语句贴出来。对于大数据量的UPDATE 因为回滚段比较大，所以会很慢。可以通过 b表 ip1 ，ip2 加上索引
create table a_1 as select a.id,a.ipdizhi,a.iplong,(select b.country from b where a.iplong >=b.ip1 and a.iplong < b.ip2) as country,(select b.city from b where a.iplong >=b.ip1 and a.iplong < b.ip2) as city from a;
来实现，然后把两张a表换下。

2022-11-10 23:04 回答

Danny邓lovestars
这里肯定是子嵌套查询导致速度过慢，如果有兴趣可以把数据集发上来，这样可以帮忙调一下SQL。一次性取出所有表数据那个IO开销太高，可以试试这样写：
update a set a.country = b.country, a.city = b.city from b where a.iplong >=b.ip1 and a.iplong < b.ip2;

另外看不到你ip字段用什么数据类型存储的，这种比较用int会得到比较好的性能。

2022-11-10 23:04 回答
可以试试先join insert到临时表之后用临时表join update

不考虑效率可以while循环更新
http://stackoverflow.com/questions/11430362/update-column-from-another-table-in-large-mysql-db-7-million-rows

2022-11-10 23:09 回答

LYJ189_163
嵌套了子查询，任何SQL一旦嵌套了子查询速度大大降低,我在工作中，除非一定必须用子查询，否则我绝不会写嵌套子查询的，我看你 (select * from b) 后面居然不加 limit 限制行数？万一要是1000万的数据呢？

2022-11-10 23:14 回答

mobiledu2502861465

撰写答案

今天，你开发时遇到什么问题呢？

立即提问

热门标签