Yappli Tech Blog

株式会社ヤプリの開発メンバーによるブログです。最新の技術情報からチーム・働き方に関するテーマまで、日々の熱い想いを持って発信していきます。

ノーコード開発プラットフォームYappliの社内用検索機能をGenerated Columnsを使って刷新しました

サーバーサイドエンジニアの田実です!

YappliはSQLite3を使ってアプリ情報・アカウントの管理やコンテンツの管理を行っています。 社内システムでYappliが管理しているアプリの情報やアカウントを良い感じに検索できる機能があるのですが、今回はその検索機能をSQLite3からMySQLに移行・刷新した話を紹介したいと思います。

背景

アプリのコンテンツはマルチテナント・マルチバージョンの機能要件のため SQLite3を使って管理しています。

tech.yappli.io

以前はアプリやアカウントもSQLite3で管理していたのですが、こちらはマルチバージョンで扱う必要がなかったため、現在はMySQLで管理・運用しています。

SQLite3で管理していたときは各アプリの情報がそれぞれのSQLite3に格納されていて、SQLite3ファイルがアプリの数だけあるような状態でした。 そのため、アプリやアカウントを検索するには複数のSQLite3ファイルを横断的に検索する必要がありました。

実装のイメージはこんな感じです。

<?php

function searchApp()
{
  foreach ($apps as $app) {
    // SQLite3ファイルを開いてアプリのレコードを検索
  }
}

この方式だと一度に複数のDBファイルにアクセスすることになり、アプリ数が増えれば増えるほど検索性能が劣化します。 そこで、アプリの情報を予め1つのSQLite3ファイルに集約し、検索時はその集約したSQLite3ファイルにアクセスを行うことで性能改善を行いました。

SQLite3による検索機能

しかしながら、この方式にはいくつか課題がありました。

1つ目は集約処理が非同期のため、アプリを新規作成したり更新しても検索結果には即時反映されませんでした。 そのため、開発時・QA時で待ちが発生したり、反映されない不具合と勘違いするようなこともありました。

2つ目は認知負荷の課題で、横断検索の場合は集約されたSQLite3を参照し、横断検索以外の場合は個別のSQLite3を参照していることを意識する必要がありました。 サーバーの再起動などにより、集約処理が意図せず止まってしまい、検索に反映されないこともありました。 これはアーキテクチャ上の課題というよりは、実装を改善することで対応可能だったのですが、非同期処理・バッチ処理特有の考慮が必要で、やりたいことに対して実装が複雑になる可能性もありました。

今回、アプリ・アカウント管理がMySQLに移行されたため、これらの課題を解決するためにSQLite3で運用されていた検索機能も刷新することにしました。

MySQLによる簡易検索とアプローチ

MySQLに集約されているため、そのまま検索できそうな感じがしますが実はそう簡単なロジックではありませんでした。

まず、アプリとアカウントの情報の詳細情報はJSONカラムで管理されている状態でした。 JSONの中身を検索するには、JSON_EXTRACT() 関数などを駆使して検索する必要がありますが、LIKE句を入れて検索した場合、検索速度が悪くなってしまいました。

SELECT
  *
FROM
  app
WHERE
  `metadata` ->> '$.name' LIKE ?
  OR `metadata` ->> '$.title' LIKE ?
  OR `metadata` ->> '$.description' LIKE ?;

詳細な原因調査はしていませんが、アプリやアカウントの数は10,000〜程度で、曖昧検索かつJSONのデコードが入るため、検索速度が出なかったのだと思われます。 社内システムと言えどユーザー体験が良くなかったため、MySQLの Generated Columns を利用した方法を検討・採用しました。

Generated Columnsはカラムの値を使って別の値を算出するようなカラムで、Excelの数式のようなカラムを実現できる機能です。 格納方式にはVIRTUALとSTOREDを選択でき、VIRTUALはクエリ実行時に計算する方式、STOREDは予め計算した値を格納・保存して利用する方式になります。

そこで、以下のような検索用のカラムを追加し、このカラムに対してLIKE検索をすることで検索速度を改善することができました。

ALTER TABLE app ADD COLUMN search_field VARCHAR(512) COLLATE utf8mb4_general_ci 
GENERATED ALWAYS AS
CONCAT(
  `metadata` ->> '$.name',
  `metadata` ->> '$.title',
  `metadata` ->> '$.description'
) STORED;

Generated Columnsを利用することにより、WHERE句も search_field LIKE ? だけで良くなり、クエリの見通しも良くなりました。 search_fieldのDDLとアプリケーションのコードでロジックが分散するのがデメリットになりますが、検索項目の変更は頻繁に発生しなかったため許容範囲と判断しています。

ちなみに格納方式にVIRTUALを指定した場合はクエリ実行時にJSONデコードが入るため、WHERE句でOR検索するのと同じ検索速度でした。VIRTUALでもインデックスが貼れるようなので、インデックスが効くような検索の場合はVIRTUALでも良いのですが、今回はあいまい検索になるのでSTOREDを選択しています。

また、今回の検索機能は大文字小文字を区別しないという要件があったため、COLLATEを utf8mb4_general_ci にしています。

その他

検索機能刷新に伴い、検索に関連する機能で現在未使用のものが無いかを確認しました。 ある特定のステータスのアプリやアカウントを検索する機能もありましたが、現状利用していないとのことだったのでロジックから除外しました。 CSVをダウンロードする機能もありましたが、こちらも現在未使用ということで撤廃しています。もし必要になる場合でも現在はRedashのCSVダウンロードで要件を満たせますし、むしろ管理画面でダウンロード機能を自作するより便利だったりします。

まとめ

検索機能の刷新とGenerated Columnsを用いた実装について紹介しました。 ユーザ側(今回でいうと社員)から見たメリットは少ないですが、エンジニア視点でいうと認知負荷を下げ、不要なシステム・処理を撤廃する良い機会となりました。 ヤプリでは新機能開発・機能改善だけではなく、認知負荷を下げてチーム全体のパフォーマンスを上げていけるようなタスクにも積極的に取り組んでいます。