例えば、1億件の日記データをidによって5000万件ずつ別の
テーブル(DiaryTable1, DiaryTable2)に分けるとします。
※分け方は単純に剰余計算。
Insert時はidから剰余計算で格納先テーブルが分かるのでいいのですが、
全日記データから検索を実行したい場合、
DiaryTable1とDiaryTable2からそれぞれselectを実行し、
アプリ側で結果をマージするという処理しかないと思います。
こうすると、データのソートなどアプリ側の処理がかなり複雑化する気がします。
こういった形で複数にテーブル分割を行っていて、
それら全部からどのように(横断検索とでもいうのですかね?)
selectを行い、マージをしているか、いい手法があれば教えて下さい。
ちなみに、環境はMysql5 + php5(adodb) + apacheですが、
環境に依存した回答でなくて構いません。
もし,物理的に同じ DB に居るなら,テーブル同士を結合してしまう手があります.
あと,テンポラリーテーブルを使っていったん合成したテーブルを一時的に作り,そこでソートを掛ける,という手もあります.
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
SQL の書き方によっては,テンポラリーテーブルの作成にかなり時間が掛かる恐れがあります.
しかし,一億件も行があったら,検索はかなり大変なので,MySQL の機能だけでは処理に時間が掛かってしまい実用的では無いでしょうから,外部ツールを使う方法も考えられます.
mixi でも,初期は検索がほとんどできなくて,検索結果もあまり好ましい表示では無かったですね.最近は改善されたようですけど.
日記の場合,日付という要素がソートで大きいと思うので,垂直分散に置いては,id で分散させず,時系列で分けてはいかがでしょうか.例えば,毎月新しいテーブルを作っていく,など.
これなら,検索時にも検索結果のペイジ単位で,ある程度対象のテーブルが絞れるので,全文検索をしなくても済むかと思います.
私だったら,日記の IN/OUT のデータとは別に,検索専用のサーバを立てて,ここには検索に最適化したデータを置き,定期的に日記DB からデータを取ってくるような仕組みを作ると思います.検索対象が数千件とかになってくると,単純な文字列一致検索より,インデックスを用いた検索が現実的でしょうから,検索ツール(例えば Namazu とか)に適したデータ形式に落とし込んでしまう,という形です.
Namazu を使ったとしても,公式サイトに載ってる最大サイズの例で,検索対象88万ファイル,2GB程度ですから,数千万の行を対象とした検索では使えないかもしれません.
http://www.namazu.org/FAQ.html#index-scale
また,例えば,「りんご」を「リンゴ」でも検索できるようにしようと思ったら,MySQL の機能だけでは実現できないですよね.kakasi などの外部ツールを頼ることになります.(すべての書き込みに対し,kakasi でひらがな化してインデックス作成.検索ワードも kakasi でひらがな化する,など)
多少は,外部ツールを頼るのも必要かもしれない,と思いました.
PS.
これだけの規模のシステムは,個人的に興味があります.ぜひ見てみたいです(^^;
ありがとうございます。
なるほど、外部ツールとを商用ツールと勘違いしておりました。すみません。
idでの分割ではなく、時系列ですか。
しかし時系列だと古くなったテーブルが
だんだん参照されなくなっていく可能性はありませんかね??
検索部分についてですが、多分私もgoodvnさんと思想が一緒だと思います。
Senna+Mecabによる全文検索専用のマスタ・スレーブ構造グループ別途用意。
日記データを更新で、Sennaマスタも更新されるようにしようと思いましたが、
Fulltextのインデックス更新のコストが高いので、キュー処理することに。
別途専用のキューデータベースを立て、Senna用キュー管理テーブルを用意し、(ここにはデータを一意に示すidしか入っていない)
PHPで作成したキュー更新デーモンが定期的にキュー管理テーブルに入ってるIDから実態データを引っ張り出し、
全文検索用にデータを加工(絵文字除去やごちゃごちゃしたことなど)を行ってから、
適宜Senna専用データベースを更新するという実装にしました。
全文検索時はSenna専用データベースから"match against"で一回検索→
取得した結果のIDを元に、分割されたテーブルの所在を示すマップから格納先DBを取得→
そのDBに対してwhere in (id,id,id)でみたいな感じで最終的に必要な日記データを取得。
みたいな感じです。idは主キーなので検索速度は件数が膨大でも早いです。
このように、全文検索時はそれなりに手間かからずいいのですが、
例えば日記の状態を示すフラグ(あいまいですみません)が1であるデータを
引っ張る必要がある時、分割されたテーブルすべてにSQLを発行し、
結果を取得。それらの結果からある値でソートを行う場合などは
アプリ側で結果配列を廻しまくる処理が入ってしまっているため、
ちょぃ自分のDB側設計ミスったかと思っております。。
もうちょっと頭冷やして考えてみます。
>PS.
>これだけの規模のシステムは,個人的に興味があります.ぜひ見てみたいです(^^;
本当ですか、是非一度お茶でも(^^)
ご懸念の通り、TABLEを分割している影響がアプリケーション側に及ぶのは好ましいことではありません。
TABLEが2つに分かれているだけならいいのですが、状況に応じて3つ、4つ‥‥に分かれる可能性があるなら、DBMSの負荷は低下するかもしれませんが、その分、アプリケーション側の負荷が増します。
検索速度を向上させたいなどの負荷分散が目的であるなら、MySQL Cluster を利用するのが妥当です。
これであれば、アプリケーション側はTABLEが分散しているかどうかを認識する必要もなく、ふつうにMySQLに接続するだけで(じつはNDB Cluster Engine に接続している)済みます。
ご回答ありがとうございます。
MySQL Clusterですね、マスタ・スレーブ構造より、
管理が難しそうだと思い、検討からはずしていました。調査してみます。
しかし、データが巨大な故、ひとつのテーブルを二つやみっつに分割するのであり、
それらを別ホストに置くことにより負荷を下げているのですが、
Clusterだと全データノードで分割されたテーブルを持つ必要があると思われます。
そうなると結局テーブル1とテーブル2にselectかけて結果を受け取って、マージしてっていう処理は
同じように発生しますよね??
分割したDBとは別に、統合したDBを用意するというのはどうでしょうか?
ユーザが日記データを更新したときには分割したDBが更新されるので、定期的にバッチ処理で分割したDBと統合したDBの同期をとります。全文検索のときは統合したDBから検索します。
同期がとれるまでは全文検索は最新の状態で検索は出来ませんが、それは仕方ないということで。
ご回答ありがとうございます。
ご提案通り、全文検索に関しては統合されたDBを用意してあります。
トランザクションが無くてもいいなら、 MERGE テーブル使う。
MySQL :: MySQL 4.1 リファレンスマニュアル :: 7.2 MERGE テーブル
無ければだめなら、UNION 使う。
MySQL :: MySQL 4.1 リファレンスマニュアル :: 6.4.1.2 UNION 構文
それ以外だとアプリの方で何とかするしかないねぇ。
ご回答ありがとうございます。
mergeテーブルはInnoDBでは使用できなそうです・・。
UNIONに関しては、テーブル分割(DBもホストも物理的に別)なので使用できなそうです・・。
mergeテーブルはInnoDBでは使用できなそうです・・。
UNIONに関しては、テーブル分割(DBもホストも物理的に別)なので使用できなそうです・・。
そうでしょうね。
パーティショニング手法によって対処するケースではレプリケーションも無意味です。
(Master/Slave型, MySQL Cluster とも)
全日記データから検索を実行したい場合
検索用のインデックスと格納されたDBを指し示すテーブルを別途用意するぐらいですかねぇ。
リアルタイム性を多少なりとも犠牲にすれば、の話になりますが。
どちらにせよアプリでの対応は(ラッパー関数・クラスを用意するにせよ)対応は必要かと。
ご回答ありがとうございます。
情報不足で申し訳ございません。
物理的に違うDB(かつ違うサーバー)に分割したテーブルが置いてあります。
よって結合ができないのです。※アプリ側もテーブル分割を見越してjoinはほぼしない設計にはしてあります。
データの検索が頻繁にあるのでテンポラリテーブルをアクセス毎に生成するのは厳しそうです。
また、過大表現で失礼しました。
現在のデータ件数は1千万件ちょっとです。が、早い時期に5千万くらいに達してしまいそうなのです。
先々を見越してアプリ設計をしないとやばいことになりそうです。
とりあえずは5000万件くらいまで達したら2500万件ずつ分割しようと思っております。
それを繰り返していくと、日記データ全体から検索をかけた時にアクセス毎に2~4回ほどのselectを実行することに
なりますが、主キーでの絞り込みを心がけているので速度はそれなりに出るかなと思っています。
※ちなみにInnoDBです。マスタ・スレーブ構造です。
なるべくは外部ツール?などは使わずMysqlの機能だけでがんばって行きたいと思うところです。
よくmixiとかモバゲとかの巨大システムでのDB分割手法がよく紹介されていますが、
それらを横断的に検索する手法は書かれていないなぁと思うのは私だけでしょうか。