MySQLの索引部分

6543 ワード
MySQL
索引の概要
インデックスの本質と役割
インデックス(キーとも呼ばれる)は、テーブル内のデータ行の検索を高速化するために作成される分散したデータ構造です.
データベース・クエリーは、データベースの最も主要な機能の1つです.クエリー・データの速度をできるだけ速くしたいので、データベース・システムの設計者はクエリー・アルゴリズムの観点から最適化します.最も基本的なクエリーアルゴリズムはもちろんシーケンス検索(linear search)であり、この複雑さはO(n)のアルゴリズムがデータ量が大きい場合に明らかに悪いが、コンピュータ科学の発展には二分検索(binary search)、二叉木検索(binary tree search)など、より優れた検索アルゴリズムが多く提供されている.少し分析すると、各ルックアップアルゴリズムは特定のデータ構造にしか適用できないことがわかります.例えば、二分ルックアップではデータの秩序化が要求され、二叉木ルックアップは二叉ルックアップツリーにのみ適用されますが、データ自体の組織構造が様々なデータ構造を完全に満たすことはできません(例えば、理論的には両方の列を順番に組織することは不可能です).したがって、データに加えて、データベースシステムは、特定のルックアップアルゴリズムを満たすデータ構造を維持しており、これらのデータ構造は、何らかの方法でデータを参照(指向)し、これらのデータ構造上で高度なルックアップアルゴリズムを実現することができる.このデータ構造は、インデックスです.
インデックスのメリットとデメリット
メリット
  • はデータの検索速度を大幅に加速させる.
  • 一意性インデックスを作成し、データベース・テーブル内の各行のデータの一意性を保証します.
  • 加速テーブルとテーブルの間の接続;
  • パケットおよびソート句を使用してデータ検索を行う場合、クエリー内のパケットおよびソートの時間を大幅に短縮できます.

    • 欠点
  • インデックスは、追加の物理空間を占有する必要があります.
  • テーブル内のデータを追加、削除、変更する場合、インデックスも動的にメンテナンスする必要があり、データのメンテナンス速度を低下させる.

    • 索引のタイプ
    MySQLでは、インデックスはサーバ層ではなくストレージエンジン層で実現されるため、インデックス基準は統一されていません.異なるストレージエンジンのインデックスの動作は異なり、すべてのストレージエンジンがすべてのタイプのインデックスをサポートしているわけではありません.複数のストレージエンジンが同じタイプのインデックスをサポートしている場合でも、最下位の実装方法は異なる場合があります.
    現在MySQLでは、一般的な4つのインデックスが用意されています.
  • B-Treeインデックス:最も一般的なインデックスタイプで、ほとんどのエンジンはB-Treeインデックスをサポートしています.
  • Hashインデックス:MemoryエンジンのみがHashインデックスをサポートし、Hashテーブルに基づいて実現され、使用シーンが簡単である.
  • R-Tree:空間データインデックス、MyISAMエンジンサポート、主に地理データストレージに使用される.
  • 全文インデックス:検索エンジンの機能に似た特殊なタイプのインデックスです.

    • 説明:通常議論されているインデックスは、特に明記されていない場合、B-Treeインデックスを指すのが一般的であり、技術的に言えば、下位実装方式はB+Treeである.B-Treeインデックスは、通常のインデックス、一意のインデックス、プライマリ・キー・インデックスなど、いくつかに分けられます.全文索引については、MySQLの全文索引を参照
    3つの一般的なストレージエンジンインデックスのサポート
    索引
    MyISAM
    InnoDB
    Memory
    B-Tree
    Yes
    Yes
    Yes
    Hash索引
    No
    No
    Yes
    R-Treeインデックス
    Yes
    No
    No
    全文索引
    Yes
    5.6以降
    No
    B-Treeインデックス
  • 通常インデックス:通常インデックスを作成する場合、条件(一意、非空などの制限)は制限されません.このタイプのインデックスは、任意のデータ型に作成できます.
  • ユニークインデックス:UNIQUE、ユニークインデックス、つまりインデックスされた値がユニークであることを制限します.テーブル内のフィールドにプライマリ・キーまたは一意性制約を設定すると、フィールドに関連付けられた一意のインデックスが自動的に作成されます.
  • プライマリ・キー・インデックス:PRIMARY、プライマリ・キー・インデックス、特殊な一意のインデックスであり、NULL値は許可されません.

    • 説明:一意のインデックスと一意の制約の検討については、一意の制約と一意のインデックスについて説明します.
    インデックス操作
    作成
    3つの作成方法
    // 1.      
create table    (
    id int(11),
    xxx xxx,
    [    ] index|key [   ] (   1 [(  )] [ASC|DESC]) 
);

// 2.     
create index    
    on    (    [(  )] [ASC|DESC]);

// 3.      
alter table   
    add [    ] index|key [   ] (   1 [(  )] [ASC|DESC]);

    注:プライマリ・キー・インデックスは2つ目の方法で作成できません.
    変更
    修正する必要はなく、直接削除して再構築すればいいです.
    削除
    2つの削除方法
    // 1.     
