第1回目はインデックスに関して書いたけど、さて、第2回目はなーにを書こうかな...?
ってことで、今回も理論をよくわかってない輩が適当に書いてきますかね(汗)

第2回目は、複数のテーブルの検索を行うときDBは何をしてるんだろ?ってことを考えてみようかね。

まあ、よくあるこんな2つのテーブル。

[テーブルA]
[Hobby]
[code(テーブルBのcode参照)]

[テーブルB]
[code(主キー)]

テーブルAをある条件で検索して、CodeでリレーションされるテーブルB(マスタ系)のデータも引っ張ってくるってのは定石ですねぇ。SQL書いてみると...

SELECT テーブルA.*, テーブルB.*
  FROM テーブルA 
  LEFT JOIN テーブルB 
    ON テーブルB.Code = テーブルA.Code
 WHERE テーブルA.Hobby = 'SM';

...みたいな感じ。

もし、DBに任せず自分で頑張って探すとすると...

  1. テーブルAの中からHobby = 'SM'のデータを抽出する。
  2. 抽出したデータ全件について、1件ずつテーブルBから該当するCodeのデータを探し出してやる。(テーブルBから探す際には、Codeは主キーなのでインデックスがついてると仮定すると目次引きができますね)

...ってなことをやることになるかな。これ、DBもほぼ同じことをやってるわけ。

ただ、DBの場合はちょっとだけ人間と違うことをやってるかな?基本的にDBのデータってHDD上に書かれてるので、データを捜査する場合どうしてもHDDにアクセスしていかなければいけない。それってPCの処理にとってはすごーくボトルネックになる部分。なので、テーブルBのデータをできればメモリに確保して、2.の処理はメモリアクセスだけにしてやりたい。だけど、メモリに読み込むにもサイズ上の制限はあるのでテーブルBのCodeのインデックス(主キーなのでインデックスがついてると仮定する)からデータが入ってそうなページだけでも読み込みたい...みたいなことを水面下であくせくやってるんですね。

まあ、昨今のDBエンジンならテーブルAの検索性能さえ稼げれば、テーブルBのデータはスコンと引っ張れるのでDBにしてみれば苦労するデータ構造ではなさそう。

次、こんな2つのテーブル。

[テーブルA]
[Department]
[Hobby]

[テーブルB]
[subject]
[type]

まったく関連性がなく、2つのテーブルから条件だけでデータを取得しなければいけないような場合。(つか、そもそもこんなテーブル作るなってことなんですが、あくまで例ということで...汗)

とりあえず、このままだとあまりにも関連がなくてジョインできないので、テーブルA.Departmet = テーブルB.Subjectだという想定でSQL書くと...(実は、イメージにリレーションの線を引き忘れたんだよね...爆)

SELECT テーブルA.*, テーブルB.*
  FROM テーブルA 
  LEFT JOIN テーブルB 
    ON テーブルB.Subject = テーブルA.Departmet
   AND テーブルB.Type = 1
 WHERE テーブルA.Hobby = 'SM';

...とか。

これも、DBに任せず自分で頑張って探すとすると...

  1. テーブルAの中からHobby = 'SM'のデータを抽出する。
  2. 抽出したデータ全件について、1件ずつテーブルBから該当するSubjectとTypeのデータを探し出してやる。

ところが、今度はインデックスが使えないのでテーブルBのデータを全部見ていかなければならない。10万件全捜査かい(汗)

そこで、DBエンジン君は「なーにかいい方法はないのかい?」と考える。これがDBの実行計画ってやつ。DBエンジンは、何もいきなり検索処理を実行するわけじゃない。検索に当たりどんな捜査を行うのが一番効率よいかを数パターンの組み合わせの中から考えるのだよ。

  1. テーブルAの中からHobby = 'SM'のデータを抽出する。

これは仕方ないかなぁ(DBエンジン)

  1. 抽出したデータ全件について、1件ずつテーブルBから該当するSubjectとTypeのデータを探し出してやる。

まてよ、テーブルB.Type=1のデータだけとりあえず抽出したらメモリに入れられないかな?(DBエンジン)

いやいや、テーブルA.Departmetも条件だしテーブルAのデータ数は大した量じゃないから、テーブルA.Hobby = 'SM'を条件に先にこいつのリストを作ってテーブルB.Type=1 かつリストの条件に合ったものだけに絞り込んでメモリに入れられないかな?結合は後回しで。(DBエンジン)

限られた短い時間の中でDBエンジン君は人間様に「おっせぇーよ」と言われない様に、それはもう焦りながらも最適だと思われる捜査方法を探しまくるのであった。(続)

...

..

.

...って終わりじゃないない(汗)

将棋のAIみたいなもんだけど、先を読むといっても実際に検索をやってみて遅かったらやり直すとかは当然NGだし、限られた時間短い時間の中での仕事なので、ある程度で解が出なければDBエンジン君、とんでもない実行計画を選んでくれちゃったりもする時がある。でも、それって人間様がかーなーり無理な仕事を押し付けてるってことでっせ。

話がそれそうになったけど、そんな実行計画なのでテーブルA、テーブルBのデータ量の変化によって昨日まで順調だった実行計画も今日からはメロメロになってしまうことだってある。

今では、A4数ページにもわたるような複雑なSQL文は当然ながらとても嫌われる。(項目数が多い時は仕方ないんだと思うけど、それでも文句言われるからなぁ...汗)嫌われる理由は人間様が読みにくいってのが大半だが、DBエンジン君だってそんなSQLから実行計画をたてるのはめっちゃ大変なんすよ(汗)

つまり、DBエンジン君にいい仕事をさせてあげるには...

  • 人間様が想像できる実行計画をDBエンジン君も常に同じように立てられるようなデータ構造設計。
  • テーブルのリレーションには、なるべく意味のない一意のIDのようなものを利用し必ず参照制約(FK)※1を付与してあげる。

...みたいな感じ(-。-)y-゜゜゜

※1 FKを付与することで、参照先の相手のデータがどの辺のページに存在するかという統計情報をお互いに保持するようになる。インデックスだけでは、それぞれの統計情報のみなので、各テーブルの件数変化に弱い。