[Compilation and sharing] 18 tips for optimizing database queries in Laravel8

How does Laravel optimize database queries? The following article will share with you 18 tips on Laravel8 database query optimization. I hope it will be helpful to you!

#If your app is slow or has a lot of database queries, follow these performance optimization tips to improve your app's load time. [Related recommendations: laravel video tutorial]

1. Retrieve large data sets

This tip mainly focuses on improving the application when processing large data sets memory usage.

When processing large collections, group search results are processed instead of one-time search processing.

The following shows the process of retrieving data from the posts table.

$posts = Post::all(); // 使用 eloquent
$posts = DB::table('posts')->get(); // 使用查询构造器
 foreach ($posts as $post){
 // 处理 posts 操作
}

The above example will retrieve all records from the posts table and process them. What if this expression reaches over 1 million rows? Memory will be exhausted quickly.

To avoid problems when processing large data sets, we can retrieve a subset of results and process them as follows.

Option 1: Use chunk

// 当使用 eloquent 时
$posts = Post::chunk(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});
 // 当使用查询构造器时
$posts = DB::table('posts')->chunk(100, function ($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

The above example retrieves 100 records from the posts table for processing, and retrieves another 100 records for processing. This iteration continues until all records have been processed.

This approach will create more database queries, but will be more memory efficient. Typically, processing of large data sets should occur in the background. Therefore, more queries can be run in the background to avoid running out of memory when processing large data sets.

Option 2: Using Cursors

// 使用 eloquent
foreach (Post::cursor() as $post){
   // 处理单个 post
}
 // 使用 query 构建器
foreach (DB::table('posts')->cursor() as $post){
   // 处理单个 post
}

Example Make a single database query that retrieves all records of a table, processing the Eloquent models one after the other. This method only queries the database once and gets all posts. But use php generator to optimize memory usage.

When should I use this?

This can greatly optimize memory usage at the application layer. Since we retrieve all the data in the table, the database memory usage is still very high.

When the database has more memory and the application has less memory, it is recommended to use cursors. However, if your database does not have enough memory, it is better to use chunks.

Option 3: Use chunkById

// 使用 eloquent
$posts = Post::chunkById(100, function($posts){
    foreach ($posts as $post){
     // 处理 posts
    }
});
 // 使用 query 构造器
$posts = DB::table('posts')->chunkById(100, function ($posts){
    foreach ($posts as $post){
     // 处理 posts
    }
});

chunk The biggest difference between chunkById is chunk Retrieve data via offset and limit. However
chunkById retrieves the structure by the id field. The id field is usually an integer field, and it is also an auto-incrementing field. The query for

chunk and chunkById is as follows.

chunk

select * from posts offset 0 limit 100

select * from posts offset 101 limit 100

chunkById

select * from posts order by id asc limit 100

select * from posts where id > 100 order by id asc limit 100

Usually, query using limit and offset is slower, try to avoid using it. This article introduces in detail the problem of using offset.

chunkById uses the id integer field and queries through where clause, which will be faster.

When to use chunkById?

• Used when the database has an auto-incremented primary key.

2. Select the appropriate column

Usually when retrieving data from the database, you will do the following.

$posts = Post::find(1); // 使用 eloquent
$posts = DB::table('posts')->where('id','=',1)->first(); // 使用 query 构建器

The above code will get the following query

select * from posts where id = 1 limit 1

select * means to find all columns from the table.
This is no problem when all columns are required.

However, when you only need the specified columns (id, title), you only need to retrieve those columns like below.

$posts = Post::select(['id','title'])->find(1); // 使用 eloquent
$posts = DB::table('posts')->where('id','=',1)->select(['id','title'])->first(); // 使用 query 构建器

The above code gets the following query

select id,title from posts where id = 1 limit 1

3. When one or two columns of the database table are needed

This is the main concern Processing time for search results. This does not affect actual query time.

As I mentioned above, to retrieve the specified column, you can do this

$posts = Post::select(['title','slug'])->get(); // 使用 eloquent
$posts = DB::table('posts')->select(['title','slug'])->get(); // 使用 query 构建器

Execute the above code, it will perform the following operations behind the scenes.

• Execute select title, slug from posts Query
• Each row retrieved corresponds to a Post model object (for PHP object) (query builder gets standard PHP object)
• Generate collection for Post model
• Return collection

Access data

foreach ($posts as $post){
    // $post 是 Post 模型或  php 标准对象
    $post->title;
    $post->slug;
}

The above approach has the additional overhead of creating a Post model for each row and creating a collection for these objects. This is the most correct approach if you really need Post model instances instead of data.

But if you only need two values, you can do the following:

$posts = Post::pluck('title', 'slug'); // 使用 eloquent 时
$posts = DB::table('posts')->pluck('title','slug'); // 使用查询构造器时

When the above code is executed, it does the following behind the scenes.

• 对数据库执行 select title, slug from posts 查询
• 创建一个数组，其中会以 title 作为 数组值，slug 作为 数组键
• 返回数组 ( 数组格式：[ slug => title, slug => title ] )

要访问结果，我们可以这么做

foreach ($posts as $slug => $title){
    // $title 是 post 的 title
    // $slug 是 post 的 slug
}

如果您想检索一列，您可以这么做

$posts = Post::pluck('title'); // 使用 eloquent 时
$posts = DB::table('posts')->pluck('title'); // 使用查询构造器时
foreach ($posts as  $title){
    // $title 是 post 的 title
}

上面的方式消除了每一行 Post 对象的创建。这将降低查询结果处理的内存和时间消耗。

建议在新代码中使用上述方式。个人感觉不值得花时间遵循上面的提示重构代码。
重构代码，最好是在要处理大的数据集或者是比较闲的时候

4. 使用查询代替 collection 来统计行数

统计表的行数，通常这样做

$posts = Post::all()->count(); // 使用 eloquent
$posts = DB::table('posts')->get()->count(); // 使用查询构造器

这将生成以下查询

select * from posts

上述方法将从表中检索所有行。将它们加载到 collection 对象中并计算结果。当数据表中的行较少时，这可以正常工作。但随着表的增长，内存很快就会耗尽。

与上述方法不同，我们可以直接计算数据库本身的总行数。

$posts = Post::count(); // 使用 eloquent 时
$posts = DB::table('posts')->count(); // 使用查询构造器时

这将生成以下查询

select count(*) from posts

在 sql 中计算行数是一个缓慢的过程，当数据库表中有多行时性能会很差。最好尽量避免计算行数。

5. 通过即时加载关系避免 n + 1查询

这条建议你可能听说过无数次了。所以我会尽可能简短。让我们假设您有以下场景

class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
        return view('posts.index', ['posts' => $posts ]);
    }
}

