该段 Sql 分页代码能够优化吗？

[decdfgfe]

代码如下
$sql="select id,second_level,third_level,title,description,update_time,thumb from p_ed2k where second_level='$cid' and third_level='$scid' order by id DESC limit $offset,$perNum";
分页越到后面越慢， second_level 和 third_level 已经做索引

[xiamingchong]

没有表结构怎么看，有联合索引么

[decdfgfe]

@xiamingchong
表结构如下
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| id | int(7) | NO | PRI | NULL | auto_increment |
| page_id | int(7) | NO | | NULL | |
| page_url | varchar(50) | NO | | NULL | |
| second_level | varchar(50) | YES | MUL | NULL | |
| third_level | varchar(50) | YES | MUL | NULL | |
| title | varchar(500) | NO | | NULL | |
| resource | longtext | NO | | NULL | |
| description | text | YES | | NULL | |
| picture | varchar(1000) | YES | | NULL | |
| label | varchar(100) | YES | MUL | NULL | |
| size | varchar(20) | YES | | NULL | |
| read_count | int(11) | YES | | NULL | |
| is_finish | varchar(1) | YES | | NULL | |
| create_time | int(11) | YES | | NULL | |
| update_time | int(11) | YES | MUL | NULL | |
| resource_num | int(5) | YES | | NULL | |
| cover | varchar(100) | YES | | NULL | |
| thumb | varchar(100) | YES | | NULL | |
| status | tinyint(1) | NO | | 1 | |
| s_title | varchar(100) | YES | | NULL | |
| format | varchar(10) | YES | | NULL | |
+--------------+---------------+------+-----+---------+----------------+

[msg7086]

MySQL 分页本来就慢。先查出 id 列表然后用 id 去查记录吧。

[loveyu]

数据量多大

[xiamingchong]

@decdfgfe 最好是发创建表结构的语句，
还有多少数据就发现慢了？

[msg7086]

Rails 代码供参考

# MySQL hacks to improve pagination performance
@postids = Post.where(:category_id => @cat_id).paginate(:page => params[:page], :per_page => 15).order('id DESC').select('id').to_a;
@posts = Post.where(:id => @postids).order('id DESC');

比直接分页查数据快得多。

[sheng9632]

where id>$offset limit $pernum

[decdfgfe]

@loveyu 数据量只有 50 几 W 条。

[inmyfree]

我数据库比较挫，目前只想到两个东东，
1 、使用存储过程，可能会有一定的提升效果，但是可能不太明显
2 、应用层次做查找结果缓存，这样可以大大提升速度，缺点是数据更新可能会不及时

[decdfgfe]

@xiamingchong 在程序里面翻页时会很慢。
在 Mysql 里面 desc 执行时间却很快，本身服务器性能应该不会有这么大差别吧。

[thinkmore]

数据量大，翻页到中间就比较慢了

[hpeng]

既然 id 都排序了.你就记录上次查询的最后 id 嘛...然后就 id>lastId (个人理解

[zyue]

如果出现慢查询 econd_level 和 third_level 考虑加下索引

[realpg]

mysql limit 的 offset 比较大的情况下就会超慢……
自行用别的办法

[superbear]

@hpeng 正解，顺序翻页好使，但是一下翻到第 N 页就不太好弄。

[superbear]

可以查查 mysql 语句执行顺序， limit 是最后一步执行的

[raysmond]

建议把所有 id 放在内存中，可以用 redis/memcached ，分页的时候从内存中获取该页的所有 id ，然后再去数据库中查询

[hpeng]

@superbear 对噢。跳页就不行了。看来是个不好的方法

[wmttom]

percona 推荐的做法是查 id 分页，然后用查出来的主键 id join 本表来获取其他字段数据。

[sujin190]

翻页数据库在做操作时是数的，数据量大了肯定慢， explain 看啊

[akira]

分页是不需要精确的。
如果 id 是顺序并且连续的话，可以先获得 maxid ，然后通过简单计算，获得对应分页的起始 id 和结束 id 。

[pythoner]

对分页的优化基本就是 @raysmond 这个思路，我个人比较推荐用 redis 来搞，用 list 可以很方便地将记录 id 和页数对应起来。

再极端一点，把列表页中需要显示的字段也通通放到 redis 里，再用 lua 写一个方法，根据页数 id 的对应关系，一次性将当前页的数据拼好取出来。

再极端一点，用 nginx_lua 实现上一步。

[scalai]

不知道这样行不：
建这个索引 second_level ， third_level ， id, title, description, update_time, thumb
或 second_level ， third_level ， id

刚看了 数据库索引设计与优化 这本书，没有实操过。。。

[raysmond]

@pythoner 对啊，基本上你说的第一条就够用， redis 的 range 这些方法太好用了。至于后面两个，除非有高要求，量大，一般不用。量不是很大的，直接把一个对象扔进 redis 里去，懒得拼对象了。

[scalai]

@scalai 话说， second_level 和 third_level 看名字像是重复率很高。

[hgc81538]

是 second_level 做一個索引， third_level 做一個索引，共兩索引？
還是 second_level 和 third_level 做同一個索引？

還有你的 SQL 有注入的問題，小心

[iyaozhen]

涨见识了，以前就觉得翻页这个事情好蛋疼。每次算总页数还要 count(*)

[wawehi]

就这段 SQL ，要想优化得快，还得上缓存，推荐 redis ， list, zhash ，结合起来用，非常高效

[otakustay]

最好的办法就是把用于查询的字段再单独弄张表，从这个表拿到 id ，再用 in 通过 id 去拿数据……

[xiamingchong]

@otakustay 才这么点数据没必要的

[otakustay]

@xiamingchong 不仅仅是数据， avg row size 也有很大的影响