MySQLインデックス基礎編

MySQLインデックス
インデックスは何ですか?
インデックスは、MySQLがデータを効率的に取得するのに役立つデータ構造です.
すなわち、インデックスはデータ構造です.
並べ替えられた高速検索データ構造と理解できる
インデックスの目的
辞書のような検索効率の向上を目的としています
インデックスの最下位実装

B Tree

B+Tree

full-text

Hash

R-Tree

一般的にデフォルトはBツリー(マルチツリー+チェーンテーブル)
インデックスに適したシーン
複数クエリー、変更の少ないシーン
≪問合せ時｜Query Time｜oem_src≫:データベース内のフィールドに索引を作成した場合、次回このフィールドを問合せする必要がある場合は、索引から直接問合せを行い、無秩序な全表からの問合せを回避して問合せ速度を速めます.
変更時:データベース内のフィールドにインデックスを作成した場合、次回データベースを変更する必要がある場合は、データベース内の実際のデータを変更するとともに、対応するインデックスを変更して、インデックスが正しい方向に向くようにする必要があります.
インデックス分類

単一値インデックスデータベーステーブル内の単一フィールドに対するインデックス

ユニークインデックス作成インデックスのフィールドは、候補キーである(すなわち、インデックス作成フィールドの値がテーブルに重複しない)

.

複合インデックスデータベーステーブル内の複数のフィールドに対するインデックス作成

インデックスを作成する必要がある状況は?

プライマリ・キー自動インデックス作成

クエリー条件として頻繁に使用されるフィールドは、

を作成する必要があります.

クエリー内の他のテーブルに関連付けられたフィールド.外部キーはインデックス

を確立する必要があります.

頻繁に更新されるフィールドは、インデックス

を確立するべきではありません.

where条件で使用できないデータはインデックス

を確立すべきではない.

単一値インデックスと組合せインデックス?結合インデックス

を優先的に作成

クエリーでソートされたフィールド

クエリの統計またはグループ化フィールド

インデックスを作成する必要がない状況はどれですか?

よく添削する表

データ重複分布平均フィールド

SQLの分析方法

Explain+SQL文

ID:IDが大きいフィールドを優先的に実行し、IDは同じ順序で

を実行する.

select_type

SIMPLE簡単なSQLクエリー

PRIMARY複雑クエリの最外層はPRIMARY

である.

SUBQUERYサブクエリ

DERIVED派生クエリー(テンポラリ・テーブル)

UNION連合クエリーの後のSELECT

UNIOnRESULT連携クエリの結果をSELECT

table:テーブルのソース

type:クエリータイプ、最適から最悪のソート:

systemテーブルには1行の記録(システムテーブル)しかありません.これはconstの特例で、普段は現れません.

を無視します.

constは、プライマリ・キーまたはUniqueインデックスを直接クエリー(ユニーク・インデックス、プライマリ・キーが自動的にユニーク・インデックスを作成)

eq_ref(一意性インデックススキャン)連結テーブルは、プライマリ・キーまたはUniqueインデックスをクエリーする各インデックス・キーに対して、テーブルには

と一致するレコードが1つしかない.

ref(非一意性インデックススキャン)は、個別の値に一致するすべてのローを返し、インデックスをスキャンしますが、インデックス値が

繰り返される可能性があります.

rangeはインデックスを範囲的に検索し、where文でbetween、inなどのクエリーを使用します.このスキャンはインデックスのある点で開始され、別の点で終了し、グローバルスキャン

を行う必要はありません.

indexスキャンすべてのインデックスツリー

ALLフルテーブルスキャン

possible_keysで使用可能なインデックス

key実際に使用されているインデックス、NULLであれば上書きインデックスは使用されていません:possible_keysにはないが、keyには

が現れる.

key_lenインデックスで使用するバイト数は、クエリで使用するインデックスの長さをこの列で計算するものであり、精度を失わない場合は小さいほど、実際の使用長

ではなく最大可能な長さを表示する.

refはインデックス上の値のどの列が使用されているかを示し、可能であれば定数です.インデックス列の値

を検索するために使用される列または定数

rows各テーブルのロー数をオプティマイザによって問い合わせる

extra

filesortファイル外ソート、インデックスによるソートなし

temporaryはテンポラリ・テーブル・ソートを使用し、インデックス

には使用されません.

indexは、対応するクエリー文がインデックスを上書きしていることを示します(クエリーフィールドがインデックス範囲内であれば、インデックスはインデックスを上書きします).テーブルのデータ行にアクセスすることを回避し、効率が高いです.(インデックスがインデックスの検索を実行するために使用されると、1つのusing whereが追加され、using whereがないとインデックスのデータが直接読み込まれ、検索されないことを示す)

インデックス失効事例とソリューション
Case 1:単一テーブル範囲クエリーによる複合インデックスの失効
SQL文:

SELECT * FROM table WHERE A = ? AND B > ? ORDER BY C ASC

複合インデックスを作成すると、A、B、Cの3つのフィールドが含まれますが、2番目のフィールドを範囲クエリーすると、Cフィールドがインデックスでソートできず、filesortが表示され、SQLの速度が遅くなります.
解決方法:元のインデックスを削除し、ACフィールドのインデックスを作成します.
Case 2:インデックス2テーブル接続最適化
テーブル接続の場合

SELECT * FROM tb_A a LEFT JOIN tb_B b ON a.field = b.field

このような場合、データ量の少ない側にインデックスを作成すると、効率が向上します.(すなわち、tb_Bテーブルにインデックスを作成する)
左の接続:右に索引を付ける
右接続:左に索引を付ける
Case 3:インデックス3テーブル接続最適化

SELECT * FROM tb_A a LEFT JOIN tb_B b ON a.field = b.field LEFT JOIN tb_C c ON b.field = c.field

このような場合、テーブルを2つのテーブルに分割する形式分析(tb_Bにインデックスを付け、t b_Cにインデックスを付ける)
caseは更新を続けます...皆さんの補足を歓迎します
JOIN最適化

nestedloopのサイクル数(小表駆動大表)

をできるだけ減らす

nestedloopの内層サイクル

を優先的に最適化

は、被駆動テーブル上のJoinフィールドがインデックス

であることを保証する.

被駆動テーブルのJoinフィールドがインデックスされメモリが十分であることが保証できない場合、JoinBufferの設定

をあまりけちけちしないでください.