どうも、最近システムエンジニアの出てくる海外映画をよく見る人です
今日は「本当にあったやらかしDB設計②【囚人番号テーブル】」に続いてびっくりしたことを紹介します
#ロジカルクエリー
これ、本当に良く見かけます
どういうことかというと、本来アプリケーションで処理するべき機能を無理矢理クエリーに詰め込む、ということです
###何が悪いの？？
DBというのはデータという商品の入った、ただの倉庫です
RDMBSという倉庫番が居るため、倉庫に入れる前に商品を検査することができます
倉庫番(RDBMS)は商品を倉庫に入れたり、取り出したりすることが仕事であり、その道のプロです
そこにアプリくんがやってきました
アプリくんはレシピを持っていて、こう言いました
「RDBMSくん、このレシピあげるから倉庫に入っている商品を調理して完成品をちょうだい。もちろん、きれいにね」
この後どうなってしまうでしょうか
RDBMSくんも商品を調理することはできますが、明らかに本業ではないですよね
RDBMSくんはなんとか商品を調理して、完成品をアプリくんに渡すことができました
しかし、レシピ通りに作ったのでその完成品が本当にアプリくんが欲しいものかどうかはわからない上に、そのレシピ自体が正しいのかどうかもよくわかりませんでした
このレシピがロジカルクエリーです
SELECT文を発行するときによくロジカルクエリーが使われてしまいます
###問題
ロジカルクエリーには数々の問題が隠されています
-
クエリーを見ただけでは何をしているのかがほぼわからない
ロジカルクエリーは無駄に複雑です
SQLというのは書き方がシンプルで僕は非常に好きなのですが、初めてロジカルクエリーを見たときは吐き気がしましたｗ -
クエリーの変更ができなくなる
クエリーを見ても何をしているのかがわからないため、実質的にブラックボックス化します
そのため、後から部分的な変更を加えることができなくなります
更に、もし想定しているデータと違うデータを返してきていたとしてもそれを判定する手段がありません
ライブラリーは人間が使うため、多く使われれば使われるほどフィードバックが集まりやすくなり機能が洗練されますが、クエリーはアプリケーションが使うため、間違っていたとしても人間が気付くのは非常に難しいです
運良くどこかでエラーになれば問題を突き止めて修正することができますが、エラーにならなければ問題に気付くことすらできません -
異常に遅い
ロジカルクエリーは当然遅いです
僕の知っているロジカルクエリーはレコードが500行前後のテーブルを4つか5つ参照して結果を返していたのですが、およそ200msec(0.2秒)掛かっていました
Webサービスの場合、クライアントから通信が開始され、リクエストがサーバーに到達し、WebサーバーがDBにアクセスし、DBから返ってきた結果を元にレスポンスを生成してクライアントに返す、ということをしているため、DBアクセスだけに200msecも掛かってしまうとネットワーク上の遅延を含めて実際に使う人間に無駄にストレスを与えてしまいます
ネットワーク上の遅延を減らすためにCDNを使って距離を縮めたり、適宜WebサーバーでDBから返ってきたデータをキャッシュしておくことでDBアクセスの回数を減らすことは可能ですが、クエリーを早くするほうが単純で簡単です
###原因
なぜこのようなことが起こってしまうのか、いくつか考えてみました
- 複数のクエリーをひとつのクエリーにまとめようとしてしまっている
- なんでもかんでも結合とサブクエリーで解決しようとしてしまっている
- アプリケーションの変更ができない
こんなところでしょうか
複数のクエリーをひとつのクエリーにまとめようとしてしまっている
クエリーが複雑になる原因として、ひとつのクエリーに意味を持たせすぎる、というのがあります
ひとつのクエリーにはひとつの意味、となるようにするのが基本的なクエリーの使い方です
ひとつの意味だけでもクエリーを作成するのは意外と大変ですよ
なんでもかんでも結合とサブクエリーで解決しようとしてしまっている
これが一番悪質です！！
大層なクエリーを書いて満足してしまう人っていますよね
ここまで並べてきた問題点を上げて修正するように言っても「動いてるんだからいーじゃん」と言われて放置されるという…
こういうことがあるから「動けば正義」みたいな考え方は嫌いなんですよね…w
それはともかく、その問題はアプリケーション側で解決するべき問題なのか、DB側で解決するべき問題なのかをはっきりさせる必要があります
クエリーはアプリケーションの一部、と理解してしまっている人がいるかもしれませんが、それは半分合っていて半分違います
クエリーというのはアプリケーションが利用する、DBにアクセスするためのDB側が提供する機能です
しかし、クエリーはDB側が管理している訳ではないので、構文エラーかどうかくらいのチェックしか行いません
そのため、クエリーの書き方に完全に依存してしまいます
問題の発生源、解決方法を正しく考えることで、より良い選択ができるようになります
アプリケーションの変更ができない
これ、運用中によくあるパターンなのかなと思います
アプリケーションの変更は加えたくない、でも抽出するデータは変えたい、、、こう聞くと意味不明ですがw
このような、どうしてもDB側で変更を加えたいという場合はPL/SQLを慎重に使ってください
PL/SQLはロジカルクエリーの唯一の例外です
PL/SQLを使う場合は可読性に注意して、実質的なブラックボックスにならないようにしてください
ドキュメントも残しておくべきです
誤解してほしくないのは、PL/SQLの最大の利点はアプリケーションが発行するクエリーの回数を減らすことができる、ということです
アプリケーション側で解決するべき問題をPL/SQLに押し付けるのは根本的には良い考えではありません
###解決方法
######シンプルなクエリーを書こう！
クエリーは演算処理をするための道具ではありません
RDBMSくんの仕事はデータを倉庫に入れること、取り出すことです
これを忘れないでください
######実行速度を測定しよう！
長いクエリーを書く人、意外とこれやらないんです
特に、複数のテーブルを参照したりする場合には実行速度を測定してみてください
長く運用されてきたDBだと、クエリー自体はそこまで複雑ではなくても、もしかしたら実行計画のせいで遅くなっているかもしれません
######クエリーチューニングをしよう！
既存のロジカルクエリーはクエリーチューニングするしか解決手段がありません
ただし、ロジカルクエリーは実質的にブラックボックス化してしまっているため解読に時間が掛かり、もしかしたらアプリケーション側の変更が必要になるかもしれません
しかし、その代わりに実行速度が抜群に早くなりそれ以降のメンテナンスが簡単になります
#まとめ
- 「使える」と「使うしかない」は別物、本当にクエリーじゃないとダメか？
- 実行速度を無視したクエリーを作るな
- 致し方ない場合は必ずドキュメントを残せ
ロジカルクエリー、どれだけ有害なことかわかって頂けたでしょうか…
クエリーは作る人によってクオリティが変わってきます
より優れたクエリーを書いてください
#おまけ(2020/8/11追記)
ロジカルクエリーに対してこのような意見もあるようでした
アプリケーションで処理するよりも、DBで処理したほうが早いからロジカルクエリーを使ったほうが良い
この意見にも一理ありますが、これには別の問題が隠れています
いつ最適化を行うのかという問題です
これには既に結論が出ています
「最適化はするな」
「(専門家に対して)最適化はまだするな」
です
ロジカルクエリーをシステムの立ち上げ段階で使うべきではありませんが、成熟しているシステムのパフォーマンス改善のために慎重に行うのであれば許されるかもしれません
それを行うにしても、適切なカプセル化を施したり、ドキュメントを残す必要はあります