20万条数据，超过1G的数据库该怎么优化？

z9web

一个文章库，里面有两个表：category和article。category里面有10条分类数据。article里面有20万条。article里面有一个"article_category"字段是与category里的"category_id"字段相对应的。article表里面已经把article_category字义为了索引。数据库大小为1.3G。
现在执行一个很普通的查询： SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5 。执行时间大约要5秒左右。如果要分别取出10个分类中的数据，估计要一分钟的时间。感觉太慢了，求助于各位朋友，望指点和交流一下经验。感激不尽。

hongsejiaozhu

就是说取出每个类别的前五名？？没有玩过这么多的记录，关注。顶

z9web

估计都还没起床，都是夜猫一族的。
事情紧急，临时弄了一个方法，效果还不错。写下来跟各位切磋一下。

我感觉得是单表的体积太大了，决定试试把条件查询的字段提出来另外做一个表。于是新建了一个"article_to_category"的表，这个表就两个字段：article_id和category_id。给category_id加了一个索引。改造后的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

查询时间由先前的5秒多降低到0.0864秒。应该算是一个不错的成绩了。

先用这个顶着了。希望有更多朋友的经验交流。

yueliangdao0608

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



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

z9web

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



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

刚刚测试了一下，这个方法不错，简单高效，执行效率减少到0.0027秒。不过怎么避免"IN"这个操作呢？

如：SELECT * FROM `article` WHERE article_category IN (2,3) ORDER BY article_id DESC LIMIT 5
执行时间要11.2850秒。

yueliangdao0608

原帖由 z9web 于 2008-7-1 10:36 发表


刚刚测试了一下，这个方法不错，简单高效，执行效率减少到0.0027秒。不过怎么避免"IN"这个操作呢？

如：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
执行时间：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
执行时间：0.0261

是不是我的OR写法有问题？

sunnyfun

用or会导致扫表

yueliangdao0608

原帖由 sunnyfun 于 2008-7-2 09:55 发表
用or会导致扫表

SUNNYFUN的SQL是非常强悍的。

ydlhero

值得学习