MySQLのクエリについて質問です。
１００万レコード程（１つのレコードは１～最大１００文字、平均１０文字程度のローマ字または日本語でフィールド数は１５）のDBがあります。
主キーはindexというフィールド名で、auto_incrementで単純に１から番号が増えていき、1～1000000まであります。
さて、フィールドのうちの一つ id を指定して、その最後のレコードを取り出したいのですが、クエリとして
select * from test where id = 'id5' order by index desc limit 1
を使っています（1分に数回程度実行）が、この処理が重いらしく、度々DBが落ち、ログインできなくなります。
サポートに連絡すると、「未処理のクエリが残っていたので強制終了しました」とあり、その後ログインできるようになります。
これを解消したいです。
そこで質問があり、内容をコメント欄に示しました。
どうかお力添えをよろしくお願いします。
ベストアンサー
-
うぃんど
1)取出すフィールドの数を減らす？
今回は取出すレコードが１件だけなので、
根本的なものではなく、とりあえずはそのままで良い
2)カラムを空にする？
今件のままの環境およびSQLであれば、
多少なりとも軽量化に寄与する可能性はあるけれど、
根本的なものではないため、とりあえずはそのままで良い
4)昔のレコードを削除する？
今件のままの環境およびSQLであれば、
大量のレコード削除は劇的な速度アップに寄与するため、
必要な措置ではあるけれど、根本的な解決ではない
3)クエリだけでの解決方法は？
無理
原因はフルスキャンによるタイムオーバー＆暴走と思われるので、
まずはインデックス用フィールドを作ってみることを勧めてみたい
フィールド名に予約語を含むことの是非は置いておくとして、
index2 というフィールドを追加して下記のように計算した値を入れる
index2 = 10,000,000 - index
次に
id, index2 でインデックスを作成
問い合わせは下記のいずれかとする
SELECT * FROM test WHERE id = 'id5' ORDER BY index2 LIMIT 1
SELECT * FROM test WHERE id = 'id5' ORDER BY id, index2 LIMIT 1
根本部分から説明するとどれだけになるかわからないので、ひとまず以上です
（無料質問でどこまで回答すべき／せざるべきかか悩み中・・・） -
うぃんどその他の回答
この質問へのコメント
質問の情報
- 登録日時
- 2011-11-20 19:12:31
- 終了日時
- 2011-11-27 19:15:03
- 回答条件
- 1人5回まで
この質問のカテゴリ
この質問に含まれるキーワード
人気の質問
-
経験者の方や詳しい方にお聞きしたいです。 親が亡くなった後に親と一緒に暮らしていた親族が遺産隠しや他…
tomo2024tomo
1
1
2
-
もし２０００年代前半からn高があったら日本は変わっていたと思いますか？ 最近の１０代の子どもの多くはn…
匿名質問者11
-
aiで作った女の子が気持ち悪く感じるのは、保存してあるいろんな女の子のパーツからaiが探してきてつけてる…匿名質問者1
-
画像を1ストローク程度でフォルダ分けできるAndroidアプリはありますか？ 例えばTwitterの画像にはイラスト…
Lamron🖇
-
この肌って色白な方ですか？匿名質問者
２）update test set e(eはフィールド名で、100文字程度の日本語が入っている) = null where index < *** のように、
あまり重要でない昔のデータ量が多いレコードを空にすることで、これは倍以上軽くなりますか？
３）処理が根本的（倍以上）に軽くなるクエリはありますか？
４）delete from test where index < *** のように、あまり重要でない昔のレコードを削減することで、これは倍以上軽くなりますか？
４）と２）は一度目のDB落ち時に試しましたが、二度目のDB落ちに遭ってしまいました。
>主キーはindexというフィールド名で、auto_incrementで単純に１から番号が増えていき、1～1000000まであります。
これは、いいとして、
.
>さて、フィールドのうちの一つ id を指定して、その最後のレコードを取り出したいのですが、クエリとして
.
id指定(を条件に)データを取り出し、indexというフィールド名の一番後ろを取りたいので、
select * from test where id = 'id5' order by index desc limit 1
のSQL文だと思いますが、
id+index の キーを作ると 速度的にどうなるか？興味があったり。
※idが10種類とか、少ないと効果が無いかもしれませんが、idが多種類あるなら idをキーに含めると(idのキーを追加すると) where id = 'id5' が速くなると思いますが。
以上、キーにidを追加してみる案でした。(チラっと見た思いつきなので外していたらすみません)
●show table status
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
test MyISAM 10 Dynamic 742402 321 238320960 281474976710655 127460352 0 5375692 2011-11-20 17:12:02 2011-11-20 17:35:24 2011-11-20 17:13:21 ujis_japanese_ci NULL
●「, desc」の部分→意味がわかりませんでした。すみません。
●「explain」の部分→以下参照しましたが、方法が分かりませんでした。申し訳ありません。
http://www.shift-the-oracle.com/sql/explain-plan.html
申し訳ありません、
indexは本当はuniquekeyというフィールド名です。
簡略化のためフィールド名を単純化したつもりが、予約語をフィールド名として用いてしまっているように見えたため、余計混乱させてしまったようで申し訳無いです。
いつもありがとうございます。
idをキーにするということですね。
実は、主キーがindex（もといuniquekey）というフィールド、他にキーがいくつかあって、idというフィールドもキーになっていたりします。ので、既にそれは試行済み・・・ということで良いのでしょうか？
> ●「explain」の部分→以下参照しましたが、方法が分かりませんでした。申し訳ありません。
> http://www.shift-the-oracle.com/sql/explain-plan.html
例えばコンソールで以下のように、select文の頭に"explain"をつけるだけです。
mysql> explain select * from foobar
ここで出てきた結果を見ると、何が原因で遅くなっているか原因を探ることができます。
詳しくは以下を。
http://nippondanji.blogspot.com/2009/03/mysqlexplain.html
＃大抵の場合はインデックスがはられていない、張り方を間違えて遅くなっているなどです。
結果は
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test index id PRIMARY 4 NULL 7 Using where
申し訳ありませんが、これがどういう意味なのかはわかりませんでした。結果は7行なので、そんなに多くは無いということでしょうか（DBを大幅に削除した後なので当然かもしれませんが）。
尚、新規DBにレコード数０から同じ構造のTABLE testを作り、実行していますが、今のところ軽快です。
従って、レコード数ゼロの場合と、実際にデータが入っている場合では、
まったく違った結果を出してきます
運用中のサーバーで行うとフリーズ状態になる可能性がありますので、
まずはローカルにコピーを作って、そこで実施してみてください
ご丁寧に、最後までありがとうございます。
index
uniquekey2 = 10000000 - uniquekey
にて、試してみたいと思います。
皆様本当にありがとうございました。