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

第 1 条附言 · 2017-08-03 18:08:36 +08:00

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

第 2 条附言 · 2017-08-03 19:05:39 +08:00

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

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 条回复 • 2017-08-06 11:11:06 +08:00

xiaobai987

2017-08-03 16:21:48 +08:00

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

Aluhao

2017-08-03 16:23:19 +08:00

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

imnpc

2017-08-03 16:25:30 +08:00

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

Aluhao

2017-08-03 16:29:13 +08:00

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

id4alex

2017-08-03 16:29:38 +08:00

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

sagaxu

2017-08-03 16:32:14 +08:00

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

zjsxwc

2017-08-03 16:33:19 +08:00

explain 看下用了哪些索引。

sagaxu

2017-08-03 16:34:27 +08:00

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

RuzZ

2017-08-03 16:36:13 +08:00

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

cnwtex

2017-08-03 16:37:34 +08:00

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

Aluhao

2017-08-03 16:39:34 +08:00

@zjsxwc

已经建立的索引

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

Aluhao

2017-08-03 16:40:12 +08:00

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

Aluhao

2017-08-03 16:41:08 +08:00

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

Aluhao

2017-08-03 16:41:44 +08:00

@cnwtex 这个可以考虑呀

sagaxu

2017-08-03 16:42:23 +08:00

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

noNOno

2017-08-03 16:45:38 +08:00

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

wayne712

2017-08-03 16:46:01 +08:00

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

Aluhao

2017-08-03 16:49:52 +08:00

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

Aluhao

2017-08-03 16:50:21 +08:00

@wayne712 time 有建立索引的

sagaxu

2017-08-03 16:51:36 +08:00

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

Aluhao

2017-08-03 16:51:46 +08:00

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

id4alex

2017-08-03 16:54:12 +08:00

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

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

Aluhao

2017-08-03 16:54:54 +08:00

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

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

Aluhao

2017-08-03 16:56:53 +08:00

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

Aluhao

2017-08-03 16:58:33 +08:00

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

RuzZ

2017-08-03 16:59:15 +08:00

你的 explain 结果贴一下

Aluhao

2017-08-03 17:02:43 +08:00

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

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

查询时间优化到 0.020 秒了

liprais

2017-08-03 17:03:13 +08:00

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

Aluhao

2017-08-03 17:03:47 +08:00

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

frye

2017-08-03 17:04:05 +08:00

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

phx13ye

2017-08-03 17:04:21 +08:00

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

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

Aluhao

2017-08-03 17:06:23 +08:00

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

Aluhao

2017-08-03 17:07:00 +08:00

@phx13ye 我试试先

frye

2017-08-03 17:08:45 +08:00

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

daye

2017-08-03 17:08:54 +08:00

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

zhx1991

2017-08-03 17:09:54 +08:00

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

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

frye

2017-08-03 17:10:14 +08:00

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

Aluhao

2017-08-03 17:11:10 +08:00

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

Aluhao

2017-08-03 17:13:38 +08:00

@frye 谢谢指示

phx13ye

2017-08-03 17:14:17 +08:00

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

gouchaoer

2017-08-03 17:15:11 +08:00 via Android

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

akira

2017-08-03 17:15:41 +08:00

SELECT * FROM 换成 select id , time 呢

Aluhao

2017-08-03 17:16:13 +08:00

@gouchaoer 这里不能传图片呀

frye

2017-08-03 17:16:26 +08:00

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

Aluhao

2017-08-03 17:18:52 +08:00

@frye 好的，谢谢