V2EX = way to explore V2EX 是一个关于分享和探索的地方
Sign Up Now For Existing Member Sign In
MySQL 5.5 Community Server MySQL 5.6 Community Server Percona Configuration Wizard XtraBackup 搭建主从复制 Great Sites on MySQL Percona MySQL Performance Blog Severalnines 推荐管理工具 Sequel Pro phpMyAdmin 推荐书目 MySQL Cookbook
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html 
This topic created in 3191 days ago, the information mentioned may be changed or developed.
表 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 秒了，还能再优化吗？
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 多万数据了，目前也在优化中。。。
索引依旧使用单独的 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
)
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
)
 | 1 laoli123 Aug 3, 2017 我感觉直接用 ORDER BY ID 吧 如果 ID 是自增的 |
 | 2 Alucns OP @xiaobai987 如果只用 ORDER BY id DESC 查询速度和 ORDER BY time DESC 是一样的。 |
 | 3 imnpc Aug 3, 2017 列表页要显示哪些 请把哪些字段加索引 查询语句只查询需要的字段 |
| 4 Alucns OP @imnpc 列表是显示标题和内容，这个 varchar 和 text 不需要加索引吧，整个表也就大概有 10 个字段吧，除了标题和内容其它都是 int 型的数字。 |
 | 5 id4alex Aug 3, 2017 cid 和 uid 上建一个索引就好了 |
 | 6 sagaxu Aug 3, 2017 加个索引就行了 (cid,uid,time desc) |
 | 7 zjsxwc Aug 3, 2017 explain 看下用了哪些索引。 |
 | 8 sagaxu Aug 3, 2017 或者用(uid,cid,time desc) 哪个索引更快，看数据分布，多个 and 条件，把分布更稀疏的排前面更好 |
 | 9 RuzZ Aug 3, 2017 如果是单列的索引，可以添加联合索引试试看。explain 下查询，看看实际上使用的索引情况 |
 | 10 cnwtex Aug 3, 2017 把内容挪到另外一个表，用 id 关联起来，会提速百-千倍左右 |
| 11 Alucns OP |
| 15 sagaxu Aug 3, 2017 @Aluhao 贴 explain SELECT * FROM `article` WHERE cid=1 AND uid=100 ORDER BY time DESC LIMIT 20; 的结果 |
 | 16 noNOno Aug 3, 2017 select * from (SELECT * FROM `article` WHERE cid=1 AND uid=100) t ORDER BY time DESC LIMIT 20; 放到子查询里走索引么 |
 | 17 wayne712 Aug 3, 2017 order by time, 如果 time 没有索引则会拖慢查询 , cid,uid 设置组合索引 |
| 20 sagaxu Aug 3, 2017 @Aluhao desc 是让你建索引的时候按照 time 降序，order by time desc 的时候可以提高些许性能，不是增加一个 desc 字段 |
| 22 id4alex Aug 3, 2017 KEY `uid` (`uid`), KEY `cid` (`cid`), KEY `time` (`time`) 按道理说这种索引都不需要建立， 没卵用。 你建立联合索引（ cid，uid ）跑下 explain 就一清二楚了。 |
| 23 Alucns OP |
| 26 RuzZ Aug 3, 2017 你的 explain 结果贴一下 |
| 27 Alucns OP |
 | 28 F281M6Dh8DXpD1g2 Aug 3, 2017 先贴下加了联合索引的执行计划 |
 | 30 frye Aug 3, 2017 @Aluhao MySQL 的一个 Query 只能用一条索引，所以你分别建索引是没有用的，MySQL 只会用其中一条索引。就像大家说的，你建一个 cid,uid,time 的联合索引就好了，多多仔细深入思考下。然后还得 explain 一下，看看用对了索引没有。 |
 | 31 phx13ye Aug 3, 2017 人家让你这样建索引，把单条索引删掉，睿智 CREATE INDEX index_name ON article (cid, uid, time DESC) |
| 34 frye Aug 3, 2017 |
 | 35 daye Aug 3, 2017 索引是有使用顺序的，建了联合索引之后，请删除其他索引，确保正确性 |
 | 36 zhx1991 Aug 3, 2017 大体上讲 uid, time 的索引是没问题的 但是要看区分度, 你索引建完 explain 一下吧. 如果 cid 是分类我猜区分度巨低, 删了吧. |
 | 41 gouchaoer Aug 3, 2017 via Android 让你贴 explain 你就贴啊，这么多人帮你看墨迹啥 |
 | 42 akira Aug 3, 2017 SELECT * FROM 换成 select id , time 呢 |
| 44 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 建立索引了。 |
 | 46 simaguo Aug 3, 2017 unique (cid,uid,time desc)? |
 | 47 debye Aug 3, 2017 索引也不是万能的 还有索引多了也有牺牲 |
 | 49 orzfly Aug 3, 2017 |
 | 50 dikT Aug 3, 2017 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; ) |
 | 51 yanze0613 Aug 3, 2017 ORDER BY time DESC LIMIT 20 会导致扫描大量数据，就很麻烦 |
Select Language