MySQLパフォーマンス最適化[理論編]-クラスタインデックスと非クラスタインデックス,InnoDBとMyISAM

3951 ワード
[データベース] --MySQL MySQLデータベース最適化
クラスタインデックス
クラスタインデックス(Clustered Index)は新しいデータ構造ではなく、Bツリーインデックスの格納方法です.
クラスタインデックスの特徴は、完全なデータ行がBツリーのリーフノードに配置され、Clustered(クラスタ、クラスタ)は、データ行が対応するキーとコンパクトに格納されていることを示します.
次の図は、「高性能MySQL」クラスタインデックスの断面図です.リーフノードにはデータ行の完全なデータが含まれており、非リーフノードにはインデックス列データのみが含まれています.
データ行の論理順序は、クラスタインデックスの順序と一致します.B+ツリーのリーフノードはチェーンテーブルとして直列に接続されており、リーフノードのデータ行の論理順序は1つしかないため、1つのテーブルにはクラスタインデックスが1つしかありません.
反対に、非クラスタインデックスとは、データ行と対応するキーが存在しないことを意味し、非クラスタインデックスのリーフノードには、一般的にデータ行の参照が格納されます.
MySQLのインデックスはストレージエンジンによって実現され、異なるストレージエンジンのインデックスのストレージ構造は異なり、すべてのストレージエンジンがクラスタインデックスをサポートしているわけではありません.
MySQLは、公式およびサードパーティを含む多くのストレージエンジンをサポートし、最も多く使用されているのはInnoDB(5.5以降のデフォルトのストレージエンジン)とMyISAM(5.5以前のデフォルトのストレージエンジン)です.
InnoDBストレージエンジン
InnoDBのプライマリ・キー・インデックスのデフォルトはクラスタ化されています.
テーブルにプライマリ・キーが定義されていない場合、InnoDBはクラスタ・インデックスとしてすべてのカラムが空でないUniqueインデックスを選択します.
テーブルにプライマリ・キーも空でない一意のインデックスもない場合、InnoDBの内部にはGEN_CLUST_INDEXという暗黙的なクラスタ・インデックスが生成されます.この暗黙的クラスタインデックスには、各データ行のRowIDが含まれ、RowIDを暗黙的プライマリ・キーとしてソートする.RowIDは6バイトのフィールドであり、新しい行の挿入に伴って単調に増加し、暗黙インデックスのデータ行は物理構造上挿入順に格納される.
ほとんどの場合、プライマリ・キーを定義するので、上記の2つのプライマリ・キーがない場合は、以下に考慮しません.
InnoDBの2次インデックスは、リーフノードにプライマリ・キーを保存します.
2次インデックスで検索する場合、InnoDBは2次インデックスのリーフノードから対応するプライマリ・キーを取得し、プライマリ・キーに基づいてプライマリ・キー・インデックスから対応するローを検索する必要があります.つまり、InnoDB検索データ行は2つのBツリーを通ります.このようにすると、データ行の移動やデータページの分割時の2次インデックスのメンテナンス作業を減らすことができますが、検索速度がやや遅くなり、インデックスの上書きを実現し、必要なクエリーフィールドを2次インデックスに直接上書きさせることで、データ行を検索する必要がなく、プライマリ・キー・インデックスを実行する必要がなくなります.
クラスタインデックスの利点
  • クラスタリングインデックスはインデックスとデータ行を同じB-Treeに保存し、クエリはクラスタリングインデックスによってデータを直接取得することができ、クラスタリングインデックスではなく複数回のI/Oを行うため、クラスタリングインデックスは通常、非クラスタリングインデックス検索よりも高速である.
  • クラスタリングインデックスは、そのデータがプライマリ・キーに従って配列された
    • であるため、プライマリ・キー範囲クエリの効率が高い.
  • 二次インデックスインデックスインデックスは、リーフノードのプライマリ・キー値を直接使用するためにインデックス・オーバーライドを使用します.

    • クラスタインデックスの欠点
  • クラスタリングインデックスはI/O密集型アプリケーションのパフォーマンスを最大限に高めすぎますが、データがメモリに格納されている場合、アクセス順序はそれほど重要ではありません.クラスタリングインデックスにもメリットはありません.
  • の挿入速度は、挿入順序に大きく依存します.プライマリ・キー順にInnoDBにデータをインポートするのが一番早いです.プライマリ・キーに従って挿入しない場合は、インポートが完了したらOPTIMIZE TABLEコマンドを使用してテーブルを再編成することが望ましい.
  • クラスタインデックス新しいローを挿入し、プライマリ・キーを更新すると、ページ分割の問題が発生する可能性があります.満タンのリーフ・ノードに挿入すると、B+ツリーが2つのページに分割され、新しく挿入したロー・データが格納されます.ページ分割によって、テーブルがより多くのディスク領域を占有します(UUIDや乱数をプライマリ・キーとしないで、単調に増加した値をプライマリ・キーとして使用する必要があります).
  • クラスタリングインデックスは、物理的に遠いページをメモリにロードする必要があるため、テーブル全体のスキャン速度が遅くなる可能性があります(時間のかかるディスクトラッキング操作が必要です).
  • 2二次インデックスデータ行にアクセスするには、二次インデックスがプライマリ・キー列を保存しているため、二次インデックスがより大きなスペースを占める必要があります(短いプライマリ・キーを選択すると有利です).

    • MyISAMストレージエンジン
    InnoDBはプライマリ・キーを介してデータを集約し、クラスタ・インデックス全体が完全なテーブルです.MyISAMストレージエンジンのデータは比較的簡単です.
    MyISAM(ISAM,indexed sequential access method)のデータ行とインデックスは別々に格納されます.データファイルは.MYDを接尾辞とし、インデックスファイルは.MYIを接尾辞とします(Innodbには.idbファイルが1つしかありません).
    データ行はデータ挿入順にデータファイルに格納され、データ行の格納は複数行格納フォーマット(ROW_FORMAT)をサポートする.
    MyISAMにはクラスタリングインデックスがなく、プライマリ・キー・インデックスと2次インデックスの動作は同じです.リーフ・ノードは、データ・ファイル内のデータ行の物理的オフセット(行ポインタ)を格納します.
    MyISAMのメリット
  • は、特にデータ行長が固定されている場合に、データ行の読み取り速度が速い.
  • データ行挿入が容易で、新しい行はデータファイルの末尾に直接追加されます.

    • MyISAMの欠点
  • 削除操作は、空白領域を残しておく必要があります.そうしないと、後続の行データのオフセットが変化します.したがって、MyISAMテーブルデータを大量に削除した場合、データファイルのサイズは変化しないため、定期的にOPTIMIZE TABLE操作を実行してMyISAMフラグメント空間を整理する.
  • 修正操作データ行長が短くなると白くなる.データ行が長くなると、データはセグメント化されて格納されます.

    • MyISAMにはいくつかの重要なメリットとデメリットがあります.
  • 全文(FULLTEXT)インデックス
    • をサポート
  • トランザクションはサポートされていません.外部キーはサポートされていません.行レベルのロックはサポートされていません.

    • MyISAM詳細https://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html
    InnoDBは5.6.4以降全文インデックスをサポートしているが、MySQLの全文インデックスはすべて鶏肋で、中国語をサポートしていない.
    InnoDBは、トランザクション、外部キー、行レベルロックをサポートします.これらは、MySQLがMyISAMの代わりにInnoDBを使用してデフォルトのストレージエンジンとして使用する理由です.
    InnoDB詳細参照https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html
    参照先:
    『高性能MySQL』
    https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
    main関数のアセンブリコード
    RDSの基本構成