MySQLやSQliteの中のデータ検索速度について

質問

ざっくりとした回答で結構ですので、データベース言語のバージョンについては、あえて記載を省きます。
環境：Apatch、Linux、PHP4、SQlite2

バージョンによって動作が異なるのであれば、その場合には適宜補足します。
なお、私はデータベース言語の知識をそれほど持っていませんので、分かりやすく記述して頂けると大変助かります。

---------------------------
では、本題に移らせて頂きます。

現在、PHP4とSQlite2の組み合わせでスクリプトを制作中なのですが、
その際に、データベース（以下、DB）の中のデータを検索・抽出することがあります。
SQliteのコマンドでは、SELECT文を使った記述になります。
（恐らく、MySQLなどでも同様だと思います。）

で、知りたいことは、
DBをどう構築したら、「検索・抽出」速度が遅くなりにくいか（高速になりやすいか）。
これが知りたいわけです。

私が考えている方法は、いくつかあって、それらについて、
速い遅いの意見を覗いたいと思います。
また、もっと良い方法がありましたら、そちらも教えて下さい。

想定しているDBは、例として、簡単に以下のものとします。
例：学年の生徒DB
クラスがA組～E組まで５クラスあります。
生徒の、
氏名、性別、クラス、出席番号、生年月日、所属している部活名、担当委員、欠席数
のようなものを記録するDBだと思って下さい。

さて、この例において、以下のどの形式が良さそうでしょうか。

方法１：
（DBファイルを１つ用意）
DBをCREATEするときに、TABLE名を　tbl_all という風に１学年全体で記録するような形式。

方法２：
（DBファイルを１つ用意）
DBをCREATEするときに、TABLE名を　tbl_a　～　tbl_e という風に分け、それぞれのテーブルに各クラスの生徒データを記録するような形式。

方法３：
（DBファイルを複数用意）
DBをCREATEするときに、クラスごとに、DBファイルを「db_a.sqlite2」～「db_e.sqlite2」のように作成し、それぞれにおいて、TABLEを１つ作り、そこにクラス内の生徒のデータを記録する形式。

どの形式のDBが、速く（もちろん正確に）検索・抽出（SELECT）できるでしょうか。

一般論でも結構です。

また、データ数が多くなるにつれ、速度が逆転するというような場合には、そちらも教えて下さい。

この例では、クラス数が５なので、比較的データは少ないですが、
私が構築しようとしているDBでは、データを１万件以上扱う可能性が高いので、その点も考慮して、アドバイスして頂けると嬉しいです。

以上、よろしくお願い致します。

回答No.2

notnot · ベストアンサー

ご存じかもしれませんが、よく使う項目でindexを作っておくと検索は速いです。indexをたくさん作りすぎると更新が遅くなりますが。

データが1万件程度なのだったら、あまり速度を気にしなくてもいいと思いますよ。

一般に、テーブルを分割するのは、1秒間に何十件とか何百件の参照・更新処理を行うときに、テーブルを分けて別の物理ディスクに配置するとディスク入出力を独立して行えるので、処理時間が短くて済むという理由です。
あと、sqliteの場合は排他がDB単位でかかるので、DBを分けると言うことも行うようです。

質問に書かれてませんが、おそらく並列処理しないか、しても2-3個くらいだと思うので、1テーブルにまとめる方がいいと思います。

お礼

ありがとうございました（＾＾）
また何かありましたら、教えて下さいね。

補足

＞ご存じかもしれませんが、よく使う項目でindexを作っておくと検索は速いです。

いえ、全く、ご存じではありませんでした（笑）。

indexという概念について、私には全く知識がないので、
それが何であるか、調べてみますね。

＞データが1万件程度なのだったら、あまり速度を気にしなくてもいい

具体的なアドバイスをありがとうございます。

＞テーブルを分けて別の物理ディスクに配置するとディスク入出力を独立して行えるので、処理時間が短くて済む

つまり、テーブルを分けると、場合によっては検索速度が上がるという解釈で問題なさそうですか？

