SQL

6月一発目のhogehoge会はエンジニアゆーさんプレゼンツ「SQLアンチパターンナイト」

DB(データベース)を操作するデータベース言語SQLの使い方についてみんなでディスカッションしようというテーマです。
今回は、技術本からアンチパターン(記述や設計の際に避けるべき事柄)をピックアップし、みんなで議論・ノウハウを共有していきます。

ちなみに、hogehoge会は今回で第6回目!今までは
第1回目は「リモートhogehoge会「リモート環境での困ったことの共有&俺スゲー環境自慢
第2回目は「第2回 hogehoge会の実施 -アプリを作ってみよう-
第4回目は「第4回hogehoge会 -個人開発「川柳アプリ」の紹介-
第5回目は「第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の方が早くて、JOIN使いまくった挙句死んでるというパターンがある。それはINDEXがちゃんとはれて無いとか、能力を超えた複雑なクエリを吐きまくっている可能性がある。ちゃんとJOINを理解して書けている人は、JOINして持ってくるレコードが少なくなっているはずだから、処理が軽くなっているはず」
うるおじ「JOINするときはINDEXを相互に行なっているカラムどうしでJOINが鉄則」

 
まとめ
・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をはらないようにという事ですね」

まとめ
・ソシャゲとかで使う場面はある(高速性を期待した実装)

・無意味なところにINDEXをはるのはやめよう

 

4.NoSQLの使いどころ


NoSQL
うるおじ「俺もききたーい。NoSQL聞きたいおじさん」
※NoSQL・・・Not only SQLの略で、 SQLを使わずに操作できるデータベースを指す。

赤ちゃん「私は前職で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変わってデータをとる場所が別になるよとなると再度考え直さないといけないって事?」

くーちゃん「UI変わるよってなったら設計を考えないといけないね。でも柔軟に変えられるところもNoSQLのいいところだと思うし、僕が前に担当していた案件も、NoSQLにしたおかげで仕様変更がしやすかった。ですよね、シンさん」
シンさん「ACIDいらないなら、RDBはいらないですね」(ACID =関連する複数の処理を一つの単位として管理するトランザクション処理に求められる4つの特性の頭文字をとったもの)
シロー「あ〜トランザクションないもんね、NoSQLには」

うるおじ「データを何にしたいかによってNoSQLにすべきかRDBにすべきか、さっきシンさんが書いてくれたACIDがいらないならっていうのがまさにそういう事かなと。お金の管理などをする場合トランザクションがないなどはもはや論外になるのでNoSQLっていう選択肢は多分無くなるんだろうなっていう」
ゆーさん「そうですね」
うるおじ「データ構成を書き換えるのが NoSQLのメリット。逆を意味すれば型が変わらないようにしておいてデータの保守性をちゃんと担保できるというところがRDBのメリットになってくるので、優先すべき事項が何なのかによって使い分けるべきかなと」
うるおじ「まあでもRDB大好きなおじさんとしてはRDBから抜け出せないんですよね〜〜」
赤ちゃん「RDBの良いところってデータが厳密ですよね、入れるのも。NoSQLって何でもJSONの中に入ってしまうので扱い方に注意しないといけない部分はありますね」
うるおじ「MySQLでJSON型を使うんだったらJSONの中身を取り出してINDEXを貼ったりする事も一応できるので、ちゃんと型を決めた方が良いよね」

 
まとめ
・NoSQL or RDB? → 優先順位を考えよう

 

5.デッドロック


シロー「デッドロックってわかりやすく言うとどんな感じなの?」

うるおじ「例えば俺とシローが更新してて、俺はAのテーブル全部を更新かけます、シローはBのテーブル全部に更新をかけます。それが同時に動き始めたら、俺がBを更新しようとしてもシローがB更新してるし、シローがAを更新しようとしても俺がAを更新してて更新できない、みたいな状態になる。デッドロックが発生している」
シロー「なるほど」
うるおじ「デッドロックが発生すると処理自体も返ってこないとかもあり得るからずっと固まっちゃう。ロックを解除するまで待たされる事になる」
ゆーさん「今のMySQLのデフォルトは短くなっていると思うんです古いSQLだとTIMEOUTの設定が2時間とかあってずっと障害になってました」

