受付中 1日に100万レコード増える場合のテーブル設計

こんにちは、ソーシャルゲームの会社で、データ分析基盤を作ってます。日常的に数十億件のデータを集計します。
現在は、BigQueryを利用しています。
1日1000万件なら、200日で20億件位になりますので、mysqlはしんどいなと思っています。
最初は、なんとか結果を返せますが、年をおうごとにレスポンスが悪化してくので辛くなると思います。

他の方が上げてらっしゃいますが、大規模な集計に特化した分散システムを使ったほうが、幸せになれます。

BigQueryやAWSが提供しているRedShift,TreasureDataが提供しているシステムなどを使うと良いかも知れません。TD社もhiveベースではなく、Prestoベースの集計エンジンを入れて、集計速度もだいぶ早くなってます。

試しに、mysqlに3年分のデータ(60億件位？)を入れて集計してベンチマークを取ってみたらどうでしょうか？mysqlはある程度までは頑張って集計してくれますが、ある程度を超えると急に集計時間が指数関数的に増えてしまうため、取り扱いが難しいです。

上に上げた、BigQueryにしろ ,RedShiftにしろTDにしろ、データが溜まっても、集計時間が大幅に伸びづらいので、後々楽です。
ちなみにRedshiftはPostgresqlのドライバーが使えるのでphpからアクセするなら乗換コストは少なくて済むと思います。

あと、株価の銘柄って増えづらいので、私だったら、銘柄ごとにテーブル分けちゃいます。多分銘柄でjoinするとか無いと思う(あるのだろうか？)ので、銘柄ごとにすれば、各テーブル1日300件しか増えないので、集計は楽かなと思います。


ああ、よく読んだら、1000万件ではなくて100万件でしたね。それなら1年(200日と換算して)2億件位ですね。それならmysqlでもギリギリなんとかなるかも。2億件のダミーデータを入れてベンチマークを取ってみたら如何でしょう？

※私にはこれほど大きなデータを扱った経験はないので、
  推測と現状の知識、および本回答のために調べた内容だけをもとに回答させていただきます。

selectの際、codeもしくはcreated_atを元にpriceを取得したいのですが、 
インデックスを付けるのはcodeとcreated_atだけではなく、priceにもつけた方が良いのでしょうか？ 
○円以上○円未満というbetweenによってpriceを取得します。 

レコードが数億に対し、codeの種類が3600というのはカーディナリティが低いといえるのか 

パーティショニングした方がよいのかテーブルを年毎に分けた方が良いのか 

パーティションはレコード数がどのくらい増えるごとに作成する必要があるのか

インデックスは部分インデックスにした方がよいのか

一意の値のカラムを無くした場合デメリット

テーブル設計の質問で違う回答をしてしまうのも
如何とは思うのですが
念のため記載します。

データの更新削除がないのであれば
GoogleBigqueryを利用すればいいかと思います。

Googleが１０００億レコードフルスキャンで２０秒と謳っているので
テーブル構成を気を配らなくてもいいと思います。

つまり、単純なテーブル構成でいいと思います。

codeとcreated_atをPKにして、idは除外します。
その上で、code単位でパーティションを切り、更にcreated_atでサブパーティションかなぁと。

例えばなんですけど
1）年単位の表示は夜間バッチで集計させて年単位表示用のテーブルに格納する
2）月単位の表示も夜間バッチで集計させて月単位表示用のテーブルに格納する
3）日時単位の表示のみ本テーブルを利用する
こんな感じにするだけでも多少分散されます。
1と2は別DBにしたっていいわけですしね。

