MYSQL 查询优化

[Aluhao]

表 article 有 100 多万主题内容
cid 是分类，建立索引
uid 是用户 ID，建立索引
time 是发布时间，建立了索引
表内还有标题和内容及其它杂项字段

SELECT * FROM `article` WHERE cid=1 AND uid=100 ORDER BY time DESC LIMIT 20;

查询时间需要 0.5 秒，如果不加 ORDER BY time DESC 只需要 0.08 秒；
如果不加 ORDER BY time DESC 又达不到业务需求，我需要查询的是用户最新的主题内容，除了这个方法还有没其它更优化的查询方法？

还有就是当翻页更多的时候更慢 LIMIT 100, 20

我再经过优化了下语句：

SELECT * FROM `aws_article` WHERE cid=1 AND uid=100 ORDER BY time DESC, id DESC LIMIT 20;

增加了 id DESC 查询时间减少到 0.2 秒了，还能再优化吗？

[Aluhao]

@noNOno 你这个没有效果呀，反而多一次查询

[id4alex]

KEY `uid` (`uid`),
KEY `cid` (`cid`),
KEY `time` (`time`)
按道理说这种索引都不需要建立， 没卵用。


你建立联合索引（ cid，uid ）跑下 explain 就一清二楚了。

[Aluhao]

@sagaxu 如果不建立一个字段查询出错呀？

查询出错 (1054): Unknown column 'desc' in 'order clause'

[Aluhao]

@id4alex 我试过了，cid 和 uid 不建索引 几十秒查不出来，你要看有 WHERE cid=1 AND uid=12091
还是有用的。

[Aluhao]

@wayne712 cid,uid,time 都有单独建立索引

[RuzZ]

你的 explain 结果贴一下

[Aluhao]

@RuzZ 现在按 @sagaxu 的方法新建立一个字段 displayorder 用它来做联合索引(cid,uid,time)

SELECT * FROM `article` WHERE cid=1 AND uid=100 ORDER BY displayorder DESC LIMIT 20;

查询时间优化到 0.020 秒了

[liprais]

先贴下加了联合索引的执行计划

[Aluhao]

@liprais 结果在上面 优化到 0.020 秒 了

[frye]

@Aluhao MySQL 的一个 Query 只能用一条索引，所以你分别建索引是没有用的，MySQL 只会用其中一条索引。就像大家说的，你建一个 cid,uid,time 的联合索引就好了，多多仔细深入思考下。然后还得 explain 一下，看看用对了索引没有。

[phx13ye]

人家让你这样建索引，把单条索引删掉，睿智

CREATE INDEX index_name ON article (cid, uid, time DESC)

[Aluhao]

@frye 我有测试的，如果把单独索引去了，查询也是很慢，因为查询的时候除了排序还有条件，如：
WHERE cid=1 AND uid=12091

[Aluhao]

@phx13ye 我试试先

[frye]

@Aluhao 如果你做了 @phx13ye 31 楼的操作，并且用到了这个索引，剩下能够优化的就只有字段类型了。如果还是很慢的话，就只能升级机器配置，或者考虑其他的数据库了。

[daye]

索引是有使用顺序的，建了联合索引之后，请删除其他索引，确保正确性

[zhx1991]

大体上讲 uid, time 的索引是没问题的

但是要看区分度, 你索引建完 explain 一下吧. 如果 cid 是分类我猜区分度巨低, 删了吧.

[frye]

@Aluhao 在我的印象中，MySQL 优化好，做你这样的 Query 的话，查询一个亿的数据，还是可以的。

[Aluhao]

@phx13ye 删除其它单条索引不行，我有其它地方专门针对 cid 和 uid 查的。

[Aluhao]

@frye 谢谢指示

[phx13ye]

@Aluhao (cid, uid, time DESC)对 cid 应该也是有用的，uid 可能需要单独建，具体还是看 explain