MySQL のインデックス作成と最適化に関する知識ポイントは何ですか?

インデックスとは何ですか?

• インデックスは、MySQL が効率的なクエリを実行するのに役立つデータ構造です。本の目次と同様に、クエリを高速化できます

インデックスの構造?

インデックスには、B-Tree インデックスとハッシュ インデックスを含めることができます。インデックスはストレージ エンジンに実装されます

InnoDB/MyISAM は B ツリー インデックスのみをサポートします

メモリ/ヒープは B ツリー インデックスとハッシュ インデックスをサポートします

• #B-Tree
  B-Tree は、ディスク操作に非常に適したデータ構造です。これは、多方向のバランスの取れた検索ツリーです。その高さは通常 2 ～ 4 で、その非リーフ ノードとリーフ ノードにデータが格納されます。そのすべてのリーフ ノードは同じレイヤー上にあります。下の図は B ツリーです

## B ツリー: B ツリーは、B ツリー最適化に基づくタイプです。 B ツリーとの主な違いは、B ツリーのすべてのデータがリーフ ノードに格納され、リーフ ノードがリンク リストによって結合されていることです。次の図は B ツリーです。

#InnoDB のページのサイズは 16 KB (ページは B ツリー上のノードです)テーブルの主キーが INT で、サイズが 4 バイトの場合、そのノードは 4K のキー値も格納できます。ポインターとキー値の両方が同じサイズを占めると仮定すると、高さ 3 の B ツリーは次のようになります。第 2 層には 2048 ノード、第 3 層には 2048 ノードがあり、層内のリーフ ノードの数は 2048*2048 = 4194304 で、1 つのノードは 16KB なので、合計 67108864KB、つまり 65536MB、つまり 64G のデータを格納できます。収容されました。

リーフノードはリンクリストでつながっているので、インデックス列で並べるとデフォルトでソートされるので効率が非常に高くなります。

MyISAM インデックス

MyISAM インデックスとデータは別々に保存されます。 MyISAM の主キー インデックスでは、レコード アドレスが B ツリーのリーフ ノードに格納されるため、MyISAM はインデックス クエリを介して 2 回の IO を実行する必要があります。
• ##MyISAM の補助インデックスは主キー インデックスと同じです。唯一の違いは、補助インデックスのキーは繰り返すことができますが、主キー インデックスのキーは繰り返すことができないことです。

  
  

InnoDB インデックス

InnoDB データとインデックスは一緒に保存され、クラスター化インデックスとも呼ばれます。データは主キーによってインデックス付けされ、主キー インデックス B ツリーのリーフ ノードに保存されます。

InnoDB の主キー インデックスでは、データはすでにリーフ ノードに含まれています。つまり、インデックスとデータが一緒に格納されており、これがクラスター化インデックスです。

• InnoDB の補助インデックスでは、主キーの値はアドレスではなくリーフ ノードに格納されます。補助インデックスを使用するには、2 つの検索が必要です。
  
  

InnoDB と MyISAM インデックスの違い:

InnoDB はクラスター化インデックスを使用し、その主キー インデックスはデータを直接保存します。リーフ ノード。補助インデックス内のリーフ ノードには、主キーの値が格納されます。

MyISAM は非クラスター化インデックスを使用します。データとインデックスは同じファイル内にありません。主キーインデックスのリーフノードには行レコードが配置されているアドレスが格納され、補助インデックスのリーフノードにはレコードが配置されているアドレスも格納されます。ただし、補助インデックスのキーは繰り返すことができますが、補助インデックスのキーは主キー インデックスを繰り返すことはできません

• 質問
:

• 
  

なぜ InnoDB は繰り返さないのでしょうか長すぎるフィールドを主キーとして使用します

?

主キーが長すぎると、補助インデックスが多くのスペースを占有することになります

• InnoDB で自動インクリメント主キーの使用が推奨される理由

  ? 自動インクリメント主キーを使用する場合、新しいレコードが挿入されるたびに、新しいレコードは現在のインデックス ノードの後続の位置に順次追加されます。1 つのページがいっぱいになると、新しいページが開きますそのため、インデックス構造は非常にコンパクトであり、既存のデータを挿入するたびに移動する必要がなく、非常に効率的です。自動インクリメント主キーを使用しない場合、新しいレコードを挿入するたびに挿入位置を選択する必要があり、データの移動が必要になる場合があるため、効率が高くなく、インデックス構造もコンパクトになりません
  

• B ツリーではなく B ツリーを使用する理由

  
  

  インデックスはどこに存在するのでしょうか?

• インデックス自体も比較的大きく、通常はディスクに保存されます。インデックスとデータは別々に保存される場合もあり (MyISAM の非クラスター化インデックス)、一緒に保存される場合もあります (InnoDB の場合)。クラスター化インデックス)

#インデックスの長所と短所は何ですか?

• #利点

