程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MYSQL更新優化實錄

MYSQL更新優化實錄

編輯：MySQL綜合教程

MYSQL更新優化實錄。本站提示廣大學習愛好者：（MYSQL更新優化實錄）文章只能為提供參考，不一定能成為您想要的結果。以下是MYSQL更新優化實錄正文

引言

明天（August 5, 2015 5:34 PM）在給數據庫中一張表的構造做一次調劑，添加了幾個字段，前面對之前的數據停止刷新，刷新的內容是：對個中的一個已有字段url停止婚配，然後更新新加的字段type和typeid。後來就寫了個shell劇本來刷數據，成果運轉shell劇本後我就懵了，怎樣這麼慢~~~

情形再現

CREATE TABLE `fuckSpeed` (
 `uin` bigint(20) unsigned NOT NULL DEFAULT 0,
 `id` int(11) unsigned NOT NULL DEFAULT 0,
 `url` varchar(255) NOT NULL DEFAULT '',
 `type` int(11) unsigned NOT NULL DEFAULT 0,
 `typeid` varchar(64) NOT NULL DEFAULT '',
 ......
 KEY `uin_id` (`uin`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表構造年夜概是下面如許的（省略了很多多少字段），表中只要一個結合索引uin_id，而我在更新的時刻是上面的思緒：

起首依據一個id規模獲得到必定數目的數據

select id,url from funkSpeed where id>=101 and id<=200;

遍歷一切的數據，對每條數據停止更新
#起首對數據停止處置，婚配獲得type和typeid

update fuckSpeed set type=[type],typeid=[typeid] where id=[id]

依照下面的思緒弄了以後，發明更新特殊的慢，均勻每秒鐘3~5個閣下，我也是醉了，我看看要更新的數據，總共有32w+條，如許更新上去年夜概須要24h+，也就是1天還要多，額~~哭了，想一想確定是哪裡出成績了。

發明成績

起首我想到的是是否是由於只要一個過程在更新，招致很慢，我啟動了5個過程，將id分段了，就像上面如許

./update_url.sh 0 10000 &
./update_url.sh 10000 20001 &
./update_url.sh 20001 30001 &
./update_url.sh 30002 40002 &
./update_url.sh 40003 50003 &

運轉以後發明照樣那樣，速度沒有晉升若干，照樣每秒鐘更新3~5個閣下，想一想也是啊，時光弗成能消費在拔出數據之前的那些步調（婚配、組裝sql語句、。。。），應當是拔出的時刻有成績

再來看看我的sql語句select id,url from funkSpeed where id>=101 and id<=200;，這裡，試著在敕令行履行了下，成果以下

mysql> select id,url from funkSpeed where id>=0 and id<=200;
Empty set (0.18 sec)

居然花了0.18秒，這個時刻我猜豁然開朗，結合索引我沒有應用到，結合索引失效的前提是——必需要有右邊的字段，用explain驗證下，果真是如許：

mysql> explain id,url from funkSpeed where id>=0 and id<=200;
+-------------+------+---------------+------+---------+------+--------+-------------+
| table    | type | possible_keys | key | key_len | ref | rows  | Extra    |
+-------------+------+---------------+------+---------+------+--------+-------------+
| funkSpeed  | ALL | NULL     | NULL | NULL  | NULL | 324746 | Using where |
+-------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

然後應用結合索引：

mysql> select uin,id from funkSpeed where uin=10023 and id=162;
+------------+----------+
| uin    |  id   |
+------------+----------+
| 10023   | 162   |
+------------+----------+
1 row in set (0.00 sec)

mysql> explain select uin,id from funkSpeed where uin=10023 and id=162;
+-------------+------+---------------+----------+---------+-------------+------+-------------+
| table    | type | possible_keys | key   | key_len | ref     | rows | Extra    |
+-------------+------+---------------+----------+---------+-------------+------+-------------+
| funkSpeed  | ref | uin_id    | uin_id  | 12   | const,const |  4 | Using index |
+-------------+------+---------------+----------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

可以看到簡直是秒查，這個時刻根本可以判斷成績是湧現在索引這個處所了

我select的時刻次數比擬少，每兩個select之間id相差10000，所以這裡可以疏忽失落，並且這裡沒方法優化，除非在id下面添加索引。

成績產生在update fuckSpeed set type=[type],typeid=[typeid] where id=[id]，這裡在更新的時刻也是會用到查詢的，我的mysql版本是5.5，不克不及explain update，否則確定可以驗證我所說的，這裡要更新32w+條數據，每條數據都邑去更新，每條數據0.2s閣下，這太嚇人了~~

處理成績

成績找到了，處理起來就輕易多了~~

select的時刻加了一個字段uin，改成上面如許select uin,id,url from funkSpeed where id>=101 and id<=200;，然後更新的時刻應用update fuckSpeed set type=[type],typeid=[typeid] where uin=[uin] id=[id]，如許一來索引就是用上了。

三下五除二改好了代碼，試著啟動了一個過程，看看後果若何，果真，後果晉升的不是一點點，均勻30+次/s，如許年夜概3個小時閣下便可以完成一切的更新了。

總結Mysql語句級優化：

1. 機能查的讀語句，在innodb中統計行數,建議別的弄一張統計表，采取myisam，按期做統計.普通的對統計的數據不會請求太精准的情形下實用。

2. 盡可能不要在數據庫中做運算。

3. 防止負向查詢和%前綴隱約查詢。

4. 不在索引列做運算或許應用函數。

5. 不要在臨盆情況法式中應用select * from 的情勢查詢數據。只查詢須要應用的列。

6. 查詢盡量應用limit削減前往的行數，削減數據傳輸時光和帶寬糟蹋。

7. where子句盡量對查詢列應用函數，由於對查詢列應用函數用不到索引。

8. 防止隱式類型轉換，例如字符型必定要用''，數字型必定不要應用''。

9. 一切的SQL症結詞用年夜寫，養成優越的習氣，防止SQL語句反復編譯形成體系資本的糟蹋。

10. 聯表查詢的時刻，記得把小成果集放在後面，遵守小成果集驅動年夜成果集的准繩。