2000行のSQLが流れてた話

この記事は スターフェスティバル スターフェスティバル Advent Calendar 2022の23日目の記事です。

はじめまして

スターフェスティバル株式会社 ソフトウェアエンジニアのまつやです。

エンジニアとして、外界にアウトプットするのが初めてなので簡単な自己紹介...
北海道出身。高校を卒業して役者を目指して上京したが途中で挫折。
猫を飼うために居酒屋のバイトをやめて、
2014年にスターフェスティバルでコールセンターのアルバイトとして入社。
気づけば正社員になり、2020年からエンジニアとして働いております。
もう８年もいます。居心地がとても良いです。
よろしくお願いいたします。

さて、本日お話しする内容は...

とあるサービスで「検索機能が重い」という問い合わせがあって改善した話。

長年利用されてきたサービスで
当時そのサービスを創り上げた戦士たちはもういない。
そんな中ペーペーである私が、できる限り保守をしていました。

問い合わせがあり、試しにサイトを調べてみると
確かに、ずーっとぐるぐる回って読み込み状態が続き、検索結果の画面が表示されませんでした。

「あっ！これ一人で解決できない無理なやつだ！」と察して
ここでスターフェスティバルに所属している全エンジニアに助けを求めた。

まずは検索機能がどうなってるかみてみた

• PHP5.6 / MySQL / Zend Framework 1
• 検索機能自体はMySQLの全文検索にて実現されている。
• 検索機能のクラスは、他の機能でも使用されている。
  ざっくりみた感じだとこんな感じだった🤔

検索機能が重い原因調査

総力を上げて調査した結果、
いくつかの原因が上げられてきました。

MySQLのアップグレードによる影響

MySQLをアップグレードしたことにより
データベース自体の検索エンジンのアルゴリズムがバージョンアップによって変更されたことで
一部のクエリでパフォーマンス劣化があり、検索クエリがそのアルゴリズム変更により影響を受けていた。

でも、アップグレードすることは悪ではない。
アップグレードすることにより止まってしまうのも問題なので他の原因もある。

定数でSQL文が書かれており、条件によって色んなSQL文をJOINさせまくっていた。

検索機能のコードを見ていったところ
店舗の営業時間検索用、商品価格検索用などなど....
検索条件項目のために、JOIN句のSQL文が大量にありました。

if($where['max_price']){
  $sql .= sprintf(self::SERACH_MAX_PRICE, $where['max_price']);
}

コードを読んでいる途中で中身がわからないSQLが書かれた定数が現れ
どこのテーブルの何のカラムに対して条件を追加しているのかが全く読み取れず、可読性も悪くなっていました。

その中でも、お届け日までの営業日数を判定するSQL文を作成する箇所があったのですが

if($where['day_sabun'] > 1 ){ // 差分２日以上の場合
  // 注文日から配達日までの日数分ループ
  for($i = 0; $i < $where['day_sabun']; $i++ ){
    $sql .= sprintf(self::SERACH_SHOP_HOLIDAY_COUNT, $i);
  }
}

上記のようにfor文により、お届けまでの日数分のJOIN句を追加しておりました。
「SERACH_SHOP_HOLIDAY_COUNT」には
予約期限内の店舗商品を取得する40行ほど記載されて、
お届け日を2ヶ月先まで設定できるため、ここだけで最大2400行のSQL文を作成していました。

全部の検索条件をまとめたSQLクエリを一撃で投げて検索している。

前述した通り、中にはループ処理でSQL文を大量に結合し、
さまざまな条件でさらにJOIN句増えて、最終的に1個のSQL文としてDBにクエリを投げてました。

また、今回調査しているファイル内にあるメソッドが
検索機能以外の箇所でも使われていたため、より複雑にSQL文が絡み合っているような状態でした。

改善のためにやったこと

• 絞り込みの処理をPHP側で処理をするように変更
• 検索する順番を変更して、先に店舗だけを絞るように変更

データの構造上「商品」は「店舗」ありきとなっていたため
先に店舗を絞った上で、絞った店舗の商品のみを書き出すようにしました。

それぞれの検索に必要なデータのみを取得し、それをPHP側で処理することで
SQLのクエリ検索で判定するよりかは圧倒的に速度が速くなり、
ループでお届け日までの営業日数を判定していたSQLを抹消することに成功しました。（よかった）

もっとできることがあったんじゃないかってところ

コードの可読性

修正したはいいものの、なんのデータを取得しているかはSQLが書かれた定数を見に行かなければいけないことは変わり無くなっているため、そこのデータ取得をORMライブラリを使用して可読性を上げることができたらもっと良かったと反省。

機能ごとにメソッドの分離

今回修正したコードが検索機能以外でも使用されている箇所がいくつもあり、
他の機能にも影響範囲が広がって調査に時間がかかってしまった。

ある程度共通処理があったとしても、検索機能のクラスが巨大化しすぎていたため
共通処理は共通処理として分割し、できる限り小さいコードを保つことを意識してリファクタリングも兼ねて修正するべきだったと思います。

締めます

ここまで中身のない内容を読んでいただきありがとうございました。
今まで投稿されてきた記事を見ていると

• 「おいおい！ここの会社にはすごい奴しかいないな！！！」
• 「あいつらはアウトプットの天才だ！！！」

など、スターフェスティバルに近寄り難かった方もいるかと思います。
今回の内容を読んでいただいて「スターフェスティバルにもこんなエンジニアいるんだな...」と言うことを知っていただければ我幸いです。

引き続きスターフェスティバルをよろしくお願いいたします。