うるおじ「デッドロックの話、過去案件であったのがAテーブルとBテーブルを更新しないといけない時、A→Bの順番で更新するところをB→Aの順番で更新した人がいてデッドロックが発生してしまった事がある」
ゆーさん「チーム開発しているとそこら辺の統制って難しいですよね」
うるおじ「基本的に1→Nの関係になっている構成であれば1→Nの順番で更新するっていうのをルール化すればいいと思う。上から更新します、下から更新しますってなると上を更新しようとするとトランザクション中でロックがかかる」
ゆーさん「1→Nの順番で更新するという規約でチーム開発をやっていればOKですよという事ですね」
うるおじ「更新順番を揃えるっていうのは正しいと思います。実際にデッドロックは起こりえる問題なので」
ゆーさん「DBは奥が深い」
くーちゃん「DBエンジニアっていうそれだけの職種もありますからね」

 

まとめ
1→Nの関係なら1→Nの順番で更新するルールにする規約が良いぞ

 

6.売り越し厳禁


うるおじ「チケットの売越を防ぐためにSELECT…FOR UPDATEを検討した事があった。SELECT FOR UPDATEはSELECTした時点でその行を掴んでしまうというやり方」
ゆーさん「ふむふむ」

うるおじ「ただ、チケットの購入処理の負荷試験で、どんどん処理が溜まって… 解放されるまで時間がかかった。 

SELECT FOR UPDATEは正確だけど遅くなって処理速度が担保できない。なので結局それは採用せず、売り越しが発生したときはデータを消すようにした。運用でカバーしたパターン」

シン「売り越し、Amazonみたいなシステムには大変ですね」

くーちゃん「売り越しってなんですか?」

シン「売り越しは在庫ないのに売れちゃうこと」
うるおじ「例えば、お菓子を100個用意しました。101個売れちゃいました。そうすると残りの1個はどうするの?!ってなるよね。売り越しは絶対発生させちゃいけない。これはECの開発では鉄則だYO!売り越し絶対禁止。当たり前だぞ!」

くーちゃん「なるほど」
 
 
まとめ
・ECを1から作るのであれば売り越しって概念を知っておかないとやばいぞ
 

 


7.DISTINCT


うるおじ「ところでDISTINCT(重複行の削除)使ってますか?」
ゆーさん「使ってます」
うるおじ「私からすると、DISTINCTは使うなよ、ボ○!って言いたくなります。 どうしてもっていうならありだが…重複消せば良いっしょっていう考えはまじで怒る」
シロー「重複を消すのがボトルネックになる?」
うるおじ「例えば、A, B, Cの1万レコードずつのテーブルがJOINしました。A * B * Cで1億レコードです。それを頑張ってDISTINCTすると…1億レコードを1レコードにするとなると?ちょー負荷になるぞ!!!!はるか昔、そんな処理があってげきおこした時があった。 チーム内でDISTINCT禁止の規約を定めたこともある。DISTINCTは甘えだ!!!」
おじさん「それがJOIN禁止にも繋がる論理ですね」
シン「そうするとDISTINCT使わない場合、どうすれば重複しないよう処理できるか教えてもらえますか」

うるおじ「EXIST(存在検査)を使え!!EXISTではINDEXをはってちゃんと副問い合わせせよ。」
シン「おー。EXISTはサブクエリなので、遅くなる可能性も・・?」
うるおじ「DISTINCTをするくらいならEXITの方が早い」
赤ちゃん「検索対象のデータと取得対象のデータを最小限にするみたいな?」
うるおじ「例えば、抽出しなければいけないのはAです、検索対象はBです。 そんな時にEXISTにしろボ○ケ!」
うるおじさんの洗礼を受けた参加者達。

まとめ
・DISTINCTは甘えだ!
・重複処理はEXITを使うのがおすすめ
 
 
 

8.VIEW


リモートhogehoge会
うるおじ「皆さんVIEWは使った事ありますか?」
ゆーさん「あまりないです」
うるおじ「VIEWとは、AテーブルとBテーブルがあったとします。それがJOINした状態で1つのテーブルのように扱いたい時、VIEWという概念を使う事ができる。CREATE VIEWという形で作るとあたかもその2つのテーブルが1つのテーブルかのように取り扱う事ができる」
ゆーさん「なるほど」
うるおじ「これってAとB同時に取りたいよっていうケースが沢山あったら作りたいと思うじゃないですか。ただ注意が必要です。
理由としてはVIEWはあくまで副問合せになる。意味合い的にはこれ

と同意義なんですよね。

 
ではこれの何が問題なのか?
実は副問合せはINDEXが無効化されてしまう懸念があります。
副問合せの第1階層ではINDEX効くんですけど、第2階層、つまりこのような時、
これは条件をかけてもINDEXがきかないです。副問合せが2個になると無効になります。
 
例えば、下記

