MySQL インデックスに関するヒントのまとめ

1. インデックスの基礎知識

1.1 インデックスの利点

1. サーバーがスキャンする必要があるデータの量を大幅に削減し、データベースの取得を高速化します。
2. サーバーがソートや一時テーブルを回避するのに役立ちます
3. ランダムIOをシーケンシャルIOに変換する

1.2 インデックスの有用性

1. WHERE句に一致する行を素早く見つける
2. 行を考慮から除外します。複数のインデックスから選択できる場合、MySQL は通常、最も少ない行を見つけるインデックスを使用します。
3. テーブルに複数列のインデックスがある場合、オプティマイザーはインデックスの左端のプレフィックスを使用して行を検索できます。
4. テーブル結合がある場合、他のテーブルから行データを取得します
5. 特定のインデックス列の最小値または最大値を見つける
6. 利用可能なインデックスの左端のプレフィックスでソートまたはグループ化が行われると、テーブルはソートされグループ化されます。
7. 場合によっては、データ行をクエリせずに値を取得するようにクエリを最適化できます。

1.3 インデックスの分類

データベースはデフォルトでインデックスを作成しますが、主キーは一意であり空ではないため、主キーに対してではなく、一意キーに対してインデックスを作成します。

• 主キー インデックス: null 値を許可しない特別な一意のインデックスです。 （主キー制約は主キーインデックスです）
• 一意のインデックス: インデックス列の値は一意である必要がありますが、null 値も許可されます。
• 通常のインデックス: MySQL の基本的なインデックス タイプで、制限はなく、インデックスが定義されている列に重複値や null 値を挿入できます。純粋にデータ クエリを高速化するためです。
• フルテキスト インデックス: MyISAM エンジンでのみ使用でき、CHAR、VARCHAR、および TEXT タイプのフィールドでのみ使用できます。
  >フルテキストインデックスとは？ 大量のテキストの中から、特定のキーワードを使って、そのフィールドが属するレコード行を見つけられる機能です。例えば、「LOL LPL 牧小农」というレコードがあった場合、「牧小农」と入力すると、そのレコードを見つけられる場合があります。これは可能性の 1 つです。フルテキスト インデックスの使用には多くの詳細が含まれるため、概要のみを知っておく必要があります。一般的な開発では、フルテキスト インデックスは多くの物理スペースを占有し、レコードの変更可能性を低下させるため、ほとんど使用されません。
• 複合インデックス: テーブル内の複数のフィールドの組み合わせに基づいて作成されるインデックス。このインデックスは、これらのフィールドの左側のフィールドがクエリ条件で使用される場合にのみ使用されます。複合インデックスを使用する場合は、最も左のプレフィックス セットに従います。

たとえば、このインデックスは、id、name、age の 3 つのフィールドで構成されています。インデックス行は、id/name/age の順序で保存されます。インデックスは、(id、name、age)、(id、name)、または (id) のフィールドの組み合わせをインデックス化できます。クエリ対象のフィールドがインデックスの左端のプレフィックスを構成していない場合、インデックスは使用されません。たとえば、年齢または (名前、年齢) の組み合わせは、インデックスを使用してクエリされません。

1.4 インタビューの専門用語

テーブル リターン: データベースは、インデックス (主キーではない) に基づいて指定されたレコードが配置されている行を見つけた後、主キーに基づいてデータ ブロックから再度データを取得する必要があります。これをテーブル リターンと呼びます。

カバーインデックス: 私が書いた記事を読む: SQL の質問で 3 つの面接に落ちた - SQL パフォーマンスの最適化

左端の一致: 結合インデックスでは、SQL ステートメントが結合インデックスの左端のインデックスを使用する場合、この SQL ステートメントはこの結合インデックスを一致に使用できます。範囲クエリ (>、<、between、like) が検出されると、一致は停止します。

select * from t where a=1 and b=1 and c =1; #このようにして、定義されたインデックス (a,b,c) を使用し、a,b,c を使用することができます。
select * from t where a=1 and b=1; #このようにして、定義されたインデックス(a,b,c)を使用してa,bを使用することができます。
select * from t where b=1 and a=1; #このようにして、定義されたインデックス (a, b, c) を使用し、a と c を使用できます (MySQL にはクエリ オプティマイザがあります)
select * from t where a=1; #このようにして、定義されたインデックス(a, b, c)を使用することもできます。
select * from t where b=1 and c=1; #定義されたインデックス (a, b, c) は使用できません
select * from t where a=1 and c=1; #この方法では、定義されたインデックス (a、b、c) を使用できますが、インデックス a のみが使用され、インデックス b と c は使用されません。

