Optimisation du tri et du filtrage sur les colonnes JSON dans Laravel avec des colonnes virtuelles indexées

Publié à l'origine sur bcd.dev

Contour

• Défis de performances des colonnes JSON
  • Problèmes de surveillance et de temps d'exécution
  • Tri et filtrage avec les colonnes JSON
• Présentation des colonnes virtuelles
  • Pourquoi les colonnes virtuelles fonctionnent mieux
  • Comment implémenter des colonnes virtuelles
• Analyse comparative des résultats
  • Avant : colonnes JSON imbriquées
  • Après : Colonne Virtuelle + Index
• Comment optimiser les requêtes JSON avec des colonnes virtuelles
• Conclusion et bonnes pratiques

Défis de performances des colonnes JSON

Travailler avec de grands ensembles de données stockés dans des colonnes JSON présente des problèmes de performances importants, en particulier lors du filtrage et du tri. D'après mon expérience, ces défis sont devenus évidents lors de la surveillance des processus PHP et de la gestion de grands volumes d'enregistrements, ce qui a entraîné le respect des délais d'exécution.

Problèmes de surveillance et de temps d’exécution

Dans le cadre de mes tâches de surveillance régulières, j'ai rencontré des temps d'exécution maximum de 30 secondes lors de l'interrogation de colonnes JSON dans un ensemble de données d'enregistrement de 580 000. Les colonnes JSON, bien que flexibles, sont sujettes à des goulots d'étranglement en termes de performances, en particulier sans indexation appropriée.

Tri et filtrage avec les colonnes JSON

Le premier problème majeur est apparu lors du travail sur une page d'enregistrement de liste de filaments, sur laquelle le tri par défaut était appliqué à un attribut JSON. L'absence d'indexation sur cet attribut entraînait un ralentissement important, notamment lors du traitement de plus de 10 000 enregistrements. Sans index, l'interrogation et le tri des attributs JSON imbriqués peuvent entraîner des retards d'exécution et des inefficacités dans la récupération des résultats, poussant les processus PHP au-delà des limites acceptables.

Présentation des colonnes virtuelles

Face à des problèmes de performances liés au tri et au filtrage de grandes colonnes JSON, j'ai revisité une ancienne solution : les colonnes virtuelles de mon ami Rob Fonseca. Les colonnes virtuelles dans MySQL me permettent de créer une colonne indexée et calculée à partir de données JSON, rendant les requêtes plus efficaces sans dupliquer les données.

Pourquoi les colonnes virtuelles fonctionnent mieux

Contrairement aux colonnes JSON standard, les colonnes virtuelles sont calculées automatiquement à partir des données existantes mais peuvent être indexées, ce qui les rend plus rapides pour les requêtes. Cela améliore considérablement les performances de tri et de filtrage, en particulier dans les grands ensembles de données où le temps d'exécution est critique.

Comment implémenter des colonnes virtuelles

J'ai implémenté des colonnes virtuelles en ajoutant une migration qui a créé une nouvelle colonne indexée pour le filtrage et le tri. Cette colonne virtuelle a extrait et indexé des attributs JSON spécifiques, améliorant considérablement les performances des requêtes. Voici un exemple de migration :

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

En indexant cette colonne virtuelle, j'ai pu réduire les temps de requête et améliorer l'efficacité globale, notamment lors du filtrage et du tri de grands ensembles de données.

Analyse comparative des résultats

Une fois les colonnes virtuelles implémentées, je devais m'assurer que les gains de performances étaient réels. L'analyse comparative a fourni des données concrètes, comparant les temps d'exécution du filtrage, du tri et de la pagination de grands ensembles de données en utilisant à la fois la colonne JSON imbriquée d'origine et la nouvelle colonne virtuelle avec indexation.

Avant : colonnes JSON imbriquées

Avec plus de 580 000 enregistrements, les requêtes sur la colonne JSON imbriquée étaient lentes :

• Le tri d'une page de 100 enregistrements a pris plus de 5 000 ms.
• Le filtrage + tri + pagination ont pris près de 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);

Après : colonne virtuelle + index

Après indexation de la colonne virtuelle, les améliorations ont été substantielles :

• Le tri de la même page de 100 enregistrements est tombé à 750 ms (7,5 fois plus rapide).
• Filtrage + tri + pagination améliorés à seulement 53 ms (36 fois plus rapide).

Ces benchmarks ont confirmé l'efficacité des colonnes virtuelles dans l'optimisation des performances des requêtes.

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

Mesures

1. Ajouter une colonne virtuelle avec migration

Pour améliorer les performances, nous commencerons par ajouter une colonne virtuelle pour le champ approved_at. Cette colonne extrait et indexe l'attribut JSON pour de meilleures performances de requête.

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. Créez un trait pour les champs virtuels

Nous allons créer un trait HasVirtualFields pour garantir que les champs virtuels ne sont pas enregistrés par erreur.

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. Ajoutez la propriété Trait et Colonne virtuelle à votre modèle

Dans le modèle, incluez le trait et définissez les champs virtuels. Cela garantit que toutes les colonnes virtuelles sont correctement gérées.

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!