mySQL server 5.1 でのindex作成について質問です
かなり大きいデータ数(1000億)のテーブルを扱う必要があり検索速度向上のためにindexを作成しようとしています。テーブルのdouble型のカラムに対してインデックス作成コマンドを入力しましたが(create index)、数日経ってもまだインデックス作成が終わりません。長すぎて何か問題でも起きているのでは、と思ってのですが同様の形式のサイズの小さいデータベースに対して同様の処理を行うと問題なく終わります。
なんらかの方法でインデックス作成のスピードを上げることは可能でしょうか?たとえばint型のデータ型に変換するとスピードが向上するなどはあるでしょうか?
No.2ベストアンサー
- 回答日時:
「インデックス作成のスピード」に最も影響を与えるパラメータは「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
- 回答日時:
#3です。
>double型を範囲指定して(例えば 90.1< x < 90.3)該当するデータ
浮動小数点のデータ型では、小数点以下の精度は厳密に保証されませんが、その辺は大丈夫でしょうか?
インデクス作成時、1000億件のソートを行うことになり、MySQLサーバのパラメタでの作業領域増といったレベルでは対処しようがないと個人的には思っています。
もし、試行錯誤されるのでしたら、どういう設定をしてどのように状況が変わったといったことを、参考までに知らせてもらえるとありがたいです。
色々ネットをしらべて以下のページにたどり着きました。
http://serverfault.com/questions/140488/mysql-cr …
このページで言われているようにパーティションを40ほどに分け、double型をint型に変更して(データの仕様を変えて)パーティションに対応させたうえでテーブルを作り直しました。
パーティション作成前は1週間かかってもインデックスを作り終えることができませんでしたが、作成後は2時間ほどでインデックス作成が終了しました。
今回の件ではいろいろ勉強になりました。
No.3
- 回答日時:
>かなり大きいデータ数(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
- 回答日時:
億単位になると小手先のごまかしではパフォーマンス改善は期待できないんじゃ
ないですかねぇ・・・
CPU・メモリ・OS含むファイルシステムなんかも絡めてカリカリに
チューニングしていかないと難しいかと
MySQLもどのソリューションを利用しているかわかりませんが
どうしてもその処理が必要ならITコンサルを絡めて商用ベースの特別な
システムを組むことを検討する段階かもしれません。
あとはある程度のスパンで予め最適化した集計済みのデータを作成しておいて、
不要な検索を避けるようにするとか運用上の工夫も少なからず必要です。
会社ではなく個人規模での作業のためのデータベースなのでコンサルタントや商用ベースのシステムというのは厳しいですね。。
正直データベース運用というものを理解しないままいきなり大量のデータを扱いはじめたので右も左も分からない状態でした。
実際の運用は大変なものなのですね。ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・14歳の自分に衝撃の事実を告げてください
- ・架空の映画のネタバレレビュー
- ・「お昼の放送」の思い出
- ・昨日見た夢を教えて下さい
- ・ちょっと先の未来クイズ第4問
- ・【大喜利】【投稿~10/21(月)】買ったばかりの自転車を分解してひと言
- ・メモのコツを教えてください!
- ・CDの保有枚数を教えてください
- ・ホテルを選ぶとき、これだけは譲れない条件TOP3は?
- ・家・車以外で、人生で一番奮発した買い物
- ・人生最悪の忘れ物
- ・【コナン30周年】嘘でしょ!?と思った○○周年を教えて【ハルヒ20周年】
- ・ハマっている「お菓子」を教えて!
- ・最近、いつ泣きましたか?
- ・夏が終わったと感じる瞬間って、どんな時?
- ・10秒目をつむったら…
- ・人生のプチ美学を教えてください!!
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・都道府県穴埋めゲーム
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ファイルの漢数字の順番につい...
-
並べ替えについて
-
accessでDISTINCT 句と矛盾
-
テーブルレーコードをソートし...
-
文字型の順番がうまく並ばない。
-
OracleとAccessの出力順の違い
-
Selectした時のレコードの取得順
-
上から何番目か。
-
エクセル、並び替え正しくソー...
-
帳票フォームの行の順番を簡単...
-
エクセルのソート方法
-
都道府県順、北からソートした...
-
ソートについて
-
SELECT文のソート条件
-
ハイフンの入ったデータの並べ替え
-
MSDOS ソート sortf 使用方法
-
Accessのテーブルの変化を自動...
-
レコードの登録順がおかしい
-
コンボボックスのソートについて
-
エクセルの縦列のソートできま...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイルの漢数字の順番につい...
-
並べ替えについて
-
エクセル、並び替え正しくソー...
-
レコードの登録順がおかしい
-
テーブルレーコードをソートし...
-
月末日のみソートしたい
-
1の行を固定した上でVBAを用い...
-
ハイフンの入ったデータの並べ替え
-
コンボボックスのソートについて
-
SQLのORDERBY句について質問です。
-
エクセルの縦列のソートできま...
-
上から何番目か。
-
OracleとAccessの出力順の違い
-
アクセスに関して。クエリの並...
-
タイトル行を固定してソートしたい
-
Selectした時のレコードの取得順
-
accessでDISTINCT 句と矛盾
-
数字と漢字が混じった日付デー...
-
SQLで曜日のソートを月火水木金...
-
WORDの「並べ替え」について
おすすめ情報