MySql インデックスの詳細な紹介と正しい使用方法

MySql インデックスの詳細な紹介と正しい使用方法

1. はじめに:

インデックスはクエリ速度に重大な影響を与えるため、インデックスを理解することがデータベースパフォーマンスチューニングの出発点となります。

インデックスは、ストレージエンジンがレコードをすばやく見つけるために使用するデータ構造です。データベースインデックスを適切に使用すると、システムのアクセスパフォーマンスが大幅に向上します。以下では、主に MySQL データベースのインデックスの種類と、より合理的で効率的なインデックス作成手法を作成する方法について説明します。

注: これは主に、InnoDB ストレージエンジンの B+Tree インデックスデータ構造を対象としています。

2. インデックスの利点

1. サーバーがスキャンする必要があるデータの量が大幅に削減され、データ取得速度が向上します。

2. サーバーがソートや一時テーブルを回避できるようにする

3. ランダムI/OをシーケンシャルI/Oに変換できる

3. インデックスの作成

3.1. 主キーインデックス

ALTER TABLE 'table_name' に PRIMARY KEY 'index_name' ('column') を追加します。

3.2 ユニークインデックス

ALTER TABLE 'テーブル名' ADD UNIQUE 'インデックス名' ('列');

3.3. 共通インデックス

ALTER TABLE 'テーブル名' ADD INDEX 'インデックス名' ('列');

3.4 全文索引

ALTER TABLE 'テーブル名' ADD FULLTEXT 'インデックス名' ('列');

3.5. 複合インデックス

ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);

4. B+ツリーインデックスルール

テストユーザーテーブルを作成する

user_testが存在する場合はテーブルを削除します。
テーブルuser_testを作成します(
 id int AUTO_INCREMENT 主キー、
 ユーザー名varchar(30) NOT NULL、
 性別ビット(1) NOT NULL デフォルト b'1',
 都市varchar(50) NOT NULL,
 年齢 int NOT NULL
)ENGINE=InnoDB デフォルト文字セット=utf8;

複合インデックスを作成します: ALTER TABLE user_test ADD INDEX idx_user(user_name , city , age);

4.1. インデックス効果のあるクエリ

4.1.1、完全な値マッチング

完全な値一致とは、インデックス内のすべての列を一致させることを指します。たとえば、上記で作成したインデックスを例にとると、where 条件の後に、(user_name、city、age) を条件として同時にデータをクエリできます。

注: whereの後のクエリ条件の順序とは関係ありません。ここは多くの学生が誤解しがちなところです。

SELECT * FROM user_test WHERE user_name = 'feinik' AND age = 26 AND city = 'Guangzhou';

4.1.2. 左端の接頭辞に一致

左端のプレフィックスに一致するということは、最初に左端のインデックス列に一致することを意味します。たとえば、上記で作成したインデックスは、クエリ条件に使用できます: (user_name), (user_name, city), (user_name, city, age)

注: 左端のプレフィックスクエリ条件を満たす順序は、インデックス列の順序とは関係ありません (例: (city, user_name)、(age, city, user_name))。

4.1.3. 列プレフィックスの一致

一致する列値の先頭を参照します。例: ユーザー名が feinik で始まるすべてのユーザーをクエリします。

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

4.1.4、範囲値の一致

たとえば、ユーザー名が feinik で始まるすべてのユーザーを照会するには、ここではインデックスの最初の列が使用されます。

SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

4.2 インデックスの制限

1. where クエリ条件にインデックス列の左端のインデックス列が含まれていない場合、次のようにインデックスクエリは使用できません。

SELECT * FROM user_test WHERE city = '広州';

または

user_test から * を選択 WHERE age= 26;

または

SELECT * FROM user_test WHERE city = 'Guangzhou' AND age = '26';

2. クエリ条件が左端のインデックス列であっても、ユーザー名がfeinikで終わるユーザーをクエリするためにインデックスを使用することはできません。

SELECT * FROM user_test WHERE user_name like '%feinik';

