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秒