// posts/index.blade.php 文件
 @foreach($posts as $post)
    

        
{{ $post->title }}
        
Author: {{ $post->author->name }}
    
@endforeach

上面的代码是检索所有的帖子，并在网页上显示帖子标题和作者，假设帖子模型关联作者。

执行以上代码将导致运行以下查询。

select * from posts // 假设返回5条数据
select * from authors where id = { post1.author_id }
select * from authors where id = { post2.author_id }
select * from authors where id = { post3.author_id }
select * from authors where id = { post4.author_id }
select * from authors where id = { post5.author_id }

如上，1 条查询来检索帖子，5 条查询来检索帖子的作者（假设有 5 篇帖子）。因此对于每篇帖子，都会进行一个单独的查询来检索它的作者。

所以如果有 N 篇帖子，将会产生 N+1 条查询（1 条查询检索帖子，N 条查询检索每篇帖子的作者）。这常被称作 N+1 查询问题。

避免这个问题，可以像下面这样预加载帖子的作者。

$posts = Post::all(); // Avoid doing this
$posts = Post::with(['author'])->get(); // Do this instead

执行上面的代码得到下面的查询：

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )

6. 预加载嵌套关系

从上面的例子，考虑作者归属于一个组，同时需要显示组的名字的情况。因此在 blade 文件中，可以按下面这样做。

@foreach($posts as $post)
    

        
{{ $post->title }}
        
Author: {{ $post->author->name }}
        
Author's Team: {{ $post->author->team->name }}
    
@endforeach

接着

$posts = Post::with(['author'])->get();

得到下面的查询：

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id = { author1.team_id }
select * from teams where id = { author2.team_id }
select * from teams where id = { author3.team_id }
select * from teams where id = { author4.team_id }
select * from teams where id = { author5.team_id }

如上，尽管预加载了 authors 关系，仍然产生了大量的查询。这是因为没有预加载 authors 上的 team 关系。

通过下面这样来解决这个它。

$posts = Post::with(['author.team'])->get();

执行得到下面的查询。

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id in( { author1.team_id }, { author2.team_id }, { author3.team_id }, { author4.team_id }, { author5.team_id } )

通过预加载嵌套关系，可以将查询数从 11 减到 3。

7. 如果仅需要 id 时，别预加载 belongsTo 关系

想象一下，有 posts 和 authors 两张表。帖子表有 author_id 列归属作者表。

为了得到帖子的作者 id，通常这样做

$post = Post::findOrFail();
$post->author->id;

执行得到两个查询。

select * from posts where id =  limit 1
select * from authors where id =  limit 1

然而，可以直接通过下面方式得到作者 id 。

$post = Post::findOrFail();
$post->author_id; // 帖子表有存放作者 id 的 author_id 列