drop index    
    on   ;

// 2.      
alter table   
    drop index    ;

    表示
    show index|keys from

    列の意味:
  • Table:テーブル名
  • Non_Unique:インデックス値が一意の場合は0、そうでない場合は1
  • Key_name:インデックス名
  • Seq_in_index:インデックス内のカラムのシーケンス番号、1から
  • Column_name:カラム名
  • Collation:インデックスにリストされた格納形式.MySQLでは、値「A」(昇順)またはNULL(分類なし)
  • Cardinality:インデックス内の一意の値の数の推定値
  • Sub_part:カラムが部分的にインデックスに組み込まれている場合はインデックスに組み込まれた文字の数、カラム全体がインデックスに組み込まれている場合はNULL
    • となる.
  • Packed:キーワードがどのように圧縮されるかを示し、圧縮されていない場合はNULL
  • Null:カラムにNULLが含まれている場合はYES、ない場合はNO
  • Index_type:インデックスタイプ(BTREE,FULLTEXT,HASH,RTREE)
  • Comment:その他のコメント
    • 索引の選択
    インデックス選択の原則
  • クエリー条件として頻繁に使用されるフィールドはインデックスを作成する必要があります.
  • 一意性が悪すぎるフィールド(性別など)は、頻繁にクエリー条件として使用されても、インデックスを個別に作成するのに適していません.
  • 更新が非常に頻繁なフィールドはインデックスの作成に適していません.
  • Where句には表示されないフィールドはインデックスを作成しません.

    • インデックス選択の原則の詳細
  • パフォーマンスの最適化では、インデックスを作成する列を選択することが最も重要です.インデックスを使用するには、where句に表示される列、selectキーワードの後にリストの列を選択するのではなくjoin句に表示される列の2つのタイプがあります.
  • インデックス列の基数が大きいほど、インデックスの効果は向上します.たとえば、生年月日を格納する列は異なる値を持ち、区別しやすく、性別を記録する列は、「男」と「女」だけで、インデックスを行うのにあまり役に立たない.どの値を検索しても、約半分の行が得られるからだ.
  • は短いインデックスを使用します.文字列をインデックスする場合は、接頭辞の長さを指定する必要があります.これにより、インデックスのスペースを大幅に節約し、クエリーの速度を向上させることができます.
  • は、最左接頭辞の原則を利用する.

    • 索引選択の考慮事項
    インデックスがクエリーの速度を速めることができる以上、クエリー文が必要であればインデックスを作成しますか?答えは否定的だ.インデックスはクエリーの速度を速めますが、インデックスにも代価があります.インデックスファイル自体はストレージスペースを消費し、インデックスはレコードの挿入、削除、変更の負担を重くします.また、MySQLは実行時にもリソースを消費してインデックスを維持するので、インデックスが多ければ多いほど良いわけではありません.
    一般的に、次の2つのケースではインデックスの作成は推奨されません.
  • 表の記録は比較的に少なくて、例えば1、2千本甚だしきに至っては数百本の記録の表で、インデックスを作る必要はなくて、クエリーに全表のスキャンをさせたらいいです;
  • インデックスの選択性が低く、インデックスの選択性(Selectivity)とは、重複しないインデックス値(基数とも呼ばれ、Cardinality)とテーブルレコード数(#T)との比:Index Selectivity=Cardinality/#Tの選択性の取値範囲が(0,1)であることを意味し、選択性が高いインデックス値が大きいほどB+Treeの性質によって決定される.
    • この他にも、次の2つの点に注意してください.
  • MySQLでは、次のオペレータに対してのみインデックスが使用されます.、>=、between、in、および場合によってはlike(ワイルドカード%または_で始まるものではありません).
  • インデックスを過度にしないで、必要なインデックスだけを保持します.各追加のインデックスは、追加のディスク領域を消費し、書き込み操作のパフォーマンスを低下させます.テーブルの内容を変更するときは、インデックスを更新する必要があり、再構築する必要がある場合があります.そのため、インデックスが多ければ多いほど、時間がかかります.

    • インデックスは最高のソリューションですか?
    インデックスは常に最良のソリューションではありません.総じて、インデックスは、インデックスがストレージエンジンがレコードの利点が追加の作業よりも大きいことを迅速に検索するのに役立つ場合にのみ有効です.非常に小さなテーブルでは、ほとんどの場合、簡単な全テーブルスキャンがより効率的です.中～大規模のテーブルでは、インデックスが有効です.しかし、特大なテーブルでは、インデックスの作成と使用のコストが増加します.この場合、クエリに必要なデータのセットを直接区別する技術が必要であり、1つのレコードが1つのレコードに一致するのではなく、1つの技術が必要である.これは、テーブル、ライブラリなどのパーティション化技術を使用する必要があります.
    参考記事
    MySQLインデックスの背後にあるデータ構造とアルゴリズム原理インデックスの利害とどのように判定するか、インデックス高性能MySQL(第3版)
    この文書の元のリンク:MySQLのインデックスセクション
    mysqlの4つのトランザクション独立性レベル
    LeetCode c++基本計算機II