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




データベースの索引付けはどのように機能しますか? (6)

それでは、 'Abc'という名前の従業員のすべての詳細を検索するためにクエリを実行したいとします。

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

インデックスがなければどうなりますか?

データベースソフトウェアは文字通り、その行のEmployee_Nameが 'Abc'であるかどうかを調べるためにEmployeeテーブルのすべての行を調べなければなりません。 そして、その中に 'Abc'という名前のすべての行が必要なので、 'Abc'という名前の行が1つだけ見つかったら、見なくてはなりません。Abcという名前の行が他にある可能性があります。 したがって、最後の行まですべての行を検索する必要があります。つまり、このシナリオでは、 'Abc'という名前の行を見つけるためにデータベースによって何千もの行を調べる必要があります。 これが フルテーブルスキャン と呼ばれるものです。

データベースインデックスがパフォーマンスにどのように役立つか

インデックスを持つことの全体的なポイントは、調査する必要があるテーブル内のレコード/行の数を本質的に減らすことによって検索クエリをスピードアップすることです。 インデックスは、テーブル内の特定の列の値を格納するデータ構造(最も一般的にはBツリー)です。

Bツリーインデックスはどのように機能しますか?

Bツリーがインデックスの最も一般的なデータ構造である理由は、それらが時間効率的であるという事実によるものです - 検索、削除、および挿入はすべて対数時間で行うことができるからです。 また、Bツリーがより一般的に使用されるもう1つの主な理由は、Bツリー内に格納されているデータをソートできるためです。 RDBMSは通常、どのデータ構造を実際に索引に使用するかを決定します。 しかし、特定のRDBMSのシナリオでは、索引自体を作成するときにデータベースに使用するデータ構造を実際に指定できます。

ハッシュテーブルインデックスはどのように機能しますか?

ハッシュインデックスが使用されるのは、ハッシュテーブルは値を調べるだけで非常に効率的だからです。 したがって、文字列と等しいかどうかを比較するクエリでは、ハッシュインデックスを使用すると非常に高速に値を取得できます。

たとえば、前に説明したクエリは、Employee_Name列に作成されたハッシュインデックスの恩恵を受けることができます。 ハッシュインデックスが機能する方法は、列の値がハッシュテーブルへのキーになり、そのキーにマッピングされた実際の値が単にテーブル内の行データへのポインタになることです。 ハッシュテーブルは基本的に連想配列なので、典型的なエントリは「Abc => 0x28939」のようになります。ここで、0x28939はAbcがメモリに格納されているテーブル行への参照です。 ハッシュテーブルインデックスで "Abc"のような値を検索し、メモリ内の行への参照を取り戻すことは、明らかにテーブルをスキャンしてEmployee_Name列に "Abc"の値を持つすべての行を見つけるよりはるかに高速です。

ハッシュインデックスの短所

ハッシュテーブルはソートされたデータ構造ではありません、そしてハッシュインデックスでさえ助けることができない多くのタイプのクエリがあります。 たとえば、40歳未満のすべての従業員を見つけたいとします。 ハッシュテーブルインデックスを使ってそれをどのように行うことができますか? ハッシュテーブルはキーと値のペアの検索にのみ適しているため、不可能です。つまり、等価性をチェックするクエリを意味します。

データベースインデックスの中身は? これで、データベースインデックスはテーブルの列に作成され、インデックスはその特定の列に値を格納することがわかりました。 ただし、データベースインデックスは同じテーブルの他の列に値を格納しないことを理解することが重要です。 たとえば、Employee_Name列に索引を作成すると、Employee_Age列とEmployee_Address列の値も索引に格納されません。 他のすべての列をインデックスに格納しただけでは、テーブル全体のコピーをもう1つ作成するのと同じようになります。スペースを浪費しすぎて非効率的になります。

データベースは、いつインデックスを使用するかをどのように認識しますか? 「SELECT * FROM従業員WHERE Employee_Name = 'Abc'」のような照会が実行されると、データベースは照会されている列に索引があるかどうかを確認します。 Employee_Name列に索引が作成されていると仮定すると、データベースは、検索対象の値を見つけるために索引を実際に使用するのが妥当かどうかを判断する必要があります。テーブル全体をスキャンするだけで、より効率的です。

データベースインデックスを作成するコストはいくらですか。

それはスペースをとります - そしてあなたのテーブルが大きければ大きいほど、あなたのインデックスも大きくなります。 インデックスでパフォーマンスが低下するもう1つの点は、対応するテーブルの行を追加、削除、または更新するたびに、インデックスに対して同じ操作を実行する必要があるということです。 索引には、その索引がカバーする表の列にあるものと同じものまで、同じ分のデータまで含める必要があることを忘れないでください。

原則として、インデックスが作成された列のデータが頻繁にクエリされる場合にのみ、テーブルにインデックスを作成します。

また見なさい

  1. どの列が一般に良い索引を作るのでしょうか。
  2. データベースインデックスの仕組み

データセットのサイズが大きくなると、索引付けが非常に重要になることを考えれば、データベースに依存しないレベルで索引付けがどのように機能するのかを説明できる人はいますか。

フィールドにインデックスを付けるためのクエリについては 、データベースカラムにインデックス を付けるにはをご覧ください。


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


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

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

もう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%未満の場合、クエリオプティマイザはインデックスの使用を回避し、インデックスをスペースの無駄にします。





database-indexes