深入理解Laravel中JSON列的索引策略与实践-php教程-PHP中文网

深入理解Laravel中JSON列的索引策略与实践

本文深入探讨在Laravel应用中为JSON列创建索引的多种策略。文章详细介绍了如何利用数据库原生功能（如函数索引或生成列）来优化JSON数据的查询性能，并提供了在Laravel迁移中实现这些策略的示例代码。同时，文章也强调了混合使用原生SQL与Laravel Schema构建器可能遇到的问题及相应的解决方案，旨在帮助开发者高效地管理和查询JSON数据。

JSON列索引的必要性

随着现代web应用对非结构化数据处理需求的增加，将json数据直接存储在数据库列中已成为一种常见做法。然而，当json列中的特定字段被频繁用于查询、排序或过滤时，缺乏索引会导致全表扫描，严重影响查询性能。为json列的特定路径创建索引，能够显著提升这些操作的效率，尤其是在数据量庞大时。

Laravel对JSON列的支持

Laravel通过其Schema构建器原生支持JSON数据类型。在迁移文件中，你可以轻松地定义一个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::create('products', function (Blueprint $table) {
            $table->id();
            $table->json('details'); // 定义一个JSON列
            $table->timestamps();
        });
    }

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

登录后复制

尽管Laravel Schema可以创建JSON列，但它不直接提供为JSON列的子路径创建索引的抽象方法。这需要我们借助底层数据库的特性来完成。

数据库原生JSON索引机制

不同的数据库系统提供了不同的方式来为JSON数据创建索引：

生成列 (Generated Columns) - MySQL 5.7+ 通过创建一个“虚拟”或“存储”列，其值由JSON列的特定路径计算而来，然后对这个生成列创建索引。这种方法兼容性较好，且Laravel Schema构建器也支持生成列。
函数索引 (Functional Indexes) - MySQL 8.0+ MySQL 8.0及更高版本支持直接在表达式上创建索引，这使得可以直接在JSON_VALUE()或JSON_EXTRACT()等函数的结果上创建索引，而无需额外的生成列。

在Laravel迁移中实现JSON索引

以下是在Laravel中为JSON列创建索引的几种策略。

策略一：使用生成列并索引（推荐）

这种方法利用数据库的生成列特性，将JSON路径中的数据提取到一个新的（通常是虚拟的）列中，然后对该列进行索引。这种方式与Laravel Schema构建器兼容性良好，且易于管理。

示例代码：

假设area_groups表中有一个title JSON列，包含多语言标题（如{"de": "德语标题", "en": "英语标题"}），我们想为德语和英语标题创建索引。

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::create('area_groups', function (Blueprint $table) {
            $table->id();
            $table->foreignId('area_id')->constrained(); // 假设有外键
            $table->json('title');
            $table->timestamps();

            // 为JSON列的特定路径创建生成列并添加索引
            // virtualAs() 方法创建一个虚拟列，其值是动态计算的
            // index() 方法则为这个生成列创建索引
            $table->string('title_de_index')
                  ->virtualAs("JSON_UNQUOTE(JSON_EXTRACT(title, '$.de'))")
                  ->index();

            $table->string('title_en_index')
                  ->virtualAs("JSON_UNQUOTE(JSON_EXTRACT(title, '$.en'))")
                  ->index();

            // 注意：如果需要存储生成列的值（例如，为了避免每次查询都重新计算），
            // 可以使用 storedAs() 代替 virtualAs()。
            // $table->string('title_de_index')->storedAs("JSON_UNQUOTE(JSON_EXTRACT(title, '$.de'))")->index();
        });
    }

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

登录后复制

优点： 与Laravel Schema构建器高度兼容，迁移文件清晰，易于理解和回滚。

策略二：直接使用原生SQL创建函数索引

对于MySQL 8.0+或PostgreSQL等支持函数索引的数据库，可以直接在JSON_VALUE()或JSON_EXTRACT()等函数的结果上创建索引。这种方法需要使用DB::statement执行原生SQL。

Find JSON Path Online

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

查看详情

示例代码：

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('area_groups', function (Blueprint $table) {
            $table->id();
            $table->foreignId('area_id')->constrained();
            $table->json('title');
            $table->timestamps();
        });

        // 在表创建完成后，使用原生SQL添加函数索引
        DB::statement('CREATE INDEX area_groups_title_de ON area_groups ((JSON_VALUE(title, "$.de")));');
        DB::statement('CREATE INDEX area_groups_title_en ON area_groups ((JSON_VALUE(title, "$.en")));');
    }

    public function down(): void
    {
        Schema::dropIfExists('area_groups');
        // 在 down 方法中也需要删除索引
        DB::statement('DROP INDEX area_groups_title_de ON area_groups;');
        DB::statement('DROP INDEX area_groups_title_en ON area_groups;');
    }
};

