MySQLのLIMIT句についての質問です。バージョン5.0.22です。


現在DBに1000万件ほどの簡単なデータを入れ色々とテストを行っているのですが、以下のような時、どうしても処理が遅くなってしまいます。これは「LIMIT」の仕様でしょうか。「id」は主キーで「hoge」はテーブル名です。

mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 9000,10;
10 rows in set (8.30 sec)


なお、オフセットの部分が0〜1000くらいでしたら問題ない速度です。
mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 0,10;
10 rows in set (0.00 sec)
mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 1000,10;
10 rows in set (0.05 sec)


9000から9010行目までの範囲をサクッと取り出せるのが理想なのですが、たった10行を取り出すのに実際には9010レコードを走査してしまっていているようです。これは仕方のないことなのでしょうか?何か良い方法があればご教授ください。実際のクエリーをお知らせ頂くと大変助かります。

なお、こんな記事も見つけましたが、出来ればデフォルトのMYSQLのみで何とかしたいと思います。。
http://qwik.jp/senna/old_mysql_binding_docs.html

以上、よろしく願い致します。

回答の条件
  • 1人3回まで
  • 登録:2007/05/14 14:41:01
  • 終了:2007/05/14 19:06:07

ベストアンサー

id:Yota No.4

Yota回答回数453ベストアンサー獲得回数282007/05/14 16:23:29

ポイント40pt

現在DBに1000万件ほどの簡単なデータを入れ色々とテストを行っているのですが、以下のような時、どうしても処理が遅くなってしまいます。これは「LIMIT」の仕様でしょうか。「id」は主キーで「hoge」はテーブル名です。

mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 9000,10;

10 rows in set (8.30 sec)

このテーブルは常時更新するものでしょうか、それともマスタテーブルのように割と長期間更新されないのでしょうか。

もし前者ならAUTO_INCREMENTのカラムが1からふられているのでそのINDEXをWHEREに使えばいいので簡単ですよね。

後者の場合、更新(削除・追加)のたびにidを付け直すという手があります。

つまりidのMAXイコールレコード数という状態にしておくということです。

>SELECT * FROM hoge WHERE id BETWEEN(9001,9010) ORDER BY id DESC ;

ならすぐ返ってきますよね。

他に、1000万件というレコード数を考えた場合、テーブルの水平分割が考えられると思います。例えば、hogeを100のテーブルに分割して1テーブル約10万レコードにすれば、上のクエリは1番目のテーブルしかソートする必要はないということです。「非正規化」「水平分割」などのキーワードで書籍などを調べればもっといい方法があると思います。

さらに、テーブルを作らなくてもレコード数で区切ってVIEWを作っておくのもありだと思います。

id:uniuniko

ご回答ありがとう御座います。

テーブルはアプリケーションで言うところの「掲示板」で使用しております。普通に記事ナンバーや発言を記録しております。

なるほど!いろんなやり方があるのですね。大変参考になりました。まだ私の方のレベルが低すぎてYotaさんの仰ることが100%理解出来ておりませんが、BETWEENを使ったテストでサクッと取り出すことが出来ました!目からウロコです。

BETWEEN(9001,9010)の、開始番号と終了番号の取得を確定出来れば、やりたいことが実現出来そうです。※実際には番号が欠番になることがあるので、、


もう少し詳しく説明いたしますと、掲示板で次のページへという処理を行っております。アプリ側は..hoge.php?page=100などの感じです。


取り急ぎお礼申し上げます。引き続き色々と試行錯誤してみたいと思います。

この度はありがとう御座いました。

2007/05/14 17:17:44

その他の回答(3件)

id:nohohon_x No.1

nohohon_x回答回数72ベストアンサー獲得回数12007/05/14 14:55:53

ポイント23pt

テーブルの構造を見ていないので、なんとも言えませんが、

テーブル作成の際に、インデックスを作成するとレコード数が増えた場合、速度向上が期待できます。

詳しくは下記URLにて。

http://dev.mysql.com/doc/refman/4.1/ja/mysql-indexes.html

http://dev.mysql.com/doc/refman/4.1/ja/tips.html

id:uniuniko

ご回答ありがとう御座います。

テーブルは以下のような感じで作成しました。


$sql = 'CREATE TABLE hoge (

id INT NOT NULL AUTO_INCREMENT,

aaa CHAR(16) NOT NULL,

bbb INT(4) NOT NULL,

ccc INT NOT NULL,

PRIMARY KEY (id),

)TYPE = myisam;';



idが主キー(インデックス)になっていて、その点は大丈夫かと思います。

2007/05/14 15:03:54
id:studioes No.2

