时间戳

首页 > 解决方案 > laravel datatable 需要很长时间才能加载数据

php - laravel datatable 需要很长时间才能加载数据

问题描述

我正在尝试使用以下代码将数据库表显示到 laravel 数据表，但 laravel 数据表需要很长时间才能加载数据。我在数据库中有大约 700000 条记录。如何减少加载数据的时间？

代码

网页.php

Route::get('home', 'HomeController@index')->name('home');

家庭控制器.php

public function index()
{
    $campaigns = TonicData::select('campaign')->distinct()->get();

    if (request()->ajax()) {
        $data = \DB::table('tonic_data')
            ->whereNotNull('subid4')
            ->where('subid4', '!=', '')
            ->select('subid4')
            ->groupBy('subid4')
            ->selectRaw('sum(view) as sum_of_views, sum(term_view) as sum_of_term_views,
                sum(add_click) as sum_of_add_click, sum(revenue_usd) as sum_of_revenue,
                (sum(revenue_usd)/sum(view)*1000) as rpm')
            ->when((request()->has('selectedCampaign') && request()->get('selectedCampaign') != ''), function ($query) {
                $query->whereIn('campaign', request()->get('selectedCampaign'));
            })
            ->when((request()->has('selectedDateRange') && request()->get('selectedDateRange') != ''), function ($query) {
                $query->whereBetween('day_date', [request()->get('selectedDateRange')['fromDate'], request()->get('selectedDateRange')['toDate']]);
            });

        return DataTables::of($data)
            ->addIndexColumn()
            ->make();
    }

    return view('dashboard', compact('campaigns'));
}

仪表盘.blade.php

<script type="text/javascript">
    $(document).ready(function() {
        $('#datatable').dataTable({
            responsive: true,
            processing: true,
            serverSide: true,
            ajax: '{{ route('home') }}',
            columns: [
                {data: 'DT_RowIndex', orderable: false, searchable: false},
                {data: 'subid4', name: 'subid4'},
                {data: 'sum_of_views', name: 'sum_of_views', searchable: false},
                {data: 'sum_of_term_views', name: 'sum_of_term_views', searchable: false},
                {data: 'sum_of_add_click', name: 'sum_of_add_click', searchable: false},
                {data: 'sum_of_revenue', name: 'sum_of_revenue', searchable: false},
                {data: 'rpm', name: 'rpm', searchable: false}
            ]
        });
   });
</script>

sql代码

query 1:
select  count(*) as aggregate
    from ( SELECT  `subid4`,
                   sum(view) as sum_of_views,
                   sum(term_view) as sum_of_term_views,
                   sum(add_click) as sum_of_add_click,
                   sum(revenue_usd) as sum_of_revenue,
                   (sum(revenue_usd)/sum(view)*1000) as rpm
            from  `tonic_data`
            where  `subid4` is not null
              and  `subid4` != ?
            group by  `subid4`
         ) count_row_table

query 2: 
select  `subid4`, sum(view) as sum_of_views, sum(term_view) as sum_of_term_views,
        sum(add_click) as sum_of_add_click, sum(revenue_usd) as sum_of_revenue,
            (sum(revenue_usd)/sum(view)*1000) as rpm
    from  `tonic_data`
    where  `subid4` is not null
      and  `subid4` != ?
    group by  `subid4`
    limit  10 offset 0

提前致谢！

标签： phpmysqllaravelperformancelaravel-datatables

解决方案

首先，我建议查看您的查询性能并尝试优化您的查询。

其次，jquery 数据表插件有一个特性（称为管道）来缓存页面数量以减少 ajax 调用。因此，如果您已经缓存了 10 个页面，那么在第一次请求时假设它不会向服务器发出另一个请求，直到第 11 个页面被访问并继续下去。

因此，在客户端，您的 ajax 将更新为

<script type="text/javascript">
    $(document).ready(function() {
        $('#datatable').dataTable({
            responsive: true,
            processing: true,
            serverSide: true,
            ajax: $.fn.dataTable.pipeline({
                url: '{{ route('home') }}',
                pages: 20 // number of pages
            })
        });
   });
</script>

有关管道的更多帮助，请参阅https://datatables.net/examples/server_side/pipeline.html

推荐阅读