新闻数据分页查询的优化

关于分页的优化相信很多博客里都有描述，原理都是一样的。比如大多数举的例子：

select * from t1 limit 10000,10;可以转换成
select * from t1 where id>=(select id from t1 limit 10000,1) limit 10;

id为主键，这样直接先查id，因为是索引覆盖的查询方式，所以很快。

但是根据id有一个问题，如果你的主键id不是自增的，或者中间有很多数据被删除了，然后又插入了新的数据。这样自增的顺序可能会被打乱，再这样去查询会不会查询的结果不对。又或者说类似于新闻文章这样的数据，我们是按照发布时间降序排序的，不是按照id。又该怎么优化新闻分页查询sql呢，首先先粘贴下新闻栏目分页查询的sql：

<pre name="code" class="sql"> SELECT
	`phpcms_a`.`id`,
	`phpcms_a`.`title`,
	`phpcms_a`.`description`,
	`phpcms_a`.`thumb`,
	`phpcms_a`.`inputtime`,
	`phpcms_a`.`is_recommend`,
	`phpcms_a`.`recommend_date`,
	`phpcms_b`.`icon`,
	`phpcms_c`.`news_view_priv`,
	`phpcms_c`.`news_view_start`,
	`phpcms_c`.`news_view_end`
FROM
	`phpcms_news` AS `phpcms_a`
LEFT JOIN `phpcms_mc_news_type` AS `phpcms_b` ON `phpcms_a`.`news_typeid` = `phpcms_b`.`id`
LEFT JOIN `phpcms_news_data` AS `phpcms_c` ON `phpcms_a`.`id` = `phpcms_c`.`id`
WHERE
	`phpcms_a`.`status` = 99
AND `phpcms_a`.`inputtime` >= 1459440000
AND `phpcms_a`.`inputtime` <= 1475251199
AND `phpcms_a`.`catid` = 12
AND `phpcms_a`.`inputtime` <= (
	SELECT
		`inputtime`
	FROM
		`phpcms_news`
	WHERE
		`catid` = 12
	AND `status` = 99
	AND `inputtime` >= 1459440000
	AND `inputtime` <= 1475251199
	ORDER BY
		`inputtime` DESC
	LIMIT 1 OFFSET 0
)
ORDER BY
	`phpcms_a`.`inputtime` DESC
LIMIT 20

这里主要有四张表关联，where后有多个查询条件，并且最后是按照发布时间inputtime降序排列的，针对这个分页查询，如果按照上面所描述的，我们应该如上面sql所写的一样，通过inputtime<=(select inputtime ...来优化分页查询，并且分页查询里的条件需要跟外层where后的条件保持一致。然后我们建个联合索引比如idx_c(catid,status,inputtime)。explain分析下这个sql结果如下：

我们来看下，这个查询还是蛮优的吧， 十几万的数据几十毫秒就能查出来了。

好了，现在问题出来了，因为我是通过inputtime来降序排序的，发布时间我们知道，在新闻的后台一般都有批量发布，批量发布的话那这些文章的发布时间都是相同的，如果分页显示每页显示20条记录，而我批量发布某个栏目的文章50篇，那么第一页通过子查询获取的inputtime和第二页通过子查询获取的inputtime是一样的了，然后再去limit查询，结果导致第一页和第二页的数据出现了重复。问题很严重，怎么办？

解决的方式是我们可以额外建一个字段比如说displayorder bigint(13)，这个字段的值默认等于inputtime*1000，如果出现重复发布时间，那么我们在保存数据的时候，写一个程序，给重复的发布时间后面加上001，这样子的话相同发布时间的文章对应的displayorder字段就是inputtime000,inputtime001,inputtime002....，这样子的话按照displayorder排序就可以了，inputtime<=换成displayorder<=，索引idx_c修改为idx_c(catid,status,displayorder)。这样就避免了发布时间相同导致的分页查询出现数据重复的问题。又能保证效率。