Laravel优化SQL分页查询语句

好久没更新了……最近自己搭的论坛人数暴涨，数据库的CPU压力太大，要优化一下。

后端框架：Laravel

数据库：MySQL

回帖的Posts表约有500万行，thread_id字段代表所属thread。某thread有40000个posts。

优化前的PHP语句：

$posts = DB::table('posts')->where('thread_id',$thread_id)->paginate(200);

优化前相应的SQL查询：

select * FROM posts where thread_id = 14690 LIMIT 200 OFFSET 39800;

优化前的查询时间约200ms。

优化后的PHP语句：

$sql_child = DB::table('posts')->select('id')->where('thread_id', $thread_id)->offset(39800)->limit(200);//SQL子查询语句

$posts = DB::table('posts')
         ->joinSub($sql_child, 'sql_child', function ($join) use ($posts_table) {
                $join->on('posts.id', '=', 'sql_child.id');})//子查询语句的inner join
         ->get();

优化后相应的SQL查询：

select * FROM posts JOIN(select id  from posts where thread_id= 14690 LIMIT 200 OFFSET 39800) as sql_child on(posts.id = sql_child.id) ;

优化后的查询时间约100ms。约减少一半。

原理：

offset查询的时候，实际上所有行都查询一遍，再舍去offset前的行数。

优化后，通过子查询只select符合条件的id字段。再通过id字段join到原有表格所有字段。

虽然子查询id字段的时候，同样是查询所有行再舍去，但毕竟还是比查询所有字段要快得多。

结果：

CPU占用改善非常显著！