直近の開発の課題と解決案（SQLとコードの一般論）

1. 前段

とあるIT企業に所属していて、Java(Springboot)+Mybatis+MySQLで新しいシステムを立ち上げました。私自身は、プロジェクトマネージャーで必要な業務要件を理解しつつ、コードも書けるタイプです。（ただし、Springbootのフレームワークはあまり詳しく無いため、詳しい人に助けてもらいながら開発してきました。）

そして今現在、システム稼働から1年以上経過したところで、気づいたら技術負債がかなりたまってしまいました。
私としても失敗して初めて気づいたこともあったので、将来同じ失敗をしないために、ここにメモをします。なお、あまり特定の技術には偏っておらず、SQLとコードの一般的な内容ばかりです。

もし気づいたことや意見があれば、コメントいただけると助かります！

2. 「とりあえず」で作ったけど必要無かった

(1) 削除フラグ（関連：SQLアンチパターン幻の第26章）

課題

参考のスライド内でもいろいろ書かれているが、私が困ったのは主に以下の2点。

• SELECT時に削除フラグをWHERE条件内に書き忘れる。結局、システム全体で削除フラグが正しく動作することの保証ができない。
• ほとんどのテーブルのデータは削除する運用がなく、削除フラグはずっと0（削除されていない）のままなので、実質的な効果がないのに、SQL作成が面倒になっただけという印象を受けた。

解決案

以下の2点を検討したい。

• 履歴を残す必要があるテーブルに対応する履歴テーブルを用意。挿入・更新・削除があった時に、Triggerで履歴テーブルに書き出す。テーブル名は「（元テーブル）_history」などで統一する。ただし、元テーブルをALTERする際に、履歴テーブルもALTERする必要がある。　履歴テーブルからデータを読み出す際は、日時を引数とするstored procedureを作っておくと便利かもしれない。
• 状態で管理する。例えば、課題対応チケットであれば、「新規」、「対応中」、「対応完了」、「中止」などと状態を作って管理。この場合、削除は「中止」に相当する。

(2) 作成者、更新者

課題

削除フラグと一緒。UIから操作できない（バックエンドの計算のみで使用する）テーブルに対してもつけてしまっており、最終的に形骸化した。

解決案

必要な時（特にUI上での操作者を記録する必要がある時）につけるようにする。不要なら付けない。

(3) headerテーブル（ヘッダー表）とlineテーブル（明細表）

課題

例えば請求書などで、ヘッダーと明細が分かれていて、1対多の関係の場合、2テーブル作成するのは合理的。
ヘッダー：請求先会社名、作成日時、合計金額
明細：各請求項目（商品名、個数、金額）

しかし、ヘッダーと明細が必ず1対1なのに、とりあえず2テーブル作ってしまい、毎回JOINが必要になった機能ができた。正直、新しいフィールド追加時に、ヘッダー表と明細表どちらに追加すべきかよく分からない。

解決策

1対1なら1テーブルにしよう。

余談

ちなみに弊社システムでは、作成日時と更新日時もとりあえず付けているが、これは調査時に役立っているので、全テーブルにあっても良いと思っている。（コード内にREPLACE INTOがあって、作成日時がリセットされたこともある。通常はINSERT INTO ... ON DUPLICATE KEY UPDATEを使おう。）

3. SQLアンチパターン系

(4) カンマ区切りでデータが入っている（関連：SQLアンチパターン第1章）

課題

カンマ区切りでデータが入っている。
例1) master_employee.phoneに"090-1234-5678,080-8765-4321"というような感じでデータが入っている。これの課題は、SQLでの調査が辛いこと。電話番号が重複していないかなどの確認のために、Javaでコードを書いて文字列処理してチェックすることになった。
例2) budget_batch_taskという予算集計バッチタスクを管理するテーブルに、parameterというフィールドがあり、そこに"1,101,2,8,4"というようにパラメーターが入っている。この文字の羅列だと新人は何も分からないだろう。（実際には、「タスク優先順位1で、部門ID=101の部門に対して、2（マジックナンバー、ここでは週単位を意味する）で、過去8週の予算消化実績データを使って、未来4週の週次予算消化予測を集計する」というような意味となる。）

解決案

解決方法としては、以下の2つを採用したいと思っている。

• 例1の場合、別テーブルに切り出す。例えばemployee_phoneテーブルを作成し、そこにemployee_idとphoneフィールドを作成することで、関係性を記録する。
• 例2の場合、優先順位や部門IDは他のバッチタスクとの関連が強いので、独立したフィールドを作成する。残りの2,8,4に対しては、JSONなどでデータを入れることを検討したい。例えば{"NumPastWeeks": 8, "NumFutureWeeks": 4}の方が分かりやすい。

(5) SQLが超長い（関連：SQLアンチパターン第17章）

課題

ひたすらに長いSQLをMybatisのmapper.xmlに記述している。とにかくJOINが多く、長いものだと500行。以下が辛い。