##IO コストの削減、データ クエリの効率の向上

• 並べ替えコストの削減 (インデックス付き列は自動的に並べ替えられ、order by を使用すると効率が大幅に向上します)

• 欠点

• #インデックスは追加の記憶領域を占有します

• インデックスはテーブル データの更新効率を低下させます。操作を追加、削除、または変更する場合は、データを保存するだけでなく、対応するインデックスも更新する必要があります

#インデックスの分類
#単一列インデックス

• ##主キー インデックス

• #一意のインデックス

• 通常のインデックス

• 複合インデックス

• インデックスは

を使用してインデックスを作成します

•  CREATE INDEX index_name ON table_name(col_name);
  -- 或者
  ALTER TABLE table_name ADD INDEX index_name(col_name)

インデックスの削除

• DROP INDEX index_name ON table_name;

#インデックス作成が必要なシナリオ

• #頻繁にクエリ条件として使用される列にはインデックスを付ける必要があります

#複数テーブルの関連付けでは、関連付けられたフィールドにインデックスを付ける必要があります
クエリの並べ替えフィールドにはインデックスを作成する必要がありますインデックスの構築
• インデックス作成が適さないシナリオ

• 書き込みが多く読み取りが少ないテーブルはインデックス作成には適していません

頻繁に更新されるフィールドはインデックスの構築には適していません
#実行計画の説明
• ##既存のユーザー テーブルがあり、そのインデックスは次のとおりです。

##名前、年齢の 3 つのフィールドとアドレスは結合インデックスとして使用されます。

Explain を使用して、特定の SQL ステートメントのパフォーマンス分析を実行できます

explain select * from user where name = 'am';



possible_keys
使用される可能性のあるインデックス
key

実際に使用されるインデックス

key_lenクエリに使用されるインデックスの長さ

ref 同等のクエリの場合、const

rowsスキャンされる推定行数 (正確な値ではありません)

extra


using where
などの追加情報は、ストレージ エンジンから返された結果が SQL 層レイヤーでもフィルタリングされる必要があることを示します

usingindex

は、テーブルにクエリを戻す必要がないことを示します。通常、この値はカバー インデックスを使用する場合に当てはまります。インデックスをカバーするとは、選択内の列がすべてインデックス列であることを意味します。テーブルに返す必要のないクエリは、補助インデックスを経由してインデックス列の値を直接取得できることを意味し、レコードをフェッチするために主キー インデックスに移動する必要はありません

• 
  インデックス条件の使用

  MySQL 5.6.x 以降では、チェック条件をストレージ エンジン層にプッシュできる ICP 機能 (インデックス条件プッシュダウン) がサポートされています。条件を満たさないレコードは、 SQL レイヤーのレイヤー フィルタリングにより、ストレージ エンジン レイヤーによってスキャンされる行数が削減されます。

• filesort を使用する
  は並べ替えできません 使用されるインデックス

• ##type
  

#system: テーブルにデータが 1 行しかないか、テーブルが空です



const: 一意のインデックスまたは主キー インデックスを使用し、where およびその他の値を使用してクエリを実行します返されるレコードは 1 行であり、一意のインデックス スキャンとも呼ばれます

• 
  

##ref: 一意でないインデックスの場合、同等の where 条件または左端のプレフィックス ルールを使用したクエリ。

• #次は、満たされる左端のプレフィックス ルールです。つまり、idx_name_age_add の場合、左端のプレフィックスは満たされ、最初のインデックスは name

range: インデックス範囲スキャン、>、



##このような場合、ワイルドカード文字 % を先頭に置くことはできないことに注意してください。そうしないとテーブル全体のスキャンが発生します。



##index ：





#すべてインデックスと完全には一致しませんが、テーブルにクエリを戻す必要はありません。テーブル全体をスキャンし、SQL レイヤーの要件を満たすレコードをフィルタリングします。

索引使用规范（索引失效分析）

1. 全值匹配
   在索引列上使用等值查询

explain select * from user where name = 'y' and age = 15;

2. 最左前缀

组合索引中，查询条件要从组合索引的最左列开始，如上述example中组合索引idx_name_age_add，是建立在三个列name，age，address的，若跳过name，直接用age查询，则会变为全表扫描

explain select * from user where age = 15;

3. 不要在索引列上做计算

4. 范围条件右侧的索引列会失效

看到第一个SQL语句，没有用上addresss索引

5. 尽量使用覆盖索引

explain select name,age from user where name = 'y' and age = 1;

可以避免回表查询

6. 索引字段不要使用不等（!= 或 ）,不要判断null（is null/ is not null）
会导致索引失效，转为全表扫描

7. 索引字段上使用like时，不要以%开头

8. 索引字段如果是字符串，记得加单引号

9. 索引字段不要用or

例子总结：

以上がMySQL のインデックス作成と最適化に関する知識ポイントは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。