登录后复制

关于用户遇到的错误 Argument 1 passed to Doctrine\DBAL\Schema\Index::_addColumn() must be of the type string, null given：

用户在尝试中混合了DB::statement创建包含复杂函数索引的表，然后又使用Schema::table去修改这个表。这个错误通常发生在Doctrine DBAL（Laravel Schema构建器底层依赖的库）尝试解析或修改由原生SQL创建的复杂结构时。Doctrine DBAL可能无法正确解析INDEX ((JSON_VALUE(title, '$.de')))这种函数索引的列信息，导致在尝试添加或修改列时，期望的列名（字符串类型）却得到了null。

解决方案： 如果必须使用原生SQL来定义复杂的索引或表结构，请尽量避免随后立即使用Schema::table来修改同一个表。如果需要修改，考虑：

将所有表定义（包括ID、外键等）都放在同一个DB::statement中完成，完全绕过Schema::create和Schema::table。
或者，像上面示例所示，先用Schema::create创建基础表，然后再用DB::statement添加复杂索引。这样Schema::create只处理它能理解的部分，而DB::statement负责添加特殊索引，两者职责分离。

策略三：完全使用原生SQL创建表（避免Schema Builder干扰）

当表结构和索引定义极其复杂，或者为了确保数据库特性被精确使用而避免任何抽象层可能带来的问题时，可以考虑完全使用原生SQL来创建整个表。

示例代码：

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

return new class extends Migration
{
    public function up(): void
    {
        DB::statement(DB::raw(<<<SQL
        CREATE TABLE area_groups (
          id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          area_id BIGINT UNSIGNED NOT NULL,
          title JSON,
          created_at timestamp NULL,
          updated_at timestamp NULL,
          INDEX area_groups_title_de ((JSON_VALUE(title, '$.de'))),
          INDEX area_groups_title_en ((JSON_VALUE(title, '$.en'))),
          CONSTRAINT area_groups_area_id_foreign FOREIGN KEY (area_id) REFERENCES areas (id)
        ) DEFAULT CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_ci'
        SQL
        ));
    }

    public function down(): void
    {
        Schema::dropIfExists('area_groups'); // 仍然可以使用 Schema::dropIfExists
    }
};

登录后复制

注意事项：

这种方法失去了Laravel Schema构建器提供的便利性，例如自动处理列类型映射、外键约束等。
你需要手动编写所有SQL，包括主键、外键、时间戳等标准列。
回滚时，Schema::dropIfExists通常仍然有效。

注意事项与最佳实践

数据库版本兼容性： 确保你的数据库版本支持你选择的JSON索引策略。例如，生成列在MySQL 5.7+可用，函数索引在MySQL 8.0+可用。
索引选择： 并非JSON列的所有路径都需要索引。只为那些频繁用于WHERE子句、ORDER BY子句或GROUP BY子句的特定路径创建索引。
性能考量： 索引会增加写入操作的开销和存储空间。权衡查询性能提升与写入性能下降之间的关系。
可维护性： 尽量使用Laravel Schema构建器提供的功能。只有在Schema构建器无法满足特定需求时，才回退到原生SQL。当使用原生SQL时，务必在down()方法中提供相应的回滚逻辑（如DROP INDEX）。
JSON数据的存储： 尽管Laravel提供了json类型，但有时也可以使用text类型来存储JSON字符串。不过，使用json类型通常能获得更好的性能和数据验证。

总结

在Laravel中为JSON列的特定路径创建索引是优化查询性能的关键一步。通过利用数据库的生成列或函数索引特性，并结合Laravel的迁移系统，我们可以有效地实现这一目标。推荐优先使用生成列结合virtualAs()方法，因为它与Laravel Schema构建器兼容性最好。当需要更复杂的函数索引或遇到Schema构建器限制时，可以考虑使用DB::statement执行原生SQL，但需注意避免混合使用DB::statement和Schema::table可能导致的冲突，或考虑完全使用原生SQL来定义表结构。选择合适的策略，将有助于构建高性能、可维护的Laravel应用。

以上就是深入理解Laravel中JSON列的索引策略与实践的详细内容，更多请关注php中文网其它相关文章！