インデックス プッシュダウン: インデックス コンディション プッシュダウン (ICP) とも呼ばれるこの方法は、MySQL が提供する方法で、特定のインデックスを使用して特定のテーブルからタプルを取得します。ここでは意図的に「1」を強調していることに注意してください。これは、このようなインデックス最適化は、複数のテーブル接続ではなく、単一のテーブル スキャンに使用されるためです。正確には、インデックスを使用して単一のテーブルをスキャンしてデータを取得する方法です。

1.5 インデックスで使用されるデータ構造

1.5.1 ハッシュテーブル

欠点︰

1. ハッシュ ストレージを使用する場合、すべてのデータ ファイルをメモリに追加する必要があり、より多くのメモリ領域が消費されます。

2. すべてのクエリが等値クエリである場合、ハッシュは確かに非常に高速です。ただし、企業や実際の作業環境では、等値クエリではなく、範囲内でより多くのデータが検索されるため、ハッシュはあまり適していません。

1.5.2 二分木

デメリット:

バイナリツリーでも赤黒ツリーでも、ツリーの深さによって IO 操作の数が増え、データ読み取りの効率に影響します。

1.5.3 B+ツリー

Bツリーの機能:

1. すべてのキー値がツリー全体に分散されている

2. 検索はリーフノード以外で終了する可能性があり、キーワードセット全体で検索が実行され、バイナリ検索に近いパフォーマンスが得られます。

3. 各ノードは最大でm個のサブツリーを持つ

4. ルートノードには少なくとも2つのサブツリーがある

5. ブランチノードには少なくともm/2個のサブツリーがあります（ルートノードとリーフノードを除くすべてのノードはブランチノードです）

6. すべてのリーフノードは同じレイヤーにあり、各ノードは最大でm-1個のキーを持つことができ、昇順に並べられます。

例の図の説明:

各ノードはディスク ブロックを占有します。ノードには 2 つの昇順のキーワードと、サブツリーのルート ノードへの 3 つのポインタがあります。ポインタには、子ノードが配置されているディスク ブロックのアドレスが格納されます。 2 つのキーワードによって分割された 3 つの範囲ドメインは、3 つのポインタによって指されるサブツリーのデータの範囲ドメインに対応します。ルートノードを例にとると、キーワードは 16 と 34 であり、P1 ポインタが指すサブツリーのデータ範囲は 16 未満、P2 ポインタが指すサブツリーのデータ範囲は 16 ～ 34、P3 ポインタが指すサブツリーのデータ範囲は 34 より大きいです。

キーワード検索プロセス:

1. ルート ノードに基づいてディスク ブロック 1 を見つけ、それをメモリに読み込みます。 [ディスクI/O操作1回目]
2. 区間（16,34）内のキーワード28を比較し、ディスクブロック1のポインタP2を見つけます。
3. P2 ポインタに従ってディスク ブロック 3 を見つけ、それをメモリに読み込みます。 [ディスクI/O操作2回目]
4. 区間（25,31）内のキーワード28を比較し、ディスクブロック3のポインタP2を見つけます。
5. P2 ポインタに従ってディスク ブロック 8 を見つけ、それをメモリに読み込みます。 [ディスクI/O操作3回目]
6. ディスク ブロック 8 のキーワード リストでキーワード 28 を見つけます。

欠点:

• 各ノードにはキーがあり、データも含まれています。ただし、各ページの保存スペースには制限があります。データが大きい場合、各ノードに保存されるキーの数は少なくなります。
• 保存されるデータの量が多い場合、深度が大きくなり、クエリ中のディスク IO 回数が増加し、クエリのパフォーマンスに影響します。

1.6 インデックスマッチング法

完全な値の一致: 完全な値の一致とは、インデックス内のすべての列が一致することを指します。

select * from staffs where name = 'July' and age = '23' and pos = 'dev'; を説明します。

