６月一発目のhogehoge会はエンジニアゆーさんプレゼンツ「SQLアンチパターンナイト」！
DB（データベース）を操作するデータベース言語「SQL」の使い方についてみんなでディスカッションしようというテーマです。
今回は、技術本からアンチパターン（記述や設計の際に避けるべき事柄）をピックアップし、みんなで議論・ノウハウを共有していきます。
ちなみに、hogehoge会は今回で第6回目！今までは
第1回目は「リモートhogehoge会「リモート環境での困ったことの共有＆俺スゲー環境自慢」
第2回目は「第２回 hogehoge会の実施 -アプリを作ってみよう-」
第4回目は「第4回hogehoge会 -個人開発「川柳アプリ」の紹介-」
第5回目は「第５回hogehoge会の実施 -FizzBuzzを日本語でプログラミング、からの色々な手法へ-」
※第3回目はブログではなく別の場所で近日公開!!
と開催してきました！
今回はSQLマスターこと・エンジニアのうるおじさんを中心にSQLの知見を惜しみなく披露して頂き、大変盛り上がりました！
それではいってみましょう！
SQLとは
SQLは、データベース言語の中で、最も普及している言語の一つです。DBMS（データベース管理システム）上でデータやデータベースを制御するための言語であり、ユーザーやシステムからの命令を受けてRDB（リレーショナルデータベース）にクエリ（問い合わせ）を行い、結果を返します。返ってきた結果はモニターなどに表示されます。
SQLはデータベース言語としてISO（国際標準化機構）で規格化されています。そのためほとんどのRDBで同じように利用できます。SQLの使えるデータベースには、Oracle社のOracle Database、Microsoft社のMicrosoft SQL ServerやAccess、オープンソースのMySQLやPostgreSQLなどがあり、それぞれ異なるデータベース・エンジンを搭載していますが、どれもSQLで操作可能です。
参照：https://hnavi.co.jp/knowledge/blog/sql/
という事で、RDBというデータベースを操作する時に使うのがSQLなんですね。
アンチパターンを紹介
SQLのアンチパターンを議論していきます。
1.JOIN（結合）禁止
おじさん「JOINの方が早くて、JOIN使いまくった挙句死んでるというパターンがある。それはINDEXがちゃんとはれて無いとか、能力を超えた複雑なクエリを吐きまくっている可能性がある。ちゃんとJOINを理解して書けている人は、JOINして持ってくるレコードが少なくなっているはずだから、処理が軽くなっているはず」
うるおじ「JOINするときはINDEXを相互に行なっているカラムどうしでJOINが鉄則」
・JOINを使う場合はONの時のカラムにINDEXを貼ろう
2.スパゲッティクエリ
うるおじ「この文章中のUNIONはおそらくUNION ALLを指している気がするな。UNIONしちゃうとDISTINCTとGROUP BYが走っちゃうので」
まとめ
・UNIONとUNION ALLは違う
・UNION使うならUNION ALLを使おう
3.正規化くずし
うるおじ「データの正規化をするとテーブルが増えちゃうので、検索速度を上げるためにあえて非正規化するパターンはある。俺は経験した事ないけど、ゲーム系の案件で、非正規化したテーブルで情報を冗長的に持つっていうパターンはあるそうです」
赤ちゃん「なるほど。中間データではないですけど、すぐ使えるようにしておくというイメージですかね」
ゆーさん「確かにソシャゲなどではむしろ正規化くずししないとやっていけませんみたいな」
うるおじ「そうらしいね。正規化くずしをするとデータを冗長に持つ事になるので業務系システムだとまずないかなと思いますね。INDEXの貼りすぎも違うところでボトルネックがでるしね」
ゆーさん「更新の時に遅くなるんでしたっけ」
うるおじ「そうですね、INDEXを貼りすぎると致命的に登録と更新が遅くなりますね。主に遅くなるのは更新。INDEXの再構築が完全に動き始めているので、速度が大幅に落ちる可能性がある。無意味なところにINDEXをはるのはやめよう」
赤「勉強になるわ〜」
ゆーさん「WHEREとかJOINとかでINDEXが必要なところ以外はINDEXをはらないようにという事ですね」
まとめ
・ソシャゲとかで使う場面はある（高速性を期待した実装）
4.NoSQLの使いどころ
赤ちゃん「私は前職でIoT系の開発をやっていた時にNoSQLのデータベースとか列指向データベースなどを使っていました。IoTは接続するデバイスの種類が増えていったりするので、RDBでデータを持っていると列追加するのがすごい大変になるのですが、NoSQLだとJSONなのでノーダウンタイムでカラム追加できて運用がしやすいという理由で採用されてました」
くーちゃん「ちなみにFirebaseもNoSQLですよね」
赤ちゃん「FirebaseはUIが便利すぎてDBって感じがあんまりしないんですよね」
うるおじ「NoSQLが良いと思った点でいうと、『多階層構造』を持っているという状態だとNoSQLの方が良いのかなって思いました。推測にはなるんだけど、多階層になってくるとRDBだとJOIN,JOIN,JOINとなってしまうから、NoSQLの方が管理しやすいのかなって」
シロー「俺の川柳のDB、Firebase使っててNoSQLだけど、Keyの持ち方をどうしようかなって考えてる」
くーちゃん「まずWEBで色々読み込むデータがあるじゃん。それは基本的に全部入れておくべきかな。Keyだけで色々探す処理を毎回毎回かけるよりかはあらかじめ全部入れておく。だからNoSQLの設計っていうのはフロントで何を読み込まれるっていうのも意識してやった方が良いっていうのはよく聞く事だね」
シロー「それって画面のUI変わってデータをとる場所が別になるよとなると再度考え直さないといけないって事？」
シンさん「ACIDいらないなら、RDBはいらないですね」（ACID =関連する複数の処理を一つの単位として管理するトランザクション処理に求められる4つの特性の頭文字をとったもの）
シロー「あ〜トランザクションないもんね、NoSQLには」
うるおじ「データを何にしたいかによってNoSQLにすべきかRDBにすべきか、さっきシンさんが書いてくれたACIDがいらないならっていうのがまさにそういう事かなと。お金の管理などをする場合トランザクションがないなどはもはや論外になるのでNoSQLっていう選択肢は多分無くなるんだろうなっていう」
ゆーさん「そうですね」
うるおじ「まあでもRDB大好きなおじさんとしてはRDBから抜け出せないんですよね〜〜」
赤ちゃん「RDBの良いところってデータが厳密ですよね、入れるのも。NoSQLって何でもJSONの中に入ってしまうので扱い方に注意しないといけない部分はありますね」
うるおじ「MySQLでJSON型を使うんだったらJSONの中身を取り出してINDEXを貼ったりする事も一応できるので、ちゃんと型を決めた方が良いよね」
・NoSQL or RDB? → 優先順位を考えよう
5.デッドロック
シロー「デッドロックってわかりやすく言うとどんな感じなの？」
ゆーさん「今のMySQLのデフォルトは短くなっていると思うんです古いSQLだとTIMEOUTの設定が２時間とかあってずっと障害になってました」
うるおじ「デッドロックの話、過去案件であったのがAテーブルとBテーブルを更新しないといけない時、A→Bの順番で更新するところをB→Aの順番で更新した人がいてデッドロックが発生してしまった事がある」
ゆーさん「チーム開発しているとそこら辺の統制って難しいですよね」
うるおじ「基本的に１→Nの関係になっている構成であれば１→Nの順番で更新するっていうのをルール化すればいいと思う。上から更新します、下から更新しますってなると上を更新しようとするとトランザクション中でロックがかかる」
ゆーさん「1→Nの順番で更新するという規約でチーム開発をやっていればOKですよという事ですね」
うるおじ「更新順番を揃えるっていうのは正しいと思います。実際にデッドロックは起こりえる問題なので」
ゆーさん「DBは奥が深い」
くーちゃん「DBエンジニアっていうそれだけの職種もありますからね」
1→Nの関係なら1→Nの順番で更新するルールにする規約が良いぞ
6.売り越し厳禁
ゆーさん「ふむふむ」
くーちゃん「売り越しってなんですか？」
7.DISTINCT
ゆーさん「使ってます」
うるおじ「私からすると、DISTINCTは使うなよ、ボ○！って言いたくなります。 どうしてもっていうならありだが…重複消せば良いっしょっていう考えはまじで怒る」
シロー「重複を消すのがボトルネックになる？」
うるおじ「例えば、A, B, Cの１万レコードずつのテーブルがJOINしました。A * B * Cで１億レコードです。それを頑張ってDISTINCTすると…1億レコードを1レコードにするとなると？ちょー負荷になるぞ！！！！はるか昔、そんな処理があってげきおこした時があった。 チーム内でDISTINCT禁止の規約を定めたこともある。DISTINCTは甘えだ！！！」
おじさん「それがJOIN禁止にも繋がる論理ですね」
シン「そうするとDISTINCT使わない場合、どうすれば重複しないよう処理できるか教えてもらえますか」
シン「おー。EXISTはサブクエリなので、遅くなる可能性も・・？」
うるおじ「DISTINCTをするくらいならEXITの方が早い」
赤ちゃん「検索対象のデータと取得対象のデータを最小限にするみたいな？」
うるおじ「例えば、抽出しなければいけないのはAです、検索対象はBです。 そんな時にEXISTにしろボ○ケ！」
まとめ
・DISTINCTは甘えだ！
8.VIEW
ゆーさん「あまりないです」
うるおじ「VIEWとは、AテーブルとBテーブルがあったとします。それがJOINした状態で１つのテーブルのように扱いたい時、VIEWという概念を使う事ができる。CREATE VIEWという形で作るとあたかもその２つのテーブルが１つのテーブルかのように取り扱う事ができる」
理由としてはVIEWはあくまで副問合せになる。意味合い的にはこれ
select * from views; select * from (select * from aaa)
と同意義なんですよね。
実は副問合せはINDEXが無効化されてしまう懸念があります。
副問合せの第１階層ではINDEX効くんですけど、第２階層、つまりこのような時、
select * from (select * from (select * from aaa))
select * from (select * from (select * from aaa)) where id = 1;
これ、id = 1をプライマリーキーだとするじゃないですか。でもINDEX効かないんでフルスキャンになります」
ゆーさん「まじかーーーー」
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS SELECT c1, c2 FROM t WHERE c3 > 100
これだったらIndex利用できるようになるね」
うるおじ「これはどれで使える書き方ですか？」
シン「MySQLです。MERGE使ったらINDEXが使えるようになるかもですね」
うるおじ「こういう書き方もあるんですね。これは初めて知りました。普通にCREATE VIEWでやっちゃうとダメだけど、という事ですね」
シン「多分MySQLの8からかな」
9.物理削除について
くーちゃん「削除されるファイルの性質によりますね。例えば削除して、別に今後使われるものでもなければ物理的にいくこともあります。しかし例えばユーザー情報とか投稿とか、物理削除してしまったら何も残らなくなってしまう」
おじさん「ちなみに物理削除した時に、MySQLを再起動すると何が起きるか知ってますか」
ゆーさん「何が起きるんですか・・」
おじさん「トラブルが起きる可能性がある。あ、AUTO INCREMENTを使ってればだけどね。でもAUTO INCREMENTは使うじゃないですか、大体」
おじさん「レコードを物理削除して、末端のレコードを出した場合、MySQLを再起動するとAUTO INCREMENT値が残ってる最後のINCREMENT値に変わる。ってことは関連テーブルにレコードが残っていると新規レコードができた場合に同じIDがふられちゃって、残っている関連テーブルのレコードが紐づく事になる、っていうバグがある」
ゆーさん「やばやば」
うるおじ「つまりは設計が大切。」
物理削除 or 論理削除はデータの特性によって使い分ける
10.AUTO INCREMENT
シン「ところでAUTO INCREMENT使わないべき？」
うるおじ「AUTO INCREMENTでプライマリーにしてちゃんと管理しているんだったらAUTO INCREMENTでも良いかなと」
おじさん「あとはUUIDにするとか？まあでもUUIDも衝突しないって言われているだけで確率は０じゃないからね」
シン「AUTO INCREMENT使ったら冗長化しにくいかなと思っております」
うるおじ「AUTO INCREMENT値でやっても良いと思いますし、おじさんが言う通り、UUIDって衝突する可能性が０じゃないけど、１億回か１兆回に１回衝突するくらいのすごい低確率だからシステムを運用している期間とデータを登録する量でいうとほぼゼロなんだよね」
おじさん「あとさ、AUTO INCREMENT値は、リリース当初は絶対１番からはじまるじゃん。ユーザーがそこを見ると『このサービス、人少ないんだな』ってわかると」
ゆーさん「ああ（笑）」
うるおじ「でもAUTO INCREMENTはスタート決められるじゃん」
おじさん「なので、スタートの時は値をテキトーな値にしてくださいっていう教えです（笑）」
うるおじ「俺、AI値で管理してて、１番からは嫌なんですけどって言われて10000001から始めたことあるよ」
おじさん「意味わかんねえ（笑）まあ俺も確かに最初はそんな感じだった」
うるおじ「結局UUIDがプライマリーと同意義になるってことでしょ？こういうのも含めて設計だと思うのですよ。AUTO INCREMENTだとこういう弊害があるからこのシステムではどうかというのを検討したうえでAUTO INCREMENTにするのかUUIDにするのかを決めれば良いだけの話であって。
システム的にAUTO INCREMENTを使うべき使わないべき、というよりもこのシステムが３年５年後にどうなっていくからこうしていきましょうっていう話をする方が建設的なのかなという印象です」
・IDが１から始まるとはずかしいぞ
おしまい
投稿者プロフィール
