大きいデータ数のテーブルに対するインデックス作成

質問

mySQL server 5.1　でのindex作成について質問です
かなり大きいデータ数(1000億)のテーブルを扱う必要があり検索速度向上のためにindexを作成しようとしています。テーブルのdouble型のカラムに対してインデックス作成コマンドを入力しましたが（create index）、数日経ってもまだインデックス作成が終わりません。長すぎて何か問題でも起きているのでは、と思ってのですが同様の形式のサイズの小さいデータベースに対して同様の処理を行うと問題なく終わります。

なんらかの方法でインデックス作成のスピードを上げることは可能でしょうか？たとえばint型のデータ型に変換するとスピードが向上するなどはあるでしょうか？

回答No.2

nora1962 · ベストアンサー

「インデックス作成のスピード」に最も影響を与えるパラメータは「SORT_BUFFER_SIZE」です。
この値はセッション単位に変更可能ですから「SET SESSION SORT_BUFFER_SIZE メモリサイズ」で一時的にソートエリアを拡大します。単位はバイトです。512MBにしたい時は「SET SESSION SORT_BUFFER_SIZE　536870912」を実行してください。
後、「READ_BUFFER_SIZE」「READ_RND_BUFFER_SIZE」をそれぞれ1MB程度にして見てください。
もちろん、一時的にであれ、「SORT_BUFFER_SIZE」を大きくすることは他のセッションと資源の取り合いになる危険性もありますし、32ビットベースのMYSQLではプロセス全体のメモリサイズ2GBの制限にも気をつけなければなりません。最悪クラッシュします。
メモリ配分にはよく注意してください。

お礼

とてもためになるアドバイスありがとうございます。まさにこのようなことが知りたかったのです。SORT_BUFFER_SIZEはデフォルトではかなり低く設定されているようなので、メモリサイズの制限に気をつけつつ設定をあげてみようと思います。

回答No.4

chukenkenkou

#3です。

>double型を範囲指定して（例えば 90.1< x < 90.3）該当するデータ

浮動小数点のデータ型では、小数点以下の精度は厳密に保証されませんが、その辺は大丈夫でしょうか？

インデクス作成時、1000億件のソートを行うことになり、MySQLサーバのパラメタでの作業領域増といったレベルでは対処しようがないと個人的には思っています。

もし、試行錯誤されるのでしたら、どういう設定をしてどのように状況が変わったといったことを、参考までに知らせてもらえるとありがたいです。

お礼

色々ネットをしらべて以下のページにたどり着きました。
http://serverfault.com/questions/140488/mysql-create-index-on-1-4-billion-records
このページで言われているようにパーティションを40ほどに分け、double型をint型に変更して（データの仕様を変えて）パーティションに対応させたうえでテーブルを作り直しました。
パーティション作成前は1週間かかってもインデックスを作り終えることができませんでしたが、作成後は2時間ほどでインデックス作成が終了しました。

今回の件ではいろいろ勉強になりました。

回答No.3

chukenkenkou

>かなり大きいデータ数(1000億)のテーブルを扱う必要

どういうテーブル設計をしているのでしょうか？

世の中で動いている「大規模」と言われるシステムでも、1個のテーブルで1000億件などという設計はしません。

クラスタ化してサーバーを分散したり、一定期間より前のデータ、アクセス頻度が殆どないデータなどを、別方式で管理するといったことをします。

>検索速度向上のためにindexを作成しようと

どういう検索をするのでしょうか？

「=」条件や範囲条件で、母体から相当に絞り込めるような検索でないと、インデクスによる性能向上は図れません。また、order by、group by、distinctなどのソートを要する処理で、インデクスを活用できずに「作業ファイルを使ってのソート」が発生すると、十分な性能向上を図れません。特に、十分に絞り込みできない状態での「作業ファイルを使ってのソート発生」は、致命的になります。

また、MySQLでは、複合キー（複数列）での昇順と降順の混在したインデクスを、実装していません。

例えば、

create index t1ix1 on t1(c1,c2 desc)

のようなインデクスを定義すると、定義自体は成功しても、実際には

create index t1ix1 on t1(c1,c2)

というインデクスに内部的に変更して作成されます。

これに伴い、

select * from t1
where c1 between a and b
order by c1,c2 desc

といった昇順と降順を混在したソートも、インデクスを活用できません。

逆に、

select * from t1
where c1 between a and b
order by c1,c2

や

select * from t1
where c1 between a and b
order by c1 desc,c2 desc

といった操作では、インデクスを活用できます。（ただし、絞込み度合いによる）

検索条件で絞り込める　＆　ソートの仕方は、クラスタ化をする上でも、重要な要件になります。

いずれにしても、もっと具体的な情報提示がなければ、具体的なアドバイスは誰にもできません。

お礼

具体的な情報を提示せず、分かりづらくしてしまってすみません。
string型、double型、int型カラムからなるテーブルでdouble型を範囲指定して（例えば 90.1< x < 90.3）該当するデータを取り出す、という形です。データベース運用自体ほぼ初めてなので軽い気持ちではじめてしまって、実際は複雑な操作が必要なようでやや戸惑っています。
検索の仕方の具体的なアドバイスありがとうございます。

回答No.1

yambejp

億単位になると小手先のごまかしではパフォーマンス改善は期待できないんじゃ
ないですかねぇ・・・
CPU・メモリ・OS含むファイルシステムなんかも絡めてカリカリに
チューニングしていかないと難しいかと

MySQLもどのソリューションを利用しているかわかりませんが
どうしてもその処理が必要ならITコンサルを絡めて商用ベースの特別な
システムを組むことを検討する段階かもしれません。

あとはある程度のスパンで予め最適化した集計済みのデータを作成しておいて、
不要な検索を避けるようにするとか運用上の工夫も少なからず必要です。

お礼

会社ではなく個人規模での作業のためのデータベースなのでコンサルタントや商用ベースのシステムというのは厳しいですね。。
正直データベース運用というものを理解しないままいきなり大量のデータを扱いはじめたので右も左も分からない状態でした。

実際の運用は大変なものなのですね。ありがとうございました。