インデックス付き仮想列を使用した Laravel の JSON 列の並べ替えとフィルター処理の最適化

원래 bcd.dev에 게시됨

개요

• JSON 열 성능 문제
  • 모니터링 및 실행 시간 문제
  • JSON 컬럼을 이용한 정렬 및 필터링
• 가상 컬럼 소개
  • 가상 칼럼이 더 나은 이유
  • 가상 칼럼 구현 방법
• 결과 벤치마킹
  • 이전: 중첩된 JSON 열
  • 이후: 가상 컬럼 + 인덱스
• 가상 열을 사용하여 JSON 쿼리를 최적화하는 방법
• 결론 및 모범 사례

JSON 열 성능 문제

JSON 열에 저장된 대규모 데이터 세트로 작업하면 특히 필터링 및 정렬 시 심각한 성능 문제가 발생합니다. 내 경험에 따르면 PHP 프로세스를 모니터링하고 대량의 레코드를 관리하는 동안 이러한 문제가 분명해졌고, 이로 인해 실행 시간 제한이 발생했습니다.

모니터링 및 실행 시간 문제

정기적인 모니터링 업무의 일환으로 580,000개의 레코드 데이터 세트에서 JSON 열을 쿼리하는 동안 최대 실행 시간이 30초에 달했습니다. JSON 열은 유연하기는 하지만 특히 적절한 인덱싱이 없으면 성능 병목 현상이 발생하기 쉽습니다.

JSON 열을 사용한 정렬 및 필터링

첫 번째 주요 문제는 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 열

580,000개가 넘는 레코드로 인해 중첩된 JSON 열에 대한 쿼리가 느렸습니다.

• 100개의 레코드 페이지를 정렬하는 데 5,000ms가 넘게 걸렸습니다.
• 필터링 + 정렬 + 페이지 매김 작업에 거의 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);

이후: 가상 컬럼 + 인덱스

가상 열을 인덱싱한 후 상당한 개선이 이루어졌습니다.

• 동일한 페이지 100개 레코드를 정렬하면 750ms로 단축됩니다(7.5배 더 빠릅니다).
• 필터링 + 정렬 + 페이지 매김이 53ms로 향상되었습니다(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. 마이그레이션을 통해 가상 컬럼 추가

성능 향상을 위해 승인된_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. 모델에 특성 및 가상 열 속성 추가

모델에 특성을 포함하고 가상 필드를 정의합니다. 이렇게 하면 모든 가상 열이 적절하게 관리됩니다.

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 中国語 Web サイトの他の関連記事を参照してください。