点滴之间 聚沙成金

实例：需要根据用户日志的ip地址计算出其地理地址

表结构：

用户日志表（200万条记录），其中address是待填充的字段：

CREATE TABLE `tmp_open_ip` (
  `email` varchar(60) NOT NULL DEFAULT '',
  `address` varchar(50) NOT NULL DEFAULT '',
  `ip` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `email` (`email`),
  KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

另ip地址数据库表（44万条记录）

CREATE TABLE `ip` (
`s` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '开始ip',
`e` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '结束ip',
`a` varchar(50) NOT NULL DEFAULT '',
KEY `s` (`s`),
KEY `e` (`e`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

需要根据用户日志表 tmp_open_ip 里的 ip字段到ip地址数据库表里查询出对应的地理地址，将地址填充到address字段。

使用如下update语句执行：

UPDATE tmp_open_ip AS u 
INNER JOIN ip 
ON u.ip BETWEEN ip.s AND ip.e 
SET u.address = ip.a

在笔者的电脑上运行了速度非常之慢，执行了一个多小时（4500s）都没有完，也不知道还要多久。

实在看不过去，于是想到使用insert 是否会快一些，于是重新导一张表 tmp_open_log 与tmp_open_log完全一致。

创建一张表 tmp_open_address，是insert的目标表，为了速度更快，没建索引：

CREATE TABLE `tmp_open_address` (
`email` varchar(60) NOT NULL DEFAULT '',
`address` varchar(50) NOT NULL DEFAULT '',
`ip` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8

执行insert 语句

insert into tmp_open_address (email,address,ip)
select l.email,ip.a,l.ip
 from  tmp_open_log as l inner join ip on l.ip between ip.s and ip.e ;

/* Affected rows: 2,543,124  Found rows: 0  Warnings: 0  Duration for 3 queries: 16.922 sec. */

不到17s！本来还想去倒杯水、稍事休息一下，结果已经执行完毕。


到本文写完时，前面的update语句已经执行了5000s，结束仍是遥遥无期。

所以，对于大数据量执行update时，可以考虑改用insert 语句实现，可能麻烦一些，但高速带来的收益远大于麻烦！

后记：

直接杀死了update进程，去看看update执行了多少：运行

SELECT * FROM `tmp_open_ip` where address!=”

结果只有 11,373 ，照这个速度，要运行N天….