左端のプレフィックスに一致: 最初の数列のみに一致

select * from staffs where name = 'July' and age = '23'; を説明します。

select * from staffs where name = 'July'; を説明します。

列プレフィックスの一致: 列値の先頭を一致させることができます。

select * from staffs where name like 'J%'; を説明します。

select * from staffs where name like '%y'; を説明します。

一致範囲値: 特定の範囲内のデータを検索できます。

説明 select * from staffs where name > 'Mary';

列と範囲の一致は完全に一致し、別の列と一致します。最初の列全体と2番目の列の一部を照会できます。

名前が 'July' かつ年齢が 25 を超える staffs から * を選択します。

インデックスのみのクエリ: クエリを実行するときは、インデックスのみにアクセスすればよく、データ行にアクセスする必要はありません。これは基本的にカバーリング インデックスです。

説明 select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

2. ハッシュインデックス

ハッシュテーブルの実装に基づいて、インデックスのすべての列に正確に一致するクエリのみが有効になります。

MySQL では、メモリ ストレージ エンジンのみがハッシュ インデックスを明示的にサポートします。

ハッシュ インデックス自体は対応するハッシュ値のみを格納する必要があるため、インデックス構造は非常にコンパクトになり、ハッシュ インデックスの検索が非常に高速になります。

2.1 ハッシュインデックスの制限

1. ハッシュ インデックスにはハッシュ値と行ポインターのみが含まれており、フィールド値は格納されません。インデックスは、行の読み取りを回避するためにインデックス内の値を使用することはできません。
2. ハッシュインデックスデータはインデックス値の順序で保存されないため、並べ替えることはできません。
3. ハッシュ インデックスは部分的な列の一致をサポートしていません。ハッシュ インデックスは、インデックス列の内容全体を使用してハッシュ値を計算します。
4. ハッシュ インデックスは等価比較クエリをサポートしておらず、範囲クエリもサポートしていません。
5. ハッシュ インデックス データへのアクセスは、ハッシュの競合が多数発生しない限り、非常に高速です。ハッシュの競合が発生すると、ストレージ エンジンはリンク リスト内のすべての行ポインターを走査し、条件に該当するすべての行が見つかるまで行ごとに比較する必要があります。
6. ハッシュの競合が多いと、メンテナンスコストが非常に高くなります。

2.2 例

多数の URL を保存し、 select id from url where url=""

select id fom url where url="" and url_crc=CRC32("")

このクエリは、検索を完了するために非常に小さなインデックスを使用するため、パフォーマンスが高くなります。

3. 総合指数

複数の列をインデックスとして含める場合、正しい順序はインデックスのクエリによって異なることに注意することが重要であり、並べ替えやグループ化のニーズをより適切に満たす方法も考慮する必要があります。

例: 複合インデックス a、b、c を作成し、異なる SQL ステートメントでそのインデックスを使用する

4. クラスター化インデックスと非クラスター化インデックス

4.1 クラスター化インデックス

これは個別のインデックス タイプではなく、データ ストレージ方式であり、データ行と隣接するキー値がコンパクトにまとめて格納され、データ ストレージとインデックスが一緒に配置されます。インデックスを検索すると、データも検索されます。

主キーが定義されていない場合、InnoDB は代わりに一意の空でないインデックスを選択します。一意のインデックスがない場合、InnoDB は暗黙的に主キーをクラスター化インデックスとして定義します。 InnoDB は同じページ内のレコードのみをクラスター化します。隣接するキー値を含むページは離れている場合があります。

4.2 非クラスター化インデックス

データファイルはインデックスファイルとは別に保存され、データはインデックスとは別の構造に保存されます。インデックス構造のリーフノードは、データの対応する行を指します。MyISAM は、key_buffer を介してインデックスをメモリにキャッシュします。データにアクセスする必要がある場合 (インデックスを介してアクセスする場合)、メモリ内でインデックスを直接検索し、次にインデックスを介してディスク上の対応するデータを見つけます。これが、インデックスがキーバッファにヒットしない場合に速度が遅くなる理由です。

データページ内のデータはリーフノードポインターを介して検索されるため、非クラスター化インデックスは論理的な順序になっています。

5. カバーインデックス

5.1 基本的な紹介

