Info
Content

Laravel 数据库性能优化实战（五）：使用函数索引和虚拟生成列优化模糊匹配查询

Created 3 years ago by 学院君, Updated 3 years ago    Revision #2    2381 views    1 likes    0 collects

我们接着来看模糊匹配查询优化的例子。所谓模糊匹配就是 LIKE 查询，关于 LIKE 查询优化的基本原则学院君在前面 MySQL 索引篇讲最左前缀原则时已经介绍过，这里侧重的是涉及到关联查询的模糊匹配，在此之前，我们先准备好相关的模型数据，以及演示对单表模糊匹配的优化（包含对 %XXX 这种查询的优化）。

模型数据准备工作

我们将演示对用户表和其所属的公司表进行模糊匹配。所以需要创建公司模型类和对应的数据表迁移文件：

php artisan make:model Company -m

在刚刚新建的 companies 数据表对应迁移文件中编写表结构实现代码如下，其中只包含了公司名和地址两个额外业务字段：

<?php

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

class CreateCompaniesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('companies', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->string('address');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('companies');
    }
}

用户和公司之间是归属关系，一个公司包含了多个雇员用户，所以还要新建一个数据库迁移文件，在 users 表中新增一个 company_id 字段来实现这种一对多关联：

php artisan make:migration alter_users_add_company_id --table=users

编写对应的表字段新增实现代码如下：

<?php

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

class AlterUsersAddCompanyId extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->bigInteger('company_id')->unsigned()
                ->after('current_team_id')->index();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('company_id');
        });
    }
}

重建数据库让上述数据库变更生效：

php artisan migrate

接下来，我们分别在 Company 和 User 模型类中定义它们之间的一对多关联关系：

// 公司模型类
class Company extends Model
{
    use HasFactory;

    public function employees()
    {
        return $this->hasMany(User::class);
    }
}

// 用户模型类
class User extends Authenticatable
{
    ... 
    
    public function company()
    {
        return $this->belongsTo(Company::class);
    }
}

通过 php artisan make:factory CompanyFactory 创建一个公司模型工厂类，并编写实现代码如下：

<?php

namespace Database\Factories;

use App\Models\Company;
use Illuminate\Database\Eloquent\Factories\Factory;
use Illuminate\Support\Str;

class CompanyFactory extends Factory
{
    /**
     * The name of the factory's corresponding model.
     *
     * @var string
     */
    protected $model = Company::class;

    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        return [
            'name' => $this->faker->unique()->company,
            'address' => $this->faker->address
        ];
    }
}

通过 php artisan make:seeder CompaniesSeeder 创建一个公司数据表填充器类，并编写实现代码如下：

<?php

namespace Database\Seeders;

use App\Models\Company;
use App\Models\User;
use Illuminate\Database\Seeder;

class CompaniesSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        // 先清空
        Company::truncate();
        User::truncate();
        // 再填充
        Company::factory()
            ->has(User::factory()->count(100), 'employees')
            ->count(100)->create();
    }
}

运行如下 Artisan 命令填充相关公司和用户数据：

php artisan db:seed --class=CompaniesSeeder

运行完成后就做好了模型数据准备工作。

实现用户搜索列表功能

接下来，我们来实现用户搜索功能。

在 UserController 控制器中新增一个用户搜索实现方法 search：

<?php

namespace App\Http\Controllers;

use App\Models\User;

class UserController extends Controller
{
    ...

    public function search()
    {
        $columns = ['id', 'name', 'email', 'company_id', 'created_at'];
        $query = User::with('company')->select($columns);
        if ($term = request()->get('keyword')) {
            $query->where('name', 'like', '%' . $term . '%')
                ->orWhere('email', 'like', '%' . $term . '%');
        }
        $users = $query->orderByDesc('id')->paginate(20);
        return view('user.search', ['users' => $users, 'term' => $term]);
    }
}

然后在 resources/views/user 目录下新建 search.blade.php 作为对应的视图模板，编写模板代码如下：

