Laravel Eloquent查询性能优化求助：14000条Catalog数据查询过慢问题排查

Hey there! Let's break down why your query might be running slow even with a seemingly small dataset, and how to speed it up.

First, Check for Missing Indexes (Most Likely Culprit) #

Your query filters by shop_id and sorts by created_at (via latest()), but if these columns don't have proper indexes, the database has to do a full table scan every time. Even 14k records can feel slow when scanning a table with 100+ columns.

Fixes: #

1. Add an index on shop_id:

   // Run this in a migration
   Schema::table('catalogs', function (Blueprint $table) {
       $table->index('shop_id');
   });
   

2. Better yet, create a composite index:
   Since you're filtering by shop_id AND sorting by created_at, a composite index will let the database both find the rows quickly and return them in sorted order without extra work:

   Schema::table('catalogs', function (Blueprint $table) {
       $table->index(['shop_id', 'created_at']);
   });
   

Analyze the Query Execution Plan #

To confirm what the database is actually doing, use EXPLAIN on your raw SQL query. Here's how:

1. Get the raw SQL from Laravel:

   $query = Catalog::where('shop_id', $shop->id)->latest();
   dd($query->toSql());
   

2. Paste that SQL into phpMyAdmin, prepend it with EXPLAIN, and run it. Look for:
   • The type column: Should show range or ref (good, means using an index). If it says ALL, that's a full table scan (bad).
   • The Extra column: If it says Using filesort, that means the database is sorting results manually instead of using an index—your composite index will fix this.

Rule Out Unintended Model Overhead #

Check if your Catalog model has any global scopes, eager loaded relationships, or observers that might be adding extra work:

• If you have a $with property in the model that auto-loads relationships, temporarily disable it with without():

  $catalogs = Catalog::without(['relatedModel'])
      ->where('shop_id', $shop->id)
      ->latest()
      ->get(['id','title', 'created_at', 'shop_id', 'cover_bg', 'frontpage', 'pdf', 'clicks', 'finished']);
  

• Ensure no observers are running extra queries when fetching records.

Check Database Configuration #

Even with low CPU usage, slow disk I/O or insufficient memory can cause delays:

• For MySQL, verify the innodb_buffer_pool_size setting. For a dedicated database server, set it to 50-70% of your total RAM. This lets the database keep frequently accessed data in memory instead of hitting the disk.
• Make sure there are no other long-running queries locking the table. Run SHOW PROCESSLIST in MySQL to check for stuck queries.

Consider Pagination (If Possible) #

If your frontend doesn't need all 14k records at once, switch to pagination to reduce the amount of data fetched per request:

$catalogs = Catalog::where('shop_id', $shop->id)
    ->latest()
    ->select(['id','title', 'created_at', 'shop_id', 'cover_bg', 'frontpage', 'pdf', 'clicks', 'finished'])
    ->paginate(20); // Adjust the per-page count as needed

Final Notes #

Since your CPU usage is low, this is almost certainly a database indexing or configuration issue rather than server performance. Start with the composite index and EXPLAIN analysis—those should resolve most of the slowness.

内容的提问来源于stack exchange，提问作者Aleks Per