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

質問

MySqlバージョン：5.1.61で、下記のSQLを実行すると、
１件しかデータが無いにも関わらず、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」を
発生させなくする事はできるのでしょうか？

回答No.3

maiko0318 · ベストアンサー

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

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

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

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

補足

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

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

現在、テスト環境で、総データ約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
----------------
サーバーの性能や、ユーザー数・同時接続数等を見つつ
調整を行っていくしかないかなと、ひとまず納得する事にいたしました。

回答No.2

yambejp

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

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

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

補足

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

「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」は大きな問題ではないと考えたほうがよいのでしょうか？
こちらも、いろいろなサイトで避けるべき項目として記載されていましたので。

回答No.1

maiko0318

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