18 个 Laravel 8 数据库查询优化建议

摈弃世俗浮躁，追求技术精湛

转载自 Laravel 论坛：https://learnku.com/laravel/t/61384

如果应用运行缓慢或存在大量数据库查询，请按照以下性能优化提示来缩短应用的加载时间。

1. 检索大型数据集

本提示主要侧重于提高处理大型数据集时应用的内存使用率。

处理大的集合时，分组检索结果处理，而不是一次性检索处理。

如下展示了从 posts 表检索数据的过程。

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

上面的例子会从 posts 表检索所有的记录并处理。如果这个表达到了 100 多万行呢？内存将很快被耗尽。

为了避免在处理大型数据集时出现问题，我们可以检索结果子集并按照下面的方式处理它们。

选项 1: 使用 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
    }
});

以上例子从 posts 表中检索 100 条记录对其进行处理，另外再检索 100 条记录进行处理。此迭代将继续，直到处理完所有记录。

这种方法将创建更多的数据库查询，但内存效率会更高。通常, 大型数据集的处理应该再后台进行。因此，可以在后台运行时进行更多查询，以避免在处理大型数据集时耗尽内存。

选项 2: 使用游标

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

示例进行单个数据库查询，检索表的所有记录，一个接一个一个处理 Eloquent 模型。这种方式仅查询一次数据库，得到全部 posts 。但使用 php 生成器优化内存使用。

什么情况使用这个呢？

这能够在应用层极大地优化内存使用，由于我们检索表的所有数据，数据库内存占用任然很高。

在数据库内存较多，应用内存较少的时候，建议使用游标。然而，如果你的数据库没有足够的内存，最好使用 chunks 。

选项 3: 使用 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 和 chunkById 最大的区别是 chunk 通过offset 和 limit 检索数据。然而 chunkById 通过id 字段检索结构。id 字段通常是整型字段，而且它也是自增字段。

chunk 和 chunkById 的查询如下。

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

通常，查询使用 limit 和 offset 是较慢的，尽量避免使用。本文详细介绍使用 offset 的问题。

chunkById 使用 id 整型字段，通过 where clause 查询，这样会更快。

什么时候使用 chunkById ？

当数据库存在自增 主键 的时候使用。

2. 选择合适的列

通常从数据库检索数据时，会像下面这样做。

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

上面的代码会得到如下的查询

select * from posts where id = 1 limit 1

select * 表示从表中查出所有列。当需要所有列时，这没有问题。

然而，仅需要指定的列（id，title）时，只需要像下面这样检索那些列。

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

上面代码得到如下查询

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

3. 当需要数据库表的一两个列时

这点主要关注对检索结果的处理时间。这不影响实际的查询时间。

如我上面提到的，检索指定的列，可以这样做

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

执行上面的代码，它会在幕后执行以下操作。

执行 select title, slug from posts 查询
检索出的每一行对应一个 Post 模型对象（对 PHP 对象）(query 构建器得到标准的 PHP 对象)
为 Post 模型生成 collection
返回 collection

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

上面的方式有额外的开销，为每一行创建 Post 模型，并为这些对象创建一个集合。如果的确需要 Post 模型实例而不是数据，这是最正确的做法。

但如果您只需要两个值时，则可以执行以下操作：

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

当上面代码被执行时，它在幕后会执行以下操作。

对数据库执行 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)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
    </li>
@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)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
        <p>Author's Team: {{ $post->author->team->name }}</p>
    </li>
@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 id>);
$post->author->id;

执行得到两个查询。

select * from posts where id = <post id> limit 1
select * from authors where id = <post author id> limit 1

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

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

什么时候采取上面的方式？
采取上的方式，需要确保帖子关联的作者在作者表始终存在。

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

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

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

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

// posts/index.blade.php
 @if( request()->user()->isAdmin() )
    <h2>Private Posts</h2>
    <ul>
        @foreach($private_posts as $post)
            <li>
                <h3>{{ $post->title }}</h3>
                <p>Published At: {{ $post->published_at }}</p>
            </li>
        @endforeach
    </ul>
@endif
 <h2>Posts</h2>
<ul>
    @foreach($posts as $post)
        <li>
            <h3>{{ $post->title }}</h3>
            <p>Published At: {{ $post->published_at }}</p>
        </li>
    @endforeach
</ul>

正如你上面看到的，$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); // 仅生成上一页和下一页的分页链接

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

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

| | paginate / simplePaginate | | --- | --- | | 数据库表只有很少行，并且不会变大 | paginate / simplePaginate | | 数据库表有很多行，并且增长很快 | simplePaginate | | 必须提供用户选项以跳转到特定页面 | paginate | | 必须向用户显示结果总数 | paginate | | 不主动使用分页链接 | simplePaginate | | UI/UX 不会影响从切换编号分页链接到下一个/上一个分页链接 | simplePaginate | | 使用“加载更多”按钮或“无限滚动”分页 | simplePaginate |

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 有一个 database选项卡，点击该选项卡将会展示你打开一个页面时应用程序执行的所有查询语句。你可以浏览应用的每个页面并查看每个页面用到的查询语句。
Clockwork - Clockwork 与 Laravel Debugbar 一样，只不过 Clockwork 不会在你的网站上注入一个工具栏，你可以在『开发者工具窗口』( developer tools window )，或者通过打开 url /yourappurl/clockwork 进入一个单独的页面来查看应用的调试信息。
Laravel Telescope - Laravel Telescope 是一个专为开发 Laravel 应用而提供的十分优秀的调试工具。一旦你安装了 Laravel Telescope，便可以通过访问 yourappurl/telescope 地址进入它的仪表盘页面。在 telescope 的仪表盘界面，点击打开 queries 标签页，这个页面将会展示你的应用执行过的所有 MySQL 查询语句。

讨论请前往专业的 Laravel 论坛：https://learnku.com/laravel/t/61384

18 个 Laravel 8 数据库查询优化建议

18 个 Laravel 8 数据库查询优化建议

1. 检索大型数据集

选项 1: 使用 chunk

选项 2: 使用游标

选项 3: 使用 chunkById

2. 选择合适的列

3. 当需要数据库表的一两个列时

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

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

6. 预加载嵌套关系

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

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

9. 合并相似的查询

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

11. 使用 simplePaginate 而不是 Paginate

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

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

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

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

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

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

18. 检查和优化查询方法

Recommend

The democratization of software with low-code/no-code platforms

The Rent Versus Buy of Career Growth

甘肃启动自然灾害救助Ⅲ级应急响应

What Do People Want In A Co-Founder? YCombinator Has Some Answers

What Happened When The Remote Irish Island Of Arranmore Finally Got Broadband In...

Robo-cat waiters come to restaurant chain and other news

Microsoft acquires Clear Software to strengthen process automation offerings

浪潮彭震：数字经济提速，数据存储产业迎来新机遇

Nearly 45 million received scam calls in three months, Ofcom says

Bright Sparks:The Spark Festival kicks off this week - here are 5 events to ligh...

About Joyk