MySQLのlimitで大量データのパフォーマンスが遅い重い!

mysql_limit_pf
MySQLのlimitを使用した場合、オフセット部分が増えるとパフォーマンスが低下する場合の対処方法メモ。
データベースからlimit指定で必要な情報を抜き出すのは便利ですが、インデックスが使われていなかったりして激重になるケースもよく見かけます。
自分自身の勉強も兼ねて大量データに対するlimitの使い方を色々調べてみました。

SPONSORED LINK

まずはlimitの処理内容を理解する

例文からlimitの内部処理がどうなっているのかを見てみましょう。

mysql> select * from hoge order by id desc limit 100, 10;

上記の場合「データを100から10個だけ取得」ではなく「110個データを取得してから先頭から100個を捨てる」という処理が行われます。
初心者がMySQLのlimitで重いと勘違いしやすい部分でしょう。
では、この場合の対処方法はどうなるのでしょうか?

対処方法

betweenなどWHERE句を使用してリスエストする!

mysql> select * from hoge where id between 100 and 110 order by id desc;

limitだけで全ての処理を行っているようなプログラムを見かけますが、適した関数を選択することでパフォーマンスの維持に繋がりますね。
注意点としては、インデックスを利用する為にはWHERE句とORDER BY句の両方で同じカラムを指定することが必要になりますので、柔軟な利用には適していないかもしれません。

大量データのオフセットのパフォーマンス

それでは100万件以上あるデータを取り扱う場合で見ていきます。

まずは、100万件の後半を取得する処理をみてみます。

SELECT * FROM `hoge` LIMIT 999990 , 10;
10 rows in set (6.2237 sec)

これは激重。
前述した通り100万件取得して99万9990件を捨てる処理になってしまっているということですね。

念のため100万件の前半をオフセットで取得してみましょう。

SELECT * FROM `hoge` LIMIT 10;
10 rows in set (0.0014 sec)

本来はこのぐらい早くないとダメですよね。

対処方法

プライマリキーを使って条件を絞る!
番号に抜けがない時に使える小技ですね。

SELECT * FROM `hoge` WHERE `id` >999990 LIMIT 10;
10 rows in set (0.0014 sec)

圧倒的にlimitのパフォーマンスが上がりました。
もちろん「order by」を付与して使っても問題ありません。

まとめ

MySQLで大量データを取り扱う局面ではこういった小技を知っておかないとサーバー処理はパンクしてしまいます。
逆に数百件程度なら、目に見えるパフォーマンス低下が起こらないので、無駄な処理に気づきにくいかもしれません。
インデックスやプライマリ、並べ替えなどクリーンにガシガシMySQLを使っていきたいですね。

今日も知識欲は止まらない。

関連トピック

mysql

MySQL カラム名・テーブル名などに使ってはいけないキーワード

MySQLでテーブル名やカラム名に使用できないワードの一覧まとめ 予約語を使うのは危険なので回避しましょう。 バッククオートで囲って

php_timestamp

PHPでタイムスタンプを変換して日付を表示する

PHPでタイムスタンプへの変換とタイムスタンプからの変換メモです。 データベースの日付型からの出力などで使ったり、xmlやRSSでの日

Question

PHPのdate関数で「年」が文字化けした時の対処方法

PHPのエラーについて「PHP date 年 文字化け」です。 PHPで日付を表示する時に欠かせないdate関数ですが「Y年」が文字化

Text

PHPエラー対処方法「RSSをXML取得時に不正文字が存在する場合」

PHPでRSSをsimplexml_load_stringなどを使って解析する場合に不正な文字が含まれている時に発生するエラー対処方法の

Facebook

FacebookのRSSを取得する方法@Facobook API JSON対応

FacebookのRSSを取得する方法メモです。Facebookでは大規模な仕様変更が何度も行われて、古い情報ではRSSの取得ができない

php-cannot

PHPで配列のエラー「Cannot use string offset as an array」がでる場合の対処法

PHPで「Cannot use string offset as an array」が出たら確認したいポイントをまとめました。 このエ

earth

PHPなどでDB不要のブログがまだまだ使えそう

データベースまで利用してわざわざ作るほどでもないブログや、 データベースが使えないサーバーだったり、 とにかく軽量化されたブログを目

Yesterday

PHPのdate関数で日付「昨日・明日・1時間前」を取得しよう!

PHPのdate関数を使えば自分の取得したい日付を1行でズバッと指定できます。 もう簡単すぎて、プログラムというよりは言葉で伝えてあげ

Comment

  1. […] MySQL limit 大量のデータのパフォーマンス | デアイモビ […]

公開日:2012/11/02