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

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

まずは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[運用+管理]トラブルシューティングガイド

posted with ヨメレバ

奥野幹也技術評論社 2010-06-12

Amazon

Kindle

楽天ブックス

まとめ

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

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