sql - 種類 - データベース インデックス 設計

ほんのちょっとした提案です。インデックス作成には追加の書き込みと記憶領域が必要になるので、アプリケーションで追加の更新操作が必要な場合はインデックスなしのテーブルを使用することをお勧めします。表。

データベース索引を本の索引と考えてください。

あなたが犬についての本を持っていて、あなたが言おう、例えば、ジャーマン・シェパードについての情報を見つけたいと思うなら、あなたはもちろん本のすべてのページをめくってあなたが探しているものを見つけることができます - しかし、もちろんこれは時間がかかりませんとても早い。

もう1つの選択肢は、本のIndexセクションに移動してから、探しているエンティティの名前（この例ではGerman Shepherds）を使用してページ番号を調べて探しているものを見つけることです。すぐにあなたが探しているものを見つけます。

データベースでは、ページ番号は、エンティティが配置されているディスク上のアドレスにデータベースを転送するポインタとして参照されます。 同じジャーマンシェパードのアナロジーを使用して、次のようなものを作成できます（“ German Shepherd”、0x77129）。ここで、 0x77129 は、ジャーマンシェパードの行データが格納されているディスク上のアドレスです。

つまり、インデックスは、クエリ検索を高速化するためにテーブル内の特定の列の値を格納するデータ構造です。

古典的な例 "本の中の索引"

1000ページの「本」を100セクションに分けて考えます。各セクションはXページです。

簡単ですね。

さて、インデックスページがなければ、文字 "S"で始まる特定のセクションを見つけるために、本全体をスキャンする以外に選択肢はありません。 例：1000ページ

しかし最初にインデックスページがあれば、あなたはそこにいます。 さらに、重要な特定のセクションを読むためには、インデックスページを何度も見直す必要があります。 一致するインデックスを見つけたら、他のセクションをスキップしてそのセクションに効率的にジャンプできます。

しかし、その場合、1000ページに加えて、インデックスページを表示するにはさらに10ページ、つまり合計1010ページが必要になります。

したがって、インデックスは、効率的なルックアップのために、ソートされた順序でインデックス付き列+インデックス付き行へのポインタの値を格納する独立したセクションです。

学校では物事は簡単ですね。 ：P

簡単な説明!!!!!!!!!!

インデックスは、テーブル内の特定の列の値を格納するデータ構造に他なりません。 索引は表の列に作成されます。

たとえば、名前、年齢、住所の3つの列を持つユーザーというデータベーステーブルがあります。 Userテーブルに何千もの行があるとします。

それでは、 'John'という名前のユーザーの詳細をすべて検索するためのクエリを実行したいとします。 次のクエリを実行したとします。

SELECT * FROM User 
WHERE Name = 'John'

データベースソフトウェアは、その行の名前が 'John'であるかどうかを確認するために、文字通りUserテーブルのすべての行を調べなければなりません。 これは長い時間がかかります。
これが、インデックスを使用して「調べる必要があるテーブル内のレコード数/行数を実質的に削減することで、検索クエリを高速化するために使用される」のに役立ちます。
インデックスを作成する方法

CREATE INDEX name_index
ON User (Name)

インデックスは、1つのテーブルからの列値（例：John）で構成され、それらの値はデータ構造に格納されています。
インデックスはおそらくユーザー名のアルファベット順にソートされるため、データベースはJohnという名前の従業員を検索するためにインデックスを使用します。 また、ソートされているため、「J」で始まる名前はすべてインデックス内で互いに隣接しているため、名前の検索がはるかに高速になります。

なぜそれが必要なのですか？

データがディスクベースのストレージデバイスに格納されると、データブロックとして格納されます。 これらのブロック全体がアクセスされるため、アトミックディスクアクセス操作になります。 ディスクブロックは、リンクリストとほぼ同じ構造になっています。 どちらもデータのセクション、次のノード（またはブロック）の位置へのポインタを含み、両方を連続して格納する必要はありません。

多くのレコードは1つのフィールドでしかソートできないため、ソートされていないフィールドでの検索には（平均して） N/2 ブロックアクセスを必要とする線形検索が必要になります。テーブルがまたがるブロックの数。 そのフィールドが非キーフィールドの場合（つまり、一意のエントリが含まれていない場合）、 N ブロックアクセスでテーブルスペース全体を検索する必要があります。

ソートされたフィールドでは、 log2 N ブロックアクセスを持つバイナリサーチが使用されるかもしれません。 また、データはキー以外のフィールドを指定してソートされるため、テーブルの残りの部分では、高い値が見つかったら重複した値を検索する必要はありません。 したがって、パフォーマンスの向上はかなりのものです。

インデックスとは何ですか？

