Laravel8中怎么优化数据库查询?18 个优化建议分享
时间:2022-04-14 16:34
Laravel8中怎么优化数据库查询?本篇文章给大家整理总结18 个 Laravel 8 数据库查询优化建议,希望对大家有所帮助! 如果应用运行缓慢或存在大量数据库查询,请按照以下性能优化提示来缩短应用的加载时间。 本提示主要侧重于提高处理大型数据集时应用的内存使用率。 处理大的集合时,分组检索结果处理,而不是一次性检索处理。 如下展示了从 上面的例子会从 posts 表检索所有的记录并处理。如果这个表达到了 100 多万行呢?内存将很快被耗尽。 为了避免在处理大型数据集时出现问题,我们可以检索结果子集并按照下面的方式处理它们。 以上例子从 posts 表中检索 100 条记录对其进行处理,另外再检索 100 条记录进行处理。此迭代将继续,直到处理完所有记录。 这种方法将创建更多的数据库查询,但内存效率会更高。 通常, 大型数据集的处理应该再后台进行。因此,可以在后台运行时进行更多查询,以避免在处理大型数据集时耗尽内存。 示例进行单个数据库查询,检索表的所有记录,一个接一个一个处理 Eloquent 模型。这种方式仅查询一次数据库,得到全部 posts 。 但使用 php 生成器 优化内存使用。 什么情况使用这个呢? 这能够在应用层极大地优化内存使用,由于我们检索表的所有数据,数据库内存占用任然很高。 在数据库内存较多,应用内存较少的时候,建议使用游标。然而,如果你的数据库没有足够的内存,最好使用 chunks 。 chunk chunkById 通常,查询使用 limit 和 offset 是较慢的,尽量避免使用。本文 详细介绍使用 offset 的问题。 chunkById 使用 id 整型字段,通过 什么时候使用 chunkById ? 通常从数据库检索数据时,会像下面这样做。 上面的代码会得到如下的查询 然而,仅需要指定的列(id,title)时,只需要像下面这样检索那些列。 上面代码得到如下查询 这点主要关注对检索结果的处理时间。这不影响实际的查询时间。 如我上面提到的,检索指定的列,可以这样做 执行上面的代码,它会在幕后执行以下操作。 访问数据 上面的方式有额外的开销,为每一行创建 但如果您只需要两个值时,则可以执行以下操作: 当上面代码被执行时,它在幕后会执行以下操作。 要访问结果,我们可以这么做 如果您想检索一列,您可以这么做 上面的方式消除了每一行 建议在新代码中使用上述方式。个人感觉不值得花时间遵循上面的提示重构代码。 统计表的行数,通常这样做 这将生成以下查询 上述方法将从表中检索所有行。将它们加载到 与上述方法不同,我们可以直接计算数据库本身的总行数。 这将生成以下查询 在 sql 中计算行数是一个缓慢的过程,当数据库表中有多行时性能会很差。最好尽量避免计算行数。 这条建议你可能听说过无数次了。所以我会尽可能简短。让我们假设您有以下场景 上面的代码是检索所有的帖子,并在网页上显示帖子标题和作者,假设帖子模型关联 执行以上代码将导致运行以下查询。 如上,1 条查询来检索帖子,5 条查询来检索帖子的作者(假设有 5 篇帖子)。因此对于每篇帖子,都会进行一个单独的查询来检索它的作者。 所以如果有 N 篇帖子,将会产生 N+1 条查询(1 条查询检索帖子,N 条查询检索每篇帖子的作者)。这常被称作 N+1 查询问题。 避免这个问题,可以像下面这样预加载帖子的作者。 执行上面的代码得到下面的查询: 从上面的例子,考虑作者归属于一个组,同时需要显示组的名字的情况。因此在 blade 文件中,可以按下面这样做。 接着 得到下面的查询: 如上,尽管预加载了 通过下面这样来解决这个它。 执行得到下面的查询。 通过预加载嵌套关系,可以将查询数从 11 减到 3。 想象一下,有 为了得到帖子的作者 id,通常这样做 执行得到两个查询。 然而,可以直接通过下面方式得到作者 id 。 什么时候采取上面的方式? 采取上的方式,需要确保帖子关联的作者在作者表始终存在。 很多时候,一些数据库查询是不必要的。看看下面的例子。 上面代码是从两张不同的表( 正如你上面看到的, 问题是,当我们在做 我们进行两次查询。一次从 我们可以调整逻辑,避免额外的查询。 将逻辑更改为上述内容后,我们对管理员用户进行了两次查询,并对其他用户进行了一次查询。 我们有时需要进行查询以同一个表中检索不同类型的行。 上述代码正从同一个表检索状态不同的行。代码将进行以下查询。 如您所见,它正在对同一个表进行三次不同的查询以检索记录。我们可以重构此代码以仅进行一次数据库查询。 上面的代码生成一个查询来检索全部特定状态的帖子,通过状态为返回的帖子创建不同的 collections 。三个不同的状态的变量由一个查询生成。 如果查询中含有 上面例子,我们对 分页结果时,我们通常会这样做 这将进行两次查询,第一次检索分页结果,第二次表中计算表中的总行数。对表中的行数进行计数是一个缓慢的操作,会对查询性能产生负面影响。 那么为什么 laravel 会计算总行数呢? 为了生成分页连接,Laravel 会计算总行数。因此,当生成分页连接时,您可以预先知道会有多少页,以及过去的页码是多少。 另一方面,执行 如果您的数据库表有很多行,最好避免使用 什么时候使用分页和简单分页 查看下面的比较表,确定是分页还是简单分页适合您 当尝试查询匹配特性模式的结果时,我们通常会使用 上述查询导致全表扫描。如果我们知道出现在列值开头的关键字,我们会查询以下结果。 最好避免在 where 子句中使用 SQL 函数,因为它们会导致全表扫描。 让我们看下面的例子。要根据特定的时间查询结果,我们通常会这样做 这将导致类似的于下面的查询 上面的查询将导致全表扫描,因为在计算 我们可以重构这个函数,以避免使用如下的 最好限制表中列的总数。可以利用像 mysql 这样的关系数据库将具有如此多列的表拆分为多个表。可以使用它们的主键和外键将它们连接在一起。 向表中添加太多列会增加单个记录的长度,并且会减慢表扫描的速度。在执行 这个技巧来自个人经验,并不是设计数据库表的标准方法。我建议只有当您的表有太多的记录或者会快速增长时才遵循这个技巧。 如果一个表有存储大量数据的列(例如: 数据类型为 TEXT 的列) ,那么最好将它们分离到它们自己的表中,或者分离到一个不经常被询问的表中。 当表中有包含大量数据的列时,单个记录的大小会变得非常大。我个人观察到它影响了我们其中一个项目的查询时间。 假设您有一个名为 所以,在数据表中有大量文章记录的时候,将这些长文本字段(大字段)分离到单独的表中将会彻底的改善查询性能。 当需要从一个数据表中查询最新的记录行时,通常我们会这么做: 上面的查询方式将会产生如下 sql 语句: 这种查询方式基本上都是按照 如果你的数据表中使用了自增长的 该方法会产生如下 sql 语句 为了更快地从数据库查询数据,我们已经为 在 Laravel 框架中,优化数据查询并没有完全通用的办法。你只能尽量搞清楚下面这些问题:你的程序是如何运行的、进行了多少个数据库查询操作、有多少查询操作是真正必要的。所以请检查你的应用产生的查询操作,这将有助于你确定并减少数据查询操作的总量。 有很多工具可以辅助你检查每个页面产生的查询方法: 注意: 不推荐在生产环境下使用这些工具。在生产环境使用这些工具将会降低你的应用性能,并且会让未经授权的用户获取到程序的敏感信息。 原文地址:https://laravel-news.com/18-tips-to-optimize-your-laravel-database-queries 【相关推荐:laravel视频教程】 以上就是Laravel8中怎么优化数据库查询?18 个优化建议分享的详细内容,更多请关注gxlsystem.com其它相关文章!1. 检索大型数据集
posts
表检索数据的过程。$posts = Post::all(); // 使用 eloquent
$posts = DB::table('posts')->get(); // 使用查询构造器
foreach ($posts as $post){
// 处理 posts 操作
}
选项 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
}
});
选项 2: 使用游标
// 使用 eloquent
foreach (Post::cursor() as $post){
// 处理单个 post
}
// 使用 query 构建器
foreach (DB::table('posts')->cursor() as $post){
// 处理单个 post
}
选项 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
的查询如下。select * from posts offset 0 limit 100
select * from posts offset 101 limit 100
select * from posts order by id asc limit 100
select * from posts where id > 100 order by id asc limit 100
where clause
查询,这样会更快。主键
的时候使用。2. 选择合适的列
$posts = Post::find(1); // 使用 eloquent
$posts = DB::table('posts')->where('id','=',1)->first(); // 使用 query 构建器
select * from posts where id = 1 limit 1
select *
表示从表中查出所有列。
当需要所有列时,这没有问题。$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
模型生成 collectionforeach ($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
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 }
$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. 预加载嵌套关系
@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 } )
7. 如果仅需要 id 时,别预加载 belongsTo 关系
posts
和 authors
两张表。帖子表有 author_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
$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' )
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);
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 函数
$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. 避免在表中添加过多的列
select *
查询时,最终会检索到一些实际上并不需要的列。15. 将带有文本数据的单独列输入到它们自己的表中
posts
的表,其中包含一列 内容
,用于存储博客文章内容。博客文章的内容将是真正的巨大和经常的时候,你需要这个数据只有当一个人正在查看这个特定的博客文章。16. 从表中查询最新记录的最佳实践
$posts = Post::latest()->get();
// or $posts = Post::orderBy('created_at', 'desc')->get();
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();
select * from posts order by id desc
17. 优化 MySQL 的数据插入操作
select
方法做了很多优化。 大多数情况下,我们只需要为查询方法进行优化就可以满足性能要求了。 但是很多时候我们还需要为『插入』和『更新』(insert
和 update
)方法进行优化。所以我给大家推荐一篇有趣的文章optimizing mysql inserts,这篇文章将有助于优化缓慢的『插入』和『更新』操作。18. 检查和优化查询方法
database
选项卡,点击该选项卡将会展示你打开一个页面时应用程序执行的所有查询语句。你可以浏览应用的每个页面并查看每个页面用到的查询语句。developer tools window
),或者通过打开 url /yourappurl/clockwork
进入一个单独的页面来查看应用的调试信息。yourappurl/telescope
地址进入它的仪表盘页面。在 telescope 的仪表盘界面,点击打开 queries
标签页,这个页面将会展示你的应用执行过的所有 MySQL 查询语句。