平时优化SQL的集合

cms_program表里字段有80多个，数据有300多W，我来时就这样了~~~额，问领导反应过，，，，额，，，

案例一：

select * from `cms_program` where `fr_dict_epg_status` = 64 and `fr_tb_source` = 2 order by `id` asc limit 50 offset 0

平均12秒左右

优化后的

其实最初的想法是

select * from
(select id from cms_program where `fr_tb_source` = 2)a INNER JOIN cms_program b
on a.id=b.id and   b.`fr_dict_epg_status` = 64
order by b.`id` asc limit 50

但方便laravel和with的写法，改为以下方案

select * from `cms_program` inner join (select `id` from `cms_program` where `fr_tb_source` = 2) as b on `cms_program`.`id` = `b`.`id` where `fr_dict_epg_status` = 64 order by `cms_program`.`id` asc limit 50 offset 0

平均1.5秒

$sub = CMSProgram::select(['id'])->where('fr_tb_source', $kw_source);
$builder = CMSProgram::Join(DB::raw("({$sub->toSql()}) as b"), 'cms_program.id', '=', 'b.id')->mergeBindings($sub->getQuery());

$builder = $builder->where('fr_dict_epg_status', '=', Dictionary::getDictIdByKey('db.program.state.online'))->with('partner')->with('type')->with('vt');

案例二：

select id,title,tid,duration_second from `cms_program` where `fr_dict_epg_status` = 64 order by `id` asc limit 50 offset 784050

平均18秒左右

1）limit语句的查询时间与起始记录的位置成正比
2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

SELECT * FROM cms_program a JOIN (select id from cms_program  where `fr_dict_epg_status` = 64 ORDER BY id asc limit 784050,50) b ON a.ID = b.id

平均0.6秒