ソートの最適化の方法

質問

mySQL5.0.45で、ある500万件以上のテキストデータをインポートし、ソートの上、データのエクスポートを行う作業をしています。
以下のようにテーブルを作り、
create table test (
seq int8,
sid char(028),
pay int,
sp1 int
) type=Myisam;
alter table test add primary key (
seq
);
LOAD DATA　～で取り込み
SELECT * FROM test ORDER BY sid ;

ソートを効率よく行うため、my.iniを修正し
(1)tempを空きドライブに移動
(2)作業スペースの確保
　key_buffer_size=256M
　sort_buffer_size=3000M

処理をまわしましたが、ERROR5(HY000) out of memory (needed 695000000byte)で落ちてしまいます。
修正不足があるのでしょうか。
また、win2000スタンドアロンベースでトランザクションを気にする必要もないため、myisam流しているのですが、エラー発生までに5時間以上かかります。ソート対象件数の増加も考えており、先の修正不足を含め、最適化(高速化)の方法を教えていただきたいのですが。

回答No.3

chukenkenkou

メモリ増には限界があるでしょうし、ソートキー長やデータ件数が増加すれば、また設定値の見直しが必要になるかも知れません。

根本的な問題は、ソートを発生させてしまっていることです。ソートが発生するとメモリ所要量やテンポラリ・ファイルへのI/Oが増え、処理時間も掛かります。

一定の件数以上が格納されている状況で、
「SELECT * FROM test ORDER BY sid 」
といったSQLを実行する場合、
「create index test_idx1 on test(sid)」
というインデクスがあれば、インデクスを利用することでソート抑止可能です。
いくつかのRDBMSでは、「order by」指定とインデクスの定義が合致していると、ソート抑止してくれます。
MySQLの場合は、これに加え、ソートキーで全件ヒットする「sid>=最小値」といった検索条件が必要なようです。

上述のインデクスを定義し、
「「SELECT * FROM test WHERE sid>=最小値 ORDER BY sid 」
と変更してEXPLAINの結果を見てみてください。filesortが発生しなくなっていれば今回の問題は発生しなくなると思います。

なお、データが格納されている状態で、create index時に内部的にソートが発生するので、やはり今回のエラーが発生するかも知れません。その場合は、データ格納前にインデクスを定義しておく必要があります。

補足

ありがとうございます。
正しく理解できていないかもしれませんが、インポートのあと
create index test_idx1 on test(sid) ;
SELECT * FROM test WHERE sid>=' ' ORDER BY sid ;
EXPLAIN test ;
このあとエクスポート・・・
として、確かにエラーは出ませんでした。ただ、ソートしたデータのエクスポートという目的は達成できていません。
indexが用意されるということだけだとしたら、データの序列の示されているindexをテキストでエクスポートしてソートの代わりに使うことも考えましたが、いかんせんindexのエクスポートの手順がわかりません。
手立てがあれば教えていただけないでしょうか。

回答No.2

noname#140971

全面撤回します。
全く、勘違いしておりました。
挿入ソートで吐き出し用一時テーブルに書き出すアイデアが途中から無茶苦茶に・・・。
よって、完全撤回します。

回答No.1

noname#140971

[イミディエイト]
? 2^23
8388608

つまり、23回の検索で最大値ないし最小値が判るということです。
この検索に0.001秒を要したとすれば、5,000秒で全ての検索が終了します。
5000秒=83分ということになります。
もちろん、＜この検索に0.001秒を要したとすれば＞はテストなんかしていません。
検索の都度にエクスポート一時テーブルに書き出すとすれば、どれだけ遅くなるのかは定かではないです。
検索の都度にファイルにアペンドする方が早いのかも定かではないです。

当方、ブログラマではなく服飾デザイナです。
ですから、参考程度に・・・。