如何优化大查询? [英] How to optimize a big query?

问题描述

我有一个拥有10000个用户的表，并且与work_hours表有关系.

I have a table with 10000 users and there is a relationship with work_hours table.

这是我的用户模型，以及我如何获得工作时间总和.

This is my User Model and how I get sum of work hours.

protected $appends = ['sum_work_hours'];

public function work_hours()
{
    return $this->hasMany(WorkHour::class);
}

public function getSumWorkHoursAttribute()
{
    return $this->work_hours->sum('hours_total');
}

然后我进行了查询，以显示所有用户及其工作时间总和，如下所示:

And I made a query to show all users and their sum of work hours like this:

public function getHoursDatatable()
{
    $users = User::with('work_hours')->get();

    return Datatables::of($users)->make();
}

此查询需要大约 7-8秒来显示非常慢的表.

And this query takes about 7-8 seconds to show the table which is very slow.

我知道这是因为总结了work_hours表中每个用户的总工作时间，并且我想学习如何优化此查询(即使考虑到数据量也可以做到这一点?)

I know that this is because of summing up the total work hours from work_hours table for each user and I want to learn how to optimize this query if this is even possible considering the amount of data?

    $users = User::with('work_hours')->select(['users.name', 'users.email']);

    return Datatables::of($users)
        ->add_column('sum_work_hours', function(User $user) {
            return $user->work_hours->sum('hours_total');
        })
        ->make();

这给了我一个错误:

DataTables警告:表id = users-table-Ajax错误.欲了解更多 有关此错误的信息，请参见 http://datatables.net/tn/7

DataTables warning: table id=users-table - Ajax error. For more information about this error, please see http://datatables.net/tn/7

更新2

这有效:

Update 2

This works:

public function getHoursDatatable()
{
    $users = User::with('work_hours');

    return Datatables::of($users)
        ->addColumn('sum_work_hours', function(User $user) {
            return $user->work_hours->sum('hours_total');
        })
        ->make();
}

但是加载大约需要 7-8秒 ...

But also takes about 7-8 seconds to load...

推荐答案

有时，您的数据将变得太大而无法一次查询所有内容.

At some point your data will just get too big to query everything at once.

基本上，您有2个选择:

Basically you have 2 options:

1. 由于您已经有1万个用户(这个数字可能还会增加吗?)，我建议切换到异步(serverSide: true选项).您正在使用数据表，因此显然您不需要一页中的所有10K用户，也不需要单个报告中的所有用户.

1. Since you already have 10K users (and that number might still grow?) I would suggest switching to async (serverSide: true option). You are using datatables, so obviously you do not need all 10K users in 1 page, nor do you need all of them for a single report.

数据表确实允许服务器端处理，并且它确实允许分页，同时保持过滤和排序选项的状态，就像您现在可能正在做的那样.

Datatables do allow serverside processing and it does allow pagination, while keeping the options to filter and sort like you're probably doing right now.

yajra软件包将继续做它现在正在做的事情，您需要更改的是您没有将所有用户分配给该视图并以此方式构建它，您将需要一个emtpy表并让前端填充它.只需创建一个api端点即可将数据表指向该端点，以便从中获取分页列表.

The yajra package will keep doing what it is doing right now, the thing you will have to change is that you do not assign all users to the view and build it like that, you'll need an emtpy table and let the frontend populate it. Just create an api endpoint where you can point your datatable to, in order to fetch the paginated list from it.

因此，不要在视图中使用@foreach ($users as $user)，而不要在显示表的控制器操作中获取它，让api端点对其进行处理.

So instead of using @foreach ($users as $user) in your view, do not fetch it in the controller action that displays the table, let the api endpoint handle it.

示例:

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>First name</th>
            <th>Last name</th>
            <th>Total Hours</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th>First name</th>
            <th>Last name</th>
            <th>Total Hours</th>
        </tr>
    </tfoot>
</table>

您将需要执行此操作(在此处或您正在使用的某些lib中)

And you'll need to do this (here or in some lib you are using)

$(document).ready(function() {
    $('#example').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "../server_side/scripts/server_processing.php"
    } );
} );

您还需要:

//code.jquery.com/jquery-1.12.4.js https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js

//code.jquery.com/jquery-1.12.4.js https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js

如果您尚未将其包含在项目中，则您的信息尚不清楚.

If you did not already include that in your project, that's not clear from you info.

有关更多信息，请参见:数据表服务器端处理

For more info see: Datatables Server-side processing

2. 将总小时数存储在其他位置.完成对工作时间模型的更改后，您可以简单地更新每位用户的小时总数.这样，您无需在每次请求一个或一个列表(或所有)用户时进行总和.

2. Store the total sum of hours somewhere else. You could simply update the sum of hours per user when changes to the Workhour model are done. That way you will not need to do the sum every time you request a single or a list (or all) users.

我的建议是至少做#1，然后做#2将是一个很好的优化.

My suggestion would be to do at least #1 and #2 will be a good optimization after that.

这篇关于如何优化大查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！