質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.51%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

Q&A

解決済

10回答

89529閲覧

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

happy_tera

総合スコア159

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

3グッド

52クリップ

投稿2015/10/29 04:22

編集2015/10/29 05:31

株価データを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カラムを削除する方向へもっていきたいのですが、一意の値のカラムを無くした場合デメリットがありましたらご教示頂けますと幸いです。

NobuhiroNakayam, yuokada👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答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
shibacow

総合スコア73

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

happy_tera

2016/01/25 14:06

回答ありがとうございました。 とても参考になりました。
happy_tera

2016/01/25 14:06

回答ありがとうございました。 とても参考になりました。
guest

0

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

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

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

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

投稿2015/10/29 05:11

yoshis22

総合スコア107

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

happy_tera

2015/10/29 05:28

回答ありがとうございます。 GoogleBigqueryの存在をわすれていました。 これいいですよね。 しかし数億行を毎日数万回selectすると思うので、コストが大変なことになりそうです。
yoshis22

2015/11/16 00:36

そうでしたか AmazonRedshiftであればクエリに対するコストはないと思います。
guest

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を導入してシステム、インフラを構築、運用していくコストをなくしていくのも十分に検討する価値はあると思います。

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

投稿2015/11/04 07:14

mryo0826

総合スコア77

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

happy_tera

2015/11/04 12:09

回答ありがとうございます。 >MyISAMの場合簡単にDBのデータが吹っ飛ぶので運用上も宜しくないとの事です。 selectやinsertのほかupdateやdeleteを頻繁に使うとデータが飛ぶのでしょうか。 selectやとnsertだけしか使用しない予定なのですが、それでもデータが飛ぶリスクはあるのでしょうか? 億単位のレコードを常にselectする状況になるので転送量がどのくらいになるか目処がついてませんが、Bigqueryもありかもしれません。 ありがとうございます。
tora470

2015/11/05 00:16

BigQueryのクエリ料金は転送量ではなくスキャン対象のデータ量なはずですよ。 なのでクエリ結果が1件でも対象データが1TBあれば1TB分のクエリ料金発生するので気にはした方がいいと思います。
guest

0

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

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

code+price、およびcreated_at+priceの2つの複合インデックスを作成すると、検索性能の向上が期待できます。
between句による検索でもインデックスを使用することはできますので。

上記2つの複合インデックスを(カラムの順番も上記の通りに)作成したなら、codecreate_atの単一インデックスは不要です。
複合インデックスで代用できるからです。

余談ですが、price BETWEEN x AND yprice >= 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による検索は年月日までしか行なわない」
というのであれば、年月日までの部分インデックスにするという手は有効だと思います。

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

特にないと思います。
ご質問を拝読する限り、レコードの一意性はcodecreated_atによって確保されており、
それ以外に【簡単に】レコードを一意に特定する仕組みを用意する必要はなさそうだと考えるためです。

最後に、MySQLに関しては以下のサイトが非常に役に立ちます。
http://nippondanji.blogspot.jp/

私も、MySQLに関して困ったことがあれば、まず
"漢のコンピュータ 【キーワード】"
で検索しますw

参考までに、紹介させていただきます。

投稿2015/10/29 15:05

編集2015/10/29 15:09
KiyoshiMotoki

総合スコア4791

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

happy_tera

2015/11/04 12:03

回答ありがとうございます。 とてもわかりやすい説明で勉強になります。 複合インデックスを使用したいと思います。 勉強のため単一インデックスをそれぞれに設定した場合、どのくらい速度に差がでるのか試してみたいと思います。 パーティションに関してはMysql全機能バイブルという本に次のように書かれていました。 「チューニングが不十分なのか、現時点では劇的に効果がでるわけではありません。5.1.30現在」 テーブルを分けた場合の速度とパーティションの速度を計測して、前者の方が速いようであれば、テーブルを分けたいと思います。 その他の説明も参考になりました、ありがとうございます。
KiyoshiMotoki

2015/11/04 12:58