1. インデックスにクエリする必要があるすべてのフィールドの値が含まれている場合、それをカバーリング インデックスと呼びます。
2. すべてのタイプのインデックスがカバーリング インデックスと呼ばれるわけではありません。カバーリング インデックスは、インデックス列の値を格納する必要があります。
3. ストレージによって、カバーリング インデックスの実装方法が異なります。すべてのエンジンがカバーリング インデックスをサポートしているわけではありません。メモリはカバーリング インデックスをサポートしていません。

5.2 利点

1. インデックス エントリは通常、データ行のサイズよりもはるかに小さくなります。インデックスの読み取りのみが必要な場合、MySQL はデータ アクセスの量を大幅に削減します。

2. インデックスは列の値の順序で保存されるため、IO 集中型の範囲クエリの IO は、ディスクから各データ行をランダムに読み取る場合よりもはるかに少なくなります。

3. MYISAM などの一部のストレージ エンジンは、インデックスをメモリにキャッシュするだけで、データのキャッシュはオペレーティング システムに依存します。そのため、データにアクセスするにはシステム コールが必要になり、重大なパフォーマンスの問題が発生する可能性があります。

4. カバーリングインデックスは、INNODBのクラスター化インデックスのため、INNODBテーブルに特に役立ちます。

5.3 ケースデモンストレーション

1. インデックスでカバーされたクエリが開始されると、使用しているインデックスの情報がexplainの追加列に表示され、この時点でカバーするインデックスが使用されます。

2. ほとんどのストレージ エンジンでは、カバー インデックスは、インデックス内の一部の列のみにアクセスするクエリのみをカバーできます。ただし、InnoDB のセカンダリ インデックスを使用してクエリをカバーすることで、さらに最適化を実行できます。

たとえば、actor は Innodb ストレージ エンジンを使用し、last_name フィールドにセカンダリ インデックスを持っています。このインデックスの列には主キー actor_id は含まれていませんが、actor_id に対してカバーリング クエリを実行するために使用することもできます。

6. 細部を最適化する

クエリにインデックス列を使用する場合は、式を使用せず、計算をデータベース レイヤーではなくビジネス レイヤーに配置するようにしてください。

主キー クエリはテーブル クエリをトリガーしないため、他のインデックスの代わりに主キー クエリを使用するようにしてください。

プレフィックスインデックスの使用

> 非常に長い文字列をインデックスする必要があり、インデックスが大きく遅くなる場合があります。通常、列の先頭に部分的な文字列を使用すると、インデックススペースが大幅に節約され、インデックスの効率が向上します。ただし、これによりインデックスの選択性が低下します。インデックスの選択性とは、データテーブル内のレコードの総数に対する非重複インデックス値の比率を指し、1/#Tから1の範囲です。インデックスの選択性が高くなるほど、クエリの効率が高くなります。これは、選択性の高いインデックスを使用すると、MySQL が検索時により多くの行をフィルター処理できるためです。

一般的に、列プレフィックスの選択性はクエリのパフォーマンスを満たすのに十分高いですが、BLOB、TEXT、および VARCHAR 型の列の場合、MySQL ではこれらの列の全長のインデックス作成が許可されていないため、プレフィックス インデックスを使用する必要があります。この方法を使用するコツは、高い選択性を確保するのに十分な長さのプレフィックスを選択しながら、長すぎないようにすることです。

--データ テーブルを作成します。create table citydemo(city varchar(50) not null);
citydemo(city) に挿入し、city から city を選択します。

-- 次の SQL ステートメントを 5 回繰り返します。 insert into citydemo(city) select city from citydemo;

--city テーブルの名前を更新します。update citydemo set city=(select city from city order by rand() limit 1);

--最も一般的な都市リストを見つけ、各値が45〜65回出現することを見つけます。
citydemo から count(*) を cnt,city として選択し、group by city で cnt desc limit 10 で並べ替えます。

