マネフォアドベントカレンダー2022
この記事は、マネフォアドベントカレンダー2022 8日目の投稿です。
7日目はyuito nakamoriさんで メタデータ管理の最初の一歩 でした。
本日は亀井亮介 が「く〜ろ〜れ〜き〜し〜 〜SQLアンチパターン・DB設計アンチパターン〜」について書いていきたいと思います。
自己紹介
マネーフォワードビジネスカンパニークラウド横断本部QA推進部長亀井です！
QA推進といいながら、スクラムマスターしたり、どさくさに紛れてソース書こうとしたり（未遂）、放っておくとアイデンティティが崩壊しそうな今日この頃です！詳しくは「改めて自分が何者なのかを問う」で！
来期は次のことをミッションにしてゆく所存です！
- マネーフォワードビジネスカンパニー共通基盤・マイクロサービスの品質を定量的に可視化
- 各プロダクトチームのエンジニアの品質に対する意識を高める仕組みを構築
SQL10,000万行くらい書いた話
知ってました？JOINするときにLIKE使えるって
10年以上前にとある企業にフリーランスで入っていた時の話です。
データベースにおいて根幹となるテーブルは正規化されておらず、1つのカラムに複数の要素（ xx_id:xx_code:xx_key ）が詰め込まれていました。
そしてこのカラムはプライマリーキーに指定されていたため、プライマリーキーのメリットを行かせない状態でした。
上記はSQLアンチパターンのジェイウォークです。
デメリットの項目にある通り、常にLIKEで検索しないといけないなど性能面でのデメリット、そもそもデータ不整合の温床になり、不具合に対処するための実装など考慮することが多く保守性が低くなります。
その会社の少ない社員SEから、「亀井さんに、私が設計した○○画面の作成をお願いします」と依頼され、話を聞くと、とあるエクセルをそのままシステム化、しかも毎回リアルタイムでデータ取ってこないといけない仕様（バッチでデータを作成し、それを表示するは仕様上できません）でした。
いざ取り掛かると、関連テーブル数は30とか40ありキーが設定されていませんでした。
キーを付与することを提案しましたが、諸事情あり既存のテーブル構成のまま実装することとなりました。
キーがないのでテーブル同士がJOINできません。なんとかキーにできそうなのは日付のみだったので次のような対応を行いました。
yyyy/mm/dd HH:mm:ss → yyyy/mm/ddだけを使う
SQLと戦っていると、JOINでLIKEが使えることがわかります（笑）
JOINでLIKE例：MySQL LIKE JOIN というアウトローなクエリーを試してみるから引用
UPDATE
musics a
INNER JOIN
tbl_artist b
ON
a.Title LIKE CONCAT('%', b.artist_name , '%')
SET
a.artist_id = b.Id
;
どうもJOIN ONの後はほぼWHEREと同じ扱いらしいことがわかる
参考 SQLにおける結合条件の違いを把握しよう！ON句とWHERE句に指定する場合の違いとは？
上記参考にもある通り、状況によって取得できる値が異なるので慎重にSQLを書きました。その結果、このソースコード（ほぼSQL）の行数は10,000万行を超えていました。しかも
- 関連テーブル数は30とか40ある
- キーが設定されていないためインデックスが効かない
- 毎回リアルタイムでデータ取ってこないとダメという仕様
実行すると…結果がなかなか返ってこない（そりゃそう）。WHERE句で最小データ数にしても30分かかります（笑）。
一応、EXPLAINとにらめっこしチューニングをはかる。
もちろん（？）インデックスなんて効きやしない。
サブクエリもふんだんに使っており、コストがかかっていることがわかる。
どうもJOIN ONの後はほぼWHEREと同じ扱いらしい事を思い出し、サブクエリのWHEREを全てONに持っていくとパフォーマンスが上がるというノウハウを得ました（EXPLAINの偉大さを知りました）。
だがしかぁぁし、こんなことをやっていてもSQL10,000万行・正規化されていない設計には打ち勝てず。ぜんぜん遅いです。
なぜかこのままの状態でリリースされ、当然のごとく「遅い」とクレームがあり、状況説明したら、当然のごとく使われませんでした…泣
エンジニアとして価値が出せないソースコードを書くのは辛かったです。
教訓
この経験から、ソースコードを書くだけだと明らかなアンチパターンでもやらざる得ない状況になります。
要件・設計ができて、さらにマネジメントができるエンジニアを目指そうと思いました。
マネーフォワードでは上記のようなアンチパターンに今まで遭遇したことがなく、マイクロサービス化しているとテーブルの数も少なくなるので、アンチパターンを産みにくい状況を作れるなぁと思います。