happy_tera様 丁寧なコメント、ありがとうございます。 > 「チューニングが不十分なのか、現時点では劇的に効果がでるわけではありません。5.1.30現在」 これは初めて知りました。 ただ、現時点のMySQLの最新バージョンは5.7とだいぶ改版していますので、 もしかしたら改善されているかもしれませんね。 https://dev.mysql.com/downloads/mysql/ いずれにせよ、 happy_tera様が(MySQL以外のストレージを使用する、という選択肢も含めて) どのような設計を採用されるのか、非常に興味があります。 厚かましいお願いで恐縮ですが、何らかの形で共有していただけると幸いです。 システム構築が成功することをお祈り申し上げます。
guest

0

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

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

投稿2015/10/29 05:34

anonymouskawa

総合スコア856

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

anonymouskawa

2015/10/29 23:53

>回答者の方からidカラムは不要ではないかとの回答を頂きました。 >容量削減のためidカラムを削除する方向へもっていきたいのですが、一意の値のカラムを無くした場合デメリットがありましたらご教示頂けますと幸いです。 きちんと設計し、それにあったSQLを組んだシステムであればサロゲートキーはいらないですね。 サロゲートキーのせいでパーティションが切れなかったこともありますし。 メリットってプログラマーがちょっと楽になる、くらいなんじゃないですかね…。
anonymouskawa

2015/10/30 00:05

なんかすごくざっくりした答えになってしまったので、もうちょっときちんと。 ・そのサロゲートキーは検索条件になるのか ・そのサロゲートキーは何かの外部キーになりうるのか この2つがNOならサロゲートキーは使う必要がありません。 よく「複合キーのうちの一つのコード体系が代わったらめんどくさい云々」ありますけれど 元々「キーは不変」という思想なのだからそれを考慮しなかった設計者が悪いだけですね。 とはいえ、本当にめんどくさくなったら自分もサロゲートキーを使うと思います。
happy_tera

2015/11/04 11:49

回答ありがとうございます。 バッチ処理いいですね。参考になります サロゲートキーは検索対象にも外部キーにも今のところなる予定は無いので不要ですね。 しかし後になって検索対象になった場合困るということですよね。 ありがとうございました。
guest

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

katsumiy

総合スコア479

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

TomoyukiEnomoto

2015/11/16 08:13

クラスターインデックスの部分を追記したほうが良いかもです。 MYSQLのプライマリーインデックスは、実データを一緒に持つため、B木のリーフ移動が多発するとI/Oで詰まる事があります。 今回の質問では、code + created_atでもB木のリーフ移動が発生しずらいと思われますので、idの場合と比較してもさほどI/Oで困る事はないと思われますが、外部システムで発番されたIDなど、ランダム性のある一意なIDの場合考慮したほうが良いです。
guest

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

happy_tera

2015/11/04 12:04

回答ありがとうございます。 知らなかったことなので参考になります。 ありがとうございます。
guest

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
miu_ras

総合スコア902

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

happy_tera

2015/11/04 11:55

回答ありがとうございます。 おっしゃる通りTIMESTAMPが一番容量削減できてよさそうです。 ご指摘ありがとうございます。 インデックスのわかりやすい説明もありがとうございます。 インデックスに関しては自分でも色々なパターンで計測してみたいと思います。 >日付・銘柄関係なく100円近辺になったことのある銘柄 これは不要ですので、priceをインデックスする必要はなさそうですね。
guest

0

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

投稿2015/10/29 04:50

maiko0318

総合スコア876

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

happy_tera

2015/10/29 05:09

回答ありがとうございます。 一意の値のカラムは必ず付けるべきだとする本の内容をいまだに引きずっています。 でもたしかにこの場合、idの必要性を感じないので無くても大丈夫な気がします。
miyabi-sun

2015/11/08 09:59

横からですが、それはフレームワークやORMの実装上の都合だと思います。 とりあえずテーブルのIDカラムを見に行けばそれが一意という概念は分かりやすいですからね。 気になるのなら複合主キーにすれば良いのでは? 「○○の銘柄」かつ「××時刻」のレコードは一意であるはずなので。
guest

0

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

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

投稿2015/10/29 04:38

trick

総合スコア366

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

happy_tera

2015/10/29 05:12

回答ありがとうございます。 インデックスの件、参考になります。 パーティションに関してはどのくらいのレコード数を目安に作るべきかが未だによくわかりません。 増えるレコード数一日約100万と確定しているので、予測が簡単なのが幸いです。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問