-- 最も頻繁に出現する都市のプレフィックスを検索します。プレフィックスの 3 文字から始めて、以前よりも頻繁に出現していることを確認します。複数の文字をインターセプトして、都市が出現する回数を確認できます。select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
citydemo から count(*) を cnt、left(city,7) を pref として選択し、group by pref、order by cnt desc limit 10 を実行します。
--この時点で、プレフィックスの選択性は完全な列の選択性に近いです--別の方法を使用して完全な列の選択性を計算することもできます。プレフィックス長が7に達すると、プレフィックス長を増やしても選択性の向上が非常に小さいことがわかります。select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4、
count(distinct left(city,5))/count(*) as sel5、
count(distinct left(city,6))/count(*) as sel6、
count(distinct left(city,7))/count(*) as sel7、
count(distinct left(city,8))/count(*) as sel8 
citydemo より;

--計算が完了したら、プレフィックスインデックスを作成できます。alter table citydemo add key (city (7));

--注: プレフィックス インデックスは、インデックスを小さくして高速化するための効果的な方法ですが、欠点もあります。MySQL では、order by および group by にプレフィックス インデックスを使用できません。 

インデックススキャンを使用してソートする

MySQL には、ソートまたはインデックス順のスキャンという 2 つの方法で順序付けられた結果を生成する方法があります。explain の type 列の値が index の場合、MySQL はインデックス スキャンを使用してソートを実行します。
インデックス自体のスキャンは、1 つのインデックス レコードから次のレコードに移動するだけなので高速です。ただし、インデックスがクエリに必要なすべての列をカバーしていない場合は、インデックス レコードがスキャンされるたびに、テーブル内の対応する行をクエリする必要があります。これは基本的にランダム IO であるため、インデックス順にデータを読み取ると、通常、順次フル テーブル スキャンよりも遅くなります。
MySQL では、行の並べ替えと検索に同じインデックスを使用できます。可能であれば、インデックスは両方のタスクを満たすように設計する必要があります。
MySQL は、インデックス列の順序が order by 句の順序とまったく同じで、すべての列が同じ方法でソートされている場合にのみ、インデックスを使用して結果をソートできます。クエリで複数のテーブルを関連付ける必要がある場合、order by 句によって参照されるすべてのフィールドが最初のテーブルのものである場合にのみ、インデックスを使用してソートできます。 order by 句の制限は、検索クエリの制限と同じです。インデックスの左端のプレフィックスを満たす必要があります。そうでない場合、MySQL は順次操作を実行する必要があり、インデックスを使用して並べ替えることができません。

union all、in、またはインデックスを使用できますが、in が推奨されます。

範囲列ではインデックスを使用できます。範囲条件は <、> です。範囲列ではインデックスを使用できますが、範囲列に続く列ではインデックスを使用できません。インデックスは最大 1 つの範囲列に対して使用できます。

強制型変換はテーブル全体をスキャンします

テーブルuser(id int,name varchar(10),phone varchar(11))を作成します。
テーブル user を変更し、インデックス idx_1(phone) を追加します。
select * from user where phone=13800001234; を説明します (インデックスはトリガーされません)
select * from user where phone='13800001234'; （トリガーインデックス）を説明します。

頻繁に更新され、データの差別化が低いフィールドにインデックスを作成することはお勧めできません。

更新により B+ ツリーが変更されます。頻繁に更新されるフィールドにはインデックスを作成することをお勧めしますが、これによりデータベースのパフォーマンスが大幅に低下します。
性別のようにあまり差がない属性の場合、インデックスを作成しても意味がなく、データを効果的にフィルタリングすることはできません。
一般的に、識別度が80%を超えるとインデックスを作成できます。識別度はcount(distinct(列名))/count(*)で計算できます。

インデックスを作成する列では null 値が許可されないため、予期しない結果が発生する可能性があります。

テーブルを結合する必要がある場合、結合する必要があるフィールドは同じデータ型である必要があるため、3 つ以上のテーブルを使用しないことをお勧めします。

可能な限り制限を使用する

1 つのテーブル上のインデックスの数を 5 以下に制限することをお勧めします。

単一インデックスフィールドの数は 5 を超えることはできません (結合インデックス)

インデックスを作成するときは、次の誤解を避ける必要があります。

> インデックスは多ければ多いほど良い（間違い）

> 時期尚早な最適化、システムを理解せずに最適化する（エラー）

要約する

これで、MySQL インデックスの知識のヒントに関するこの記事は終了です。より関連性の高い MySQL インデックスの知識コンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM を応援していただければ幸いです。