• 途中経過が知りたくてもログを出力できない。結局、SQLの一部をDBeaverなどにコピペして逐一確認する羽目になる。
• 長いSQLに業務ロジックが入っている。業務ロジックがJava側とSQL側に散在していて改修が難しい。
• 通信量が増える。例えば、master_item（商品）とmaster_class（商品クラス）をJOINしたデータをSELECTする場合、全体としては商品単位だが、一部フィールドはクラス単位の（重複した）データが返ってくる。結果、フェッチの時間が増加する。

開発者に聞いたところ、長いクエリを書きたい主な理由は以下の2つ。

• クエリを流す回数を減らしたい
• 要素が多いので、IN (...)の形（クエリを分割すると2つ目以降はこの形になる場合が多い）だとパフォーマンスが心配。

解決案

まず設計方針として、業務ロジックは（できれば）Javaで書け。

• デバッグしやすい。
• APのリソースの方が、安いし、増やしやすい。
• クエリの実行時間を減らす方が、デッドロックの危険性が減る。

IN (...)に関しては、参考資料5(range_optimizer_max_mem_size)も考慮しつつ、要素数が数千くらいなら気にせず実施する。もっと増えるようなら複数回に分割してクエリを流すのが安全か。
そもそも、1クエリ（JOINの連続）の方が速い保証は無い。

(6) INDEXがどんどん増える（関連：SQLアンチパターン第12章）

課題

データが増えてきてクエリが遅くなった場合、とりあえずINDEXを何個も追加する。

解決策

意味がないINDEXを増やすな。EXPLAINしてくれ。
そもそもSQL長すぎて、SQL内のどの部分が遅いとか細かく確認してないだろ（怒）

(7) コード登録用テーブルができる（関連：SQLアンチパターン第5章+第2章）

以下のようなコードを登録したmaster_codeテーブルが、ある開発者によって作られていた。

table name: master_code
|code  |sub_code|  value|
|"0001"|     "1"|  "春物"|
|"0001"|     "2"|  "夏物"|
|"0001"|     "3"|  "秋物"|
|"0001"|     "4"|  "冬物"|
|"0001"|     "5"|  "通年"|
|"0002"|     "1"|  "日本"|
|"0002"|     "2"|  "中国"|
|"0002"|     "3"|  "タイ"|
|"0002"|     "4"|  "米国"|
|"0002"|     "5"|    "他"|
|"0003"|    "01"|  "新規"|
|"0003"|    "02"|"対応中"|
|"0003"|    "03"|  "完了"|
|"0003"|    "04"|  "中止"|
...

本人によると、テーブル数を減らすための方法らしいが、色々な問題がある。

• 見ただけでは、すぐに何のデータが入っているか分からない。
• JOINが面倒。JOIN master_code ON master_code = "0001" AND master_item.season_code = master_code.sub_codeのようになる。"0001"って何やねん。
• 外部キー制約がかけられない。

解決案

テーブル数が多い？気にするな、増やせ。

table name: master_season (PK: season_id)
|season_id|season_name|
|        1|      "春物"|
|        2|      "夏物"|
|        3|      "秋物"|
|        4|      "冬物"|
|        5|      "通年"|

table name: master_item (FK: season_id)
|item_id|...|season_id|
|      1|...|        1|
|      2|...|        5|
|      3|...|        2|
...

これなら外部キーで変なデータが入ることを防げる。

もしくは、別の選択肢として、ON/OFFのような値の種類が絶対に決まっている場合や、バッチ進行状態のような業務に関係がない場合(※)であれば、DBではENUMを使い、Java側でもENUMを定義すれば良い。
※業務に関係がある場合、別のフィールドが必要になる傾向がある。例えば、国名であれば、地域（アジア、アメリカ、ヨーロッパ。。。）などである。こういう情報はDBにちゃんと入れるべき。

参考までに、CHECKで制限するのは、下にある">="のような使い方をする時で、ENUMが使えるような時に使うのはやめた方が良い。

(8) 外部キーが無い（関連：SQLアンチパターン第4章）

課題

外部キーを作成しておらず、テーブルが増えてきた今、どこがどう関連しているのかを、ER図作成ツールなどで機械的に把握できない。各テーブルのデータの整合性が取れているか否かも、調査できてない。
これは一応理由がある。当初は外部キーを作成するつもりだったのだが、数万行単位のデータを一度にINSERTする要件が出た際、パフォーマンス観点よりSQL実行を非同期にしたら、外部キー制約によりINSERTが失敗してしまった。その場では、外部キー制約を無くすという短絡的な方法を取り、その後の開発でも外部キーを「とりあえず設定しない」で今に至ることになる。

解決案

• 大前提：外部キー参照先が消えた場合にデータとして成り立たなくなる場合は外部キーが必要。成り立つ場合は、外部キーは不要。（参考：参考資料3）
• 外部キーを作成しない場合は、id名などで繋がる先がわかるようにする。（例item_id, employee_id）
• 外部キーを作成したいが、1つの処理の中で参照元と参照先を非同期にINSERTする場合、外部キーによるエラーが発生する可能性がある。その場合、そもそも別々の処理（別々のトランザクション）でINSERT出来ないかを検討する。（一時的な開発の楽さよりも、今後のメンテが楽になる方法を優先する。）

余談