索引付けは、複数のフィールドで多数のレコードをソートする方法です。 テーブル内のフィールドにインデックスを作成すると、フィールド値とそれが関連するレコードへのポインタを保持する別のデータ構造が作成されます。 その後、このインデックス構造はソートされ、バイナリサーチを実行することができます。

インデックス作成のマイナス面は、MyISAMエンジンを使用してインデックスがテーブルにまとめて格納されるため、これらのインデックスはディスク上に追加のスペースを必要とすることです。 。

それはどのように機能しますか？

まず、サンプルのデータベーステーブルスキーマの概要を説明しましょう。

Field name       Data type      Size on disk
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes

注 ：ディスク値の正確なサイズを考慮して、varcharの代わりにcharが使用されました。 このサンプルデータベースには500万行含まれており、インデックスは付けられていません。 いくつかのクエリのパフォーマンスが分析されます。 これらは、 id （ソートされたキーフィールド）を使用したクエリと firstName （キーでソートされていないフィールド）を使用したクエリです。

例1 - ソート済みフィールドと未ソートフィールド

R = 204 バイトのレコード長を与える、固定サイズの r = 5,000,000 レコードのサンプルデータベースがあるとします。これらのデータベースは、デフォルトのブロックサイズ B = 1,024 バイトを使用しているMyISAMエンジンを使用してテーブルに格納されます。 テーブルのブロック化因数は、ディスクブロックあたり bfr = (B/R) = 1024/204 = 5 レコードです。 テーブルを保持するのに必要なブロックの総数は、 N = (r/bfr) = 5000000/5 = 1,000,000 ブロックです。

idフィールドをキーフィールドとすると、idフィールドを線形検索するには、値を見つけるために平均 N/2 = 500,000 ブロックアクセスが必要です。 しかし、idフィールドもソートされているので、平均 log2 1000000 = 19.93 = 20 ブロックアクセスを必要とする二分探索を実行することができます。 即座にこれが劇的な改善であることがわかります。

firstName フィールドはソートもキーフィールドもされていないため、バイナリ検索は不可能であり、値も一意ではありません。したがって、テーブルでは正確な N = 1,000,000 ブロックアクセスを最後まで検索する必要があります。 索引付けが修正を目的としているのは、このような状況です。

索引レコードに索引付きフィールドと元のレコードへのポインターのみが含まれていると仮定すると、それは、それが指している複数フィールド・レコードよりも小さくなるのは当然のことです。 そのため、インデックス自体は元のテーブルよりも少ないディスクブロックしか必要としないため、繰り返し実行するのに必要なブロックアクセスは少なくなります。 firstName フィールドのインデックスのスキーマは以下の通りです。

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes

注 ：MySQLのポインタは、テーブルのサイズに応じて長さが2、3、4、または5バイトです。

例2 - 索引付け

R = 54 バイトのインデックスレコード長で、デフォルトのブロックサイズ B = 1,024 バイトを使用した、 r = 5,000,000 レコードのサンプルデータベースを考えます。 インデックスのブロック化因数は、ディスクブロックあたり bfr = (B/R) = 1024/54 = 18 レコードです。 インデックスを保持するために必要なブロックの総数は、 N = (r/bfr) = 5000000/18 = 277,778 ブロックです。

firstName フィールドを使用した検索では、インデックスを利用してパフォーマンスを向上させることができます。 これにより、平均 log2 277778 = 18.08 = 19 ブロックアクセスでインデックスのバイナリ検索が可能になります。 実際のレコードのアドレスを見つけるには、さらにブロックアクセスが必要です。合計で 19 + 1 = 20 ブロックアクセスになります。インデックスなしのテーブルで firstNameの 一致を見つけるのに必要な1,000,000ブロックアクセスからはほど遠いものです。 。

いつ使うべきですか？

インデックスを作成するには追加のディスク容量（上記の例より277,778ブロック増、最大28％の増加）が必要であり、インデックスが多すぎるとファイルシステムのサイズ制限から問題が生じる可能性があるため、正しいものを選択するために慎重な検討が必要です。索引付けするフィールド。

インデックスはレコード内の一致するフィールドの検索を高速化するためにのみ使用されるので、出力にのみ使用されるインデックスフィールドは単に挿入または削除操作を実行するときのディスク容量と処理時間の無駄になるため、避けるべきです。 バイナリ検索の性質を考えると、データの濃度または一意性も重要です。 カーディナリティが2のフィールドにインデックスを付けると、データが半分に分割されますが、カーディナリティが1,000の場合は約1,000レコードが返されます。 カーディナリティが低いと、有効性は線形ソートになり、カーディナリティがレコード数の30％未満の場合、クエリオプティマイザはインデックスの使用を回避し、インデックスをスペースの無駄にします。