データベース・インデックスのメリットとデメリット
5738 ワード
索引:
インデックスを作成する理由これは、インデックスの作成により、システムのパフォーマンスが大幅に向上するためです.
インデックスを増やすメリットがこんなに多いのに、なぜテーブルの各カラムにインデックスを作成しないのかと聞かれるかもしれません.この考えには合理性があるが、一面性もある.インデックスには多くの利点がありますが、テーブルの各カラムにインデックスを追加するのは賢明ではありません.これは,インデックスを増やすことにも多くの不利な側面があるからである.
インデックスは、データベース・テーブルの一部のカラムの上に作成されます.したがって、インデックスを作成するときは、インデックスを作成できるカラムと、インデックスを作成できるカラムをよく考慮する必要があります.一般的には、次のようなカラムにインデックスを作成する必要があります.
一部のカラムではインデックスを作成するべきではありません.一般に、インデックスを作成するべきでないカラムには、次のような特徴があります.第1に、クエリであまり使用されていないカラムや参照されていないカラムに対してインデックスを作成するべきではありません.これは、これらのカラムがあまり使用されていないため、インデックスがあるかインデックスがないかでは、クエリーの速度を向上させることはできません.逆に,インデックスが増加するため,システムのメンテナンス速度が低下し,空間的要件が増大する. 第2に、わずかなデータ値しかないカラムに対してもインデックスを追加するべきではありません.これは、人事テーブルの性別列など、これらのカラムの値が少ないため、クエリの結果、結果セットのデータ行がテーブル内のデータ行に大きな割合を占め、テーブル内で検索する必要があるデータ行の割合が大きいためです.インデックスを追加すると、検索速度が大幅に速くなるわけではありません. 第3に、textとして定義されているカラムに対して、imageおよびbitデータ型のカラムはインデックスを追加すべきではない.これは、これらのカラムのデータ量がかなり大きいか、値が少ないためです. 第4に、修正性能が取得性能よりはるかに大きい場合、インデックスを作成するべきではない.これは,性能の修正と検索性能が矛盾しているためである.インデックスを追加すると、取得のパフォーマンスは向上しますが、変更のパフォーマンスは低下します.インデックスを減らすと、変更のパフォーマンスが向上し、取得のパフォーマンスが低下します.したがって、取得パフォーマンスよりもはるかに大きいパフォーマンスを変更する場合、インデックス を作成するべきではない.
集計インデックス
私たちは通常、テーブルを構築するときにテーブルにプライマリ・キーを付けます.一部のリレーショナル・データベースでは、テーブルを構築するときにプライマリ・キーを指定しないと、データベースはテーブルを構築する文の実行を拒否します.実際、プライマリ・キーを付けたテーブルを「テーブル」と呼ぶことはできません.プライマリ・キーを付けていないテーブルは、データが無秩序にディスク・メモリに配置され、1行1行が整然と並んでいて、私の認識の中の「テーブル」に近い.テーブルにプライマリ・キーを付けると、テーブルのディスク上のストレージ構造が整列した構造からツリー構造、すなわち前述の「バランス・ツリー」構造に変わり、言い換えればテーブル全体がインデックスになります.そう、もう一度言うと、テーブル全体がインデックスになります.つまり、いわゆる「集約インデックス」です.これは、テーブルにプライマリ・キーが1つしかなく、テーブルに「集計インデックス」が1つしかない理由です.プライマリ・キーの役割は、「テーブル」のデータ・フォーマットを「インデックス(バランス・ツリー)」のフォーマットに変換して配置することです.ここでは、上の図を書くと、プライマリ・キー付きテーブル(集約インデックス)の構造図になります.絵があまりよくないので、見てみましょう.ツリーのすべてのノード(下を除く)のデータは、プライマリ・キー・フィールドのデータで構成されます.つまり、通常、プライマリ・キーのidフィールドを指定します.一番下の部分は、真のテーブルのデータです.SQL文を実行する場合は、次のようにします.
まず、インデックスに基づいて1256という値が存在するリーフノードに位置決めし、その後、idが1256に等しいデータ行にリーフノードを介して取得する.バランスツリーの動作の詳細は説明しませんが、上図から分かるように、ツリーは全部で3階建てで、ルートノードからリーフノードまで3回検索するだけで結果が得られます.下図のようにここに画像の説明を書きます
1枚のテーブルに1億個のデータがある場合、いずれかのデータを検索する必要があります.通常の論理に従って、1個1個のデータをマッチングすると、最悪の場合、1億回マッチングして結果を得る必要があります.大きなOマーク法ではO(n)の最悪の時間の複雑さです.これは受け入れられません.そして、この1億個のデータは明らかに一度にメモリを読み込んでプログラムに使用することはできません.そのため、この1億回のマッチングは、キャッシュ最適化なしで1億回のIOオーバーヘッドであり、現在のディスクのIO能力とCPUの演算能力では、結果が出るまで数ヶ月かかる可能性があります.この表をバランスツリー構造(非常に茂っている木とノードが非常に多い木)に変換し、この木に10層があると仮定すると、10回のIOオーバーヘッドで必要なデータが見つかり、速度が指数レベルで向上し、大きなOマーク法でO(log n)、nは総木を記録し、底数は木の分岐数、結果として木の層回数となる.言い換えれば、検索回数は木の分岐数をベースにして、総数の対数を記録し、数式で表すとここで画像の説明を書く
プログラムで表すとMathです.ログ(10000000,10),10000000はレコード数,10はツリーの分岐数(実環境では分岐数が10をはるかに超えている)であり,結果として検索回数であり,ここでの結果は億から桁数に下がった.そのため,インデックスを利用するとデータベースクエリの驚くべきパフォーマンスが向上する.
しかし、物事には両面があり、インデックスはデータベースのクエリーデータの速度を上昇させ、書き込みデータの速度を低下させることができる.理由は簡単である.バランスツリーという構造は常に正しい状態を維持しなければならないため、データを削除して変更するとバランスツリーの各ノードにおけるインデックスデータの内容が変化し、ツリー構造が破壊されるため、データが変更されるたびに、DBMSは、ツリー(インデックス)の構造を再整理して正確であることを確認する必要があります.これは、インデックスがクエリー以外の操作に副作用をもたらす理由です.
非集計インデックス
集計インデックスについてお話しします.次に、非集計インデックスについてお話しします.つまり、私たちが普段よく話している一般的なインデックスです.
非集計インデックスは、集計インデックスと同様に、インデックスとしてバランスツリーを使用するデータ構造です.インデックスツリー構造の各ノードの値はテーブル内のインデックスフィールドから来ており、userテーブルのnameフィールドにインデックスを付けると、インデックスはnameフィールドの値から構成され、データが変更されるとDBMSはインデックス構造の正確性を維持する必要がある.テーブル内の複数のフィールドにインデックスを付けると、複数の独立したインデックス構造が表示されます.各インデックス(集計されていないインデックス)は互いに関連付けられていません.下図に示すように、文字セグメントに新しいインデックスを作成するたびに、フィールドのデータがコピーされ、インデックスが生成されます.したがって、テーブルにインデックスを追加すると、テーブルのボリュームが増加し、ディスクストレージ領域が占有されます.
非集計インデックスと集計インデックスの違いは、集計インデックスを使用して検索する必要があるデータを検索し、非集計インデックスを使用してレコードに対応するプライマリ・キー値を検索し、プライマリ・キーの値を使用して集計インデックスを使用して必要なデータを検索することです.
どのようにテーブルをクエリーしても、最終的にはプライマリ・キーを使用して集約インデックスを使用してデータにナビゲートされ、集約インデックス(プライマリ・キー)は実際のデータへの唯一のパスです.
インデックスの上書き(複合インデックスまたはマルチフィールドインデックスクエリー)
しかし、集約インデックスを使用せずに必要なデータをクエリーできる例外があります.この非主流の方法は、「上書きインデックス」クエリー、すなわち、通常は複合インデックスまたはマルチフィールドインデックスクエリーと呼ばれています.フィールドにインデックスを作成すると、フィールドの内容がインデックスに同期され、1つのインデックスに2つのフィールドを指定すると、両方のフィールドの内容がインデックスに同期されることが示されています.
まず、次のSQL文を見てください.
//索引の作成
//1991年11月1日生まれのユーザー名を調べる
このSQL文の実行手順は次のとおりです.
まず、非集計インデックスindex_birthdayはbirthdayが1991-11-1に等しいすべてのレコードのプライマリ・キーID値を検索する
そして、得られたプライマリ・キーID値が集計インデックス検索を実行することにより、プライマリ・キーID値ペアの実データ(データ行)が格納されている場所を見つける
最後に、得られた真実データからuser_を取得するnameフィールドの値が返されます.つまり、最終的な結果が得られます.
birthdayフィールドのインデックスをダブルフィールドの上書きインデックスに変更しました
このSQL文の実行プロセスは
非集計インデックスによるindex_birthday_and_user_nameはbirthdayが1991-11-1に等しいリーフノードの内容を検索するが、リーフノードにはuser_nameテーブルプライマリキーIDの値以外はuser_nameフィールドの値も入っているので、プライマリ・キーID値のデータ行の真実を検索する必要はなく、リーフ・ノードのuser_を直接取得するnameの値を返します.このようにインデックスを上書きして直接検索することにより、インデックスを上書きしない次の2つのステップを省略することができ、クエリー性能を大幅に向上させることができ、下図に示すように、データベースインデックスを記述する画像の大まかな動作原理は、本文で述べたように記述されているが、詳細についてはわずかに偏差することができるが、概念の説明の結果に影響を与えることはない.
インデックスを作成する理由これは、インデックスの作成により、システムのパフォーマンスが大幅に向上するためです.
， ， 。
， ， 。
， ， 。
， ， 。
， ， ， ， 。
インデックスを増やすメリットがこんなに多いのに、なぜテーブルの各カラムにインデックスを作成しないのかと聞かれるかもしれません.この考えには合理性があるが、一面性もある.インデックスには多くの利点がありますが、テーブルの各カラムにインデックスを追加するのは賢明ではありません.これは,インデックスを増やすことにも多くの不利な側面があるからである.
， ， 。
， ， ， ， ， 。
， 、 ， ， 。
インデックスは、データベース・テーブルの一部のカラムの上に作成されます.したがって、インデックスを作成するときは、インデックスを作成できるカラムと、インデックスを作成できるカラムをよく考慮する必要があります.一般的には、次のようなカラムにインデックスを作成する必要があります.
， ；
， ；
， ， ；
， ， ；
， ， ， ；
WHERE ， 。
一部のカラムではインデックスを作成するべきではありません.一般に、インデックスを作成するべきでないカラムには、次のような特徴があります.
集計インデックス
私たちは通常、テーブルを構築するときにテーブルにプライマリ・キーを付けます.一部のリレーショナル・データベースでは、テーブルを構築するときにプライマリ・キーを指定しないと、データベースはテーブルを構築する文の実行を拒否します.実際、プライマリ・キーを付けたテーブルを「テーブル」と呼ぶことはできません.プライマリ・キーを付けていないテーブルは、データが無秩序にディスク・メモリに配置され、1行1行が整然と並んでいて、私の認識の中の「テーブル」に近い.テーブルにプライマリ・キーを付けると、テーブルのディスク上のストレージ構造が整列した構造からツリー構造、すなわち前述の「バランス・ツリー」構造に変わり、言い換えればテーブル全体がインデックスになります.そう、もう一度言うと、テーブル全体がインデックスになります.つまり、いわゆる「集約インデックス」です.これは、テーブルにプライマリ・キーが1つしかなく、テーブルに「集計インデックス」が1つしかない理由です.プライマリ・キーの役割は、「テーブル」のデータ・フォーマットを「インデックス(バランス・ツリー)」のフォーマットに変換して配置することです.ここでは、上の図を書くと、プライマリ・キー付きテーブル(集約インデックス)の構造図になります.絵があまりよくないので、見てみましょう.ツリーのすべてのノード(下を除く)のデータは、プライマリ・キー・フィールドのデータで構成されます.つまり、通常、プライマリ・キーのidフィールドを指定します.一番下の部分は、真のテーブルのデータです.SQL文を実行する場合は、次のようにします.
select * from table where id = 1256
まず、インデックスに基づいて1256という値が存在するリーフノードに位置決めし、その後、idが1256に等しいデータ行にリーフノードを介して取得する.バランスツリーの動作の詳細は説明しませんが、上図から分かるように、ツリーは全部で3階建てで、ルートノードからリーフノードまで3回検索するだけで結果が得られます.下図のようにここに画像の説明を書きます
1枚のテーブルに1億個のデータがある場合、いずれかのデータを検索する必要があります.通常の論理に従って、1個1個のデータをマッチングすると、最悪の場合、1億回マッチングして結果を得る必要があります.大きなOマーク法ではO(n)の最悪の時間の複雑さです.これは受け入れられません.そして、この1億個のデータは明らかに一度にメモリを読み込んでプログラムに使用することはできません.そのため、この1億回のマッチングは、キャッシュ最適化なしで1億回のIOオーバーヘッドであり、現在のディスクのIO能力とCPUの演算能力では、結果が出るまで数ヶ月かかる可能性があります.この表をバランスツリー構造(非常に茂っている木とノードが非常に多い木)に変換し、この木に10層があると仮定すると、10回のIOオーバーヘッドで必要なデータが見つかり、速度が指数レベルで向上し、大きなOマーク法でO(log n)、nは総木を記録し、底数は木の分岐数、結果として木の層回数となる.言い換えれば、検索回数は木の分岐数をベースにして、総数の対数を記録し、数式で表すとここで画像の説明を書く
プログラムで表すとMathです.ログ(10000000,10),10000000はレコード数,10はツリーの分岐数(実環境では分岐数が10をはるかに超えている)であり,結果として検索回数であり,ここでの結果は億から桁数に下がった.そのため,インデックスを利用するとデータベースクエリの驚くべきパフォーマンスが向上する.
しかし、物事には両面があり、インデックスはデータベースのクエリーデータの速度を上昇させ、書き込みデータの速度を低下させることができる.理由は簡単である.バランスツリーという構造は常に正しい状態を維持しなければならないため、データを削除して変更するとバランスツリーの各ノードにおけるインデックスデータの内容が変化し、ツリー構造が破壊されるため、データが変更されるたびに、DBMSは、ツリー(インデックス)の構造を再整理して正確であることを確認する必要があります.これは、インデックスがクエリー以外の操作に副作用をもたらす理由です.
非集計インデックス
集計インデックスについてお話しします.次に、非集計インデックスについてお話しします.つまり、私たちが普段よく話している一般的なインデックスです.
非集計インデックスは、集計インデックスと同様に、インデックスとしてバランスツリーを使用するデータ構造です.インデックスツリー構造の各ノードの値はテーブル内のインデックスフィールドから来ており、userテーブルのnameフィールドにインデックスを付けると、インデックスはnameフィールドの値から構成され、データが変更されるとDBMSはインデックス構造の正確性を維持する必要がある.テーブル内の複数のフィールドにインデックスを付けると、複数の独立したインデックス構造が表示されます.各インデックス(集計されていないインデックス)は互いに関連付けられていません.下図に示すように、文字セグメントに新しいインデックスを作成するたびに、フィールドのデータがコピーされ、インデックスが生成されます.したがって、テーブルにインデックスを追加すると、テーブルのボリュームが増加し、ディスクストレージ領域が占有されます.
非集計インデックスと集計インデックスの違いは、集計インデックスを使用して検索する必要があるデータを検索し、非集計インデックスを使用してレコードに対応するプライマリ・キー値を検索し、プライマリ・キーの値を使用して集計インデックスを使用して必要なデータを検索することです.
どのようにテーブルをクエリーしても、最終的にはプライマリ・キーを使用して集約インデックスを使用してデータにナビゲートされ、集約インデックス(プライマリ・キー)は実際のデータへの唯一のパスです.
インデックスの上書き(複合インデックスまたはマルチフィールドインデックスクエリー)
しかし、集約インデックスを使用せずに必要なデータをクエリーできる例外があります.この非主流の方法は、「上書きインデックス」クエリー、すなわち、通常は複合インデックスまたはマルチフィールドインデックスクエリーと呼ばれています.フィールドにインデックスを作成すると、フィールドの内容がインデックスに同期され、1つのインデックスに2つのフィールドを指定すると、両方のフィールドの内容がインデックスに同期されることが示されています.
まず、次のSQL文を見てください.
//索引の作成
create index index_birthday on user_info(birthday);
//1991年11月1日生まれのユーザー名を調べる
select user_name from user_info where birthday = '1991-11-1'
このSQL文の実行手順は次のとおりです.
まず、非集計インデックスindex_birthdayはbirthdayが1991-11-1に等しいすべてのレコードのプライマリ・キーID値を検索する
そして、得られたプライマリ・キーID値が集計インデックス検索を実行することにより、プライマリ・キーID値ペアの実データ(データ行)が格納されている場所を見つける
最後に、得られた真実データからuser_を取得するnameフィールドの値が返されます.つまり、最終的な結果が得られます.
birthdayフィールドのインデックスをダブルフィールドの上書きインデックスに変更しました
create index index_birthday_and_user_name on user_info(birthday, user_name);
このSQL文の実行プロセスは
非集計インデックスによるindex_birthday_and_user_nameはbirthdayが1991-11-1に等しいリーフノードの内容を検索するが、リーフノードにはuser_nameテーブルプライマリキーIDの値以外はuser_nameフィールドの値も入っているので、プライマリ・キーID値のデータ行の真実を検索する必要はなく、リーフ・ノードのuser_を直接取得するnameの値を返します.このようにインデックスを上書きして直接検索することにより、インデックスを上書きしない次の2つのステップを省略することができ、クエリー性能を大幅に向上させることができ、下図に示すように、データベースインデックスを記述する画像の大まかな動作原理は、本文で述べたように記述されているが、詳細についてはわずかに偏差することができるが、概念の説明の結果に影響を与えることはない.