In the Laravel project, as the traffic increases, it is not uncommon for database to query speed. Recently, when I optimized the back end of a real estate platform, I encountered this problem and learned some lessons from it.

Database optimization is one of the key areas of developmentable and high -performance applications. It can improve the data retrieval speed, shorten the response time and page loading time, and reduce the server load, and minimize the cost.

The challenge of the real estate platform

Imagine: You build an excellent real estate platform that serves multiple cities and is equipped with advanced search filters. The real estate list loads fast, the search filter responds rapidly, and everything looks perfect. However, with the expansion of the application scale and the growth of the user base, those inquiries that have performed perfectly during the development process have begun to perform longer and longer. Does it sound familiar?

This is exactly what our platform encountered. The SENTRY alert has a slow database query in the production environment, which gives us a warning. The monitoring shows that the search results query takes more than 5 seconds to complete -this is far from the fast experience we promise!

Common query defects (how to avoid)

1. n 1 Inquiry Question: The secret enemy of the database

Do you remember when you play the game, will defeating one enemy produce multiple smaller enemies? This is essentially the same as the N 1 query problem in Laravel. You get the list of real estate, and then make additional inquiries for each property to obtain related data. Before you realize, your database is dealing with hundreds of inquiries, not only one.

The following is its typical expression:

<code>// 優(yōu)化前
$properties = Property::all();
foreach ($properties as $property) {
    echo $property->agent->name;  // 每個(gè)房產(chǎn)都會(huì)觸發(fā)一個(gè)新的查詢
}

// 優(yōu)化后
// 使用 `with()` 進(jìn)行預(yù)加載
$properties = Property::with(['agent'])->get();
foreach ($properties as $property) {
    echo $property->agent->name;  // 不需要額外的查詢！
}</code>

2. The art of database index

It can compare the database index to the index of writing -they can help you find what you want without scanning each page. However, the index is not only added to each column. Let's explore in -depth.

Understand different types of indexes

<code>// 基本的單列索引
Schema::table('properties', function (Blueprint $table) {
    $table->index('price');
});

// 多列的組合索引
Schema::table('properties', function (Blueprint $table) {
    $table->index(['city', 'price']); // 順序很重要！
});

// 唯一索引
Schema::table('properties', function (Blueprint $table) {
    $table->unique('property_code');
});</code>

The best practice of index strategy

The sequence of columns in the combination index is important

<code>   // 良好：匹配查詢模式
   $properties = Property::where('city', 'New York')
                        ->whereBetween('price', [200000, 500000])
                        ->get();

   // 索引應(yīng)匹配此模式
   $table->index(['city', 'price']); // 首先是城市，然后是價(jià)格</code>

Selective index
2. Not every column requires indexes. Selecting some columns to index some columns include:

   The columns often used in the where clause and the order by statement

   Index outer key column
   Do not index columns with low selectivity (such as the Boolean logo)
   The use of monitoring index

3. 3. Choose the content you need, not all the content

One of the most common errors I have ever seen (and offered) is to use Select *by default. This is like going to a grocery store, but buying things from the entire store, and you only need a meal ingredients. The following is a better way:

<code>   -- 檢查索引使用情況的 MySQL 查詢
   SELECT
       table_name,
       index_name,
       index_type,
       stat_name,
       stat_value
   FROM mysql.index_statistics
   WHERE table_name = 'properties';</code>

<code>// 優(yōu)化前
$properties = Property::all();
foreach ($properties as $property) {
    echo $property->agent->name;  // 每個(gè)房產(chǎn)都會(huì)觸發(fā)一個(gè)新的查詢
}

// 優(yōu)化后
// 使用 `with()` 進(jìn)行預(yù)加載
$properties = Property::with(['agent'])->get();
foreach ($properties as $property) {
    echo $property->agent->name;  // 不需要額外的查詢！
}</code>

4. For the segmentation of the large data set

When processing large datasets, handle all content in a single operation that may overwhelm the resources of the system and cause bottlenecks. Instead, you can use Laravel's Chunk method to manage the recordable batch processing records:

<code>// 基本的單列索引
Schema::table('properties', function (Blueprint $table) {
    $table->index('price');
});

// 多列的組合索引
Schema::table('properties', function (Blueprint $table) {
    $table->index(['city', 'price']); // 順序很重要！
});

// 唯一索引
Schema::table('properties', function (Blueprint $table) {
    $table->unique('property_code');
});</code>

5. Effective and effective cache strategy

Caches is like a good assistant who can remember everything. However, like any assistant, it needs clear instructions.

<code>   // 良好：匹配查詢模式
   $properties = Property::where('city', 'New York')
                        ->whereBetween('price', [200000, 500000])
                        ->get();

   // 索引應(yīng)匹配此模式
   $table->index(['city', 'price']); // 首先是城市，然后是價(jià)格</code>

Professional Tips: Do not cache everything! Focus on:

The data that is frequently accessed
Calculate the data with high cost
The data that is not changed frequently

Best Practice

Monitor first, and then optimize
1. Don't get into the trap of premature optimization. Use tools such as Laravel's built -in query logs or Telescope to identify the actual bottleneck. Thinking with gathering instead of the cycle
Whenever you find that you have written a ForeACH loop that query the database, please take a step back and ask if you can use a single query to deal with it.
2. Calling strategically Not everything needs to be cached. Pay attention to the frequently access and high cost queries. These queries do not require real -time accuracy.
3. Create the index Fighting the index as the directory of the book -you need enough details to quickly find things, but not too much and the directory is longer than the book itself.
4. Common defects that need to be avoided
Don't "pre -load" the unnecessary relationship

Avoid running query in the cycle (trap of the N 1 problem for camouflage)

Do not cache everything -sometimes the overhead of cache management exceeds the benefits
When using the OrderBY for the non -index columns of large data sets, be careful
Do not create indexes for columns that are rarely used in WHERE clauses
Avoid frequent updates; each update requires indexing
• Conclusion: This is a journey, not the destination
• Database optimization is not a one -time task that can be selected from the list. It is more like taking care of the garden -
regular maintenance and attention to get the best results

. Starting from these basic knowledge, monitoring the performance of the application continues to improve your method.

Remember, the goal is not to achieve every optimization technology you know. Instead, you must find a balance point that is suitable for your specific use cases between code maintenance and performance

. Sometimes, a simple pre -load statement is more helpful than spending a few hours to perform complex optimization strategies. What are you encountered in the Laravel project and which problems have you encountered in the Laravel project? Let's discuss in the comments!

The above is the detailed content of Laravel Performance Tuning: Optimizing Database Queries for Scalability. For more information, please follow other related articles on the PHP Chinese website!