MySQL インデックスの設計と最適化の方法

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

データベース インデックスは、追加の書き込みとストレージ スペースを犠牲にして、データベース テーブルでのデータ取得操作の速度を向上させるデータ構造です。簡単に言えば、索引は本の目次に似ており、そこに記録されたページ番号に基づいて必要なコンテンツをすばやく見つけることができます。 ——Wikipedia の一般的な索引は何ですか?

• 通常インデックス: 制限のない最も基本的なインデックス
• ユニークインデックス：「通常のインデックス」と同様ですが、インデックス列の値は一意である必要がありますが、null値が許可されます
• 主キー インデックス: null 値を許可しない特別なインデックスです。
• 全文インデックス: MyISAM テーブルにのみ使用できます。大規模なデータの場合、全文インデックスの生成には時間がかかり、スペースも消費されます。
• 複合インデックス: 複数条件クエリの効率を向上させるために、「最左接頭辞一致原則」に従って複合インデックスを確立できます。
  

ここでは比較的複雑な組み合わせを例に挙げて、最適化する方法を紹介します。

左端のプレフィックス一致の原則

まず、左端のプレフィックス一致の原則が何であるかを知る必要があります。

最左プレフィックス一致原則とは、データ取得に B+Tree ジョイント インデックスを使用する場合、MySQL オプティマイザは述語 (フィルタ条件) を読み取り、範囲クエリまたは不等クエリに遭遇するまでジョイント インデックス フィールドが作成された順序で右方向に一致させます。このフィールドの後のインデックス列は使用されません。このとき、 key_lenを計算すると、ジョイント インデックスによって実際に使用されているインデックス列を分析できます。

key_lenの計算方法

key_len計算は、インデックスの左端のプレフィックス一致の原則を理解するのにも役立ちます。

key_len 、 order byを除いて、結果セットを取得するために使用される選択インデックスの長さ [バイト数] を示します。つまり、 order byでもインデックスが使用される場合、 key_lenは含まれません。

key_lenを計算する前に、基本的なデータ型を確認しましょう (UTF8 エンコードを例にします)。

テストデータテーブルは次のとおりです。

テーブル `test_table` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) デフォルトはNULLではない、
 `b` int(11) デフォルトはNULLではない、
 `c` int(11) デフォルトはNULLではない、
 主キー (`id`)、
 キー `test_table_a_b_c_index` (`a`,`b`,`c`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

ヒットインデックス:

mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 12 | const、const、const | 1 | 100.00 | インデックスを使用 |
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+

key_len = 12がわかります。これはどのように計算されるのでしょうか?
文字セットは UTF8 なので、1 つのフィールドは 4 バイトを占め、3 つのフィールドは 4 * 3 = 12 バイトになります。

NULL が許可されるかどうか。NULL が許可される場合は、フィールドをマークするために追加のバイトが必要です。異なるデータ型には異なるバイト サイズが必要です。

mysql> ALTER TABLE `test_table` CHANGE `a` `a` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `c` `c` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `b` `b` INT(11) NULL;
mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 15 | const、const、const | 1 | 100.00 | インデックスを使用 |
+----+--------------+-------------+------------+-----------+-------------------------+-------------------------+-----------+--------------------+------------+-------------+

フィールドが空になることが許可されている場合、 key_len 15 = 4 3 + 1 3 になることがわかります (INT 型が空の場合、余分なバイトを占有します)。

インデックスの最適化

これらの基礎知識があれば、実際の SQL に基づいてパフォーマンスを判断できます。

上記のデータ テーブルを例に、3 つのフィールド a、b、c の結合インデックスを作成します。

通常、実行プランを表示するときに、Extra 列が Using index になっている場合は、オプティマイザーがカバーリング インデックスを使用していることを意味します。

• SQL1はカバーインデックスを使用でき、パフォーマンスが良好です。
• SQL2はカバーインデックスを使用してソートを回避できるため、パフォーマンスが向上します。
• SQL3ではカバーインデックスを使用できますが、where句に基づいてフィルタリングする必要があります。
• SQL4では部分インデックスaを使用できますが、ソートを避けることができず、パフォーマンスが低下します。
• SQL5 では、カバーリング インデックスをフルに活用してソートを回避できるため、パフォーマンスが向上します。
• SQL6 ではカバーリング インデックスを使用できますが、ソートは避けられません (これは、MySQL InnoDB がインデックスを作成するときに昇順をデフォルトとし、インデックスを自動的に降順でソートできないためです)。
• SQL7 ではカバーリングインデックスを使用できますが、where 句 (非固定値クエリ) に従ってフィルタリングする必要があります。
  

インデックス仕様を作成する

• インデックスのメンテナンスのコストを考慮すると、1 つのテーブル内のインデックスの数は 5 を超えてはならず、1 つのインデックス内のフィールドの数も 5 を超えてはなりません。
• 性別などのカーディナリティの低い列にはインデックスを作成しないでください。 低カーディナリティ列で作成されたインデックス クエリは、特にテーブルを返すコストがある場合、必ずしも完全なテーブル スキャンよりもパフォーマンス上の利点があるわけではありません。
• 適切に結合インデックスを作成します。(a,b,c) は (a)、(a,b)、(a,b,c) と同等です。
• カバーリング インデックスを適切に使用すると、IO が削減され、ソートが回避されます。

上記は、MySQL インデックスの設計と最適化の方法の詳細です。MySQL インデックスの設計と最適化の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。