HEAP または MEMORYについて

質問

MySQLでのキャッシュを使用したストレージエンジン、HEAP またはMEMORYについて質問です。

あるゲームのスコアランキングを表示したいのですが、
ユーザーごとに最新のデータでのランキングとしたいので、処理が重くなりそうです。
仮にrankingテーブルとして、
sequenceID:シーケンスID(主キー) int auto increment
score:得点
user:ユーザー

select `user`,`score` from `ranking` where `sequenceID` in (
select `sequenceID`,`score` from `ranking` group by `user` order by max( `sequenceID`) ) order by `score` desc

クエリはこんな感じで大丈夫でしょうか。。。。
このクエリ自体はためしていませんが、
とりあえず、重そうですので、HEAP　または　MEMORY のようなストレージエンジンを使いたいと考えています。

この場合の注意点はどういうところでしょうか。
再起動で消える、ということは承知していますので、メモリ上限にならないようにする工夫などの体験談というか、実際にMEMORYで運用されたことがある方、ぜひご助言ねがいます。

回答No.1

chukenkenkou · ベストアンサー

MEMORY等を使うこと以前に、テーブル設計やインデクス設計をしっかり行った方が良いのでは？
質問中のSQLは、文法的に間違っているし、性能上も好ましくありません。
母体データ件数、ユーザ毎の平均のデータ件数は、ある程度、見積もれているのでしょうか？
また、ユーザ毎のスコアの履歴を、残す必要があるのでしょうか？そういう点でもメモリ常駐する意味があるのか疑問です。

補足

ご助言ありがとうございます。
SQL文の文法的誤りは、
副問い合わせの
「select `sequenceID`,`score` from ...」の部分でしたでしょうか。。。
ここは「select `sequenceID` from ...」でOKでしょうか。

また、質問の趣旨とはずれますが、
こういう場合のSQL文の例など、教えていただけると幸いです。

>母体データ件数、ユーザ毎の平均のデータ件数は、ある程度、見積もれているのでしょうか？

こちらですが、まだオープン前のサイトなので、目処がたっていません。
ユーザごとのスコアの履歴は残す必要があります。

MEMORYを使用したいと考えたのは、単純に高速になるかな。。という浅はかな考えからでした。
逆に、MEMORYを使用するのに適したケースというのは、どういった場合なのでしょうか？

回答No.3

chukenkenkou

MySQLのバージョンは何でしょうか？

>調べたところ副問い合わせでは、インデックスは使えないようなことを見かけ

副問い合わせでは、「まったくインデクスを使ってくれない」ということはありません。ただ、少し試してみましたが、確かに期待したようには使ってくれない感じで、もっと試行錯誤が必要な感じです。

履歴を「取りあえず、すべて保存」ということからも、メモリ上に置くという考えはすぐに破綻してしまうかも知れません。やはり最新値と履歴で、テーブルを分けた方がいいと思います。

>普通にスコアの降順で集計したほうがいいのでしょうか？

MySQLは、「昇順と降順を混在させると、インデクスを利用したソート抑止をできない」という制限事項があるので、留意して置いてください。
例えば、
create index t1ix1 on t1(c1 desc,c2 asc)
といったインデクスを定義できるのですが、実際には(c1 asc,c2 asc)のインデクスが作成されます。
「order by c1 asc,c2 asc」または「order by c1 desc,c2 desc」では、このインデクスを利用できますが、「order by c1 desc,c2 asc」ではソート抑止してくれません。
これについては、マニュアルに説明があり、「show create table」でのDDL生成結果からも確認できます。

>ヒープとは無関係になってきておりますが、ご教授いただけると幸いです。

とりあえず、メモリに乗せるかどうかは棚上げにし、テーブル設計、インデクス設計、SQLの書き方に絞って再質問してはいかがでしょうか？
質問日時が古くなり、質問のタイトルからも他の人は回答に参加しにくいでしょう。

この質問のURLを貼り付け、再質問することをお勧めします。

お礼

アドバイス、ありがとうございます。
質問の仕方も含め、いろいろと参考になりました。

あせって聞きかじったことで対処しようとしていましたが、
ご指摘いただいた「テーブル設計、インデクス設計、SQLの書き方」これらの勉強をもっとしなければ、、、と痛感しました。

ちょっとまとめて、改めて別タイトルで再質問してみます。
ありがとうございました。

回答No.2

chukenkenkou

MySQLで実機確認する環境はないのでしょうか？

>SQL文の文法的誤りは、副問い合わせの
>「select `sequenceID`,`score` from ...」の部分でしたでしょうか。。。
>ここは「select `sequenceID` from ...」でOKでしょうか。

何がしたいのでしょうか？
その条件部分だけでなく、group by、max関数等の使い方が正しくありません。

>こういう場合のSQL文の例など、教えていただけると幸いです

「こういう場合」とは、具体的にどういう場合ですか？

たぶんやりたいのは、次のようなSQLなのでしょう。。。

select *
from ranking as R
where `score`=(select max(`score`)
from ranking
where R.`user`=`user`)
order by `score` desc

※サブクエリ中に、「group by `user`」があってもいい

ただ、、、

(1)得点履歴を残す理由→何に使う？
(2)得点履歴を残す場合、どのタイミングで記録する？
(3)得点履歴を残す場合、何世代前まで保持する？
(4)得点は増えるだけ？減ることはない？

など、ちょっと思いつくだけでも、これくらいの仕様の不明点があります。

インデクスは、どのように付けようと思っていますか？
仕様が不明確な状態だから、そこまで考慮されていないでしょうか？
母体データ数が数百件くらいまでなら、インデクスはなくても平気ですが、１万件、10万件となれば、適切なインデクスがないと、母体件数に比例して性能劣化します。また、group byやorder byを使う場合、適切なインデクスがないと、作業用のメモリやファイルを浪費します。
「重そうだから、MEMORYはどうか？」という、はるか以前の話です。
こんな状態でメモリに乗せようとしたら、過去何世代ものデータを持てばメモリを浪費することになるし、乗り切らないかもしれません。適切なインデクスがなければ、CPU使用率が100%近い状態が続き、ハングアップしたような状況になるかも知れません。

>MEMORYを使用するのに適したケース

(1)多くの処理で必ず参照や更新するようなテーブル
(2)操作は、「=」条件などで絞込みできるもの。
(3)行の長さはなるべく短く、可変長のデータ型は極力使わない。
(4)データの内容は、本当に「メモリに乗せる必要があるものだけ」にする。

補足

ご回答ありがとうございます。
したいことは、
「ユーザーとスコアを表示させるランキング表示」で、
ランキングはスコアの降順になります。
ユーザーのスコアは以前記録したスコアより低くても、最新のものが評価されます。

ご指摘いただいた不明点ですが、以下のようになります。
(1)得点履歴を残す理由は、後々、スコアの変遷などを見るためです。
(2)得点履歴の記録のタイミングは、ゲーム終了時です。
(3)得点履歴の保持ですが、すべて保存します。
(4)得点は減ることがあります。最新のものが反映されます。

教えていただいたSQLですが(こちらの意図を汲んでいただいてすみません。。。)、ほぼこちらで問題なくほしかった結果を取得できました。

select *
from ranking as R
where `sequenceID`=(select max(`sequenceID`)
from ranking
where R.`user`=`user`)
order by `score` desc

最新のものを取得するので、
where `score`=(select max(`score`)
→where `sequenceID`=(select max(`sequenceID`)
と変更して、うまくいきました。

インデックスは「user」につけました。
...ですが、EXPLAINで確認すると、userにつけたインデックスは使えていましたが、主キーのsequenceIDが使えていませんでした。
調べたところ副問い合わせでは、インデックスは使えないようなことを見かけました。

この場合は仕様上しょうがないということで、
インデックスなしのままにするしかないのでしょうか？

もしくは、そもそも一つのテーブルでやろうとせず、
最新データを記録するランキングテーブルと、履歴用テーブルをつくり、
スコア記録時に、
ランキングテーブル→最新データでアップデート
履歴用テーブル→インサート
とし、
ランキング集計時はランキングテーブルから
普通にスコアの降順で集計したほうがいいのでしょうか？

ヒープとは無関係になってきておりますが、ご教授いただけると幸いです。