什么时候采取上面的方式？

采取上的方式，需要确保帖子关联的作者在作者表始终存在。

8. 避免使用不必要的查询

很多时候，一些数据库查询是不必要的。看看下面的例子。

 $posts, 'private_posts' => $private_posts ]);
    }
}

上面代码是从两张不同的表（posts， private_posts）检索数据，然后传到视图中。
视图文件如下。

// posts/index.blade.php
 @if( request()->user()->isAdmin() )
    
Private Posts
    

        @foreach($private_posts as $post)
            

                
{{ $post->title }}
                
Published At: {{ $post->published_at }}
            
        @endforeach
    
@endif
 
Posts

    @foreach($posts as $post)
        

            
{{ $post->title }}
            
Published At: {{ $post->published_at }}
        
    @endforeach

正如你上面看到的，$private_posts 仅对 管理员 用户可见，其他用户都无法看到这些帖子。

问题是，当我们在做

$posts = Post::all();
$private_posts = PrivatePost::all();

我们进行两次查询。一次从 posts 表获取记录，另一次从 private_posts 表获取记录。

private_posts 表的记录仅 管理员用户 可见。但我们仍在查询以检索所有用户记录，即使它们不可见。

我们可以调整逻辑，避免额外的查询。

$posts = Post::all();
$private_posts = collect();
if( request()->user()->isAdmin() ){
    $private_posts = PrivatePost::all();
}

将逻辑更改为上述内容后，我们对管理员用户进行了两次查询，并对其他用户进行了一次查询。

9. 合并相似的查询

我们有时需要进行查询以同一个表中检索不同类型的行。

$published_posts = Post::where('status','=','published')->get();
$featured_posts = Post::where('status','=','featured')->get();
$scheduled_posts = Post::where('status','=','scheduled')->get();

上述代码正从同一个表检索状态不同的行。代码将进行以下查询。

select * from posts where status = 'published'
select * from posts where status = 'featured'
select * from posts where status = 'scheduled'

如您所见，它正在对同一个表进行三次不同的查询以检索记录。我们可以重构此代码以仅进行一次数据库查询。

$posts =  Post::whereIn('status',['published', 'featured', 'scheduled'])->get();
$published_posts = $posts->where('status','=','published');
$featured_posts = $posts->where('status','=','featured');
$scheduled_posts = $posts->where('status','=','scheduled');

select * from posts where status in ( 'published', 'featured', 'scheduled' )

上面的代码生成一个查询来检索全部特定状态的帖子，通过状态为返回的帖子创建不同的 collections 。三个不同的状态的变量由一个查询生成。

10. 为常查询的列添加索引

如果查询中含有 where 条件作用于 string 类型的 column ，最好给这列添加索引。通过这列的查询将会快很多。

$posts = Post::where('status','=','published')->get();

上面例子，我们对 status 列添加 where 条件来查询。可以通过下面这样的数据库迁移来优化查询。

Schema::table('posts', function (Blueprint $table) {
   $table->index('status');
});

11. 使用 simplePaginate 而不是 Paginate

分页结果时，我们通常会这样做

$posts = Post::paginate(20);

这将进行两次查询，第一次检索分页结果，第二次表中计算表中的总行数。对表中的行数进行计数是一个缓慢的操作，会对查询性能产生负面影响。

那么为什么 laravel 会计算总行数呢？

为了生成分页连接，Laravel 会计算总行数。因此，当生成分页连接时，您可以预先知道会有多少页，以及过去的页码是多少。

另一方面，执行 simplePaginate 不会计算总行数，查询会比 paginate 方法快得多。但您将无法知道最后一个页码并无法跳转到不同的页面。

如果您的数据库表有很多行，最好避免使用 paginate，而是使用 simplePaginate。

$posts = Post::paginate(20); // 为所有页面生成分页链接
$posts = Post::simplePaginate(20); // 仅生成上一页和下一页的分页链接

什么时候使用分页和简单分页

查看下面的比较表，确定是分页还是简单分页适合您

12. 避免使用前导通配符(LIKE 关键字)

当尝试查询匹配特性模式的结果时，我们通常会使用

select * from table_name where column like %keyword%

上述查询导致全表扫描。如果我们知道出现在列值开头的关键字，我们会查询以下结果。

select * from table_name where column like keyword%

13. 避免 where 子句使用 SQL 函数

最好避免在 where 子句中使用 SQL 函数，因为它们会导致全表扫描。 让我们看下面的例子。要根据特定的时间查询结果，我们通常会这样做

$posts = POST::whereDate('created_at', '>=', now() )->get();

这将导致类似的于下面的查询

select * from posts where date(created_at) >= 'timestamp-here'

上面的查询将导致全表扫描，因为在计算日期函数之前，不会应用 where 条件。

