【実践】Laravel+MySQLの全文検索機能実践

はじめに

LIKE検索は全ての行に対して条件に合致するかを判定するのでデータが増えるにつれ検索速度が遅くなる場合があるようです。

この記事では、MySQLの全文検索機能を導入した方法についてハンズオン形式で紹介させていただきます。

完成系のリポジトリ
@ucan-labさんの記事を参考にLAMP環境を構築いたしました🙇‍♂️

続編

事前準備

• GitHub・Dockerが利用できる準備をお願いします

• MAC

• Windows

リポジトリをクローン

git clone https://github.com/nao-haba-dev/ngram-docker-laravel.git

セットアップ

docker-compose up -d
docker-compose exec app bash
composer install
composer update
cp .env.example .env
php artisan key:generate
php artisan storage:link
chmod -R 777 storage bootstrap/cache

トップページが表示されていればセットアップ完了です

今回のハンズオンのゴール

• 全文検索用のDDLを作成
• 検索ロジックを実装
• 全文検索を実践

【実践】Laravel+MySQLで全文検索機能を導入してみる

bootstrapのスカフォールドを生成

root@xxxx:/work#

composer require laravel/ui
php artisan ui bootstrap
npm install
npm run dev

全文検索用のModelとmigrationを生成

root@xxxx:/work#

php artisan make:model Shop --migration

Model定義

backend/app/Models/Shop.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Shop extends Model
{
    use HasFactory;

    /**
     * モデルに関連付けるテーブル
     *
     * @var string
     */
    protected $table = 'shops';

    /**
     * 複数代入可能な属性
     *
     * @var array
     */
    protected $fillable = ['name', 'age', 'gender_id'];
}

migration定義

backend/database/migrations/2022_01_01_132402_create_shops_table.php

<?php

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

class CreateShopsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('shops', function (Blueprint $table) {
            $table->bigIncrements('id')->comment('店ID');
            $table->string('name', 255)->comment('従業員');
            $table->unsignedInteger('age')->comment('年齢');
            $table->smallInteger('gender_id')->comment('性別');
            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));
        });
        DB::statement("ALTER TABLE shops ADD free_word TEXT as (concat(IFNULL(age, ''), ' ',IFNULL(name, ''), ' ',(case gender_id when 1 then '男性' when 2 then '女性' else '' end), ' ')) STORED");
        DB::statement("ALTER TABLE shops ADD FULLTEXT index ftx_free_word (free_word) with parser ngram");
    }

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

migration定義の補足

LaravelのクエリビルダでSQL文を直接実行します

backend/database/migrations/2022_01_01_132402_create_shops_table.php

DB::statement()

ALTER TABLE shops ADD free_word TEXT:TEXT型のカラムを追加　
as:as以降の値を追加して生成します

backend/database/migrations/2022_01_01_132402_create_shops_table.php

"ALTER TABLE shops ADD free_word TEXT as xxx STORED

concat:複数の文字列を連結します
IFNULL:1番目に指定した引数の値が NULL だった場合に 2番目の引数の値を返します

backend/database/migrations/2022_01_01_132402_create_shops_table.php

(concat(IFNULL(age,''),' ')

case:各 WHEN 句内の when_value 式のいずれかに等しくなるまで、それらの式と比較されます
when:条件
then:条件に一致した場合の処理

backend/database/migrations/2022_01_01_132402_create_shops_table.php

(case gender_id when 1 then '男性' when 2 then '女性' else '' end), ' '

migration

root@27c72a17e24e:/work#

php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (60.12ms)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (42.83ms)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (63.83ms)
Migrating: 2019_12_14_000001_create_personal_access_tokens_table
Migrated:  2019_12_14_000001_create_personal_access_tokens_table (84.70ms)
Migrating: 2022_01_01_132402_create_shops_table
Migrated:  2022_01_01_132402_create_shops_table (249.35ms)

seederを定義

backend/database/seeders/DummyShopsSeeder.php

<?php

namespace Database\Seeders;

use App\Models\Shop;
use Illuminate\Database\Seeder;

class DummyShopsSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $data = [
            [
                'name' => 'サンプル太郎',
                'age' => 25,
                'gender_id' => 1
            ],
            [
                'name' => 'サンプル花子',
                'age' => 30,
                'gender_id' => 2
            ],
            [
                'name' => 'サンプル二郎',
                'age' => 20,
                'gender_id' => 1
            ],
        ];

        (new Shop())->query()->insert($data);
    }
}

Seeding

root@27c72a17e24e:/work#

php artisan db:seed --class=DummyShopsSeeder
Database seeding completed successfully.

Seeder結果

dbコンテナに入る

root@27c72a17e24e:/work#

docker-compose exec db bash 

mySQL接続

bash-4.4#

bash-4.4# mysql -u root -p
# password と入力してEnter
Enter password: 

