MySQL update select 速度缓慢

Question

有如下两个表，引擎都为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

返回

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

Answer 1

Nested subqueries. Once any SQL has nested subqueries, the speed will be greatly reduced. At work, unless I absolutely must use subqueries, I will never write nested subqueries. I see you (select * from b) There is no limit at the end to limit the number of lines? What if there are 10 million data?

Answer 2

You can try join insert into the temporary table first and then use the temporary table to join update

You can update while loop without considering efficiency
http://stackoverflow.com/questions/11430362/update-column-from-another-table-in-large-mysql-db-7-million-rows

Answer 3

It must be the sub-nested query that causes the speed to be too slow. If you are interested, you can send the data set so that you can help adjust the SQL. The IO overhead of retrieving all table data at once is too high. You can try writing like this:
update a set a.country = b.country, a.city = b.city from b where a.iplong >=b.ip1 and a.iplong < b.ip2;

In addition, I can’t see what data type your IP field is stored in. For this comparison, using int will get better performance.

Answer 4

The execution plan does not appear in the index, and iplong cannot enter the index. Can you post the table creation and index statements? For UPDATE with a large amount of data, it will be very slow because the rollback segment is relatively large. You can pass b table ip1, ip2 plus index
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;
To implement, then replace the two A tables.