一、UPDATE常見(jiàn)用法
首先建立測(cè)試環(huán)境：
 

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (
 bs bigint(20) NOT NULL auto_increment,
 username varchar(20) NOT NULL,
 password varchar(20) default NULL,
 remark varchar(200) default NULL,
 PRIMARY KEY (bs)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;
 
 
INSERT INTO t_test VALUES (1,'lavasoft','123456',NULL);
INSERT INTO t_test VALUES (2,'hello',NULL,NULL);
INSERT INTO t_test VALUES (3,'haha',zz,tt);

 
1、set一個(gè)字段
在表t_test中設(shè)置第二條記錄（bs為2）的password為'***'。

update t_test t 
  set t.password = '***' 
 where t.bs = 2;

 
2、set多個(gè)字段
在表t_test中設(shè)置第一條記錄（bs為1）的password為'*'、remark為'*'。

update t_test t 
  set t.password = '*', t.remark = '*' 
 where t.bs = 1;

 
3、set null值
在表t_test中設(shè)置第三條記錄（bs為3）的password為null、remark為null。

update t_test t 
  set t.password = null, t.remark = null 
 where t.bs = 3;

 
這個(gè)是按照標(biāo)準(zhǔn)語(yǔ)法寫(xiě)的，在不同的數(shù)據(jù)庫(kù)系統(tǒng)中，update還有更多的寫(xiě)法，但是標(biāo)準(zhǔn)寫(xiě)法都是支持的。以上三個(gè)例子為了說(shuō)明情況，每次都更新一行。在實(shí)際中，可以通過(guò)where語(yǔ)句約束來(lái)控制更新行數(shù)。

二、UPDATE使用中的相關(guān)性能問(wèn)題以及解決方法
UPDATE的功能是更新表中的數(shù)據(jù)。這的語(yǔ)法和INSERT的第二種用法相似。必須提供表名以及SET表達(dá)式，在后面可以加WHERE以限制更新的記錄范圍。

UPDATE table_anem SET column_name1 = value1, column_name2 = value2,  WHERE ;

如下面的語(yǔ)句將users表中id等于123的記錄的age改為24

UPDATE users SET age = 24 WHERE id = 123;

同樣，可以使用UPDATE更新多個(gè)字段的值

UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;

上面的UPDATE語(yǔ)句通過(guò)WHERE指定一個(gè)條件，否則，UPDATE將更新表中的所有記錄的值
百萬(wàn)級(jí)別的數(shù)據(jù)，對(duì)于mysql應(yīng)該沒(méi)有問(wèn)題。

你這個(gè)sql的問(wèn)題是，相當(dāng)于修改ONE表里面所有記錄的AGE信息，而修改的過(guò)程是，對(duì)于每一條ONE里面的記錄，去TWO里面查詢，再修改。而且，期間很可能會(huì)有鎖之類(lèi)的東西。
首先，這種sql不應(yīng)該出現(xiàn)在業(yè)務(wù)邏輯里面，而應(yīng)該是后臺(tái)的job里面。
如果一定要這么做，可以試著用相反的方式，如果不一樣的記錄不是特別多，那就找到ONE表里面AGE記錄跟TWO表不一樣的記錄，再修改, 例如大概象下面（可能語(yǔ)法不太對(duì)）：

update ONE，TWO 
set ONE.AGE=TWO.AGE 
where ONE.ID=TWO.ID AND ONE.AGE != TWO.AGE 

當(dāng)我把數(shù)據(jù)調(diào)到了1000W就更新不了了，下面我來(lái)分析原因。
實(shí)例：需要根據(jù)用戶日志的ip地址計(jì)算出其地理地址
表結(jié)構(gòu)：
用戶日志表（200萬(wàn)條記錄），其中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地址數(shù)據(jù)庫(kù)表（44萬(wàn)條記錄）

CREATE TABLE `ip` (
`s` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '開(kāi)始ip',
`e` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '結(jié)束ip',
`a` varchar(50) NOT NULL DEFAULT '',
KEY `s` (`s`),
KEY `e` (`e`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

需要根據(jù)用戶日志表 tmp_open_ip 里的 ip字段到ip地址數(shù)據(jù)庫(kù)表里查詢出對(duì)應(yīng)的地理地址，將地址填充到address字段。
使用如下update語(yǔ)句執(zhí)行：

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

在筆者的電腦上運(yùn)行了速度非常之慢，執(zhí)行了一個(gè)多小時(shí)（4500s）都沒(méi)有完，也不知道還要多久。
實(shí)在看不過(guò)去，于是想到使用insert 是否會(huì)快一些，于是重新導(dǎo)一張表 tmp_open_log 與tmp_open_log完全一致。
創(chuàng)建一張表 tmp_open_address，是insert的目標(biāo)表，為了速度更快，沒(méi)建索引：

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

執(zhí)行insert 語(yǔ)句

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！本來(lái)還想去倒杯水、稍事休息一下，結(jié)果已經(jīng)執(zhí)行完畢。

到本文寫(xiě)完時(shí)，前面的update語(yǔ)句已經(jīng)執(zhí)行了5000s，結(jié)束仍是遙遙無(wú)期。
所以，對(duì)于大數(shù)據(jù)量執(zhí)行update時(shí)，可以考慮改用insert 語(yǔ)句實(shí)現(xiàn)，可能麻煩一些，但高速帶來(lái)的收益遠(yuǎn)大于麻煩！
后記：
直接殺死了update進(jìn)程，去看看update執(zhí)行了多少：運(yùn)行

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

結(jié)果只有 11,373 ，照這個(gè)速度，要運(yùn)行N天....

最新評(píng)論