これ、id = 1をプライマリーキーだとするじゃないですか。でもINDEX効かないんでフルスキャンになります」
ゆーさん「まじかーーーー」
うるおじ「1個だった場合はちゃんとINDEX効きます。ただ、副問合せが2個になった場合は効かなくなるっていうRDBの特性があります」
 
シン「

これだったらIndex利用できるようになるね」

うるおじ「これはどれで使える書き方ですか?」
シン「MySQLです。MERGE使ったらINDEXが使えるようになるかもですね」
うるおじ「こういう書き方もあるんですね。これは初めて知りました。普通にCREATE VIEWでやっちゃうとダメだけど、という事ですね」
シン「多分MySQLの8からかな」

うるおじ「なるほど。MySQL5.7と8って結構違うんですね。」
 

うるおじ「というわけで、VIEWを開発で使う際は、全員がちゃんと理解しているか注意が必要です」
 
まとめ
・VIEWは副問合せが2個になるとINDEXが効かなくなる
・VIEW使用には開発者全員のちゃんとした理解が必要
 
 
 

9.物理削除について


 
物理削除
おじさん「ところでレコードの物理削除はどう思う?」
くーちゃん「削除されるファイルの性質によりますね。例えば削除して、別に今後使われるものでもなければ物理的にいくこともあります。しかし例えばユーザー情報とか投稿とか、物理削除してしまったら何も残らなくなってしまう」

おじさん「ちなみに物理削除した時に、MySQLを再起動すると何が起きるか知ってますか」
ゆーさん「何が起きるんですか・・」
おじさん「トラブルが起きる可能性がある。あ、AUTO INCREMENTを使ってればだけどね。でもAUTO INCREMENTは使うじゃないですか、大体」
おじさん「レコードを物理削除して、末端のレコードを出した場合、MySQLを再起動するとAUTO INCREMENT値が残ってる最後のINCREMENT値に変わる。ってことは関連テーブルにレコードが残っていると新規レコードができた場合に同じIDがふられちゃって、残っている関連テーブルのレコードが紐づく事になる、っていうバグがある」
ゆーさん「やばやば」
うるおじ「つまりは設計が大切。」

まとめ
物理削除 or 論理削除はデータの特性によって使い分ける
 
 
 
 

10.AUTO INCREMENT


シン「ところでAUTO INCREMENT使わないべき?」
うるおじ「AUTO INCREMENTでプライマリーにしてちゃんと管理しているんだったらAUTO INCREMENTでも良いかなと」
おじさん「あとはUUIDにするとか?まあでもUUIDも衝突しないって言われているだけで確率は0じゃないからね」

シン「AUTO INCREMENT使ったら冗長化しにくいかなと思っております」
うるおじ「AUTO INCREMENT値でやっても良いと思いますし、おじさんが言う通り、UUIDって衝突する可能性が0じゃないけど、1億回か1兆回に1回衝突するくらいのすごい低確率だからシステムを運用している期間とデータを登録する量でいうとほぼゼロなんだよね」
おじさん「あとさ、AUTO INCREMENT値は、リリース当初は絶対1番からはじまるじゃん。ユーザーがそこを見ると『このサービス、人少ないんだな』ってわかると」
ゆーさん「ああ(笑)」
うるおじ「でもAUTO INCREMENTはスタート決められるじゃん」
おじさん「なので、スタートの時は値をテキトーな値にしてくださいっていう教えです(笑)」
うるおじ「俺、AI値で管理してて、1番からは嫌なんですけどって言われて10000001から始めたことあるよ」
おじさん「意味わかんねえ(笑)まあ俺も確かに最初はそんな感じだった」

うるおじ「結局UUIDがプライマリーと同意義になるってことでしょ?こういうのも含めて設計だと思うのですよ。AUTO INCREMENTだとこういう弊害があるからこのシステムではどうかというのを検討したうえでAUTO INCREMENTにするのかUUIDにするのかを決めれば良いだけの話であって。
システム的にAUTO INCREMENTを使うべき使わないべき、というよりもこのシステムが3年5年後にどうなっていくからこうしていきましょうっていう話をする方が建設的なのかなという印象です」

 

まとめ
・AUTO INCREMENTの使用はシステムの運用を中長期的に考えて検討しよう
・IDが1から始まるとはずかしいぞ
 
 

おしまい

以上でSQLアンチパターンナイトは終了!
実に10個(小話を含めるともっと多い)のテーマが議論され、とても白熱した第6回目となりました!

うるおじさんを筆頭に皆さんがSQLの知見を余す事なく共有して下さり、「とても勉強になった」という参加者の声が多々聞こえました!
企画者のゆーさん、お疲れ様でした!
 
それでは引き続き、次回のhogehoge会もお楽しみに!!

Follow me!