Laravel口才vs SelectRaw.它如何影响查询性能? [英] Laravel Eloquent vs SelectRaw. How does it effect on query performance?
问题描述
当我们在如下情况下使用口才vs SelectRaw时，Laravel如何处理查询:
How do Laravel process the query when we use Eloquent vs SelectRaw for an scenario like:
这样可以更快地获取数据:
Will this fetch data faster:
$data = Records::where('active', 1)->get();
或者这会更快地获取我的数据:
or will this fetch my data faster:
$data = DB::select( DB::raw("SELECT * from records WHERE active ='1'") );
当我们处理约500万条记录的大数据时，使用SelectRaw是否会影响查询处理速度?
Does using SelectRaw effect on query processing speed when we are dealing with large data around 5,00,000 records?
推荐答案
使用任何ORM总是会产生一些开销.但是，这几乎总是非问题.
There will always be some overhead from using any ORM. However, this is nearly always a non-issue.
例如，尽管编写原始SQL可能会稍微提高性能，但通常与首先进行查询的成本相比，这是相形见war的.通过缓存响应，与用原始SQL重写ORM查询相比，您可以在性能上获得更大得多的改进.
For example, while there might be some slight performance increase from writing raw SQL, it's generally dwarfed by the cost of making the query in the first place. You can get a far, far bigger improvement in performance by caching the responses than by rewriting ORM queries in raw SQL.
ORM确实使某些类型的慢速低效率查询更有可能，但这可以通过使用诸如时钟来识别缓慢或不必要的查询，并将其重构.大多数ORM都具有处理诸如 N + 1问题之类的工具的工具-例如，Eloquent具有with()方法来快速加载相关表，通常比显式编写查询为您进行快速加载要方便得多.
An ORM does make certain types of slow inefficient queries more likely, but that's something that can be resolved by using a profiler like Clockwork to identify the slow or unnecessary queries and refactoring them away. Most ORM's have tools to handle things like the N+1 problem - for instance, Eloquent has the with() method to eager-load related tables, which is generally a lot more convenient than explicitly writing a query to do the eager-loading for you.
使用ORM还为开发人员带来了很多好处:
Using an ORM also comes with significant benefits to developers:
- 表达表之间的关系通常更容易
- 它有助于避免在PHP和SQL之间进行心理上下文切换
- 它为您完成了许多数据清理工作
- 它有助于使您的应用程序在不同数据库之间可移植(例如，您可以使用SQLite进行测试，但在生产环境中使用MySQL)
- 在您具有无法使用ORM表示的逻辑的地方，通常很容易就可以开始为该部分编写原始SQL
如果您在Web应用程序中的查询速度很慢，那么在以下情况下，您应该考虑做的最后一件事是将其重写为原始查询:
If you have a slow query in a web application, then rewriting it as a raw query is probably the very last thing you should consider doing, after:
- 重构查询以提高效率/删除不必要的查询
- 确保在数据库上设置了适当的索引
- 缓存响应
将所有查询写为原始查询是一个微优化-付出了很多工作却没有那么多的回报，并且考虑到开发人员的时间比服务器的时间要昂贵得多，因此几乎不值得花时间.即使您有一个单一的，极其可怕的查询或一组开销巨大的查询，也有更好的方法来处理它-在这种情况下，我倾向于在迁移中创建一个存储过程，然后调用该存储过程，而不是调用它直接进行查询.
Writing all your queries as raw queries is a micro-optimization - it's a lot of work for not that much payback, and considering developer time is much more expensive than server time, it's hardly ever worth the bother. Even if you have a single, utterly horrendous query or set of queries that has a huge overhead, there are better ways to deal with it - under those circumstances I'd be inclined to create a stored procedure in a migration and call that rather than making the query directly.
这篇关于Laravel口才vs SelectRaw.它如何影响查询性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