• 外部キーについて、私は今までCASCADEが正義だと思っていたが、特にON DELETEについては、SET NULLやNO ACTIONもちゃんと検討した方が良いかも。余談だが、AWS S3のバケット削除は、先に中のファイルを全削除する必要があるので、NO ACTIONっぽい動作。
• ついでに、UNIQUE制約も忘れずに付けた方が良い。Javaがバグった時にデータが汚れる。というか実際汚れた。

4. 名前は「ちゃんと」決めよう

(9) DBの各テーブルのid名が分かりにくい

課題

例えば、master_departmentという部門を示すテーブルのPKのフィールド名をidとするかdepartment_idとするかが統一できなかった。結果、各エンジニアの信条で、テーブルによって表記が揺れることになった。
さらに悪いことに、PKではない部分ではdepart_idなど省略した名称を使い始めた。

解決案

単純に言ってしまえば、ルールを決めれば良い。master系テーブルのPKならid、その他ではdepartment_id（どのマスターを指すかわかるようにする、勝手に省略しない）で統一したい。

(10) DBのテーブル名が分かりにくい

課題

各詳細情報を入れるテーブル名が統一できなかった。例えば、商品情報を入れるテーブルにしても、master_item（基本商品情報）、item_day_report（商品の日次販売データ）、item_week_plan_data（商品の週次販売計画データ）、item_week_detail（商品の週次集計データ）、item_year_sales_info（商品の年間販売データ）などが存在する、

解決案

誰もが理解できる命名ルールを考える。以下のは今の想定だが、これは意見が割れるかもしれない。

• 末尾_past: 過去データ
• 末尾_future: 未来データ
• 末尾_report: UIでのレポート表示用などのデータ
• _data, _infoなどは、中身が分かりにくいので使わない。（「全テーブル、中身はdataやろ」という話）
• データの単位によってday, week, month, quarter,yearなどを、テーブル名に入れる

(11) 単語を省略する：DBテーブルのフィールド名

課題

depa, depart, departmentなどの表記揺れが発生、同じテーブル内の別フィールドでも名称がブレるという頭を悩ませる事態に。
例：department_year_budgetという部門ごとの年間予算を保存するテーブルがある時、そのフィールドにdepart_id（部門ID）, depart_initial_budget（当初予算金額）、department_revised_budget（更新後予算金額）というようにdepartとdepartmentが混在する状況になっている。

解決案

以下のルールを強制したい。

• DBのフィールド名は省略不可とする。
• Javaコード内の一時変数であれば、（正直管理しきれないので）適度に。。。

(12) 単語を省略する：DBテーブルエイリアス

課題

Mybatisを使っているため、xmlファイルでSQLを管理しているが、その中のテーブル名エイリアスが適当。先ほどのmaster_departmentの例であれば、md、mDepa、dなどが混在している。

解決案

これもルールを決める。

• 基本は頭文字。master_departmentであれば、md。
• ただし被る場合は別途検討。例えばmaster_departmentとmaster_divisionがあるなら、前者はmdp、後者はmdv。

(13) DATEやDATETIMEのフィールドの名称が混在する

課題

フィールド名からではどの値が入れられるのか判断できない。

• created_at（作成日時、DATETIME型）
• start_date（開始日、DATE型、ただし業務上月曜日しか入れられない）
• sent_time（送信日、実はDATE型）

解決案

ルールを決める。やはり明文化が最強。

• 末尾_date: DATE型
• 末尾_time: DATETIME型
• 末尾_weekもしくは_monday: 月曜日しか入らないなど、特定の制限があるもの（MySQLならCHECKで制限できると良い）

(14) Javaコード内の変数名がdata1, data2, data3,...のような名付け方になっている。

課題

変数に何のデータが入っているのか、変数名から全く判断できない！
開発者当人は「コメント書いてるから良いだろ」という返答（良くねえよ）

解決案

コメントに頼るな！変数名で頑張れ！
ただ、1つ現実的な課題がある。弊社の設計書は（どこもそうか？）、業務ロジックの説明はあるが、変数名の指定までは無い。各開発者は業務ロジックが理解できない人も多く、「ロジックは設計書の通りに作りました」だと、結局適切な変数名をつけることができない。だからと言って、変数名まで私が指定するのは作業量的にキツい。
良い方法があれば教えてください。

参考

1. https://amazon.co.jp/SQL%E3%82%A2%E3%83%B3%E3%83%81%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3-Bill-Karwin/dp/4873115892 SQLアンチパターン
2. https://www.slideshare.net/slideshow/ronsakucasual/52256922 SQLアンチパターン 幻の第26章「とりあえず削除フラグ」
3. https://blog.j5ik2o.me/entry/2020/06/16/105311 外部キー制約は何も考えずに適用するとよくない
4. https://zenn.dev/praha/articles/2667cbb1ab7233 外部キー制約が一切ないと何に困るのか？
5. https://zenn.dev/lowzzy/scraps/fa3f1a7276b24c IN句に大量の要素を渡すと死ぬ理由の補足メモ
6. https://zenn.dev/convers39/articles/0e58e17d0da43f SQLアンチパターン感想その二ーENUM型を扱う