DataLatable для загрузки данных занимает много времени

Question

Я пытаюсь отобразить таблицу базы данных в формате данных Laravel, используя приведенный ниже код, но данные в формате Laravel занимают много времени для загрузки данных. У меня есть около 700000 записей в базе данных. Как уменьшить время загрузки данных?

Код

web.php

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

HomeController.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'));
}

dashboard.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

Заранее спасибо!

Answer 1

Подводя итоги обсуждения, по мере того, как ваш запрос пытается получить все записи по 700 тыс. Одновременно, неудивительно, что это занимает много времени.

Несмотря на то, что при отображении страницы вы показываете только ограниченное количество записей, технически запрос получает все записи при загрузке самой страницы.

Требуются действия

1. Сверните следующий раздел:

->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')

Вместо того, чтобы суммировать все эти значения на лету, ведите отдельную таблицу и сохраняйте все эти значения, когда любая запись изменяется в tonic_data, Есть функции обратного вызова согласно этому ответу.

2. Вместо datatables используйте разбиение на страницы и сортировку laravel, которые будут выполняться на стороне сервера, которая контролирует загрузку огромных данных при загрузке страницы.

3. попробуйте применить дату или другие возможные фильтры.

Answer 2

Похоже, Laravel выполняет один и тот же запрос дважды - один раз, чтобы получить количество строк, один раз, чтобы получить первые 10 строк.

Из-за GROUP BY, LIMIT 10 мало влияет на скорость. Это потому, что он должен собрать много строк, затем выполнить группировку и, наконец, доставить 10 строк.

Первый запрос (для подсчета) можно было бы упростить и значительно ускорить, изменив

    select  count(DISTINCT subid4) as aggregate
        from  `tonic_data`
        where  `subid4` is not null
          and  `subid4` != ?

и имея

    INDEX(subid4)

Или отказаться от счета (если Laravel позволяет вам сделать это).

Пожалуйста предоставьте SHOW CREATE TABLE tonic_data; У меня могут быть дополнительные советы.

Answer 3

Прежде всего, я предлагаю изучить производительность вашего запроса и попытаться его оптимизировать.

Во-вторых, плагин jquery datatables имеет функцию (называемую конвейером) для кэширования количества страниц для уменьшения количества вызовов 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.