MYSQL索引优化, IN OR 等优化措施

一个文章库，里面有两个表：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秒左右

解决方案：

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

select * from article where article_category=11 order by article_id desc limit 5  减少到0.0027秒.

 

又有问题：

1) select * from article`where article_category IN (2, 3) order by article_id desc limit 5, 执行时间要11.2850秒。
2) 换成OR, select * from article where article_category=2 or article_category=3 order by article_id desc limit 5, 执行时间：11.0777  

上面这两个问题的时间基本差不多。

优化方案： 避免使用 in 或者 or ( or会导致扫表)，使用 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

从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL

很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists ( select num from b where num = a.num )