mysql update速度慢原因分析与解决办法
我们先来看看关于update语法
UPDATE
UPDATE的功能是更新表中的数据。这的语法和INSERT的第二种用法相似。必须提供表名以及SET表达式,在后面可以加WHERE以限制更新的记录范围。
代码如下 | |
|
如下面的语句将users表中id等于123的记录的age改为24
代码如下 | |
|
同样,可以使用UPDATE更新多个字段的值
代码如下 | |
UPDATE users SET age = 24, name = 'Mike' WHERE id = 123; |
上面的UPDATE语句通过WHERE指定一个条件,否则,UPDATE将更新表中的所有记录的值
百万级别的数据,对于mysql应该没有问题。
你这个sql的问题是,相当于修改ONE表里面所有记录的AGE信息,而修改的过程是,对于每一条ONE里面的记录,去TWO里面查询,再修改。而且,期间很可能会有锁之类的东西。
首先,这种sql不应该出现在业务逻辑里面,而应该是后台的job里面。
如果一定要这么做,可以试着用相反的方式,如果不一样的记录不是特别多,那就找到ONE表里面AGE记录跟TWO表不一样的记录,再修改, 例如大概象下面(可能语法不太对):
代码如下 | |
update ONE,TWO set ONE.AGE=TWO.AGE where ONE.ID=TWO.ID AND ONE.AGE != TWO.AGE |
,当我把数据调到了1000W就更新不了了,下面我来分析原因。
实例:需要根据用户日志的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 语句
代码如下 | |
/* 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天....