在 Laravel 中为 JSON 列创建高效索引策略

引言：Laravel 中的 JSON 列

随着现代应用程序对非结构化数据处理需求的增加，数据库中的 json 类型列变得越来越普遍。laravel 框架通过其 eloquent orm 和 schema builder 对 json 列提供了良好的支持，允许开发者方便地存储和检索 json 数据。例如，一个 products 表可能有一个 details json 列，其中包含不同产品的各种属性，如颜色、尺寸、品牌等。

然而，当我们需要根据 JSON 列中某个特定字段的值进行高效查询时，仅仅存储 JSON 数据是不够的。如果不创建适当的索引，对 JSON 数据的查询可能会导致全表扫描，严重影响应用程序的性能。

JSON 列索引的必要性与挑战

在处理包含 JSON 数据的数据库列时，直接对整个 JSON 列创建常规索引通常效率不高，因为它会将整个 JSON 字符串视为一个整体进行索引，无法针对 JSON 内部的特定键值进行优化。为了提高查询效率，我们需要对 JSON 列中的特定路径（即 JSON 对象的某个属性）创建索引，这通常被称为函数式索引或表达式索引。

用户在尝试通过混合原生 SQL CREATE TABLE 语句和 Laravel 的 Schema::table 操作来创建带有函数式索引的 JSON 列时，遇到了 Argument 1 passed to Doctrine\DBAL\Schema\Index::_addColumn() must be of the type string, null given 的错误。这个错误通常发生在 Doctrine DBAL (Laravel 底层数据库抽象层所依赖的库) 尝试解析或内省复杂的原生 SQL 语句时，如果这些语句包含了 Doctrine 无法直接理解的语法（例如某些数据库特有的函数式索引定义），并且随后又尝试使用 Laravel Schema Builder 对同一张表进行操作，就可能导致解析失败。

为了避免此类问题，推荐的最佳实践是将 Laravel Schema Builder 的标准操作与原生 SQL 语句进行合理分离。

方法一：使用 Laravel Schema Builder 定义 JSON 列

Laravel 的 Schema Builder 提供了简单的方式来创建 JSON 类型的列。

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateProductsTable extends Migration
{
    public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->json('details')->nullable(); // 定义一个JSON类型的列
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('products');
    }
}

这种方式可以成功创建 JSON 列，但 Laravel Schema Builder 自身并没有直接提供创建针对 JSON 内部特定路径的函数式索引的方法。对于这类高级索引，我们需要借助原生 SQL。

Find JSON Path Online

Easily find JSON paths within JSON objects using our intuitive Json Path Finder

30

查看详情

方法二：利用原生 SQL 实现 JSON 列的函数式索引

要为 JSON 列的特定路径创建索引，我们需要使用数据库的原生 SQL 功能，例如 MySQL 5.7+ 或 PostgreSQL 支持的函数式索引。为了避免上述 Doctrine 错误，最佳实践是：

1. 首先使用 Laravel Schema Builder 创建表和 JSON 列。
2. 然后，在同一个迁移文件的 up 方法中，使用 DB::statement 执行原生 SQL 语句来添加函数式索引。

以下是一个完整的示例，演示如何创建 area_groups 表，其中包含一个 title JSON 列，并为其 $.de 和 $.en 路径创建索引：

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

class CreateAreaGroupsTableAndAddJsonIndexes extends Migration
{
    /**
     * 运行数据库迁移。
     *
     * @return void
     */
    public function up()
    {
        // 步骤 1: 使用 Laravel Schema Builder 创建表和基础列，包括 JSON 列。
        // 这样可以确保 Doctrine DBAL 能够正确解析表的结构。
        Schema::create('area_groups', function (Blueprint $table) {
            $table->id(); // 自增主键
            $table->foreignId('area_id')->constrained(); // 外键
            $table->json('title'); // JSON 类型列，用于存储多语言标题
            $table->timestamps(); // created_at 和 updated_at
        });

        // 步骤 2: 使用原生 SQL 添加 JSON 列的函数式索引。
        // 这将为 JSON 字段 'title' 中的 '$.de' 和 '$.en' 路径创建索引，
        // 从而优化基于这些路径的查询性能。
        // 请确保您的数据库（例如 MySQL 5.7+ 或 PostgreSQL）支持函数式索引。
        DB::statement('ALTER TABLE area_groups ADD INDEX area_groups_title_de ((JSON_VALUE(title, "$.de")));');
        DB::statement('ALTER TABLE area_groups ADD INDEX area_groups_title_en ((JSON_VALUE(title, "$.en")));');
    }