studioes回答回数522ベストアンサー獲得回数612007/05/14 15:16:44

ポイント28pt

>これは仕方のないことなのでしょうか?何か良い方法があればご教授ください。実際のクエリーをお知らせ頂くと大変助かります。

 仕方のないことだと思います。 というのは、この場合、ORDER BYを利用しているわけですが、LIMIT x,yにおいて、xの位置はORDER BYによりソートした結果になるので、レコードの開始位置までソート処理しなければxが確定できない為です。

 EXPLAINで問い合わせを確認してみたらいかがでしょうか。

id:uniuniko

ご回答ありがとう御座います。

なるほど、やはり仕方ないことなのですね、、

2007/05/14 15:27:19
id:kn1967 No.3

kn1967回答回数2915ベストアンサー獲得回数3012007/05/14 15:21:35

ポイント30pt

■仕様

SELECT * FROM hoge ORDER BY id DESC

の返してきた結果セットに対して LIMIT が働く。

  ↓

SELECT * FROM hoge ORDER BY id DESC

までの部分ではインデックスの有無で処理速度が大きく変わるけれども

LIMITの時点ではインデックスの有無は既に関係無い状態。

■対処

WHEREで結果セットを少なくするしかないでしょう。idを利用して絞り込む方向で考えてみてください。

データの抹消などもあってidが一部欠如する場合もあるかとは思いますが、

その部分はイレギュラー処理(結果がゼロ件ならば再検索など)するようなロジックを組んで対処すれば良いでしょう

(イレギュラー処理を行っても8.3secよりは十分に高速だと割り切るしかない)

id:uniuniko

ご回答ありがとう御座います。

なるほど、そのような内部処理なのですか、勉強になります。

対処方も大変参考になりました。ようは工夫ですね。もう少し色々考えてみたいと思います。

2007/05/14 15:27:16
id:Yota No.4

Yota回答回数453ベストアンサー獲得回数282007/05/14 16:23:29ここでベストアンサー

ポイント40pt

現在DBに1000万件ほどの簡単なデータを入れ色々とテストを行っているのですが、以下のような時、どうしても処理が遅くなってしまいます。これは「LIMIT」の仕様でしょうか。「id」は主キーで「hoge」はテーブル名です。

mysql> SELECT * FROM hoge ORDER BY id DESC LIMIT 9000,10;

10 rows in set (8.30 sec)

このテーブルは常時更新するものでしょうか、それともマスタテーブルのように割と長期間更新されないのでしょうか。

もし前者ならAUTO_INCREMENTのカラムが1からふられているのでそのINDEXをWHEREに使えばいいので簡単ですよね。

後者の場合、更新(削除・追加)のたびにidを付け直すという手があります。

つまりidのMAXイコールレコード数という状態にしておくということです。

>SELECT * FROM hoge WHERE id BETWEEN(9001,9010) ORDER BY id DESC ;

ならすぐ返ってきますよね。

他に、1000万件というレコード数を考えた場合、テーブルの水平分割が考えられると思います。例えば、hogeを100のテーブルに分割して1テーブル約10万レコードにすれば、上のクエリは1番目のテーブルしかソートする必要はないということです。「非正規化」「水平分割」などのキーワードで書籍などを調べればもっといい方法があると思います。

さらに、テーブルを作らなくてもレコード数で区切ってVIEWを作っておくのもありだと思います。

id:uniuniko

ご回答ありがとう御座います。

テーブルはアプリケーションで言うところの「掲示板」で使用しております。普通に記事ナンバーや発言を記録しております。

なるほど!いろんなやり方があるのですね。大変参考になりました。まだ私の方のレベルが低すぎてYotaさんの仰ることが100%理解出来ておりませんが、BETWEENを使ったテストでサクッと取り出すことが出来ました!目からウロコです。

BETWEEN(9001,9010)の、開始番号と終了番号の取得を確定出来れば、やりたいことが実現出来そうです。※実際には番号が欠番になることがあるので、、


もう少し詳しく説明いたしますと、掲示板で次のページへという処理を行っております。アプリ側は..hoge.php?page=100などの感じです。


取り急ぎお礼申し上げます。引き続き色々と試行錯誤してみたいと思います。

この度はありがとう御座いました。