<x-app-layout>
    <x-slot name="header">
        <h2 class="font-semibold text-xl text-gray-800 leading-tight">
            用户搜索
        </h2>
    </x-slot>

    <div class="py-12">
        <div class="max-w-7xl mx-auto sm:px-6 lg:px-8 mb-4 relative text-gray-600">
            <form action="{{ url('/users/search') }}" method="GET">
                <input class="w-full border-2 border-gray-300 bg-white h-12 px-5 rounded-lg text-sm focus:outline-none" name="keyword" placeholder="Search" value="{{ $term }}">
                <button type="submit" class="absolute right-12 top-3.5">
                    <svg class="text-gray-600 h-5 w-5 fill-current" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" version="1.1" id="Capa_1" x="0px" y="0px" viewBox="0 0 56.966 56.966" style="enable-background:new 0 0 56.966 56.966;" xml:space="preserve" width="512px" height="512px">
                    <path d="M55.146,51.887L41.588,37.786c3.486-4.144,5.396-9.358,5.396-14.786c0-12.682-10.318-23-23-23s-23,10.318-23,23  s10.318,23,23,23c4.761,0,9.298-1.436,13.177-4.162l13.661,14.208c0.571,0.593,1.339,0.92,2.162,0.92  c0.779,0,1.518-0.297,2.079-0.837C56.255,54.982,56.293,53.08,55.146,51.887z M23.984,6c9.374,0,17,7.626,17,17s-7.626,17-17,17  s-17-7.626-17-17S14.61,6,23.984,6z"></path>
                </svg>
                </button>
            </form>
        </div>

        <div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
            <div class="bg-white overflow-hidden shadow-xl sm:rounded-lg">
                <div class="flex justify-center">
                    <table class="w-full m-8">
                        <thead>
                        <tr>
                            <th class="px-4 py-2">ID</th>
                            <th class="px-4 py-2">Name</th>
                            <th class="px-4 py-2">Email</th>
                            <th class="px-4 py-2">Company</th>
                            <th class="px-4 py-2">Created At</th>
                        </tr>
                        </thead>
                        <tbody>
                        @foreach ($users as $user)
                            <tr>
                                <td class="border px-4 py-2">{{ $user->id }}</td>
                                <td class="border px-4 py-2">{{ $user->name }}</td>
                                <td class="border px-4 py-2">{{ $user->email }}</td>
                                <td class="border px-4 py-2">{{ $user->company->name }}</td>
                                <td class="border px-4 py-2">{{ $user->created_at->diffForHumans() }}</td>
                            </tr>
                        @endforeach
                        </tbody>
                    </table>
                </div>

                <div class="max-w-full m-8">
                    {{ $users->links() }}
                </div>
            </div>
        </div>
    </div>
</x-app-layout>

在 routes/web.php 中新增对应的路由，就可以访问到用户搜索页面了：

-w1270

我们可以通过搜索框模糊匹配用户记录：

-w1423

模糊匹配常规优化

我们已经知道模糊匹配能够使用数据库索引进行优化的前提有两个：

  • 在对应匹配字段上设置了索引
  • 符合最左前缀原则

现在已经在 name 和 email 字段上都设置索引了，但是查询的时候没有满足最左前缀原则，所以此时模糊匹配并未应用索引，而是使用了全表扫描：

-w952

要使用 name 或者 email 字段上的索引进行模糊匹配，需要去掉匹配值左侧的 % 来满足最左前缀原则：

-w1214

对应到 Laravel 项目代码，我们需要修改 UserController 中的模糊匹配实现代码如下：

public function search()
{
    $columns = ['id', 'name', 'email', 'company_id', 'created_at'];
    $query = User::with('company')->select($columns);
    if ($term = request()->get('keyword')) {
        $query->where('name', 'like', $term . '%')
            ->orWhere('email', 'like', $term . '%');
    }
    $users = $query->orderByDesc('id')->paginate(20);
    return view('user.search', ['users' => $users, 'term' => $term]);
}

刷新用户搜索页面，可以看到应用索引后现在的查询时间大幅缩减：

-w1425

通过函数索引优化模糊匹配

但是这引入了另一个问题，那就是无法匹配形如 %强 这样的字符串，要实现这个功能，可以通过下面这样的 SQL 语句来查询：

select * from `users` where `name` LIKE '强%' or reverse(`name`) like '强%';

注：由于 email 字段不包含中文字符，所以后续示例代码将其省略。

我们通过 MySQL 自带的 reverse 函数将包含 %强 的记录匹配出来，同时，也应用上了最左前缀原则，不过前面介绍 MySQL 索引原理的时候提到过，对于这种调用了内置函数的查询字段，是不能使用索引的：

-w949

不过从 MySQL 8.0 起，开始支持函数索引了，我们可以为 reverse(name) 函数调用整体设置索引来优化这个查询，从而实现对 %XXX 模糊匹配的索引优化，如果通过 Laravel 迁移文件来实现的话，可以这么做：

php artisan make:migration alter_users_add_reverse_name_index --table=users

然后编写对应的数据库迁移文件实现代码如下：

<?php

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

