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.3
- 回答日時:
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
- 回答日時:
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)得点は増えるだけ?減ることはない?
など、ちょっと思いつくだけでも、これくらいの仕様の不明点があります。
インデクスは、どのように付けようと思っていますか?
仕様が不明確な状態だから、そこまで考慮されていないでしょうか?
母体データ数が数百件くらいまでなら、インデクスはなくても平気ですが、1万件、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が使えていませんでした。
調べたところ副問い合わせでは、インデックスは使えないようなことを見かけました。
この場合は仕様上しょうがないということで、
インデックスなしのままにするしかないのでしょうか?
もしくは、そもそも一つのテーブルでやろうとせず、
最新データを記録するランキングテーブルと、履歴用テーブルをつくり、
スコア記録時に、
ランキングテーブル→最新データでアップデート
履歴用テーブル→インサート
とし、
ランキング集計時はランキングテーブルから
普通にスコアの降順で集計したほうがいいのでしょうか?
ヒープとは無関係になってきておりますが、ご教授いただけると幸いです。
No.1ベストアンサー
- 回答日時:
MEMORY等を使うこと以前に、テーブル設計やインデクス設計をしっかり行った方が良いのでは?
質問中のSQLは、文法的に間違っているし、性能上も好ましくありません。
母体データ件数、ユーザ毎の平均のデータ件数は、ある程度、見積もれているのでしょうか?
また、ユーザ毎のスコアの履歴を、残す必要があるのでしょうか?そういう点でもメモリ常駐する意味があるのか疑問です。
この回答への補足
ご助言ありがとうございます。
SQL文の文法的誤りは、
副問い合わせの
「select `sequenceID`,`score` from ...」の部分でしたでしょうか。。。
ここは「select `sequenceID` from ...」でOKでしょうか。
また、質問の趣旨とはずれますが、
こういう場合のSQL文の例など、教えていただけると幸いです。
>母体データ件数、ユーザ毎の平均のデータ件数は、ある程度、見積もれているのでしょうか?
こちらですが、まだオープン前のサイトなので、目処がたっていません。
ユーザごとのスコアの履歴は残す必要があります。
MEMORYを使用したいと考えたのは、単純に高速になるかな。。という浅はかな考えからでした。
逆に、MEMORYを使用するのに適したケースというのは、どういった場合なのでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL PhpMyAdminで作成して実行せよ。 東京23区を、皇居を中心とした4つのエリア(南東, 南西, 1 2023/06/11 11:58
- MySQL SQLです。下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 「昨年の各月の総降 1 2023/07/01 00:32
- MySQL うまくいきません教えてくださいお願いしますSQLです。クエリ構文です。 1 2023/07/07 12:39
- MySQL SQLです。こんな感じですか?あってますか? うまくいきません教えてくださいお願いします 1 2023/07/08 15:27
- PostgreSQL SQLで検索結果の記事を表示したい 1 2022/04/28 21:03
- PHP php my adminより取り出したデータ表示 2 2022/06/15 11:56
- MySQL 下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 「名前(first name) 1 2023/06/24 13:03
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- Oracle SQLについて教えて下さい。 主キーを持ったカラムを主キーの機能を持たせたまま カンマ区切りで文字列 1 2023/03/27 22:47
- その他(プログラミング・Web制作) python コードについて(初学者です) 3 2023/07/20 14:44
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
#1062 - '0' は索引 'PRIMARY' ...
-
テーブル名が可変の場合のクエ...
-
SQLです。こんな感じですか?あ...
-
下記の問合せを行うクエリを、P...
-
下記の問合せを行うクエリを、P...
-
下記の問合せを行うクエリを、P...
-
mysqlがインストールされている...
-
下記の問合せを行うクエリを、P...
-
クエリを教えてください select...
-
SQL任意に並び替えをしたい
-
下記の問合せを行うクエリを、P...
-
mysqlがインストールされている...
-
東京23区を、皇居を中心とした...
-
書籍の内容はまともでしょうか?
-
MySQL NULLだけをカウントして...
-
本を見ながらPHPを勉強している...
-
うまくいきません教えてくださ...
-
「都道府県の面積の大きい順に...
-
あっってますか?うまくいきま...
-
[1000地域 × 10カテゴリー = 1...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
#1062 - '0' は索引 'PRIMARY' ...
-
テーブル名が可変の場合のクエ...
-
「最高気温が35度以上の日を猛...
-
SQLでカラムを追加し、条件に合...
-
mysqlのupdate構文についての質...
-
htmlコードで書かれた表にphpで...
-
php テーブルを作れない
-
SQLです。こんな感じですか?あ...
-
「第一回模試の3科目の各得点...
-
データベースの接続に失敗して...
-
select *, `人口(男)`AND`人口(...
-
次の時間帯の勝率の合計を求め...
-
下記の問合せを行うクエリを、P...
-
うまくいきません教えてくださ...
-
SQLです。下記の問合せを行うク...
-
エラー 1068 (42000): 複数の主...
-
東京23区を、皇居を中心とした...
-
MySQL NULLだけをカウントして...
-
データベースの複製の仕方(mysql)
-
MySQLのエラーメッセージ(エラ...
おすすめ情報