登録情報を参照

mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| laravel_local      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

use laravel_local;

 show tables;
+-------------------------+
| Tables_in_laravel_local |
+-------------------------+
| failed_jobs             |
| migrations              |
| password_resets         |
| personal_access_tokens  |
| shops                   |
| users                   |
+-------------------------+

mysql> select * from shops;
+----+--------------------+-----+-----------+---------------------+---------------------+-------------------------------+
| id | name               | age | gender_id | created_at          | updated_at          | free_word                     |
+----+--------------------+-----+-----------+---------------------+---------------------+-------------------------------+
|  1 | サンプル太郎       |  25 |         1 | 2022-01-02 00:57:29 | 2022-01-02 00:57:29 | 25 サンプル太郎 男性          |
|  2 | サンプル花子       |  30 |         2 | 2022-01-02 00:57:29 | 2022-01-02 00:57:29 | 30 サンプル花子 女性          |
|  3 | サンプル二郎       |  20 |         1 | 2022-01-02 00:57:29 | 2022-01-02 00:57:29 | 20 サンプル二郎 男性          |
+----+--------------------+-----+-----------+---------------------+---------------------+-------------------------------+
3 rows in set (0.00 sec)

Routeを定義

backend/routes/web.php

<?php

use Illuminate\Support\Facades\Route;

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('welcome');
});

Route::get('/admin', [\App\Http\Controllers\ShopController::class, 'index']);

Controllerを定義

• 今回はControllerにビジネスロジックを定義していますが、useCase層等に分離させた方がいいです

backend/app/Http/Controllers/ShopController.php

<?php

namespace App\Http\Controllers;

use App\Models\Shop;
use Illuminate\Contracts\Foundation\Application;
use Illuminate\Contracts\View\Factory;
use Illuminate\Contracts\View\View;
use Illuminate\Http\Request;

class ShopController extends Controller
{
    /**
     * 検索
     * @param Request $request
     * @return Application|Factory|View
     */
    public function index(Request $request)
    {
        $query = Shop::query();
        $freeWord = $request->input('free_word');

        if ($freeWord) {
            $query->whereRaw("match(`free_word`) against (? IN BOOLEAN MODE)", $freeWord);
        }

        $shops = $query
            ->select(['id', 'name', 'age', 'gender_id'])
            ->paginate(20, ['*'], 'page', 1);

        $param = collect($request->input());
        $shops->appends($request->input());

        return view('index', ['shops' => $shops, 'parameters' => $param]);
    }
}

Controllerを定義:補足

whereRaw:クエリビルダに直接SQL文を指定します(必ずプリペアドステートメント機能を利用してください）

rawメソッドはクエリを文字列として挿入するため、SQLインジェクションの脆弱性を生まないように十分気をつけてください。

backend/app/Http/Controllers/ShopController.php

$query->whereRaw("match(`free_word`) against (? IN BOOLEAN MODE)", $freeWord);

Viewの定義

backend/resources/views/index.blade.php

<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>Laravel</title>
    <link href="{{ asset('css/app.css') }}" rel="stylesheet">
    <script src="{{ asset('js/app.js') }}"></script>
</head>
<body>
<div class="container">
    <div class="card center-block">
        <div class="card-header">
            Featured
        </div>
        <div class="card-body">
            <form action="/admin" method="Get">
                <div class="mb-2">
                    <label for="free_word" class="form-label">全文検索</label>
                    <input type="text" class="form-control" name="free_word" id="free_word"
                           value="{{$parameters->get('free_word')}}">
                </div>
                <input type="submit" class="btn btn-primary" value="検索">
                <a href="/admin" class="btn btn-secondary">検索条件クリア</a>
            </form>
        </div>
    </div>
    <br>
    <div>
        <div>検索結果は{{$shops->total()}}件です。</div>
        <div>
            <table class="table">
                <thead>
                <tr>
                    <th>ID</th>
                    <th>名前</th>
                    <th>年齢</th>
                    <th>性別</th>
                </tr>
                </thead>
                <tbody>
                @foreach($shops as $shop)
                    <tr>
                        <td>{{$shop->id}}</td>
                        <td>{{$shop->name}}</td>
                        <td>{{$shop->age}}</td>
                        <td>{{$shop->gender_id ===1 ? '男性':'女性'}}</td>
                    </tr>
                @endForeach
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>

男性 2で検索すると完全一致でないものも検索してくれます!
またIndexを利用して検索してくれるのでLIKE検索に比べてパフォーマンスが高いようです

おわりに

今回はMySQLの全文検索機能+Laravelについて紹介させていただきました。

Laravelのクエリビルダーを使用するとSQLを書かなくてもいい感じにやってくれる反面、特殊な対応が必要な場合はSQLの知識・経験が必要なので日々勉強の毎日です。。。
この記事が誰かのお役に立てば幸いです