MySQL Laravel Eloquent 与查询构建器 - 为什么使用 eloquent 来降低性能

Laravel Eloquent vs query builder - Why use eloquent to decrease performance

提问by Panagiotis Koursaris

I did some performance test between Laravel query builder and eloquent. Query builder was much faster with various of sql statement (select-update-delete-insert).

我在 Laravel 查询构建器和 eloquent 之间做了一些性能测试。使用各种 sql 语句（select-update-delete-insert）查询构建器要快得多。

So my question is: Why someone uses Laravel Eloquent against plain query builder?

所以我的问题是：为什么有人使用 Laravel Eloquent 来对抗普通查询构建器？

回答by JustAMartin

Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses.

Eloquent 是 Laravel 对 Active Record 模式的实现，它具有所有优点和缺点。

Active Record is a good solution for processing a single entity in CRUD manner - that is, create a new entity with filled properties and then save it to a database, load a record from a database, or delete.

Active Record 是以 CRUD 方式处理单个实体的一个很好的解决方案 - 即创建一个具有填充属性的新实体，然后将其保存到数据库、从数据库加载记录或删除。

You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alerts or update statistics counters when someone has created a new account), traits (timestamps, soft deletes, your custom traits) eager/lazy loading etc. You can also apply domain-driven pattern and implement some pieces of business logic in your Active Record entities, for example, validation, managing relations, calculations etc.

您将从 Eloquent 的功能中受益匪浅，例如脏检查（仅针对已更改的字段发送 SQL 更新）、模型事件（例如，在有人创建新帐户时发送管理警报或更新统计计数器）、特征（时间戳、软删除、您的自定义特征）急切/延迟加载等。您还可以应用域驱动模式并在您的 Active Record 实体中实现一些业务逻辑，例如，验证、管理关系、计算等。

But, as you already know, Active Record comes with some performance price.

但是，正如您已经知道的，Active Record 带来了一些性能代价。

When you process a single record or a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain Laravel DBmethods is a better approach.

当您处理单条记录或几条记录时，无需担心。但是对于读取大量记录的情况（例如，对于数据网格、报表、批处理等），普通的 LaravelDB方法是更好的方法。

For our Laravel based applications we are using both approaches as we see appropriate. We use Laravel's Eloquent for UI forms to process a single record and use DBmethods (backed by SQL views with additional database engine specific performance tweaks) to retrieve data for UI tables, export tasks etc. It also works well with RESTful APIs - Eloquent for GET, PUT, POST, DELETE with a key and DBfor GET without key but with filters and sorting and paging.

对于我们基于 Laravel 的应用程序，我们将使用两种我们认为合适的方法。我们使用 Laravel 的 Eloquent for UI 表单来处理单个记录并使用DB方法（由 SQL 视图支持，并具有额外的数据库引擎特定性能调整）来检索 UI 表、导出任务等的数据。它也适用于 RESTful API - Eloquent for GET , PUT, POST, DELETE 带键和DBGET 不带键但带过滤器和排序和分页。

回答by Maniruzzaman Akash

Yes, In some case you are right. When we've more dataand almost in every site, data is not small really. Then it is better to use DB Query than the Eloquent Query.

是的，在某些情况下你是对的。当我们拥有更多数据并且几乎在每个站点中时，数据确实不小。那么使用 DB Query 比使用 Eloquent Query 更好。

In a performance issue of Eloquent VS DBI've heard that,

在Eloquent VS DB的性能问题中，我听说过，

To insert 1000 rows for a simple table Eloquent takes 1.2 seconds and in that case DB facades take only 800 mili seconds(ms).

为一个简单的表插入 1000 行 Eloquent 需要 1.2 秒，在这种情况下，DB Facades 只需要 800 毫秒（ms）。

So Why then Eloquent ? Is't any necessary of that ?

那么为什么是 Eloquent 呢？没有必要吗？

Answer is - Eloquent is also necessary. Cause-

答案是——Eloquent 也是必要的。原因-

To create a better relationshipand get the results in view with so much simple syntax, when there needs to join.

Eloquent is also for who have not much knowledge of SQL query.

