mysql update速度慢原因分析与解决办法

时间：2013-06-03 编辑：简简单单来源：一聚教程网

我们先来看看关于update语法

UPDATE

UPDATE的功能是更新表中的数据。这的语法和INSERT的第二种用法相似。必须提供表名以及SET表达式，在后面可以加WHERE以限制更新的记录范围。

代码如下	复制代码
UPDATE table_anem SET column_name1 = value1, column_name2 = value2, WHERE ;

如下面的语句将users表中id等于123的记录的age改为24

代码如下	复制代码
UPDATE users SET age = 24 WHERE id = 123;

同样，可以使用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 语句

代码如下

复制代码

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天....

上一个： mysql分表实现方法详解
下一个： mssql server数据库转换为MySQL详解

推荐专题

最新下载

热门教程

mysql update速度慢原因分析与解决办法

相关文章

热门栏目

php教程

asp.net教程

手机开发

css教程

网页制作

办公数码

jsp教程