インデックスとテーブルリターンをカバーするMySQLの使い方

インデックスの2つの主要なカテゴリ

使用されるストレージエンジン: MySQL 5.7 InnoDB

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

• * テーブルに主キーがある場合、主キーはクラスター化インデックスです
• * テーブルに主キーがない場合、最初のNOT NULLかつ一意の（UNIQUE）列がデフォルトでクラスター化インデックスとして使用されます。
• * 上記のいずれも指定されていない場合は、デフォルトで非表示の row_id がクラスター化インデックスとして作成されます。

InnoDB のクラスター化インデックスのリーフ ノードには行レコード (実際にはページ構造で、ページには複数のデータ行が含まれます) が格納されます。InnoDB には少なくとも 1 つのクラスター化インデックスが必要です。

クラスター化インデックス クエリを使用すると、行レコードを直接見つけることができるため、非常に高速になることがわかります。

通常のインデックス

通常のインデックスはセカンダリ インデックスとも呼ばれ、クラスター化インデックス以外のインデックス、つまり非クラスター化インデックスです。

InnoDB の通常のインデックス リーフ ノードには主キー (クラスター化インデックス) の値が格納されますが、MyISAM の通常のインデックスにはレコード ポインターが格納されます。

例

テーブルを作成する

mysql> テーブルユーザーを作成します(
  -> id int(10) 自動インクリメント、
  -> 名前varchar(30),
  -> 年齢 tinyint(4)、
  -> 主キー（ID）、
  -> インデックス idx_age (年齢)
  ->)エンジン=innodb 文字セット=utf8mb4;

idフィールドはクラスター化インデックスであり、ageフィールドは通常のインデックス（セカンダリインデックス）です。

データの記入

ユーザーに挿入(名前、年齢)値('张三'、30);
ユーザー(名前、年齢)に値('李四'、20)を挿入します。
ユーザーに(名前、年齢)値('王五'、40)を挿入します。
ユーザーに(名前、年齢)値('刘八'、10)を挿入します。

mysql> ユーザーから * を選択します。
+----+--------+------+
| ID | 名前 | 年齢 |
+----+--------+------+
| 1 | 張三 | 30 |
| 2 | 李思 | 20 |
| 3 | 王武 | 40 |
| 4 | 劉八 | 10 |
+----+--------+------+

インデックスの保存構造

id は主キーであるため、クラスター化インデックスであり、そのリーフ ノードには対応する行レコードのデータが格納されます。

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

age は共通インデックス (セカンダリ インデックス) であり、非クラスター化インデックスであり、そのリーフ ノードにはクラスター化インデックスの値が格納されます。

通常のインデックス (secondaryIndex)

クエリ条件が主キー (クラスター化インデックス) である場合、クラスター化インデックスを通じて検索される行レコード データを見つけるために、B+ ツリーを 1 回スキャンするだけで済みます。

たとえば、 select * from user where id = 1;

クラスター化インデックス検索プロセス

クエリ条件が共通インデックス (非クラスター化インデックス) の場合、B+ ツリーを 2 回スキャンする必要があります。最初のスキャンでは、共通インデックスを通じてクラスター化インデックスの値を検索し、2 回目のスキャンでは、クラスター化インデックスの値を通じて検索する行レコード データを検索します。
たとえば、 select * from user where age = 30;

1. まず、共通インデックスage=30を通じて主キー値id=1を見つけます。

2. 次に、クラスター化インデックス id=1 を通じて行レコードデータを検索します。

通常のインデックス検索プロセスの最初のステップ

通常のインデックス検索プロセスの2番目のステップ

テーブルクエリに戻る

まず、通常のインデックスの値を通じてクラスター化インデックスの値を検索し、次にクラスター化インデックスの値を通じて行レコード データを検索します。インデックス B+ ツリーを 2 回スキャンする必要があり、インデックス ツリーを 1 回スキャンするよりもパフォーマンスが低下します。

インデックスカバー

SQL に必要なすべての列データは、テーブルに戻らずに 1 つのインデックス ツリーで取得できるため、高速になります。

たとえば、 select id,age from user where age = 10;

カバーインデックスの実装方法

一般的な方法は、クエリされたフィールドを共同インデックスに構築することです。

1. 例: select id,age from user where age = 10;

分析の説明: 年齢は共通インデックスであるため、年齢インデックスが使用され、B+ ツリーを 1 回スキャンするだけで対応する結果を照会できるため、カバー インデックスが実現されます。

2. 実装: select id,age,name from user where age = 10;

分析の説明: age は共通インデックスですが、name 列はインデックス ツリーにありません。そのため、age インデックスを介して id と age の値を照会した後、テーブルに戻って name の値を照会する必要があります。 Extra 列の NULL 値は、テーブル クエリが実行されたことを示します。

インデックスカバレッジを達成するには、複合インデックスidx_age_name(age,name)を作成する必要があります。

ユーザーのインデックス idx_age を削除します。
user(`age`,`name`) にインデックス idx_age_name を作成します。

分析を説明します。この時点で、フィールド age と name は複合インデックス idx_age_name です。クエリされたフィールド id、age、name の値は、インデックス ツリー内にあります。複合インデックス B+ ツリーを 1 回スキャンするだけで済みます。このようにしてインデックス カバレッジが実現されます。この時点で、Extra フィールドは Using index になっており、インデックス カバレッジが使用されていることを意味します。

インデックス カバーリングを使用して SQL を最適化するのに適したシナリオはどれですか?

テーブル全体のカウントクエリを最適化する

mysql> テーブルユーザーを作成します(
  -> id int(10) 自動インクリメント、
  -> 名前varchar(30),
  -> 年齢 tinyint(4)、
  -> 主キー（ID）、
  ->)エンジン=innodb 文字セット=utf8mb4;

たとえば、 select count(age) from user;

インデックス カバレッジ最適化の使用: 年齢フィールドにインデックスを作成する

user(age) にインデックス idx_age を作成します。

列クエリバックテーブル最適化

前回の記事でインデックスカバレッジの使用法を説明するために使用した例は、

たとえば、 select id,age,name from user where age = 10;

インデックス カバレッジを使用する: 複合インデックス idx_age_name(age,name) を作成します。

ページネーションクエリ

たとえば、 select id,age,name from user order by age limit 100,2;

名前フィールドはインデックスではないため、ページング クエリにはバック テーブル クエリが必要です。この場合、Extra はファイルソートを使用しており、クエリのパフォーマンスは低下します。

インデックス カバレッジを使用する: 複合インデックス idx_age_name(age,name) を作成します。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。