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

我昨天把数据库的内容增加到了10W万记录,发现数据库利用update更新相当的慢了,利用命令查看mysql状态N多条等待的信息,下面我来给大家介绍我如何解决mysql对大表执行update速度慢办法,有碰到此类的问题的朋友可参考.

我们先来看看关于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表不一样的记录,再修改,例如大概象下面,可能语法不太对,代码如下:

1. updateONE，TWO
2. setONE.AGE=TWO.AGE
3. whereONE.ID=TWO.IDANDONE.AGE!=TWO.AGE

当我把数据调到了1000W就更新不了了,下面我来分析原因.

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

表结构:用户日志表,200万条记录,其中address是待填充的字段,代码如下:

1. CREATETABLE`tmp_open_ip`(
2. `email`varchar(60)NOTNULLDEFAULT'',
3. `address`varchar(50)NOTNULLDEFAULT'',
4. `ip`int(10)unsignedNOTNULLDEFAULT'0',
5. KEY`email`(`email`),--phpfensi.com
6. KEY`ip`(`ip`)
7. )ENGINE=MyISAMDEFAULTCHARSET=utf8

另ip地址数据库表,44万条记录,代码如下:

1. CREATETABLE`ip`(
2. `s`int(10)unsignedNOTNULLDEFAULT'0'COMMENT'开始ip',
3. `e`int(10)unsignedNOTNULLDEFAULT'0'COMMENT'结束ip',
4. `a`varchar(50)NOTNULLDEFAULT'',
5. KEY`s`(`s`),
6. KEY`e`(`e`)
7. )ENGINE=MyISAMDEFAULTCHARSET=utf8

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

使用如下update语句执行:

1. UPDATEtmp_open_ipASu
2. INNERJOINip
3. ONu.ipBETWEENip.sANDip.e
4. SETu.address=ip.a

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

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

创建一张表 tmp_open_address,是insert的目标表,为了速度更快,没建索引,代码如下:

1. CREATETABLE`tmp_open_address`(
2. `email`varchar(60)NOTNULLDEFAULT'',
3. `address`varchar(50)NOTNULLDEFAULT'',
4. `ip`int(10)unsignedNOTNULLDEFAULT'0'
5. )ENGINE=MyISAMDEFAULTCHARSET=utf8

执行insert 语句,代码如下:

1. insertintotmp_open_address(email,address,ip)
2. selectl.email,ip.a,l.ip
3. fromtmp_open_logaslinnerjoiniponl.ipbetweenip.sandip.e;
4. /*Affectedrows:2,543,124Foundrows:0Warnings:0Durationfor3queries:16.922sec.*/

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

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

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

后记:直接杀死了update进程,去看看update执行了多少,运行:

SELECT * FROM `tmp_open_ip` where address!=''

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