大きいデータ数のテーブルに対するインデックス作成
- mySQL server 5.1 でのindex作成について質問です
- かなり大きいデータ数(1000億)のテーブルを扱う必要があり検索速度向上のためにindexを作成しようとしています
- インデックス作成のスピードを上げる方法はありますか?int型に変換するとスピードが向上するでしょうか?
- ベストアンサー
大きいデータ数のテーブルに対するインデックス作成
mySQL server 5.1 でのindex作成について質問です かなり大きいデータ数(1000億)のテーブルを扱う必要があり検索速度向上のためにindexを作成しようとしています。テーブルのdouble型のカラムに対してインデックス作成コマンドを入力しましたが(create index)、数日経ってもまだインデックス作成が終わりません。長すぎて何か問題でも起きているのでは、と思ってのですが同様の形式のサイズの小さいデータベースに対して同様の処理を行うと問題なく終わります。 なんらかの方法でインデックス作成のスピードを上げることは可能でしょうか?たとえばint型のデータ型に変換するとスピードが向上するなどはあるでしょうか?
- hydrozoa
- お礼率78% (57/73)
- MySQL
- 回答数4
- ありがとう数4
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
「インデックス作成のスピード」に最も影響を与えるパラメータは「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の制限にも気をつけなければなりません。最悪クラッシュします。 メモリ配分にはよく注意してください。
その他の回答 (3)
- chukenkenkou
- ベストアンサー率43% (833/1926)
#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時間ほどでインデックス作成が終了しました。 今回の件ではいろいろ勉強になりました。
- chukenkenkou
- ベストアンサー率43% (833/1926)
>かなり大きいデータ数(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)該当するデータを取り出す、という形です。データベース運用自体ほぼ初めてなので軽い気持ちではじめてしまって、実際は複雑な操作が必要なようでやや戸惑っています。 検索の仕方の具体的なアドバイスありがとうございます。
- yambejp
- ベストアンサー率51% (3827/7415)
億単位になると小手先のごまかしではパフォーマンス改善は期待できないんじゃ ないですかねぇ・・・ CPU・メモリ・OS含むファイルシステムなんかも絡めてカリカリに チューニングしていかないと難しいかと MySQLもどのソリューションを利用しているかわかりませんが どうしてもその処理が必要ならITコンサルを絡めて商用ベースの特別な システムを組むことを検討する段階かもしれません。 あとはある程度のスパンで予め最適化した集計済みのデータを作成しておいて、 不要な検索を避けるようにするとか運用上の工夫も少なからず必要です。
お礼
会社ではなく個人規模での作業のためのデータベースなのでコンサルタントや商用ベースのシステムというのは厳しいですね。。 正直データベース運用というものを理解しないままいきなり大量のデータを扱いはじめたので右も左も分からない状態でした。 実際の運用は大変なものなのですね。ありがとうございました。
関連するQ&A
- インデックスを張るべき項目について
20万件レコードのあるテーブルに、インデックスを張ると INSERTが遅くなるので、WHERE句で検索する項目のどれに インデックスを張るか悩んでいます。 インデックスはパターンが多い程、張った場合に 検索速度が向上すると理解しているのですが正しいでしょうか? であれば、下記1.だけは貼ろうと思っているのですが・・ 1.カラムに入るデータが殆どバラバラのVARCHAR(30) 2.カラムに入るデータは10万パターンのINT型 3.カラムに入るデータは1万パターンのINT型 4.カラムに入るデータはdatetime型 インデックスを張る事でINSERT速度が何%ぐらい下がるでしょうか? よろしくお願いします。
- ベストアンサー
- MySQL
- 複数テーブルとIndex
windows+mysql(4.0.xx)でブログのようなサイトを自宅で立ち上げています。 現在、一人ブログに登録すると、その人専用のテーブルを作成するという形にしています。 人が増えるたびに、同じ構成のテーブルが増えていくわけですが、以前100人前後の利用者だったときに速度を測定してみたところ、1テーブルに全員のデータを記録しIndexを張った場合より、別々のテーブルを作成したほうが高速だったために、上記のような冗長構成としました。 今現在、少し人が増えて1000人前後で、特に速度的にも問題はないのですが、これが1万人、10万人と増えた場合に同じような結果となるのか不安があります。(.frmファイルが大量に増えているので・・・) そこで 1)現在と同じように一人一テーブルとする。 2)全員で一テーブルとする。 のどちらが良いかアドバイスを皆様からいただきたいのです。 冗長性や正規化という観点よりも、検索速度の点からアドバイスいただければ幸いです。 (おそらく、NTFSのB+木検索と、MySQLのインデックス検索のどちらが早いかという問題になると思うのですが、データベース自体に疎く、以前、自分で出した結論(B+木のほうが優秀)にあまり自信がありません。) 以上、よろしくお願いします。
- 締切済み
- MySQL
- UNIQUEをつけたときのINDEXテーブル
テーブル作成時、カラムにUNIQUEをつけると自動でインデックステーブルが作成されますが、このインデックステーブルで、通常のselect検索も早くなるのでしょうか? 具体的には、ユニークなカラムidを持つテーブルtableA create table tableA( id INTEGER UNIQUE, some_column INTEGER ); を作ったあとで、そのidにあらためて CREATE INDEX id ON tableA (id); と、インデックスを張る必要があるのでしょうか。 よろしくお願いします。
- ベストアンサー
- PostgreSQL
- Ruby on Rails によるデータベース作成
Rubyもよくわからない初心者ですが、「かんたんRuby on RailsでWebアプリケーション開発」という本を見ながらデータベースを作成しています。Railsのバージョンは1.1.2、データベース管理はMySQLを使っています。 マイグレーションの作成まではうまく行っていると思うのですが、001_create_table.rbの修正、database.ymlの設定を行い、マイグレーション、scaffoldを実行してブラウザで表示すると、表示されないフィールドがあります。 001_create_table.rbでは次の部分を追加しています。 create_table(:items) do |table| table.column :index, :string, :limit => 32 table.column :pos, :string, :limit => 8 table.column :cid, :string, :limit => 32 table.column :type, :string, :limit => 5 table.column :flq, :integer table.column :ns, :string, :limit => 6 table.column :cflg, :integer table.column :wflg, :integer end 何回かやり直してみましたが、↑この中のtypeというフィールドがどうしても表示されません。 どこを直したらいいのか教えてください。 よろしくお願いします。
- ベストアンサー
- その他(プログラミング・開発)
- データベースとテーブルを作成する基準について
独学でMySQLを勉強しているのですが、さまざまな種類のデータが大量にある場合、どのような基準でデータベースやテーブルを分けるのでしょうか? 例えばOKWaveの場合、ユーザーデータを入れるDBとQ&AをいれるDBをわけるのか、それともひとつのDBに別のテーブルとしていれるのか。 掲示板の場合、一つのスレッドでテーブルを一つ作るのか、それとも全てのスレッドを一つのテーブルにいれインデックスをつけて使うのか。 これらのことが使いやすさや拡張性の問題であるのか、速度などに関係するのかわかりません。 データの種類や構成で一概に言えることではないかもしれませんが何か基準を知りたいのでよろしくお願いします。
- ベストアンサー
- MySQL
- postgres table作成時にindex付与
MySQLでは以下のようにテーブル作成時にインデックス付与ができます。 CREATE TABLE test ( a int(11) NOT NULL DEFAULT '0', b int(11) DEFAULT NULL, PRIMARY KEY (a), KEY test_idx (b), -- ★インデックス その1 KEY test_idx2 (a) -- ★インデックス その2 ); ポスグレではできませんか?
- 締切済み
- その他(データベース)
- テーブル作成でカラム名で配列を
MySQLを勉強している初心者です、よろしくお願いします。 テーブル作成時、カラム名を配列を使ってできないものでしょうか? (data1 data2 data3 data4 とするのが面倒なのですが) CREATE TABLE table_name ( code int(6) , data1[100] int(10) , data2[50] varchar(20) , data3[20] varchar(25)); ERROR 1064:You have an error in your SQL syntax. となります。 また二次元配列はどのようにしたらよいのでしょうか? よろしくお願いします。
- ベストアンサー
- その他(データベース)
- インデックスについて
宜しくお願いします。 MySQLで検索速度を上げる為にカラム毎に設定するINDEXですが、 fulltextとindexの2種を使えば、全カラムにINDEXを付けられると思いますが、検索するか分からないカラムにもINDEXをつけた場合、何か不都合がおこりますか? 例えば、INDEXが多すぎると逆に検索が遅くなるなどです。 ちなみに、fulltextは日本語に対応していないのは理解しております。
- ベストアンサー
- MySQL
- PHPでmySQLのテーブルを作成したい
今PHPの練習をしているのですが、作ろうとしているPHPの、 0:$tbname="データベース名"、とする。 1:データベースにアクセスする 2:アクセスしたデータベースに$tbnameと同じ名前のテーブルがあるかないかをチェック 3:同じ名前のテーブルがあるならそこで終了 4:同じ名前のテーブルがないなら、$tbnameをテーブル名、ID(INT)とNAME(CHAR)をカラム名とするテーブルを新規に作成する という動作をする部分のコードの書き方(どのようなSQL文を使えばいいのか)がわかりません。 どなたかご教示のほど、どうかよろしくお願いします。
- ベストアンサー
- PHP
お礼
とてもためになるアドバイスありがとうございます。まさにこのようなことが知りたかったのです。SORT_BUFFER_SIZEはデフォルトではかなり低く設定されているようなので、メモリサイズの制限に気をつけつつ設定をあげてみようと思います。