![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
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で質問しましょう!
似たような質問が見つかりました
- Ruby pandasでsqlite3にテーブル作成・追加・読み出しでindexの取り扱い方教えてください 5 2023/03/08 09:57
- その他(データベース) pythonでsqlight勉強中、クエリー結果の利用法教えて下さい 1 2022/04/28 20:38
- MySQL MySQLのテーブル作成で 自信がありません。 2 2022/08/28 05:35
- MySQL PHPとMySQLを使った掲示板の作り方 1 2022/06/02 13:00
- Excel(エクセル) CSVファイルがカンマ区切りにならない。対処法を教えていただきたいです。 仕事でSMS一斉送信ができ 2 2022/07/01 21:24
- システム 帳票出力を行う単体テストのテストデータが作成できません 2 2023/08/26 21:26
- IT・エンジニアリング 帳票出力を行う単体テストのテストデータが作成できません 2 2023/08/26 21:25
- 画像編集・動画編集・音楽編集 動画をディスク作成すると画像が乱れる。 4 2022/06/24 07:49
- Visual Basic(VBA) EXCEL VBAにて動的にCheckBOXを複数作成し、同BOXにイベントを追加したい 1 2023/03/16 07:05
- MySQL 【投稿情報用データベース posts】は必要ないと思います。 1 2022/06/02 21:25
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
並べ替えについて
-
ソート(PL/SQL)
-
ファイルの漢数字の順番につい...
-
1の行を固定した上でVBAを用い...
-
テーブルレーコードをソートし...
-
エクセル、並び替え正しくソー...
-
レコードの登録順がおかしい
-
上から何番目か。
-
Excelのオートフィルタでソート...
-
リストボックス内を昇順並べる方法
-
EXCELのデータ並び替え
-
都道府県順、北からソートした...
-
SELECT文のソート条件
-
WORDの「並べ替え」について
-
エクセルの縦列のソートできま...
-
複数のセルを並べ替え
-
エクセルのソートについて
-
Selectした時のレコードの取得順
-
エクセルでのソートについて
-
月末日のみソートしたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
レコードの登録順がおかしい
-
並べ替えについて
-
1の行を固定した上でVBAを用い...
-
ファイルの漢数字の順番につい...
-
テーブルレーコードをソートし...
-
エクセル、並び替え正しくソー...
-
SQLで曜日のソートを月火水木金...
-
月末日のみソートしたい
-
ハイフンの入ったデータの並べ替え
-
リストボックス内を昇順並べる方法
-
Selectした時のレコードの取得順
-
アクセスに関して。クエリの並...
-
accessでDISTINCT 句と矛盾
-
都道府県順、北からソートした...
-
コンボボックスのソートについて
-
SQLデータベースの処理
-
上から何番目か。
-
文字型の順番がうまく並ばない。
-
ファイルメーカーでソート後の...
-
Excelの並び替え(文字数と画数...
おすすめ情報