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

Question

ざっくりとした回答で結構ですので、データベース言語のバージョンについては、あえて記載を省きます。
環境：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では、データを１万件以上扱う可能性が高いので、その点も考慮して、アドバイスして頂けると嬉しいです。

以上、よろしくお願い致します。

notnot · Accepted Answer

ご存じかもしれませんが、よく使う項目でindexを作っておくと検索は速いです。indexをたくさん作りすぎると更新が遅くなりますが。

データが1万件程度なのだったら、あまり速度を気にしなくてもいいと思いますよ。

一般に、テーブルを分割するのは、1秒間に何十件とか何百件の参照・更新処理を行うときに、テーブルを分けて別の物理ディスクに配置するとディスク入出力を独立して行えるので、処理時間が短くて済むという理由です。
あと、sqliteの場合は排他がDB単位でかかるので、DBを分けると言うことも行うようです。

質問に書かれてませんが、おそらく並列処理しないか、しても2-3個くらいだと思うので、1テーブルにまとめる方がいいと思います。

yambejp · Answer

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

notnot · Answer

#2です。
＞並列処理とは、どういうことを言うのでしょうか。

AさんがそのDBにせっせとデータ入力をしている横で、BさんがDB全体の集計をしているようなものです。Bさんは集計するたびに数字が違うので混乱しています。

別の例で言うと、X銀行のYさんの口座に10,000円残高があったとして、YさんがATMで1,000円下ろしたとします。その処理で、10000を読み込んで1000を引いてと処理している瞬間に、Zさんから5,000円振り込まれたとすると、残高はどうなるでしょう？というのが排他が必要となるケースです。

並行処理をしないのなら、排他も要りません。

wildcat-yp · Answer

SQLiteはあまり使ったことがないので一般的なSQLサーバの話ですが、
もしテーブルが一つしかなく、検索の際にクラスをまたがった検索を行わないのであれば、クラスごとにテーブルを分けるのがいいでしょう。
もし、UNION等で連結させる必要があったり、別々に検索して後から結合するようであれば、初めから一つのテーブルにした方が良いと思います。頻度にもよりますが。

そのほかは、詳細なテーブル構成や使用する検索条件によって変わります。

一般的にはテーブルを分けたりすると検索は遅くなります。
正規化しても遅くなります。
ただ、正規化していないデータは管理が大変だったり、データの更新が遅くなったり、ディスクの容量がかさんでしまいますので、バランスが大事です。
後はIndexの作り方です。

Microsoftのツールなんかの場合は、データの更新は完全に正規化されたテーブルに更新し、更新が完了したらトリガー等で正規化されたテーブルからビューテーブルという検索専用に非正規化されたテーブルを更新して、検索作業はそこから行うようになっています。MS-ProjectServer等です。

後はDBはチューニングによっても大きく性能が左右されます。SQLiteがどれほどチューニングが可能かわかりませんが・・・

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

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

この回答への補足

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

この回答への補足

#2です。

この回答への補足

SQLiteはあまり使ったことがないので一般的なSQLサーバの話ですが、

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング