MySQLのクエリについて質問です。

100万レコード程(1つのレコードは1~最大100文字、平均10文字程度のローマ字または日本語でフィールド数は15)のDBがあります。
主キーはindexというフィールド名で、auto_incrementで単純に1から番号が増えていき、1~1000000まであります。

さて、フィールドのうちの一つ id を指定して、その最後のレコードを取り出したいのですが、クエリとして
select * from test where id = 'id5' order by index desc limit 1
を使っています(1分に数回程度実行)が、この処理が重いらしく、度々DBが落ち、ログインできなくなります。

サポートに連絡すると、「未処理のクエリが残っていたので強制終了しました」とあり、その後ログインできるようになります。
これを解消したいです。

そこで質問があり、内容をコメント欄に示しました。

どうかお力添えをよろしくお願いします。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2011/11/20 19:12:31
  • 終了:2011/11/27 19:15:03

ベストアンサー

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492011/11/20 20:26:00

1)取出すフィールドの数を減らす?
 今回は取出すレコードが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
 
根本部分から説明するとどれだけになるかわからないので、ひとまず以上です
(無料質問でどこまで回答すべき/せざるべきかか悩み中・・・)

他2件のコメントを見る
id:windofjuly

「ポイントをくれ」と言っている訳ではありません
(有料質問だけでなく無料質問にもかなりの割合で多数回答しています)
無料でもどこまでも回答してくれるというイメージがついてしまうと、
「ポイント配分しないと回答しない人」がますます締め出され、
回答者不足がさらに加速するということを懸念してのことです
 
さて、話を戻しまして、
まずはMySQLのデータベースエンジンの欠点を知っておくべきでしょう
・1つのクエリでは1つのインデックスしか利用できない
・どのインデックスを利用するかについての選択がイマイチ
・降順すなわちDESCについてはフルスキャンとなってしまう
 
もっとも足を引っ張るフルスキャン対策として、
index2(以後uniquekey2にしますね)を用意して昇順としています
(マイナス値は先頭ビットで判断するので多分ダメなように思いますが、
 マイナス値を用いての処理速度についてテストはしたことありません)
 
そして適切なインデックスを選択させるための努力として
idもORDER BYに入れてみることも提案しています
SELECT 中略 ORDER BY id, uniquekey2 LIMIT 1
 
なお、
EXPLAINの使い方はSQLの先頭にEXPLAINという言葉を付けるだけですが、
EXPLAINの結果を受け取るようにはなっていないプログラム中に書いてもダメです
phpMyAdminなどのツールから実施するか、あるいは、
別途phpなどでEXPLAINを実行するためだけのテストプログラムを作って実験することとなります

2011/11/22 15:33:00
id:ReoReo7

>「ポイントをくれ」と言っている訳ではありません
理解しました。

>index2(以後uniquekey2にしますね)を用意して昇順としています
負の値になるのを避けるため、100万ではなく、1000万から引いた値を使ってみたいと思います。

>SELECT 中略 ORDER BY id, uniquekey2 LIMIT 1
それも試してみたいと思います。
select * from test where id = 'id5' order by id, uniquekey limit 1
ということですね。

2011/11/22 16:00:22
  • id:ReoReo7
    1)select a, b, c(a,b,cはフィールド名、必要なフィールド数(全体の半数未満)だけ取り出し) ・・・というクエリに変えると、これは倍以上軽くなりますか?
    2)update test set e(eはフィールド名で、100文字程度の日本語が入っている) = null where index < *** のように、
    あまり重要でない昔のデータ量が多いレコードを空にすることで、これは倍以上軽くなりますか?
    3)処理が根本的(倍以上)に軽くなるクエリはありますか?
    4)delete from test where index < *** のように、あまり重要でない昔のレコードを削減することで、これは倍以上軽くなりますか?

    4)と2)は一度目のDB落ち時に試しましたが、二度目のDB落ちに遭ってしまいました。
  • id:ken3memo
    気になった点が1つ
    >主キーはindexというフィールド名で、auto_incrementで単純に1から番号が増えていき、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を追加してみる案でした。(チラっと見た思いつきなので外していたらすみません)
  • id:matane
    index ってつけてるんですよね? create index
  • id:munyaX
    とりあえずexplainとか、show table status, desc あたりの結果が見たいですなぁ。
  • id:ReoReo7
    >munyaXさん

    ●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
  • id:ReoReo7
    >mataneさん

    申し訳ありません、
    indexは本当はuniquekeyというフィールド名です。

    簡略化のためフィールド名を単純化したつもりが、予約語をフィールド名として用いてしまっているように見えたため、余計混乱させてしまったようで申し訳無いです。
  • id:ReoReo7
    >ken3memoさん

    いつもありがとうございます。

    idをキーにするということですね。

    実は、主キーがindex(もといuniquekey)というフィールド、他にキーがいくつかあって、idというフィールドもキーになっていたりします。ので、既にそれは試行済み・・・ということで良いのでしょうか?
  • id:munyaX
    なぜOracleのページを見てますか…。

    > ●「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:ReoReo7
    >EXPLAINをphpmyadminで実行してみました。

    結果は

    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にレコード数0から同じ構造のTABLE testを作り、実行していますが、今のところ軽快です。
  • id:windofjuly
    うぃんど 2011/11/22 17:52:45
    実際のデータに合わせて、SQLをどのように実行すべきかは変わります
    従って、レコード数ゼロの場合と、実際にデータが入っている場合では、
    まったく違った結果を出してきます

    運用中のサーバーで行うとフリーズ状態になる可能性がありますので、
    まずはローカルにコピーを作って、そこで実施してみてください
  • id:ReoReo7
    >windofjulyさん

    ご丁寧に、最後までありがとうございます。

    index
    uniquekey2 = 10000000 - uniquekey
    にて、試してみたいと思います。

    皆様本当にありがとうございました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません