Optimieren der Sortierung und Filterung von JSON-Spalten in Laravel mit indizierten virtuellen Spalten

Ursprünglich veröffentlicht auf bcd.dev

Gliederung

• Herausforderungen bei der JSON-Spaltenleistung
  • Überwachungs- und Ausführungszeitprobleme
  • Sortieren und Filtern mit JSON-Spalten
• Einführung in virtuelle Spalten
  • Warum virtuelle Säulen besser funktionieren
  • So implementieren Sie virtuelle Spalten
• Benchmarking der Ergebnisse
  • Vorher: Verschachtelte JSON-Spalten
  • Nachher: ​​Virtuelle Spalte + Index
• So optimieren Sie JSON-Abfragen mit virtuellen Spalten
• Fazit und Best Practices

Herausforderungen bei der JSON-Spaltenleistung

Das Arbeiten mit großen Datensätzen, die in JSON-Spalten gespeichert sind, führt zu erheblichen Leistungsproblemen, insbesondere beim Filtern und Sortieren. Meiner Erfahrung nach wurden diese Herausforderungen bei der Überwachung von PHP-Prozessen und der Verwaltung großer Datensatzmengen deutlich, was dazu führte, dass die Ausführungszeitlimits überschritten wurden.

Probleme bei der Überwachung und Ausführungszeit

Im Rahmen meiner regulären Überwachungsaufgaben kam es bei der Abfrage von JSON-Spalten in einem Datensatz mit 580.000 Datensätzen zu maximalen Ausführungszeiten von 30 Sekunden. Obwohl JSON-Spalten flexibel sind, sind sie anfällig für Leistungsengpässe, insbesondere ohne ordnungsgemäße Indizierung.

Sortieren und Filtern mit JSON-Spalten

Das erste große Problem trat bei der Arbeit an einer Filamentlisten-Datensatzseite auf, bei der die Standardsortierung auf ein JSON-Attribut angewendet wurde. Das Fehlen einer Indizierung dieses Attributs führte zu einer erheblichen Verlangsamung, insbesondere bei der Verarbeitung von über 10.000 Datensätzen. Ohne einen Index kann das Abfragen und Sortieren verschachtelter JSON-Attribute zu Ausführungsverzögerungen und Ineffizienzen beim Abrufen von Ergebnissen führen, wodurch PHP-Prozesse über akzeptable Grenzen hinausgehen.

Einführung in virtuelle Spalten

Als ich mit Leistungsproblemen beim Sortieren und Filtern großer JSON-Spalten konfrontiert war, griff ich noch einmal auf eine alte Lösung zurück: virtuelle Spalten von meinem Freund Rob Fonseca. Mit virtuellen Spalten in MySQL kann ich eine indizierte, berechnete Spalte aus JSON-Daten erstellen und so Abfragen effizienter gestalten, ohne Daten zu duplizieren.

Warum virtuelle Säulen besser funktionieren

Im Gegensatz zu Standard-JSON-Spalten werden virtuelle Spalten automatisch aus vorhandenen Daten berechnet, können jedoch indiziert werden, wodurch sie schneller für Abfragen geeignet sind. Dies verbessert die Sortier- und Filterleistung erheblich, insbesondere bei großen Datensätzen, bei denen die Ausführungszeit entscheidend ist.

So implementieren Sie virtuelle Spalten

Ich habe virtuelle Spalten implementiert, indem ich eine Migration hinzugefügt habe, die eine neue indizierte Spalte zum Filtern und Sortieren erstellt hat. Diese virtuelle Spalte extrahierte und indizierte bestimmte JSON-Attribute und verbesserte so die Abfrageleistung drastisch. Hier ist eine Beispielmigration:

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

Durch die Indizierung dieser virtuellen Spalte konnte ich die Abfragezeiten verkürzen und die Gesamteffizienz verbessern, insbesondere beim Filtern und Sortieren großer Datensätze.

Benchmarking der Ergebnisse

Nachdem ich die virtuellen Spalten implementiert hatte, musste ich sicherstellen, dass die Leistungssteigerungen real waren. Benchmarking lieferte konkrete Daten und verglich die Ausführungszeiten des Filterns, Sortierens und Paginierens großer Datensätze unter Verwendung sowohl der ursprünglichen verschachtelten JSON-Spalte als auch der neuen virtuellen Spalte mit Indizierung.

Vorher: Verschachtelte JSON-Spalten

Bei über 580.000 Datensätzen waren Abfragen in der verschachtelten JSON-Spalte langsam:

• Das Sortieren einer Seite mit 100 Datensätzen dauerte über 5.000 ms.
• Das Filtern + Sortieren + Paginieren dauerte fast 2.000 ms.

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);

Nachher: ​​Virtuelle Spalte + Index

Nach der Indizierung der virtuellen Spalte waren die Verbesserungen erheblich:

• Das Sortieren derselben Seite mit 100 Datensätzen sank auf 750 ms (7,5-mal schneller).
• Filtern + Sortieren + Paginieren wurde auf nur 53 ms verbessert (36x schneller).

Diese Benchmarks bestätigten die Wirksamkeit virtueller Spalten bei der Optimierung der Abfrageleistung.

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);

Schritte

1. Fügen Sie eine virtuelle Spalte mit Migration hinzu

Um die Leistung zu verbessern, fügen wir zunächst eine virtuelle Spalte für das Feld „approved_at“ hinzu. Diese Spalte extrahiert und indiziert das JSON-Attribut für eine bessere Abfrageleistung.

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. Erstellen Sie eine Eigenschaft für virtuelle Felder

Wir erstellen eine HasVirtualFields-Eigenschaft, um sicherzustellen, dass virtuelle Felder nicht versehentlich gespeichert werden.

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. Fügen Sie Ihrem Modell die Eigenschaften Trait und Virtual Column hinzu

Beziehen Sie im Modell das Merkmal ein und definieren Sie die virtuellen Felder. Dadurch wird sichergestellt, dass alle virtuellen Spalten ordnungsgemäß verwaltet werden.

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

Das obige ist der detaillierte Inhalt vonOptimieren der Sortierung und Filterung von JSON-Spalten in Laravel mit indizierten virtuellen Spalten. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!