＞sqliteの場合は排他がDB単位でかかるので、DBを分けると言うことも行うようです。

「排他」という概念についても、同様に調べようと思います（笑）。

＞おそらく並列処理しないか、しても2-3個くらいだと思うので、1テーブルにまとめる方がい

並列処理とは、どういうことを言うのでしょうか。
すみません、本当に、何も知らなくて。。

PHPコードは基本的に上から処理されると思いますが、
並列に処理されるケースというのは、どういうコードの場合なのでしょうか。

例えば、
DBのSELECT文をPHPスクリプト内に２種類続けて書いた場合、
メカの方では、最初のSELECT処理をしつつ、次のSELECT処理も同時並行的に行っていたりするのでしょうか？

もし、そういうことを並列処理と言うのであれば、
並列処理を３個くらいすることもあり得ます。

もっとも、３個程度ならば、回答の中にありますように、１テーブルで問題なさそうですね。

示唆に富む回答をありがとうございました。
また何かありましたら、教えて下さい。

回答No.4

yambejp

個人情報を管理するだけであれば、SQLよりもむしろLDAP的な処理の
方が管理しやすいかもしれないですね。

今回の例だと欠席数を集計するところは意味があります。
もう少し発展的にたとえば成績を管理集計したりするのであれば
より効果が高いです。

個人情報のテーブルについては
→氏名、性別、生年月日、
など普遍的なものに関しては、一つのテーブルにまとめた方が
効率的です。
→クラス、出席番号、所属している部活名、担当委員
など、学年が変わったり、なんらかの理由で変わる可能性が高いものは
正規化しておいた方がよいでしょう。
→欠席数
など、時系列なデータにリンクするものは専用の管理テーブルを
つかって集計する必要があります。

お礼

ありがとうございました（＾＾）
また何かありましたら、教えて下さいね。

補足

アドバイスありがとうございます。

＞SQLよりもむしろLDAP的な処理の方が管理しやすいかも

LDAP・・・、初めて聞く言葉でした。＾＾；
そして、ザッと調べてみましたが、すぐに理解できるような代物ではない気がして、今のところ保留にしています。

＞普遍的なものに関しては、一つのテーブルにまとめた方が効率的

普遍的要素テーブルを１つ用意すると良い、ということですね。

＞なんらかの理由で変わる可能性が高いものは正規化しておいた方がよい

正規化、つまり、1つのフィールドに情報を盛りだくさんに詰め込まないようにした方がいい、ということですね。

＞時系列なデータにリンクするものは専用の管理テーブルをつかって集計

時系列要素テーブルを１つ用意すると良い、ということですね。

■以上をまとめると、本問の例で、検索速度を上げるには、
テーブルを少なくとも３つ以上は作った方が良いということになりますか？
（「→」の数が３つありましたので…。笑）

よって、私が書いた「方法２」に近い形がオススメということになるのでしょうか。
そもそも、「LDAP的な処理」というものが理解できていない私には、今回アドバイス頂いた内容は参考にならないのでしょうか？＾＾；

自分が理解できているのか、いないのか、分からずに居ます…。笑
すみません、あやふやな補足になりました。苦笑

回答No.3

notnot

#2です。
＞並列処理とは、どういうことを言うのでしょうか。

AさんがそのDBにせっせとデータ入力をしている横で、BさんがDB全体の集計をしているようなものです。Bさんは集計するたびに数字が違うので混乱しています。

別の例で言うと、X銀行のYさんの口座に10,000円残高があったとして、YさんがATMで1,000円下ろしたとします。その処理で、10000を読み込んで1000を引いてと処理している瞬間に、Zさんから5,000円振り込まれたとすると、残高はどうなるでしょう？というのが排他が必要となるケースです。

並行処理をしないのなら、排他も要りません。

お礼

ありがとうございました（＾＾）
また何かありましたら、教えて下さいね。

補足

並列処理について、大変よく分かりました！

トランザクションに関係するお話なのですね。（たぶん、そうですよね。笑）

具体例を二つもあげて頂いて、親切にどうもありがとうございます。
１つ目の例で完璧に分かりましたが、２つ目を読み、人間愛を感じました。照

