みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

MySqlでのデータソートについて

MySqlバージョン:5.1.61で、下記のSQLを実行すると、
1件しかデータが無いにも関わらず、EXPLAINの結果で
「Extra: Using where; Using filesort」が発生します。

----------------
CREATE TABLE IF NOT EXISTS tbl (
user int(11) NOT NULL,
item int(11) NOT NULL,
prm1 int(11) NOT NULL,
prm2 int(11) NOT NULL,
prm3 int(11) NOT NULL,
PRIMARY KEY (user,item)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tbl (user,item,prm1,prm2,prm3) VALUES (1,1,10,10,10);

EXPLAIN SELECT * FROM tbl WHERE user=1 ORDER BY prm1;
EXPLAIN SELECT * FROM tbl WHERE user=1 ORDER BY prm2;
EXPLAIN SELECT * FROM tbl WHERE user=1 ORDER BY prm3;
----------------

ORDER BY句で使用する項目(prm[n])は10項目以上になりますので
10件を超える複合インデックスを張る事は避けたいと考えております。
また、tbl全体のデータは1億件、1userあたり100~200件を想定しています。

複合インデックスを使用せず「Using filesort」を
発生させなくする事はできるのでしょうか?

投稿日時 - 2014-04-17 16:59:41

QNo.8558217

困ってます

質問者が選んだベストアンサー

>10数件のインデックスを張ったテーブルは普通に運用できるものなのでしょうか……?
>または、「Using filesort」は大きな問題ではないと考えたほうがよいのでしょうか?

インデックスを貼るとデータの追加、削除のスピードは落ちます。
よって検索と更新、どちらを頻繁にするかということも関連してきます。

私が持っているデータで言えば、20年前のアメリカ某大手の大型コンピュータで
1万件をソートして50秒というのがありました。
顧客が電話で問い合わせてくるものに対してデータを表示するソフトでしたので、
50秒は待てないと言われました。
問い合わせのパターンを絞ってもらってインデックスを10個作成して検索したら3秒になりました。

サーバーの処理能力にもかかってきますが、1億件を考えるとテストで打ち込んでも
タイムアウトするか、しばらくそのサーバーを専有してしまうかもしれません。
(他のすべての処理を止めかねない)

投稿日時 - 2014-04-17 19:34:23

補足

ご回答、ありがとうございます。

検索が多くなりがちになりますが、
更新もそれなりに頻繁に行うことになると思います。

現在、テスト環境で、総データ約1千万、1user約100件のテーブルを作成しテストしていますが、
「Using where; Using filesort」は出るものの、約0.001秒で応答が返ってきています。
----------------
EXPLAIN実行結果
 id: 1
 select_type: SIMPLE
 table: tbl
 type: ref
 possible_keys: ref
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 96
 Extra: Using where; Using filesort
----------------
サーバーの性能や、ユーザー数・同時接続数等を見つつ
調整を行っていくしかないかなと、ひとまず納得する事にいたしました。

投稿日時 - 2014-04-17 21:12:11

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

where句に利用するカラムとorder by句で利用するカラムを複合インデックスしないと
order by にインデックスは利用されません。

https://dev.mysql.com/doc/refman/5.1/ja/order-by-optimization.html

今回の命題であれば
10個のカラムにまたがるインデックスではなく
userとprm1,userとprm2,・・・
という感じで個別に貼っていけばよいでしょう

投稿日時 - 2014-04-17 17:21:45

補足

早速のご回答、誠にありがとうございます。

「10件を超える複合インデックスを張る事は避けたい」とは、
下記の様な大量のインデックスを作成する事を避けたいという旨のつもりでした。
----------
ALTER TABLE tbl ADD INDEX idx1(user,prm1);
ALTER TABLE tbl ADD INDEX idx2(user,prm2);
 …
ALTER TABLE tbl ADD INDEX idx[n](user,prm[n]);
----------
理由は、INSERT,UPDATEのパフォーマンスの低下を懸念しての事です。
とはいえ、いろいろ調べてみても、インデックスの張りすぎは
パフォーマンスの低下をまねくとしか書かれておらず、
具体的にどの程度の負荷となるかは分かっていません。

10数件のインデックスを張ったテーブルは普通に運用できるものなのでしょうか……?

または、「Using filesort」は大きな問題ではないと考えたほうがよいのでしょうか?
こちらも、いろいろなサイトで避けるべき項目として記載されていましたので。

投稿日時 - 2014-04-17 19:05:57

ANo.1

できません。ORDER BY 句に従ってインデックスが必要になります。
インデックスを張って事前にソートしておくか、はらずに実行時にソートするかの2択です。

投稿日時 - 2014-04-17 17:12:49

あなたにオススメの質問