PHPとSQLから離れられるのなら、GT.MというOSSのデータベース(https://sites.google.com/site/gtmstudy/home http://www.fisglobal.com/products-technologyplatforms-gtm)あるいは、SQLが必要なら商用のCache'(http://www.intersystems.com/jp/our-products/cache/cache-overview/)という手があるのですが。1件50バイト程度、年間3億7000万件、5年で18億5000万件、100GB程度の単純なテーブルなら、いろいろ索引を作って300GBぐらいでしょうか。この規模ならパーティションとか必要ないし、データサイズも神経質にならなくても何とかなりそうです。PHPが必須だと、この選択は難しいようですけど。

作られるシステムの要件が良く分からないので一般論的なことになりますが、昔のMySQLは上記に挙げられている株価のログのようなものはMyISAMの方が早いと言われていましたが、MySQL5.1以降はInnoDBの方が性能が逆転し始めているのとMySQL5.6ではデータ圧縮など色々な機能も増え更に速度も向上していますので、MyISAMを選ぶメリットは実際にある程度以上のデータを入れて試験をされて明確なメリットが見つけられない限りまず無いと思います。
またMyISAMの場合簡単にDBのデータが吹っ飛ぶので運用上も宜しくないとの事です。

上記の回答でGoogle Bigqueryを上げられている方もいらっしゃいますが、質問に上げられた内容を見る限り、確かにこちらの方が良いと思います。
月当たりのQuery回数での価格を気にされているようですが、BigqueryはQuery回数ではなくQueryのデータ転送量が課金単位になります。
今時点で検索が1TB 辺り$5、データ容量が月当たり1GB辺り$0.020なので
上記のデータ負荷に耐えられるMySQLのDB構成のコストを考えると、Bigqueryを導入してシステム、インフラを構築、運用していくコストをなくしていくのも十分に検討する価値はあると思います。

https://cloud.google.com/bigquery/pricing?hl=ja

id int(10) AUTO_INCREMENT の行は必要でしょうか？
データ件数が多いということは１バイトでも短くする必要があります。
データがダブるわけではないですよね。（ダブっても問題無いですが）

まず億単位のレコード数になるなら、無駄な情報は極力省き、
容量を少しでも少なくしようと努力すべきです。

idは普通に考えたら要らないでしょう。
必要の無いものは付けるべきではありません。

あとcreated_atはdateじゃないでしょう。dateは日付ですので年月日だけしか保存できません。
DATETIMEかTIMESTAMPの2択になると思いますが、TIMESTAMP で十分でしょう。
DATETIMEだと8バイトですが、TIMESTAMPなら4バイトで済みますので。

もしくは年月日と時分のカラムを分けてもいいと思います。
(むしろ分けたほうがインデックスが小さくて済みいいと思います)

小規模システムの場合、型はテキトーでもいいとおもいますが、
大規模システムの場合には、どの型が何バイトなのかきちんと考えてテーブル設計をすべきです。

MySQLデータ型一覧 (詳細) - Miuran Business Systems

その他、前回から株価が変わっていない・出来高0ならレコードを作製しない
という工夫もあったほうがいいと思います。


それからインデックスに関しては、実際にデータを投入して試すべきだと思います。

「インデックスをつけると速くなる」というイメージを持っている人がいるとは思いますが、
適切につけなければむしろ遅くなることもあります。

本の索引と一緒です。株価情報が書いてある本があったとして、
「銘柄コード＋株価＋取得時刻」の索引があります。
条件に該当する索引を引いて、該当するページ数・行数をメモし
該当部分を参照しに行く…、ということをやっていたら時間がかかりますよね。
だったら、全文流し読み(フルスキャン)したほうが速いくらいです。

あと、どういう検索をするかによりますが、一般論としては価格にインデックスなど要らないと思います。
銘柄と日付を指定したら300本(以下)に限定できるので、それ以上インデックスで絞り込む意味がないからです。


「日付・銘柄関係なく100円近辺になったことのある銘柄」みたいな検索をするなら別ですが、たぶんしないでしょ。

○円以上○円未満というbetweenによってpriceを取得します。 

MySQLは使用したことが無いのですが
これだけのレコード数であれば、パーティショニングは行った方が良いと思います。
パーティショニングの条件をどうするかだと思います。
年月単位か、code単位になるのかな？

インデックスは検索する条件によりますが
code + created_at + price のインデックスで良いと思いますが
code のみ created_at のみで検索するのであれば
それぞれのインデックスも追加したほうが良いと思います。