Q&A
回答ありがとうございます。
GoogleBigqueryの存在をわすれていました。
これいいですよね。
しかし数億行を毎日数万回selectすると思うので、コストが大変なことになりそうです。
株価データをmysqlにinsertするシステムを組んでおります。
全銘柄の株価を1分おきに取得します。
銘柄数は3600、株式市場の時間は1日300分
3600×300=1,080,000レコードとなります。
カラムは以下の通りです。
id int(10) AUTO_INCREMENT
code smalint(5)//銘柄コード
price double//株価
dekidaka int(10)//出来高
created_at date//取得時刻
selectする際の速度を最重視するため、
ストレージエンジンはMyISAMを採用し、ORMは使用しない予定です。
普段テーブル設計をする際はupdated_atカラムを含ませているのですが、
レコード数が膨大になって容量を食う上、updateも行わないため除いています。
・insertは遅くても問題ない
・updateは基本的に行わない(過去の株価は不変)
selectの際、codeもしくはcreated_atを元にpriceを取得したいのですが、
インデックスを付けるのはcodeとcreated_atだけではなく、priceにもつけた方が良いのでしょうか？
○円以上○円未満というbetweenによってpriceを取得します。
その他ご教示頂きたいのは以下の通りです。
・レコードが数億に対し、codeの種類が3600というのはカーディナリティが低いといえるのか
・パーティショニングした方がよいのかテーブルを年毎に分けた方が良いのか
・パーティションはレコード数がどのくらい増えるごとに作成する必要があるのか
・インデックスは部分インデックスにした方がよいのか
宜しくお願いいたします。
※追記
回答者の方からidカラムは不要ではないかとの回答を頂きました。
容量削減のためidカラムを削除する方向へもっていきたいのですが、一意の値のカラムを無くした場合デメリットがありましたらご教示頂けますと幸いです。
回答10件
0
ベストアンサー
こんにちは、ソーシャルゲームの会社で、データ分析基盤を作ってます。日常的に数十億件のデータを集計します。
現在は、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億件のダミーデータを入れてベンチマークを取ってみたら如何でしょう？
投稿2015/11/04 12:37
編集2015/11/05 00:25
総合スコア73
0
作られるシステムの要件が良く分からないので一般論的なことになりますが、昔のMySQLは上記に挙げられている株価のログのようなものはMyISAMの方が早いと言われていましたが、MySQL5.1以降はInnoDBの方が性能が逆転し始めているのとMySQL5.6ではデータ圧縮など色々な機能も増え更に速度も向上していますので、MyISAMを選ぶメリットは実際にある程度以上のデータを入れて試験をされて明確なメリットが見つけられない限りまず無いと思います。
またMyISAMの場合簡単にDBのデータが吹っ飛ぶので運用上も宜しくないとの事です。
上記の回答でGoogle Bigqueryを上げられている方もいらっしゃいますが、質問に上げられた内容を見る限り、確かにこちらの方が良いと思います。
月当たりのQuery回数での価格を気にされているようですが、BigqueryはQuery回数ではなくQueryのデータ転送量が課金単位になります。
今時点で検索が1TB 辺り$5、データ容量が月当たり1GB辺り$0.020なので
上記のデータ負荷に耐えられるMySQLのDB構成のコストを考えると、Bigqueryを導入してシステム、インフラを構築、運用していくコストをなくしていくのも十分に検討する価値はあると思います。
投稿2015/11/04 07:14
総合スコア77
回答ありがとうございます。
>MyISAMの場合簡単にDBのデータが吹っ飛ぶので運用上も宜しくないとの事です。
selectやinsertのほかupdateやdeleteを頻繁に使うとデータが飛ぶのでしょうか。
selectやとnsertだけしか使用しない予定なのですが、それでもデータが飛ぶリスクはあるのでしょうか？
億単位のレコードを常にselectする状況になるので転送量がどのくらいになるか目処がついてませんが、Bigqueryもありかもしれません。
ありがとうございます。
BigQueryのクエリ料金は転送量ではなくスキャン対象のデータ量なはずですよ。
なのでクエリ結果が1件でも対象データが1TBあれば1TB分のクエリ料金発生するので気にはした方がいいと思います。
0
※私にはこれほど大きなデータを扱った経験はないので、
推測と現状の知識、および本回答のために調べた内容だけをもとに回答させていただきます。
selectの際、codeもしくはcreated_atを元にpriceを取得したいのですが、
インデックスを付けるのはcodeとcreated_atだけではなく、priceにもつけた方が良いのでしょうか？
○円以上○円未満というbetweenによってpriceを取得します。
code+price、およびcreated_at+priceの2つの複合インデックスを作成すると、検索性能の向上が期待できます。
between句による検索でもインデックスを使用することはできますので。
上記2つの複合インデックスを（カラムの順番も上記の通りに）作成したなら、codeとcreate_atの単一インデックスは不要です。
複合インデックスで代用できるからです。
余談ですが、price BETWEEN x AND yはprice >= x AND price <= yと同義です。
以上【未満】ではないのでご注意をw
レコードが数億に対し、codeの種類が3600というのはカーディナリティが低いといえるのか
十分に低いと言えると思います。
おそらく、パーティショニングには適しているのではないでしょうか？
銘柄コードが変更されることも滅多にないと思いますし。
パーティションに関しては、以下が参考になると思います。
http://nippondanji.blogspot.jp/2009/04/1.html
http://nippondanji.blogspot.jp/2009/04/http-session.html
パーティショニングした方がよいのかテーブルを年毎に分けた方が良いのか
一定期間経過したデータを削除したりしないのであれば、時間によるテーブル分割が必須になると思います。
MySQLには作成できるパーティションの数に制限があるようですので、
際限なく増えるデータをパーティショニングによって処理するのは不可能だと思うためです。
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html
また、インデックスの行数にも制限があるようです。
http://nippondanji.blogspot.jp/2009/05/mysql.html
パーティションはレコード数がどのくらい増えるごとに作成する必要があるのか
私にはわかりません(^^;
標本となるデータがあるのであれば、事前に性能試験を行なって決めるのが良いと思います。
インデックスは部分インデックスにした方がよいのか
検索性能とデータサイズのトレードオフになりますので、これも一概には言えないと思います。
標本となるデータがあるのであれば（以下同文）
ただ、例えば
「created_atによる検索は年月日までしか行なわない」
というのであれば、年月日までの部分インデックスにするという手は有効だと思います。
一意の値のカラムを無くした場合デメリット
特にないと思います。
ご質問を拝読する限り、レコードの一意性はcodeとcreated_atによって確保されており、
それ以外に【簡単に】レコードを一意に特定する仕組みを用意する必要はなさそうだと考えるためです。
最後に、MySQLに関しては以下のサイトが非常に役に立ちます。
http://nippondanji.blogspot.jp/
私も、MySQLに関して困ったことがあれば、まず
"漢のコンピュータ 【キーワード】"
で検索しますw
参考までに、紹介させていただきます。
投稿2015/10/29 15:05
編集2015/10/29 15:09
総合スコア4791
回答ありがとうございます。
とてもわかりやすい説明で勉強になります。
複合インデックスを使用したいと思います。
勉強のため単一インデックスをそれぞれに設定した場合、どのくらい速度に差がでるのか試してみたいと思います。
パーティションに関してはMysql全機能バイブルという本に次のように書かれていました。
「チューニングが不十分なのか、現時点では劇的に効果がでるわけではありません。5.1.30現在」
テーブルを分けた場合の速度とパーティションの速度を計測して、前者の方が速いようであれば、テーブルを分けたいと思います。
その他の説明も参考になりました、ありがとうございます。
happy_tera様
丁寧なコメント、ありがとうございます。
> 「チューニングが不十分なのか、現時点では劇的に効果がでるわけではありません。5.1.30現在」
これは初めて知りました。
ただ、現時点のMySQLの最新バージョンは5.7とだいぶ改版していますので、
もしかしたら改善されているかもしれませんね。
https://dev.mysql.com/downloads/mysql/
いずれにせよ、
happy_tera様が（MySQL以外のストレージを使用する、という選択肢も含めて）
どのような設計を採用されるのか、非常に興味があります。
厚かましいお願いで恐縮ですが、何らかの形で共有していただけると幸いです。
システム構築が成功することをお祈り申し上げます。
0
codeとcreated_atをPKにして、idは除外します。
その上で、code単位でパーティションを切り、更にcreated_atでサブパーティションかなぁと。
例えばなんですけど
1）年単位の表示は夜間バッチで集計させて年単位表示用のテーブルに格納する
2）月単位の表示も夜間バッチで集計させて月単位表示用のテーブルに格納する
3）日時単位の表示のみ本テーブルを利用する
こんな感じにするだけでも多少分散されます。
1と2は別DBにしたっていいわけですしね。
投稿2015/10/29 05:34
総合スコア856
>回答者の方からidカラムは不要ではないかとの回答を頂きました。
>容量削減のためidカラムを削除する方向へもっていきたいのですが、一意の値のカラムを無くした場合デメリットがありましたらご教示頂けますと幸いです。
きちんと設計し、それにあったSQLを組んだシステムであればサロゲートキーはいらないですね。
サロゲートキーのせいでパーティションが切れなかったこともありますし。
メリットってプログラマーがちょっと楽になる、くらいなんじゃないですかね…。
なんかすごくざっくりした答えになってしまったので、もうちょっときちんと。
・そのサロゲートキーは検索条件になるのか
・そのサロゲートキーは何かの外部キーになりうるのか
この2つがNOならサロゲートキーは使う必要がありません。
よく「複合キーのうちの一つのコード体系が代わったらめんどくさい云々」ありますけれど
元々「キーは不変」という思想なのだからそれを考慮しなかった設計者が悪いだけですね。
とはいえ、本当にめんどくさくなったら自分もサロゲートキーを使うと思います。
回答ありがとうございます。
バッチ処理いいですね。参考になります
サロゲートキーは検索対象にも外部キーにも今のところなる予定は無いので不要ですね。
しかし後になって検索対象になった場合困るということですよね。
ありがとうございました。
0
遅くなりましたが、間違った回答もありますので、整理、訂正させてもらいます。
まず、MyISAM を使うのはやめたほうがいいでしょう。MyISAMでは、INSERT, UPDATE中はSELECT できませんので、一分間に一回、反応が悪くなります。
MySQLの最近の性能向上やプロセッサの性能向上により、MyISAMとInnoDBの差はあまりありません。
むしろ、メモリを多く有効に使えるInnoDBのほうが速くなると思います。
次に、パーティションは必要ありません。パーティションの良いところは月単位でのDELETEをDROPで行えることと、全体スキャンを部分スキャンで置き換えられるということだけです。
今回の用途では月単位でのDELETEなどを行わず、全体スキャンも発生することはないので、パーティションは無意味且つメモリを余計に使うため逆効果です。
そして、IDはいらないでしょう。IDをなくして、code + created_at をプライマリーキーにすべきです。
code + created_at をプライマリーキーにした場合、price をプライマリーキーに入れることは無意味です。IDがどうしても必要である場合は、code + created_at にインデックスを貼るよりはcode + created_at + price にインデックスを貼ったほうが若干速くはなりますが、微々たるものなので、容量のことを考えると、code + created_at だけでいいでしょう。この辺りはクラスタードインデックスとカバリングインデックスという考え方を勉強してください。
数年後に、テーブルが巨大になって、遅くなることを考えると、別の人が回答していたように、銘柄ごとにテーブルを作って、将来的には複数のサーバーに水平分散できるようにしたほうがいいかもしれません。
ただし、初期の段階で、一台で3600個のテーブルを使い、もし、数百人からアクセスされたりするような場合、テーブル一個の場合より、それなりに余計にメモリを使用します。
投稿2015/11/06 00:30
総合スコア479
クラスターインデックスの部分を追記したほうが良いかもです。
MYSQLのプライマリーインデックスは、実データを一緒に持つため、B木のリーフ移動が多発するとI/Oで詰まる事があります。
今回の質問では、code + created_atでもB木のリーフ移動が発生しずらいと思われますので、idの場合と比較してもさほどI/Oで困る事はないと思われますが、外部システムで発番されたIDなど、ランダム性のある一意なIDの場合考慮したほうが良いです。
0
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が必須だと、この選択は難しいようですけど。
投稿2015/10/30 05:32
総合スコア16
0
まず億単位のレコード数になるなら、無駄な情報は極力省き、
容量を少しでも少なくしようと努力すべきです。
idは普通に考えたら要らないでしょう。
必要の無いものは付けるべきではありません。
あとcreated_atはdateじゃないでしょう。dateは日付ですので年月日だけしか保存できません。
DATETIMEかTIMESTAMPの2択になると思いますが、TIMESTAMP で十分でしょう。
DATETIMEだと8バイトですが、TIMESTAMPなら4バイトで済みますので。
もしくは年月日と時分のカラムを分けてもいいと思います。
(むしろ分けたほうがインデックスが小さくて済みいいと思います)
小規模システムの場合、型はテキトーでもいいとおもいますが、
大規模システムの場合には、どの型が何バイトなのかきちんと考えてテーブル設計をすべきです。
MySQLデータ型一覧 (詳細) - Miuran Business Systems
その他、前回から株価が変わっていない・出来高0ならレコードを作製しない
という工夫もあったほうがいいと思います。
それからインデックスに関しては、実際にデータを投入して試すべきだと思います。
「インデックスをつけると速くなる」というイメージを持っている人がいるとは思いますが、
適切につけなければむしろ遅くなることもあります。
本の索引と一緒です。株価情報が書いてある本があったとして、
「銘柄コード＋株価＋取得時刻」の索引があります。
条件に該当する索引を引いて、該当するページ数・行数をメモし
該当部分を参照しに行く…、ということをやっていたら時間がかかりますよね。
だったら、全文流し読み(フルスキャン)したほうが速いくらいです。
あと、どういう検索をするかによりますが、一般論としては価格にインデックスなど要らないと思います。
銘柄と日付を指定したら300本(以下)に限定できるので、それ以上インデックスで絞り込む意味がないからです。
「日付・銘柄関係なく100円近辺になったことのある銘柄」みたいな検索をするなら別ですが、たぶんしないでしょ。
○円以上○円未満というbetweenによってpriceを取得します。
とのことなので本当はするのかもしれませんが。
投稿2015/10/29 07:24
編集2015/10/29 07:26
総合スコア902
回答ありがとうございます。
おっしゃる通りTIMESTAMPが一番容量削減できてよさそうです。
ご指摘ありがとうございます。
インデックスのわかりやすい説明もありがとうございます。
インデックスに関しては自分でも色々なパターンで計測してみたいと思います。
＞日付・銘柄関係なく100円近辺になったことのある銘柄
これは不要ですので、priceをインデックスする必要はなさそうですね。
0
id int(10) AUTO_INCREMENT の行は必要でしょうか？
データ件数が多いということは１バイトでも短くする必要があります。
データがダブるわけではないですよね。（ダブっても問題無いですが）
投稿2015/10/29 04:50
総合スコア878
回答ありがとうございます。
一意の値のカラムは必ず付けるべきだとする本の内容をいまだに引きずっています。
でもたしかにこの場合、idの必要性を感じないので無くても大丈夫な気がします。
横からですが、それはフレームワークやORMの実装上の都合だと思います。
とりあえずテーブルのIDカラムを見に行けばそれが一意という概念は分かりやすいですからね。
気になるのなら複合主キーにすれば良いのでは？
「○○の銘柄」かつ「××時刻」のレコードは一意であるはずなので。
0
MySQLは使用したことが無いのですが
これだけのレコード数であれば、パーティショニングは行った方が良いと思います。
パーティショニングの条件をどうするかだと思います。
年月単位か、code単位になるのかな？
インデックスは検索する条件によりますが
code + created_at + price のインデックスで良いと思いますが
code のみ created_at のみで検索するのであれば
それぞれのインデックスも追加したほうが良いと思います。
投稿2015/10/29 04:38
総合スコア366
回答ありがとうございます。
インデックスの件、参考になります。
パーティションに関してはどのくらいのレコード数を目安に作るべきかが未だによくわかりません。
増えるレコード数一日約100万と確定しているので、予測が簡単なのが幸いです。
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/01/25 14:06
2016/01/25 14:06