MySQLノート-インデックスタイプ
3219 ワード
インデックスタイプには、B-Tree、ハッシュインデックス、R-Tree、全文インデックスなどがあります.ここでは、主にB-Treeとハッシュインデックスをまとめます.
B-Treeインデックス
B-Treeを構造とするインデックスは最も一般的なインデックスタイプであり、例えばInnoDBとMyISAMはB-Treeをインデックス構造とするインデックスであり、実際にはB+Treeをインデックス構造とし、B-TreeとB+Treeの違いは、B+Treeがリーフノードに順次アクセスポインタを追加し、リーフサブノードの範囲を遍歴しやすいことである.ここでは主にInnoDBとMyISAMの2つの異なるインデックスについて紹介します.
InnoDB
InnoDBはクラスタインデックスをサポートしており、クラスタインデックスと非クラスタインデックスは厳密にはインデックスではなく、データストレージ方式であり、この名前はそれ自体のストレージ方式と関係があります.「クラスタ化」(Cluster)「データ行と隣接するキー値が一緒に格納されていることを示します.簡単に言えば、リーフノードに格納されている実際のデータです.InnoDBはプライマリ・キーによってデータを集約するので、1つのテーブルに1つのクラスタ・インデックスしか存在せず、プライマリ・キーが必要です.プライマリ・キーが定義されておらず、空でないインデックスが存在しない場合は、InnoDBはクラスタ・インデックスとして1つのプライマリ・キーを暗黙的に定義します.
クラスタインデックスの2次インデックスには、ローの物理的な位置を指すポインタではなく、ローのプライマリ・キー値が格納されます.したがって、2次インデックスでローを検索する場合は、2次インデックスのリーフ・ノードを見つけて対応するプライマリ・キー値を取得し、対応するローを検索する必要があります.InnoDBの場合、適応ハッシュインデックスは、このような重複作業を低減することができる.
MyISAM
MyISAMは非クラスタインデックスをサポートしているが,InnoDBとの違いは,リーフノードに格納されているのは対応する行を指す物理アドレスであり,すなわちインデックスとデータは実際には別々に格納されていることである.
MyISAMは接頭辞圧縮技術を採用しており、より多くのインデックスをメモリに入れることができ、デフォルトでは文字列のみを圧縮しますが、パラメータ設定で整数を圧縮することもできます.圧縮方法は、インデックスブロックの最初のIEを完全に保存し、他の値と最初の値を比較して同じ接頭辞のバイト数と残りの異なる接尾辞部分を得て、この部分を記憶すればよい.たとえば、インデックスブロックの最初の値は「perform」であり、2番目の値は「performance」であり、2番目の値の接頭辞は圧縮されて「7,ance」のような形式で格納される.MyISAM対行ポインタも同様の接頭辞圧縮方式を採用している.
圧縮により、インデックスの使用スペースが少なくなり、パフォーマンスがある程度向上しますが、いくつかの操作が遅くなる可能性があります.各値の圧縮接頭辞は前の値に依存するため、MyISAM検索時にインデックスブロックで二分検索を使用することはできず、最初からスキャンを開始するしかなく、正のスキャン速度は悪くないが、逆のスキャンではある行を検索する操作は平均して半分のインデックスブロックをスキャンする必要がある.CPU密集型アプリケーションでは、スキャンにはランダムな検索が必要なため、インデックスの圧縮によりインデックスの検索が遅くなり、I/O密集型アプリケーションでは、クエリの最適化が顕著になります.
ロック
InnoDBはロー・ロックを使用しているのでトランザクションはサポートされていますが、MyISAMはテーブル・ロックを使用しており、トランザクションはサポートされていません.
適用範囲
B−Treeインデックスは、B−Treeが格納されたリーフノード自体が秩序化されており、B+Tree構造は、リーフノードの連続順序ポインタを追加し、区間クエリにとってより便利であるため、区間クエリに適している.
ハッシュ索引
ハッシュ・インデックスは、ハッシュ・テーブルに基づいて実装され、インデックスのすべてのカラムに正確に一致するクエリのみが有効です.方法は、すべてのインデックス列に対してhash codeを計算し、hash codeをインデックスとして、ハッシュテーブルに各データ行を指すポインタを保存することです.
メリットインデックス自体はhash codeのみを格納するため、構造がコンパクトで、検索速度が速い 制限インデックスのhash codeは順番に格納されているが、hash codeに対応するデータは順番ではないため、 のソートには使用できない.は、ハッシュインデックスがインデックス列のすべての内容を使用してhash code を計算するため、部分インデックス列マッチング検索をサポートしない.は等値比較のみをサポートし、範囲クエリー はサポートしません.ハッシュ競合が深刻な場合は、チェーンテーブル内のすべてのローポインタ を巡回する必要があります.ハッシュ競合が深刻であれば、インデックスメンテナンス操作のコストも高い InnoDBの適応ハッシュインデックス
まず、適応ハッシュインデックスは、ユーザにとって感知されず、完全に自動的で内部的な動作であり、ユーザは制御または構成できないが、閉じることができることに注意してください.
InnoDBは、インデックス値が非常に頻繁に使用されていることに気づくと、メモリ内のB−Treeインデックスに基づいてハッシュインデックスをさらに作成し、B−Treeは、高速ハッシュ検索などのハッシュインデックスのいくつかの利点を有することができる.
もちろん、ストレージエンジンがハッシュインデックスをサポートしていない場合、ユーザーはハッシュインデックスをカスタマイズすることもできます.このような性能は比較的高く、ハッシュ値を自分で維持する必要があるという欠点があります.この方法を採用すれば、
インデックスの利点インデックスを使用すると、サーバがスキャンする必要があるデータ量 を削減できます.インデックスを使用すると、サーバがソートやテンポラリ・テーブル を回避できます.インデックスを使用するランダムI/Oを順次I/O に変更することができる.
しかし、すべての場合、インデックスは最良のソリューションであり、非常に小さなテーブルでは、ほとんどの場合、簡単な全テーブルスキャンがより効率的であり、中から大規模なテーブルではインデックスが有効であり、特大なテーブルではパーティションがより効果的です.
Reference
『高性能MySQL』
B-Treeインデックス
B-Treeを構造とするインデックスは最も一般的なインデックスタイプであり、例えばInnoDBとMyISAMはB-Treeをインデックス構造とするインデックスであり、実際にはB+Treeをインデックス構造とし、B-TreeとB+Treeの違いは、B+Treeがリーフノードに順次アクセスポインタを追加し、リーフサブノードの範囲を遍歴しやすいことである.ここでは主にInnoDBとMyISAMの2つの異なるインデックスについて紹介します.
InnoDB
InnoDBはクラスタインデックスをサポートしており、クラスタインデックスと非クラスタインデックスは厳密にはインデックスではなく、データストレージ方式であり、この名前はそれ自体のストレージ方式と関係があります.「クラスタ化」(Cluster)「データ行と隣接するキー値が一緒に格納されていることを示します.簡単に言えば、リーフノードに格納されている実際のデータです.InnoDBはプライマリ・キーによってデータを集約するので、1つのテーブルに1つのクラスタ・インデックスしか存在せず、プライマリ・キーが必要です.プライマリ・キーが定義されておらず、空でないインデックスが存在しない場合は、InnoDBはクラスタ・インデックスとして1つのプライマリ・キーを暗黙的に定義します.
クラスタインデックスの2次インデックスには、ローの物理的な位置を指すポインタではなく、ローのプライマリ・キー値が格納されます.したがって、2次インデックスでローを検索する場合は、2次インデックスのリーフ・ノードを見つけて対応するプライマリ・キー値を取得し、対応するローを検索する必要があります.InnoDBの場合、適応ハッシュインデックスは、このような重複作業を低減することができる.
MyISAM
MyISAMは非クラスタインデックスをサポートしているが,InnoDBとの違いは,リーフノードに格納されているのは対応する行を指す物理アドレスであり,すなわちインデックスとデータは実際には別々に格納されていることである.
MyISAMは接頭辞圧縮技術を採用しており、より多くのインデックスをメモリに入れることができ、デフォルトでは文字列のみを圧縮しますが、パラメータ設定で整数を圧縮することもできます.圧縮方法は、インデックスブロックの最初のIEを完全に保存し、他の値と最初の値を比較して同じ接頭辞のバイト数と残りの異なる接尾辞部分を得て、この部分を記憶すればよい.たとえば、インデックスブロックの最初の値は「perform」であり、2番目の値は「performance」であり、2番目の値の接頭辞は圧縮されて「7,ance」のような形式で格納される.MyISAM対行ポインタも同様の接頭辞圧縮方式を採用している.
圧縮により、インデックスの使用スペースが少なくなり、パフォーマンスがある程度向上しますが、いくつかの操作が遅くなる可能性があります.各値の圧縮接頭辞は前の値に依存するため、MyISAM検索時にインデックスブロックで二分検索を使用することはできず、最初からスキャンを開始するしかなく、正のスキャン速度は悪くないが、逆のスキャンではある行を検索する操作は平均して半分のインデックスブロックをスキャンする必要がある.CPU密集型アプリケーションでは、スキャンにはランダムな検索が必要なため、インデックスの圧縮によりインデックスの検索が遅くなり、I/O密集型アプリケーションでは、クエリの最適化が顕著になります.
ロック
InnoDBはロー・ロックを使用しているのでトランザクションはサポートされていますが、MyISAMはテーブル・ロックを使用しており、トランザクションはサポートされていません.
適用範囲
B−Treeインデックスは、B−Treeが格納されたリーフノード自体が秩序化されており、B+Tree構造は、リーフノードの連続順序ポインタを追加し、区間クエリにとってより便利であるため、区間クエリに適している.
ハッシュ索引
ハッシュ・インデックスは、ハッシュ・テーブルに基づいて実装され、インデックスのすべてのカラムに正確に一致するクエリのみが有効です.方法は、すべてのインデックス列に対してhash codeを計算し、hash codeをインデックスとして、ハッシュテーブルに各データ行を指すポインタを保存することです.
メリット
まず、適応ハッシュインデックスは、ユーザにとって感知されず、完全に自動的で内部的な動作であり、ユーザは制御または構成できないが、閉じることができることに注意してください.
InnoDBは、インデックス値が非常に頻繁に使用されていることに気づくと、メモリ内のB−Treeインデックスに基づいてハッシュインデックスをさらに作成し、B−Treeは、高速ハッシュ検索などのハッシュインデックスのいくつかの利点を有することができる.
もちろん、ストレージエンジンがハッシュインデックスをサポートしていない場合、ユーザーはハッシュインデックスをカスタマイズすることもできます.このような性能は比較的高く、ハッシュ値を自分で維持する必要があるという欠点があります.この方法を採用すれば、
SHA1()とMD5()をハッシュ関数として使用しないでください.この2つは強力な暗号化関数であるため、設計目標は衝突を最大限に解消することです.生成されたhash codeは非常に長い文字列であり、大量の空間を浪費し、ハッシュインデックスではインデックスの衝突に対する要求はそれほど高くない.インデックスの利点
しかし、すべての場合、インデックスは最良のソリューションであり、非常に小さなテーブルでは、ほとんどの場合、簡単な全テーブルスキャンがより効率的であり、中から大規模なテーブルではインデックスが有効であり、特大なテーブルではパーティションがより効果的です.
Reference
『高性能MySQL』