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 秒了，还能再优化吗？

经过测试最终还是使用这个方案，即增加一个 id DESC，这样由每次查询时间由 0.5 秒降到 0.02 秒;
索引依旧使用单独的 cid uid time，总体看 100 多万数据 0.02 应该都接受了；
SELECT * FROM `article` WHERE cid=1 AND uid=100 ORDER BY time DESC, id DESC LIMIT 20;

后面还有一个回复表，表已经是 2000 多万数据了，目前也在优化中。。。

为什么第一条那么快呀，没太搞明白

2017-08-03 18:42:56 : Array
(
[sql] => SELECT * FROM `article` WHERE cid=1 OR (cid=2 AND uid IN('62923','1','3461','25823','64582','64587','64588','64589','64590')) OR (cid=5 AND uid=62923) ORDER BY time DESC LIMIT 20
[time] => 0.000885
)

2017-08-03 18:42:58 : Array
(
[sql] => SELECT * FROM `article` WHERE cid=1 AND uid=12091 ORDER BY time DESC, id DESC LIMIT 20
[time] => 0.022885
)

2017-08-03 18:43:58 : Array
(
[sql] => SELECT * FROM `article` WHERE cid=1 AND uid=12091 ORDER BY time DESC LIMIT 20
[time] => 0.49885
)

[xiaobai987]

我感觉直接用 ORDER BY ID 吧 如果 ID 是自增的

[Aluhao]

@xiaobai987 如果只用 ORDER BY id DESC 查询速度和 ORDER BY time DESC 是一样的。

[imnpc]

列表页要显示哪些 请把哪些字段加索引
查询语句只查询需要的字段

[Aluhao]

@imnpc 列表是显示标题和内容，这个 varchar 和 text 不需要加索引吧，整个表也就大概有 10 个字段吧，除了标题和内容其它都是 int 型的数字。

[id4alex]

cid 和 uid 上建一个索引就好了

[sagaxu]

加个索引就行了 (cid,uid,time desc)

[zjsxwc]

explain 看下用了哪些索引。

[sagaxu]

或者用(uid,cid,time desc) 哪个索引更快，看数据分布，多个 and 条件，把分布更稀疏的排前面更好

[RuzZ]

如果是单列的索引，可以添加联合索引试试看。explain 下查询，看看实际上使用的索引情况

[cnwtex]

把内容挪到另外一个表，用 id 关联起来，会提速百-千倍左右

[Aluhao]

@zjsxwc

已经建立的索引

PRIMARY KEY (`id`),
KEY `uid` (`uid`),
KEY `cid` (`cid`),
KEY `time` (`time`)

[Aluhao]

@RuzZ 联合索引 (uid,cid,time) 也试过了，还是一样很慢

[Aluhao]

@id4alex 那二个都建立过索引了，主要是这个影响到了，加了 ORDER BY time DESC 就是全表扫描了

[Aluhao]

@cnwtex 这个可以考虑呀

[sagaxu]

@Aluhao 贴 explain SELECT * FROM `article` WHERE cid=1 AND uid=100 ORDER BY time DESC LIMIT 20; 的结果

[noNOno]

select * from (SELECT * FROM `article` WHERE cid=1 AND uid=100) t ORDER BY time DESC LIMIT 20;
放到子查询里走索引么

[wayne712]

order by time, 如果 time 没有索引则会拖慢查询 , cid,uid 设置组合索引

[Aluhao]

@sagaxu 你的方法我测试了，可以优化一些 (cid,uid,time) desc 新建一个字段 desc 我以前是直接用 time 所以没效果

[Aluhao]

@wayne712 time 有建立索引的

[sagaxu]

@Aluhao desc 是让你建索引的时候按照 time 降序，order by time desc 的时候可以提高些许性能，不是增加一个 desc 字段

[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

[gouchaoer]

让你贴 explain 你就贴啊，这么多人帮你看墨迹啥

[akira]

SELECT * FROM 换成 select id , time 呢

[Aluhao]

@gouchaoer 这里不能传图片呀

[frye]

@Aluhao MySQL 可以用 FORCE INDEX 语句指定使用哪个索引，另外如果 uid 比 cid 的离散度高的话，建立索引的时候建议把 uid 放在前面 CREATE INDEX index_name ON article (uid, cid, time DESC)，另外如果你建立了 uid, cid, time DESC 的索引的话，就完全没有必要再单独给 uid 建立索引了。

[Aluhao]

@frye 好的，谢谢

[simaguo]

unique (cid,uid,time desc)?

[debye]

索引也不是万能的
还有索引多了也有牺牲

[Aluhao]

@simaguo CID 是分类 UID 是用户 ID TIME 是时间

[orzfly]

@Aluhao #43 觉得“发不了图”为什么不早点说呢？

https://www.v2ex.com/t/130567?p=1

[dikT]

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

[yanze0613]

ORDER BY time DESC LIMIT 20
会导致扫描大量数据，就很麻烦

[joeke]

@orzfly 为什么不早说，哈哈

[Aluhao]

@dikT 对，用 IN 取数据也快