class AlterUsersAddReverseNameIndex extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->rawIndex('(reverse(`name`))', 'reverse_name_index');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropIndex('reverse_name_index');
        });
    }
}

我们需要通过 rawIndex 函数来定义函数索引，并且不要忘了在 reverse(``name``) 外层套上括号，以示和普通索引的区别，该函数的第二个参数是对应的索引名称。

运行 php artisan migrate 命令让上述函数索引生效：

-w752

然后将 UserController 中将对应的 Eloquent 模糊匹配代码调整如下：

if ($term = request()->get('keyword')) {
    $query->where('name', 'like', $term . '%')
        ->orWhereRaw('reverse(`name`) like ?', $term . '%');
}

刷新用户列表页，就可以获取到所有用户名包含 %强 和 强% 的用户记录了：

-w1420

但是需要注意的是这样依然匹配不到形如 %强% 的用户名。

使用虚拟生成列索引优化模糊匹配

除了函数索引之外，在 MySQL 8.0 之前还可以通过生成列（Generated Column）来实现类似的模糊匹配查询优化，所谓生成列，顾名思义，指的是该列是通过其他列计算生成。

该特性是在 MySQL 5.7 版本之后引入的，在此之前，我们只能通过创建一个实际的数据列来实现类似查询优化，但是业务中又用不到这个列，所以浪费了存储空间。

生成列可以根据是否在磁盘上实际存储数据进一步细分为虚拟生成列（Virtual Generated Column）和存储生成列（Stored Generated Column），默认使用的是虚拟生成列，即不会将该列数据持久化到磁盘，只是将元信息保存到数据字典，我们可以为生成列设置索引来优化查询效率。

实际上，MySQL 8.0 之后引入的函数索引就是在虚拟生成列功能基础上实现的。

关于生成列的概念网上的信息一大把，这里就不详细展开了，接下来，我们以上述示例为例演示如何在 Laravel Eloquent 中通过虚拟生成列优化形如 %XXX 的模糊匹配查询效率。

在此之前，需要先为 users 表的 name 字段新增一个虚拟生成列并为其设置索引：

php artisan make:migration alter_users_add_virtual_generated_name --table=users

编写对应的数据库迁移文件实现代码如下：

<?php

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

class AlterUsersAddVirtualGeneratedName extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('virtual_name_reverse')
                ->virtualAs('reverse(`name`)')
                ->index();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('virtual_name_reverse');
        });
    }
}

这里，我们通过 reverse(``name``) 来生成这个虚拟的 virtual_name_reverse 字段并为其设置索引，其实现思路和上面的函数索引非常像。

运行 php artisan migrate 让上述创建的虚拟生成列和索引生效。查看 users 数据表结构，可以看到已经包含了相应的虚拟生成列 virtual_name_reverse 和对应的索引：

-w753

而在数据记录视图中现在可以看到 virtual_name_reverse 这一列：

-w514

我们在查询时可以使用这个虚拟列来实现基于索引对 %XXX 这种模糊匹配的优化：

-w1073

改写 UserController 中的用户名模糊匹配代码如下：

if ($term = request()->get('keyword')) {
    $query->where('name', 'like', $term . '%')
    ->orWhere('virtual_name_reverse', 'like', $term . '%');
}

刷新用户搜索列表，可以看到返回结果和之前一样，并且查询效果也得到了优化：

-w1424

下篇教程，学院君将给大家演示如何结合关联查询实现更复杂的模糊匹配，并对查询性能进行优化。

Laravel
MySQL
数据库
性能优化
Eloquent
索引
模糊查询
LIKE
最左前缀原则
函数索引
生成列
虚拟生成列
迁移
模糊匹配
搜索
Vote Vote Cancel Collect Collect Cancel

<< 上一篇: Laravel 数据库性能优化实战（四）：通过子查询优化关联查询性能

>> 下一篇: Laravel 数据库性能优化实战（六）：通过子查询和联合查询结合关联模型进行模糊匹配

4 Comments
#1    laradocs   laradocs commented 3 years ago

请问 current_team_id 字段哪来的？

#2    站住别跑   站住别跑 commented 3 years ago

我这为什么利用最左前缀原则后耗时还高了？使用虚拟生成列索引 的耗时和开始没差别

#3    张三   张三 commented 2 years ago •  Updated 2 years ago by 张三

reverse 这个函数 mysql8 增加函数索引的时候 是不是不支持啊？ 改怎么解决？

#4    MOYU   MOYU commented 2 years ago

x like '强%' or x_reverse like '强%' 只能查出左右两边有强的 那我要查中间‘志’就查不到呀

登录后即可添加评论