MySQL數(shù)據(jù)庫(kù)優(yōu)化處理實(shí)現(xiàn)千萬(wàn)級(jí)快速分頁(yè)分析,來(lái)看下吧。
數(shù)據(jù)表 collect ( id, title ,info ,vtype) 就這4個(gè)字段,其中 title 用定長(zhǎng),info 用text, id 是逐漸,vtype是tinyint,vtype是索引。這是一個(gè)基本的新聞系統(tǒng)的簡(jiǎn)單模型。現(xiàn)在往里面填充數(shù)據(jù),填充10萬(wàn)篇新聞。
最后collect 為 10萬(wàn)條記錄,數(shù)據(jù)庫(kù)表占用硬盤(pán)1.6G。OK ,看下面這條sql語(yǔ)句:
select id,title from collect limit 1000,10; 很快;基本上0.01秒就OK,再看下面的
select id,title from collect limit 90000,10; 從9萬(wàn)條開(kāi)始分頁(yè),結(jié)果?
8-9秒完成,my god 哪出問(wèn)題了????其實(shí)要優(yōu)化這條數(shù)據(jù),網(wǎng)上找得到答案。看下面一條語(yǔ)句:
select id from collect order by id limit 90000,10; 很快,0.04秒就OK。 為什么?因?yàn)橛昧薸d主鍵做索引當(dāng)然快。網(wǎng)上的改法是:
select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;
這就是用了id做索引的結(jié)果。可是問(wèn)題復(fù)雜那么一點(diǎn)點(diǎn),就完了。看下面的語(yǔ)句
select id from collect where vtype=1 order by id limit 90000,10; 很慢,用了8-9秒!
到了這里我相信很多人會(huì)和我一樣,有崩潰感覺(jué)!vtype 做了索引了啊?怎么會(huì)慢呢?vtype做了索引是不錯(cuò),你直接 select id from collect where vtype=1 limit 1000,10; 是很快的,基本上0.05秒,可是提高90倍,從9萬(wàn)開(kāi)始,那就是0.05*90=4.5秒的速度了。和測(cè)試結(jié)果8-9秒到了一個(gè)數(shù)量級(jí)。從這里開(kāi)始有人 提出了分表的思路,這個(gè)和discuz 論壇是一樣的思路。思路如下:
建一個(gè)索引表: t (id,title,vtype) 并設(shè)置成定長(zhǎng),然后做分頁(yè),分頁(yè)出結(jié)果再到 collect 里面去找info 。 是否可行呢?實(shí)驗(yàn)下就知道了。
10萬(wàn)條記錄到 t(id,title,vtype) 里,數(shù)據(jù)表大小20M左右。用
select id from t where vtype=1 order by id limit 90000,10; 很快了。基本上0.1-0.2秒可以跑完。為什么會(huì)這樣呢?我猜想是因?yàn)閏ollect 數(shù)據(jù)太多,所以分頁(yè)要跑很長(zhǎng)的路。limit 完全和數(shù)據(jù)表的大小有關(guān)的。其實(shí)這樣做還是全表掃描,只是因?yàn)閿?shù)據(jù)量小,只有10萬(wàn)才快。OK, 來(lái)個(gè)瘋狂的實(shí)驗(yàn),加到100萬(wàn)條,測(cè)試性能。
加了10倍的數(shù)據(jù),馬上t表就到了200多M,而且是定長(zhǎng)。還是剛才的查詢語(yǔ)句,時(shí)間是0.1-0.2秒完成!分表性能沒(méi)問(wèn)題?錯(cuò)!因?yàn)槲覀兊膌imit還是9萬(wàn),所以快。給個(gè)大的,90萬(wàn)開(kāi)始
select id from t where vtype=1 order by id limit 900000,10; 看看結(jié)果,時(shí)間是1-2秒!
why ?? 分表了時(shí)間還是這么長(zhǎng),非常之郁悶!有人說(shuō)定長(zhǎng)會(huì)提高limit的性能,開(kāi)始我也以為,因?yàn)橐粭l記錄的長(zhǎng)度是固定的,mysql 應(yīng)該可以算出90萬(wàn)的位置才對(duì)啊? 可是我們高估了mysql 的智能,他不是商務(wù)數(shù)據(jù)庫(kù),事實(shí)證明定長(zhǎng)和非定長(zhǎng)對(duì)limit影響不大? 怪不得有人說(shuō) discuz到了100萬(wàn)條記錄就會(huì)很慢,我相信這是真的,這個(gè)和數(shù)據(jù)庫(kù)設(shè)計(jì)有關(guān)!
難道MySQL 無(wú)法突破100萬(wàn)的限制嗎???到了100萬(wàn)的分頁(yè)就真的到了極限???
答案是: NO !!!! 為什么突破不了100萬(wàn)是因?yàn)椴粫?huì)設(shè)計(jì)mysql造成的。下面介紹非分表法,來(lái)個(gè)瘋狂的測(cè)試!一張表搞定100萬(wàn)記錄,并且10G 數(shù)據(jù)庫(kù),如何快速分頁(yè)!
好了,我們的測(cè)試又回到 collect表,開(kāi)始測(cè)試結(jié)論是: 30萬(wàn)數(shù)據(jù),用分表法可行,超過(guò)30萬(wàn)他的速度會(huì)慢道你無(wú)法忍受!當(dāng)然如果用分表+我這種方法,那是絕對(duì)完美的。但是用了我這種方法后,不用分表也可以完美解決!
答案就是:復(fù)合索引! 有一次設(shè)計(jì)mysql索引的時(shí)候,無(wú)意中發(fā)現(xiàn)索引名字可以任取,可以選擇幾個(gè)字段進(jìn)來(lái),這有什么用呢?開(kāi)始的select id from collect order by id limit 90000,10; 這么快就是因?yàn)樽吡怂饕墒侨绻恿藈here 就不走索引了。抱著試試看的想法加了 search(vtype,id) 這樣的索引。然后測(cè)試
select id from collect where vtype=1 limit 90000,10; 非常快!0.04秒完成!
再測(cè)試: select id ,title from collect where vtype=1 limit 90000,10; 非常遺憾,8-9秒,沒(méi)走search索引!
再測(cè)試:search(id,vtype),還是select id 這個(gè)語(yǔ)句,也非常遺憾,0.5秒。
綜上:如果對(duì)于有where 條件,又想走索引用limit的,必須設(shè)計(jì)一個(gè)索引,將where 放第一位,limit用到的主鍵放第2位,而且只能select 主鍵!
完美解決了分頁(yè)問(wèn)題了。可以快速返回id就有希望優(yōu)化limit , 按這樣的邏輯,百萬(wàn)級(jí)的limit 應(yīng)該在0.0x秒就可以分完。看來(lái)mysql 語(yǔ)句的優(yōu)化和索引時(shí)非常重要的!
好了,回到原題,如何將上面的研究成功快速應(yīng)用于開(kāi)發(fā)呢?如果用復(fù)合查詢,我的輕量級(jí)框架就沒(méi)的用了。分頁(yè)字符串還得自己寫(xiě),那多麻煩?這里再看一個(gè)例子,思路就出來(lái)了:
select * from collect where id in (9000,12,50,7000); 竟然 0秒就可以查完!
mygod ,mysql 的索引竟然對(duì)于in語(yǔ)句同樣有效!看來(lái)網(wǎng)上說(shuō)in無(wú)法用索引是錯(cuò)誤的!
有了這個(gè)結(jié)論,就可以很簡(jiǎn)單的應(yīng)用于輕量級(jí)框架了:
代碼如下:
$db=dblink();
$db->pagesize=20;
$sql="select id from collect where vtype=$vtype";
$db->execute($sql);
$strpage=$db->strpage(); //將分頁(yè)字符串保存在臨時(shí)變量,方便輸出
while($rs=$db->fetch_array()){
$strid.=$rs['id'].',';
}
$strid=substr($strid,0,strlen($strid)-1); //構(gòu)造出id字符串
$db->pagesize=0; //很關(guān)鍵,在不注銷(xiāo)類(lèi)的情況下,將分頁(yè)清空,這樣只需要用一次數(shù)據(jù)庫(kù)連接,不需要再開(kāi);
$db->execute("select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)");
fetch_array()): ?>
作者“ERDP技術(shù)架構(gòu)”
bitsCN.com聲明:本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí),若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系,我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com