Laravel Eloquent 查询 200 万行需要很长时间 [英] Laravel Eloquent Query to 2 Million Rows takes long time

问题描述

我必须从有 200 万行的表中提取数据.雄辩的查询如下所示:

I have to pull data from a table that has 2 million rows. The eloquent query looks like this:

$imagesData = Images::whereIn('file_id', $fileIds)
                    ->with('image.user')
                    ->with('file')
                    ->orderBy('created_at', 'DESC')
                    ->simplePaginate(12);

whereIn 中使用的 $fileIds 数组可以包含 100 个甚至 1000 个文件 ID.

The $fileIds array used in whereIn can contain 100s or even 1000s of file ids.

上述查询在小表中运行良好.但是在 Images 表中有超过 200 万行的生产站点中，需要 15 秒以上才能得到回复.我只将 Laravel 用于 api.

The above query works fine in small table. But in production site that has over 2 million rows in Images table, it takes over 15 seconds to get a reply. I use Laravel for api only.

我已阅读有关此主题的其他讨论.我将 paginate() 更改为 simplePaginate().一些人建议使用 whereRaw 进行 DB:: 查询可能比 whereIn 效果更好.有人说这可能是由于处理 whereIn 时 php 中的 PDO 造成的，有人建议使用我已经使用过的 Images::whereIn.

I have read through other discussions on this topic. I changed paginate() to simplePaginate(). Some suggests perhaps having a DB:: query with whereRaw might work better than whereIn. Some says it might be due to PDO in php while processing whereIn and some recommends using Images::whereIn which I already used.

我使用 MariaDB，带有 InnoDB for db 引擎并将其加载到 RAM 中.sql 查询对所有其他查询都表现良好，但只有那些必须从像这样的大表中收集数据的查询才需要时间.

I use MariaDB, with InnoDB for db engine and its loaded into RAM. The sql queries performs well for all other queries, but only the ones that has to gather data from huge table like this takes time.

如何优化上述 laravel 查询，以便在表有数百万行时将查询响应缩短到几秒钟?

How can I optimise the above laravel query so I can reduce down the query response to couple of seconds if possible when the table has millions of rows?

推荐答案

您需要索引，它按某些列对数据进行分段.您正在访问 file_id 和 created_at.因此，以下索引将有助于提高性能.

You need indexing, which segmented your data by certain columns. You are accessing file_id and created_at. Therefore this following index will help performance.

$table->index(['file_id', 'created_at']);

索引会增加插入时间，并使查询具有奇怪的执行计划.如果您在执行查询之前和之后在查询上使用 SQL EXPLAIN，我们可以验证它有助于解决问题.

Indexing will increase insert time and can make queries have weird execution plans. If you use the SQL EXPLAIN on the query before an after executing the query, we can verify it helps the problem.

这篇关于Laravel Eloquent 查询 200 万行需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！