mysql查询大容量数据的优化经验

渡人自渡

查询大容量数据的优化经验:
先查到id,然后查询相关的数据...
比如这个查询在1M条记录,1.5g数据库内存情况下相当慢,大概20s以上
select id,title from articles order by rank desc limit 12222,34;
但是拆分成如下查询只要2秒:
select title from articles where id in (select * from (select id from articles order by rank desc limit 312212,34 ) as b);
优化成这个样子只要1秒:
select title from articles as a,(select id from articles order by rank desc limit 512212,34 ) as b where a.id=b.id;

表现在数据库表设计上应该就是拆分表,多用小表做查询,查到后再去查存数据的大表


--------------
chenjinlai
2008-04-15