3. where クエリ条件に列の範囲クエリがある場合、その右側にあるすべての列ではインデックス最適化クエリを使用できません。次に例を示します。

SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE 'Guangzhou%' AND age = 26;

5. 効率的なインデックス戦略

5.1. インデックス列は式の一部にしたり、関数パラメータとして使用したりすることはできません。そうしないと、インデックスクエリは使用できません。

SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

5.2 プレフィックスインデックス

非常に長い文字列のインデックスを作成する必要がある場合もありますが、その場合、インデックスの保存スペースが増加し、インデックスの効率が低下します。1 つの戦略はハッシュインデックスを使用することです。もう 1 つはプレフィックスインデックスを使用することです。プレフィックスインデックスは、文字列の最初の n 文字をインデックスとして選択するため、インデックススペースを大幅に節約でき、インデックスの効率が向上します。

5.2.1. プレフィックスインデックスの選択性

プレフィックスインデックスは、高い選択性を確保できるほど長くする必要がありますが、長すぎてもいけません。適切なプレフィックスインデックスの選択長の値は、次のように計算できます。

（１）

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name; -- index_columnはプレフィックスインデックスを追加する列を表します

注: プレフィックスインデックスの選択率は上記の方法で計算されます。比率が高いほど、インデックスの効率が高くなります。

（２）

選択

COUNT(DISTINCT LEFT(インデックス列,1))/COUNT(*),

COUNT(DISTINCT LEFT(インデックス列,2))/COUNT(*),

COUNT(DISTINCT LEFT(インデックス列,3))/COUNT(*)

...

テーブル名から;

注：上記のステートメントを使用すると、（1）のプレフィックスインデックスに最も近い選択率を徐々に見つけることができ、対応する文字切り捨て長を使用してプレフィックスインデックスを作成できます。

5.2.2. プレフィックスインデックスの作成

ALTER TABLE テーブル名 ADD INDEX インデックス名 (インデックス列(長さ));

5.2.3. プレフィックスインデックスの使用に関する注意

プレフィックスインデックスはインデックスを小さくして高速化する効果的な方法ですが、MySQL ではプレフィックスインデックスを ORDER BY および GROUP BY に使用したり、カバーリングスキャンに使用したりすることはできません。

5.3. 適切なインデックス列の順序を選択する

複合インデックスを作成する場合、インデックス列の順序は非常に重要です。正しいインデックス順序は、インデックスを使用するクエリ方法によって異なります。複合インデックスのインデックス順序は、経験則で決定できます。つまり、選択性が最も高い列をインデックスの先頭に配置します。このルールは、プレフィックスインデックスの選択性メソッドと一致しています。ただし、このルールを使用してすべての複合インデックスの順序を決定できるわけではありません。特定のインデックス順序も、特定のクエリシナリオに応じて決定する必要があります。

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

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

クラスター化インデックスは、物理ディスク上のデータの物理的な順序を決定します。テーブルには、クラスター化インデックスを 1 つだけ含めることができます。主キーが定義されている場合、InnoDB は主キーによってデータをクラスター化します。主キーが定義されていない場合、InnoDB は代わりに一意の空でないインデックスを選択します。一意の空でないインデックスがない場合、InnoDB は暗黙的に主キーをクラスター化インデックスとして定義します。

クラスター化インデックスはアクセス速度を大幅に向上させることができます。クラスター化インデックスはインデックスと行データを同じ B ツリーに格納するため、インデックスが見つかると、それに応じて対応する行データが見つかります。ただし、クラスター化インデックスを使用する場合は、ランダムクラスター化インデックス (一般に、主キーの値が不連続で、分布範囲が不均一であることを指します) を避けるように注意する必要があります。たとえば、UUID をクラスター化インデックスとして使用すると、UUID 値の不連続性により、インデックスの断片化やランダム I/O が大量に発生し、最終的にクエリのパフォーマンスが大幅に低下するため、パフォーマンスが非常に低下します。

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

