mysql优化

1、page过大时mysql搜索过慢，可使用id的between来优化

原代码

$items = Db::name('coupondetail cd')->leftjoin('couponrecord cr','cd.id = cr.coupondetail_id')->leftjoin('user u','u.id = cr.user_id')->page($page,$limit)->field('cd.*,u.name,u.phone')->order($order)->select();
$total = Db::name('coupondetail cd')->count();
$returnData['items'] = $items;
$returnData['total'] = $total;

优化后

$items = Db::name('coupondetail cd')->leftjoin('couponrecord cr','cd.id = cr.coupondetail_id')->leftjoin('user u','u.id = cr.user_id')->where('cd.id','between',[($page-1)*$limit,($page)*$limit])->field('cd.*,u.name,u.phone')->order($order)->select();
$total = Db::name('coupondetail cd')->count();
$returnData['items'] = $items;
$returnData['total'] = $total;

优化前10万数据用时5-6秒，优化后毫秒级别