1000万件のデータと闘うには

仕事で1000万件超のデータを扱っているという知り合いから，データベースについていろいろと聞いてみた．

データベースを速くするコツは?

DBサーバとアプリケーションサーバを分けると24時間かかっていたものが2時間になった
おそらく，1つのタスクに集中したほうがキャッシュのヒット率があがるから？

Materialized View

いくら1000万件の生データをもっていても，それら直接扱うことはやっぱり稀らしい．普段は集計済みのデータを扱うらしい．
集計内容と生データは整合性がないといけない．でもViewを使うのは遅い，というときには
Materialized View というものがあるらしい．簡単にいうとクエリの結果を特定のテーブルにとっておいて，たまに更新をかける，というもの．

Oracle には create materialized view があるらしい．

http://www.akadia.com/services/ora_materialized_views.html

 create materialized view mv_bigtab
   build immediate
   refresh on commit
   enable query rewrite
 as
 select owner, count(*)
   from bigtab
  group by owner

MySQL では，単に Insert into .. を使ってテーブルを作る，のをストアドプロシージャに登録しておくことで実現する

http://www.shinguz.ch/MySQL/mysql_mv.html

サブクエリ

Select文をネスト（サブクエリ化）しまくることもある．そのほうが何度もクエリを発行するより速いらしい．

サブクエリの例を挙げてみる．

http://www.techscore.com/tech/sql/07_01.html

  SELECT 受注番号 FROM 受注表
         WHERE 商品コード IN
         (SELECT 商品コード FROM 商品表
          WHERE 単価 = 400);

Java脳の人がこのクエリを実現しようとすると，

Set codes=(SELECT 商品コード FROM 商品表
                    WHERE 単価 = 400);
for (Integer bango in (SELECT 受注番号 FROM 受注表)) {
   if (codes.contains(bango)) {
         ...
   }
}

なんていうコードを書きかねない．Java脳，というか手続き脳の人は
「WHERE判定を行うたびに中のサブクエリを毎回実行してんじゃないのか」，つまり

for (Integer bango in (SELECT 受注番号 FROM 受注表)) {
   Set codes=(SELECT 商品コード FROM 商品表
                       WHERE 単価 = 400);
   if (codes.contains(bango)) {
         ...
   }
}

なんじゃないかと思い込んでいる．さすがにSQLはその辺は面倒みてくれているらしい．

Java脳が書いたクエリ＋プログラムを1個のクエリに自動統合する仕組みがあるといいかもしれない．

他にも Order by，Group By ，Union，Distinct などの機能はSQLに任せたほうがだんぜん速い．Group By をJavaのハッシュや集合で実現するような真似はしないこと．

Union の賢い使い方

  a  b  c
  T  1  X
  T  2  Z

と

  a  b  d
  T  1  Y
  S  1  P

をUnionすると

  a  b  c  d
  T  1  X  Y
  T  2  Z
  S  1     P

となるらしい

テーブルのスキーマが変わるときは

テーブルをDropしてCREATEするか，Insert intoするか，EXPORT&IMPORTする．ALTER TABLE は使っても結局時間がかかるらしい（1000万件に新しい列を追加するのはすごいコスト）．

もちろん，その間はDB更新をとめる必要がある．EXPORT中にDBを更新
したい場合は，....そんなのは想定したことないらしい．差分管理でがんばれ？

OracleのBitmapインデックス

http://www.intersystems.co.jp/support/cache_50/csp/rsql/rsql_createindex.html

たぶん，全文検索における「逆インデックス」のことだと思う．

というデータがあったら犬= 10100 猫=01011 という値をもたせておく．

正規形とビジネスモデル

だいたいのテーブルはボイスコッド正規形くらいまで行うらしい．

第3正規形まではツールが自動的に行うが，n>=4 の場合，ビジネスモデルを知らないと，それが第n正規形かどうかの判定すらできない．

例えば，時間帯がかぶる指定席券はとれるが，同じ時限の授業はとれないとか，
生徒と先生と科目名というテーブルがあった場合に「フェルマー先生の物理」や「アインシュタイン先生の数学」というデータがありうるが，実際にはそんな授業はない．「存在しうる組み合わせを示すテーブル」まで用意すると第5正規形になる．

集計による高速化

MATERIALIZED VIEWの応用バージョン？ 1000万件の収支データ（細目）があったときに，それらをまともに検索したくないときは，1年以上前の決算について「その他」品目で，月ごとの合計値のみをもたせ，1年以上前の詳細は別テーブルにもたせて普段はさわらない，という構成でだいぶ速くなった．