話を戻しますと、
私のスクリプトにおいては、並列処理は、、、、ない、と思っていましたが、よく考えてみましたら、

私の作成中のスクリプト群において、データ挿入（INSERT）するスクリプトと、検索抽出（SELECT）するスクリプトが同時期にアクセスされることは充分あり得ますので、

この場合、並列処理が行われる可能性があるということですから、排他についても、適宜考える必要があるということなのでしょうね。
（並列処理が２～３個程度なら、考えなくても良いかなと、というお話でしたね。前レスを振り返り…。）

ちなみに、銀行の残高ほど情報の正確性を求めているようなスクリプトではないので、その場合には、たとえ並列処理が行われる可能性があったとしても、排他を考える必要はなさそうですよね？
（と、他人様に聞いてどうなるんだ。笑）
また、本題からそれているので、これ以上、深追いはしないでおきますね。笑

今回も、適切なアドバイスをありがとうございました。
助かりました。

回答No.1

wildcat-yp

SQLiteはあまり使ったことがないので一般的なSQLサーバの話ですが、
もしテーブルが一つしかなく、検索の際にクラスをまたがった検索を行わないのであれば、クラスごとにテーブルを分けるのがいいでしょう。
もし、UNION等で連結させる必要があったり、別々に検索して後から結合するようであれば、初めから一つのテーブルにした方が良いと思います。頻度にもよりますが。

そのほかは、詳細なテーブル構成や使用する検索条件によって変わります。

一般的にはテーブルを分けたりすると検索は遅くなります。
正規化しても遅くなります。
ただ、正規化していないデータは管理が大変だったり、データの更新が遅くなったり、ディスクの容量がかさんでしまいますので、バランスが大事です。
後はIndexの作り方です。

Microsoftのツールなんかの場合は、データの更新は完全に正規化されたテーブルに更新し、更新が完了したらトリガー等で正規化されたテーブルからビューテーブルという検索専用に非正規化されたテーブルを更新して、検索作業はそこから行うようになっています。MS-ProjectServer等です。

後はDBはチューニングによっても大きく性能が左右されます。SQLiteがどれほどチューニングが可能かわかりませんが・・・

お礼

ありがとうございました（＾＾）
また何かありましたら、教えて下さいね。

補足

早速のアドバイス、ありがとうございます。

＞もし、UNION等で連結させる必要があったり、別々に検索して後から結合するようであれば、初めから一つのテーブルにした方が良いと思います。頻度にもよりますが。

テーブルを小分けしたほうが、検索するエリアを予め絞り込めるので、その方が高速な検索ができるものと思っていますが、それ自体、間違っているのでしょうか？
と、疑問に思った所、
頂いたアドバイスの中に、
＞　一般的にはテーブルを分けたりすると検索は遅くなります。
という一文を発見。

じゃぁ、テーブルを分ける意味って、何なのだろう。。。
という疑問がわいてきました。
（私が、その程度のDB利用者という意味でもあります。汗）

＞そのほかは、詳細なテーブル構成や使用する検索条件によって変わります。

ふむ。。。なるほどぉ。

＞正規化しても遅くなります。

1つのカラムに盛りだくさんの情報を入れた方が、管理が大変になったり等の不便な面はありますけども、検索自体は速くなりやすいわけですね。

これは、テーブルを複数つくらず、1つのテーブルに盛りだくさんの情報を入れる方が、検索速度が高速になりやすいという話と似ていますね。

なんとなく、DBの特製が分かってきました。

データを小分けしたほうが、逆に速度は遅くなり、
逆に、1つの箱にデータを盛りだくさん詰め込んだ方が、一般的に検索速度は上がりやすいんですね。

ただ、データの更新の処理速度は遅くなるようですね。
（「データの更新が遅くなったり」というご意見を受けて。）

＞DBはチューニングによっても大きく性能が左右されます

この辺は、まだ私には難しい話になりますが、検索速度に大きく影響するようであれば、これから勉強したいと思います。