使用索引虛擬列優化 Laravel 中 JSON 欄位的排序和篩選

原發佈於 bcd.dev

大綱

• JSON 列效能挑戰
  • 監控與執行時間問題
  • 使用 JSON 欄位進行排序和篩選
• 虛擬柱簡介
  • 為什麼虛擬列效果更好
  • 如何實作虛擬列
• 對結果進行基準測試
  • 之前：巢狀 JSON 欄位
  • 之後：虛擬列+索引
• 如何使用虛擬列最佳化 JSON 查詢
• 結論與最佳實務

JSON 列效能挑戰

使用儲存在 JSON 列中的大型資料集會帶來嚴重的效能問題，尤其是在過濾和排序時。根據我的經驗，這些挑戰在監控 PHP 進程和管理大量記錄時變得明顯，導致執行時間限制。

監控和執行時間問題

作為我常規監控職責的一部分，我在查詢 580k 記錄資料集中的 JSON 列時遇到了 30 秒的最大執行時間。 JSON 列雖然靈活，但很容易出現效能瓶頸，特別是在沒有適當索引的情況下。

使用 JSON 欄位進行排序和篩選

第一個主要問題出現在處理 Filament 清單記錄頁面時，該頁面將預設排序套用至 JSON 屬性。缺少此屬性的索引會導致速度顯著下降，尤其是在處理超過 10,000 筆記錄時。如果沒有索引，透過巢狀 JSON 屬性進行查詢和排序可能會導致執行延遲和檢索結果效率低下，從而使 PHP 進程超出可接受的限制。

虛擬柱簡介

當面臨排序和過濾大型 JSON 列的效能問題時，我重新審視了一個舊的解決方案：來自我的朋友 Rob Fonseca 的虛擬列。 MySQL 中的虛擬列允許我從 JSON 資料建立索引計算列，使查詢更加高效，而無需重複資料。

為什麼虛擬色譜柱效果更好

與標準 JSON 欄位不同，虛擬列是根據現有資料自動計算的，但可以建立索引，從而使查詢速度更快。這顯著提高了排序和過濾效能，尤其是在執行時間至關重要的大型資料集中。

如何實作虛擬列

我透過新增一個遷移來實作虛擬列，該遷移建立了一個用於過濾和排序的新索引列。此虛擬列提取特定 JSON 屬性並為其建立索引，從而大大提高了查詢效能。這是一個遷移範例：

$table->string('approved_at')
    ->nullable()
    ->virtualAs("json_unquote(json_extract(data, '$.latest_approval_date'))");
$table->index('approved_at');

透過索引這個虛擬列，我能夠減少查詢時間並提高整體效率，特別是在過濾和排序大型資料集時。

對結果進行基準測試

實作虛擬列後，我需要確保效能提升是真實的。基準測試提供了具體數據，比較了使用原始巢狀 JSON 列和帶有索引的新虛擬列對大型資料集進行過濾、排序和分頁的執行時間。

之前：巢狀 JSON 列

超過 580k 筆記錄，對巢狀 JSON 欄位的查詢速度很慢：

• 對一頁 100 筆記錄進行排序花費了超過 5,000 毫秒。
• 過濾+排序+分頁耗時近2000ms。

Benchmark::dd([
    'count' => fn () => Document::count(),
    'paginate' => fn () => Document::paginate(100),
    'filter + paginate' => fn () => Document::where('data->latest_approval_date', '>', '2024-09-05')->paginate(100),
    'sort + paginate' => fn () => Document::orderBy('data->latest_approval_date')->paginate(100),
    'filter + sort + paginate' => fn () => Document::where('data->latest_approval_date', '>', '2024-09-05')->orderBy('data->latest_approval_date')->paginate(100),
], iterations: 100);

之後：虛擬列+索引

對虛擬列建立索引後，改進是顯著的：

• 對同一頁的 100 筆記錄進行排序已降至 750 毫秒（快了 7.5 倍）。
• 濾波+排序+分頁改善至僅 53 毫秒（快了 36 倍）。

這些基準測試證實了虛擬列在最佳化查詢效能方面的有效性。

Benchmark::dd([
    'count' => fn () => Document::count(),
    'paginate' => fn () => Document::paginate(100),
    'filter + paginate' => fn () => Document::where('approved_at', '>', '2024-09-05')->paginate(100),
    'sort + paginate' => fn () => Document::orderBy('approved_at')->paginate(100),
    'filter + sort + paginate' => fn () => Document::where('approved_at', '>', '2024-09-05')->orderBy('approved_at')->paginate(100),
], iterations: 100);

步驟

1. 新增帶有遷移的虛擬列

為了提高效能，我們首先為approved_at 欄位新增一個虛擬列。此列提取 JSON 屬性並為其建立索引，以提高查詢效能。

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void {
        Schema::table('documents', function (Blueprint $table) {
            $table->string('approved_at')
                ->nullable()
                ->virtualAs("json_unquote(json_extract(data, '$.latest_approval_date'))");
            $table->index('approved_at');
        });
    }

    public function down(): void {
        Schema::table('documents', function (Blueprint $table) {
            $table->dropColumn('approved_at');
        });
    }
};

2. 為虛擬欄位建立特徵

我們將建立一個 HasVirtualFields 特徵，以確保虛擬欄位不會被錯誤儲存。

namespace App\Models\Concerns;

trait HasVirtualFields {
    public function save(array $options = []) {
        if (isset($this->virtualFields)) {
            $this->attributes = array_diff_key($this->attributes, array_flip($this->virtualFields));
        }
        return parent::save($options);
    }
}

3. 將 Trait 和 Virtual Column 屬性加入模型中

在模型中，包含特徵並定義虛擬欄位。這可確保任何虛擬列都正確管理。

use App\Models\Concerns\HasVirtualFields;

class Document extends Model {
    use HasVirtualFields;

    protected array $virtualFields = [
        'approved_at',
    ];
}

4. Testing Environment

To test the performance improvements, we’ll generate fake data and benchmark the queries before and after using virtual columns. Use the following provisioning script:

$count = 500 * 1000;
for ($i = 0; $i < 250; $i++) {
    Document::factory()->count(1000)->create();
}

6. Wrapping Up with Unit Tests

Write tests to verify that the virtual column works as expected. Here’s an example test suite:

namespace Tests\Feature\Models;

use Tests\TestCase;
use App\Models\Document;

class DocumentTest extends TestCase {
    public function testApprovedAt() {
        $date = fake()->dateTimeBetween()->format(DATE_ATOM);
        $document = Document::factory()->create([
            'data' => [
                'latest_approval_date' => $date,
            ],
        ]);
        $document->refresh();
        $this->assertEquals($date, $document->approved_at);
    }
}

This complete solution ensures that your JSON columns can be optimized for performance, particularly for large datasets.

Conclusion and Best Practices

Using virtual columns with indexing can dramatically improve performance when working with large datasets and JSON columns. By transitioning from nested JSON queries to indexed virtual columns, I was able to reduce query times by up to 36x.

Best Practices:

• Use virtual columns to index frequently queried JSON attributes.
• Always benchmark before and after implementing changes to measure real performance improvements.
• Ensure your database structure evolves with your data as it scales, especially with JSON-heavy models.

Originally posted on bcd.dev

以上是使用索引虛擬列優化 Laravel 中 JSON 欄位的排序和篩選的詳細內容。更多資訊請關注PHP中文網其他相關文章！