LOADING

标签 Laravel 下的文章

public function collectionPage($datas,$filters){
        $datas_count = $datas->count();
        switch ($filters['sort_by']){
            case "DESC":
                $datas = $datas->sortByDesc($filters['sort'])->forPage($filters['page'],$filters['limit']);
                break;
            case "ASC":
                $datas = $datas->sortBy($filters['sort'])->forPage($filters['page'],$filters['limit']);
                break;
            default:
                $datas = $datas->sortBy($filters['sort'])->forPage($filters['page'],$filters['limit']);
                break;
        }
        $datas = $datas->values()->all();
        $option = [
            "path" => URL::full(),
            "pageName" => "page",
        ];
        return new LengthAwarePaginator($datas,$datas_count,$filters['limit'],$filters['page'],$option);
    }

用的两张表关联主表 49609 副表 数据35352
首先是子查询的代码 用的laravel自带orm

$student = Student::doesntHave('hasBanjiStudents')->get();
//变成mysql语句是
select * from `tbl_students` where not exists (select * from `tbl_banji_students` where `tbl_students`.`id` = `tbl_banji_students`.`student_id`) and `tbl_students`.`deleted_at` is null
//耗时时间 0.270s

接下来有请join闪亮登场

 $student = Student::leftJoin('banji_students','students.id','=','banji_students.student_id')
                ->select('students.*','banji_students.banji_id')
                ->where('banji_students.banji_id',null)
                ->get();
//变成sql语句是
select `tbl_students`.*, `tbl_banji_students`.`banji_id` from `tbl_students` left join `tbl_banji_students` on `tbl_students`.`id` = `tbl_banji_students`.`student_id` where `tbl_banji_students`.`banji_id` is null and `tbl_students`.`deleted_at` is null
//耗时0.169s

上网查了一下, laravelorm基本都基于子查询, 而子查询在使用中会建立一张临时表进行数据排序等,而join则不会,所以子查询又慢又占用内存

$link_popular_record_false = Student::whereNotIn('id',$temp_link_popular)
                    ->whereIn('id',$rand_arr)
                    ->limit($limit-$link_count)
                    ->get();

//打印写法故意写错select
$link_popular_record_false = Student::whereNotIn('id',$temp_link_popular)
                    ->select('asdasd')
                    ->whereIn('id',$rand_arr)
                    ->limit($limit-$link_count)
                    ->get();

当数据库表中数据过3w之后inRandomOrder用起来就十分的慢还占用内存,现数据50w.
explan之后发现查询速度为0.32

解决方案

        $rand_arr = [];
        for ($i=0;$i<100;$i++){
            $rand_arr[] = mt_rand(500,10000);
        }
 $link_popular_record_false = Student::whereNotIn('id',$temp_link_popular)
                    ->whereIn('id',$rand_arr)
                    ->limit($limit-$link_count)
//                    ->inRandomOrder()
                    ->get();

用whereIn进行主键筛选就可以了 修改后查询效率为0.0026

1.修改laravel配置文件. configapp.php

   'log'=>'daily'

2.在项目目录中composer命令安装扩展:composer require arcanedev/log-viewer

3.安装成功后,需要在configapp.php 的providers阵列中注册服务

     Arcanedev\LogViewer\LogViewerServiceProvider::class

此时可以直接访问 http:://域名/log-viewer

需要改变route或者语言可以在log-viewer中的config配置

php artisan log-viewer:publish :运行此命令发布配置和翻译文件

git地址:https://github.com/ARCANEDEV/LogViewer/blob/master/_docs/1.Installation-and-Setup.md

需要注意 安装几个依赖, 最新版本互相可能不兼容, 选择最合适的就好

大日志会导致内存溢出, 建议小项目用

public function up()

{
    Schema::create('activitys', function (Blueprint $table) {
        $table->increments('activity_id')->comment('活动表');
        $table->timestamps();
    });
    DB::statement("ALTER TABLE `tbl_activitys` comment'活动表'");//表注释一定加上前缀
}

$map['task_id'] = $request->task_id;
$finished = $request->finished;
$isShow = $request->is_show;
$isAll = $request->is_all ? $request->is_all: 0;
$limit = $request->limit ? $request->limit : 6;
if($isShow==1) {

$finished = '';

}
$data = TableModel::where($map)

->when($finished == 2 , function ($query) use ($finished) {//            未完成
    return $query->where('finished','!=','1');
})
->when($finished == 1  , function ($query) use ($finished) {//            已完成
    return $query->where('finished','1');
})
->when($isShow == 1 , function ($query) use ($isShow) {//              
    return $query->where('read_time','=','null');
})
->select('*')
->orderBy('num', 'desc')
->with('hasUser')
->when($isAll == 1 , function ($query) use ($isAll) {//              没有分页
    return $query->get();
})
->when($isAll != 1 , function ($query) use ($isAll,$limit) {//              有分页
    return $query->paginate($limit);
});

$keyword = $this->filters['keyword'];
$options = $this->parseOptions();
$datas = TableModel::with('hasBook')
    ->with('hasGradeCell.hasGrade')
    ->when($keyword,function($query)use($keyword){
        return $query->where('title','like','%'.$keyword.'%');
    })
    ->when($options,function ($query) use ($options){
        if (isset($options['cell_id'])) {
            return $query->where('cell_id', $options['cell_id']);
        }
        if (isset($options['grade_id'])) {
            return $query->whereHas('hasGradeCell',function ($query) use ($options){
                return $query->whereHas('hasGrade',function ($query) use ($options){
                    return $query->where('id',$options['grade_id']);
                });
            });
        }

    });
$counts            = $datas->count();

$datas = $datas->where('type', 1)
        ->orderBy($this->filters['sort'], $this->filters['sort_by'])
        ->paginate($this->filters['limit'])
        ->appends($this->filters);