使用索引虚拟列优化 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中文网其他相关文章！