我们可以重构这个函数，以避免使用如下的 date sql 函数

$posts = Post::where('created_at', '>=', now() )->get();

select * from posts where created_at >= 'timestamp-here'

14. 避免在表中添加过多的列

最好限制表中列的总数。可以利用像 mysql 这样的关系数据库将具有如此多列的表拆分为多个表。可以使用它们的主键和外键将它们连接在一起。

向表中添加太多列会增加单个记录的长度，并且会减慢表扫描的速度。在执行 select * 查询时，最终会检索到一些实际上并不需要的列。

15. 将带有文本数据的单独列输入到它们自己的表中

这个技巧来自个人经验，并不是设计数据库表的标准方法。我建议只有当您的表有太多的记录或者会快速增长时才遵循这个技巧。

如果一个表有存储大量数据的列(例如: 数据类型为 TEXT 的列) ，那么最好将它们分离到它们自己的表中，或者分离到一个不经常被询问的表中。

当表中有包含大量数据的列时，单个记录的大小会变得非常大。我个人观察到它影响了我们其中一个项目的查询时间。

假设您有一个名为 posts 的表，其中包含一列 内容，用于存储博客文章内容。博客文章的内容将是真正的巨大和经常的时候，你需要这个数据只有当一个人正在查看这个特定的博客文章。

所以，在数据表中有大量文章记录的时候，将这些长文本字段（大字段）分离到单独的表中将会彻底的改善查询性能。

16. 从表中查询最新记录的最佳实践

当需要从一个数据表中查询最新的记录行时，通常我们会这么做：

$posts = Post::latest()->get();
// or $posts = Post::orderBy('created_at', 'desc')->get();

上面的查询方式将会产生如下 sql 语句：

select * from posts order by created_at desc

这种查询方式基本上都是按照 created_at 字段做降序排列来给查询结果排序的。由于 created_at 字段是字符串类型的数据，所以用这种方式对查询结果进行排序通常会更慢。（译者注：MySQL 的 TIMESTAMP 类型字段是以 UTC 格式存储数据的，形如 20210607T152000Z，所以 created_at 字段确实是字符串类型的数据）。

如果你的数据表中使用了自增长的 id 字段作为主键，那么大多数情况下，最新的数据记录行的 id 字段值也是最大的。因为 id 字段不仅是一个整形数据的字段，而且也是一个主键字段，所以基于 id 字段对查询结果进行排序会更快。所以查询最新记录的最佳实践如下：

$posts = Post::latest('id')->get();
// or $posts = Post::orderBy('id', 'desc')->get();

该方法会产生如下 sql 语句

select * from posts order by id desc

17. 优化 MySQL 的数据插入操作

为了更快地从数据库查询数据，我们已经为 select 方法做了很多优化。 大多数情况下，我们只需要为查询方法进行优化就可以满足性能要求了。 但是很多时候我们还需要为『插入』和『更新』（insert 和 update）方法进行优化。所以我给大家推荐一篇有趣的文章optimizing mysql inserts，这篇文章将有助于优化缓慢的『插入』和『更新』操作。

18. 检查和优化查询方法

在 Laravel 框架中，优化数据查询并没有完全通用的办法。你只能尽量搞清楚下面这些问题：你的程序是如何运行的、进行了多少个数据库查询操作、有多少查询操作是真正必要的。所以请检查你的应用产生的查询操作，这将有助于你确定并减少数据查询操作的总量。

有很多工具可以辅助你检查每个页面产生的查询方法：

注意: 不推荐在生产环境下使用这些工具。在生产环境使用这些工具将会降低你的应用性能，并且会让未经授权的用户获取到程序的敏感信息。

• Laravel Debugbar - Laravel Debugbar has a database tab. Clicking this tab will show you when you open a page All query statements executed by the application. You can browse each page of the application and view the queries used for each page.
• Clockwork - Clockwork is the same as Laravel Debugbar, except that Clockwork will not inject a toolbar on your website. You can use it in the "Developer Tool Window" ( developer tools window ), or enter a separate page to view the application's debugging information by opening the url /yourappurl/clockwork.

• Laravel Telescope - Laravel Telescope is an excellent debugging tool specially provided for developing Laravel applications. Once you have Laravel Telescope installed, you can access its dashboard page by visiting yourappurl/telescope. In the telescope dashboard interface, click to open the queries tab. This page will display all MySQL queries executed by your application.

Original address: https://laravel-news.com/18-tips-to-optimize-your-laravel-database-queries

Translation Address: https://learnku.com/laravel/t/61384

For more programming-related knowledge, please visit: programming video! !

The above is the detailed content of [Compilation and sharing] 18 tips for optimizing database queries in Laravel8. For more information, please follow other related articles on the PHP Chinese website!