[Kompilasi dan perkongsian] 18 petua untuk mengoptimumkan pertanyaan pangkalan data dalam Laravel8

Bagaimanakah Laravel mengoptimumkan pertanyaan pangkalan data? Artikel berikut akan berkongsi dengan anda 18 petua tentang pengoptimuman pertanyaan pangkalan data Laravel8 Saya harap ia akan membantu anda!

Jika apl anda lambat atau mempunyai banyak pertanyaan pangkalan data, ikuti petua pengoptimuman prestasi ini untuk meningkatkan masa muat apl anda. [Cadangan berkaitan: tutorial video laravel]

1 Dapatkan set data yang besar

Petua ini tertumpu terutamanya pada penambahbaikan aplikasi apabila memproses set data yang besar. penggunaan ingatan.

Apabila memproses koleksi yang besar, hasil carian diproses dalam kumpulan dan bukannya pemprosesan carian sekali sahaja.

menunjukkan proses mendapatkan semula data daripada jadual posts seperti berikut.

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

Contoh di atas akan mendapatkan semula semua rekod daripada jadual siaran dan memprosesnya. Bagaimana jika ungkapan ini mencapai lebih 1 juta baris? Memori akan cepat habis.

Untuk mengelakkan masalah semasa memproses set data yang besar, kami boleh mendapatkan subset hasil dan memprosesnya seperti berikut.

Pilihan 1: Gunakan bongkah

// 当使用 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
    }
});

Contoh di atas mendapatkan 100 rekod daripada jadual siaran untuk diproses dan mendapatkan 100 rekod lagi untuk diproses. Lelaran ini berterusan sehingga semua rekod telah diproses.

Pendekatan ini akan mencipta lebih banyak pertanyaan pangkalan data, tetapi akan menjadi lebih cekap memori. Biasanya, pemprosesan set data yang besar harus berlaku di latar belakang. Oleh itu, lebih banyak pertanyaan boleh dijalankan di latar belakang untuk mengelakkan kehabisan memori semasa memproses set data yang besar.

Pilihan 2: Menggunakan Kursor

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

Contoh membuat pertanyaan pangkalan data tunggal, mendapatkan semula semua rekod jadual, memproses model Eloquent satu demi satu. Kaedah ini hanya menanyakan pangkalan data sekali dan mendapat semua siaran. Tetapi gunakan penjana php untuk mengoptimumkan penggunaan memori.

Bilakah saya harus menggunakan ini?

Ini boleh mengoptimumkan penggunaan memori pada lapisan aplikasi, memandangkan penggunaan memori pangkalan data masih sangat tinggi memandangkan kami sedang mendapatkan semula semua data dalam jadual.

Apabila memori pangkalan data besar dan memori aplikasi kecil, disyorkan untuk menggunakan kursor. Walau bagaimanapun, jika pangkalan data anda tidak mempunyai memori yang mencukupi, lebih baik menggunakan ketulan.

Pilihan 3: Gunakan 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 dan chunkById Perbezaan terbesar ialah chunk pass offset dan limit Dapatkan semula data. Walau bagaimanapun, struktur
chunkById diambil melalui medan id. Medan id biasanya merupakan medan integer, dan medan ini juga merupakan medan peningkatan automatik. Pertanyaan untuk

chunk dan chunkById adalah seperti berikut.

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

Biasanya, pertanyaan menggunakan had dan offset adalah lebih perlahan, cuba elakkan menggunakannya. Artikel ini memperincikan isu penggunaan offset.

chunkById menggunakan medan integer id dan pertanyaan melalui where clause, yang akan menjadi lebih pantas.

Bila hendak menggunakan chunkById?

• Digunakan apabila pangkalan data mempunyai autokenaikan 主键.

2. Pilih lajur yang sesuai

Biasanya apabila mendapatkan data daripada pangkalan data, anda akan melakukan sesuatu seperti berikut.

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

Kod di atas akan mendapat pertanyaan berikut

select * from posts where id = 1 limit 1

select * bermaksud mencari semua lajur daripada jadual.
Ini tiada masalah apabila semua lajur diperlukan.

Walau bagaimanapun, apabila hanya lajur yang ditentukan (id, tajuk) diperlukan, cuma dapatkan lajur tersebut seperti di bawah.

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

Kod di atas mendapat pertanyaan berikut

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

3 Apabila satu atau dua lajur jadual pangkalan data diperlukan

Perkara ini tertumpu terutamanya pada masa pemprosesan hasil carian. Ini tidak menjejaskan masa pertanyaan sebenar.

Seperti yang saya nyatakan di atas, untuk mendapatkan semula lajur yang ditentukan, anda boleh melakukan ini

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

Laksanakan kod di atas dan ia akan melakukan perkara berikut di belakang tabir.

• Laksanakan select title, slug from posts pertanyaan
• Setiap baris yang diambil sepadan dengan objek model Post (untuk objek PHP) (pembina pertanyaan mendapat objek PHP standard)
• Jana koleksi untuk Post model
• Pulangan koleksi

Akses data

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

Pendekatan di atas mempunyai overhed tambahan, mencipta untuk setiap baris Post model dan buat koleksi untuk objek ini. Ini ialah pendekatan yang betul jika anda benar-benar memerlukan Post contoh model dan bukannya data.

Tetapi jika anda hanya memerlukan dua nilai, anda boleh melakukan perkara berikut:

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

Apabila kod di atas dilaksanakan, ia melakukan perkara berikut di belakang tabir.

• 对数据库执行 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&#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>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 mempunyai tab database akan menunjukkan kepada anda perkara yang dilakukan oleh aplikasi apabila anda membuka halaman Semua pernyataan pertanyaan. Anda boleh menyemak imbas setiap halaman aplikasi dan melihat pertanyaan yang digunakan untuk setiap halaman.
• Clockwork - Clockwork adalah sama dengan Laravel Debugbar, kecuali Clockwork tidak akan menyuntik bar alat pada tapak web anda. Anda boleh menggunakannya dalam "Developer Tool Window " ( developer tools window ), atau masukkan halaman berasingan untuk melihat maklumat penyahpepijatan aplikasi dengan membuka url /yourappurl/clockwork.

• Laravel Telescope - Laravel Telescope ialah alat nyahpepijat yang sangat baik yang direka untuk membangunkan aplikasi Laravel. Sebaik sahaja anda memasang Teleskop Laravel, anda boleh mengakses halaman papan pemukanya dengan melawati alamat yourappurl/telescope. Dalam antara muka papan pemuka teleskop, klik untuk membuka tab queries Halaman ini akan memaparkan semua pertanyaan MySQL yang dilaksanakan oleh aplikasi anda.

Alamat asal: https://laravel-news.com/18-tips-to-optimize-your-laravel-database-queries

Terjemahan Alamat: https://learnku.com/laravel/t/61384

Untuk lebih banyak pengetahuan berkaitan pengaturcaraan, sila lawati: Video Pengaturcaraan! !

Atas ialah kandungan terperinci [Kompilasi dan perkongsian] 18 petua untuk mengoptimumkan pertanyaan pangkalan data dalam Laravel8. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!