    /**
     * 回滚数据库迁移。
     *
     * @return void
     */
    public function down()
    {
        // 步骤 1: 回滚时，首先删除函数式索引。
        // 对于函数式索引，使用原生 SQL 删除索引通常更可靠。
        DB::statement('ALTER TABLE area_groups DROP INDEX area_groups_title_de;');
        DB::statement('ALTER TABLE area_groups DROP INDEX area_groups_title_en;');

        // 步骤 2: 然后删除表。
        Schema::dropIfExists('area_groups');
    }
}

在上述代码中：

• Schema::create('area_groups', ...) 负责创建表和常规列，包括 json('title')。
• DB::statement(...) 用于执行原生 SQL 语句 ALTER TABLE ... ADD INDEX ... 来创建函数式索引。JSON_VALUE(title, "$.de") 是 MySQL 的一个函数，用于提取 JSON 字段 title 中路径 $.de 的值。

处理 JSON 数据：编码与存储

在将 PHP 数组或对象存储到 JSON 列之前，需要使用 json_encode() 函数将其转换为 JSON 字符串。Laravel 的 Eloquent 模型在处理 json 类型的属性时，通常会自动进行这种转换（如果模型中定义了 casts 属性），但手动操作时也需注意。

// 示例：准备数据并存储到JSON列
$data = [
    'de' => 'Deutscher Titel',
    'en' => 'English Title',
    'fr' => 'Titre Français'
];

$areaGroup = new AreaGroup();
$areaGroup->area_id = 1;
// Eloquent 会自动将数组转换为JSON字符串存储，如果模型中配置了 $casts = ['title' => 'json'];
$areaGroup->title = $data;
$areaGroup->save();

// 如果没有配置 casts，则需要手动编码
// $areaGroup->title = json_encode($data);
// $areaGroup->save();

注意事项与最佳实践

1. 数据库兼容性： 函数式索引的语法和支持程度取决于您使用的数据库。
   • MySQL： 从 5.7 版本开始支持 JSON 数据类型和函数式索引。请确保使用 JSON_VALUE() 或 CAST(JSON_EXTRACT(...)) 等函数。
   • PostgreSQL： 提供了强大的 JSONB 类型和 GIN/BTREE 索引来支持 JSON 数据的查询。
   • SQLite： 对 JSON 的支持相对有限，可能需要将 JSON 存储为 TEXT 并手动解析。
2. JSON 类型与 TEXT 类型的选择：
   • JSON 类型： 推荐使用，因为它能确保存储的数据是有效的 JSON 格式，并提供更优化的存储和查询性能（尤其是在支持原生 JSON 类型的数据库中）。
   • TEXT 类型： 可以用来存储 JSON 字符串，但在数据库层面没有 JSON 格式的校验，且查询优化不如原生 JSON 类型。
3. 索引命名规范： 为索引指定清晰、描述性的名称，例如 表名_列名_路径，有助于管理和维护。
4. 性能考量：
   • 并非所有 JSON 路径都需要索引。只对经常用于查询条件的路径创建索引。
   • 过多的索引会增加写入操作（插入、更新、删除）的开销。
   • 定期监控查询性能，根据实际需求调整索引策略。
5. down() 方法的正确回滚： 在 down() 方法中，确保以正确的顺序删除索引和表。对于函数式索引，使用 DB::statement 删除通常更可靠，因为 Laravel Schema Builder 的 dropIndex 方法可能无法识别这类复杂索引。

总结

在 Laravel 中为 JSON 列创建索引是优化涉及 JSON 数据查询的关键一步。通过合理地结合 Laravel Schema Builder 创建基础表结构和 JSON 列，以及使用 DB::statement 执行原生 SQL 来添加函数式索引，我们可以高效地为 JSON 列的特定路径创建索引，从而显著提升查询性能。同时，理解数据库的兼容性、选择合适的 JSON 存储类型以及遵循最佳实践，将有助于构建健壮且高性能的应用程序。

以上就是在 Laravel 中为 JSON 列创建高效索引策略的详细内容，更多请关注php中文网其它相关文章！