An MVC framework follow the rules of Code Readability, Code Maintainabilityand which Eloquent is, you know that. A code comparison below. Obviously, Eloquent is better to read.

在需要加入时，创建更好的关系并使用如此简单的语法获得结果。

Eloquent 也适用于对 SQL 查询不太了解的人。

MVC 框架遵循代码可读性、代码可维护性以及 Eloquent的规则，你知道的。下面的代码比较。显然，Eloquent 更适合阅读。

// In Eloquent
$student = App\Student::find($id);

// In DB facade
$student = DB::table('student')->where('id', $id)->first();

Most important part isif we want to change other database, then raw query will be a lot much headache to us and in that case Laravel Eloquent will solve all the problems with one hand. It can handle different types Database.

最重要的部分是，如果我们想更改其他数据库，那么原始查询会让我们非常头疼，在这种情况下，Laravel Eloquent 将一手解决所有问题。它可以处理不同类型的数据库。

So when we use Eloquent and When DB facades:

所以当我们使用 Eloquent 和 When DB facades 时：

1. When we'll work on a simple and small records site with simple CRUD and there records are not fact, then use Eloquent there.
2. When we'll work on a lot's of records, it is better to use DB Query than Eloquent.

1. 当我们使用简单的 CRUD 在一个简单的小型记录站点上工作并且记录不是事实时，请在那里使用 Eloquent。
2. 当我们处理大量记录时，使用 DB Query 比使用 Eloquent 更好。

So, finally it is cleared that - when we'll use Database Query and When we'll use Eloquent Query.

所以，最后明确了 - 我们何时使用数据库查询，何时使用 Eloquent Query。

Edit - Real Life Example

编辑 - 现实生活中的例子

1. I'm making an University website. Which may contain maximum 5,000 teachers and 10,000 students and some notices and files. Then it is better to do this with simple Laravel Eloquent which is very much standard and readable.
2. Now I'm making a site like Stackoverflow. Which may contains more than 1,000,0000 (1 crore) posts and many more things. I will must choose the conventional DB facades there. It is more faster for searching the posts from so much records.

1. 我正在制作一个大学网站。其中可能包含最大值5,000 teachers and 10,000 students and some notices and files。那么最好使用简单的 Laravel Eloquent 来做到这一点，它非常标准且可读。
2. 现在我正在制作一个像Stackoverflow这样的网站。其中可能包含多个1,000,0000 (1 crore) posts and many more things。我必须在那里选择传统的 DB 外观。从这么多记录中搜索帖子更快。

You can check your query performance using Laravel Debugbar(A popular package to check Eloquent/Database query performance/ execution times)

您可以使用Laravel Debugbar（用于检查Eloquent/数据库查询性能/执行时间的流行包）检查查询性能

Now it's your choice. What you want to make...

现在是你的选择。你想做什么...

回答by Imran

Why Laravel Eloquent:

为什么 Laravel 雄辩：

1. Executing query in a OOPmanner.
2. Easy to use than raw query or query builder.
3. No binding with table schema. i.e. Even if you change your table name, no need to touch a single query(there may have 1000 query) to make it work. Just change the table name in eloquent model.
4. Relationship among tables can be maintained in an elegant way. Just mention the type of relationship, nothing else(JOIN, LEFT JOIN, RIGHT JOINetc.) needed in query anymore to get data of related tables.
5. Queries are highly readable while written using Eloquent comparing with Query Builder.

1. 以某种OOP方式执行查询。
2. 比原始查询或query builder.
3. 没有绑定table schema。即即使您更改了表名，也无需触摸单个query（可能有 1000 个query）即可使其工作。只需更改 eloquent 中的表名model。
4. 可以以优雅的方式维护表之间的关系。只需提及关系的类型，JOIN, LEFT JOIN, RIGHT JOIN查询中不再需要其他（等）来获取相关表的数据。
5. 与查询生成器相比，使用 Eloquent 编写的查询具有高度可读性。

回答by Mohammad Naji

When it comes to performance and the application grows, for the sake of comparison, take a loot at the following tables:

当谈到性能和应用程序增长时，为了比较，请查看下表：

Comparison of select operation average response time between Eloquent ORM and Raw SQL

Eloquent ORM 和 Raw SQL 的 select 操作平均响应时间比较

Eloquent ORM average response time

Eloquent ORM 平均响应时间

Joins | Average (ms) 
------+-------------
1     | 162,2 
3     | 1002,7 
4     | 1540,0 

Result of select operation average response time for Eloquent ORM 

---

---

Raw SQL average response time

原始 SQL 平均响应时间

Joins | Average (ms) 
------+-------------
1     | 116,4 
3     | 130,6 
4     | 155,2 

Result of select operation average response time for Raw SQL 

Article Reference

文章参考

回答by Arthur Tarasov

It is just my opinion, not a comprehensive answer. I use whatever is more convenient in a given situation.

这只是我的意见，不是一个全面的答案。在给定的情况下，我使用更方便的方法。

If I come across a package or code written either in eloquent or query builder, I use whatever is being used.

如果我遇到用 eloquent 或查询构建器编写的包或代码，我会使用正在使用的任何内容。

I found query builder to be more intuitive if I create something from scratch so I use it more often.

我发现如果我从头开始创建一些东西，查询生成器会更直观，所以我会更频繁地使用它。

When it comes to Laravel, it seems, the ease and speed of developing an app is more important then performance. I really like that they make everything very easy even for someone with little prior knowledge of php/mysql. In some cases eloquent is easier than query builder. In others vice versa. I think having many ways of doing something is what makes Laravel so easy and newbie friendly.

对于 Laravel，似乎开发应用程序的简便性和速度比性能更重要。我真的很喜欢它们让一切变得非常简单，即使对于那些对 php/mysql 知之甚少的人来说也是如此。在某些情况下，eloquent 比查询生成器更容易。在其他情况下则相反。我认为有很多做某事的方法是让 Laravel 如此简单和新手友好的原因。

回答by HENG Vongkol

I like using query builder when building complex query from database because it seems easy to use. For working with a single table, I like eloquent.

我喜欢在从数据库构建复杂查询时使用查询构建器，因为它看起来很容易使用。对于使用单个表，我喜欢 eloquent。

回答by Patrick N.

Eloquent ORM is best suited working with fewer data in a particular table. On the other side, query builder takes less time to handle numerous data whether in one or more tables faster than Eloquent ORM.

Eloquent ORM 最适合处理特定表中较少的数据。另一方面，查询构建器比 Eloquent ORM 更快地处理一个或多个表中的大量数据所需的时间更少。

In my case, I use ELoquent ORM in an application with tables that will hold less than 17500 entries. Whenever I anticipate the table will hold more than 17500 entries, query builder is the best.

就我而言，我在包含少于 17500 个条目的表的应用程序中使用 ELoquent ORM。每当我预计该表将包含超过 17500 个条目时，查询生成器是最好的。

Further, in applications with subqueries, I prefer query builder over ELoquent ORM.

此外，在具有子查询的应用程序中，我更喜欢查询构建器而不是 ELoquent ORM。

回答by Nitesh Mangla

There is many different between them

他们之间有很多不同

1. Builder query is much faster than ORM you test it by debugger. here is the how you can test https://scotch.io/tutorials/debugging-queries-in-laravel.
2. Builder query less time to execute when you work with large amount data like if have more 1,00,000 in your database than ORM.
3. Builder query is best suitable for large amount of data.Orm is best suitable when you work with relation because its provide many relation method to define relation between tables.
4. Builder query use join of sql but orm use relation to work 2 tables or more table.

1. Builder 查询比您通过调试器测试的 ORM 快得多。这是测试https://scotch.io/tutorials/debugging-queries-in-laravel 的方法。
2. 当您处理大量数据时，生成器查询的执行时间更少，例如数据库中的数据比 ORM 多 1,00,000。
3. Builder 查询最适合大量数据。Orm 最适合处理关系，因为它提供了许多关系方法来定义表之间的关系。
4. 生成器查询使用 sql 的连接，但 orm 使用关系来处理 2 个或多个表。