《MYSQL教程MYSQL更新優(yōu)化實(shí)錄》要點(diǎn)：
本文介紹了MYSQL教程MYSQL更新優(yōu)化實(shí)錄，希望對您有用。如果有疑問，可以聯(lián)系我們。

MYSQL應(yīng)用弁言

MYSQL應(yīng)用今天(August 5, 2015 5:34 PM)在給數(shù)據(jù)庫中一張表的結(jié)構(gòu)做一次調(diào)整,添加了幾個字段,后面對之前的數(shù)據(jù)進(jìn)行刷新,刷新的內(nèi)容是：對其中的一個已有字段url進(jìn)行匹配,然后更新新加的字段type和typeid.后來就寫了個shell腳原來刷數(shù)據(jù),結(jié)果運(yùn)行shell腳本后我就懵了,怎么這么慢~~~

MYSQL應(yīng)用情景再現(xiàn)

MYSQL應(yīng)用
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;

MYSQL應(yīng)用表布局大概是上面這樣的(省略了好多字段),表中只有一個聯(lián)合索引uin_id,而我在更新的時候是下面的思路：

MYSQL應(yīng)用首先根據(jù)一個id范圍獲取到必定數(shù)量的數(shù)據(jù)

MYSQL應(yīng)用
select id,url from funkSpeed where id>=101 and id<=200;

MYSQL應(yīng)用遍歷所有的數(shù)據(jù),對每一條數(shù)據(jù)進(jìn)行更新
#首先對數(shù)據(jù)進(jìn)行處置,匹配獲取type和typeid

MYSQL應(yīng)用
update fuckSpeed set type=[type],typeid=[typeid] where id=[id]

MYSQL應(yīng)用依照上面的思路搞了之后,發(fā)現(xiàn)更新特別的慢,平均每秒鐘3~5個左右,我也是醉了,我看看要更新的數(shù)據(jù),總共有32w+條,這樣更新下來大概需要24h+,也就是1天還要多,額~~哭了,想想肯定是哪里出問題了.

MYSQL應(yīng)用發(fā)現(xiàn)問題

MYSQL應(yīng)用首先我想到的是是不是因?yàn)橹挥幸粋€進(jìn)程在更新,導(dǎo)致很慢,我啟動了5個進(jìn)程,將id分段了,就像下面這樣

MYSQL應(yīng)用
./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 &

MYSQL應(yīng)用運(yùn)行之后發(fā)現(xiàn)還是那樣,速度沒有提升多少,還是每秒鐘更新3~5個左右,想想也是啊,時間弗成能花費(fèi)在插入數(shù)據(jù)之前的那些步驟(匹配、組裝sql語句、...),應(yīng)該是插入的時候有問題

MYSQL應(yīng)用再來看看我的sql語句select id,url from funkSpeed where id>=101 and id<=200;,這里,試著在命令行執(zhí)行了下,成果如下

MYSQL應(yīng)用
mysql> select id,url from funkSpeed where id>=0 and id<=200;
Empty set (0.18 sec)

MYSQL應(yīng)用竟然花了0.18秒,這個時候我猜恍然大悟,聯(lián)合索引我沒有使用到,聯(lián)合索引生效的條件是――必需要有左邊的字段,用explain驗(yàn)證下,果然是這樣：

MYSQL應(yīng)用
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應(yīng)用然后使用聯(lián)合索引：

MYSQL應(yīng)用
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)

MYSQL應(yīng)用可以看到幾乎是秒查,這個時候基本可以斷定問題是呈現(xiàn)在索引這個地方了

MYSQL應(yīng)用我select的時候次數(shù)比較少,每兩個select之間id相差10000,所以這里可以忽略掉,而且這里沒方法優(yōu)化,除非在id上面添加索引.

MYSQL應(yīng)用問題發(fā)生在update fuckSpeed set type=[type],typeid=[typeid] where id=[id],這里在更新的時候也是會用到查詢的,我的mysql版本是5.5,不克不及explain update,不然肯定可以驗(yàn)證我所說的,這里要更新32w+條數(shù)據(jù),每條數(shù)據(jù)都會去更新,每條數(shù)據(jù)0.2s左右,這太嚇人了~~

MYSQL應(yīng)用辦理問題

MYSQL應(yīng)用問題找到了,辦理起來就容易多了~~

MYSQL應(yīng)用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],這樣一來索引便是用上了.

MYSQL應(yīng)用三下五除二改好了代碼,試著啟動了一個進(jìn)程,看看效果如何,果然,效果提升的不是一點(diǎn)點(diǎn),平均30+次/s,這樣年夜概3個小時左右就可以完成所有的更新了.

MYSQL應(yīng)用總結(jié)Mysql語句級優(yōu)化：

MYSQL應(yīng)用1.?? 性能查的讀語句,在innodb中統(tǒng)計(jì)行數(shù),建議另外弄一張統(tǒng)計(jì)表,采納myisam,定期做統(tǒng)計(jì).一般的對統(tǒng)計(jì)的數(shù)據(jù)不會要求太精準(zhǔn)的情況下適用.

MYSQL應(yīng)用2.?? 盡量不要在數(shù)據(jù)庫中做運(yùn)算.

MYSQL應(yīng)用3.?? 避免負(fù)向查詢和%前綴模糊查詢.

MYSQL應(yīng)用4.?? 不在索引列做運(yùn)算或者使用函數(shù).

MYSQL應(yīng)用5.?? 不要在生產(chǎn)環(huán)境程序中使用select * from 的形式查詢數(shù)據(jù).只查詢必要使用的列.

MYSQL應(yīng)用6.?? 查詢盡可能使用limit減少返回的行數(shù),減少數(shù)據(jù)傳輸時間和帶寬浪費(fèi).

MYSQL應(yīng)用7.?? where子句盡可能對查詢列使用函數(shù),因?yàn)閷Σ樵兞惺褂煤瘮?shù)用不到索引.

MYSQL應(yīng)用8.?? 避免隱式類型轉(zhuǎn)換,例如字符型必定要用'',數(shù)字型必定不要使用''.

MYSQL應(yīng)用9.?? 所有的SQL關(guān)鍵詞用年夜寫,養(yǎng)成良好的習(xí)慣,避免SQL語句重復(fù)編譯造成系統(tǒng)資源的浪費(fèi).

MYSQL應(yīng)用10. 聯(lián)表查詢的時候,記得把小成果集放在前面,遵循小成果集驅(qū)動大成果集的原則.

MYSQL應(yīng)用11. 開啟慢查詢,按期用explain優(yōu)化慢查詢中的SQL語句.

《MYSQL教程MYSQL更新優(yōu)化實(shí)錄》是否對您有啟發(fā)，歡迎查看更多與《MYSQL教程MYSQL更新優(yōu)化實(shí)錄》相關(guān)教程，學(xué)精學(xué)透。維易PHP學(xué)院為您提供精彩教程。

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/13989.html