Mengoptimumkan Isih dan Penapisan pada Lajur JSON dalam Laravel dengan Lajur Maya Berindeks

Asal disiarkan di bcd.dev

Garis besar

• Cabaran Prestasi Lajur JSON
  • Isu Masa Pemantauan dan Pelaksanaan
  • Isih dan Penapisan dengan Lajur JSON
• Memperkenalkan Lajur Maya
  • Mengapa Lajur Maya Berfungsi Lebih Baik
  • Cara Melaksanakan Lajur Maya
• Penandaarasan Keputusan
  • Sebelum: Lajur JSON Bersarang
  • Selepas: Lajur Maya + Indeks
• Cara Mengoptimumkan Pertanyaan JSON dengan Lajur Maya
• Kesimpulan dan Amalan Terbaik

Cabaran Prestasi Lajur JSON

Bekerja dengan set data besar yang disimpan dalam lajur JSON memberikan isu prestasi yang ketara, terutamanya apabila menapis dan mengisih. Dalam pengalaman saya, cabaran ini menjadi jelas semasa memantau proses PHP dan menguruskan volum besar rekod, yang membawa kepada had masa pelaksanaan yang dilanda.

Isu Masa Pemantauan dan Pelaksanaan

Sebagai sebahagian daripada tugas pemantauan biasa saya, saya menemui masa pelaksanaan maksimum 30 saat semasa menanyakan lajur JSON dalam set data rekod 580k. Lajur JSON, walaupun fleksibel, terdedah kepada kesesakan prestasi, terutamanya tanpa pengindeksan yang betul.

Isih dan Penapisan dengan Lajur JSON

Isu utama pertama muncul apabila bekerja pada halaman rekod senarai Filamen, yang mempunyai pengisihan lalai digunakan pada atribut JSON. Ketiadaan pengindeksan pada atribut ini mengakibatkan kelembapan yang ketara, terutamanya apabila memproses lebih 10,000 rekod. Tanpa indeks, pertanyaan dan pengisihan melalui atribut JSON bersarang boleh menyebabkan kelewatan pelaksanaan dan ketidakcekapan dalam mendapatkan semula hasil, menolak proses PHP melebihi had yang boleh diterima.

Memperkenalkan Lajur Maya

Apabila berhadapan dengan isu prestasi daripada mengisih dan menapis lajur JSON yang besar, saya menyemak semula penyelesaian lama: lajur maya daripada rakan saya Rob Fonseca. Lajur maya dalam MySQL membenarkan saya membuat lajur yang diindeks dan dikira daripada data JSON, menjadikan pertanyaan lebih cekap tanpa menduplikasi data.

Mengapa Lajur Maya Berfungsi Lebih Baik

Tidak seperti lajur JSON standard, lajur maya dikira secara automatik daripada data sedia ada tetapi boleh diindeks, menjadikannya lebih pantas untuk membuat pertanyaan. Ini meningkatkan prestasi pengisihan dan penapisan dengan ketara, terutamanya dalam set data besar yang masa pelaksanaan adalah kritikal.

Cara Melaksanakan Lajur Maya

Saya melaksanakan lajur maya dengan menambahkan migrasi yang mencipta lajur diindeks baharu untuk menapis dan mengisih. Lajur maya ini mengekstrak dan mengindeks atribut JSON khusus, meningkatkan prestasi pertanyaan secara drastik. Berikut ialah contoh penghijrahan:

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

Dengan mengindeks lajur maya ini, saya dapat mengurangkan masa pertanyaan dan meningkatkan kecekapan keseluruhan, terutamanya apabila menapis dan mengisih set data yang besar.

Penandaarasan Keputusan

Setelah saya melaksanakan lajur maya, saya perlu memastikan peningkatan prestasi adalah nyata. Penandaarasan menyediakan data konkrit, membandingkan masa pelaksanaan penapisan, pengisihan dan penomboran set data besar menggunakan kedua-dua lajur JSON bersarang asal dan lajur maya baharu dengan pengindeksan.

Sebelum: Lajur JSON Bersarang

Dengan lebih 580k rekod, pertanyaan pada lajur JSON bersarang adalah perlahan:

• Mengisih halaman 100 rekod mengambil masa lebih 5,000ms.
• Penapis + isihan + penomboran mengambil masa hampir 2,000ms.

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

Selepas: Lajur Maya + Indeks

Selepas mengindeks lajur maya, peningkatan adalah ketara:

• Mengisih halaman yang sama daripada 100 rekod menurun kepada 750ms (7.5x lebih pantas).
• Penapisan + pengisihan + penomboran dipertingkatkan kepada hanya 53ms (36x lebih pantas).

Penanda aras ini mengesahkan keberkesanan lajur maya dalam mengoptimumkan prestasi pertanyaan.

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

Langkah

1. Tambahkan Lajur Maya dengan Migrasi

Untuk meningkatkan prestasi, kami akan mulakan dengan menambah lajur maya untuk medan approved_at. Lajur ini mengekstrak dan mengindeks atribut JSON untuk prestasi pertanyaan yang lebih baik.

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. Cipta Sifat untuk Medan Maya

Kami akan mencipta ciri HasVirtualFields untuk memastikan medan maya tidak tersilap disimpan.

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. Tambahkan Sifat dan Harta Lajur Maya pada Model Anda

Dalam model, masukkan sifat dan tentukan medan maya. Ini memastikan bahawa mana-mana lajur maya diurus dengan betul.

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

Atas ialah kandungan terperinci Mengoptimumkan Isih dan Penapisan pada Lajur JSON dalam Laravel dengan Lajur Maya Berindeks. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!