2007/05/14 17:17:44
  • id:kurukuru-neko
    >SELECT * FROM hoge ORDER BY id DESC LIMIT 9000,10;


    回答#3にあるように
    SELECT * を SELECT ID
    に変更すると処理するデータ量に
    aaa/bbb/ccc/dddが含まれなくなります。

    サンプルで1000万件で実験してみると
    (データ約600M、索引約100M)

    SELECT * FROM hoge ORDER BY id DESC LIMIT 9000,10;
    10 rows in set (0.02 sec)

    とするとかなり速度が違います。

    select * from (SELECT id FROM hoge ORDER BY id DESC LIMIT 9000,10) as s left join hoge using(id);
    10 rows in set (0.01 sec)

    SELECT * FROM hoge WHERE id BETWEEN 9001 and 9010 ORDER BY id DESC ;
    10 rows in set (0.00 sec)

    となりました。
    my.cnfのisamのsortバッファの量
    や、搭載メモリによりだいぶ結果が違うことが
    あります。



  • id:uniuniko
    ご回答ありがとう御座いますm(__)m

    仰るとおり、「*」を使わず、直指定することで雲泥の差となりました。ビックリです。。こんなに違うものなんですね、、

    また、以下も試させて頂きました。がこちらも同様高速です。
    select * from (SELECT id FROM hoge ORDER BY id DESC LIMIT 9000,10) as s left join hoge using(id);

    joinなどはまだ勉強していないので、よく分からないのですが大変参考になりました。この度は誠にありがとう御座います。

  • id:kn1967
    ページ移動の際に、現在表示しているIDを投げさせて
    WHERE句で、投げられたIDから前(あるいは後)何件というような形でで絞り込めばインデックスが効きますし、
    遅くなる原因のLIMITは使わなくても済みますので高速処理可能になりますよ。
    1ページ10件だとすれば少し大目に前20件(後20件)くらい返すようにしておいてTOP句で10件に減らすという形にするなどと組み合わせて考えてみて下さい。
  • id:uniuniko
    ご回答ありがとう御座いますm(__)m
    なるほど、、そのような方法もあるんですね。勉強になります。
    TOP句というのはまだよく解らないのですが、ちょっと調べてやってみます!
  • id:kn1967
    ごめんなさい。。。。
    TOP句はAccessなどで使われているものです。
    平行作業していたので少々ごちゃごちゃになってしまいました。
    申し訳無いです。

    ページ移動の際に、現在表示しているIDを投げさせて
    WHERE句で、投げられたIDから前(あるいは後)何件というような形でで絞り込めばインデックスが効きますし、
    遅くなる原因のLIMITは使わなくても済みますので高速処理可能になりますよ。
    1ページ10件だとすれば少し大目に前20件(後20件)くらい返すようにしておいてHTML生成ロジックのほうで10件分を超える部分は無視(10件に満たない場合は範囲を広げて再度絞り込み)というような方法も考慮してみてください。

  • id:uniuniko
    そうだったんですか^^
    いえいえ、全然大丈夫です。
    ご丁寧にお知らせ頂きありがとう御座いました。
  • id:kurukuru-neko

    >TOP句はAccessなどで使われているものです

    現在のページから前後へ移動する場合は、
    TOP相当の処理はbetween等と併用すれば
    簡単に実現可能です。

    例えば10件単位で1ページ前を表示する場合
    現在位置を9000とすると削除が連続最大30件
    程度と見積もると
    1ページ前は、IDは9000-30~9000-1
    の最後の10件を選べばよい。

    select * from (SELECT id FROM hoge where id BETWEEN 8970 and 8999 ORDER BY id DESC LIMIT 10) as s left join hoge using(id) order by id;

    逆も同様に最大IDが9010からとすると

    select * from (SELECT id FROM hoge where id BETWEEN 9011 and 9030 ORDER BY id asc LIMIT 10) as s left join hoge using(id);

    とか出来る。

    Nページ前後も同様に実現できる。

  • id:kurukuru-neko

    set @row=0;
    select * from (select @row:=@row+1 as rownum,id from hoge order by id asc) as pageidx having mod(rownum-1,10000)=0;

  • id:uniuniko
    おはよう御座います。
    度々ありがとう御座います。

    「削除が連続最大30件程度と見積もると」の部分ですが、このあたりで悩んでおります。削除される記事が万が一100件とかになったら、、と考えるとどうしたもんか、、、と悩んでいます。そんなに削除されることは殆どないと思いますが^^;

    多めに取り出し、後でカットする方法がやはり負荷的に無難ですかね^^;
    引き続き試行錯誤してみます。

    この度はどうもありがとう御座いました。
  • id:standard_one
    力技ですが、メモリをいっぱい積んでRAMディスク上で動かすとか、CPUを速いのに変えるとか、そんな方法もあるかと・・・

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

トラックバック

  • 2011年04月13日のツイート > @bootjp: @mocchicc お疲れ様です。届くのを楽しみにしています。 2011-04-14 03:54:26 via web to @mocchicc @bootjp: それもありですね、ただ公開するタイミングを上手くやらな
「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

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

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