[コンパイルと共有] Laravel8 でデータベースクエリを最適化するための 18 のヒント-Laravel-php.cn

ホームページ

PHPフレームワーク

Laravel

[コンパイルと共有] Laravel8 でデータベースクエリを最適化するための 18 のヒント

青灯夜游

Dec 20, 2022 pm 10:33 PM

phplaravellaravel8

Laravel はデータベースクエリをどのように最適化しますか?次の記事では、Laravel8 データベースクエリの最適化に関する 18 のヒントを紹介します。

[コンパイルと共有] Laravel8 でデータベースクエリを最適化するための 18 のヒント

#アプリが遅い場合、またはデータベースクエリが多い場合は、次のパフォーマンス最適化のヒントに従ってアプリの読み込み時間を改善してください。 [関連する推奨事項: laravel ビデオチュートリアル ]

1. 大きなデータセットを取得する

このヒントは主に、大きなデータを処理する際のアプリケーションの改善に焦点を当てています。メモリ使用量を設定します。

大規模なコレクションを処理する場合、1 回限りの検索処理ではなく、グループ検索結果が処理されます。

以下は、posts テーブルからデータを取得するプロセスを示しています。

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

上の例では、posts テーブルからすべてのレコードを取得して処理します。この式が 100 万行を超えたらどうなるでしょうか?メモリはすぐになくなってしまいます。

大規模なデータセットを処理する際の問題を回避するために、結果のサブセットを取得して、次のように処理できます。

オプション 1: チャンクを使用する

// 当使用 eloquent 时
$posts = Post::chunk(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});
 // 当使用查询构造器时
$posts = DB::table(&#39;posts&#39;)->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(&#39;posts&#39;)->cursor() as $post){
   // 处理单个 post
}

例テーブルのすべてのレコードを取得する単一のデータベースクエリを作成し、Eloquent モデルを順番に処理します。このメソッドはデータベースに 1 回クエリを実行するだけで、すべての投稿を取得します。ただし、メモリ使用量を最適化するには、php ジェネレーターを使用してください。

いつ使用すればよいですか?

これにより、アプリケーション層でのメモリ使用量を大幅に最適化できます。テーブル内のすべてのデータを取得するため、データベースのメモリ使用量は依然として非常に高くなります。

データベースのメモリが多く、アプリケーションのメモリが少ない場合は、カーソルを使用することをお勧めします。ただし、データベースに十分なメモリがない場合は、チャンクを使用することをお勧めします。

オプション 3: chunkById を使用する

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

chunk chunkById の最大の違いはチャンクです。 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 を使用したクエリは時間がかかるため、使用は避けてください。この記事では、オフセットを使用する場合の問題について詳しく紹介します。

chunkById は id 整数フィールドを使用し、where 句 を通じてクエリを実行するため、高速になります。

chunkById をいつ使用するか?

データベースに自動インクリメントされる 主キーがある場合に使用されます。

2. 適切な列を選択します

通常、データベースからデータを取得するときは、次の操作を行います。

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

上記のコードは次のクエリを取得します

select * from posts where id = 1 limit 1

select * はテーブルからすべての列を検索することを意味します。
すべての列が必要な場合、これは問題ありません。

ただし、指定した列(id、title)のみが必要な場合は、以下のように列を取得するだけで済みます。

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

上記のコードは次のクエリを取得します

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

3. データベーステーブルの 1 つまたは 2 つの列が必要な場合

これ主な懸念事項は、検索結果の処理時間です。これは実際のクエリ時間には影響しません。

上で述べたように、指定された列を取得するには、これを行うことができます。

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

上記のコードを実行すると、バックグラウンドで次の操作が実行されます。

実行 select title、slug from 投稿 クエリ
取得された各行は、Post モデルオブジェクト (PHP オブジェクトの場合) に対応します (クエリビルダーは標準 PHP オブジェクトを取得します)
Post モデルのコレクションを生成
コレクションを返す

データにアクセス

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

上記のアプローチには、行ごとに Post モデルを作成し、これらのオブジェクトのコレクションを作成するという追加のオーバーヘッドがあります。データではなく Post モデルインスタンスが本当に必要な場合、これが最も正しいアプローチです。

ただし、必要な値が 2 つだけの場合は、次のようにすることができます。

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

上記のコードが実行されると、バックグラウンドで次の処理が行われます。

对数据库执行 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(&#39;title&#39;); // 使用 eloquent 时
$posts = DB::table(&#39;posts&#39;)->pluck(&#39;title&#39;); // 使用查询构造器时
foreach ($posts as  $title){
    // $title 是 post 的 title
}

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

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

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

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

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

这将生成以下查询

select * from posts

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

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

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

这将生成以下查询

select count(*) from posts

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

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

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

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

// posts/index.blade.php 文件
 @foreach($posts as $post)
    <li>
        <h3 id="nbsp-post-title-nbsp">{{ $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([&#39;author&#39;])->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 id="nbsp-post-title-nbsp">{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
        <p>Author&#39;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(&#39;posts.index&#39;, [&#39;posts&#39; => $posts, &#39;private_posts&#39; => $private_posts ]);
    }
}

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

// posts/index.blade.php
 @if( request()->user()->isAdmin() )
    <h2 id="Private-nbsp-Posts">Private Posts</h2>
    <ul>
        @foreach($private_posts as $post)
            <li>
                <h3 id="nbsp-post-title-nbsp">{{ $post->title }}</h3>
                <p>Published At: {{ $post->published_at }}</p>
            </li>
        @endforeach
    </ul>
@endif
 <h2 id="Posts">Posts</h2>
<ul>
    @foreach($posts as $post)
        <li>
            <h3 id="nbsp-post-title-nbsp">{{ $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(&#39;status&#39;,&#39;=&#39;,&#39;published&#39;)->get();
$featured_posts = Post::where(&#39;status&#39;,&#39;=&#39;,&#39;featured&#39;)->get();
$scheduled_posts = Post::where(&#39;status&#39;,&#39;=&#39;,&#39;scheduled&#39;)->get();

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

select * from posts where status = &#39;published&#39;
select * from posts where status = &#39;featured&#39;
select * from posts where status = &#39;scheduled&#39;

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

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

select * from posts where status in ( &#39;published&#39;, &#39;featured&#39;, &#39;scheduled&#39; )

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

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

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

$posts = Post::where(&#39;status&#39;,&#39;=&#39;,&#39;published&#39;)->get();

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

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

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(&#39;created_at&#39;, &#39;>=&#39;, now() )->get();

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

select * from posts where date(created_at) >= &#39;timestamp-here&#39;

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

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

$posts = Post::where(&#39;created_at&#39;, &#39;>=&#39;, now() )->get();

select * from posts where created_at >= &#39;timestamp-here&#39;

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

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

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

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

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

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

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

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

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

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

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

$posts = Post::latest()->get();
// or $posts = Post::orderBy(&#39;created_at&#39;, &#39;desc&#39;)->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(&#39;id&#39;)->get();
// or $posts = Post::orderBy(&#39;id&#39;, &#39;desc&#39;)->get();

该方法会产生如下 sql 语句

select * from posts order by id desc

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

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

18. 检查和优化查询方法

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

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

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

Laravel デバッグバー - Laravel デバッグバーには database タブがあり、このタブをクリックするとページを開くと表示されます。アプリケーションによって実行されるクエリステートメント。アプリケーションの各ページを参照し、各ページで使用されているクエリを表示できます。
Clockwork - Clockwork は Laravel Debugbar と同じですが、Clockwork が Web サイトにツールバーを挿入しない点が異なります。「開発者ツールウィンドウ」で使用できます。 " ( 開発者ツールウィンドウ )、または URL /yourappurl/クロックワーク を開いて別のページに入り、アプリケーションのデバッグ情報を表示します。
Laravel Telescope - Laravel Telescope は、Laravel アプリケーションの開発のために特別に提供される優れたデバッグツールです。 Laravel Telescope をインストールしたら、yourappurl/telescope にアクセスしてダッシュボードページにアクセスできます。 Telescope ダッシュボードインターフェイスで、[queries] タブをクリックして開きます。このページには、アプリケーションによって実行されたすべての MySQL クエリが表示されます。