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表不一樣的記錄，再修改, 例如大概象下面（可能文法不太對）：

代碼如下	複製代碼
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天....