Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of how to optimize the database query in Laravel8, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value. I believe you will gain something after reading this Laravel8 article on how to optimize the database query. Let's take a look.

If the application runs slowly or there are a large number of database queries, follow the following performance tuning tips to reduce the load time of the application.

1. Retrieve large datasets

This tip focuses primarily on improving the memory usage of applications when working with large datasets.

When dealing with large collections, grouping retrieval results processing, rather than one-time retrieval processing.

The process of retrieving data from the posts table is shown below.

$posts = Post::all (); / / use eloquent$posts = DB::table ('posts')-> get (); / / use query constructor foreach ($posts as $post) {/ / handle posts operations}

The above example retrieves all records from the posts table and processes them. What if the table reaches more than 1 million rows? Memory will soon be exhausted.

To avoid problems when dealing with large datasets, we can retrieve a subset of the results and process them as follows.

Option 1: use chunk// $posts = Post::chunk when using eloquent (100, function ($posts) {foreach ($posts as $post) {/ / Process posts}}); / / when using query constructor $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 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. In general, the processing of large datasets should be carried out in the background. As a result, more queries can be made while running in the background to avoid running out of memory when working with large datasets.

Option 2: use cursors / / use eloquentforeach (Post::cursor () as $post) {/ / process a single post} / / use query builder foreach (DB::table ('posts')-> cursor () as $post) {/ / process a single post}

The example makes a single database query, retrieves all the records of the table, and processes the Eloquent model one by one. In this way, the database is queried only once to get all the posts. You can use the php generator to optimize memory usage.

Under what circumstances do you use this?

This greatly optimizes memory usage at the application layer, and the database memory footprint is still high because we retrieve all the data in the table.

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

Option 3: use chunkById// to use eloquent$posts = Post::chunkById (100, function ($posts) {foreach ($posts as $post) {/ / process posts}}); / / use the query constructor $posts = DB::table ('posts')-> chunkById (100, function ($posts) {foreach ($posts as $post) {/ / process posts}})

The biggest difference between chunk and chunkById is that chunk retrieves data through offset and limit. However,

ChunkById retrieves the structure through the id field. The id field is usually an integer field, and it is also a self-increasing field.

The queries for chunk and chunkById are as follows.

Chunk

Select * from posts offset 0 limit 100select * from posts offset 101 limit 100

ChunkById

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

In general, queries using limit and offset are slow and should be avoided as much as possible.

ChunkById uses id integer fields, which are queried through where clause, which is faster.

When do I use chunkById?

Used when there is a self-increasing primary key in the database.

two。 Select the appropriate column

Typically, when retrieving data from a database, you will do something like this.

$posts = Post::find (1); / / use eloquent$posts = DB::table ('posts')-> where (' id','=',1)-> first (); / / use the query builder

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 not a problem when all columns are needed.

However, when only the specified columns (id,title) are needed, only those columns need to be retrieved as follows.

$posts = Post::select (['id','title'])-> find (1); / / use eloquent$posts = DB::table (' posts')-> where ('id','=',1)-> select ([' id','title'])-> first (); / / use query builder

The above code gets the following query

Select id,title from posts where id = 1 limit 13. When one or two columns of a database table are needed

This is mainly concerned with the processing time of the search results. This does not affect the actual query time.

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

$posts = Post::select (['title','slug'])-> get (); / / use eloquent$posts = DB::table (' posts')-> select (['title','slug'])-> get (); / / use the query builder

Execute the above code, and it does the following behind the scenes.

Execute select title, slug from posts query

Each row retrieved corresponds to a Post model object (for PHP objects) (the query builder gets the standard PHP object)

Generate collection for Post model

Return to collection

Access data

Foreach ($posts as $post) {/ / $post is the Post model or php standard object $post- > title; $post- > slug;}

The above approach has the extra overhead of creating a Post model for each row and creating a collection for these objects. If you do need Post model instances instead of data, this is the right thing to do.

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

$posts = Post::pluck ('title','slug'); / / $posts = DB::table (' posts')-> pluck ('title','slug') when using eloquent; / / when using query constructor

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

