巨大になってしまう家庭内株価データベースを少し最適化する

テキトーに株価のデータをdata-get から買ってmysql にインポートしたはいいけど、
ギガバイトオーダーのデータ量になってしまい1個の銘柄を検索するのに8分かかるクレイジーなテーブルになってしまった。8分も検索にかかるとやる気がなくなってしまい、艦これをやってしまう。

こ れ は い か ん 。

恐ろしい無計画。

なんで遅いかよくわからないのだが、myisamchk --sort-index --sort-record を使うと速くなるかもっていう気がした。 たぶんあっているだろうが、 テーブルのエンジンがInnodb だったので、できない。 

じゃあ、innodb でクエリを高速化する方法が無いかというと、 無いわけがない。
もしなかったら世界中で mysql が普及するわけがない。

絶対あるはずだがよくわからないなあと思って help alter table とか打ってみると んむむパーティションというモノがあるではないか。

https://dev.mysql.com/doc/refman/5.6/ja/partitioning-limitations.html まずは、今腐るほど遅いテーブルの構造を引き継いだテーブルで実験してみることに。

create table tmp like tbl_dg_ohlc;

要は、日付順に並んでるデータをコード毎にテーブルを分ければアクセスが速くなる。
山積みの書類を積んでおくと、関係のあるヤツどれって言われると（目録はあるにせよ）取りにいかないといけないが。
引き出しにあらかじめ分けて入れておけばサッと取り出せるおばあちゃんの知恵みたいな。

alter table tmp PARTITION BY RANGE COLUMNS(s) (
PARTITION p1500 VALUES LESS THAN('1500'),
PARTITION p2000 VALUES LESS THAN('2000'),
PARTITION p2500 VALUES LESS THAN('2500'),
PARTITION p3000 VALUES LESS THAN('3000'),
PARTITION p3500 VALUES LESS THAN('3500'),
PARTITION p4000 VALUES LESS THAN('4000'),
PARTITION p4500 VALUES LESS THAN('4500'),
PARTITION p5000 VALUES LESS THAN('5000'),
PARTITION p5500 VALUES LESS THAN('5500'),
PARTITION p6000 VALUES LESS THAN('6000'),
PARTITION p6500 VALUES LESS THAN('6500'),
PARTITION p7000 VALUES LESS THAN('7000'),
PARTITION p7500 VALUES LESS THAN('7500'),
PARTITION p8000 VALUES LESS THAN('8000'),
PARTITION p8500 VALUES LESS THAN('8500'),
PARTITION p9000 VALUES LESS THAN('9000'),
PARTITION p9500 VALUES LESS THAN('9500'),
PARTITION p9999 VALUES LESS THAN('9999'),
PARTITION pMAX VALUES LESS THAN(MAXVALUE)
);

むーん。パワー。

insert into tmp select * from tbl_dg_ohlc;

今なら言える。
データ一日1万個アホは万里を超える。

待ち。

ババーーーン。
風呂入ってこよ

select * from tmp where s="9984";
...
| 20160331 | 9984 | t1 |   5454 |   5466 |   5362 |   5366 |   6804900 |
+----------+------+----+--------+--------+--------+--------+-----------+
5332 rows in set (50.46 sec)

もとは8分以上かかっていたが、50秒に短縮され 8倍以上は速くなった。 けどやっぱり十分遅いっちゃあ遅いな、RPIなんで仕方ない部分はあるけどパーティションギチギチに切って 日付とセキュリティIDを入れ替えたり、セキュリティIDやマーケットIDをもっと小さなサイズの数値にしたりという事は可能かもしれない。 でもあんまり複雑にすると面倒なのでどうしたもんか

仮にこれだけしか実力を発揮できないとすると、仮に銘柄が3000くらいあったとして日々それを調査するのにSelect文3000個発行すると、3000分時間がかかって全然実用にならない。パーティションではたぶんこれ以上の高速化はたいして効果ないように思う。


根本的に何か間違っているとしか思えない。

で、いろいろやってみましたよ

alter table tmp modify d char(8) after m;

⇒効果なし。

alter table tmp drop primary key;
alter table tmp add primary key(s,d,m);
select * from tmp where s="9984"
略）
| 9984 | t1 | 20160331 |   5454 |   5466 |   5362 |   5366 |   6804900 |
+------+----+----------+--------+--------+--------+--------+-----------+
5332 rows in set (0.39 sec)

正解はこれだったか。 Raspberry PI の株価サーバは十分実用に耐えると思います。


結論 複合プライマリキーの順番には要注意！！！！