MYSQL 查询优化

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

Supplement 1 · Aug 3, 2017

经过测试最终还是使用这个方案，即增加一个 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 多万数据了，目前也在优化中。。。

Supplement 2 · Aug 3, 2017

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

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
)

53 replies • 2017-08-06 11:11:06 +08:00

laoli123

Aug 3, 2017

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

Alucns

Aug 3, 2017

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

imnpc

Aug 3, 2017

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

Alucns

Aug 3, 2017

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

id4alex

Aug 3, 2017

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

sagaxu

Aug 3, 2017

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

zjsxwc

Aug 3, 2017

explain 看下用了哪些索引。

sagaxu

Aug 3, 2017

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

RuzZ

Aug 3, 2017

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

cnwtex

Aug 3, 2017

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

Alucns

Aug 3, 2017

@zjsxwc

已经建立的索引

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

Alucns

Aug 3, 2017

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

Alucns

Aug 3, 2017

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

Alucns

Aug 3, 2017

@cnwtex 这个可以考虑呀

sagaxu

Aug 3, 2017

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

noNOno

Aug 3, 2017

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

wayne712

Aug 3, 2017

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

Alucns

Aug 3, 2017

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

Alucns

Aug 3, 2017

@wayne712 time 有建立索引的

sagaxu

Aug 3, 2017

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

Alucns

Aug 3, 2017

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

id4alex

Aug 3, 2017

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

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

Alucns

Aug 3, 2017

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

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

Alucns

Aug 3, 2017

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

Alucns

Aug 3, 2017

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

RuzZ

Aug 3, 2017

你的 explain 结果贴一下

Alucns

Aug 3, 2017

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

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

查询时间优化到 0.020 秒了

F281M6Dh8DXpD1g2

Aug 3, 2017

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

Alucns

Aug 3, 2017

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

frye

Aug 3, 2017

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

phx13ye

Aug 3, 2017

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

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

Alucns

Aug 3, 2017

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

Alucns

Aug 3, 2017

@phx13ye 我试试先

frye

Aug 3, 2017

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

daye

Aug 3, 2017

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

zhx1991

Aug 3, 2017

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

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

frye

Aug 3, 2017

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

Alucns

Aug 3, 2017

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

Alucns

Aug 3, 2017

@frye 谢谢指示

phx13ye

Aug 3, 2017

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

gouchaoer

Aug 3, 2017 via Android

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

akira

Aug 3, 2017

SELECT * FROM 换成 select id , time 呢

Alucns

Aug 3, 2017

@gouchaoer 这里不能传图片呀

frye

Aug 3, 2017

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

Alucns

Aug 3, 2017

@frye 好的，谢谢