Execute select title, slug from posts queries against the database

Create an array with title as the array value and slug as the array key

Returns an array (array format: [slug = > title, slug = > title])

To access the results, we can do this

Foreach ($posts as $slug = > $title) {/ / $title is the title of post / / $slug is the slug of post}

If you want to retrieve a column, you can do this

$posts = Post::pluck ('title'); / / $posts = DB::table (' posts')-> pluck ('title') when using eloquent; / / foreach ($posts as $title) {/ / $title is the title} of post when using query constructor

The above approach eliminates the creation of each row of Post objects. This will reduce the memory and time consumption of query result processing.

The above approach is recommended in the new code. Personally, I don't think it's worth taking the time to follow the above tips to ReFactor the code.

Refactoring code, preferably when dealing with large datasets or when it is relatively idle

4. Use queries instead of collection to count rows

The number of rows in a statistical table, which is usually done

$posts = Post::all ()-> count (); / / use eloquent$posts = DB::table ('posts')-> get ()-> count (); / / use query constructor

This will generate the following query

Select * from posts

The above method retrieves all rows from the table. Load them into the collection object and calculate the results. This works when there are fewer rows in the data table. But as the table grows, memory runs out quickly.

Unlike the above method, we can directly calculate the total number of rows in the database itself.

$posts = Post::count (); / / $posts = DB::table ('posts')-> count () when using eloquent; / / when using query constructor

This will generate the following query

Select count (*) from posts

Counting rows in sql is a slow process, and performance can be poor when there are multiple rows in a database table. It is best to avoid counting rows as much as possible.

5. Avoid n + 1 queries by loading relationships immediately

You may have heard this advice countless times. So I'll be as brief as possible. Let's assume that you have the following scenarios

Class PostController extends Controller {public function index () {$posts = Post::all (); return view ('posts.index', [' posts' = > $posts]);}} / / posts/index.blade.php file @ foreach ($posts as $post) {{$post- > title}}

Author: {{$post- > author- > name}}

@ endforeach

The above code retrieves all posts and displays the post title and author on the web page, assuming that the post model is associated with the author.

Executing the above code will cause the following query to be run.

Select * from posts / / suppose five pieces of data are returned 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}

As above, there are 1 query to retrieve the post and 5 queries to retrieve the author of the post (fake 5 posts). So for each post, a separate query is made to retrieve its author.

So if there are N posts, there will be 1 query (1 query to retrieve posts, N queries to retrieve the author of each post). This is often referred to as a Number1 query problem.

To avoid this problem, you can preload the author of the post as follows.

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

Execute the above code to get the following query:

Select * from posts / / Assume this query returned 5 postsselect * from authors where id in ({post1.author_id}, {post2.author_id}, {post3.author_id}, {post4.author_id}, {post5.author_id}) 6. Preload nesting relationship

From the above example, consider that the author belongs to a group and needs to display the name of the group. So in the blade file, you can do this as follows.

@ foreach ($posts as $post) {{$post- > title}}

Author: {{$post- > author- > name}}

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

@ endforeach

Then

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

Get the following query:

Select * from posts / / Assume this query returned 5 postsselect * 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}

As above, although the authors relationship is preloaded, a large number of queries are generated. This is because the team relationship on authors is not preloaded.

Solve this by doing the following.

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

Execute to get the following query.

Select * from posts / / Assume this query returned 5 postsselect * 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})

By preloading nested relationships, you can reduce the number of queries from 11 to 3.

7. Do not preload belongsTo relationships if only id is needed

Imagine having two tables, posts and authors. The post table has the author_id column belonging to the author table.

To get the id of the author of the post, you usually do this

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

Execute to get two queries.

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

However, the author id can be obtained directly in the following ways.

$post = Post::findOrFail (); $post- > author_id; / / the post table has an author_id column for the author's id

When will the above method be adopted?

To take the above approach, you need to ensure that the author associated with the post always exists in the author table.

8. Avoid using unnecessary queries

In many cases, some database queries are unnecessary. Look at the following example.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.