索引はデータのディレクトリであり、いわゆるストレージエンジンとは、データをどのように保存し、保存されたデータにインデックスをどのように作成し、データをどのように更新、照会するかなどの技術的な実装方法を指します。
インデックスの分類#
4 つの観点からインデックスを分類します。
- 「データ構造」による分類：B+tree インデックス、Hash インデックス、Full-text インデックス。
- 「物理ストレージ」による分類：クラスタインデックス（主キーインデックス）、セカンダリインデックス（補助インデックス）。
- 「フィールド特性」による分類：主キーインデックス、一意インデックス、通常インデックス、プレフィックスインデックス。
- 「フィールド数」による分類：単一列インデックス、複合インデックス。
データ構造による分類#
データ構造の観点から見ると、MySQL の一般的なインデックスには B+Tree インデックス、HASH インデックス、Full-Text インデックスがあります。
B+Tree インデックス#
B+Tree インデックスタイプは、MySQL ストレージエンジンで最も多く採用されているインデックスタイプです。
テーブルを作成する際、InnoDB ストレージエンジンは異なるシナリオに応じて異なる列をインデックスとして選択します：
- 主キーがある場合、デフォルトで主キーをクラスタインデックスのインデックスキー（key）として使用します；
- 主キーがない場合、最初の NULL 値を含まない一意の列をクラスタインデックスのインデックスキー（key）として選択します；
- 上記の 2 つがない場合、InnoDB は自動的に暗黙の自動増分 ID 列をクラスタインデックスのインデックスキー（key）として生成します。
作成された主キーインデックスとセカンダリインデックスはデフォルトで B+Tree インデックスを使用します。
B+Tree は多分木の一種で、葉ノードにのみデータが格納され、非葉ノードにはインデックスのみが格納されます。また、各ノード内のデータは主キー順に格納されており、各葉ノードには次の葉ノードと前の葉ノードを指す 2 つのポインタがあり、双方向リストを形成しています。
B+Tree は千万レベルのデータを3-4 層の高さで満たすことができ、これは千万レベルのテーブルから目標データを照会するのに最大3-4 回のディスク I/Oが必要であることを意味します。したがって、B+Tree は B ツリーや二分木と比較して、最大の利点は照会効率が非常に高いことです。データ量が非常に大きい場合でも、データを照会する際のディスク I/O は依然として 3-4 回に保たれます。
なぜ MySQL InnoDB は B+tree をインデックスのデータ構造として選択したのか？
1、B+Tree vs B Tree
B+Tree は葉ノードにのみデータを格納し、B ツリーの非葉ノードもデータを格納するため、B+Tree の単一ノードのデータ量は小さく、同じディスク I/O 回数でより多くのノードを照会できます。
さらに、B+Tree の葉ノードは双方向リスト接続を採用しており、MySQL で一般的な範囲に基づく順次検索に適していますが、B ツリーはこれを実現できません。
2、B+Tree vs Hash
Hash は等値検索の際に非常に高速で、検索の複雑さは O (1) です。
しかし、Hash テーブルは範囲検索には適しておらず、等値検索により適しています。これが B+Tree インデックスが Hash テーブルインデックスよりも広範な適用シーンを持つ理由です。
物理ストレージによる分類#
クラスタインデックス（主キーインデックス）、セカンダリインデックス（補助インデックス）に分かれます。
この 2 つの違い：
- 主キーインデックスの B+Tree の葉ノードには実際のデータが格納されており、すべての完全なユーザーレコードが主キーインデックスの B+Tree の葉ノードに格納されています；
- セカンダリインデックスの B+Tree の葉ノードには主キー値が格納されており、実際のデータではありません。
カバリングインデックス：したがって、照会時にセカンダリインデックスを使用し、照会するデータがセカンダリインデックスで照会できる場合、テーブルに戻る必要はありません。このプロセスはカバリングインデックスです。
テーブルに戻る：照会するデータがセカンダリインデックスにない場合、最初にセカンダリインデックスを検索し、対応する葉ノードを見つけ、主キー値を取得した後、主キーインデックスを再度検索してデータを照会します。このプロセスはテーブルに戻るです。
フィールド特性による分類#
フィールド特性の観点から見ると、インデックスは主キーインデックス、一意インデックス、通常インデックス、プレフィックスインデックスに分かれます。
1. 主キーインデックス
主キーインデックスは主キー列に基づいて作成されるインデックスであり、通常はテーブルを作成する際に一緒に作成されます。テーブルには最大 1 つの主キーインデックスしか存在できず、インデックス列の値には NULL 値を許可しません。
2. 一意インデックス
一意インデックスは UNIQUE フィールドに基づいて作成されるインデックスであり、テーブルには複数の一意インデックスが存在できます。インデックス列の値は一意でなければならず、NULL 値を許可します。
3. 通常インデックス
通常インデックスは通常のフィールドに基づいて作成されるインデックスであり、フィールドが主キーである必要も、UNIQUE である必要もありません。
4. プレフィックスインデックス
プレフィックスインデックスは、文字型フィールドの最初の数文字に基づいて作成されるインデックスであり、フィールド全体に基づいて作成されるインデックスではありません。プレフィックスインデックスは、char、varchar、binary、varbinary 型の列に作成できます。
プレフィックスインデックスを使用する目的は、インデックスが占有するストレージスペースを減らし、照会効率を向上させることです。
フィールド数による分類#
フィールド数の観点から見ると、インデックスは単一列インデックス、複合インデックス（複合インデックス）に分かれます。
- 単一列に基づいて作成されたインデックスは単一列インデックスと呼ばれ、主キーインデックスのようなものです；
- 複数列に基づいて作成されたインデックスは複合インデックスと呼ばれます；
複合インデックス
複数のフィールドを組み合わせて 1 つのインデックスを作成することを複合インデックスと呼びます。
たとえば、商品テーブルの product_no と name フィールドを組み合わせて複合インデックス (product_no, name) を作成する方法は次のとおりです：
CREATE INDEX index_product_no_name ON product(product_no, name);
複合インデックスの非葉ノードは、2 つのフィールドの値を B+Tree の key 値として使用します。複合インデックスでデータを照会する際、最初に product_no フィールドを比較し、product_no が同じ場合に name フィールドを比較します。
つまり、複合インデックスでの B+Tree の照会は、最初に product_no でソートし、次に product_no が同じ場合に name フィールドでソートされます。
したがって、複合インデックスを使用する際には、最左一致の原則が存在し、最左優先の方法でインデックスの一致を行います。複合インデックスを使用して照会する際に「最左一致の原則」に従わない場合、複合インデックスは無効になり、インデックスの迅速な照会特性を利用できなくなります。
複合インデックスの範囲検索#
複合インデックスの最左一致の原則は、右に一致し続け、範囲検索に遭遇すると一致を停止します。つまり、範囲検索のフィールドは複合インデックスを使用できますが、範囲検索フィールドの後のフィールドは複合インデックスを使用できません。
複合インデックスの最左一致の原則は、範囲検索（例：>、<）に遭遇したときに一致を停止します **。つまり、範囲検索のフィールドは複合インデックスを使用できますが、範囲検索フィールドの後のフィールドは複合インデックスを使用できません。注意すべきは、>=、<=、BETWEEN、like プレフィックス一致の範囲検索では一致を停止しないことです。前述の 4 つの例で説明しました。
インデックスプッシュダウン#
インデックスプッシュダウン最適化（index condition pushdown）は、複合インデックスの走査中に、複合インデックスに含まれるフィールドに対して最初に判断を行い、条件を満たさないレコードを直接フィルタリングして、テーブルに戻る回数を減らします。
複合インデックス（a, b）に対して、select * from table where a > 1 and b = 2文を実行する際、a フィールドのみがインデックスを使用できる場合、複合インデックスの B+Tree で条件を満たす最初の主キー値（ID が 2）を見つけた後、b が = 2 かどうかを判断し、条件を満たさない場合は直接フィルタリングします。
インデックスの区別度#
前方のフィールドがインデックスフィルタリングに使用される確率は高く、実際の開発作業では複合インデックスを作成する際に、区別度の高いフィールドを前に配置する必要があります。これにより、区別度の高いフィールドがより多くの SQL で使用される可能性が高くなります。
区別度は、特定のフィールド column の異なる値の数「割る」テーブルの総行数であり、計算式は次のとおりです：
たとえば、性別の区別度は非常に小さく、インデックスを作成するのに適していないか、複合インデックス列の前の位置に配置するのに適していませんが、UUID のようなフィールドはインデックスを作成するのに適しているか、複合インデックス列の前の位置に配置するのに適しています。
複合インデックスによるソート#
次の SQL に対して、インデックスを使用して照会効率を向上させるにはどうすればよいでしょうか？
select * from order where status = 1 order by create_time asc
より良い方法は、status と create_time 列に複合インデックスを作成することです。これにより、MySQL データベースでファイルソートが発生するのを回避できます。
照会時に status のインデックスのみを使用する場合でも、この文は create_time でソートする必要があります。この場合、ファイルソート filesort を使用する必要があり、SQL 実行計画の Extra 列に Using filesort が表示されます。
したがって、インデックスの順序性を利用するために、status と create_time 列に複合インデックスを作成します。これにより、status でフィルタリングされたデータは create_time でソートされ、ファイルソートを回避し、照会効率が向上します。
インデックスを最適化する方法は？#
いつインデックスを作成する必要があるか / 不要か？#
インデックスの最大の利点は照会速度を向上させることですが、インデックスには欠点もあります。たとえば：
- 物理スペースを占有する必要があり、数が多いほど占有スペースが大きくなります；
- インデックスの作成と維持には時間がかかり、この時間はデータ量の増加に伴い増加します；
- インデックスの増減改訂の効率が低下します。なぜなら、インデックスを維持するために B + ツリーは動的に維持する必要があるからです。
いつインデックスが適用されるか？#
- フィールドに一意性制約がある場合、たとえば商品コード；
- WHERE 照会条件で頻繁に使用されるフィールド。これにより、テーブル全体の照会速度が向上します。照会条件が 1 つのフィールドでない場合は、複合インデックスを作成できます。
- GROUP BY および ORDER BY で頻繁に使用されるフィールド。これにより、照会時に再度ソートを行う必要がなくなります。なぜなら、インデックスを作成した後、B+Tree 内のレコードはすでにソートされているからです。
いつインデックスを作成する必要がないか？#
- WHERE 条件、GROUP BY、ORDER BY で使用されないフィールド。インデックスの価値は迅速な位置特定にあります。位置特定に役立たないフィールドは通常インデックスを作成する必要がありません。なぜなら、インデックスは物理スペースを占有するからです。
- フィールドに大量の重複データが存在する場合、インデックスを作成する必要はありません。たとえば性別フィールドには男女しかない場合、データベーステーブルに男女のレコードが均等に分布している場合、どの値を検索しても半分のデータが得られる可能性があります。このような場合、インデックスを作成しない方が良いです。なぜなら、MySQL にはクエリオプティマイザがあり、特定の値がテーブルのデータ行に出現する割合が非常に高い場合、通常はインデックスを無視して全表スキャンを行うからです。
- テーブルのデータが少ない場合、インデックスを作成する必要はありません；
- 頻繁に更新されるフィールドにはインデックスを作成しないでください。たとえば、e コマースプロジェクトのユーザー残高にインデックスを作成しないでください。なぜなら、インデックスフィールドが頻繁に変更され、B + ツリーの順序性を維持する必要があるため、インデックスを頻繁に再構築する必要があり、このプロセスはデータベースのパフォーマンスに影響を与えるからです。
インデックスを最適化する方法は？#
プレフィックスインデックスの最適化#
プレフィックスインデックスは、特定のフィールドの文字列の最初の数文字を使用してインデックスを作成することを意味しますが、なぜプレフィックスを使用してインデックスを作成する必要があるのでしょうか？
プレフィックスインデックスを使用する目的は、インデックスフィールドのサイズを小さくし、1 つのインデックスページに格納できるインデックス値を増やし、インデックスの照会速度を効果的に向上させることです。大きな文字列のフィールドをインデックスとして使用する場合、プレフィックスインデックスを使用することでインデックス項目のサイズを小さくするのに役立ちます。
ただし、プレフィックスインデックスには一定の制限があります。たとえば：
- order by ではプレフィックスインデックスを使用できません；
- プレフィックスインデックスをカバリングインデックスとして使用することはできません；
カバリングインデックスの最適化#
商品名、価格を照会するだけで、テーブルに戻るのを避ける方法はありますか？
「商品 ID、名称、価格」を 1 つの複合インデックスとして作成できます。インデックスにこれらのデータが存在する場合、照会は主キーインデックスを再度検索せず、テーブルに戻るのを避けます。
主キーインデックスは自動増分が最適#
自動増分主キーを使用する場合、新しいデータが順番に現在のインデックスノードの位置に追加され、既存のデータを移動する必要がありません。ページが満杯になると、自動的に新しいページが開かれます。なぜなら、新しいレコードを挿入するたびに、追加操作であり、データを再度移動する必要がないため、このデータ挿入方法の効率は非常に高いからです。
非自動増分主キーを使用する場合、主キーのインデックス値がランダムであるため、新しいデータを挿入するたびに、既存のデータページの中間の位置に挿入される可能性があります。これにより、他のデータを移動して新しいデータの挿入を満たす必要が生じ、場合によっては 1 つのページから別のページにデータをコピーする必要があります。この状況はページ分割と呼ばれます。ページ分割は大量のメモリの断片化を引き起こし、インデックス構造が緩くなり、照会効率に影響を与える可能性があります。
インデックスは NOT NULL に設定するのが最適#
第一の理由：インデックス列に NULL が存在すると、オプティマイザがインデックス選択を行う際により複雑になり、最適化が難しくなります。NULL を許可する列は、インデックス、インデックス統計、および値比較をより複雑にします。たとえば、インデックス統計を行う際、count は NULL の行を省略します。
第二の理由：NULL 値は意味のない値ですが、物理スペースを占有します。したがって、ストレージスペースの問題を引き起こします。InnoDB がレコードを保存する際、テーブルに NULL を許可するフィールドが存在する場合、行形式には少なくとも 1 バイトのスペースが NULL 値リストを保存するために使用されます。
インデックスの無効化を防ぐ#
インデックスが無効化される状況：
- 左または左右の曖昧な一致を使用する場合、つまり like % xx または like % xx% の 2 つの方法は、インデックスを無効にします；
- 照会条件でインデックス列に計算、関数、型変換操作を行った場合、これらの状況はインデックスを無効にします；
- 複合インデックスを正しく使用するには最左一致の原則に従う必要があります。つまり、最左優先の方法でインデックスの一致を行います。そうでない場合、インデックスが無効になります。
- WHERE 句で、OR の前の条件列がインデックス列であり、OR の後の条件列がインデックス列でない場合、インデックスは無効になります。
どの count が最もパフォーマンスが良いか？#
COUNT (*) = COUNT (1) < COUNT (フィールド)（セカンダリインデックスが存在する）< COUNT (主キー列)（主キーインデックスのみ存在する）< COUNT（非主キー列）（セカンダリインデックスが存在しない）
count とは何か？#
count () は集約関数であり、関数の引数はフィールド名だけでなく、他の任意の式も使用できます。この関数の目的は、照会条件に一致するレコードの中で、関数が指定した引数が NULL でないレコードがいくつあるかをカウントすることです。
count (主キー列) の実行プロセスはどのようなものか？#
count 関数を使用してレコードの数をカウントする際、MySQL のサーバーレイヤーは count という名前の変数を維持します。
サーバーレイヤーは InnoDB から 1 つのレコードをループして読み取り、count 関数が指定した引数が NULL でない場合、count 変数を 1 増やします。照会に一致するすべてのレコードが読み取られるまでループを続け、最後に count 変数の値をクライアントに送信します。
InnoDB は B + ツリーを使用してレコードを保存し、インデックスのタイプによってクラスタインデックスとセカンダリインデックスに分かれます。これらの違いは、クラスタインデックスの葉ノードには実際のデータが格納されており、セカンダリインデックスの葉ノードには主キー値が格納されていることです。
テーブルに主キーインデックスのみが存在し、セカンダリインデックスがない場合、InnoDB はクラスタインデックスをループして走査し、読み取ったレコードをサーバーレイヤーに返します。その後、レコード内の id 値を読み取り、id 値が NULL でないかを判断します。NULL でない場合、count 変数を 1 増やします。
しかし、テーブルにセカンダリインデックスがある場合、InnoDB がループして走査する対象はクラスタインデックスではなく、セカンダリインデックスです。
これは、同じ数のセカンダリインデックスレコードがクラスタインデックスレコードよりも少ないストレージスペースを占有できるため、セカンダリインデックスツリーはクラスタインデックスツリーよりも小さく、したがって、セカンダリインデックスを走査する I/O コストはクラスタインデックスを走査する I/O コストよりも小さいため、「オプティマイザ」は優先的にセカンダリインデックスを選択します。
count (1) と count (*) の実行プロセスはどのようなものか#
count (1) と count () の実行プロセスはほぼ同じです。なぜなら、count () = count (0) だからです。
InnoDB はクラスタインデックス（主キーインデックス）をループして走査し、読み取ったレコードをサーバーレイヤーに返しますが、レコード内のフィールド値を読み取ることはありません。なぜなら、count 関数の引数は 1 であり、フィールドではないため、レコード内のフィールド値を読み取る必要がないからです。引数 1 は明らかに NULL ではないため、サーバーレイヤーは InnoDB から 1 つのレコードを読み取るたびに count 変数を 1 増やします。
count (1) は count (主キー列) よりも 1 つのステップが少ないことがわかります。つまり、レコード内のフィールド値を読み取る必要がないため、通常 count (1) の実行効率は count (主キー列) よりもわずかに高いと言われます。
count (フィールド) の実行プロセスはどのようなものか#
count (フィールド) の実行効率は最も低いです。なぜなら、全表スキャンを行う必要があるからです。
MYISAM と InnoDB の違い
MyISAM エンジンを使用する場合、count 関数の実行には O (1) の複雑さが必要です。これは、各 MyISAM データテーブルに meta 情報があり、row_count 値が保存されているため、テーブルレベルのロックが一貫性を保証し、row_count 値を直接読み取ることで count 関数の実行結果が得られるからです。
count (*) を最適化する方法は？#
大規模なテーブルのレコード統計に直面した場合、count の実行効率は非常に低く、たとえばテーブル t_order には 1200 万以上のレコードがあり、セカンダリインデックスも作成されていますが、select count(*) from t_orderを実行するのに約 5 秒かかります！
効率を向上させるにはどうすればよいでしょうか？
第一の方法、近似値
ビジネスが個数の統計に対して非常に正確である必要がない場合、たとえば検索エンジンが検索キーワードを検索する際に、検索結果の件数が大まかな値である場合があります。
この場合、show table status や explain コマンドを使用してテーブルを推定できます。
第二の方法、追加のテーブルにカウント値を保存する
テーブルのレコード総数を正確に取得したい場合、このカウント値を別のカウントテーブルに保存できます。
データテーブルにレコードを挿入するたびに、カウントテーブルのカウントフィールドを + 1 します。つまり、新規追加および削除操作の際に、このカウントテーブルを追加で維持する必要があります。