クラスター化インデックスとは異なり、非クラスター化インデックスはディスク上のデータの物理的な順序を決定せず、インデックスは含まれますが、B ツリーの行データは含まれません。行データは、B ツリーに格納されているインデックスに対応するポインタによってのみポイントされます。たとえば、上記の (user_name, city, age) に作成されたインデックスは、非クラスター化インデックスです。

5.5 カバーインデックス

インデックス（複合インデックスなど）にクエリ対象となるすべてのフィールドの値が含まれている場合、そのインデックスはカバーリングインデックスと呼ばれます。

user_test から user_name、city、age を選択します。user_name = 'feinik' かつ age > 25 です。
クエリ対象のフィールド (user_name、city、age) はすべて複合インデックスのインデックス列に含まれているため、カバーリングインデックスクエリが使用されます。カバーリングインデックスが使用されているかどうかを確認するには、実行プランの Extra の値が Using index であるかどうかを確認します。これは、カバーリングインデックスが使用されていることを証明します。カバーリングインデックスを使用すると、アクセスパフォーマンスが大幅に向上します。

5.6 インデックスを使ってソートする方法

ソート操作において、インデックスを使用してソートすることができれば、ソート速度が大幅に向上します。インデックスを使用してソートするには、次の 2 つのポイントを満たす必要があります。

1. ORDER BY句の後の列の順序は複合インデックスの列の順序と一致している必要があり、すべてのソート列のソート方向（昇順/降順）は一致している必要があります。

2. クエリされたフィールド値はインデックス列に含まれ、カバーインデックスを満たす必要があります。

例を使って分析する

user_testテーブルに複合インデックスを作成する

ALTER TABLE user_test に ADD INDEX index_user(user_name, city, age);

インデックスソートが使用できる例

1. user_name、city、age を user_test から選択し、user_name で ORDER BY します。

2. user_test から user_name、city、age を選択し、user_name、city で ORDER BY します。

3. SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;

4. user_name、city、age を user_test から選択します。WHERE user_name = 'feinik' ORDER BY city;

注: ポイント 4 は少し特殊です。where クエリ条件がインデックス列の最初の列であり、定数条件である場合は、インデックスも使用できます。

インデックスソートが使用できない場合

1. 性別は索引欄にない

SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;

2. ソート列の方向が一貫していない

SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;

3. クエリ対象のフィールド列性別がインデックス列に含まれていない

SELECT user_name、city、age、sex FROM user_test ORDER BY user_name;

4. whereクエリ条件の後のuser_nameは範囲クエリなので、インデックスの他の列は使用できません。

SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;

5. 複数のテーブルをクエリする場合、インデックスソートは、ORDER BY 後のソートフィールドがすべて最初のテーブルのインデックス列である場合にのみ使用できます (上記のインデックスソートの 2 つのルールを満たす必要があります)。たとえば、別のユーザー拡張テーブル user_test_ext を作成し、uid のインデックスを確立します。

user_test_ext が存在する場合はテーブルを削除します。

テーブルuser_test_extを作成します(

  id int AUTO_INCREMENT 主キー、

  uid int NOT NULL、

  u_password VARCHAR(64) NULLではない

)ENGINE=InnoDB デフォルト文字セット=utf8;

テーブル user_test_ext を変更し、インデックス index_user_ext(uid) を追加します。

インデックスで並べ替え

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY u.user_name;

インデックスソートなし

SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;

6. まとめ

この記事では、主に B+Tree ツリー構造のインデックス作成ルール、さまざまなインデックスの作成、およびクエリ速度を最大化するための効率的なインデックス作成手法を正しく作成する方法について説明します。もちろん、インデックスの使用方法に関するヒントは他にもたくさんあります。インデックス作成についてさらに学ぶには、関連する経験を積む必要があります。

読んでいただきありがとうございます。お役に立てれば幸いです。このサイトをサポートしていただきありがとうございます。

以下もご興味があるかもしれません: