程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql update速度慢原因分析與解決辦法

mysql update速度慢原因分析與解決辦法

編輯:MySQL綜合教程

我昨天把數據庫的內容增加到了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表不一樣的記錄,再修改, 例如大概象下面(可能語法不太對):

 代碼如下 復制代碼 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天....

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved