20萬條數(shù)據(jù)，超過1G的數(shù)據(jù)庫該怎么優(yōu)化？

z9web

一個(gè)文章庫，里面有兩個(gè)表：category和article。category里面有10條分類數(shù)據(jù)。article里面有20萬條。article里面有一個(gè)"article_category"字段是與category里的"category_id"字段相對應(yīng)的。article表里面已經(jīng)把a(bǔ)rticle_category字義為了索引。數(shù)據(jù)庫大小為1.3G。
現(xiàn)在執(zhí)行一個(gè)很普通的查詢： SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5 。執(zhí)行時(shí)間大約要5秒左右。如果要分別取出10個(gè)分類中的數(shù)據(jù)，估計(jì)要一分鐘的時(shí)間。感覺太慢了，求助于各位朋友，望指點(diǎn)和交流一下經(jīng)驗(yàn)。感激不盡。

hongsejiaozhu

就是說取出每個(gè)類別的前五名？？沒有玩過這么多的記錄，關(guān)注。頂

z9web

估計(jì)都還沒起床，都是夜貓一族的。
事情緊急，臨時(shí)弄了一個(gè)方法，效果還不錯(cuò)。寫下來跟各位切磋一下。

我感覺得是單表的體積太大了，決定試試把條件查詢的字段提出來另外做一個(gè)表。于是新建了一個(gè)"article_to_category"的表，這個(gè)表就兩個(gè)字段：article_id和category_id。給category_id加了一個(gè)索引。改造后的SQL語句如下：

SELECT * FROM `article_to_category` a2c
LEFT JOIN article a ON a.article_id = a2c.article_id
WHERE a2c.category_id =9
ORDER BY a2c.article_id DESC
LIMIT 5

查詢時(shí)間由先前的5秒多降低到0.0864秒。應(yīng)該算是一個(gè)不錯(cuò)的成績了。

先用這個(gè)頂著了。希望有更多朋友的經(jīng)驗(yàn)交流。

yueliangdao0608

SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5



建一個(gè)索引吧。
create index idx_u on article (article_category,article_id);

z9web

原帖由 yueliangdao0608 于 2008-7-1 10:01 發(fā)表
SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5



建一個(gè)索引吧。
create index idx_u on article (article_category,article_id);

剛剛測試了一下，這個(gè)方法不錯(cuò)，簡單高效，執(zhí)行效率減少到0.0027秒。不過怎么避免"IN"這個(gè)操作呢？

如：SELECT * FROM `article` WHERE article_category IN (2,3) ORDER BY article_id DESC LIMIT 5
執(zhí)行時(shí)間要11.2850秒。

yueliangdao0608

原帖由 z9web 于 2008-7-1 10:36 發(fā)表


剛剛測試了一下，這個(gè)方法不錯(cuò)，簡單高效，執(zhí)行效率減少到0.0027秒。不過怎么避免"IN"這個(gè)操作呢？

如：SELECT * FROM `article` WHERE article_category IN (2,3) ORDER BY article_id DESC LIMIT 5
...

改成OR或者用UNION ALL吧。

z9web

使用OR:
select * from article
where article_category=2
or article_category=3
order by article_id desc
limit 5
執(zhí)行時(shí)間：11.0777

使用UNION ALL：
(select * from article where article_category=2 order by article_id desc limit 5)
UNION ALL (select * from article where article_category=3 order by article_id desc limit 5)
ORDER BY article_id desc
limit 5
執(zhí)行時(shí)間：0.0261

是不是我的OR寫法有問題？

sunnyfun

用or會導(dǎo)致掃表

yueliangdao0608

原帖由 sunnyfun 于 2008-7-2 09:55 發(fā)表
用or會導(dǎo)致掃表

SUNNYFUN的SQL是非常強(qiáng)悍的。

ydlhero

值得學(xué)習(xí)